ODBC连接从Clickhouse到MSSQL数据库

Question

请帮助从ClickHouse到SQL Server数据库的ODBC连接。
我在Clickhouse服务器上配置了 ODBC 。
来自 isql ， tsql 等客户的联系是成功的。
但是不可能连接从clickhouse客户端的。

Operation system – Ubuntu 20.04
Clickhouse Server – version 22.1.3
Clickhouse Client – version 18.16.1 
MS SQL Server 2016 on Windows Server.

1. /etc/freetds/freetds.conf

    [TSQL_NE]
    host = 10.72.82.72
    port = 1433
    tds version = 7.4
    client charset = UTF-8

2. /etc/odbcinst.ini/etc/odbc.ini

[FreeTDS]
Description=FreeTDS
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage=1
UsageCount=8

3. MSSQL

[TSQL_NE]
Description=FreeTDS NE
Driver=FreeTDS
Server=10.72.82.72
Database=ASU
UID=user
PWD=password
Port=1433

4. 通过odbc连接到MSSQL数据库

root@srv:/# isql -v TSQL_NE "user" "password"

    +---------------------------------------+
    | Connected! |
    | |
    | sql-statement |
    | help [tablename] |
    | quit |
    | |
    +---------------------------------------+

SQL> SELECT top 10 v1 from asu.dbo.data

    +-------------------------+
    | V1                      |
    +-------------------------+
    | 1.45                    |
    | 1.5062500000000001      |
    | 1.385                   |
    | 1.4237500000000001      |
    | 1.3712500000000001      |
    | 1.425                   |
    | 1.39625                 |
    | 1.6487499999999999      |
    | 1.28                    |
    | 1.2037500000000001      |
    +-------------------------+
    SQLRowCount returns 10
    10 rows fetched

root@srv:/# tsql -v -S TSQL_NE -U user –P password

    locale is "C.UTF-8"
    locale charset is "UTF-8"
    using default charset "UTF-8"
    1> SELECT top 10 v1 from asu.dbo.data
    …
    10 rows fetched

5. ，并使用ClickHouse-CLIENT和CLIENT和CLIENT CLENCTION与 数据库进行检查错误

root@srv:~# clickhouse-client

    ClickHouse client version 18.16.1.
    Password for user :
    Connecting to localhost:9000.
    Connected to ClickHouse server version 22.2.2 revision 54455.

b7e1d742cbd0 :) SELECT top 10 v1 from odbc('DSN=TSQL_NE;Uid=user;Pwd=password;', 'asu', 'dbo.data')

0 rows in set. Elapsed: 0.290 sec.

Received exception from server (version 22.2.2):

> Code: 86. DB::Exception: Received from localhost:9000, 127.0.0.1. 
DB::Exception: Received error from remote server /columns_info?connection_string=DSN%3DTSQL_NE%3B%20Uid%3Duser%3BPwd%3Dpassword%3B&table=dbo.data&external_table_functions_use_nulls=true. 
HTTP status code: 500 Internal Server Error, body: Error getting columns from ODBC 
'Code: 36. DB::Exception: Table dbo.data not found. (BAD_ARGUMENTS) (version 22.2.2.1)'

Answer 1

您在clickhouse-client中的命令应该是

SELECT top 10 v1 from odbc('DSN=TSQL_NE;Uid=user;Pwd=password;Database=asu', 'dbo', 'data')

Answer 2

SELECT top 10 v1 from odbc('DSN=TSQL_NE;Uid=user;Pwd=password;', '', 'data')

删除模式和数据库时，您将获得成功。我在这里