===== Connecting TransSECS to Databricks ===== 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** ==== Overview ==== 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: ^ Variable ^ VID ^ | LOTID | `1514` | | PPID | `1516` | | SetPoint | `2000` | | WaferCount | `1510` | 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. ==== Step 1: Get the Databricks Connection Details ==== 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: ^ Setting ^ Example ^ | Server Hostname | `YOUR-SERVER-HOSTNAME` | | Port | `443` | | HTTP Path | `/sql/1.0/warehouses/YOUR-WAREHOUSE-ID` | Use the Server Hostname and HTTP Path from your own Databricks SQL Warehouse. Do not copy another user's hostname, warehouse ID, or credentials from an example configuration. ==== Step 2: Download the Databricks JDBC Driver ==== Download the Databricks JDBC Driver from Maven Central: [[https://central.sonatype.com/artifact/com.databricks/databricks-jdbc|Databricks JDBC Driver on Maven Central]] The Maven artifact is: com.databricks:databricks-jdbc The driver JAR follows this naming pattern: databricks-jdbc-.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 ==== Step 3: Add the JDBC Driver to TransSECS Builder ==== 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 the JDBC JAR is copied into the resources directory while TransSECS Builder is already running, the current Builder JVM will not automatically load it. Fully close and restart TransSECS Builder after adding or replacing the JDBC JAR. 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". ==== Step 4: Configure the Databricks DatabaseConnection ==== For the verified TransSECS build 11026 configuration, a standard **DatabaseConnection** was used. The connection was named: DatabricksConnection Configure the DatabaseConnection with: ^ Property ^ Value ^ | Name | `DatabricksConnection` | | Database User Name | `token` | | Database Password | Databricks Personal Access Token | | Database Driver Class Name | `com.databricks.client.jdbc.Driver` | 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: ^ Setting ^ Value ^ | User Name | `token` | | Password | Current Databricks Personal Access Token | | AuthMech | `3` | Do not store a Databricks Personal Access Token in wiki documentation, screenshots, source control, issue reports, example files, or shared console output. Treat the PAT as a password. If a token is exposed, revoke it and generate a replacement. ==== Step 5: Configure Triggered Historical ==== Add or configure a **Triggered Historical** device beneath the Databricks DatabaseConnection. The verified device was named: Historical The known-good configuration was: ^ Property ^ Value ^ | Name | `Historical` | | Timestamp Column | `ts` | | Table Name | `secs_poc` | | Create Table | `False` | | Cache Size | `0` | The historical device writes to the configured Databricks catalog and schema. The verified destination was: workspace.default.secs_poc ==== Step 6: Create or Verify the Databricks Table ==== 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 ==== Step 7: Configure the SECS/GEM Report and Event ==== The verified STARTED event uses: CEID 7501 STARTED is linked to Report: 101 Report 101 contains: ^ Variable ^ VID ^ | LOTID | `1514` | | PPID | `1516` | | SetPoint | `2000` | | WaferCount | `1510` | 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** ==== Step 8: Start the GEM Tool Simulator ==== 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. ==== Step 9: Verify the GEM Tool Is Listening ==== 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. ==== Step 10: Start TransSECS Builder ==== 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. ==== Step 11: Open the Host Project ==== 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: ^ Property ^ Value ^ | Type | Host | | Host | `localhost` | | Device ID | `1` | | Port | `5010` | | Uses GEM | Enabled | The Devices tree contained: DatabricksConnection | +-- Historical ==== Step 12: Switch TransSECS to LIVE ==== 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. ==== Step 13: Verify the HSMS Connection ==== 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 ==== Step 14: Send a STARTED Event ==== In the yellow GEM Tool, enter: ^ Field ^ Value ^ | PPID | `Recipe_A` | | Lot ID | `LOT_001` | | Set Point | `50` | | Wafer Count | `1` | | Event | `STARTED` | After editing a GEM Tool text field, click out of the field or press Tab before sending the event. During verification, the first test visually showed LOT_001 in the UI, but the actual S6F11 contained an empty LOTID. After the field value was committed, the next event correctly contained LOT_001. 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 ==== Step 15: Verify the Row in Databricks ==== 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: ^ Column ^ Value ^ | LOTID | `LOT_001` | | PPID | `Recipe_A` | | SetPoint | `50` | | WaferCount | `1` | | CEID | `7501` | | EventName | `STARTED` | 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 Triggered Historical may not make the row visible immediately after the S6F11/S6F12 exchange. During the standalone deployment verification, the SECS event was acknowledged immediately, while the Historical write completed roughly 25-30 seconds later. Wait about 30 seconds and query the table again before treating a missing row as a failure. 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 ==== Building a Deployable Runtime ==== 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 === Run the Packaged Deployment === 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 ==== Troubleshooting ==== === No Port 5010 Listener === 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. === Port Shows Listen but Not Established === 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. === JDBC Driver ClassNotFoundException === 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`. === Invalid Access Token === 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. === Illegal Reflective Access Warning === 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. === S6F11/S6F12 Works but No Databricks Row Appears === 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 === Increase Historical Debug Logging === 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. === "2 of 8 MAP columns not written" === 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. === Blank LOTID Appears in Databricks === Inspect the actual S6F11 payload. If it contains: 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: === UNRESOLVED_COLUMN === 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. ==== Verified Baseline ==== The following configuration was verified end to end on September 28, 2026: ^ Component ^ Verified Value ^ | TransSECS Build | `11026` | | Java | `11.0.31` | | Databricks JDBC | `3.4.2` | | JDBC Driver Class | `com.databricks.client.jdbc.Driver` | | DatabaseConnection | `DatabricksConnection` | | Historical | `Historical` | | Databricks Catalog | `workspace` | | Databricks Schema | `default` | | Databricks Table | `secs_poc` | | Timestamp Column | `ts` | | HSMS Port | `5010` | | SECS Device ID | `1` | | Report | `101` | | Event | `STARTED` | | CEID | `7501` | | Verified Host Modes | `Builder LIVE` and `GEMHostDeployment` | 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 Testing Is Complete ==== 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. ==== Related Pages ==== * [[:transsecs:start|TransSECS]] * [[:devices|TransSECS Devices]] * [[:secs_gem_lessons_and_concepts|Understanding SECS/GEM - Concepts and Lessons]] * [[:transsecs_host--configuring_event_data_collection_reports|TransSECS Host: Configuring Event Data Collection Reports]] * [[:transsecs_host--configuring_s1f3_polling_in_transsecs_for_a_host_project|TransSECS Host: Configuring S1F3 Polling in TransSECS for a Host Project]]