SAS Access to ODBC for ClickHouse

[John_Wick]

Hello everyone!

Does anyone have SAS and ClickHouse integration on projects?

I am trying to establish a connection via SAS ACCESS TO ODBC with ClickHouse, but nothing is working.

isql is working successfully, but it does not work through SAS.

I have two cases:

1)

odbc.ini

[ClickHouse]

Description = connection to ClickHouse

DNS = clickhouse

ServerName = dwh-*********

Driver = /sas/clickhouse-odbc-1.2.1-Linux/lib64/libclickhouseodbc.so

database = default

UID = ***

PWD = ***

Port = 8123

libname click ODBC DATAsrc=ClickHouse SCHEMA=default USER=*** PASSWORD=***;

NOTE: Libref CLICK was successfully assigned as follows:

           Engine: ODBC

           Physical Name: ClickHouse 

But there are no tables, and they are not registered in SAS MC. Without any obvious error in the log.

When I try to execute:

proc sql;

connect to odbc;

execute( select 1) by click;

quit;

As a result, I got an error:

ERROR: CLI error trying to establish connection: [DataDirect] [ODBC lib] Driver Manager Message file not found.

Please check for the value of InstallDir in your odbc.ini.

LD_LIBRARY_PATH contains /sas/clickhouse-odbc-1.2.1-Linux/lib64/

isql -v ClickHouse

Connected!

SQL> SELECT 1

[S1000]Connection refused

[ISQL]ERROR: Could not SQLExecute

2)

odbc.ini

[ClickHouse]

Description = connection to ClickHouse

DNS = clickhouse

Server = dwh-*********       

Driver = /sas/clickhouse-odbc-1.2.1-Linux/lib64/libclickhouseodbc.so

database = default

UID = ***

PWD = ***

Port = 8123

libname click ODBC DATAsrc=ClickHouse SCHEMA=default USER=*** PASSWORD=***;

ERROR: CLI error trying to establish connection: 523 650

isql -v ClickHouse

Connected!

SQL> SELECT 1

1

1

1 rows fetch

[AhmedAl_Attar]

Hi @John_Wick 

First, let me say, I like the name, and I watched all four parts of John Wick 😆

I believe you'll be better served by Opening a SAS Tech Support Track for this issue of yours.

AS long as you can verify 

- The ClickHouse Database ODBC Driver requirements is compatible with SAS's ODBC Driver.

I have seen it with Tableau, SAS's JDBC driver's protocol/version was not compatible with Tableau! (SAS v2, Tableau wanted v4)

Hope this helps

[Ksharp]

You need to specify RIGHT schema.
libname click ODBC DATAsrc=ClickHouse SCHEMA=DBO USER=*** PASSWORD=***;

You could check RIGHT schema by pass-through SQL:
proc sql;
connect to odbc(user= pw=);
select * from connection to odbc(ODBC::SQLTables);
quit;

[John_Wick · Accepted solution]

The problem was in the confict with the ODBC DataDirect driver for PostgreSQL.

Because DataDirect appears in the error, although the ClickHouse driver is not DataDirect.

The connection on the other server was successful.

[John_Wick]

To change ODBC Driver Manager from DataDirect to unixODBC, you should set unixODBC libraries in LD_LIBRARY_PATH first.

For example:

in <SASHome>/SASFoundation/9.4/bin/sasenv_local

LD_LIBRARY_PATH=/usr/local/unixODBC/lib:$LD_LIBRARY_PATH

export LD_LIBRARY_PATH