odbc

Returns table that is connected via ODBC.

  1. odbc(connection_settings, external_database, external_table)

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.

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.

The fields with the NULL values from the external table are converted into the default values for the base data type. For example, if a remote MySQL table field has the INT NULL type it is converted to 0 (the default value for ClickHouse Int32 data type).

Usage example

Getting 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
  2. mysql> CREATE USER 'clickhouse'@'localhost' IDENTIFIED BY 'clickhouse';
  3. 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)

Retrieving data from the MySQL table in ClickHouse:

  1. SELECT * FROM odbc('DSN=mysqlconn', 'test', 'test')
  1. ┌─int_id─┬─int_nullable─┬─float─┬─float_nullable─┐
  2. │      1 │            0 │     2 │              0 │
  3. └────────┴──────────────┴───────┴────────────────┘

See Also

Original article