Skip to main content
Skip to main content
Edit this page

ODBC

Not supported in ClickHouse Cloud

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​

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.
  • The external_table_functions_use_nulls setting defines how to handle Nullable columns. Default value: 1. If 0, the table function does not make Nullable columns and inserts default values instead of nulls. This is also applicable for NULL values inside arrays.

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.

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

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

Table in MySQL:

Table in ClickHouse, retrieving data from the MySQL table:

See Also​