Running Queries on ClickHouse via ODBC in SAS Viya

[Mahis]

Hello everyone,

We have configured a connection between SAS Viya 4 and ClickHouse using SAS ACCESS to ODBC and the connection itself works we can connect successfully using a defined DSN.
but when we try to run any query (even simple ones like SELECT * FROM my_table LIMIT 10) we get this error:

ERROR: CLI describe error: H

Example:

proc sql;
connect to odbc (
dsn="ClickHouse_DSN"
user="default"
password=""
);

select * from connection to odbc (
SELECT name
FROM system.tables
WHERE database = 'db_x'
);

disconnect from odbc;
quit;

log:

ODBC: ENTER SQLAllocHandle
2 <SQL_HANDLE_DBC>
0x00000000e404ecc0
0x00000000c38dca38
ODBC: EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
2 <SQL_HANDLE_DBC>
0x00000000e404ecc0
0x00000000c38dca38 (0x00000000d801e910)
ODBC: ENTER SQLDriverConnect
0x00000000d801e910
0x0000000000000000
0x00000000d4a2c710 XXXXXXX
-3 SQL_NTS
0x00000000d4a2c300
1024
0x00000000d4a2cb12
0 <SQL_DRIVER_NOPROMPT>
ODBC: EXIT SQLDriverConnect with return code 0 (SQL_SUCCESS)
0x00000000d801e910
0x0000000000000000
0x00000000d4a2c710 XXXXXXX
-3 SQL_NTS
0x00000000d4a2c300 XXXXXXX
1024
0x00000000d4a2cb12 [ 31]
0 <SQL_DRIVER_NOPROMPT>
ODBC: ENTER SQLGetInfo
0x00000000d801e910
47 <SQL_USER_NAME>
0x00000000d4a2d5b0
64
0x00000000d4a2c702
ODBC: EXIT SQLGetInfo with return code 0 (SQL_SUCCESS)
0x00000000d801e910
47 <SQL_USER_NAME>
0x00000000d4a2d5b0 [ 7] "default"
64
0x00000000d4a2c702 (7)
ODBC: ENTER SQLAllocHandle
3 <SQL_HANDLE_STMT>
0x00000000d801e910
0x00000000c38dca48
ODBC: EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
3 <SQL_HANDLE_STMT>
0x00000000d801e910
0x00000000c38dca48 (0x00000000d8039870)
ODBC: ENTER SQLSetConnectAttr
0x00000000d801e910
102 <SQL_AUTOCOMMIT>
1 <SQL_AUTOCOMMIT_ON>
-6 <SQL_IS_INTEGER>
ODBC: EXIT SQLSetConnectAttr with return code 0 (SQL_SUCCESS)
0x00000000d801e910
102 <SQL_AUTOCOMMIT>
1 <SQL_AUTOCOMMIT_ON>
-6 <SQL_IS_INTEGER>
ODBC: ENTER SQLGetInfo
0x00000000d801e910
2 <SQL_DATA_SOURCE_NAME>
0x00000000d4a2c0c0
128
0x00000000d4a2b602
ODBC: EXIT SQLGetInfo with return code 0 (SQL_SUCCESS)
0x00000000d801e910
2 <SQL_DATA_SOURCE_NAME>
0x00000000d4a2c0c0 [ 14] "ClickHouse_DSN"
128
0x00000000d4a2b602 (14)
ODBC: ENTER SQLSetConnectAttr
0x00000000d801e910
102 <SQL_AUTOCOMMIT>
1 <SQL_AUTOCOMMIT_ON>
-6 <SQL_IS_INTEGER>
ODBC: EXIT SQLSetConnectAttr with return code 0 (SQL_SUCCESS)
0x00000000d801e910
102 <SQL_AUTOCOMMIT>
1 <SQL_AUTOCOMMIT_ON>
-6 <SQL_IS_INTEGER>
ODBC: Database/data source: ClickHouse_DSN
ODBC: USER=default, PASS=XXXXXXX
ODBC: No schema given, using database default.
ODBC: ENTER SQLGetConnectAttr
0x00000000d801e910
102 <SQL_ATTR_AUTOCOMMIT>
0x00000000d4a2c678
-6 <SQL_IS_INTEGER>
ODBC: EXIT SQLGetConnectAttr with return code 0 (SQL_SUCCESS)
0x00000000d801e910
102 <SQL_ATTR_AUTOCOMMIT>
0x00000000d4a2c678 (1) <SQL_AUTCOMMIT_ON>
-6 <SQL_IS_INTEGER>
ODBC: ENTER SQLGetConnectAttr
0x00000000d801e910
102 <SQL_ATTR_AUTOCOMMIT>
0x00000000d4a2c5e8
-6 <SQL_IS_INTEGER>
ODBC: EXIT SQLGetConnectAttr with return code 0 (SQL_SUCCESS)
0x00000000d801e910
102 <SQL_ATTR_AUTOCOMMIT>
0x00000000d4a2c5e8 (1) <SQL_AUTCOMMIT_ON>
-6 <SQL_IS_INTEGER>
ODBC: ENTER SQLAllocHandle
3 <SQL_HANDLE_STMT>
0x00000000d801e910
0x00000000bcc8de68
ODBC: EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
3 <SQL_HANDLE_STMT>
0x00000000d801e910
0x00000000bcc8de68 (0x00000000d804adb0)
ODBC: ENTER SQLSetStmtAttr
0x00000000d804adb0
7 <SQL_ATTR_CONCURRENCY>
1 <SQL_CONCUR_READ_ONLY>
-6 <SQL_IS_INTEGER>
ODBC: EXIT SQLSetStmtAttr with return code 0 (SQL_SUCCESS)
0x00000000d804adb0
7 <SQL_ATTR_CONCURRENCY>
1 <SQL_CONCUR_READ_ONLY>
-6 <SQL_IS_INTEGER>
ODBC: ENTER SQLSetStmtAttr
0x00000000d804adb0
1 <SQL_ATTR_MAX_ROWS>
0
-6 <SQL_IS_INTEGER>
ODBC: EXIT SQLSetStmtAttr with return code 0 (SQL_SUCCESS)
0x00000000d804adb0
1 <SQL_ATTR_MAX_ROWS>
0
-6 <SQL_IS_INTEGER>
ODBC: ENTER SQLFreeStmt
0x00000000d8039870
0 <SQL_CLOSE>
ODBC: EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
0x00000000d8039870
0 <SQL_CLOSE>
ODBC: ENTER SQLPrepare
0x00000000d804adb0
0x00000000bcc8e840 [ 64] "SELECT name FROM system.tables WHERE database = 'db_x'"
64
ODBC: EXIT SQLPrepare with return code 0 (SQL_SUCCESS)
0x00000000d804adb0
0x00000000bcc8e840 [ 64] "SELECT name FROM system.tables WHERE database = 'db_x'"
64
ODBC: ENTER SQLNumResultCols
0x00000000d804adb0
0x00000000d4a2c7a8
ODBC: EXIT SQLNumResultCols with return code -1 (SQL_ERROR)
0x00000000d804adb0
0x00000000d4a2c7a8 (-7200)
ODBC: ENTER SQLEndTran
2 <SQL_HANDLE_DBC>
0x00000000d801e910
1 <SQL_ROLLBACK>
ODBC: EXIT SQLEndTran with return code 0 (SQL_SUCCESS)
2 <SQL_HANDLE_DBC>
0x00000000d801e910
1 <SQL_ROLLBACK>
ODBC: ENTER SQLFreeHandle
3 <SQL_HANDLE_STMT>
0x00000000d804adb0
ODBC: EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
3 <SQL_HANDLE_STMT>
0x00000000d804adb0
ERROR: CLI describe error: H
ODBC: ENTER SQLGetConnectAttr
0x00000000d801e910
102 <SQL_ATTR_AUTOCOMMIT>
0x00000000d4a2d988
-6 <SQL_IS_INTEGER>
ODBC: EXIT SQLGetConnectAttr with return code 0 (SQL_SUCCESS)
0x00000000d801e910
102 <SQL_ATTR_AUTOCOMMIT>
0x00000000d4a2d988 (1) <SQL_AUTCOMMIT_ON>
-6 <SQL_IS_INTEGER>
ODBC: ENTER SQLFreeHandle
3 <SQL_HANDLE_STMT>
0x00000000d8039870
ODBC: EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
3 <SQL_HANDLE_STMT>
0x00000000d8039870
ODBC: ENTER SQLDisconnect
0x00000000d801e910
ODBC: EXIT SQLDisconnect with return code 0 (SQL_SUCCESS)
0x00000000d801e910

We also tested the same connection directly from a CAS pod using isql and it works.

Has anyone faced a similar issue, either with ClickHouse or any other ODBC connection in SAS Viya? Any ideas or suggestions would be appreciated. Thanks!

[SASKiwi]

How about an even simpler example:

proc sql;
connect to odbc (
dsn="ClickHouse_DSN"
user="default"
password=""
);

select * from connection to odbc (
SELECT count(*)  as row_count
FROM mytable
);

disconnect from odbc;
quit;

The error suggests a column describe issue so it's possible a simple row count may bypass that.

[Mahis]

I tested to use the same example but i got the same error:

1    /* region: Generated preamble */
2    /* Make sure the current directory is writable */
3    data _null_;
4        length rc 4;
5        %let tworkloc="%sysfunc(getoption(work))";
6        rc=dlgcdir(&tworkloc);
7    run;
NOTE: The current working directory is now 
      "/opt/sas/viya/config/var/tmp/compsrv/default/e65e07e5-570e-47c6-bae1-d1f27f9fbf0a/SAS_work3CD300000231_sas-compute-server-128
      fc60b-a745-47c8-a990-4b40592daa6b-1798".
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
8    
9    /* Setup options */
10   title;
11   footnote;
12   options validvarname=any;
13   options validmemname=extend;
14   options dtreset date number;
15   options device=png;
16   
17   /* Setup macro variables */
18   %let syscc=0;
19   %let _clientapp = %nrquote(%nrstr(SAS Studio));
20   %let _clientappabbrev = %nrquote(%nrstr(Studio));
21   %let _clientappversion=2023.10;
22   %let _clientversion=;
23   %let _sasservername=&SYSHOSTNAME;
24   %let _sashostname=&SYSHOSTNAME;
25   %let _sasprogramfilehost=&SYSHOSTNAME;
26   %let _clientuserid = %nrquote(%nrstr(s.mahis));
27   %let _clientusername = %nrquote(%nrstr(User Mahis));
28   %let clientmachine = %nrquote(%nrstr());
29   %let _clientmachine = %nrquote(%nrstr());
30   %let _clientmode = %nrquote(%nrstr(viya));
31   %let sasworklocation="%sysfunc(getoption(work))/";
32   filename _cwd &sasworklocation;
33   data _null_;
34       call symput('_sasworkingdir',pathname('_cwd'));
35   run;
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
36   filename _cwd;
NOTE: Fileref _CWD has been deassigned.
37   %let _sasprogramfile = %nrquote(%nrstr());
38   %let _baseurl = %nrquote(%nrstr(https://sasviya.domain.com/SASStudio/));
39   %let _execenv = %nrquote(%nrstr(SASStudio));
40   %symdel _dataout_mime_type _dataout_name _dataout_url _dataout_table / nowarn;
41   %let _sasws_ = %bquote(%sysfunc(getoption(work)));
42   %let _saswstemp_ = %bquote(%sysfunc(getoption(work)));
43   
44   /* Detect SAS/Graph and setup graph options */
45   data _null_;
46       length rc $255;
47       call symput("graphinit","");
48       call symput("graphterm","");
49       rc=tslvl('sasxgopt','n');
50       _error_=0;
51       if (rc^=' ') then do;
52           call symput("graphinit","goptions reset=all gsfname=_gsfname;");
53           call symput("graphterm","goptions noaccessible;");
54       end;
55   run;
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
56   data _null_;
57       length rc 4;
58       rc=sysprod("PRODNUM002");
59       if (rc^=1) then do;
60           call symput("graphinit","");
61           call symput("graphterm","");
62       end;
63   run;
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
64   
65   /* Setup ODS destinations */
66   ods _all_ close;
67   %studio_results_directory;
68   filename _htmlout "&_results_prefix_..html";
69   filename _listout "&_results_prefix_..lst";
70   filename _gsfname temp;
71   filename _dataout "&_results_prefix_..dat";
72   ods autonavigate off;
73   ods graphics on;
74   ods html5 (id=web) METATEXT='http-equiv="Content-Security-Policy" content="default-src ''none''; style-src ''unsafe-inline'';
74 ! img-src data: ;"' device=png gpath="&_saswstemp_" path="&_saswstemp_" encoding=utf8 file=_htmlout (title='Results:SAS
74 ! Program.sas') style=Illuminate options(bitmap_mode='inline' outline='on' svg_mode='inline'
74 ! css_prefix=".ods_&SYS_COMPUTE_JOB_ID" body_id="div_&SYS_COMPUTE_JOB_ID" );
NOTE: Writing HTML5(WEB) Body file: _HTMLOUT
75   ods listing file=_listout;
76   &graphinit;
77   %studio_initialize_custom_output;
78   /* endregion */
79   
80   proc sql;
81   connect to odbc (
82   dsn="ClickHouse_DSN"
83   user="default"
84   password=XX
85   );
86   
87   select * from connection to odbc (
88   SELECT count(*)  as row_count
89   FROM mytable
90   );
ERROR: CLI describe error: H
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
91   
92   disconnect from odbc;
NOTE: Statement not executed due to NOEXEC option.
93   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.57 seconds
      cpu time            0.03 seconds
      
94   
95   /* region: Generated postamble */
96   /* Close ODS destinations */
97   &graphterm; ;*';*";*/;run;quit;
98   quit;run;
99   ods html5 (id=web) close;
100  ods listing close;
101  %if %sysfunc(fileref(_gsfname)) lt 0 %then %do;
102      filename _gsfname clear;
NOTE: Fileref _GSFNAME has been deassigned.
103  %end;
104  %studio_capture_custom_output;
105  /* endregion */
106  

[SASKiwi · Accepted solution]

I suggest you open a track with Tech Support and also check with ClickHouse to see if you have the correct and most up to date ODBC driver. My suspicion is the problem is with the driver not correctly translating database commands.

Looks like another user has got it going successfully: https://communities.sas.com/t5/Administration-and-Deployment/SAS-Access-to-ODBC-for-ClickHouse/td-p/...

[Mahis]

You were right, the issue was with the ODBC driver.
Tech Support confirmed that since we're using a UnixODBC driver, we need to set dm_unicode='utf-16' in both the libname and caslib statements
After adding that, everything worked correctly. Thank you for your help