Proper way to collect Statement ID from JDBC Connection

harripy
Databricks Partner

‎04-22-2024 11:10 PM

Hi, 

We are executing DML calls on Databricks SQL Warehouse programmatically, with Java and Python.

There can be thousands of executions running on daily level, so in case of an error occurs, it would be very beneficial to spot the Statement ID of the failed execution.

We just recently discovered that the Statement ID can be retrieved on Python SDK as follows from the connection cursor:

 

if cursor and cursor.active_op_handle is not None:
    queryId = UUID(bytes= cursor.active_op_handle.operationId.guid)

 

But what would be equivalent for doing the same on Java SDK?

We are getting the Java Connection from the DriverManager.getConnection(jdbcUrl, props) -call.

0 Kudos

harripy
Databricks Partner
In response to Retired_mod

‎04-23-2024 06:49 AM

Thanks @Retired_mod for the suggestion, but I still would need the Statement ID for further analysis (as Query History performs best with direct ID). - Any way to Unwrap the java.sql.Connection interface to the implementing class and getting the metadata through it?

0 Kudos

oprime
New Contributor II

‎03-12-2025 10:57 AM

Found a way to extract it for below dbx-java library.

-> databricks-jdbc library version : `2.6.32`

    private static String extractQueryIdFromDbxStatement(Hive42PreparedStatement statement) {
        byte[] guid = ((HiveJDBCNativeQueryExecutor) statement.getQueryExecutor())
                .getExeContext()
                .m_fetchResultsReq
                .getOperationHandle()
                .getOperationId()
                .getGuid();


        return (guid != null && guid.length > 0) ?
                formatGuid(HexFormat.of().formatHex(guid)) :
                EMPTY_STRING;
    }

    private static String formatGuid(String input) {
        if (input.length() != 32) return input;
        return String.format("%s-%s-%s-%s-%s",
                input.substring(0, 8),
                input.substring(8, 12),
                input.substring(12, 16),
                input.substring(16, 20),
                input.substring(20, 32));
    }

In short - get preparedStatement from the connection obtained from DriverManager above like 

PreparedStatement pstmt = conn.prepareStatement(query);

& then pass preapredStatement to `extractQueryIdFromDbxStatement` method shown above - which will either return formatted statement-id or a empty string if not found.

To be on safer side - use try/catch for extracting & handle errors as your desire.

0 Kudos