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.
jdbc:e6data://<HOSTNAME>:<PORT>/database=<DATABASE>&catalog=<CATALOG_NAME>&secure=true&cluster-uuid=<CLUSTER_UUID>&auto-resume=trueLast updated