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.

Installation
- Linux
  - Installing unixODBC
  - Running iSQL
- Windows
Configuration
Connecting
- KiSQL
- iSQL in Linux
- Tableau
- Excel
- Java Client (via JDBC)
- Secure Connections
  - ODBC
  - JDBC
- Failover Connections
  - ODBC
  - JDBC
Logging

The Kinetica ODBC driver sends SQL to the Kinetica server, which translates & processes the SQL, returning the response back to the client.

The client runs on Linux, Mac, and Windows. There is also a JDBC Client for connecting from Java programs.

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/odbc. Specifically, the ODBC Client driver itself is at /opt/gpudb/connectors/odbc/lib/libKineticaODBC.so. Instructions on configuring it are in /opt/gpudb/connectors/odbc/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¶

Install the unixODBC package.

Ubuntu:
```
$ sudo apt-get install unixodbc
```
CentOS:
```
$ sudo yum install unixODBC
```

Copy the ODBC Client library from an existing Kinetica server to the target server running unixODBC. The library is installed by default at /opt/gpudb/connectors/odbc/lib/libKineticaODBC.so on any Kinetica server. There is no requirement that the library be copied to any specific directory on the target unixODBC server.
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 ODBC Client library file on the unixODBC server:
```
[KineticaODBCIDriver]
APILevel=1
ConnectFunctions=YYY
Description=Kinetica ODBC Client Driver
Driver=</path/to/odbc/lib>/libKineticaODBC.so
DriverODBCVer=03.80
SQLLevel=1
```
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 ODBC Client library path as above, and URL is the URL of the primary Kinetica database server:
```
[KINETICA]
Description=Kinetica Connection
Driver=</path/to/odbc/lib>/libKineticaODBC.so
URL=http://<database.host>:9191
PagingTableTtl=10
Timeout=0
Locale=en-US
```
For an SSL connection, use the following:
```
[KINETICA]
Description=Kinetica Connection
Driver=</path/to/odbc/lib>/libKineticaODBC.so
URL=https://<database.host>:8082/gpudb-0
UID=<username>
PWD=<password>
SslCACertPath=/opt/gpudb/certs
SslAllowHostMismatch=0
PagingTableTtl=10
Timeout=0
Locale=en-US
```
Tip

If using self-signed certificates, set SslAllowHostMismatch to 1.
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
```

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.

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

$ which isql
/opt/gpudb/connectors/odbc/bin/isql

$ isql --version
unixODBC 2.3.4

$ odbcinst -q -d
[ODBC Drivers]
[KineticaODBCDriver]

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

$ odbcinst -j
unixODBC 2.3.4
DRIVERS............: /opt/gpudb/connectors/odbc/etc/odbcinst.ini
SYSTEM DATA SOURCES: /opt/gpudb/connectors/odbc/etc/odbc.ini
FILE DATA SOURCES..: /opt/gpudb/connectors/odbc/etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Connect to Kinetica and request a list of tables:

$ isql -m20 -v kinetica
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> help
+---------------------+---------------------+---------------------+---------------------+---------------------+
| TABLE_CAT           | TABLE_SCHEM         | TABLE_NAME          | TABLE_TYPE          | REMARKS             |
+---------------------+---------------------+---------------------+---------------------+---------------------+
| Kinetica            | MASTER              | nyctaxi             | TABLE               |                     |
| Kinetica            | SYSTEM              | ITER                | TABLE               |                     |
+---------------------+---------------------+---------------------+---------------------+---------------------+
SQLRowCount returns -1
2 rows fetched
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.

Download & install the ODBC Client for Windows following the instructions provided there
Launch ODBC Data Source Administrator (64-bit or 32-bit, as needed)
Click the System DSN tab
Click the Add button to add a DSN
Choose Kinetica ODBC Client, and click Finish
Fill in the DSN Setup parameters (the following settings are examples - you may have to change some for your environment):
- Description: Kinetica
- URL: http://<database.host>:9191
- Leave User and Password blank
Click OK

Configuration¶

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

Linux ODBC - Settings are configured via the /opt/gpudb/connectors/odbc/etc/odbc.ini file.
Windows ODBC - Settings are stored in the registry in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<DSN NAME>
JDBC - Settings are passed on the connection string, separated by semicolons; e.g.:
```
jdbc:kinetica://kineticahost:9191/;RowsPerFetch=20000;Timeout=5
```
Note

The database URL parameter can be used in lieu of the JDBC host/port parameters; e.g., the following connection strings are equivalent:
- jdbc:kinetica://kineticahost:9191/;RowsPerFetch=20000;Timeout=5
- jdbc:kinetica:URL=http://kineticahost:9191;RowsPerFetch=20000;Timeout=5

Some settings of note follow.

Config Parameter	Default Value	Description
URL	`http://127.0.0.1:9191`	URL of Kinetica database server to connect to
UID		User ID for database connection (blank to use default credentials)
PWD		Password for database connection (blank to use default credentials)
Limit	`-1`	Limit number of records returned for each query issued; `-1` for no limit, i.e., all records will be returned for each query
RowsPerFetch	`10000`	Number of records to request from the server at a time
Timeout	`0`	Minutes to wait for a response from the database; `0` for no timeout

The following are optional parameters that could be used to override the default settings, which are set by the server. See the Configuration Reference for more information on configuring the SQL Engine server-side.

Config Parameter	Description
CombinePrepareAndExecute	Whether or not to combine the ODBC prepare and execute phases, when appropriate. This can improve the performance of static queries (those that lack parameterized values). `0` = Don't combine prepare and execute phases `1` = Combine prepare and execute phases
CostBasedOptimization	Whether or not to use the cost-based optimizer `0` = Disable cost-based optimizations `1` = Enable cost-based optimizations
DistributedJoins	Whether or not to process operations that would require resharding of data to complete successfully `0` = Disable distributed operations, failing any queries requiring resharding of data to succeed `1` = Enable distributed operations, resharding data to meet sharding requirements, if necessary
LateMaterialization	Whether or not to avoid materializing intermediary result sets when processing a complex query `0` = Materialize intermediary result sets `1` = Avoid materializing intermediary result sets
ParallelExecution	Whether or not to execute independent portions of a query in parallel `0` = Execute each component of a query in series, regardless of intra-query dependencies `1` = Enable parallel execution of independent portions of a query
PlanCache	Whether or not to cache query plans, as derived by the rule-based/cost-based optimizer `0` = Disable caching of query plans `1` = Enable caching of query plans
ResultsCaching	Whether or not to cache result sets, which happens automatically for certain queries that have only materialized views as sources `0` = Disable caching of result sets `1` = Enable caching of result sets
RuleBasedOptimization	Whether or not to use the rule-based optimizer `0` = Disable rule-based optimizations `1` = Enable rule-based optimizations
PagingTableTtl	TTL for the result set paging table, in minutes. Other possible values: `-1` = no TTL (temp sets will not expire) `0` = expire immediately (at next clean-up interval)
SsqOptimization	Whether or not to use scalar subquery optimizations, in which non-correlated single-value-returning subqueries will be evaluated first and then those result constants will be used in the remainder of the query processing `0` = Disable scalar subquery optimizations `1` = Enable scalar subquery optimizations
TTL	TTL for temporary result tables, 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)
UpdateOnExistingPk	Whether or not to use an upsert policy for record collision when inserting into a table with a primary key; if the specified table does not have a primary key, then this parameter is ignored `0` = Discard any record being inserted whose primary key matches that of an existing record `1` = Replace any existing record with the record being inserted, when the primary keys match
ValidateChange	When issuing an `ALTER TABLE` that changes column types/sizes, verify that the column data can be contained losslessly in the target column type/size. If true, fail the request if the data will not fit; if false, truncate the data to fit. `0` = Truncate any column data that won't fit into the new type size on a column change request `1` = Validate any column change and fail the request if column data is too large/long for the new type/size

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

Config Parameter	Default Value	Description
SslAllowHostMismatch	`0`	Whether or not certificates whose host name does not match the Kinetica server are accepted by the client `0` = Do not accept certificates whose host name is different than the Kinetica server host name `1` = Accept certificates whose host name is different than the Kinetica server host name
SslCACertPassword		Password to the certificate store specified by either SslCACertPath or TrustedStorePath (JDBC only)
SslCACertPath		Path to client's trust store, used to validate the certificate from the Kinetica server
TrustedStorePath		Path to client's trust store, used to validate the certificate from the Kinetica server (JDBC-only override for SslCACertPath parameter)

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/connectors/jdbc/kisql -host localhost

Connection successful
Catalog [Kinetica]
Deployment Time: 0.477 s
Kinetica()=>

For a listing of kisql options, run:

$ /opt/gpudb/connectors/jdbc/kisql -?

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

Kinetica()=> \tables

+---------------+--------------+--------------+
| TABLE_SCHEM   | TABLE_NAME   | TABLE_TYPE   |
+---------------+--------------+--------------+
| MASTER        | nyctaxi      | TABLE        |
| SYSTEM        | ITER         | TABLE        |
+---------------+--------------+--------------+

Semicolon-terminated SQL statements can be issued:

Kinetica()=> SELECT COUNT(*) FROM nyctaxi;

+----------+
|   EXPR_0 |
+----------+
|   500000 |
+----------+

Enter \quit to exit KiSQL.

iSQL in Linux¶

$ PATH=${PATH}:/opt/gpudb/connectors/odbc/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_CAT           | TABLE_SCHEM         | TABLE_NAME          | TABLE_TYPE          | REMARKS             |
+---------------------+---------------------+---------------------+---------------------+---------------------+
| Kinetica            | MASTER              | nyctaxi             | TABLE               |                     |
| Kinetica            | SYSTEM              | ITER                | TABLE               |                     |
+---------------------+---------------------+---------------------+---------------------+---------------------+

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

SQL> SELECT COUNT(*) FROM nyctaxi;

+---------------------+
| EXPR_0              |
+---------------------+
| 500000              |
+---------------------+

You can exit this mode by typing quit.

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

Tableau¶

Install the ODBC Client - see ODBC Installation above
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¶

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

To read in data using the ODC file created above:

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

Java Client (via JDBC)¶

Download the JDBC client JAR, kinetica-jdbc-7.0.<X>.<Y>-jar-with-dependencies.jar, from the JDBC Client Project on GitHub.
Install a Java SQL client:
- SQuirreL SQL
  - Download SQuirreL SQL
  - Read this to adjust the fonts
- DBeaver (v7.1.1)
  - 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.kinetica.jdbc.Driver
    - URL Template: jdbc:kinetica://{host}:{port}
      - The use of {host} & {port} variables will instruct DBeaver to prompt for host & port when creating a new connection.
    - Default Port: 9191
    - Click Add File and select the downloaded kinetica-jdbc-7.0.<X>.<Y>-jar-with-dependencies.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 the Host (hostname or IP) & Port of the Kinetica instance to connect to
    - Enter a User name (admin for database administrator connection)
    - Enter the Password for the database user
    - Click Finish to complete the connection configuration
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:kinetica://<database.host>:<database.port>
  - e.g., jdbc:kinetica://127.0.0.1:9191
- Class Name: com.kinetica.jdbc.Driver
  
  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.

Secure Connections¶

When connecting to a Kinetica instance over HTTPS, the connection string should be updated to reference the head node secure address.

ODBC¶

Modify the URL parameter to use the HTTPS-enabled database endpoint:

URL: https://<database.host>:<database.sslport>/gpudb-0

For example:

URL: https://localhost:8082/gpudb-0

JDBC¶

Add a URL parameter to the JDBC connection string to use the HTTPS-enabled database endpoint, replacing the standard hostname & port references:

jdbc:kinetica:URL=https://<database.host>:<database.sslport>/gpudb-0

For example:

jdbc:kinetica:URL=https://127.0.0.1:8082/gpudb-0

Failover Connections¶

When connecting to a Kinetica instance with one or more failover clusters, the connection string should be updated to contain a comma-delimited list of head node addresses for all clusters. The URL of the primary cluster can also be specified.

ODBC¶

Modify the URL parameter to contain all cluster head node addresses and add a PrimaryURL parameter designating the cluster that should be tried first:

URL: http://<database1.host>:<database1.port>,...,http://<databaseN.host>:<databaseN.port>
PrimaryURL: http://<databaseX.host>:<databaseX:port>

For example:

URL: http://kineticaA:9191,http://kineticaB:9191,http://kineticaC:9191
PrimaryURL: http://kineticaB:9191

JDBC¶

Add a URL parameter to the JDBC connection string to contain all cluster head node addresses and add a PrimaryURL parameter designating the cluster that should be tried first:

jdbc:kinetica:URL=http://<database1.host>:<database1.port>,...,http://<databaseN.host>:<databaseN.port>;PrimaryURL=http://<databaseX.host>:<databaseX:port>

For example:

jdbc:kinetica:URL=http://kineticaA:9191,http://kineticaB:9191,http://kineticaC:9191;PrimaryURL=http://kineticaB:9191

Logging¶

Kinetica logs inbound SQL requests, internal calls made on behalf of those requests, and other debugging information. These logs can be found in the following log files:

/opt/gpudb/core/logs/host-manager-<hostname>-<datetime>.log: Contains all endpoint calls, including the initial call to /execute/sql and any endpoint calls made as a result of servicing the request
/opt/gpudb/sql/logs/sql-engine-<kinetica-hostname>.log: Contains detailed error messages

Table Of Contents

The ODBC/JDBC Connector¶

Installation¶

Linux¶

Installing unixODBC¶

Running iSQL¶

Windows¶

Configuration¶

Connecting¶

KiSQL¶

iSQL in Linux¶

Tableau¶

Excel¶

Java Client (via JDBC)¶

Secure Connections¶

ODBC¶

JDBC¶

Failover Connections¶

ODBC¶

JDBC¶

Logging¶