ErgoTechWiki

This page explains how to connect TransSECS to a Databricks SQL Warehouse using the Databricks JDBC driver.

The connection can be used with TransSECS database and historical components to store SECS/GEM event and report data in Databricks.

A complete SECS/GEM-to-Databricks workflow was verified using:

• TransSECS build 11026
• Java 11.0.31
• Databricks JDBC 3.4.2
• HSMS port 5010
• SECS Device ID 1
• Triggered Historical
• Databricks table workspace.default.secs_poc

The verified workflow is:

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

In the verified example, the GEM Tool sends a STARTED event using CEID 7501.

That event contains Report 101, which includes:

TransSECS receives the S6F11 event, acknowledges it with S6F12, and writes the event values to Databricks.

The workflow was verified both with TransSECS Builder in LIVE mode and with the packaged GEMHostDeployment runtime running independently of Builder.

Sign in to the Databricks workspace.

Open the SQL Warehouse that 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.

Typical connection information includes:

Download the Databricks JDBC Driver from Maven Central:

Databricks JDBC Driver on Maven Central

The Maven artifact is:

com.databricks:databricks-jdbc

The driver JAR follows this naming pattern:

databricks-jdbc-<version>.jar

The version verified with TransSECS build 11026 and Java 11.0.31 was:

databricks-jdbc-3.4.2.jar

The JDBC driver class is:

com.databricks.client.jdbc.Driver

TransSECS Builder loads additional libraries from its Builder classpath.

Place the Databricks JDBC JAR in:

MIStudioSuite\TransSECS\Builder\resources

For example, the verified build 11026 installation used:

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.

An example PowerShell setup for the verified driver is:

$resources = "C:\Users\Public\ErgoTech\TransSECSDevicesTrial_11026\MIStudioSuite\TransSECS\Builder\resources"
 
New-Item -ItemType Directory -Path $resources -Force | Out-Null
 
$dst = Join-Path $resources "databricks-jdbc-3.4.2.jar"
 
Invoke-WebRequest `
  -Uri "https://repo1.maven.org/maven2/com/databricks/databricks-jdbc/3.4.2/databricks-jdbc-3.4.2.jar" `
  -OutFile $dst
 
Get-Item $dst | Select-Object FullName, Length

Optional: verify that the JAR contains the expected Databricks JDBC driver class:

Add-Type -AssemblyName System.IO.Compression.FileSystem
 
$zip = [System.IO.Compression.ZipFile]::OpenRead($dst)
 
$zip.Entries |
    Where-Object { $_.FullName -eq "com/databricks/client/jdbc/Driver.class" } |
    Select-Object FullName
 
$zip.Dispose()

Expected result:

com/databricks/client/jdbc/Driver.class

If Builder was not restarted, TransSECS may report:

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

or:

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

For the verified TransSECS build 11026 configuration, a standard DatabaseConnection was used.

The connection was named:

DatabricksConnection

Configure the DatabaseConnection with:

A PAT-authenticated JDBC URL can use the following structure:

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

The verified connection properties included:

AuthMech=3
transportMode=http
ssl=1
ConnCatalog=workspace
ConnSchema=default

For PAT authentication:

Add or configure a Triggered Historical device beneath the Databricks DatabaseConnection.

The verified device was named:

Historical

The known-good configuration was:

The historical device writes to the configured Databricks catalog and schema.

The verified destination was:

workspace.default.secs_poc

Because Create Table is disabled in the verified Historical configuration, the Databricks table must already exist with all columns that TransSECS will write.

The verified table schema is:

CREATE TABLE workspace.default.secs_poc (
    ts TIMESTAMP,
    LOTID STRING,
    PPID STRING,
    SetPoint BIGINT,
    WaferCount BIGINT,
    CEID BIGINT,
    EventName STRING
);

If the table already exists, do not recreate it.

The original POC added the event metadata columns using:

ALTER TABLE workspace.default.secs_poc
ADD COLUMNS (CEID BIGINT);

ALTER TABLE workspace.default.secs_poc
ADD COLUMNS (EventName STRING);

The expected columns are:

ts
LOTID
PPID
SetPoint
WaferCount
CEID
EventName

The verified STARTED event uses:

CEID 7501

STARTED is linked to Report:

101

Report 101 contains:

On LIVE startup, TransSECS configures the tool using SECS messages including:

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

A successful setup should receive normal acknowledgements including:

• S2F34
• S2F36
• S2F38

The Java 11 TransSECS build includes a StandAloneGEMTool that can be used to simulate equipment.

The verified simulator source was:

C:\Users\Public\ErgoTech\TransSECSDevicesTrial_11026\StandAloneGEMTool

The verified Java runtime was:

C:\Users\Public\ErgoTech\TransSECSDevicesTrial_11026\MIStudioSuite\jre\bin\java.exe

The simulator can be run from a temporary copy so the original source remains unchanged:

$src = 'C:\Users\Public\ErgoTech\TransSECSDevicesTrial_11026\StandAloneGEMTool'
 
$jre = 'C:\Users\Public\ErgoTech\TransSECSDevicesTrial_11026\MIStudioSuite\jre\bin\java.exe'
 
$run = Join-Path $env:TEMP ("GEMTool11026_" + (Get-Date -Format 'yyyyMMdd_HHmmss'))
 
Copy-Item $src $run -Recurse
 
Set-Location $run
 
& $jre `
 "-Dlog4j.configuration=log4j.console.xml" `
 -cp ".;*" `
 com.ergotech.mix.client.ContainerApplet

Expected result:

• The yellow GEM Tool window opens.
• The tool listens on port 5010.
• Device ID is 1.

Some older-tool warnings may appear during simulator startup, including persistence or SQLite warnings. These did not prevent the verified HSMS event test.

Use PowerShell:

Get-NetTCPConnection -LocalPort 5010 -ErrorAction SilentlyContinue |
    Select-Object LocalAddress, LocalPort, RemoteAddress, RemotePort, State, OwningProcess

Before the host connects, the expected state is:

Listen

If nothing is returned, the simulator is not currently listening on port 5010.

Start Builder from its Builder directory.

For the verified build 11026 installation:

Set-Location "C:\Users\Public\ErgoTech\TransSECSDevicesTrial_11026\MIStudioSuite\TransSECS\Builder"
 
.\scripts\run_trans.bat

The verified startup reported:

TransSECS build id: 11026
JVM Version: 11.0.31

The Windows batch file may print an error for a line beginning with:

#

This was non-blocking in the verified build 11026 test.

The following startup warning was also non-blocking in the verified test:

"mistudio.hs" is not available.

The Java 11 POC project used during verification was:

C:\Users\Public\ErgoTech\TransSECSDevicesTrial_11026\Projects\GEMHost_Databricks_POC_Java11\GEMHost.tsx

The verified Tool Attributes were:

The Devices tree contained:

DatabricksConnection
    |
    +-- Historical

The verified end-to-end test used TransSECS Builder in:

LIVE

mode.

When the Databricks JDBC connection opens successfully, TransSECS should report a line similar to:

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

A Java warning similar to the following may appear immediately before the successful connection:

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

This warning was non-blocking in the verified Java 11 test.

Run:

Get-NetTCPConnection -LocalPort 5010 -ErrorAction SilentlyContinue |
    Select-Object LocalAddress, LocalPort, RemoteAddress, RemotePort, State, OwningProcess

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

Listen
Established

A successful HSMS connection should also include:

HSMS_SS_VALID_SELECT_RESPONSE -- Successful Select

followed by the normal GEM communications handshake:

S1F13
S1F14

In the yellow GEM Tool, enter:

Click:

Send Selected Event

The expected SECS message is an S6F11 containing CEID 7501 and Report 101:

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

TransSECS should acknowledge 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 contained:

The clean Builder LIVE verification produced:

2026-09-28T09:31:56.291+00:00
LOT_001
Recipe_A
50
1
7501
STARTED

The packaged GEMHostDeployment runtime was later verified independently and produced:

2026-09-28T11:02:28.000+00:00
LOT_001
Recipe_A
50
1
7501
STARTED

This confirms the complete path:

GEM Tool
    |
    | S6F11
    v
TransSECS 11026 / Java 11
    |
    | Triggered Historical
    v
Databricks JDBC 3.4.2
    |
    v
workspace.default.secs_poc

After changing the project, use:

Tools → Build

The verified build 11026 build completed successfully.

The deployment output was:

GEMHostDeployment

Verify that the Databricks JDBC JAR was copied into the deployment:

$deploy = "C:\Users\Public\ErgoTech\TransSECSDevicesTrial_11026\Projects\GEMHost_Databricks_POC_Java11\GEMHostDeployment"
 
Get-ChildItem $deploy -Filter "databricks-jdbc*.jar" -File |
    Select-Object Name, Length, FullName

The verified deployment contained:

databricks-jdbc-3.4.2.jar

The verified JAR size was:

41,301,251 bytes

Do not leave TransSECS Builder in LIVE mode while validating the standalone deployment. Builder LIVE and GEMHostDeployment are both hosts and should not compete for the same equipment connection on port 5010.

With the GEM Tool simulator running, switch Builder out of LIVE mode or close Builder.

Then start the packaged runtime:

Set-Location "C:\Users\Public\ErgoTech\TransSECSDevicesTrial_11026\Projects\GEMHost_Databricks_POC_Java11\GEMHostDeployment"
 
.\run.bat

The verified startup used Java 11.0.31 and reported:

Simulation mode: LIVE
Started GEMHost connecting to localhost on port 5010 with device id 1

The Java 11 Arrow reflective-access warning may still appear. It is non-blocking if the Databricks JDBC connection opens successfully.

Verify the equipment side is listening:

Get-NetTCPConnection -LocalPort 5010 -ErrorAction SilentlyContinue |
    Select-Object LocalAddress, LocalPort, RemoteAddress, RemotePort, State, OwningProcess

Verify the standalone host is connected to the equipment:

Get-NetTCPConnection -RemotePort 5010 -ErrorAction SilentlyContinue |
    Select-Object LocalAddress, LocalPort, RemoteAddress, RemotePort, State, OwningProcess

The verified standalone runtime showed an Established client connection to `127.0.0.1:5010`.

The deployment log also reported:

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

Send the same STARTED event described above and verify the resulting Databricks row.

The packaged runtime was verified end to end on 2026-09-28 with this row:

2026-09-28T11:02:28.000+00:00
LOT_001
Recipe_A
50
1
7501
STARTED

This proves the deployed path independently of Builder:

GEM Tool
    |
    | HSMS / SECS-GEM
    v
GEMHostDeployment
    |
    | Java 11 / Triggered Historical
    v
Databricks JDBC 3.4.2
    |
    v
workspace.default.secs_poc

If this command returns nothing:

Get-NetTCPConnection -LocalPort 5010 -ErrorAction SilentlyContinue

the GEM Tool simulator is not listening.

Start the simulator and test the port again.

If port 5010 shows:

Listen

but not:

Established

the simulator is waiting, but the TransSECS host has not connected.

Check:

• TransSECS is in LIVE mode.
• Host is configured for localhost.
• Port is 5010.
• Device ID is 1.
• GEM Tool is still running.

If TransSECS reports:

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

confirm:

• `databricks-jdbc-3.4.2.jar` exists in `Builder\resources`.
• The JAR contains `com/databricks/client/jdbc/Driver.class`.
• TransSECS Builder was fully closed after the JAR was added.
• Builder was restarted after adding the JAR.
• Driver Class Name is `com.databricks.client.jdbc.Driver`.

If Databricks returns:

403 Forbidden
Invalid access token

the JDBC driver successfully reached Databricks, but authentication failed.

Check:

• The Personal Access Token is current.
• The token has not expired or been revoked.
• User Name is:

    token
    

• Password contains the current PAT.
• `AuthMech=3` is present.

Do not change the hostname or warehouse URL first if Databricks is explicitly returning an invalid-token response.

Java 11 may report a warning involving:

com.databricks.internal.apache.arrow.memory.util.MemoryUtil

If the next messages show a successful:

Driver: "DatabricksJDBC"

connection, the warning can be treated as non-blocking for this verified configuration.

If the SECS event is received and acknowledged but no row appears:

• Wait about 30 seconds and query the table again. Triggered Historical may write after the immediate S6F11/S6F12 exchange.
• Confirm Historical points to the intended DatabaseConnection.
• Confirm the Databricks JDBC connection opened successfully.
• Confirm the Databricks table contains every required non-optional column.
• Query the table ordered by `ts DESC`.
• Check the deployment or Builder `SECSMessages.log`.
• Check the Databricks SQL Warehouse Query History if a SQL failure is suspected.

For the packaged runtime, the verified log file is:

C:\Users\Public\ErgoTech\TransSECSDevicesTrial_11026\Projects\GEMHost_Databricks_POC_Java11\GEMHostDeployment\SECSMessages.log

A useful PowerShell filter is:

$log = "C:\Users\Public\ErgoTech\TransSECSDevicesTrial_11026\Projects\GEMHost_Databricks_POC_Java11\GEMHostDeployment\SECSMessages.log"
 
Get-Content $log -Tail 500 |
    Select-String -Pattern "column|secs_poc|Historical|INSERT|UPDATE|not written|failed|error" -Context 8,15

If the Historical log says to set Debug Level to 10, edit:

GEMHostDeployment\ErgoTechConfiguration.properties

The relevant property is:

historical.debuglevel=0

Change it temporarily to:

historical.debuglevel=10

The `sessionmanager.debuglevel` property controls SECS/HSMS session logging and does not need to be changed just to troubleshoot Historical database writes.

Example PowerShell:

$config = "C:\Users\Public\ErgoTech\TransSECSDevicesTrial_11026\Projects\GEMHost_Databricks_POC_Java11\GEMHostDeployment\ErgoTechConfiguration.properties"
 
Copy-Item $config "$config.bak" -Force
 
(Get-Content $config) `
    -replace '^historical\.debuglevel=0$', 'historical.debuglevel=10' |
    Set-Content $config

Restart `GEMHostDeployment\run.bat` after changing the property, reproduce one event, and inspect `SECSMessages.log`.

When troubleshooting is complete, return the setting to:

historical.debuglevel=0

and restart the deployment the next time it is run.

With Historical debug level 10, the verified deployment reported:

2 of 8 MAP columns [RPTID, ReportName] are not written to the "secs_poc" table
because this data is optional and the table does not contain these columns.
The rest of the row is written normally.

For the verified `secs_poc` schema, this is informational rather than a failed historian write.

`RPTID` and `ReportName` are optional MAP metadata columns. The verified seven-column table does not require them:

ts
LOTID
PPID
SetPoint
WaferCount
CEID
EventName

If those optional values are desired, add text columns for them to the Databricks table or use a configuration that allows Historical to create the table/columns.

If they are not needed for the demo, the warning can be left as-is because the normal event row is still written.

Inspect the actual S6F11 payload.

If it contains:

<A ''>

for LOTID, Databricks is storing the value TransSECS actually received.

Re-enter LOT_001 in the GEM Tool and commit the field by clicking away or pressing Tab before sending another event.

The correct S6F11 should contain:

<A 'LOT_001'>

If Databricks reports an unresolved column, the Historical component is attempting to write a column that is not present in the Databricks table.

Compare the table schema with:

ts
LOTID
PPID
SetPoint
WaferCount
CEID
EventName

Add the missing column using the correct Databricks type, then resend one event.

The following configuration was verified end to end on September 28, 2026:

The successful tests confirmed:

• Java 11 startup
• Databricks JDBC driver loading
• Databricks authentication
• HSMS connection establishment
• S1F13/S1F14 communication
• Report 101 configuration
• STARTED event 7501 configuration
• S6F11 event reception
• S6F12 acknowledgement
• Triggered Historical processing
• Successful Databricks row insertion from Builder LIVE
• Successful Databricks row insertion from the packaged GEMHostDeployment runtime
• Optional MAP columns `RPTID` and `ReportName` may be omitted without preventing the normal row write

The Builder LIVE known-good row was:

2026-09-28T09:31:56.291+00:00
LOT_001
Recipe_A
50
1
7501
STARTED

The standalone GEMHostDeployment known-good row was:

2026-09-28T11:02:28.000+00:00
LOT_001
Recipe_A
50
1
7501
STARTED

The known-good deployed path is:

STARTED 7501
    ->
Report 101
    ->
GEMHostDeployment / TransSECS 11026 / Java 11
    ->
Triggered Historical
    ->
Databricks JDBC 3.4.2
    ->
workspace.default.secs_poc

When finished:

• Switch TransSECS Builder out of LIVE mode or close the project.
• Stop `GEMHostDeployment\run.bat` if the packaged runtime is running.
• If Historical debug logging was raised for troubleshooting, restore `historical.debuglevel=0`.
• Close the StandAloneGEMTool simulator.
• Temporary `GEMTool11026_…` folders under `%TEMP%` can be removed.
• Preserve the working Java 11 project and deployment.
• Preserve `Builder\resources\databricks-jdbc-3.4.2.jar`.
• Keep older Java 8-era TransSECS installations available as fallback/reference until they are no longer needed.

• TransSECS
• TransSECS Devices
• Understanding SECS/GEM - Concepts and Lessons
• TransSECS Host: Configuring Event Data Collection Reports
• TransSECS Host: Configuring S1F3 Polling in TransSECS for a Host Project