On this page

    ODBC

    The ODBC driver is supported for Linux, Mac, and Windows. This topic covers the installation and configuration of the ODBC driver.

    Prerequisites

    Operating SystemRequirements
    Linux64-bit version
    unixODBC
    Mac64-bit version
    ODBC Manager (recommended)
    Windows34-bit or 64-bit versions of Windows 7 and Windows 10

    Downloading and Installing

    See the instructions below based on your operating system:

    Downloading and Installing on Linux

    To download and install the ODBC driver:

    1. Go to the ODBC driver download page. Click Download for Linux to download the driver.

    2. Run the following command to install the Dremio ODBC Driver and automatically create a DSN called Dremio ODBC Driver 64-bit.

      sudo yum localinstall <dremio-odbc-rpm-path>
      

    Next, configure the driver.

    Downloading and Installing on MacOS

    To download and install the ODBC driver:

    1. Go to the ODBC driver download page. Click Download for macOS to download the driver.

    2. Go to the downloaded location and click the DremioODBC-<version>.dmg file.

    3. In the DremioODBC popup, double-click the DremioODBC.pkg .

    4. In the Welcome to the Dremio ODBC Driver Mac OS X Installer page, click Continue.

    5. In the Software License Agreement page, read and click Continue.

    6. In the next page, click Agree to agree to the terms of the software license agreement.

    7. In the Standard Install on “Macintosh HD” page, if you want to change the install location, click Change Install Location and navigate to the new location. Click Install.

    8. In the Installer is trying to install new software page, enter your MacOS username and password. Click Install Software.

    9. After the installation is complete, the successful installation is displayed in the Summary page. Click Close.

    Next, configure the driver.

    Downloading and Installing on Windows

    note:

    If you want to use a client other than Microsoft Power BI Desktop, follow the additional instructions in the section “Primary Connection Parameters” for setting up communication encrypted by SSL.

    To download and install the ODBC driver:

    1. Go to the ODBC driver download page. Click Download for Windows 64-bit to download the installer.

    2. Run the installer.

    3. (Optional) In the User Account Control page, click Yes. This page appears only if there is user account control configured on your Windows machine.

    4. In the Welcome to Dremio page, click Next.

    5. In the License Agreement page, read and click I Agree.

    6. In the Choose Components page, follow either or both of these steps:

      • Select Tableau Extension if you plan to connect to Dremio Cloud from Tableau.

      • Select Power BI extension if you plan to connect to Dremio Cloud from Power BI Gateway (all supported versions) or from Power BI Desktop (version July 2021 or earlier).

        If you select this option, ensure that the C:\Users\{Your Username}\Documents\Power BI Desktop\Custom Connectors folder does not already contain a Dremio .mez file.

    7. Click Install.

    8. In the Installation Complete page, click Next.

    9. In the Completing Dremio Connector (x64) Setup page, click Finish.

    Next, configure the driver.

    Configuring the ODBC Driver

    Before you configure the driver:

    • Copy the ID of the project that you want to connect to. See Obtaining the ID of a Project for the steps. After you obtain it, save it somewhere that you can retrieve it from during the procedure.

    • Obtain a personal access token from Dremio Cloud. See Creating a Token for the steps to obtain one. After you create a token, save it somewhere that you can retrieve it from during the procedure.

    See the instructions for your operating system:

    Configuring on Linux

    Run the following command to install the Dremio ODBC Driver and automatically create a DSN called Dremio ODBC Driver 64-bit.

    sudo yum localinstall <dremio-odbc-rpm-path>
    

    To configure the properties in the odbc.ini file:

    note:

    For more information, see primary connection parameters.

    1. For ConnectionType, enter Direct.
    2. For HOST, enter sql.dremio.cloud.
    3. For PORT, enter 443.
    4. Only Plain authentication type is supported for Dremio Cloud. For Authentication Type, enter Plain.
    5. For UID, enter $token.
    6. For PWD, enter the personal access token. See Personal Access Tokens for information about generating a token.
    7. On the DSN, click Add to add a key-value pair.
    8. For Keyword, enter Project ID.
    9. For Value, enter your <projectID>. To obtain the project ID, log into Dremio Cloud, switch to the project that you want the ID for, click the settings icon, select Project Settings, and then copy the content of the Project field.
    10. For SSL, enter 1. For other parameters, which are optional, see SSL Connection Parameters.

    Configuring on MacOS

    To configure the system DSN, perform the following steps:

    1. Launch the ODBC Manager and click System DSN.

    2. Under System DSN, click Dremio Connector, and then click Configure.

    3. Change the existing property values if required in the Data Source Name.

    note:

    For more information, see primary connection parameters.

    1. For HOST, enter sql.dremio.cloud.
    2. For PORT, enter 443.
    3. Only Plain authentication type is supported for Dremio Cloud. For Authentication Type, enter Plain.
    4. For UID, enter $token.
    5. For PWD, enter the personal access token. See Personal Access Tokens for information about generating a token.
    6. On the DSN, click Add to add a key-value pair.
    7. For Keyword, enter Project ID.
    8. For Value, your <projectID>. To obtain the project ID, log into Dremio Cloud, switch to the project that you want the ID for, click the settings icon, select Project Settings, and then copy the content of the Project field.
    9. For SSL, enter 1. For other parameters, which are optional, see SSL Connection Parameters.
    10. Click OK.

    Configuring on Windows

    To configure the system DSN, perform the following steps:

    note:

    Do not follow these steps if you are using Microsoft Power BI Desktop to connect to Dremio Cloud.

    1. Go to Start Menu > Window Administrative Tools. Click ODBC Data Sources (64-bit).

    2. In the ODBC Data Source Administrator (64-bit) dialog, click System DSN.

    3. Click Dremio Connector and click Configure.

    note:

    For more information, see primary connection parameters.

    1. In the Dremio Connector DSN Setup, for Direct to Server, enter sql.dremio.cloud and 443 (port).

    2. Only Plain authentication type is supported for Dremio Cloud. Under Authentication, for Authentication Type, select Plain.

    3. For User, enter $token.

    4. For Password, enter the personal access token. See Personal Access Tokens for information about generating a token.

    5. In Advanced Properties, enter project_id=<projectID>; at the end of the properties list. To obtain the project ID, log into Dremio Cloud, switch to the project that you want the ID for, click the settings icon, select Project Settings, and then copy the content of the Project field.

    6. Click SSL Options.

    7. On the SSL Options dialog, check the Enable SSL box. For other parameters, which are optional, see SSL Connection Parameters.

    8. Click OK.

    9. Click OK to close the Dremio Connector DSN Setup.

    10. Click OK to close the ODBC Data Source Administrator (64-bit).

    Primary Connection Parameters

    If you are setting up encrypted communication between your ODBC client applications and the Dremio server, use the following connection parameters to enable a connection to Dremio.

    note:

    The Dremio ODBC driver does not support password-protected .pem/.crt files or multiple .crt certificates in a single .pem/.crt file.

    Use these parameters to configure basic connection details such as what data source to connect with.

    NameTypeDescriptionDefault Value
    AuthenticationTypestringControls how the driver authenticates connections to Dremio. Accepted values include:
    • No Authentication: Not applicable to Dremio Cloud.
    • Username: Not applicable to Dremio Cloud
    • Plain or Basic Authentication: The driver authenticates the connection using a username and password. Values for these must be set using the UID and PWD parameters.
    No Authentication
    ConnectionTypestringControls whether the driver should connect to a single Dremio server. Accepted values is:
    • Direct: The driver connects to a single Dremio server.
    Direct
    HoststringSets the IP address or hostname for the Dremio server.

    Note:
    A value is required if the ConnectionType parameter is set to Direct.
    sql.dremio.cloud
    LogLevelstringHandles logging behaviors in the driver and specifies the amount of detail to include in log files.

    Note:
    Dremio Cloud recommends enabling logging only when capturing an issue. Logging can dramatically impact server performance and consume large quantities of disk space. The settings for logging will apply to any connection using the Dremio ODBC driver, so always remember to disable this feature once you are done using it.
    The following are accepted log levels:
    • OFF (0): Disables all logging.
    • FATAL (1): Logs severe error events that cause the driver to abort.
    • ERROR (2): Logs error events that may not prevent the driver from continuing to run.
    • WARNING (3): Logs events that may result in an error if action is not taken.
    • INFO (4): Logs general information describing the progress of the driver.
    • DEBUG (5): Logs detailed information useful when debugging the driver.
    • TRACE (6): Logs all driver activity.
    When logging has been enabled, the driver will produce a log file at the location specified in the LogPath parameter.
    OFF (0)
    LogPathstringSets the full path to the folder where the ODBC driver should save log files when logging has been enabled.None
    LogFileSizeintegerRestricts the maximum size of each log file, as measured in bytes. Once the maximum file size is reached, the driver then creates a new file and continues logging.20971520
    PortintegerSets the TCP port number the Dremio Cloud uses to listen to client connections. The port to connect to Dremio cloud is 443.31010
    PWDstringSets the password that corresponds with the username provided in the UID parameter.

    Note:
    A value is required here when the ConnectionType parameter is set to Direct.
    None
    SchemastringProvides the name of the database schema to use by default when a schema is not specified in a query. However, this does not prevent queries from being issued for other schemas. Such queries must explicitly include the schema.None
    UIDstringSets the user name when accessing the Dremio Cloud.

    Note:
    This property is required when the ConnectionType parameter is set to Direct.
    None

    SSL Connection Parameters

    Use the following parameters to configure SSL encryption and verification methods for regular connections.

    NameTypeDescriptionDefault ValueRequired
    DisableCertificateValidationbooleanControls whether the driver verifies the host certificate against the trust store. Accepted values include:
    • 0: The driver will verify the certificate against the trust store.
    • 1: The driver will not verify the certificate against the trust store.
    0No
    DisableCertificateVerificationbooleanSpecifies whether the driver should verify the host certificate against the trust store. Accepted values are:
    • 0: The driver verifies the certificate against the trust store.
    • 1: The driver will not verify the certificate against the trust store.
    0No
    DisableHostVerificationbooleanForces the driver to verify that the host in the certificate is the host being connected to. Accepted values are:
    • 0: The driver will verify the certificate against the host being connected to.
    • 1: The driver will not verify the certificate against the host.
    0No
    SSLbooleanForces the client to use an SSL encrypted connection to communicate with the Dremio Cloud. Accepted values include:
    • 0: SSL encryption is disabled with the client.
    • 1: The client communicates with the Dremio Cloud only using SSL encryption.
    Note:
    To connect to Dremio Cloud, SSL must be enabled. SSL configuration occurs independently from authentication. If AuthenticationType and SSL are both enabled, the driver will execute the specified authentication method using an SSL connection.
    0Yes
    TLSProtocolstringIndicates the TLS protocol version to use for connections. Accepted values are:
    • tlsv1
    • tlsv11
    • tlsv12
    tlsv12No
    TrustedCertsstringThe full path of the .pem file containing certificates trusted by a CA, for the purpose of verifying the server. If this option is not set, then the driver defaults to using the trusted CA certificates .pem file installed by the driver.

    Note:
    The exact file path varies depending on the version of the driver that is installed. The path for the Windows driver is different from the path set for the Mac OS driver.
    The cacerts.pem file in the \lib subfolder within the driver’s installation directory.No
    UseSystemTrustStorebooleanControls whether to use a CA certificate from the system’s trust store, or from a specified .pem file.
    • 1: The driver verifies the connection using a certificate in the system trust store.
    • 0: The driver verifies the connection using the .pem file specified by the TrustedCerts parameter.
    0No

    Advanced Parameters

    The following table describes the advanced parameters you may use when configuring the Dremio ODBC driver.

    note:

    When defining multiple parameters, separate each parameter using a semicolon (;).

    NameTypeDescriptionDefault Value
    HandshakeTimeoutintegerSpecifies an integer value, which represents the number of seconds the driver must wait before ending an attempt to connect with a data source.

    Note:
    If this property is set to 0, the driver will make connection attempts indefinitely.
    5
    HeartBeatFreqSecintegerSets the total number of seconds of inactivity allowed before the Dremio client sends a heartbeat to the server to check on the status of a connection.

    Note:
    If this property is set to 0, then the Dremio client will not send any heartbeats.
    15
    NonZeroMaxNameMaxLenbooleanRestricts the maximum length of schema, catalog, table, and column names. The Dremio ODBC Driver does not automatically enforce a maximum length for these names. By default, the driver reports these lengths as 0, indicating that there is no maximum length. Accepted values include:
    • true: The driver reports an appropriate non-zero value for the maximum length of schema, catalog, table, and column names.
    • false: The driver reports 0 as the maximum length of schema, catalog, table, and column names.
    Note:
    Some BI tools interpret the length of 0 literally. To ensure that names display correctly in your BI tool, properly set the NonZeroNameMaxLen parameter.
    false
    NumberOfPrefetchBuffersintegerControls the size of the record batch queue for the driver.

    Note When this property is set to a value less than 1, the value will default to 1.
    5
    QueryTimeoutintegerControls the number of seconds that the driver will wait before halting a query.

    Note:
    If this property is set to 0, then queries will not be stopped by the driver.
    180
    StringColumnLengthintegerSets the maximum column length that the driver reports for string columns. This property supports values between 1 and 2,147,483,647, inclusively.65536 when connecting to Apache Drill server 1.11.0 or earlier.
    The value specified by the server when connecting to Apache Drill server 1.12.0+.
    TimestampTZDisplayTimeZonestringSpecifies the timestamp to be used by the Dremio server. Accepted values include:
    • local: Timestamps use the local time zone associated with the user.
    • utc: Timestamps appear in Coordinated Universal Time (UTC).
      local