ODBC Data Wrapper Reference
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:
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:
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.
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.
You can then build a new image with installed ODBC drivers using the Dockerfile.
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
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
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
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.
RDMS-Specific Notes
PostgreSQL
Accessing BOOLEAN data in PostgreSQL requires the
BoolsAsChar=falseoption to be added to the connection string or DSN configuration.
Google BigQuery
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.dataViewerpermission grant on the GCP project.
Last updated