ClickHouse ODBC Driver Installation for Windows

Installing and configuring the ClickHouse ODBC Driver for Windows

The ClickHouse ODBC Driver for Microsoft Windows allows users to connect different applications to a ClickHouse database. There are two versions available: 64-Bit and 32-Bit based on which version of Windows is being used, and the requirements of the applications connecting to ClickHouse.

Prerequisites

Installation Steps

To install the ClickHouse ODBC Driver for Microsoft Windows:

  1. With a browser, navigate to the clickhouse-odbc releases page.

  2. Select the most recent release and select one of the following ClickHouse ODBC installer for Windows, replacing {version} with the version that will be downloaded:

    ODBC Install Releases
    1. For 32-bit versions of Windows: clickhouse-odbc-{version}-win32.msi
    2. For 64-bit versions of Windows: clickhouse-odbc-{version}-win64.msi
  3. Launch the downloaded ClickHouse ODBC installer.

    1. Note: There may be a warning from Microsoft Windows Defender that the installer is an unrecognized application. If this occurs, select More Info, then select Run Anyway.
    ODBC Install Warning
  4. Follow the ODBC installation process as detailed by the application. The default installation are typically sufficient, but refer to the clickhouse-odbc guide for full details.

  5. Once finished, the ClickHouse ODBC Driver will be installed.

Verifying the ClickHouse ODBC Driver Installation

To verify the ClickHouse ODBC Driver has been installed:

  1. Launch the Windows 10 application ODBC Data Source Administrator - there are two versions: 32 bit and 64 bit. Select the version that matches your operating system.

  2. Select the System DSN tab. Under a standard ClickHouse ODBC installation, both the ClickHouse DSN (ANSI) and the ClickHouse DSN (Unicode) will be available.

    ODBC Data Source Verification

Example Connecting to ClickHouse with ODBC

Once the ClickHouse ODBC driver has been installed, connections can be made to specific ClickHouse servers via the Data Source Name(DSN). Two connection types are recommended:

  • User DSN: These are ODBC connections that are available for the Windows 10 user.
  • System DSN: These are ODBC connections available to all users of the Windows 10 operating system.

The following example demonstrates how to create a User DSN connection to a ClickHouse server.

  1. Launch the Windows 10 application ODBC Data Source Administrator - there are two versions: 32 bit and 64 bit. Select the version that matches your operating system and the applications that will be connecting to ClickHouse.

    1. For example: If running the 64 bit version of Windows 10, but the application is 32 bit, then select the 32 bit version of the ODBC driver.
  2. Select the User DSN tab.

  3. Select Add.

  4. Select ClickHouse ODBC Driver (Unicode), then select Finish.

  5. There are two methods of setting up the DSN connection: URL or Host Name. To set up the connection via URL:

    1. Name: The name you set for your connection.

    2. Description (Optional): A short description of the ODBC connection.

    3. URL: The URL for the ClickHouse server. This will be the HTTP or HTTPS connection based on the ClickHouse HTTP Interface.

      1. This will be in the format:

      {connection type}//{url}:{port}

      For example: https://github.demo.trial.altinity.cloud:8443

  6. To set up the connection via Host Name, provide the following:

    ODBC ClickHouse Connection Settings
    1. Host: The hostname or IP address of the ClickHouse server.
    2. Port: The port to be used. This will be either the HTTP port, default 8123, or the HTTPS port default 8443.
    3. Database (Optional): The name of the database on the ClickHouse server.
    4. SSLMode (Optional)
      1. Set to require if SSL will be used and fail if it can not be verified.
      2. Set to allow if SSL will be used with self-signed certificates.
    5. User (Optional): Set to provide a specific username when connecting, leave blank to be prompted.
    6. Password (Optional): Set to provide a specific password when connecting, leave blank to be prompted.
    7. Timeout (Optional): Set a timeout period before giving up on the connection.

Test Connection

One method of testing the connection to ClickHouse through the ODBC driver is with Powershell. This script will make an ODBC connection to the specified database, then show all tables available to the authenticating ClickHouse user.

  1. Launch Powershell.
    1. If using the 64 bit version of the ClickHouse ODBC Driver, then select Windows Powershell ISE.
    2. If using the 32 bit version of the ClickHouse ODBC Driver, select Windows Powershell ISE (x86).
  2. Paste the following script, replacing the following:
    1. DSN: The DSN of your ClickHouse ODBC Connection.
    2. Uid: The ClickHouse user being used.
    3. Pwd: The password of the ClickHouse user being used.
  3. Run the script.
ODBC Powershell Test
$connectstring = "DSN=ClickHouseDemo;Uid=demo;Pwd=demo;"
$sql = @'
show tables;
'@
$connection = New-Object System.Data.Odbc.OdbcConnection($connectstring)
$connection.open()
$command = New-Object system.Data.Odbc.OdbcCommand($sql,$connection)
$data = New-Object system.Data.Odbc.OdbcDataAdapter($command)
$datatable = New-Object system.Data.datatable
$null = $data.fill($datatable)
$conn.close()
$datatable