Configuring the ODBC Driver

You can change the connection settings from the iODBC Administrator64 that is part of the iODBC Driver Manager or by editing the odbc.ini file. These are the two ways that you can configure the ODBC driver:

After configuring, you can test the configuration by following the steps in Testing the Driver.

Note

In JDBC and ODBC driver configurations, https://api.qubole.com is the default endpoint.

Prerequisites

These are the prerequisites:

  • Ensure that you have the access to the object/resource that you refer to in the driver configuration. If you see an access denied error, check with the Qubole account administrator. For more information, see Resources, Actions, and What they Mean.

Configuring the Driver through the iODBC Manager

Ensure that the iODBC Driver Manager is installed on the Mac as described in Installing the ODBC Driver on Mac.

Perform these steps to configure the ODBC driver:

  1. Open iODBC Administrator64 with Administrator Privileges. (sudo /Applications/iODBC/iODBC\ Administrator64.app/Contents/MacOS/iODBC\ Administrator64)

  2. Go to System DSN and select Qubole ODBC Driver DSN and the dialog is illustrated here.

    ../../../_images/configureODBCMac3.png
  3. Click Configure.

  4. Enter the valid API Token. Managing Your Accounts provides more information on the API tokens.

  5. Change the DSI as required. The supported values are: Hive, Presto, SQLCommand (for Quantum), or Spark. The cluster label is not mandatory for Quantum.

  6. If the DSI is Spark, enter APPID.

  7. Add SCHEMA_NAME as a keyword and enter the schema name as its value, to filter to that schema while fetching metadata so that only the related TABL_SCHEM configured is exposed.

  8. Add CATALOG_NAME as a keyword and enter the catalog’s name as its value.

  9. USE_S3 is enabled/set to true by default. It is set to bypass QDS Control Plane for getting results directly from the S3 location. You can enter false later to disable it or remove the keyword.

  10. Add BUCKET_REGION as a keyword and enter the AWS region which you use as the cloud object storage for final results (default storage location (defloc)). If you do not specify the AWS region, then it defaults to us-east-1 AWS region.

Caution

STREAM_RESULTS mentioned below is part of the ODBC Driver beta versions 2.0.0 and 2.1.0. The beta versions can cause data loss in scenarios where results data has NULL characters (0x00) and are therefore not recommended for production usage. For more information, see Launch Stage and Availability of Features.

  1. STREAM_RESULTS: Set it to true to enable Presto FastStreaming. It enables streaming of results directly from AWS S3 in the ODBC driver. This is in contrast to the earlier behavior wherein the driver waits for the query to finish before downloading any results from the QDS Control Plane or from S3. The streaming behavior can help the BI tool performance as results are displayed as soon as they are available in S3. Presto FastStreaming for ODBC driver is supported in Presto versions 0.193 and 0.208. As streaming cannot be used with Presto Smart Query Retry, the Presto FastStreaming feature automatically disables Presto Smart Query Retry.

    Note

    Create a ticket with Qubole Support to enable the Presto FastStreaming feature on the account.

  2. VIRTUAL_HOST_STYLE: It is enabled by default. Set it to false to enable path-style URLs that allow a dot in the (.) S3 bucket name of the default storage location (defloc). It is only supported from ODBC Driver version 2.2.1.

    The Configure dialog box is as illustrated here with the virtual-host-style URLs disabled and path-style URLs enabled for S3 buckets.

    ../../../_images/AdvancedConfigMac1.png

    Note

    For more information on advanced configuration such as MAX_CHAR_SIZE, see Setting Additional Configuration.

  3. Click OK.

Configuring the Driver through the ODBC.ini File

The Qubole ODBC driver comes with a DSN created that you can see in the the odbc.ini file. After you install the driver, you can trace the odbc.ini file in the Library/ODBC/ location. You can add another DSN or modify the DSN details.

For the Qubole ODBC driver, these are the defaults in the odbc.ini file.

Caution

STREAM_RESULTS mentioned below is part of the ODBC Driver beta versions 2.0.0 and 2.1.0. The beta versions can cause data loss in scenarios where results data has NULL characters (0x00) and are therefore not recommended for production usage. For more information, see Launch Stage and Availability of Features.

[ODBC]
# Specify any global ODBC configuration here such as ODBC tracing.

[ODBC Data Sources]
Qubole ODBC Driver DSN=QuboleODBC

[Qubole ODBC Driver DSN]
# This key is not necessary and is only to give a description of the data source.
Description=Qubole ODBC Driver DSN

# Driver: The location where the ODBC driver is installed to.
Driver=/Library/qubole/odbc/lib/universal/libquboleodbc.dylib
# APITOKEN: The API Token for the account to be used, as provided by Qubole.
APITOKEN=""
# CLUSTER_LABEL: The label of the cluster on which queries are to be run
CLUSTER_LABEL=default
# ENDPOINT: The endpoint for the account to be used, as provided by Qubole.
ENDPOINT=https://api.qubole.com
#DSI: The data source name : Hive, Spark, Presto, or SQLCommand (for Quantum). The cluster label is not mandatory for Quantum.
DSI=hive
APPID=""
# Catalog name
CATALOG_NAME=hive
#S3 Bucket region
BUCKET_REGION=us-west-1
#Bypass QDS Control Plane
USE_S3=true
#Enable Presto FastStreaming
STREAM_RESULTS=true
#Enable Path-style URLS for S3 buckets
VIRTUAL_HOST_STYLE=false

In the odbc.ini file, you can edit any entry’s value. You can change these parameters as per the requirements:

  • APITOKEN: Enter The API token of the QDS account. Managing Your Accounts provides more information on the API tokens.

  • CLUSTER_LABEL: The cluster label is default, which implies that it is default cluster of the account. You can change it by adding the cluster’s label that you want to use.

  • ENDPOINT: It is the QDS environment. By default, it is https://api.qubole.com. To change it to a different QDS environment on which you have the QDS account, add that QDS environment as the endpoint. For more information on the endpoints, see Supported Qubole Endpoints on Different Cloud Providers.

  • DSI: It is the Data Source Name. By default, it is Hive. You can change it to Presto, SQLCommand (for Quantum) or Spark as required. The cluster label is not mandatory for Quantum.

  • APPID: When you set the Data Source Name as Spark, enter the APP ID. It is not applicable to the Presto or Hive data source.

  • CATALOG_NAME: Add this property and enter the catalog’s name as its value.

  • USE_S3: This property is enabled/set to true by default. It is set to bypass QDS Control Plane for getting results directly from the S3 location. You can enter false later to disable it or remove the keyword.

  • BUCKET_REGION: Add this property and enter the AWS region which you use as the cloud object storage for final results (default storage location (defloc)). If you do not specify the AWS region, then it defaults to us-east-1 AWS region.

    Caution

    STREAM_RESULTS mentioned below is part of the ODBC Driver beta versions 2.0.0 and 2.1.0. The beta versions can cause data loss in scenarios where results data has NULL characters (0x00) and are therefore not recommended for production usage. For more information, see Launch Stage and Availability of Features.

  • STREAM_RESULTS: Set it to true to enable Presto FastStreaming. It enables streaming of results directly from AWS S3 in the ODBC driver. This is in contrast to the earlier behavior wherein the driver waits for the query to finish before downloading any results from the QDS Control Plane or from S3. The streaming behavior can help the BI tool performance as results are displayed as soon as they are available in S3. Presto FastStreaming for ODBC driver is supported in Presto versions 0.193 and 0.208. As streaming cannot be used with Presto Smart Query Retry, the Presto FastStreaming feature automatically disables Presto Smart Query Retry.

    Note

    Create a ticket with Qubole Support to enable the Presto FastStreaming feature on the account.

  • VIRTUAL_HOST_STYLE: It is enabled by default. Set it to false to enable path-style URLs that allow a dot in the (.) S3 bucket name of the default storage location (defloc). It is only supported from ODBC Driver version 2.2.1.

Testing the Driver

To run a simple test:

  1. Run the command: '/Library/Application Support/iODBC/bin/iodbctest'

    (Make sure you include the single quotes, or the space between Application and Support will cause the command to fail.)

  2. In the command-line utility, enter ? to see a list of available data sources.

  3. At the prompt, enter dsn=Qubole ODBC Driver DSN

  4. At the SQL> prompt, enter the SHOW TABLES command.

To test the driver with Microsoft Excel:

  1. Open a new Excel Worksheet.
  2. In Data tab, choose Select New Database Query from Database option.
  3. Go to the System DSN tab and choose the appropriate driver, then click OK.
  4. Leave the username and password blank if prompted, and click OK.