ODBC

Allows ClickHouse to connect to external databases via ODBC.

To safely implement ODBC connections, ClickHouse uses a separate program clickhouse-odbc-bridge. 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.

Creating a Table

  1. CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
  2. (
  3.     name1 [type1],
  4.     name2 [type2],
  5.     ...
  6. )
  7. ENGINE = ODBC(connection_settings, external_database, external_table)

See a detailed description of the CREATE TABLE query.

The table structure can differ from the source table structure:

  • 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.

Usage Example

Retrieving data from the local MySQL installation via ODBC

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

Ensure that unixODBC and MySQL Connector are installed.

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

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

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

  1. $ cat /etc/odbc.ini
  2. [mysqlconn]
  3. DRIVER = /usr/local/lib/libmyodbc5w.so
  4. SERVER = 127.0.0.1
  5. PORT = 3306
  6. DATABASE = test
  7. USERNAME = clickhouse
  8. PASSWORD = clickhouse

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

  1. $ isql -v mysqlconn
  2. +-------------------------+
  3. | Connected!                            |
  4. |                                       |
  5. ...

Table in MySQL:

  1. mysql> CREATE TABLE `test`.`test` (
  2.     ->   `int_id` INT NOT NULL AUTO_INCREMENT,
  3.     ->   `int_nullable` INT NULL DEFAULT NULL,
  4.     ->   `float` FLOAT NOT NULL,
  5.     ->   `float_nullable` FLOAT NULL DEFAULT NULL,
  6.     ->   PRIMARY KEY (`int_id`));
  7. Query OK, 0 rows affected (0,09 sec)
  9. mysql> insert into test (`int_id`, `float`) VALUES (1,2);
  10. Query OK, 1 row affected (0,00 sec)
  12. mysql> select * from test;
  13. +------+----------+-----+----------+
  14. | int_id | int_nullable | float | float_nullable |
  15. +------+----------+-----+----------+
  16. |      1 |         NULL |     2 |           NULL |
  17. +------+----------+-----+----------+
  18. 1 row in set (0,00 sec)

Table in ClickHouse, retrieving data from the MySQL table:

  1. CREATE TABLE odbc_t
  2. (
  3.     `int_id` Int32,
  4.     `float_nullable` Nullable(Float32)
  5. )
  6. ENGINE = ODBC('DSN=mysqlconn', 'test', 'test')
  1. SELECT * FROM odbc_t
  1. ┌─int_id─┬─float_nullable─┐
  2. │      1 │           ᴺᵁᴸᴸ │
  3. └────────┴────────────────┘

See Also

Original article