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:
|
|
|
|
To create a connection, using a connection string:
|
|
|
|
|
|
|
|
|
|
To create a connection, using properties:
|
|
|
|
|
|
|
|
|
|
JDBC Clients
JDBC connections require the Kinetica JDBC driver.
Acquire the JDBC client JAR, kinetica-jdbc-7.2.<X>.<Y>-jar-with-dependencies.jar, from either:
- the JDBC Client Project on GitHub
- under /opt/gpudb/connectors/jdbc on default Kinetica installations
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:
|
|
|
|
- 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.
|
|
|
|
|
|
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.
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.
|
|
|
|
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.
- 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:Syntax
1
http://<db.host>:9191;CombinePrepareAndExecute=1[[;<parameter>=<value>]*]
Example1
http://localhost:9191;CombinePrepareAndExecute=1;RowsPerFetch=20000
- Leave User and Password blank
- Click OK
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 to use the HTTPS-enabled database endpoint.
|
|
|
|
|
|
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.
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:
|
|
|
|
PrimaryURL:
|
|
|
|
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 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
- 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
DBeaver
Acquire the JDBC Driver
Download & install DBeaver
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 JAR:
kinetica-jdbc-7.2.<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 >
- Click Connection Details and name the connection
- Connection name: name for the connection
- Click Back
- Complete the connection configuration
- JDBC URL:Syntax
1
jdbc:kinetica:URL=http://<db.host>:9191;CombinePrepareAndExecute=1[[;<parameter>=<value>]*]
Example1
jdbc:kinetica:URL=http://kineticahost:9191;CombinePrepareAndExecute=1;RowsPerFetch=20000
- JDBC URL:
- Enter authentication credentials
- Username/password style authentication
- Username: name of connecting user (admin for database administrator connection)
- Password: password for connecting user
- OIDC/OAuth style authentication
- Disable username/password auth
- Save password: check this box, leaving Username & Password blank
- Click the Driver Properties tab
- Enter OAuth credentials
- OAuthToken: OAuth token to use for authentication
- Disable username/password auth
- Username/password style authentication
- 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.:
|
|
|
|
Some settings of note follow.
Standard Parameters
Config Parameter | Default Value | Description |
---|---|---|
URL | http://127.0.0.1:9191 | URL of Kinetica database server to connect to--this may be a comma-delimited list of multiple clusters in an HA configuration |
CombinePrepareAndExecute | 0 | 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).
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 | |
PWD | Password for database connection Note If the password contains special characters, they must be encoded using URL-style encoding; e.g., encode a semicolon as %3B | |
OAuthToken | OAuth token used in OIDC-enabled environments; when using, leave UID & PWD blank | |
Timeout | 0 | Minutes 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 Parameter | Description |
---|---|
CostBasedOptimization | Whether or not to use the cost-based optimizer
|
DistributedJoins | Whether or not to process operations that would require resharding of data to complete successfully
|
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
|
LateMaterialization | Whether or not to avoid materializing intermediary result sets when processing a complex query
|
PagingTableTtl | TTL for the result set paging table, in minutes; other possible values:
|
ParallelExecution | Whether or not to execute independent portions of a query in parallel
|
PlanCache | Whether or not to cache query plans, as derived by the rule-based/cost-based optimizer
|
ResultsCaching | Whether or not to cache result sets, which happens automatically for certain queries that have only materialized views as sources
|
RuleBasedOptimization | Whether or not to use the rule-based optimizer
|
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
|
TTL | TTL for temporary result tables, in minutes. Other possible values:
|
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
|
UseApproxCountDistinct | When issuing any COUNT (DISTINCT x) query, it will be computed using the faster but less accurate APPROX_COUNT_DISTINCT() aggregate function.
|
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.
|
Client Parameters
The following optional parameters may be used to change the ODBC/JDBC Client behavior.
Config Parameter | Default Value | Description |
---|---|---|
CombineResults | 0 | How to return the results of multiple DDL statements in a single Execute call. (JDBC only)
|
DisableAutoDiscovery | 0 | Whether to disable querying the server for its configuration. (JDBC only) Disabling auto-discovery will, in turn, disable both multi-head ingest & multi-head egress, as the client won't have the list of URLs for the cluster nodes, required for distributed operations. Disabling auto-discovery will also disable high availability failover, preventing attempts at performing operations on another cluster after a failure.
|
DisableFailover | 0 | Whether to disable high availability failover, preventing attempts at performing operations on another cluster after a failure. (JDBC only)
|
DisableMultiheadInsert | 0 | Whether to use distributed ingest for supported INSERT statements. (JDBC only)
|
DisableSnappy | 0 | Whether to disable Snappy compression when transmitting data to the server.
|
ErrorMode | Skip | 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.
|
FakeTransactions | 0 | Whether to throw exceptions when the client attempts to start or rollback a transaction; useful for 3rd party tools, such as JPA or Hibernate, that expect transaction support. (JDBC only)
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.
| |
FileReadInitialClear | 0 | When 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 | \N | When 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. |
FileReadSkip | 0 | When inserting data from a file with INSERT INTO ... SELECT ... FROM FILE, skip this number of lines after the header (if any). |
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. |
LogLevel | 0 | Controls the amount of debug logging done by driver.
|
NoSync | 0 | Whether to disable HA operations for this connection.
|
ReadOnly | 0 | 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.
|
RowsPerFetch | 10000 | Number of records to request from the server at a time. |
RowsPerInsertion | 10000 | Number of records to send to the server in a batch. |
Schema | Use the specified schema as the default schema for all object references | |
TimeZoneOverride | UTC | Time zone that client should assume time coming from the server are in. (JDBC only) All times in Kinetica are assumed to be in UTC (GMT). Java clients should call: TimeZone.setDefault(TimeZone.getTimeZone("UTC")); so that times will be displayed in UTC and match other clients. When this is not possible (e.g., when using a Java utility that does not have such an option) Java clients will usually display times in the local time (set in Java and/or the OS), which will look different from the times displayed by other clients. I.e., the Java client will display the UTC times from Kinetica converted to the local time zone. The TimeZoneOverride option can be used to report times coming from Kinetica as being in the specified time zone, rather than in UTC. If this is set to the current Java/OS time zone then no conversions will be done, and displayed times should match UTC times as stored in Kinetica. Valid options include system and time zone codes such as EST and PST. Tip Use system to match the current Java client time zone and avoid needing to look up the time zone code for your area. |
TruncateStrings | 0 | Whether to truncate strings when processing an INSERT.
|
UseKeyLookup | 0 | 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.
|
SSL Parameters
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
|
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) |
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