The JDBC Driver and command-line interface (CLI) enable you to connect to Ocient using a JDBC connection. Ensure that you meet the prerequisites before using the Ocient JDBC Driver. Then, invoke the CLI program, configure options, and connect to a database using the driver. You can also use the data extract tool to extract a result set to delimited files in the target location.

The Ocient JDBC connector supports these features as of the current version.

You can invoke the CLI using these commands.

The user can supply no credentials, a username only, or a username and password. Throughout this document, angle brackets (< >) indicate name parameters chosen by the user and square brackets ([ ]) indicate that the enclosed text is optional. Parentheses are literal.

If you do not specify the username or password, the system prompts you to enter it on startup.

You can invoke the JDBC CLI with the Ocient JDBC JAR file only. Java 8 or later is required. You can download Ocient JDBC JAR files at the Maven repository.

The system requires the JAR file with dependencies for the CLI program. The JAR file name must be: ocient-jdbc4-<version number>-jar-with-dependencies.jar. After you download the file, this command invokes the JDBC CLI.

This command shows the full example using the 2.10 version of the JAR file in the current working directory.



The JDBC CLI reads a configuration file consisting of key-value pairs located at ~/.ocient-cli-configuration with this format.

This option is supported.

For supported commands, see .

First, you must load the Ocient driver class with this statement in a Java program using the JDBC driver.

The driver class is located in the JDBC driver JAR file named ocient-jdbc4.jar and must be available in the CLASSPATH defined for the program.

The JDBC driver can use SSL/TLS to connect to Ocient, causing all traffic to be encrypted. Specify the tls property on the connect statement to enable TLS support. The tls property supports these values.

unverified Traffic on the connection is encrypted, but no verification is done on the certificate received from the Ocient system.

on Traffic is encrypted, and the JDBC client must be able to verify the certificate received from the Ocient system.

The TLS on mode requires that the client knows the Certificate Authority that signed the certificate provided by the Ocient system. Typically, this mode requires either that the certificate is signed by a well known certificate authority, or the Certificate Authority certificate has been imported into the truststore of the Java system. The Java keytool utility is used to manipulate a Java truststore.

Secure Connections via TLS discusses how you can configure user-defined certificates for the Ocient system.

This sample program illustrates how to use the Ocient JDBC driver to connect to a database, build a prepared SQL statement, execute the query, and loop through the result set.

For supported classes and methods, see Supported JDBC Classes and Methods.

The data extract tool is a feature within the JDBC driver. You can execute the tool directly from the JDBC CLI. The tool extracts a result set to delimited files in the target location. To invoke the JDBC CLI, please see the JDBC Manual.

The general structure of an extract command is:

The command is case insensitive. Each extract command must start with EXTRACT TO. The location type needs to follow and must be either LOCAL or S3. You can enclose additional options within a pair of parenthesis following the word OPTIONS. Note that location type is the only required option. Next, the query follows the word AS.

A simple example of the general command structure is:

For supported options, see Extract Options.

The general format of options is:

You need to follow certain guidelines when you specify options. Keys (option names) can only consist of alphanumeric characters and are unquoted. Values can be either quoted (with the reserved character ") or unquoted. If values are unquoted, they can only contain alphanumeric characters. If the value has a non-alphanumeric character, you must quote it with the reserved character ". Note that the single quote character does not work.

To use the reserved quote character " as an argument, you must escape it with the backslash character \. To use \ as an argument, you must escape it with another \. This code illustrates both of these scenarios.

When you use the extract tool, the tool produces a number of files. If the tool extracts with a single thread, the tool names files in this convention: {file_prefix}_{file_number}{file_extension}{gzip_extension}

If you use the multithreaded extract, then the naming convention is: {file_prefix}{thread_number}_{file_number}{file_extension}

file_prefix, file_extension, gzip_extension is still determined with the set options. thread_number is the number of the thread going from 0 to N - 1 where N is the number of threads specified. The file number is now calculated on a per thread basis with the number of rows given to that thread. Recall that rows are distributed to threads in a round robin order, starting with thread 0. So, thread 0 receives the rows 0, N, 2N, 3N, etc. Thread 1 receives the rows 1, N + 1, 2N + 1, 3N + 1, etc.

OPTIONS(...) is only required when you specify additional options. This example extracts the results of SELECT c1 FROM sys.dummy10 to the local machine at the relative path result-0.csv.

This example extracts the results of SELECT c1 FROM sys.dummy10 to the local machine at the absolute path /home/user/out/data_0.csv.

Note that in this example, the result set has 100 rows and MAX_ROWS_PER_FILE is set to 10. Thus the tool writes 10 files to S3 with the keys query_results/query_0/_results_{file_number}.csv.gzip where file_number ranges from 0 to 9, inclusive.