# ODBC Data Wrapper Reference

<Info>
  ODBC HeavyConnect and import is currently a beta feature.
</Info>

This topic provides setup and configuration details and information about supported data types when using ODBC HeavyConnect or import. This reference also describes ODBC data wrapper assumptions and supported mappings of HeavyDB column types to ODBC column types, and provides RDMS-specific notes.

## ODBC Driver Installation

To HeavyConnect to or import from an external RDMS using the ODBC data wrapper, make sure that an appropriate ODBC driver is installed for the RDMS you want to access. RDMS vendors websites usually provide ODBC driver download and configuration instructions, and in certain cases, ODBC drivers are managed by third-party vendors that provide these instructions.

### ODBC Configuration Files

Two key configuration files need to be set up when using ODBC HeavyConnect or import: `odbcinst.ini` and `odbc.ini`.

The `odbcinst.ini` file specifies a list of installed drivers, along with required configuration for those drivers (the path to the driver binary). The following example is an `odbcinst.ini` configuration file for an instance with installed drivers for PostGreSQL, Redshift, and Snowflake:

```
[ODBC Drivers]
PostgreSQL=Installed
Redshift=Installed
Snowflake=Installed

[PostgreSQL]
Description=PostgreSQL ODBC driver
Driver=/usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so

[Redshift]
Description=Redshift ODBC driver
Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so

[Snowflake]
Description=Snowflake ODBC Driver
Driver=/usr/lib/snowflake/odbc/lib/libSnowflake.so
```

Use an intuitive name for the drivers, such as the official name of the RDMS, so that users can easily know the driver names to use in connection strings.

The `odbc.ini` file contains configuration for database-specific connections. Each connection configuration is identified by a data source name (DSN). The following example is an `odbc.ini` file with configuration for the default `postgres` database on a local instance of PostgreSQL running on port 5432:

```
[ODBC Data Sources]
default_postgres_db=default_postgres_db

[default_postgres_db]
Description=Local default PostgreSQL database
Driver=PostgreSQL
Database=postgres
Servername=localhost
Port=5432
```

Locate `odbcinst.ini` and `odbc.ini` in the `/etc/` directory, or in the home directory for cases where the server process is started under a specific user account.

<Info>
  The PostgreSQL unicode ODBC driver is currently not supported.
</Info>

### Setting Up ODBC Connectors for Heavy Immerse

In certain cases, an `odbcinst.ini` configuration file that omits the `[ODBC Drivers]` section is automatically generated when ODBC drivers (such as the PostgreSQL driver) are installed. This section of the configuration file is required for the ODBC-related connectors to appear in Heavy Immerse.

In addition, the driver names specified in `odbcinst.ini` must match the RDMS name for connectors to appear in Immerse. Specifically, the driver name should be PostgreSQL for PostgreSQL, Redshift for AWS Redshift, and Snowflake for Snowflake.

### Working with HEAVY.AI Docker Images

For docker deployments of HEAVY.AI, ODBC driver installations can be managed in a Dockerfile that uses the HEAVY.AI Docker image as a base. For example, you can create a docker image with installed PostGreSQL, Redshift, and Snowflake ODBC drivers using a Dockerfile with the following content.

<Info>
  Always consult the license and terms and conditions of the ODBC drivers you install. The examples here are provided for illustrative purposes only.
</Info>

```
FROM heavyai/heavyai-ee-cuda

# Install PostGreSQL ODBC driver.
# The Snowflake ODBC driver depends on unixodbc.
RUN apt-get update && apt-get install -y odbc-postgresql unixodbc

# Install Redshift ODBC driver.
RUN wget https://s3.amazonaws.com/redshift-downloads/drivers/odbc/1.4.52.1000/AmazonRedshiftODBC-64-bit-1.4.52.1000-1.x86_64.deb
RUN dpkg -i ./AmazonRedshiftODBC-64-bit-1.4.52.1000-1.x86_64.deb
RUN rm ./AmazonRedshiftODBC-64-bit-1.4.52.1000-1.x86_64.deb

# Install Snowflake ODBC driver.
RUN wget https://sfc-repo.snowflakecomputing.com/odbc/linux/latest/snowflake-odbc-2.25.2.x86_64.deb
RUN dpkg -i ./snowflake-odbc-2.25.2.x86_64.deb
RUN rm ./snowflake-odbc-2.25.2.x86_64.deb
```

You can then build a new image with installed ODBC drivers using the Dockerfile.

<Info>
  The `odbcinst.ini` and `odbc.ini` configuration files should be bound to `/etc/odbcinst.ini` and `/etc/odbc.ini`, respectively, when running the container.
</Info>

## ODBC Data Wrapper Assumptions

* The remote database management system supports the MIN, MAX, and LENGTH SQL functions. These functions are used internally for optimizations and validations.
* The combination of the query specified in the foreign table’s SQL\_SELECT option and the SQL\_\_\_ORDER\_BY option is guaranteed to return a result set in the same order every time the query is executed.
* Queries for geo columns are always returned as WKT strings. This implies that either geo columns are stored as text columns containing WKT strings, geo columns are cast to text (for example, using a function like ST\_AsText) in the SQL\_SELECT query, or the remote database management system, by default, projects geo columns as WKT strings.

## HeavyDB to ODBC Data Type Mapping

#### Numeric and Boolean Types

| HeavyDB (Down) \ ODBC (Right)                                                                  | SQL\_BIGINT                     | SQL\_INTEGER      | SQL\_SMALLINT     | SQL\_TINYINT      | SQL\_DOUBLE | SQL\_FLOAT | SQL\_REAL | SQL\_DECIMAL                                            | SQL\_NUMERIC                                            | SQL\_BIT |
| ---------------------------------------------------------------------------------------------- | ------------------------------- | ----------------- | ----------------- | ----------------- | ----------- | ---------- | --------- | ------------------------------------------------------- | ------------------------------------------------------- | -------- |
| BIGINT                                                                                         | Yes (unsigned is not coercible) | Yes (if unsigned) | No                | No                | No          | No         | No        | Coercible                                               | No                                                      | No       |
| BIGINT ENCODING FIXED (32) / INTEGER                                                           | Coercible                       | Yes               | Yes (if unsigned) | No                | No          | No         | No        | Coercible                                               | No                                                      | No       |
| BIGINT ENCODING FIXED (16) / INTEGER ENCODING FIXED (16) / SMALLINT                            | Coercible                       | Coercible         | Yes               | Yes (if unsigned) | No          | No         | No        | Coercible                                               | No                                                      | No       |
| BIGINT ENCODING FIXED (8) / INTEGER ENCODING FIXED (8) / SMALLINT ENCODING FIXED (8) / TINYINT | Coercible                       | Coercible         | Coercible         | Yes               | No          | No         | No        | Coercible                                               | No                                                      | No       |
| BOOLEAN                                                                                        | No                              | No                | No                | No                | No          | No         | No        | No                                                      | No                                                      | Yes      |
| DECIMAL (Precision, Scale)                                                                     | No                              | No                | No                | No                | No          | No         | No        | Yes (if precision and scale are within OmniSci’s range) | Yes (if precision and scale are within OmniSci’s range) | No       |
| DOUBLE                                                                                         | No                              | No                | No                | No                | Yes         | Coercible  | No        | No                                                      | No                                                      | No       |
| FLOAT                                                                                          | No                              | No                | No                | No                | Coercible   | Coercible  | Yes       | No                                                      | No                                                      | No       |

\*\[1] PostgreSQL requires setting the option `BoolsAsChar=false` to represent boolean columns as SQL\_BIT.

#### Date and Time Types

| HeavyDB (Down) \ ODBC (Right)  | SQL\_TYPE\_DATE | SQL\_TYPE\_TIMESTAMP | SQL\_TYPE\_TIME | SQL\_TYPE\_UTCDATETIME | SQL\_TYPE\_UTCTIME |
| ------------------------------ | --------------- | -------------------- | --------------- | ---------------------- | ------------------ |
| DATE / DATE ENCODING DAYS (32) | Coercible       | No                   | No              | No                     | No                 |
| DATE ENCODING DAYS (16)        | Coercible       | No                   | No              | No                     | No                 |
| TIME                           | No              | No                   | Yes             | No                     | No                 |
| TIME ENCODING FIXED (32)       | No              | No                   | Yes             | No                     | No                 |
| TIMESTAMP (0)                  | No              | Coercible            | No              | No                     | No                 |
| TIMESTAMP (3)                  | No              | Coercible            | No              | No                     | No                 |
| TIMESTAMP (6)                  | No              | Coercible            | No              | No                     | No                 |
| TIMESTAMP (9)                  | No              | Coercible            | No              | No                     | No                 |
| TIMESTAMP ENCODING FIXED (32)  | No              | Coercible            | No              | No                     | No                 |

#### Geo and String Types

| HeavyDB (Down) \ ODBC (Right) | SQL\_CHAR | SQL\_VARCHAR | SQL\_LONGVARCHAR | SQL\_WCHAR | SQL\_WVARCHAR | SQL\_WLONGVARCHAR |
| ----------------------------- | --------- | ------------ | ---------------- | ---------- | ------------- | ----------------- |
| TEXT ENCODING DICT            | Yes       | Yes          | Yes              | Yes        | Yes           | Yes               |
| TEXT ENCODING (16)            | Yes       | Yes          | Yes              | Yes        | Yes           | Yes               |
| TEXT ENCODING (8)             | Yes       | Yes          | Yes              | Yes        | Yes           | Yes               |
| TEXT ENCODING NONE            | Yes       | Yes          | Yes              | Yes        | Yes           | Yes               |
| LINESTRING                    | No        | Yes          | Yes              | No         | No            | No                |
| MULTILINESTRING               | No        | Yes          | Yes              | No         | No            | No                |
| MULTIPOLYGON                  | No        | Yes          | Yes              | No         | No            | No                |
| POINT                         | No        | Yes          | Yes              | No         | No            | No                |
| MULTIPOINT                    | No        | Yes          | Yes              | No         | No            | No                |
| POLYGON                       | No        | Yes          | Yes              | No         | No            | No                |

#### Array Type

Array data types currently are not supported.

See [https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/sql-data-types](https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/sql-data-types) for more details about ODBC data types, and consult the specific RDMS ODBC driver documentation for  details about how the RDMS data types map to the ODBC data types.

### RDMS-Specific Notes

#### PostgreSQL

* The official PostgreSQL ODBC driver can be downloaded from [https://odbc.postgresql.org/](https://odbc.postgresql.org/)
* Accessing BOOLEAN data in PostgreSQL requires the `BoolsAsChar=false` option to be added to the connection string or DSN configuration.

#### Google BigQuery

* The official [BigQuery ODBC data connector installation and configuration guide](https://storage.googleapis.com/simba-bq-release/odbc/Simba%20Google%20BigQuery%20ODBC%20Connector%20Install%20and%20Configuration%20Guide-2.5.0.1001.pdf) can be downloaded from [https://cloud.google.com/bigquery/docs/reference/odbc-jdbc-drivers](https://cloud.google.com/bigquery/docs/reference/odbc-jdbc-drivers).
* The BigQuery ODBC connector uses the OAuth 2.0 protocol for authentication; steps are provided by the official installation guide.
  * In all authentication methods, the Principal member (e.g. Service Account, User Account) must have at least a `roles/bigquery.dataViewer` permission grant on the GCP project.
  * Using a **Service Account** is the recommended method for authentication. Steps are provided in "Using a Google Service Account" in the [installation guide](https://storage.googleapis.com/simba-bq-release/odbc/Simba%20Google%20BigQuery%20ODBC%20Connector%20Install%20and%20Configuration%20Guide-2.5.0.1001.pdf#page=15).
  * If you are using a **User Account** for authentication, some steps may be missing from the  [installation guide](https://storage.googleapis.com/simba-bq-release/odbc/Simba%20Google%20BigQuery%20ODBC%20Connector%20Install%20and%20Configuration%20Guide-2.5.0.1001.pdf) in the "**Using a Google User Account"** section. Include the following steps:
    * [ ] Before starting a Client ID & Secret must be created, from the GCP console select \
      **APIs & Services > Credentials > Create Credentials > OAuth Client ID > Web Application** to create one. The URI [https://developers.google.com/oauthplayground](https://developers.google.com/oauthplayground) must be added to the list of Authorized redirect URIs.
    * [ ] Before clicking the **Authorize APIs** button on the [https://developers.google.com/oauthplayground](https://developers.google.com/oauthplayground) webpage, click the "**OAuth 2.0 Configuration**" icon to reveal a menu; check the **Use your own OAuth credentials** box to reveal additional options; and specify the Client ID & Secret provided by the previous addendum step.
    * [ ] &#x20;While on the **Selecting & authorize APIs** section of the [https://developers.google.com/oauthplayground](https://developers.google.com/oauthplayground) webpage, ensure that the **BigQuery API v2 > [https://www.googleapis.com/auth/bigquery](https://www.googleapis.com/auth/bigquery)** option is selected.