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
Last updated