JDBC

HeavyDB supports JDBC connections.

Resources

You can connect securely to a JDBC session by defining the database URL (a colon-separated connection string with optional query string) and the keys/values in DriverManager.getConnection properties.

You can define key values separated by colons in the main part of the connection string , or as key/value pairs separated by an ampersand (&) in an optional query string following the ? character.

jdbc:omnisci:<host_name> must be defined in the main part of the connection string.

Valid keys in the connection URL are the same as those in the properties object. If defined in the main part of the connection string, they must appear in the following order:

• host_name - Name of the host. Must be defined in the main part of the connection string.

• port_num - Port number

• db_name - Name of the database.

• protocol: <binary|binary_tls|http|https|https_insecure>

  • To connect using a secure connection through binary mode, the HEAVY.AI server must be running with an encrypted main port open.
  • Use binary TLS mode to explicitly connect using SSL with the default Java trust stores.
  • To connect using HTTPS, the HEAVY.AI web server must be running with HTTPS enabled.
  • To create an encrypted connection with no server validation, use HTTPS_INSECURE.
    Note: Using HTTPS_INSECURE is not recommended in a production environment.

  For more information, see HEAVY.AI server configuration.

• server_trust_store:`` trust_store_path - The path to a Java trust store holding the HEAVY.AI server public certificate.

• server_trust_store_pwd:`` password - The password to the trust store.

• pkiauth: true | false - Enable PKI authentication. If true:

  • The client X509 public certificate is supplied to the server for verification. Use the sslcert parameter to supply a file containing the certificate.
  • The user parameter and the password parameters are ignored and should be blank.

• sslcert`` certificate-path - Path to the certificate and private key file, in PKCS12 format.

• sslkey`` key-path - Path to the SSL key.

• sslkey_password password - Password for the SSL key defined in sslkey`` key-path.

• max_rows`` max_rows - The maximum number of rows to return.

Examples

You can use the following connection URLs to connect to an HEAVY.AI database called test_DB using port 6274 with max_rows set to 10000.

jdbc:heavyai:localhost:6274:test_DB?max_rows=10000

jdbc:heavyai:localhost:6274?db_name=test_DB&max_rows=10000

If duplicate information is supplied in different sources of connection information, sources have priority as follows:

1. The main part of the URL.
2. The query portion of the URL.
3. The Properties object.

In the following connection URL, the database test_DB would be used, and if logging is turned on, the driver would issue a warning:

jdbc:heayai:localhost:6274?dbname=test_DB&max_rows=10000&db_name=test_WRONGDB

You can also provide all options and parameters for a JDBC connection in a Java properties file; for example:

server_trust_store=/tls_certs/server/trust_store_server.jks
server_trust_store_password=jks_truststore_password
pkiauth=true
sslcert=/tls_certs/client1_ca_primary_signed/client1.p12
sslkey_password=ssl_certfile_password
protocol=https

Supported JDBC Methods

Unsupported Features

• Transaction statements
• Cursors
• Multiple result sets
• Domains
• Rules
• Database procedures
• Indexes
• Keys
• Constraints

Example

This example uses the JDBC interface to create a flight information database, then reads carrier information from the database.

Sequence

The key steps are:

1. Set connection parameters and credentials:

   static final String JDBC_DRIVER = "ai.heavy.jdbc.HeavyAIDriver";
   static final String DB_URL = "jdbc::localhost:6274:heavyai";
   static final String USER = "myUserName";
   static final String PASS = "myPassWord";

2. Register the JDBC driver:

   Class.forName(JDBC_DRIVER);

3. Open a connection:

   server connection:conn = DriverManager.getConnection(DB_URL, USER, PASS);

4. Create a table, add data, and query the database:

   stmt = conn.createStatement(); 
   String sql = "CREATE table flights(arr_timestamp timestamp, dep_timestamp timestamp, uniquecarrier varchar(50))";
   stmt.executeUpdate(sql); 
   sql = "insert into flights values('2017-04-23 06:30:0’, '2017-04-23 07:45:00’, 'Southwest’)";
   stmt.executeUpdate(sql);
   sql = "insert into flights values('2017-04-23 06:50:0’, '2017-04-23 09:45:00’, 'American’)";
   stmt.executeUpdate(sql);
   sql = "insert into flights values('2017-04-23 09:30:0’,’ 2017-04-23 12:45:00’, 'United’)";stmt.executeUpdate(sql); 
   sql = "SELECT uniquecarrier from flights";
   ResultSet rs = stmt.executeQuery(sql);

5. Extract data from the result set:

   while (rs.next()) { 
     String uniquecarrier = rs.getString("uniquecarrier"); 
     System.out.println("uniquecarrier: " + uniquecarrier);
   }

Compile and Run

Dependency components are also required in the classpath.

javac Flights.java
java -cp /opt/heavyai/bin/heavyai-jdbc-6.4.1.jar;. Flights_Demo.java

Source Code

1
import java.sql.Connection;
2
import java.sql.DriverManager;
3
import java.sql.ResultSet;
4
import java.sql.SQLException;
5
import java.sql.Statement;
6
7
public class Flights {
8
static final String JDBC_DRIVER = "ai.heavy.jdbc.HeavyAIDriver";
9
static final String DB_URL = "jdbc:heavyai:localhost:6274:omnisci";
10
static final String USER = "admin";
11
static final String PASS = "HyperInteractive";
12
13
public static void main(String[] args) throws SQLException {
14
  Connection conn = null;
15
  Statement stmt = null;
16
  try {
17
    Class.forName(JDBC_DRIVER);
18
19
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
20
21
    stmt = conn.createStatement();
22
    String sql = "CREATE TABLE if not exists flights_java(arr_timestamp timestamp, dep_timestamp timestamp, uniquecarrier varchar(50))";
23
    stmt.executeUpdate(sql);
24
    sql = "insert into flights_java values('2017-04-23 06:30:0', '2017-04-23 07:45:00', 'Southwest')";
25
    stmt.executeUpdate(sql);
26
    sql = "insert into flights_java values('2017-04-23 06:50:0', '2017-04-23 09:45:00', 'American')";
27
    stmt.executeUpdate(sql);
28
    sql = "insert into flights_java values('2017-04-23 09:30:0','2017-04-23 12:45:00', 'United')";
29
    stmt.executeUpdate(sql);
30
    sql = "SELECT uniquecarrier from flights_java";
31
    ResultSet rs = stmt.executeQuery(sql);
32
33
    while (rs.next()) {
34
      String uniquecarrier = rs.getString("uniquecarrier");
35
      System.out.println("uniquecarrier: " + uniquecarrier);
36
    }
37
38
    rs.close();
39
    stmt.close();
40
    conn.close();
41
  } catch (SQLException se) {
42
    //Handle errors for JDBC
43
    se.printStackTrace();
44
  } catch (Exception e) {
45
    e.printStackTrace();
46
    if (stmt != null)
47
        stmt.close();
48
    if (conn != null)
49
        conn.close();
50
   }
51
}