CH to Teradata via ODBC

[virtualb0x]

Describe the unexpected behaviour
We've got a teradata installation where we've created a one-column table, consists of numbers only

DDL is:

CREATE MULTISET TABLE DEV_ODW.TABLE_OF_ONE_FIELD ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
MAP = TD_MAP1
(
NUMBER_FIELD DECIMAL(1,0))
NO PRIMARY INDEX ;

I also installed teradata odbc driver on linux machine where CH Is running and configured it

The isql -v is running:

I've connected to CH via clickhouse_client and wanted to get data from this table via odbc:

Сreating table:

CREATE TABLE IF NOT EXISTS TEST_ACCESS.ONE_FIELD_DOUBLE_dew
("NUMBER_FIELD" Int64)
ENGINE = ODBC(`DSN=edw_dev`, `DEV_ODW`, `TABLE_OF_ONE_FIELD`)

Getting result:

SELECT * FROM TEST_ACCESS.ONE_FIELD_DOUBLE_dew

SELECT *
FROM TEST_ACCESS.ONE_FIELD_DOUBLE_dew

Query id: 6e0d74a4-de2a-4b25-b582-aae7f54d053b

But then I receive an error:

Elapsed: 9.563 sec.

Received exception from server (version 24.3.2):
Code: 86. DB::Exception: Received from 127.0.0.1:9000. DB::HTTPException. DB::HTTPException: Received error from remote server http://127.0.0.1:9018/identifier_quote?use_connection_pooling=1&version=1&connection_string=DSN%3Dedw_dev&use_connection_pooling=1. HTTP status code: 500 Internal Server Error, body: Error getting identifier quote style from ODBC 'std::exception. Code: 1001, type: nanodbc::database_error, e.what() = contrib/nanodbc/nanodbc/nanodbc.cpp:1046: 0000:  (version 24.3.2.1)'
. (RECEIVED_ERROR_FROM_REMOTE_IO_SERVER)

Wanted to see data

• Which ClickHouse server version to use

ClickHouse client version 24.3.2.1.
Connecting to 127.0.0.1:9000 as user clickhouse_admin.
Connected to ClickHouse server version 24.3.2.

• Which interface to use, if matters
  clickhouse-odbc-brigde

Expected behavior
Wanted to see data

Error message and/or stacktrace
MEssage in server/brigde log:

2024.05.16 22:59:22.245478 [ 1542710 ] {1f964b33-0c1c-4f20-b778-217f5839160b} <Error> TCPHandler: Code: 86. DB::HTTPException: Received error from remote server http://127.0.0.1:9018/identifier_quote?use_connection_pooling=1&version=1&connection_string=DSN%3Dedw_prod&use_connection_pooling=1. HTTP status code: 500 Internal Server Error, body: Error getting identifier quote style from ODBC 'std::exception. Code: 1001, type: nanodbc::database_error, e.what() = contrib/nanodbc/nanodbc/nanodbc.cpp:1046: 0000:  (version 24.3.2.1)'
. (RECEIVED_ERROR_FROM_REMOTE_IO_SERVER), Stack trace (when copying this message, always include the lines below):

0. Poco::Exception::Exception(String const&, int) @ 0x00000000133c83d2
1. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000aff66f7
2. DB::Exception::Exception<String const&, int, String const&, String const&>(int, FormatStringHelperImpl<std::type_identity<String const&>::type, std::type_identity<int>::type, std::type_identity<String const&>::type, std::type_identity<String const&>::type>, String const&, int&&, String const&, String const&) @ 0x000000000b236aec
3. DB::HTTPException::HTTPException(int, String const&, Poco::Net::HTTPResponse::HTTPStatus, String const&, String const&) @ 0x000000000b23697e
4. DB::assertResponseIsOk(String const&, Poco::Net::HTTPResponse&, std::basic_istream<char, std::char_traits<char>>&, bool) @ 0x000000000b2367de
5. DB::ReadWriteBufferFromHTTP::callImpl(Poco::Net::HTTPResponse&, Poco::URI const&, String const&, std::optional<DB::ReadWriteBufferFromHTTP::HTTPRange> const&, bool) const @ 0x000000000ca3d4f5
6. DB::ReadWriteBufferFromHTTP::callWithRedirects(Poco::Net::HTTPResponse&, String const&, std::optional<DB::ReadWriteBufferFromHTTP::HTTPRange> const&) @ 0x000000000ca3d6b9
7. DB::ReadWriteBufferFromHTTP::initialize() @ 0x000000000ca3df7b
8. void std::__function::__policy_invoker<void ()>::__call_impl<std::__function::__default_alloc_func<DB::ReadWriteBufferFromHTTP::nextImpl()::$_0, void ()>>(std::__function::__policy_storage const*) @ 0x000000000ca3fe7d
9. DB::ReadWriteBufferFromHTTP::doWithRetries(std::function<void ()>&&, std::function<void ()>, bool) const @ 0x000000000ca3ba8a
10. DB::ReadWriteBufferFromHTTP::nextImpl() @ 0x000000000ca3f17f
11. DB::readStringBinary(String&, DB::ReadBuffer&, unsigned long) @ 0x000000000b0689a6
12. DB::XDBCBridgeHelper<DB::ODBCBridgeMixin>::getIdentifierQuotingStyle() @ 0x000000000ca398f2
13. DB::StorageXDBC::getReadPOSTDataCallback(std::vector<String, std::allocator<String>> const&, DB::ColumnsDescription const&, DB::SelectQueryInfo const&, std::shared_ptr<DB::Context const> const&, DB::QueryProcessingStage::Enum&, unsigned long) const @ 0x0000000010967b4a
14. DB::IStorageURLBase::read(DB::QueryPlan&, std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::StorageSnapshot> const&, DB::SelectQueryInfo&, std::shared_ptr<DB::Context const>, DB::QueryProcessingStage::Enum, unsigned long, unsigned long) @ 0x000000001095202c
15. DB::StorageXDBC::read(DB::QueryPlan&, std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::StorageSnapshot> const&, DB::SelectQueryInfo&, std::shared_ptr<DB::Context const>, DB::QueryProcessingStage::Enum, unsigned long, unsigned long) @ 0x0000000010968958
16. DB::(anonymous namespace)::buildQueryPlanForTableExpression(std::shared_ptr<DB::IQueryTreeNode>, DB::SelectQueryInfo const&, DB::SelectQueryOptions const&, std::shared_ptr<DB::PlannerContext>&, bool, bool) @ 0x000000000fdf5625
17. DB::buildJoinTreeQueryPlan(std::shared_ptr<DB::IQueryTreeNode> const&, DB::SelectQueryInfo const&, DB::SelectQueryOptions&, std::unordered_set<String, std::hash<String>, std::equal_to<String>, std::allocator<String>> const&, std::shared_ptr<DB::PlannerContext>&) @ 0x000000000fdece81
18. DB::Planner::buildPlanForQueryNode() @ 0x000000000fdcd150
19. DB::Planner::buildQueryPlanIfNeeded() @ 0x000000000fdc9ea7
20. DB::InterpreterSelectQueryAnalyzer::getQueryPlan() @ 0x000000000fdc80ed
21. DB::executeQueryImpl(char const*, char const*, std::shared_ptr<DB::Context>, DB::QueryFlags, DB::QueryProcessingStage::Enum, DB::ReadBuffer*) @ 0x000000001016ec8d
22. DB::executeQuery(String const&, std::shared_ptr<DB::Context>, DB::QueryFlags, DB::QueryProcessingStage::Enum) @ 0x000000001016b975
23. DB::TCPHandler::runImpl() @ 0x00000000111163eb
24. DB::TCPHandler::run() @ 0x000000001112d559
25. Poco::Net::TCPServerConnection::start() @ 0x00000000132e57c7
26. Poco::Net::TCPServerDispatcher::run() @ 0x00000000132e5c9a
27. Poco::PooledThread::run() @ 0x000000001341d7e7
28. Poco::ThreadImpl::runnableEntry(void*) @ 0x000000001341b403
29. ? @ 0x00007f41b7e7858e
30. ? @ 0x00007f41b7da92d3

Additional context
Will be appreciate for any help or ideas. More than a week I can't force it

[alexey-milovidov]

Are you able to use this ODBC driver with the isql or iusql tools?

[UnamedRus]

From my experience, it make sense to test jdbc bridge as well, it tends to have less strange compatibility problems than odbc.

[virtualb0x]

@alexey-milovidov I've just tested isql is running ok with my version of odbc driver and server has connected to teradata, select is working okay. But nobody uses isql like a basic client to work with teradata

@UnamedRus we have jdbc bridge as well. It's working like a charm with teradata and there were no pain with its deployement. But officially jdbc bridge is archived now in GitHub and it is depreciated AFAIK

[UnamedRus]

officially

For latest 3+? years it was maintained by community member and right, latest release by him was made 2 years back.
It's not like it suddenly became "nonfunctional" just because it was "officially" was recognized as deprecated. As this official support didn't existed already for x years.

BTW, i didn't checked by myself but this community member have some project which works kinda jdbc bridge but via url function
https://github.com/jdbcx/jdbcx

[den-crane]

@virtualb0x can you please share the output of echo $LD_PRELOAD, echo $LD_LIBRARY_PATH

[virtualb0x]

@den-crane

[test-01 ~]$ echo $LD_PRELOAD 

[test-01 ~]$ echo $LD_LIBRARY_PATH
/usr/lib/oracle/12.2/client64/lib:/usr/lib/:/opt/teradata/client/15.10/odbc_64/lib

LD_PRELOAD is empty. I've read CH is ignoring it? #32457 (comment)

@UnamedRus Agree with you about support. May be I will be lucky enough to explain it to users. We are at the start of CH usage. I'll take a look at project you mentioned, thank you

[den-crane]

@virtualb0x see #41266 (comment)

[virtualb0x]

@den-crane already saw it and tried to. But still no luck :(

[den-crane]

@den-crane already saw it and tried to. But still no luck :(

try unset LD_LIBRARY_PATH then try isql. If isql does not work without LD_LIBRARY_PATH then this is the reason. Clickhouse forbids using LD_LIBRARY_PATH & LD_PRELOAD.

[virtualb0x]

@den-crane It did not make sense:

test-01 bin]$ unset LD_LIBRARY_PATH
test-01 bin]$ echo $LD_LIBRARY_PATH

$ isql -v  edw_prod
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT * FROM  DEV_ODW.TABLE_OF_ONE_FIELD
+-------------+
| NUMBER_FIELD|
+-------------+
| 1           |
| 1           |
| 1           |

I've also checked connction with teradata tool:
/opt/teradata/client/15.10/bin/tdxodbc64

est-01 bin]$ ./tdxodbc64

Enter Data Source Name: edw_prod
Enter UserID:
Enter Password:

Connecting with SQLConnect(DSN=edw_prod,UID=,PWD=*)...

.....ODBC connection successful.

ODBC version        = -03.52.0000-
DBMS name           = -Teradata-
DBMS version        = -16.20.3249  16.20.32.49-
Driver name         = -tdata.so-
Driver version      = -15.10.01.07-
Driver ODBC version = -03.51-

(type quit to terminate adhoc)
Enter SQL string : SELECT * FROM  DEV_ODW.TABLE_OF_ONE_FIELD

Executing SQLExecDirect("SELECT * FROM  DEV_ODW.TABLE_OF_ONE_FIELD")...

SQL Statement [1]: 33 rows affected.

NUMBER_FIELD
1
1
1

So I suppose the server is ok with its connection to teradata. The problem is only if I am trying to do it through bridge

[den-crane]

@virtualb0x share:

env|grep -E '(tera|tdx)'

cat /proc/`pidof clickhouse-server`/environ

[virtualb0x]

@den-crane
test-01 ~]$ env|grep -E '(tera|tdx)' TTU_PATH=/opt/teradata/client/15.10 TD_HOME=/opt/teradata/client/15.10/odbc_64 PATH=/home/myhomelocation/.local/bin:/home/myhomelocation/bin:/opt/teradata/client/15.10/bin:/usr/share/Modules/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/usr/sbin/cagfad

test-01 ~]$ sudo cat /proc/`pidof clickhouse-server`/environ
LANG=ru_RU.UTF-8PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/binNOTIFY_SOCKET=/run/systemd/notifyHOME=/nonexistentLOGNAME=clickhouseUSER=clickhouseINVOCATION_ID=7b448afb527946749c7cec1cbf4b3812JOURNAL_STREAM=8:32652944RUNTIME_DIRECTORY=/run/clickhouse-serverCLICKHOUSE_WATCHDOG_NO_FORWARD=1ORACLE_SID=asdbORACLE_HOME=/usr/lib/oracle/12.1/client64LD_LIBRARY_PATH=

[den-crane]

@virtualb0x it looks like environments are different for a user and clickhouse-service.
You need to deal with it somehow and pass teradata environment to services/daemons. You can use /etc/environment or /etc/default/clickhouse

For example

cat /etc/default/clickhouse

TTU_PATH=/opt/teradata/client/15.10
TD_HOME=/opt/teradata/client/15.10/odbc_64
...

[virtualb0x]

@den-crane Thank you

TTU_PATH=/opt/teradata/client/15.10
TD_HOME=/opt/teradata/client/15.10/odbc_64
ODBCINI=/etc/odbc.ini

in /etc/default/clickhouse and restarting of server did the trick.

It works now