ODBC/JDBC Connector Guide

Kinetica provides a SQL interface through its ODBC & JDBC drivers, which run on Linux, Mac, & Windows. For details on the extent of SQL compliance, see the SQL section.

Connecting

KiSQL

KiSQL is a Kinetica JDBC-based SQL CLI client that is available on GitHub. Details can be found on the Kinetica SQL (KiSQL) page.

JDBC Applications

Java applications which use JDBC can reference the Kinetica JDBC driver as a pom.xml dependence:

Repository
1
2
3
4
5
6
<repositories>
    <repository>
        <id>gpudb-releases</id>
        <url>https://nexus.kinetica.com/repository/releases/</url>
    </repository>
</repositories>
Dependency
1
2
3
4
5
6
7
8
<dependencies>
    <dependency>
        <groupId>com.kinetica</groupId>
        <artifactId>kinetica-jdbc</artifactId>
        <version>[7.1.0.0,7.2.0.0-SNAPSHOT)</version>
        <type>jar</type>
    </dependency>
</dependencies>

To create a connection, using a connection string:

HTTPS w/o Cert Check
1
2
3
4
5
Class.forName("com.kinetica.jdbc.Driver");

String connStr = "jdbc:kinetica:URL=https://<aws.fqdn>/<aws.cluster.name>/gpudb-0";

kdb = DriverManager.getConnection(connStr, "<username>", "<password>");
HTTPS w/ Default Trust Cert Check
1
2
3
4
5
6
Class.forName("com.kinetica.jdbc.Driver");

String connStr = "jdbc:kinetica:URL=https://<aws.fqdn>/<aws.cluster.name>/gpudb-0";
connStr += ";BypassSslCertCheck=0";

kdb = DriverManager.getConnection(connStr, "<username>", "<password>");
HTTPS w/ Custom Trust Cert Check
1
2
3
4
5
6
Class.forName("com.kinetica.jdbc.Driver");

String connStr = "jdbc:kinetica:URL=https://<aws.fqdn>/<aws.cluster.name>/gpudb-0";
connStr += ";BypassSslCertCheck=0;SslCACertPath=<certPath>;SslCACertPassword=<certPass>";

kdb = DriverManager.getConnection(connStr, "<username>", "<password>");

To create a connection, using properties:

HTTPS w/o Cert Check
1
2
3
4
5
6
7
8
Class.forName("com.kinetica.jdbc.Driver");

Properties props = new Properties();
props.put("URL", "https://<aws.fqdn>/<aws.cluster.name>/gpudb-0");
props.put("UID", "<username>");
props.put("PWD", "<password>");

kdb = DriverManager.getConnection("jdbc:kinetica://", props);
HTTPS w/ Default Trust Cert Check
1
2
3
4
5
6
7
8
9
Class.forName("com.kinetica.jdbc.Driver");

Properties props = new Properties();
props.put("URL", "https://<aws.fqdn>/<aws.cluster.name>/gpudb-0");
props.put("UID", "<username>");
props.put("PWD", "<password>");
props.put("BypassSslCertCheck", 0);

kdb = DriverManager.getConnection("jdbc:kinetica://", props);
HTTPS w/ Custom Trust Cert Check
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
Class.forName("com.kinetica.jdbc.Driver");

Properties props = new Properties();
props.put("URL", "https://<aws.fqdn>/<aws.cluster.name>/gpudb-0");
props.put("UID", "<username>");
props.put("PWD", "<password>");
props.put("BypassSslCertCheck", 0);
props.put("SslCACertPath", "<certPath>");
props.put("SslCACertPassword", "<certPass>");

kdb = DriverManager.getConnection("jdbc:kinetica://", props);

JDBC Clients

JDBC connections require the Kinetica JDBC driver.

Download the JDBC client JAR, kinetica-jdbc-7.1.<X>.<Y>-jar-with-dependencies.jar, from the JDBC Client Project on GitHub.

The following is the general driver and connection information that should be used when configuring a client.

  • Driver: <point to the downloaded JAR file>
  • Connection String:
Syntax
1
jdbc:kinetica:URL=https://<aws.fqdn>/<aws.cluster.name>/gpudb-0;CombinePrepareAndExecute=1[[;<parameter>=<value>]*]
Example
1
jdbc:kinetica:URL=https://abcdefg.cloud.kinetica.com/hijklmn/gpudb-0;CombinePrepareAndExecute=1;RowsPerFetch=20000
  • Class Name: com.kinetica.jdbc.Driver

See ODBC/JDBC Configuration for additional configuration parameters.


Secure Connections

When connecting to a Kinetica instance over HTTPS, the connection string should reference the database's secure address.

Tip

See SSL Parameters for additional parameters that may be necessary to use when connecting over SSL.

Set the URL parameter on the JDBC connection string to use the HTTPS-enabled database endpoint.

HTTPS w/o Cert Check
1
jdbc:kinetica:URL=https://<aws.fqdn>/<aws.cluster.name>/gpudb-0
HTTPS w/ Default Trust Cert Check
1
jdbc:kinetica:URL=https://<aws.fqdn>/<aws.cluster.name>/gpudb-0;BypassSslCertCheck=0
HTTPS w/ Custom Trust Cert Check
1
jdbc:kinetica:URL=https://<aws.fqdn>/<aws.cluster.name>/gpudb-0;BypassSslCertCheck=0;SslCACertPath=<certPath>;SslCACertPassword=<certPass>

ODBC

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 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):

    • Description: Kinetica

    • URL:

      Syntax
      1
           
      https://<aws.fqdn>/<aws.cluster.name>/gpudb-0;CombinePrepareAndExecute=1[[;<parameter>=<value>]*]
           
      Example
      1
           
      https://abcdefg.cloud.kinetica.com/hijklmn/gpudb-0;CombinePrepareAndExecute=1;RowsPerFetch=20000
           
    • Leave User and Password blank

  7. Click OK

See ODBC/JDBC Configuration for additional configuration parameters.

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 use the user's default schema, 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

DBeaver

  1. Acquire the JDBC Driver
  2. Download & install DBeaver
  3. Configure DBeaver with the Kinetica JDBC driver
    • On the Database menu, select Driver Manager
    • Click New to create a new driver connection
    • Complete the driver configuration page:
      • Driver Name: Kinetica
      • Class Name: com.kinetica.jdbc.Driver
      • Click the Libraries tab; then click Add File and select the downloaded kinetica-jdbc-7.1.<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 >

    • Complete the connection configuration page:

      • JDBC URL:

        Syntax
        1
              
        jdbc:kinetica:URL=https://<aws.fqdn>/<aws.cluster.name>/gpudb-0;CombinePrepareAndExecute=1[[;<parameter>=<value>]*]
              
        Example
        1
              
        jdbc:kinetica:URL=https://abcdefg.cloud.kinetica.com/hijklmn/gpudb-0;CombinePrepareAndExecute=1;RowsPerFetch=20000
              
      • Username: name of connecting user (kadmin for database administrator connection)

      • Password: password for connecting user

      • Click Connection Details and enter a Connection name; then, click Back

    • Click Finish to complete the connection configuration

Other JDBC Clients

For other clients, see JDBC Applications for the general setup to use.

Tip

There are many Java SQL clients available; a list can be found here.

ODBC/JDBC Configuration

Unless noted, the configuration parameters are the same for ODBC & JDBC, though the locations of the configurations are not.

  • 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.:
Syntax
1
jdbc:kinetica:URL=https://<aws.fqdn>/<aws.cluster.name>/gpudb-0;CombinePrepareAndExecute=1[[;<parameter>=<value>]*]
Example
1
jdbc:kinetica:URL=https://abcdefg.cloud.kinetica.com/hijklmn/gpudb-0;CombinePrepareAndExecute=1;RowsPerFetch=20000

Some settings of note follow.

Standard Parameters

Config ParameterDefault ValueDescription
URLhttp://127.0.0.1:9191URL of Kinetica database server to connect to--this may be a comma-delimited list of multiple clusters in an HA configuration
CombinePrepareAndExecute0

Whether or not to combine the JDBC/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

Tip

This parameter should be set to 1 whenever possible. Some JDBC clients may return odd errors when this is active, however; and may need to be set to 0 to function properly.

This parameter is not needed when using KiSQL.

UID User ID for database connection (blank to use default credentials)
PWD 

Password for database connection (blank to use default credentials)

Note

If the password contains special characters, they must be encoded using URL-style encoding; e.g., encode a semicolon as %3B

Timeout0Minutes to wait for a response from the database; 0 for no timeout
Schema<user default>Default schema to use within the session
ImpersonateUser Impersonate another user for this session. Only available when connected as an Admin.
PrimaryURL URL of a cluster to always attempt to go to first (if URL is a list)

Server Override Parameters

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.

The default value for each of these options is 0.

Config ParameterDescription
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
IgnoreExistingPk

Whether or not to suppress errors for record collisions when inserting into or updating a table with a primary key; if the specified table does not have a primary key or UpdateOnExistingPk is 1, then this parameter is ignored

  • 0 = Treat as errors any inserted or updated records whose primary keys collide with those of existing records
  • 1 = Ignore inserted or updated records whose primary key values collide with those of existing records
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
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)
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
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 handle primary key collisions using an upsert scheme for inserts and overwrite scheme for updates when operating on a table with a primary key; if the specified table does not have a primary key, then this parameter is ignored

  • 0 = Reject any INSERT or UPDATE that results in a primary key collision with an existing record
  • 1 = Use the inserted or updated record to replace an existing record with the same primary key when there is a primary key collision between the two
UseApproxCountDistinct

When issuing any COUNT (DISTINCT x) query, it will be computed using the faster but less accurate APPROX_COUNT_DISTINCT() aggregate function.

  • 0 = Compute and return accurate COUNT DISTINCT statistics
  • 1 = Return faster but less accurate COUNT DISTINCT values
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 1, fail the request if the data will not fit; if 0, 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

Client Parameters

The following optional parameters may be used to change the ODBC/JDBC Client behavior.

Config ParameterDefault ValueDescription
CombineResults0

How to return the results of multiple DDL statements in a single Execute call (JDBC only)

  • 0 = Return individual result counts for each DDL statement
  • 1 = Combine result counts into a single result
DisableAutoDiscovery0

Whether to query the server to find related clusters (JDBC only)

  • 0 = Perform standard auto-discovery
  • 1 = Do not perform auto-discovery
DisableFailover0

Whether to attempt operations on another cluster after a failure (JDBC only)

  • 0 = Do attempt to perform the operation on another cluster after a failure, if another cluster is configured
  • 1 = Do not attempt to perform the operation on another cluster after a failure
DisableMultiheadInsert0

Whether to use distributed ingest for supported INSERT statements (JDBC only)

  • 0 = Use high-performance distributed ingest when possible, unless KI_HINT_SERVER_SIDE_INSERT is specified
  • 1 = Use normal INSERT processing
ErrorModeSkip

What to do when an error is encountered when inserting data by default, via either INSERT INTO ... VALUES or INSERT INTO ... SELECT ... FROM FILE. Can be overridden on the SQL Command.

  • Permissive = If an error is encountered parsing a source record, attempt to insert as much of the valid fields from the record as possible.
  • Skip = If an error is encountered parsing a source record, skip the record.
  • Abort = If an error is encountered parsing a source record, stop the data load process. Primary key collisions are considered abortable errors in this mode.
FakeTransactions0

Whether to throw exceptions when the client attempts to start or rollback a transaction (JDBC only); useful for 3rd party tools, such as JPA or Hibernate, that expect transaction support

  • 0 = Throw an exception when client attempts to control a transaction
  • 1 = Do not throw an exception, but still ignore, if client attempts to control a transaction

Important

Kinetica does not support transactions; this will only affect how the JDBC driver responds to transactional requests it can't fulfill.

FileReadComment#When inserting data from a file with INSERT INTO ... SELECT ... FROM FILE, this character at the start of a line indicates a commented line to ignore
FileReadDelimiter,When inserting data from a file with INSERT INTO ... SELECT ... FROM FILE, this character separates values If reading from a ".tsv" or ".psv" file, the default will be <tab> or |, respectively
FileReadEscapeChar When inserting data from a file with INSERT INTO ... SELECT ... FROM FILE, this character can be placed before other special characters to not treat them specially. Default is no escape character.
FileReadHasHeader 

When inserting data from a file with INSERT INTO ... SELECT ... FROM FILE, whether to assume the first line in the file is a header line

  • 0 = The first line in the file is not a header line, treat it as data
  • 1 = The first line in the file is a header line, and indicates column names
  • If unspecified, the first line will be read and treated as a header if it looks like a header
FileReadInitialClear0When inserting data from a file with INSERT INTO ... SELECT ... FROM FILE, delete any existing data in the destination table before inserting new data
FileReadLimit When inserting data from a file with INSERT INTO ... SELECT ... FROM FILE, stop reading the file after this many lines. Default is no limit.
FileReadNullString\NWhen inserting data from a file with INSERT INTO ... SELECT ... FROM FILE, this string represents data that should be inserted as NULL values
FileReadQuoteChar"When inserting data from a file with INSERT INTO ... SELECT ... FROM FILE, this character is used to delimit string literals
FileReadSkip0When inserting data from a file with INSERT INTO ... SELECT ... FROM FILE, skip this number of lines after the header (if any)
Limit-1Limit number of records returned for each query issued; -1 for no limit, i.e., all records will be returned for each query
LogLevel0

Controls the amount of debug logging done by driver

  • 0 = Off
  • 1 = Fatal
  • 2 = Error
  • 3 = Warning
  • 4 = Info
  • 5 = Debug
  • 6 = Trace
NoSync0

Whether to disable HA operations for this connection

  • 0 = Perform operations normally, synchronously and asynchronously, as configured
  • 1 = Do not perform any HA operations--commands will only affect the target cluster
ReadOnly0

Whether the driver will report itself as being in a read-only mode, even though it is in full-access mode; useful for 3rd party tools that expect and enforce a read-only connection

  • 0 = Driver will not report itself as being in a read-only mode when queried via the JDBC/ODBC API
  • 1 = Driver will report itself as being in a read-only mode, even though this is not enforced
RowsPerFetch10000Number of records to request from the server at a time
RowsPerInsertion10000Number of records to send to the server in a batch
TruncateStrings0

Whether to truncate strings when processing an INSERT

  • 0 = Do not truncate strings -- if a string is too long, it will not be inserted
  • 1 = Truncate strings when needed so they will fit in a restricted target string column
UseKeyLookup0

Whether to process supported SELECT statements using high-performance distributed key lookup; using this may cause some additional overhead to determine if the table will support key lookups

  • 0 = By default, use standard processing for all SELECT statements; this can be overridden with KI_HINT_KEY_LOOKUP on individual statements
  • 1 = Use high-performance distributed key lookup for supported SELECT statements, when possible

SSL Parameters

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

Config ParameterDefault ValueDescription
SslAllowHostMismatch0

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
SslCACertPath Path to client's trust store, used to validate the certificate from the Kinetica server (ODBC only)
TrustedStorePath Path to client's trust store, used to validate the certificate from the Kinetica server (JDBC only)
SslCACertPassword Password to the certificate store specified by TrustedStorePath (JDBC only)