Consultas de execução usando o driver JDBC (OSS)
Este artigo contém exemplos que mostram ao senhor como executar uma consulta de forma síncrona e assíncrona.
Exemplo: execução de uma consulta
O exemplo a seguir mostra como usar o Databricks JDBC Driver (OSS) para executar uma consulta Databricks SQL usando um recurso Databricks compute .
Java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Properties;
public class DatabricksJDBCExample {
public static void main(String[] args) {
Class.forName("com.databricks.client.jdbc.Driver");
// Set JDBC URL properties
String jdbcUrl = "jdbc:databricks://dbc-a1b2345c-d6e7.cloud.databricks.com:443";
Properties connectionProperties = new Properties();
connectionProperties.put("httpPath", "sql/protocolv1/o/123456780012345/0123-123450-z000pi22");
connectionProperties.put("ssl", "1");
// Set authentication properties (personal access token)
connectionProperties.put("AuthMech", "3");
connectionProperties.put("user", "token");
connectionProperties.put("password", "12345678901234667890abcdabcd");
// Set logging properties
connectionProperties.put("logPath", "logs/myapplication.log");
// Establish connection and execute query
try (Connection connection = DriverManager.getConnection(jdbcUrl, connectionProperties);
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT * FROM samples.nyctaxi.trips")) {
// Get metadata and column names
ResultSetMetaData metaData = resultSet.getMetaData();
String[] columns = new String[metaData.getColumnCount()];
for (int i = 0; i < columns.length; i++) {
columns[i] = metaData.getColumnName(i + 1);
}
// Process and print the result set
while (resultSet.next()) {
System.out.print("Row " + resultSet.getRow() + "=[");
for (int i = 0; i < columns.length; i++) {
if (i != 0) {
System.out.print(", ");
}
System.out.print(columns[i] + "='" + resultSet.getObject(i + 1) + "'");
}
System.out.println("]");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
Exemplo: execução de uma consulta de forma assíncrona
Os exemplos a seguir mostram como usar o Databricks JDBC Driver (OSS) para executar e manipular uma consulta assíncrona do Databricks SQL.
Para obter referência da API, consulte Referência da API Java para o Databricks JDBC Driver (OSS).
Inicie a execução assíncrona de uma instrução:
Java
Statement statement = conn.createStatement();
IDatabricksStatement dbStatement = statement.unwrap(IDatabricksStatement.class);
ResultSet result = dbStatement.executeAsync(sql);
IDatabricksResultSet asyncResult = result.unwrap(IDatabricksResultSet.class);
IExecutionStatus asyncStatus = asyncResult.getExecutionStatus();
long startTime = System.currentTimeMillis();
while ((asyncStatus.getExecutionState() == ExecutionState.RUNNING | asyncStatus.getExecutionState() == ExecutionState.PENDING) || (startTime + timeout < System.currentTimeMillis())) {
Thread.sleep(1000); // Sleep for 1000 ms
asyncResult = dbStatement.getExecutionResult().unwrap(IDatabricksResultSet.class);
asyncStatus = asyncResult.getExecutionStatus();
}
if (asyncStatus.getExecutionStatus() == ExecutionState.RUNNING | ExecutionState.PENDING) {
dbStatement.cancel();
}
if (asyncStatus.getExecutionStatus() == ExecutionState.SUCCEEDED) {
// process result set
}
if (asyncStatus.getExecutionStatus() == ExecutionState.FAILED) {
String sqlState = asyncStatus.getSqlState();
String errorMessage = asyncStatus.getErrorMessage();
// log error code and message
}
Manipule uma declaração em um tópico separado:
Java
Statement statement = conn1.createStatement();
IDatabricksStatement dbStatement = statement.unwrap(IDatabricksStatement.class);
ResultSet asyncResult = dbStatement.executeAsync(sql);
IDatabricksResultSet drs = asyncResult.unwrap(IDatabricksResultSet.class);
String statementId = drs.getStatementId();
ExecutionState state = drs.getExecutionStatus().getExecutionState();
while (state != ExecutionState.SUCCEEDED) {
Thread.sleep(sleepInterval);
asyncResult = dbStatement.getExecutionResult();
state = asyncResult.unwrap(IDatabricksResultSet.class).getExecutionStatus().getExecutionState();
}
// In another thread
IDatabricksConnection dbConn2 = conn2.unwrap(IDatabricksConnection.class);
IDatabricksStatement asyncStatementHandle = dbConn2.getStatement(statementId).unwrap(IDatabricksStatement.class);
IDatabricksResultSet asyncResultHandle = asyncStatementHandle.getExecutionResult().unwrap(IDatabricksResultSet.class);
// Cancel if needed
if (asyncResultHandle.getExecutionStatus().getExecutionState() == ExecutionState.PENDING | asyncResultHandle.getExecutionStatus().getExecutionState() == ExecutionState.RUNNING) {
asyncStatementHandle.cancel();
}
Feche uma conexão usando um ID de conexão:
Java
// Get connection-Id from existing connection
String connectionId = conn.unwrap(IDatabricksConnection.class).getConnectionId();
// Close the connection from other thread using same JDBC Url and connection properties and connection-Id retrieved from above
com.databricks.client.jdbc.Driver.getInstance().closeConnection(jdbcUrl, properties, connectionId);