I am trying to create connection between my oracle and clickhouse databases, so I could query oracle through ch like this: SELECT * FROM odbc('DSN=OracleODBC-21', 'sys', 'test'). I have successfully installed unixODBC, Oracle Instant Client, Oracle ODBC for client.
Also, I configured my .odbc.ini and odbcinst.ini, so I can access oracle:
[oracle@host ~]$ isql -v OracleODBC-21 +---------------------------------------+ | Connected! | ... SQL> select * from sys.test; +-----------------------------------------+-----------------------------------------------------------------------------------------------------+ | ID | DATA | +-----------------------------------------+-----------------------------------------------------------------------------------------------------+ | 0 | 123 | +-----------------------------------------+-----------------------------------------------------------------------------------------------------+ User clickhouse also can do this, but with some envs:
[oracle@host ~]$ sudo -u clickhouse bash -c "export LD_LIBRARY_PATH=/opt/oracle/instantclient_21_19; isql -v OracleODBC-21" +---------------------------------------+ | Connected! | ... But when I am trying to query oracle in ch:
host :) select * from odbc('DSN=OracleODBC-21','sys','test'); SELECT * FROM odbc('DSN=OracleODBC-21', 'sys', 'test') Query id: d263cc54-bd51-4a97-94c0-085177149947 Elapsed: 9.529 sec. Received exception from server (version 25.6.2): Code: 86. DB::Exception: Received from localhost:9000. DB::HTTPException. DB::HTTPException: Received error from remote server http://127.0.0.1:9018/columns_info?use_connection_pooling=1&version=1&connection_string=DSN%3DOracleODBC-21&schema=sys&table=test&external_table_functions_use_nulls=1. HTTP status code: 500 'Internal Server Error', body length: 267 bytes, body: 'Error getting columns from ODBC 'std::exception. Code: 1001, type: nanodbc::database_error, e.what() = contrib/nanodbc/nanodbc/nanodbc.cpp:1275: IM004: [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed (version 25.1.5.31 (official build))' '. (RECEIVED_ERROR_FROM_REMOTE_IO_SERVER) Will be grateful for any advice.
