BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
John_Wick
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
John_Wick
Obsidian | Level 7

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.

View solution in original post

4 REPLIES 4
AhmedAl_Attar
Ammonite | Level 13

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
Super User

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
Obsidian | Level 7

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
Obsidian | Level 7
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
 

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

Get Started with SAS Information Catalog in SAS Viya

SAS technical trainer Erin Winters shows you how to explore assets, create new data discovery agents, schedule data discovery agents, and much more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1878 views
  • 4 likes
  • 3 in conversation