topic SAS Access to ODBC for ClickHouse in Administration and Deployment

SAS Access to ODBC for ClickHouse

John_Wick — Fri, 26 May 2023 14:39:13 GMT

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:

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

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 rows fetch

Re: SAS Access to ODBC for ClickHouse

AhmedAl_Attar — Fri, 26 May 2023 15:14:15 GMT

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

Re: SAS Access to ODBC for ClickHouse

Ksharp — Sat, 27 May 2023 10:14:14 GMT

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;

Re: SAS Access to ODBC for ClickHouse

John_Wick — Thu, 01 Jun 2023 09:46:02 GMT

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.

Re: SAS Access to ODBC for ClickHouse

John_Wick — Fri, 02 Jun 2023 15:16:39 GMT

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