The Ocient® 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. For details about data extracting, see Data Extract Tool.

For a list of commands available in the JDBC CLI, see Commands Supported by the Ocient JDBC CLI Program.

Prerequisites

This software is required for the JDBC driver.

Driver Features

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

Invoke the Ocient JDBC CLI Program

If your system meets all the necessary prerequisites, you can run the JDBC CLI by using the Ocient JDBC JAR file. To do this, follow these steps.

java -classpath <path_to_jar_with_dependencies> com.ocient.cli.CLI [<username> [<password>]]

java -classpath ./ocient-jdbc4-2.10-jar-with-dependencies.jar com.ocient.cli.CLI testuser testpassword

 Username: admin@system
 Password: admin

 Ocient> _

 Ocient> connect to jdbc:ocient://10.10.1.1:4050/system;

 Connected to jdbc:ocient://10.10.1.1:4050/system
 Ocient> _

JDBC CLI Configuration Options

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

key1=value1
key2=value2
...
keyN=valueN

The JDBC CLI supports these options.

For supported commands, see Commands Supported by the Ocient JDBC CLI Program.

Use the Ocient JDBC Driver in Java Programs

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

Class.forName("com.ocient.jdbc.JDBCDriver");

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.

Connect Using JDBC

The Ocient JDBC driver supports connection properties that can be supplied using the CONNECT command in the JDBC CLI or as a properties object passed in a Java application.

JDBC URL Example

This connection string includes various connection properties that trail the login credentials.

CONNECT TO 'jdbc:ocient://db.example.com:6432/salesdb;user=admin@system;password=admin;logLevel=INFO;networkTimeout=15000;enableBulkLoad=true;bulkLoadThreshold=50000';

Java DriverManager Example

Alternatively, you can use the DriverManager class to provide connection properties if you are using a JDBC connection as part of a Java application.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class OcientConnectionExample {
    public static void main(String[] args) throws SQLException {
        // Base JDBC URL: host, port, and database
        String url = "jdbc:ocient://db.example.com:6432/salesdb";
        // JDBC connection properties
        Properties props = new Properties();
        props.setProperty("user", "analytics_user");            // Ocient username
        props.setProperty("password", "StrongPassw0rd!");       // Ocient password
        props.setProperty("logLevel", "INFO");                  // Driver logging verbosity
        props.setProperty("networkTimeout", "15000");           // 15s network timeout (ms)
        props.setProperty("enableBulkLoad", "true");            // Use bulk load for large batches
        props.setProperty("bulkLoadThreshold", "50000");        // Min rows for bulk loa
        try (Connection conn = DriverManager.getConnection(url, props)) {
        }
    }
}

Supported JDBC Connection Properties

The JDBC driver supports these connection parameters.

JDBC Bulk Loading

For very large batches, the Ocient JDBC driver provides a high-speed bulk load path. When you enable bulk loading, the driver bypasses the standard multi-row insert operation and instead streams the batch data directly to the Loader Nodes in the cluster using SSH/SFTP. The data loads in the in-memory JSON Lines format using a temporary, system-generated Ocient pipeline, providing a massive performance boost for large-scale data ingestion.

Configuration

Follow these steps to configure bulk loading in your JDBC driver.

SSH Key Access and Node

JDBC bulk loading does not use password authentication. Instead, you must use SSH access for all Loader Nodes in the cluster.

To point the driver to your private SSH key file, use the bulkLoadSshKeyPath JDBC connection property. The client application must be able to read this file.

The driver automatically discovers available Loader Nodes by querying the sys.nodes and sys.service_roles system catalog tables.

Data Type Mapping

The JDBC driver supports all standard scalar types.

For complex types, Java SQL STRUCT (java.sql.Struct) types load as Ocient TUPLE types, and Java ARRAY (java.sql.Array) types load as Ocient ARRAY types.

Enable Bulk Loading

The JDBC driver disables bulk loading by default. To enable it, you must meet these conditions:

• Set the enableBulkLoad connection property to true.
• Use a parameterized INSERT statement that uses placeholders for values, e.g., INSERT INTO customers (id, name, status) VALUES (?, ?, ?). For details, see Set Up Parameterized Insert Statements.
• The total number of rows in the batch group meets or exceeds the bulkLoadThreshold property.

Set Up Parameterized INSERT Statements

These steps demonstrate how to use a single parameterized INSERT statement using the Java PreparedStatement class. The parameterized statement binds different values for each row.

String sql = "INSERT INTO customers (id, name, status) VALUES (?, ?, ?)";
PreparedStatement ps = conn.prepareStatement(sql);

ps.setLong(1, 123L);        // First ?
ps.setString(2, "Alice");   // Second ?
ps.setString(3, "ACTIVE");  // Third ?

ps.executeUpdate();

// First row
ps.setLong(1, 123L);
ps.setString(2, "Alice");
ps.setString(3, "ACTIVE");
ps.addBatch();
// Second row
ps.setLong(1, 124L);
ps.setString(2, "Bob");
ps.setString(3, "INACTIVE");
ps.addBatch();
// Send them together
int[] results = ps.executeBatch();

ps.close();

Connection Encryption (SSL/TLS)

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 Using TLS discusses how you can configure user-defined certificates for the Ocient System.

Sample Java Program Using the Ocient JDBC Driver

This sample program demonstrates how to utilize the Ocient JDBC driver to establish a connection to a database, construct a prepared SQL statement, execute the query, and iterate through the result set.

public class OcientJDBCExample {
     public static void main(final String args[]) {
          Class.forName("com.ocient.jdbc.JDBCDriver");
          Properties props = new Properties();
          props.setProperty("user", "username");
          props.setProperty("password", "pwd");
          props.setProperty("force", "true");
          String url = "jdbc:ocient://192.168.121.82:4050/db";
          Connection conn = DriverManager.getConnection(url, props);
          PreparedStatement pstmt = conn.prepareStatement(
                              "select l_orderkey from tpch.lineitem where l_linenumber = ?");
          Pstmt.setInt(1, 4);
          ResultSet rs = pstmt.executeQuery();
          while(rs.next()){
               // do something with row
          }
          rs.close();
          pstmt.close();
          conn.close();
          return;
     }
}

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

Related Links

Connect Using JDBC

Data Extract Tool

Commands Supported by the Ocient JDBC CLI Program

JDBC Classes and Methods.

**Linux®is the registered trademark of Linus Torvalds in the U.S. and other countries.