ErgoTechWiki

This page explains how to connect MIStudio or TransSECS to a Databricks SQL Warehouse using JDBC.

The same Databricks JDBC driver can be used by both products, but the driver installation and connection setup differ between MIStudio and TransSECS.

In MIStudio, the connection can be used in the Logic Editor to read data from Databricks, execute SQL statements, write database data, and send database results to other application components.

In TransSECS, Databricks can also be used as a historical destination for SECS/GEM event and report data.

A typical Databricks connection requires:

• A Databricks workspace
• A Databricks SQL Warehouse
• Permission to use the SQL Warehouse
• The warehouse Server Hostname
• The warehouse HTTP Path
• The Databricks JDBC driver
• A Databricks authentication method

The Databricks JDBC driver class is:

com.databricks.client.jdbc.Driver

The exact setup depends on whether the connection is being created in:

• MIStudio
• TransSECS

Sign in to your Databricks workspace.

Open the SQL Warehouse that MIStudio or TransSECS should use.

In Databricks:

• Open SQL Warehouses.
• Select the SQL Warehouse.
• Open Connection Details.
• Locate the Server Hostname.
• Locate the Port.
• Locate the HTTP Path.

Keep these values available while configuring the connection.

Typical connection information includes:

Download the Databricks JDBC Driver from Maven Central:

Databricks JDBC Driver on Maven Central

Databricks publishes the JDBC driver under:

com.databricks:databricks-jdbc

The downloaded file follows this naming pattern:

databricks-jdbc-<version>.jar

For example:

databricks-jdbc-3.4.2.jar

The JDBC driver class is:

com.databricks.client.jdbc.Driver

For MIStudio projects, the Databricks JDBC driver can be added directly to the project.

In the MIStudio project tree:

• Select Drivers.
• Click Add File.
• Browse to the Databricks JDBC JAR downloaded in the previous step.
• Select the JAR and add it to the project.
• Confirm that the driver file appears under Drivers.
• Save the project.
• Close and restart MIStudio.

The driver file follows this naming pattern:

databricks-jdbc-<version>.jar

TransSECS Builder loads additional libraries from its Builder classpath.

For the verified TransSECS build 11026 installation, the Databricks JDBC JAR was placed in:

MIStudioSuite\TransSECS\Builder\resources

For example:

C:\Users\Public\ErgoTech\TransSECSDevicesTrial_11026\MIStudioSuite\TransSECS\Builder\resources\databricks-jdbc-3.4.2.jar

If the resources directory does not exist, create it.

The TransSECS Builder launch script includes:

./resources/*

on its Java classpath.

A missing restart may result in:

java.lang.ClassNotFoundException:
com.databricks.client.jdbc.Driver

or:

Cannot Connect to Database.
No Driver Exists for "com.databricks.client.jdbc.Driver".

After restarting Builder, the driver should load normally.

When a TransSECS deployment is built, verify that the Databricks JDBC JAR is also included in the generated deployment directory.

For example:

GEMHostDeployment\databricks-jdbc-3.4.2.jar

Databricks supports multiple JDBC authentication methods.

For testing and development, a Databricks Personal Access Token (PAT) can be used.

For PAT authentication:

A Personal Access Token can be useful for testing and development.

Production environments may require another authentication method based on the organization's Databricks security requirements.

Open the MIStudio project and application that will use Databricks.

Open the MIStudio Logic Editor and add a:

DatabricksConnectionManager

Configure the connection manager using the connection information from the Databricks SQL Warehouse.

Enter the appropriate values for:

• Server Hostname
• Port
• HTTP Path
• User Name
• Password
• Driver Class
• Connection Properties
• Catalog or Schema, if required by the application

For the JDBC Driver Class, use:

com.databricks.client.jdbc.Driver

The Server Hostname should be entered without the protocol.

For example:

YOUR-SERVER-HOSTNAME

Do not enter:

https://YOUR-SERVER-HOSTNAME

Copy the HTTP Path from the Databricks SQL Warehouse Connection Details.

The HTTP Path should include the leading slash, for example:

/sql/1.0/warehouses/YOUR-WAREHOUSE-ID

In the verified TransSECS build 11026 configuration, a Databricks-specific item was not required in the Devices Add menu.

A standard DatabaseConnection was used and named:

DatabricksConnection

Configure the DatabaseConnection with:

The Database URL can include the Databricks server, warehouse HTTP path, authentication mechanism, catalog, and schema.

Example structure:

jdbc:databricks://YOUR-SERVER-HOSTNAME:443/default;httpPath=/sql/1.0/warehouses/YOUR-WAREHOUSE-ID;AuthMech=3;transportMode=http;ssl=1;ConnCatalog=YOUR-CATALOG;ConnSchema=YOUR-SCHEMA

For example, a connection using the catalog workspace and schema default would contain:

ConnCatalog=workspace;
ConnSchema=default

For PAT-based authentication, the following properties are commonly used:

AuthMech=3
transportMode=http
ssl=1

The User Name should contain:

token

The Password should contain the Databricks Personal Access Token.

For TransSECS, catalog and schema can also be included in the JDBC URL:

ConnCatalog=workspace
ConnSchema=default

Change these values to match the required Databricks catalog and schema.

MIStudio must be running the application logic for database components to execute.

Before testing a Databricks connection in MIStudio:

• Confirm that DatabricksConnectionManager is configured.
• Add a DatabaseRawLookup component to the Logic Editor.
• Configure DatabaseRawLookup to use the Databricks connection manager.
• Put MIStudio into Simulating mode.

The connection manager reference will normally point to the DatabricksConnectionManager in the application.

For example:

/Main/DatabricksConnectionManager

For TransSECS SECS/GEM testing, the host application must be running in LIVE mode.

When TransSECS successfully opens the Databricks JDBC connection, the console may show a message similar to:

Connection: "SparkSQL" Version: 3.3.3 Driver: "DatabricksJDBC"

A Java warning related to Apache Arrow reflective access may also appear when using Java 11.

For example:

Illegal reflective access by
com.databricks.internal.apache.arrow.memory.util.MemoryUtil

This warning did not prevent the verified Databricks connection from operating successfully.

Start with a simple query before attempting a larger database operation.

In DatabaseRawLookup, enter:

SELECT 1

Trigger the DatabaseRawLookup while MIStudio is in Simulating mode.

If the connection is successful, the query should return:

1

You can also check the Databricks SQL Warehouse Query History to confirm that the query was received and executed.

After confirming the connection, determine which Databricks catalog and schema are being used.

Use DatabaseRawLookup with:

SELECT current_catalog()

Then check the schema:

SELECT current_schema()

These values identify the current location used by unqualified SQL queries.

Databricks objects can also be referenced using fully qualified names:

catalog.schema.table

For example:

my_catalog.my_schema.my_table

Using fully qualified table names can help prevent queries from running against the wrong catalog or schema.

Use DatabaseRawLookup for SQL statements that return data.

Common examples include:

• `SELECT`
• Metadata queries
• Catalog or schema checks
• Table checks
• Queries whose results will be sent to other Logic Editor components

Example:

SELECT *
FROM my_catalog.my_schema.my_table

Use DatabaseRawWrite for SQL statements that create or modify database content.

Common examples include:

• `CREATE TABLE`
• `INSERT`
• `UPDATE`
• `DELETE`

For example:

CREATE TABLE IF NOT EXISTS my_catalog.my_schema.mistudio_test (
    id INT,
    message STRING,
    created_at TIMESTAMP
)

After creating a test table, insert a value using DatabaseRawWrite.

For example:

INSERT INTO my_catalog.my_schema.mistudio_test
    (id, message, created_at)
VALUES
    (1, 'Hello from MIStudio', CURRENT_TIMESTAMP())

Then use DatabaseRawLookup to read the value back:

SELECT message
FROM my_catalog.my_schema.mistudio_test
WHERE id = 1

A successful readback confirms that MIStudio can:

• Connect to the Databricks SQL Warehouse
• Execute SQL
• Write database data
• Read database data

A complete TransSECS-to-Databricks historical workflow was verified with:

• TransSECS build 11026
• Java 11.0.31
• Databricks JDBC 3.4.2
• HSMS communication on port 5010
• SECS Device ID 1
• Databricks catalog workspace
• Databricks schema default
• Table secs_poc

The test architecture was:

GEM Tool
    |
    | HSMS / SECS-GEM
    | Port 5010
    v
TransSECS Host
    |
    | JDBC
    v
Databricks SQL Warehouse
    |
    v
workspace.default.secs_poc

The test used Report ID:

101

Report 101 contained:

The STARTED collection event used:

CEID 7501

and was linked to Report 101.

The host configured the equipment using SECS messages including:

• S2F33 to create reports
• S2F35 to link reports to events
• S2F37 to enable events

A Triggered Historical device named:

Historical

was configured beneath:

DatabricksConnection

The verified configuration used:

The Databricks table contained:

ts
LOTID
PPID
SetPoint
WaferCount
CEID
EventName

The verified test values were:

The equipment sent an S6F11 event containing:

S6F11 W
  CEID 7501
  Report 101
    LOTID       LOT_001
    PPID        Recipe_A
    SetPoint    50
    WaferCount  1

TransSECS acknowledged the event with:

S6F12 <B 0x0>

Run:

SELECT
    ts,
    LOTID,
    PPID,
    SetPoint,
    WaferCount,
    CEID,
    EventName
FROM workspace.default.secs_poc
ORDER BY ts DESC
LIMIT 10

The verified successful row was:

LOTID       LOT_001
PPID        Recipe_A
SetPoint    50
WaferCount  1
CEID        7501
EventName   STARTED

This confirms the complete path:

GEM Tool
   |
   | S6F11
   v
TransSECS 11026
   |
   | Databricks JDBC 3.4.2
   v
Databricks
   |
   v
workspace.default.secs_poc

When changing values in the StandAloneGEMTool interface, make sure the edited field value is committed before sending the event.

During testing, LOTID appeared visually as:

LOT_001

but the first S6F11 contained:

<A ''>

for LOTID.

After committing the field value, the following event correctly contained:

<A 'LOT_001'>

and Databricks stored the expected LOTID.

If a Databricks row contains an unexpected blank value, inspect the actual S6F11 payload before troubleshooting the Databricks connection.

Use the MIStudio database component that matches the SQL operation.

Databricks SQL Warehouse Query History can be useful when testing the connection.

Query History can help confirm that:

• MIStudio or TransSECS reached Databricks
• The SQL statement was received
• The query executed
• An error occurred on the Databricks side

If an operation appears to execute but the expected result is not visible, check Databricks Query History as part of troubleshooting.

Confirm that:

• The Databricks JDBC JAR appears under Drivers in the MIStudio project.
• The correct driver JAR was added.
• The project was saved after adding the driver.
• MIStudio was restarted after the JDBC driver was added.
• The Driver Class is:

com.databricks.client.jdbc.Driver

If TransSECS reports:

ClassNotFoundException:
com.databricks.client.jdbc.Driver

confirm that:

• The Databricks JDBC JAR exists in the Builder resources directory.
• The JAR contains:

    com/databricks/client/jdbc/Driver.class
    

• TransSECS Builder was completely closed after the JAR was added.
• Builder was restarted after the JAR was placed in resources.
• The Driver Class is:

    com.databricks.client.jdbc.Driver
    

Also verify that the generated deployment contains the JDBC JAR.

If Databricks returns:

403 Forbidden
Invalid access token

the JDBC driver has reached Databricks, but authentication failed.

Confirm that:

• The Personal Access Token is current.
• The User Name is:

    token
    

• The Password contains the PAT.
• `AuthMech=3` is configured.
• The token has not expired or been revoked.

Confirm:

• Server Hostname
• Port
• HTTP Path
• User Name
• Password or authentication credential
• Connection Properties
• SQL Warehouse permissions
• SQL Warehouse availability

For PAT authentication, confirm that the User Name is:

token

and that the Password contains the Databricks Personal Access Token.

Check the HSMS port.

For the verified example, the GEM Tool listened on:

5010

Before the host connects, the port should show:

Listen

After the TransSECS host connects, the port should show both:

Listen
Established

A successful HSMS startup should include a Select Request and Select Response followed by:

HSMS_SS_VALID_SELECT_RESPONSE -- Successful Select

or an equivalent successful communication-established message.

Confirm that the correct MIStudio database component is being used.

Use:

• DatabaseRawLookup for queries that return data.
• DatabaseRawWrite for queries that modify database content or structure.

Confirm the current catalog and schema:

SELECT current_catalog()

SELECT current_schema()

If necessary, use the complete table name:

catalog.schema.table

For MIStudio, check that:

• MIStudio is in Simulating mode.
• The correct DatabricksConnectionManager is selected.
• The SQL Warehouse connection information is correct.
• The JDBC driver was added to the project and MIStudio was restarted.

For TransSECS, check that:

• TransSECS is in LIVE mode.
• The DatabaseConnection opened successfully.
• The JDBC JAR is loaded.
• The SQL Warehouse connection information is correct.
• The historical component is configured to use the correct DatabaseConnection.
• The expected SECS event was actually received.

Then check the Databricks SQL Warehouse Query History.

Database credentials and Databricks access tokens should be handled securely.

Do not include credentials in:

• Wiki documentation
• Screenshots
• Source control
• Shared project files
• Issue reports
• Example configuration files
• Console output copied into public or shared locations

If an access token is accidentally exposed, revoke it and generate a replacement.

Use the authentication method required by your organization for production systems.

Databricks SQL Warehouse
          |
          v
Databricks JDBC Driver
          |
          v
MIStudio Project Drivers
          |
          v
DatabricksConnectionManager
          |
          +--> DatabaseRawLookup
          |        |
          |        +--> SELECT / Read Data
          |
          +--> DatabaseRawWrite
                   |
                   +--> CREATE / INSERT / UPDATE / DELETE

GEM Equipment / GEM Tool
          |
          | HSMS / SECS-GEM
          v
TransSECS Host
          |
          | Event / Report Data
          v
Triggered Historical
          |
          v
DatabaseConnection
          |
          | Databricks JDBC
          v
Databricks SQL Warehouse

The following configuration was verified successfully on September 28, 2026:

The successful test confirmed:

• Java 11 runtime startup
• Databricks JDBC driver loading
• Databricks authentication
• HSMS connection establishment
• SECS/GEM communication
• Report configuration
• Event configuration
• S6F11 STARTED event reception
• S6F12 acknowledgment
• Triggered Historical processing
• Successful Databricks row insertion

• MIStudio Logic Editor
• Database Connections
• Data Sources Overview
• Connecting a Data Source to a Graphic
• Servers and Data Sources