Differences

This shows you the differences between two versions of the page.

--- transsecs:database:connecting_to_databricks [2026/09/28 12:24] – [Related Pages] tputman
+++ transsecs:database:connecting_to_databricks [2026/09/28 13:19] (current) – tputman
@@ Line 52: / Line 52: @@
 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 ====
@@ Line 588: / Line 590: @@
 | EventName | `STARTED` |
-The verified test produced:
+The clean Builder LIVE verification produced:
 <code>
@@ Line 599: / Line 601: @@
 STARTED
 </code>
+The packaged GEMHostDeployment runtime was later verified independently and produced:
+<code>
+-09-28T11:02:28.000+00:00
+LOT_001
+Recipe_A
+STARTED
+</code>
+<note>
+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.
+</note>
 This confirms the complete path:
@@ Line 609: / Line 629: @@
 TransSECS 11026 / Java 11
     |
-    | Databricks JDBC 3.4.2
+    | Triggered Historical
     v
-Databricks
+Databricks JDBC 3.4.2
     |
     v
@@ Line 652: / Line 672: @@
 </code>
-<note>
+=== Run the Packaged Deployment ===
-The final clean 2026-09-28 STARTED-to-Databricks row was verified with the TransSECS Builder in LIVE mode.
-The generated Java 11 deployment package was also built successfully and the deployment runtime reached Databricks during migration testing.
+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.
-To specifically validate the deployed runtime end to end, switch Builder out of LIVE mode, start GEMHostDeployment\run.bat, and repeat the HSMS and STARTED-event verification.
+With the GEM Tool simulator running, switch Builder out of LIVE mode or close Builder.
-</note>
+Then start the packaged runtime:
+<code powershell>
+Set-Location "C:\Users\Public\ErgoTech\TransSECSDevicesTrial_11026\Projects\GEMHost_Databricks_POC_Java11\GEMHostDeployment"
+.\run.bat
+</code>
+The verified startup used Java 11.0.31 and reported:
+<code>
+Simulation mode: LIVE
+Started GEMHost connecting to localhost on port 5010 with device id 1
+</code>
+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:
+<code powershell>
+Get-NetTCPConnection -LocalPort 5010 -ErrorAction SilentlyContinue |
+    Select-Object LocalAddress, LocalPort, RemoteAddress, RemotePort, State, OwningProcess
+</code>
+Verify the standalone host is connected to the equipment:
+<code powershell>
+Get-NetTCPConnection -RemotePort 5010 -ErrorAction SilentlyContinue |
+    Select-Object LocalAddress, LocalPort, RemoteAddress, RemotePort, State, OwningProcess
+</code>
+The verified standalone runtime showed an **Established** client connection to `127.0.0.1:5010`.
+The deployment log also reported:
+<code>
+Connection: "SparkSQL" Version: 3.3.3 Driver: "DatabricksJDBC"
+</code>
+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:
+<code>
+-09-28T11:02:28.000+00:00
+LOT_001
+Recipe_A
+
+
+
+STARTED
+</code>
+This proves the deployed path independently of Builder:
+<code>
+GEM Tool
+    |
+    | HSMS / SECS-GEM
+    v
+GEMHostDeployment
+    |
+    | Java 11 / Triggered Historical
+    v
+Databricks JDBC 3.4.2
+    |
+    v
+workspace.default.secs_poc
+</code>
 ==== Troubleshooting ====
@@ Line 759: / Line 847: @@
 If the SECS event is received and acknowledged but no row appears:
-  * Check the TransSECS console for JDBC or SQL errors.
+  * 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 table already contains every required column.
+  * 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 Databricks SQL Warehouse Query History.
+  * 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:
+<code>
+C:\Users\Public\ErgoTech\TransSECSDevicesTrial_11026\Projects\GEMHost_Databricks_POC_Java11\GEMHostDeployment\SECSMessages.log
+</code>
+A useful PowerShell filter is:
+<code powershell>
+$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
+</code>
+=== Increase Historical Debug Logging ===
+If the Historical log says to set Debug Level to 10, edit:
+<code>
+GEMHostDeployment\ErgoTechConfiguration.properties
+</code>
+The relevant property is:
+<code>
+historical.debuglevel=0
+</code>
+Change it temporarily to:
+<code>
+historical.debuglevel=10
+</code>
+The `sessionmanager.debuglevel` property controls SECS/HSMS session logging and does not need to be changed just to troubleshoot Historical database writes.
+Example PowerShell:
+<code 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
+</code>
+Restart `GEMHostDeployment\run.bat` after changing the property, reproduce one event, and inspect `SECSMessages.log`.
+When troubleshooting is complete, return the setting to:
+<code>
+historical.debuglevel=0
+</code>
+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:
+<code>
+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.
+</code>
+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:
+<code>
+ts
+LOTID
+PPID
+SetPoint
+WaferCount
+CEID
+EventName
+</code>
+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 ===
@@ Line 823: / Line 1000: @@
 | Event | `STARTED` |
 | CEID | `7501` |
+| Verified Host Modes | `Builder LIVE` and `GEMHostDeployment` |
-The successful test confirmed:
+The successful tests confirmed:
   * Java 11 startup
@@ Line 836: / Line 1014: @@
   * S6F12 acknowledgement
   * Triggered Historical processing
-  * Successful Databricks row insertion
+  * 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 known-good result is:
+The Builder LIVE known-good row was:
+<code>
+-09-28T09:31:56.291+00:00
+LOT_001
+Recipe_A
+
+
+
+STARTED
+</code>
+The standalone GEMHostDeployment known-good row was:
+<code>
+-09-28T11:02:28.000+00:00
+LOT_001
+Recipe_A
+
+
+
+STARTED
+</code>
+The known-good deployed path is:
 <code>
@@ Line 845: / Line 1049: @@
 Report 101
     ->
-TransSECS 11026 / Java 11
+GEMHostDeployment / TransSECS 11026 / Java 11
+    ->
+Triggered Historical
     ->
 Databricks JDBC 3.4.2
@@ Line 857: / Line 1063: @@
   * 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.