Version:

Table Of Contents

  1. Docs
  2. Integration
  3. Connectors
  4. The ODBC/JDBC Connector

The ODBC/JDBC Connector

Kinetica provides a SQL-92 interface through its ODBC connector. For details on the extent of SQL-92 coverage, see the SQL Support section.

Client/Server

There are two parts of the Kinetica ODBC driver, which translates SQL for Kinetica: the small ODBC Client wrapper, and main ODBC Server. The client sends SQL to the server, and the server translates the SQL and sends it as a request to the Kinetica server.

The ODBC Server should be started when all the Kinetica components are started via gpudb start. The client runs on Linux, Mac, and Windows. There is also a JDBC Client for connecting from Java programs.

Installation

The ODBC Server is installed by default in a standard Kinetica installation.

The ODBC Client must be installed for the environment in which it will be used. The following sections will detail how to install & configure the client in Linux & Windows.

Linux

The Unix ODBC library and our ODBC Client driver are both provided in the RPM and Deb in /opt/gpudb/connectors/odbcserver/client. Specifically, the ODBC Client driver itself is at /opt/gpudb/connectors/odbcserver/client/lib/libSimbaClient.so. Instructions on configuring it are in /opt/gpudb/connectors/odbcserver/README.md.

If installing a different version of the library to a Kinetica server or if installing to a server other than a Kinetica one, use the following procedure.

Installing unixODBC

  1. Install the unixODBC package.

    • Ubuntu:

      $ sudo apt-get install unixodbc

    • CentOS:

      $ sudo yum install unixODBC

  2. Copy the Simba library from an existing Kinetica server to the target server running unixODBC. The Simba library is installed by default at /opt/gpudb/connectors/odbcserver/client/lib/libSimbaClient.so on any Kinetica server with the Kinetica ODBC Connector component. There is no requirement that the Simba library be copied to any specific directory on the target unixODBC server.

  3. Register the Kinetica ODBC driver on the unixODBC server by adding the following entry to the end of the /etc/odbcinst.ini file (create the file if it doesn't already exist). Note that Driver is the new location of the Simba library file on the unixODBC server:

    [SimbaODBCClientDSIIDriver]
APILevel=1
ConnectFunctions=YYY
Description=Kinetica Simba Client Driver
Driver=</path/to/Simba/lib>/libSimbaClient.so
DriverODBCVer=03.80
SQLLevel=1

  4. Add a system DSN entry for the Kinetica database server to which the unixODBC client will connect. The entry should be added to /etc/odbc.ini on the unixODBC server, and this file can be created if it does not exist. Note that Driver refers to the same Simba library path as above, ServerList is the host & port of the Kinetica ODBC Server, and URL is the URL of the primary Kinetica database server:

    [KINETICA]
Description=Kinetica Connection
Driver=</path/to/Simba/lib>/libSimbaClient.so
Locale=en-US
UseSsl=0
ServerList=<odbc.host> 9292
ParentSet=
URL http://<database.host>:9191
PWD=
UID=

  5. Create a user DSN by copying the system DSN file to a given user's home directory, prepending a dot to the file name in order to make it hidden:

    $ cp /etc/odbc.ini ~/.odbc.ini

  6. Add the ODBC Client driver directory to the LD_LIBRARY_PATH:

    $ export LD_LIBRARY_PATH=</path/to/Simba/lib>:$LD_LIBRARY_PATH

Running iSQL

Whether running isql on the Kinetica head node or on a separate server after following the above instructions, these instructions can be used to validate the database via isql.

  1. Check the ODBC configuration. Running the following commands should generate similar output:

    $ which isql
/opt/gpudb/connectors/odbcserver/client/bin/isql

$ isql --version
unixODBC 2.3.4

$ odbcinst -q -d
[SimbaODBCClientDSIIDriver]

$ odbcinst -q -s
[KINETICA]
[READONLY_GPUDB_DSN]
[GPUDBDSN]

$ odbcinst -j
unixODBC 2.3.4
DRIVERS............: /opt/gpudb/connectors/odbcserver/client/etc/odbcinst.ini
SYSTEM DATA SOURCES: /opt/gpudb/connectors/odbcserver/client/etc/odbc.ini
FILE DATA SOURCES..: /opt/gpudb/connectors/odbcserver/client/etc/ODBCDataSources
USER DATA SOURCES..: /home/<user>/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

  2. Connect to Kinetica and request a list of tables:

    $ isql -m20 -v kinetica
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> help
+---------------------+---------------------+---------------------+---------------------+---------------------+
| TABLE_QUALIFIER     | TABLE_OWNER         | TABLE_NAME          | TABLE_TYPE          | REMARKS             |
+---------------------+---------------------+---------------------+---------------------+---------------------+
+---------------------+---------------------+---------------------+---------------------+---------------------+
SQLRowCount returns 0
SQL>

Tip

Using the -v option will show troubleshooting information if any error occurs in connecting.

Windows

Kinetica provides both a 32-bit & 64-bit Windows ODBC Client driver. The 32-bit version of Windows requires the 32-bit driver. The 64-bit version of Windows can use either the 32-bit or 64-bit driver, or both, as needs warrant. For instance, a user running 64-bit Windows and wanting to make ODBC connections to Kinetica from a 32-bit version of Excel will need the 32-bit driver. A user with the same OS using the 64-bit Tableau will need the 64-bit driver. A user wanting to use both will need both drivers.

  1. Download & install the ODBC Client for Windows following the instructions provided there
  2. Launch ODBC Data Source Administrator (64-bit or 32-bit, as needed)
  3. Click the System DSN tab
  4. Click the Add button to add a DSN
  5. Choose Kinetica ODBC Client, and click Finish
  6. Fill in the DSN Setup parameters (the following settings are examples - you may have to change some for your environment):
    • Data Source Name: Kinetica
    • Description: Kinetica
    • Leave Username and Password blank
    • URL: http://<database.host>:<database.port>/ (can be left blank in default installations)
    • Parent Set:
    • Server IP: <odbc.host>
    • Server Port: 9292
  7. Click OK

Configuration

The configuration parameters are the same in Linux & Windows, though the locations of the configurations are not.

ODBC Server

ODBC Server settings are set in the /opt/gpudb/connectors/odbcserver/bin/gpudbodbc.ini file on Linux. In Windows, these are set in the HKEY_LOCAL_MACHINE\SOFTWARE\Kinetica\Server registry key.

Config Parameter Default Value Description
ConsoleOutput 0

Whether debug logging is turned off or written to stdout; this information is redirected to odbcconsole.log

  • 0 = No additional logging
  • 1 = Additional debug logging to stdout
DontCombine 0

Whether or not SQL statements merging multiple tables (UNION/JOIN) will be done in 1 call or multiple calls

  • 0 = UNION & JOIN statements are processed in full, one call for each, no matter how many tables are involved
  • 1 = UNION & JOIN statements are separated into multiple calls, merging only 2 tables at a time
FailPartialPassdown 0

Whether or not to have the server fail any queries which cannot be fully optimized

  • 0 = Execute queries that can't be optimized
  • 1 = Fail queries that can't be optimized
KeepTempTables 0

Whether or not temporary tables will automatically be cleaned up by the system

  • 0 = Remove temporary tables automatically
  • 1 = Don't remove temporary tables automatically
ListenPort 9292 ODBC Server listen port
LogLevel 4

Controls how much information is written to the odbcconsole.log; the higher the number, the more info that is written

  • 6 = Trace
  • 5 = Debug
  • 4 = Info
  • 3 = Warn
  • 2 = Error
  • 1 = Fatal
  • 0 = Off
LogPath /opt/gpudb/connectors/odbcserver/logs Where to write log files (including odbc.log, odbcconsole.log, and others)
LogTree 0

Whether or not AE Trees are logged. See Logging for details.

  • 0 = Don't log AE Trees
  • 1 = Log AE Trees
MaxConnections 64 Maximum number of clients that can have connections simultaneously
MaxQueryDimensions 10 Number of tables in a single query allowed to be joined on columns that are not primary keys
MaxRowsToFetch 100000 Maximum number of response records to send to the client for a given query (may be overridden by client)
NoPassdown 0

Whether or not to execute queries with no optimization

  • 0 = Execute queries with optimization
  • 1 = Execute queries without optimization
ParentSet   Collection containing tables that clients will use by default (may be overridden by client)
PWD   Password to use when connecting to the database, if none specified by client
ReceivedLinesToShow 0 Number of lines of received data and types to output to stdout (if ConsoleOutput is on)
RowsPerFetch 1000 Number of response records to send to the client at a time (may be overridden by client)
SplitJoins 0

Whether or not to execute joins within a single query using a two-phase scheme, separating the join (tables, conditions, and filters) from the rest of the subquery (column selection, aggregation, etc); offers a performance gain for any query with repeated requests (subqueries) for the same join conditions, but different sets of selected columns, at the price of marginal overhead.

  • 0 = Execute single-phase joins
  • 1 = Execute two-phase joins
TempCollection TEMP Name of collection for storing temporary result set objects
Timeout 300000 Timeout for requests issued to the database, in milliseconds
TTL 5

Time to Live (TTL) for temp sets, in minutes. Other possible values:

  • -2 = use server's default TTL
  • -1 = no TTL (temp sets will not expire)
  • 0 = expire immediately (at next clean-up interval)
UID   User ID to use when connecting to the database, if none specified by client
URL http://127.0.0.1:9191/ URL of Kinetica database server to connect to

The following optional parameters should be used when instituting SSL connections between ODBC clients and the ODBC Server, as well as between the ODBC Server and the database.

Config Parameter Default Value Description
CertPath   Path to ODBC Server CA trust store directory, used to validate the certificate from the database server; in the default configuration, the ODBC Server and the database server are co-located, so this directory only needs to contain their shared host's public certificate
SSLAllowHostMismatch 0

Whether or not certificates whose host name does not match the database server are accepted by the ODBC Server

  • 0 = Do not accept certificates whose host name is different than the database server host name
  • 1 = Accept certificates whose host name is different than the database server host name
SSLCertFile   Path to the ODBC Server certificate, used in communications with ODBC clients
UseSSL 0

Whether or not the ODBC Server should only accept SSL connections from clients

  • 0 = Only accept non-SSL client connections
  • 1 = Only accept SSL client connections

ODBC Client

In Linux, the ODBC Client DSN settings are configured via the /opt/gpudb/connectors/odbcserver/client/etc/odbc.ini file. In Windows, these settings are stored in the registry in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<DSN NAME>. Some settings of note follow.

Config Parameter Default Value Description
UID   User ID for database connection (blank to use default credentials)
PWD   Password for database connection (blank to use default credentials)
ReadOnly 0

Whether connection is read-only or read-write:

  • 0 = read-write
  • 1 = read-only
ServerList 127.0.0.1 9292 Host and port of the ODBC Server

The following are optional parameters that could be used to override the server's settings but usually should be left blank (or not supplied) to use the server's settings.

Config Parameter Default Value Description
DontCombine 0

Whether or not SQL statements merging multiple tables (UNION/JOIN) will be done in 1 call or multiple calls

  • 0 = UNION & JOIN statements are processed in full, one call for each, no matter how many tables are involved
  • 1 = UNION & JOIN statements are separated into multiple calls, merging only 2 tables at a time
FailPartialPassdown 0

Whether or not to have the server fail any queries which cannot be fully optimized

  • 0 = Execute queries that can't be optimized
  • 1 = Fail queries that can't be optimized
KeepTempTables 0

Whether or not temporary tables will automatically be cleaned up by the system

  • 0 = Remove temporary tables automatically
  • 1 = Don't remove temporary tables automatically
MaxQueryDimensions 10 Number of tables in a single query allowed to be joined on columns that are not primary keys
MaxRowsToFetch 100000 Maximum number of records to request from the server for a given query
NoPassdown 0

Whether or not to execute queries with no optimization

  • 0 = Execute queries with optimization
  • 1 = Execute queries without optimization
ParentSet   Default collection containing tables to access
RowsPerFetch 10000 Number of records to request from the server at a time
SplitJoins 0

Whether or not to execute joins in two phases--the first will be a joining of the requested tables with all columns present in the result set, and the second will be the selection of the requested columns from the joined result set. If joins are split, the phase 1 result set can be reused in queries which request the same join, but select a different set of columns. Offers a performance gain for repeated requests for the same join conditions at the price of marginal overhead.

  • 0 = Execute single-phase joins
  • 1 = Execute two-phase joins
TempCollection TEMP Name of collection for storing temporary result set objects
Timeout 300000 Timeout for requests issued to the database, in milliseconds
TTL 5

TTL for temp sets, in minutes. Other possible values:

  • -2 = use server's default TTL
  • -1 = no TTL (temp sets will not expire)
  • 0 = expire immediately (at next clean-up interval)
URL http://127.0.0.1:9191 URL of Kinetica database server to connect to

Important

When ParentSet is set (not blank) tables referenced within SQL statements will be looked for in the configured ParentSet first, before searching other schemas and the root.

The following optional parameters should be used when connecting over SSL.

Config Parameter Default Value Description
AllowHostMismatch 0

Whether or not certificates whose host name does not match the ODBC Server are accpeted by the client

  • 0 = Do not accept certificates whose host name is different than the ODBC Server host name
  • 1 = Accept certificates whose host name is different than the ODBC Server host name
AllowSelfSignedCert 0

Whether or not self-signed certificates are accepted by the client

  • 0 = Do not accept self-signed certificates from the ODBC Server
  • 1 = Accept self-signed certificates from the ODBC Server
AllowExpiredCert 0

Whether or not expired certificates are accepted by the client

  • 0 = Do not accept expired certificates from the ODBC Server
  • 1 = Accept expired certificates from the ODBC Server
SSLCACertFile   Path to client's CA trust store, used to validate the certificate from the ODBC Server
UseSSL 0

Whether or not the client should connect to the ODBC Server over SSL

  • 0 = Connect without using SSL
  • 1 = Connect over SSL

Connecting

KiSQL

KiSQL is a JDBC SQL client that is bundled with Kinetica; details can be found on the Kinetica SQL (KiSQL) page. It can be run from the Kinetica server (head node of a cluster) as the gpudb user.

$ /opt/gpudb/kitools/kisql/kisql -host localhost
Connection successful
Catalog [KINETICA]
Time 0.248
KINETICA()=>

For a listing of kisql options, run:

$ /opt/gpudb/kitools/kisql/kisql -?

At the KiSQL prompt, all tables can be listed with the \tables command:

KINETICA()=> \tables

+---------------------------------------------------------+--------------+
| TABLE_NAME                                              | TABLE_TYPE   |
+---------------------------------------------------------+--------------+
| emp                                                     | TABLE        |
+---------------------------------------------------------+--------------+

Semicolon-terminated SQL statements can be issued:

KINETICA()=> SELECT *
KINETICA()=> FROM emp;

Enter \quit to exit KiSQL.

iSQL in Linux

$ PATH=${PATH}:/opt/gpudb/connectors/odbcserver/client/bin
$ isql -m20 -v <DSN> [UID [PWD]]

DSN is the name of the configured ODBC connection to Kinetica. If configured as in the Installation > Linux section, this will be kinetica; on servers with Kinetica installed, a DSN of KINETICA is pre-configured for that server's database instance. UID and PWD are the User ID and Password parameters, respectively, which are optional on some systems. The -m20 option reduces column widths to 20 characters (the default causes severe line-wrapping), and the -v option puts isql into verbose mode. You should see something like this:

$ isql -m20 -v kinetica
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

You can type help for a list of all tables & views in the database:

SQL> help
+---------------------+---------------------+---------------------+---------------------+---------------------+
| TABLE_QUALIFIER     | TABLE_OWNER         | TABLE_NAME          | TABLE_TYPE          | REMARKS             |
+---------------------+---------------------+---------------------+---------------------+---------------------+
+---------------------+---------------------+---------------------+---------------------+---------------------+
SQLRowCount returns 0

You may now type SQL commands at the SQL> prompt, and the responses will be displayed. For example:

SQL> select x, y from calcs

You can exit this mode by typing quit.

The documentation for isql can be found at: https://www.mankier.com/1/isql

Tableau

  1. Install the ODBC Client - see ODBC Installation above
  2. In Tableau, connect to Other Databases (ODBC)
    • DSN: Kinetica
    • Click Connect
    • Click Sign In
    • Database: KINETICA
    • Schema: <choose one>
      • To access tables at the root level, don't choose a schema (leave as Select Schema)
    • Pick your table

Note

Tableau may give a warning about not all features being supported by the ODBC driver; this should be able to be safely ignored for most applications.

Excel

  1. Install the ODBC Client - see ODBC Installation above
  2. In Excel, click the Data tab at the top
  3. Click the Get External Data > From Other Sources button, then click From Data Connection Wizard
  4. In the Connection Wizard, choose ODBC DSN and click Next >
  5. Select one of the ODBC data sources (i.e., DSNs) you created above (e.g., Kinetica), and click Next >
  6. Continue with the wizard to create a link to all the tables or a specific table, and save an ODC file, etc.
  7. Click the Finish button

To read in data using the ODC file created above:

  1. In Excel, click the Data tab at the top
  2. Click Get External Data > Existing Connections
  3. Choose the ODC file you created above, and click Open
  4. Select a Table to import
  5. Select where you want the data to appear, and click the OK button

Java Client (via JDBC)

  1. Download the JDBC Client JAR file.

  2. Install a Java SQL client:

    • SQuirreL SQL
    • DBeaver
      • Download & install DBeaver
      • In DBeaver, on the Database menu, select Driver Manager
      • Click New to create a new driver connection
      • Complete the DBeaver configuration page:
        • Driver Name: Kinetica
        • Class Name: com.simba.client.core.jdbc4.SCJDBC4Driver
        • URL Template (optional): jdbc:simba://<odbc.host>:<odbc.port>;URL=http://<database.host>:<database.port>
          • e.g., jdbc:simba://127.0.0.1:9292;URL=http://127.0.0.1:9191
        • Click Add File and select the downloaded SimbaJDBCClient4.jar
        • Click OK
      • Click Close to exit Driver Manager
      • Configure a connection:
        • On the File menu, select New
        • Ensure that Database Connection is selected and click Next >
        • Select Kinetica and click Next >
        • Enter a User name, (admin for database administrator connection); this should result in the JDBC URL being populated automatically
        • Enter the Password for the database user
        • Click Next > twice and click Test Connection ... to verify the configuration
        • Click OK to clear the successful connection pop-up and then Finish to complete the connection configuration

  3. The DBeaver configuration is given above; for other clients, the following is the general driver and connection information that should be used when configuring a client.

    • Driver: <point to the downloaded JAR file>

    • URL: jdbc:simba://<odbc.host>:<odbc.port>

      • e.g., jdbc:simba://127.0.0.1:9292

    • Class Name: com.simba.client.core.jdbc4.SCJDBC4Driver

      Note

      Most of the programs have an option to find this from the specified JAR file for you

Tip

There are many Java SQL clients available, e.g., DBClient and HeidiSQL. You can find even more here.

Logging

The ODBC Server logs inbound requests, calls it makes to the database on behalf of those requests, and other debugging information. The logs, by default, are located under /opt/gpudb/connectors/odbcserver/logs/. To change the location of the log files see ODBC Configuration above.

File Name Information Logged
odbc.log Events from the ODBC Server driver
odbcconsole.log Interactions between the ODBC Server driver and the database
AETree.log Algebraic Expression Trees (AE Trees), which break down inbound queries into hierarchical component form. These can be useful in debugging problematic queries.
ETree.log Data source (table name) ultimately serving the result
© Copyright 2018, Kinetica. Created using Sphinx.