Code Samples

Java code snippets to carry out common operations on e6data via JDBC Driver

Execute a Query

Code snippet to execute a query and get a result from e6data.

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class Main {

   public static void main(String[] args) throws SQLException, ClassNotFoundException {

       Class.forName("io.e6.jdbc.driver.E6Driver");
       Properties properties = new Properties();
       properties.put("user", "<USER>"); // copy the username from e6data cluster connection info page in the e6data console
       properties.put("password", "<PASSWORD"); // password is the Personal Access Token (PAT) generated in the e6data cluster connector tab

       String connectStr = "jdbc:e6data://<HOSTNAME>:<PORT>/database=<DATABASE>&catalog=<CATALOG_NAME>&secure=true&cluster-uuid=<CLUSTER UUID>";
       Connection connection = DriverManager.getConnection(connectStr, properties);

       Statement statement = connection.createStatement();
       ResultSet resultSet = statement.executeQuery("Select current_timestamp");

       ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
       while (resultSet.next()) {
           int columnCount = resultSetMetaData.getColumnCount();
           List<String> currentLine = new ArrayList<>();
           for (int i = 1; i <= columnCount; i++) {
               Object object = resultSet.getObject(i);
               if (object == null) {
                   currentLine.add("null");
               } else {
                   currentLine.add(object.toString());
               }

           }
           String value = String.join(" | ", currentLine);
           System.out.println(value);
       }

       resultSet.close();
       statement.close();
       connection.close();
   }
}

Get Query Time Metrics

The following code runs a query and returns the amount of time taken to execute the query in seconds.

import org.apache.commons.lang3.time.StopWatch;
import java.sql.*;
import java.util.*;

public class JDBCQueryTime {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        Class.forName("io.e6.jdbc.driver.E6Driver");
        Properties properties = new Properties();
        properties.put("user", "<USER>"); // copy the username from e6data cluster connection info page in the e6data console
        properties.put("password", "<PASSWORD>"); // password is the Personal Access Token (PAT) generated in the e6data cluster connector tab

        String connectStr = "jdbc:e6data://<HOSTNAME>:<PORT>/database=<DATABASE>&catalog=<CATALOG_NAME>&secure=true&cluster-uuid=<CLUSTER UUID>";

        Connection connection = DriverManager.getConnection(connectStr, properties);

        Statement statement = connection.createStatement();
        StopWatch stopWatch = StopWatch.createStarted();
        ResultSet resultSet =  statement.executeQuery("<QUERY>"); // add the SQL query text here

        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        while (resultSet.next()) {
            int columnCount = resultSetMetaData.getColumnCount();
            List<String> currentLine = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                Object object = resultSet.getObject(i);
                if (object == null) {
                    currentLine.add("null");
                } else {
                    currentLine.add(object.toString());
                }

            }
            String value = String.join(" | ", currentLine);
            System.out.println(value);
        }
        stopWatch.stop();
        long TimeTaken = stopWatch.getTime();
        System.out.println("Time Taken in milliseconds: " + TimeTaken);

        resultSet.close();
        statement.close();
        connection.close();
    }
}

Use of Timestamps

To use timestamps in queries that are run using the JDBC driver the timezone must be set to UTC. e.g.:

 Timestamp ts_start = new Timestamp(
                ZonedDateTime.of(2020, 04, 07, 05, 26, 21, 0,
                        ZoneId.of("UTC")
                ).toInstant().toEpochMilli());
        Timestamp ts_end = new Timestamp(
                ZonedDateTime.of(2020, 04, 07, 05, 27, 07, 0,
                        ZoneId.of("UTC")
                ).toInstant().toEpochMilli());

        statement.setObject(1, ts_start);
        statement.setObject(2, ts_end);

        ResultSet resultSet = statement.executeQuery();

Advanced Use Case - Execute a Parameterized Query

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class Main {

   public static void main(String[] args) throws SQLException, ClassNotFoundException {

       Class.forName("io.e6.jdbc.driver.E6Driver");
       Properties properties = new Properties();
       properties.put("user", "<USER>"); // copy the username from e6data cluster connection info page in the e6data console
       properties.put("password", "<PASSWORD>"); // password is the Personal Access Token (PAT) generated in the e6data cluster connector tab

       String connectStr = "jdbc:e6data://<HOSTNAME>:<PORT>/database=<DATABASE>&catalog=<CATALOG_NAME>&secure=true&cluster-uuid=<CLUSTER UUID>"; // get the connection details from the e6data cluster connector tab
       Connection connection = DriverManager.getConnection(connectStr, properties);

       String sql = "select count(*) from date_dim where d_year between ? and ?";
       PreparedStatement statement = connection.prepareStatement(sql);

       statement.setObject(1, 2000);
       statement.setObject(2, 2001);

       ResultSet resultSet = statement.executeQuery();

       ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
       while (resultSet.next()) {
           int columnCount = resultSetMetaData.getColumnCount();
           List<String> currentLine = new ArrayList<>();
           for (int i = 1; i <= columnCount; i++) {
               Object object = resultSet.getObject(i);
               if (object == null) {
                   currentLine.add("null");
               } else {
                   currentLine.add(object.toString());
               }

           }
           String value = String.join(" | ", currentLine);
           System.out.println(value);
       }

       resultSet.close();
       statement.close();
       connection.close();
   }
}