HEAVY.AI Docs
v8.1.0
v8.1.0
  • Welcome to HEAVY.AI Documentation
  • Overview
    • Overview
    • Release Notes
  • Installation and Configuration
    • System Requirements
      • Hardware Reference
      • Software Requirements
      • Licensing
    • Installation
      • Free Version
      • Installing on Docker
        • HEAVY.AI Installation using Docker on Ubuntu
      • Installing on Ubuntu
        • HEAVY.AI Installation on Ubuntu
        • Install NVIDIA Drivers and Vulkan on Ubuntu
      • Installing on Rocky Linux / RHEL
        • HEAVY.AI Installation on RHEL
        • Install NVIDIA Drivers and Vulkan on Rocky Linux and RHEL
      • Getting Started on AWS
      • Getting Started on GCP
      • Getting Started on Azure
      • Getting Started on Kubernetes (BETA)
      • Upgrading
        • Upgrading HEAVY.AI
        • Upgrading from Omnisci to HEAVY.AI 6.0
        • CUDA Compatibility Drivers
      • Uninstalling
      • Ports
    • Services and Utilities
      • Using Services
      • Using Utilities
    • Executor Resource Manager
    • Configuration Parameters
      • Overview
      • Configuration Parameters for HeavyDB
      • Configuration Parameters for HEAVY.AI Web Server
      • Configuration Parameters for HeavyIQ
    • Security
      • Roles and Privileges
        • Column-Level Security
      • Connecting Using SAML
      • Implementing a Secure Binary Interface
      • Encrypted Credentials in Custom Applications
      • LDAP Integration
    • Distributed Configuration
  • Loading and Exporting Data
    • Supported Data Sources
      • Kafka
      • Using HeavyImmerse Data Manager
      • Importing Geospatial Data
    • Command Line
      • Loading Data with SQL
      • Exporting Data
  • SQL
    • Data Definition (DDL)
      • Datatypes
      • Users and Databases
      • Tables
      • System Tables
      • Views
      • Policies
      • Comment
    • Data Manipulation (DML)
      • SQL Capabilities
        • ALTER SESSION SET
        • ALTER SYSTEM CLEAR
        • DELETE
        • EXPLAIN
        • INSERT
        • KILL QUERY
        • LIKELY/UNLIKELY
        • SELECT
        • SHOW
        • UPDATE
        • Arrays
        • Logical Operators and Conditional and Subquery Expressions
        • Table Expression and Join Support
        • Type Casts
      • Geospatial Capabilities
        • Uber H3 Hexagonal Modeling
      • Functions and Operators
      • System Table Functions
        • generate_random_strings
        • generate_series
        • tf_compute_dwell_times
        • tf_feature_self_similarity
        • tf_feature_similarity
        • tf_geo_rasterize
        • tf_geo_rasterize_slope
        • tf_graph_shortest_path
        • tf_graph_shortest_paths_distances
        • tf_load_point_cloud
        • tf_mandelbrot*
        • tf_point_cloud_metadata
        • tf_raster_contour_lines; tf_raster_contour_polygons
        • tf_raster_graph_shortest_slope_weighted_path
        • tf_rf_prop_max_signal (Directional Antennas)
        • ts_rf_prop_max_signal (Isotropic Antennas)
        • tf_rf_prop
      • Window Functions
      • Reserved Words
      • SQL Extensions
      • HeavyIQ LLM_TRANSFORM
  • HeavyImmerse
    • Introduction to HeavyImmerse
    • Admin Portal
    • Control Panel
    • Working with Dashboards
      • Dashboard List
      • Creating a Dashboard
      • Configuring a Dashboard
      • Duplicating and Sharing Dashboards
    • Measures and Dimensions
    • Using Parameters
    • Using Filters
    • Using Cross-link
    • Chart Animation
    • Multilayer Charts
    • SQL Editor
    • Customization
    • Joins (Beta)
    • Chart Types
      • Overview
      • Bubble
      • Choropleth
      • Combo
      • Contour
      • Cross-Section
      • Gauge
      • Geo Heatmap
      • Heatmap
      • Linemap
      • Number
      • Pie
      • Pointmap
      • Scatter Plot
      • Skew-T
      • Table
      • Text Widget
      • Wind Barb
    • Deprecated Charts
      • Bar
      • Combo - Original
      • Histogram
      • Line
      • Stacked Bar
    • HeavyIQ SQL Notebook
  • HEAVYIQ Conversational Analytics
    • HeavyIQ Overview
      • HeavyIQ Guidance
  • HeavyRF
    • Introduction to HeavyRF
    • Getting Started
    • HeavyRF Table Functions
  • HeavyConnect
    • HeavyConnect Release Overview
    • Getting Started
    • Best Practices
    • Examples
    • Command Reference
    • Parquet Data Wrapper Reference
    • ODBC Data Wrapper Reference
    • Raster Data Wrapper Reference
  • HeavyML (BETA)
    • HeavyML Overview
    • Clustering Algorithms
    • Regression Algorithms
      • Linear Regression
      • Random Forest Regression
      • Decision Tree Regression
      • Gradient Boosting Tree Regression
    • Principal Components Analysis
  • Python / Data Science
    • Data Science Foundation
    • JupyterLab Installation and Configuration
    • Using HEAVY.AI with JupyterLab
    • Python User-Defined Functions (UDFs) with the Remote Backend Compiler (RBC)
      • Installation
      • Registering and Using a Function
      • User-Defined Table Functions
      • RBC UDF/UDTF Example Notebooks
      • General UDF/UDTF Tutorial Notebooks
      • RBC API Reference
    • Ibis
    • Interactive Data Exploration with Altair
    • Additional Examples
      • Forecasting with HEAVY.AI and Prophet
  • APIs and Interfaces
    • Overview
    • heavysql
    • Thrift
    • JDBC
    • ODBC
    • Vega
      • Vega Tutorials
        • Vega at a Glance
        • Getting Started with Vega
        • Getting More from Your Data
        • Creating More Advanced Charts
        • Using Polys Marks Type
        • Vega Accumulator
        • Using Transform Aggregation
        • Improving Rendering with SQL Extensions
      • Vega Reference Overview
        • data Property
        • projections Property
        • scales Property
        • marks Property
      • Migration
        • Migrating Vega Code to Dynamic Poly Rendering
      • Try Vega
    • RJDBC
    • SQuirreL SQL
    • heavyai-connector
  • Tutorials and Demos
    • Loading Data
    • Using Heavy Immerse
    • Hello World
    • Creating a Kafka Streaming Application
    • Getting Started with Open Source
    • Try Vega
  • Troubleshooting and Special Topics
    • FAQs
    • Troubleshooting
    • Vulkan Renderer
    • Optimizing
    • Known Issues and Limitations
    • Logs and Monitoring
    • Archived Release Notes
      • Release 6.x
      • Release 5.x
      • Release 4.x
      • Release 3.x
Powered by GitBook
On this page
  • Connecting from Microsoft Windows Using ODBC
  • Using HTTPS and Binary Encryption
  • Installing ODBC on Linux
  • Supported ODBC Functions
  • Unsupported ODBC Functions
  • Unsupported ODBC Features
  • Installing the Power BI Data Connector
  • Install the Connector
  • Security
Export as PDF
  1. APIs and Interfaces

ODBC

PreviousJDBCNextVega

Open Database Connectivity (ODBC) is a standard application programming interface (API) for accessing database management systems (DBMS). HeavyDB supports ODBC connections.

The HEAVY.AI ODBC installer tool filename has the following format:

HeavyaiInstall_<major_version>.<minor_version>.<minor_patch_version>.<odbc_driver_version>.<odbc_driver_minor_version>.
<odbc_patch_version>.<odbc_extra_info>.zip

For example:

HeavyaiInstall_6.0.0.zip

For assistance with downloading HEAVY.AI ODBC software and utilities, contact your HEAVY.AI Sales Representative.

Connecting from Microsoft Windows Using ODBC

While there are many ways to connect to ODBC, perhaps the most common is to connect from a Windows 10 client using Tableau or Excel. HEAVY.AI provides an ODBC connection utility to help you get started.

To configure your ODBC datasource:

  1. Run the HEAVY.AI Installer Tool provided by your HEAVY.AI Sales Representative.

  2. Enter the path to the ODBC driver (default is C:\Heavyai\ODBC\bin\).

  3. Click OK.

  4. Wait for the “SUCCESS!” message. Click OK.

  5. Open your Windows control panel.

  6. Open either the 32-bit or 64-bit version of ODBC Data Source Administrator, depending on the application you are connecting to the driver.

  7. Click Add....

  8. Enter the user Name (for example, heavyai), Platform, and Driver (HeavyaiDriver).

  9. In HEAVY.AI ODBC Driver DSN Setup:

    1. Enter an optional Description for your data source.

    2. Enter the User (for example, heavyai).

    3. Enter the Password for your data source.

    4. Enter the Host:Port values (for example, myhost.heavyai.com:6274).

    5. Enter the Database name (for example, heavyai).

    6. Enter the Max rows returned (for example, 10000).

    7. Select the Protocol Type to connect to the HEAVY.AI Web server: HTTP, HTTPS, Binary, or Binary Encrypted. If you select HTTPS or Binary Encrypted, you must using the Microsoft Management Console.

    8. If you want to ease server certificate validation requirements, select Ignore Invalid Certificates (not recommended). If you do this, the CN name in the certificate is not required to match the network name of the host from which it was received.

    9. In Connection Timeout, enter the time interval (in seconds) used to terminate the failed connection attempts.

    10. To configure options for HEAVY.AI integration with applications such as Alteryx, click Advanced Options. You can:

      • Escape single quotes or line-feed characters when a parameterized insert command is used.

      • Allow an SQLPrepare command to be run before an SQL SELECT command to return metadata about the columns. This adds an extra call to the HEAVY.AI database and should be used with caution.

      • Allow the driver to remove blank schema names from qualified table names. The Alteryx database discovery GUI tool attempts to insert a blank query into qualified names such as heavyai.table. This option allows the driver to remove the extra period, transforming the name to heavyai.table.

  10. Click Test.

  11. Wait for the “Success!” message. Click OK.

Using HTTPS and Binary Encryption

In HEAVY.AI ODBC Driver DSN Setup, if you set your protocol type to HTTPS or Binary Encrypted, follow these instructions to enable an encrypted connection:

  1. Open the Microsoft Management Console (MMC).

  2. On the File menu, select Add/Remove Snap-ins.

  3. In the Available snap-ins list box, select Certificates.

  4. In the Certificates snap-in box, select Computer Account, and then click Next.

  5. Select Local Computer, and click Finish, and in the Add or Remove Snap-ins window, click OK.

  6. In the left pane of the Console Root window, right-click the Trusted Root Certificate Authorities folder, click All tasks, and then click Import....

  7. In the Certificate Import Wizard, click Next.

  8. Click Browse, select the certificate file to import, and click Finish.

Installing ODBC on Linux

Follow these steps to configure HeavyDB ODBC connections on Linux.

  1. If required, install unixODBC, which you use to test that the HEAVY.AI ODBC driver configuration is working correctly:

    • For RHEL/Centos:

      $ sudo yum install unixODBC
    • For Ubuntu/Debian:

       $ sudo apt-get install unixodbc

    Type y when prompted to install the package.

  2. Validate that isql is installed by running it from the shell:

    $ isql

    If installed correctly, you see output showing the isql syntax and options.

  3. Create a folder for the ODBC driver:

    $ sudo mkdir -p /apps/odbc/heavyai/
    $ sudo chmod 777 /apps/odbc/heavyai
  4. Change to the folder you just created:

    $ cd /apps/odbc/heavyai/
    $ pwd /apps/odbc/heavyai
  5. Download the HEAVY.AI Linux ODBC driver:

    $ curl -O https://builds.mapd.com/odbc/heavydbODBC-6.0.0-20200302-a280de1b9a-Linux.tar.gz -u <username>:<password>
  6. $ tar xvf mapd_odbc_installer_linux_.tar.gz
  7. Edit the /etc/odbc.ini file by changing the properties and values to values appropriate for your HEAVY.AI installation. The included /configuration/odbc.ini.skeleton file (show below) provides a template and information about available options.

    [ODBC Data Sources]
    heavyai=heavyaiDriver
    
    [Heavy.AI]
    Description=64-bit HEAVY.AI Driver
    Driver=/apps/odbc/heavyai/lib/libheavyaiODBC.so
    Locale=en-US
    PWD=<USER_PASSWORD>
    UID=<USER_ID>
    # HOST can contain ':port number' as in 'localhost:6274'.
    HOST=<SERVER_HOSTNAME>
    PORT=<SERVER_PORT>
    DATABASE=<DATABASE_NAME>
    # Protocol can be BINARY or HTTP.
    # If not supplied, the protocol defaults to BINARY.
    PROTOCOL=<PROTOCOL>
    # If CA_CERT points to a valid PKI certificate, the driver
    # attempts to establish an SSL connection. If not supplied
    # or empty, the driver defaults to a nonencrypted connection.
    # Note that the server port must be expecting an SSL connection.
    SERVER_CA_CERT=<PATH_TO_SERVER_CA_CERT>
    # Provide a time interval (in seconds) used to terminate the failed connection attempts. 
    CONNECTION TIMEOUT=<seconds>

    If SERVER_CA_CERT is supplied, it overrides the default search path.

    Invalid non-mandatory options--for example, those that have misspellings--are ignored and do not generate errors.

  8. Edit the /etc/odbcinst.ini file by changing the following properties/values in bold to values appropriate for your HEAVY.AI installation.

    [ODBC] 
    Trace = yes 
    TraceFile=<FULL_NAME_ODBC_TRACE_FILE> 
    
    [ODCB Drivers] 
    HeavyaiDriver=Installed 
    
    [HeavyaiDriver]
    APILevel=1 
    DriverODBCVer=04.80 
    SQLLevel=1 
    ConnectionFunction=YYY 
    Description=HeavyaiDriver
    Driver=apps/odbc/heavyai/HeavyaiODBC-6.0.0.prod-4.0.000.0000-20200302-a280de1b9a-Linux/heavyai/odbc/lib/libheavyaiODBC.so 
    LogLevel=6 
    LogPath=<PATH_TO_HEAVYAI_LOGFILE>

    Your odbcinst.ini file might be empty or might already contain other entries. If your file contains other entries, add the new entries to the end of the file and do not overwrite existing entries.

  9. Create a symbolic link to the error messages folder for the HEAVY.AI driver library:

    $ ln -s apps/odbc/heavyai/HeavyaiODBC-6.0.0.prod-4.0.000.0000-20200302-a280de1b9a-Linux/heavyai/odbc/lib/libheavyaiODBC.so
    $ ls -l /apps/odbc/heavyai/libs/
    total 229256
    lrwxrwxrwx 1 mapd mapd        60 Feb  5 12:54 en-US -> 
    /apps/odbc/heavyai/DataAccessComponents/ErrorMessages/en-US/
    -rwxrwxr-x 1 mapd mapd 234750688 Oct 11 15:18 libheavyaiODBC.so
  10. Test the installation and connection using isql:

    $ isql -v heavyai
    +---------------------------------------+
    | Connected!                            |
    |                                       |
    | sql-statement                         |
    | help [tablename]                      |
    | quit                                  |
    |                                       |
    +---------------------------------------+
    SQL> select count(*) cnt from flights_2008_7M;
    +---------------------+
    | BIGINT              |
    +---------------------+
    | 7009728             |
    +---------------------+
    SQLRowCount returns 1
    1 rows fetched
    SQL

    If you receive an error message, your connection is not successfully configured. Check the connection properties in /etc/odbc.ini.

If SERVER_CA_CERT is supplied, it overrides the default search path. Invalid nonmandatory options--for example, those that have misspellings--are ignored and do not generate errors.

Supported ODBC Functions

HeavyDB supports the following Tier 1 (Core) ODBC functions:

Function

Description

Obtains an environment, connection, statement, or descriptor handle.

SQLBindCol binds application data buffers to columns in the result set.

SQLCloseCursor closes a cursor that has been opened on a statement and discards pending results.

SQLColAttribute returns descriptor information for a column in a result set. Descriptor information is returned as a character string, descriptor-dependent value, or an integer value.

SQLColumns returns the list of column names in specified tables. The driver returns this information as a result set on the specified StatementHandle.

SQLConnect establishes a connection between a driver and a data source. The connection handle references storage of all information about the connection to the data source, including status, transaction state, and error information.

SQLCopyDesc copies descriptor information from one descriptor handle to another.

SQLDescribeCol returns the result descriptor for one column in the result set.

SQLDescribeParam returns the description of a parameter marker associated with a prepared SQL statement. This information is also available in the fields of the IPD.

SQLDisconnect closes the connection associated with a specific connection handle.

SQLDriverConnect is an alternative to SQLConnect.It supports data sources that require more connection information than the three arguments in SQLConnect, dialog boxes to prompt the user for all connection information, and data sources that are not defined in the system information.

SQLExecDirect executes a preparable statement using the current values of the parameter marker variables, if any parameters exist in the statement. SQLExecDirect is the fastest way to submit an SQL statement for one-time execution.

SQLExecute executes a prepared statement using the current values of the parameter marker variables, if any parameter markers exist in the statement.

SQLFetch fetches the next rowset of data from the result set and returns data for all bound columns.

SQLFreeHandle frees resources associated with a specific environment, connection, statement, or descriptor handle.

SQLFreeStmt stops processing associated with a specific statement, closes any open cursors associated with the statement, discards pending results, or, optionally, frees all resources associated with the statement handle.

SQLGetConnectAttr returns the current setting of a connection attribute.

SQLGetData retrieves data for a single column in the result set, or for a single parameter after SQLParamData returns SQL_PARAM_DATA_AVAILABLE. You can call it multiple times to retrieve variable-length data in parts.

SQLGetDescField returns the current setting or value of a single field of a descriptor record.

SQLGetDescRec returns the current settings or values of multiple fields of a descriptor record. The fields returned describe the name, data type, and storage of column or parameter data.

SQLGetDiagField returns the current value of a field of a record of the diagnostic data structure that contains error, warning, and status information.

SQLGetDiagRec returns the current values of multiple fields of a diagnostic record that contain error, warning, and status information.

SQLGetEnvAttr returns the current setting of an environment attribute.

SQLGetFunctions returns information about whether a driver supports a specific ODBC function. This function is implemented in the Driver Manager and potentially in drivers.

SQLGetInfo returns general information about the driver and data source associated with a connection.

SQLGetStmtAttr returns the current setting of a statement attribute.

SQLGetTypeInfo returns information about data types supported by the data source.

SQLMoreResults determines whether more results are available on a statement containing SELECT, UPDATE, INSERT, or DELETE statements and, if so, initializes processing for those results.

SQLNativeSql returns the SQL string as modified by the driver. SQLNativeSql does not execute the SQL statement.

SQLNumParams returns the number of parameters in an SQL statement.

SQLNumResultCols returns the number of columns in a result set.

SQLRowCount returns the number of rows affected by an UPDATE, INSERT, or DELETE statement.

SQLSetConnectAttr sets attributes that govern aspects of connections.

SQLSetDescField sets the value of a single field of a descriptor record.

The SQLSetDescRec function sets multiple descriptor fields that affect the data type and buffer bound to a column or parameter data.

SQLSetEnvAttr sets attributes that govern aspects of environments.

SQLSetStmtAttr sets attributes related to a statement.

SQLStatistics retrieves a list of statistics about a single table and the indices associated with the table. The driver returns the information as a result set.

SQLTables returns the list of table, catalog, or schema names, and table types, stored in a specific data source. The driver returns the information as a result set.

Unsupported ODBC Functions

HeavyDB does not support the following ODBC functions at this time.

SQLBindParameter
SQLBrowseConnect
SQLCancel
SQLCancelHandle
SQLColumnPrivileges
SQLEndTran
SQLExtendedFetch
SQLFetchScroll
SQLForeignKeys
SQLGetCursorName
SQLSetCursorName
SQLSetPos
SQLSpecialColumns
SQLParamData
SQLPrepare
SQLPrimaryKeys
SQLProcedureColumns
SQLProcedures
SQLPutData
SQLTablePrivileges

Unsupported ODBC Features

HeavyDB does not support the following ODBC features at this time.

  • Batch statements

  • Multiple result sets

  • Domains

  • Rules

  • Database procedures

  • Indexes

  • Keys

  • Transactions

  • Schemas (any client can see tables and views created by any user without restriction)

  • Rollbacks, checkpoints, or any other type of database recovery

Installing the Power BI Data Connector

To use the HEAVY.AI analytics engine, the Power BI Desktop connects via the HEAVY.AI ODBC driver in DirectQuery mode. (Power BI has two modes: direct and import.)

The Power BI Data Connector is distributed with the ODBC driver; install it to the directory that the Power BI desktop instance defines for custom connectors. To connect in DirectQuery mode, the ODBC driver connection needs to be "wrapped" in the supplied Power BI Data Connector.

Install the Connector

To install the connector, copy HeavyaiPBIC.pqx to the Power BI Desktop custom connectors folder. For a standard Power BI install, this directory is \Users\<user name>\Documents\Power BI Desktop\Custom Connectors. You might need to create the directory.

Security

HeavyaiPBIC.pqx is a signed file. For the signature to be trusted, the thumbprint of the signing certificate needs to be added to the "TrustedCertificateThumbprints” registry entry in the HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Power BI Desktop path. The thumbprint of the signing key is 29A21397B8169B153577806E3C43A502D944BC99.

Alternatively, you can downgrade security on Power BI desktop to allow it to trust all connectors.

For more information, see .

Trusted third-party connectors
enable encrypted connections
SQLAllocHandle
SQLBindCol
SQLCloseCursor
SQLColAttribute
SQLColumns
SQLConnect
SQLCopyDesc
SQLDescribeCol
SQLDescribeParam
SQLDisconnect
SQLDriverConnect
SQLExecDirect
SQLExecute
SQLFetch
SQLFreeHandle
SQLFreeStmt
SQLGetConnectAttr
SQLGetData
SQLGetDescField
SQLGetDescRec
SQLGetDiagField
SQLGetDiagRec
SQLGetEnvAttr
SQLGetFunctions
SQLGetInfo
SQLGetStmtAttr
SQLGetTypeInfo
SQLMoreResults
SQLNativeSql
SQLNumParams
SQLNumResultCols
SQLRowCount
SQLSetConnectAttr
SQLSetDescField
SQLSetDescRec
SQLSetEnvAttr
SQLSetStmtAttr
SQLStatistics
SQLTables