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();
   }
}

Auto Resume

To enable Auto Resume while establishing a connection using the JDBC Driver, include auto-resume=true as a parameter in the connection string. This ensures that the cluster automatically resumes when a query is executed, preventing the need for manual intervention.

Note: Auto Resume must also be enabled at the cluster level for this setting to take effect. If Auto Resume is not enabled at the cluster level, the cluster will not resume automatically, even if the connection string includes the auto-resume=true parameter.

jdbc:e6data://<HOSTNAME>:<PORT>/database=<DATABASE>&catalog=<CATALOG_NAME>&secure=true&cluster-uuid=<CLUSTER_UUID>&auto-resume=true