ODBC

To safely implement ODBC connections, ClickHouse uses a separate program . If the ODBC driver is loaded directly from clickhouse-server, driver problems can crash the ClickHouse server. ClickHouse automatically starts clickhouse-odbc-bridge when it is required. The ODBC bridge program is installed from the same package as the clickhouse-server.

This engine supports the Nullable data type.

See a detailed description of the query.

• Column names should be the same as in the source table, but you can use just some of these columns and in any order.
• Column types may differ from those in the source table. ClickHouse tries to cast values to the ClickHouse data types.

Engine Parameters

• connection_settings — Name of the section with connection settings in the odbc.ini file.
• external_database — Name of a database in an external DBMS.
• external_table — Name of a table in the external_database.

Retrieving data from the local MySQL installation via ODBC

This example is checked for Ubuntu Linux 18.04 and MySQL server 5.7.

By default (if installed from packages), ClickHouse starts as user clickhouse. Thus, you need to create and configure this user in the MySQL server.

$ sudo mysql

mysql> CREATE USER 'clickhouse'@'localhost' IDENTIFIED BY 'clickhouse';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'clickhouse'@'clickhouse' WITH GRANT OPTION;

Then configure the connection in /etc/odbc.ini.

You can check the connection using the utility from the unixODBC installation.

$ isql -v mysqlconn
| Connected!                            |
|                                       |
...

mysql> CREATE TABLE `test`.`test` (
    ->   `int_id` INT NOT NULL AUTO_INCREMENT,
    ->   `int_nullable` INT NULL DEFAULT NULL,
    ->   `float` FLOAT NOT NULL,
    ->   `float_nullable` FLOAT NULL DEFAULT NULL,
    ->   PRIMARY KEY (`int_id`));
Query OK, 0 rows affected (0,09 sec)
mysql> select * from test;
+--------+--------------+-------+----------------+
| int_id | int_nullable | float | float_nullable |
+--------+--------------+-------+----------------+
|      1 |         NULL |     2 |           NULL |
+--------+--------------+-------+----------------+
1 row in set (0,00 sec)

Table in ClickHouse, retrieving data from the MySQL table:

SELECT * FROM odbc_t

┌─int_id─┬─float_nullable─┐
│      1 │           ᴺᵁᴸᴸ │
└────────┴────────────────┘