Differences

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

--- mistudio:logic_editor:database:connections:databricks [2026/09/28 12:00] – tputman
+++ mistudio:logic_editor:database:connections:databricks [2026/09/28 12:02] (current) – old revision restored (2026/09/18 18:56) tputman
@@ Line 1: / Line 1: @@
-===== Connecting MIStudio and TransSECS to Databricks =====
+===== Connecting MIStudio to Databricks =====
-This page explains how to connect MIStudio or TransSECS to a Databricks SQL Warehouse using JDBC.
+This page explains how to connect MIStudio 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.
+The connection can be used in the MIStudio Logic Editor to read data from Databricks, execute SQL statements, write database data, and send database results to other application components.
-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.
 ==== Overview ====
-A typical Databricks connection requires:
+A typical MIStudio-to-Databricks connection requires:
   * A Databricks workspace
@@ Line 20: / Line 16: @@
   * The Databricks JDBC driver
   * A Databricks authentication method
+  * A **DatabricksConnectionManager** in MIStudio
-The Databricks JDBC driver class is:
+After the connection is configured, database components such as **DatabaseRawLookup** and **DatabaseRawWrite** can execute SQL against Databricks.
-<code>
-com.databricks.client.jdbc.Driver
-</code>
-The exact setup depends on whether the connection is being created in:
-  * **MIStudio**
-  * **TransSECS**
-<note important>
-MIStudio and TransSECS load JDBC drivers differently.
-For MIStudio, the JDBC JAR can be added to the project under **Drivers**.
-For TransSECS Builder, the JDBC JAR must be available on the Builder classpath. In the verified TransSECS build 11026 configuration, the driver was placed in the Builder **resources** directory and the Builder was fully restarted.
-</note>
 ==== Step 1: Get the Databricks Connection Details ====
@@ Line 44: / Line 24: @@
 Sign in to your Databricks workspace.
-Open the SQL Warehouse that MIStudio or TransSECS should use.
+Open the SQL Warehouse that MIStudio should use.
 In Databricks:
@@ Line 55: / Line 35: @@
   * Locate the **HTTP Path**.
-Keep these values available while configuring the connection.
+Keep these values available while configuring MIStudio.
 Typical connection information includes:
-^ Setting ^ Example ^
 | Server Hostname | ''YOUR-SERVER-HOSTNAME'' |
 | Port | ''443'' |
@@ Line 79: / Line 58: @@
 com.databricks:databricks-jdbc
 </code>
+Select the desired driver version and download the JAR file.
 The downloaded file follows this naming pattern:
@@ Line 92: / Line 73: @@
 </code>
-The JDBC driver class is:
+The JDBC driver class used by MIStudio is:
 <code>
@@ Line 99: / Line 80: @@
 <note>
-Databricks JDBC 3.4.2 was successfully tested with TransSECS build 11026 running Java 11.0.31.
-</note>
-<note important>
 Use the current Databricks JDBC Driver rather than the legacy Simba JDBC Driver when setting up a new connection.
 </note>
-==== Step 3A: Add the JDBC Driver to MIStudio ====
+==== Step 3: Add the JDBC Driver to the MIStudio Project ====
-For MIStudio projects, the Databricks JDBC driver can be added directly to the project.
+The Databricks JDBC driver can be added directly to the MIStudio project.
 In the MIStudio project tree:
@@ Line 129: / Line 106: @@
 Restart MIStudio after adding the JDBC driver so the new driver is loaded before configuring or testing the Databricks connection.
 </note>
-==== Step 3B: Add the JDBC Driver to TransSECS ====
-TransSECS Builder loads additional libraries from its Builder classpath.
-For the verified TransSECS build 11026 installation, the Databricks JDBC JAR was placed in:
-<code>
-MIStudioSuite\TransSECS\Builder\resources
-</code>
-For example:
-<code>
-C:\Users\Public\ErgoTech\TransSECSDevicesTrial_11026\MIStudioSuite\TransSECS\Builder\resources\databricks-jdbc-3.4.2.jar
-</code>
-If the **resources** directory does not exist, create it.
-The TransSECS Builder launch script includes:
-<code>
-./resources/*
-</code>
-on its Java classpath.
-<note important>
-If the JDBC JAR is added while TransSECS Builder is already running, the current Java process will not automatically load it.
-Close TransSECS Builder completely and restart it after placing the JDBC JAR in the resources directory.
-</note>
-A missing restart may result in:
-<code>
-java.lang.ClassNotFoundException:
-com.databricks.client.jdbc.Driver
-</code>
-or:
-<code>
-Cannot Connect to Database.
-No Driver Exists for "com.databricks.client.jdbc.Driver".
-</code>
-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:
-<code>
-GEMHostDeployment\databricks-jdbc-3.4.2.jar
-</code>
 ==== Step 4: Choose an Authentication Method ====
@@ Line 190: / Line 111: @@
 Databricks supports multiple JDBC authentication methods.
-For testing and development, a Databricks Personal Access Token (PAT) can be used.
+For a simple connection test, a Databricks Personal Access Token (PAT) can be used.
 For PAT authentication:
@@ Line 200: / Line 121: @@
 <note important>
-Do not place your Personal Access Token in wiki pages, screenshots, source control, issue reports, logs, or shared files.
+Do not place your Personal Access Token in wiki pages, screenshots, source control, issue reports, or shared files.
 Treat the token as a password.
 </note>
-A Personal Access Token can be useful for testing and development.
+A Personal Access Token can be useful for testing and development. Production environments may require a different authentication method based on your organization's Databricks security requirements.
-Production environments may require another authentication method based on the organization's Databricks security requirements.
+==== Step 5: Add DatabricksConnectionManager ====
-==== Step 5A: Configure MIStudio ====
 Open the MIStudio project and application that will use Databricks.
@@ Line 217: / Line 136: @@
 **DatabricksConnectionManager**
-Configure the connection manager using the connection information from the Databricks SQL Warehouse.
+Configure the connection manager using the connection information from your Databricks SQL Warehouse.
 Enter the appropriate values for:
@@ Line 238: / Line 157: @@
 The Server Hostname should be entered without the protocol.
-For example:
+For example, enter:
-<code>
+''YOUR-SERVER-HOSTNAME''
-YOUR-SERVER-HOSTNAME
-</code>
 Do not enter:
-<code>
+''https://YOUR-SERVER-HOSTNAME''
-https://YOUR-SERVER-HOSTNAME
-</code>
 Copy the HTTP Path from the Databricks SQL Warehouse Connection Details.
@@ Line 254: / Line 169: @@
 The HTTP Path should include the leading slash, for example:
-<code>
+''/sql/1.0/warehouses/YOUR-WAREHOUSE-ID''
-/sql/1.0/warehouses/YOUR-WAREHOUSE-ID
-</code>
-==== Step 5B: Configure TransSECS ====
+==== Step 6: Configure the Connection Properties ====
-In the verified TransSECS build 11026 configuration, a Databricks-specific item was not required in the Devices Add menu.
+For PAT-based authentication, enter the following connection properties.
-A standard **DatabaseConnection** was used and named:
+Enter each property on its own line:
-<code>
-DatabricksConnection
-</code>
-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` |
-The Database URL can include the Databricks server, warehouse HTTP path, authentication mechanism, catalog, and schema.
-Example structure:
-<code>
-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
-</code>
-For example, a connection using the catalog ''workspace'' and schema ''default'' would contain:
-<code>
-ConnCatalog=workspace;
-ConnSchema=default
-</code>
-<note important>
-Do not copy another user's Server Hostname, HTTP Path, or access token from an example configuration.
-Use the connection details from your own Databricks SQL Warehouse.
-</note>
-==== Step 6: Configure Connection Properties ====
-For PAT-based authentication, the following properties are commonly used:
 <code>
@@ Line 307: / Line 183: @@
 </code>
-The User Name should contain:
+The MIStudio User Name field should contain:
 <code>
@@ Line 313: / Line 189: @@
 </code>
-The Password should contain the Databricks Personal Access Token.
+The Password field should contain the Databricks Personal Access Token.
-For TransSECS, catalog and schema can also be included in the JDBC URL:
-<code>
-ConnCatalog=workspace
-ConnSchema=default
-</code>
-Change these values to match the required Databricks catalog and schema.
 <note>
@@ Line 328: / Line 195: @@
 </note>
-==== Step 7A: Run MIStudio in Simulating Mode ====
+==== Step 7: Enable Simulating Mode ====
 MIStudio must be running the application logic for database components to execute.
-Before testing a Databricks connection in MIStudio:
+Before testing the Databricks connection:
   * Confirm that **DatabricksConnectionManager** is configured.
   * Add a **DatabaseRawLookup** component to the Logic Editor.
-  * Configure DatabaseRawLookup to use the Databricks connection manager.
+  * Configure the DatabaseRawLookup to use the Databricks connection manager.
   * Put MIStudio into **Simulating** mode.
@@ Line 348: / Line 215: @@
 <note important>
-If MIStudio database components appear to be configured correctly but nothing happens, confirm that MIStudio is in **Simulating** mode.
+If the database components appear to be configured correctly but nothing happens, confirm that MIStudio is in **Simulating** mode.
 </note>
-==== Step 7B: Run TransSECS in LIVE Mode ====
+==== Step 8: Test the Connection ====
-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:
-<code>
-Connection: "SparkSQL" Version: 3.3.3 Driver: "DatabricksJDBC"
-</code>
-A Java warning related to Apache Arrow reflective access may also appear when using Java 11.
-For example:
-<code>
-Illegal reflective access by
-com.databricks.internal.apache.arrow.memory.util.MemoryUtil
-</code>
-This warning did not prevent the verified Databricks connection from operating successfully.
-==== Step 8: Test the MIStudio Connection ====
 Start with a simple query before attempting a larger database operation.
@@ Line 394: / Line 240: @@
 ==== Step 9: Check the Catalog and Schema ====
-After confirming the connection, determine which Databricks catalog and schema are being used.
+After confirming the connection, determine which Databricks catalog and schema MIStudio is using.
 Use **DatabaseRawLookup** with:
@@ Line 491: / Line 337: @@
   * Write database data
   * Read database data
-==== TransSECS SECS/GEM Historical Example ====
-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:
-<code>
-GEM Tool
-    |
-    | HSMS / SECS-GEM
-    | Port 5010
-    v
-TransSECS Host
-    |
-    | JDBC
-    v
-Databricks SQL Warehouse
-    |
-    v
-workspace.default.secs_poc
-</code>
-=== Report Configuration ===
-The test used Report ID:
-<code>
-</code>
-Report 101 contained:
-^ Variable ^ VID ^
-| LOTID | 1514 |
-| PPID | 1516 |
-| SetPoint | 2000 |
-| WaferCount | 1510 |
-The STARTED collection event used:
-<code>
-CEID 7501
-</code>
-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
-=== Triggered Historical Configuration ===
-A **Triggered Historical** device named:
-<code>
-Historical
-</code>
-was configured beneath:
-<code>
-DatabricksConnection
-</code>
-The verified configuration used:
-^ Property ^ Value ^
-| Name | `Historical` |
-| Timestamp Column | `ts` |
-| Table Name | `secs_poc` |
-| Create Table | `False` |
-The Databricks table contained:
-<code>
-ts
-LOTID
-PPID
-SetPoint
-WaferCount
-CEID
-EventName
-</code>
-=== Sending a STARTED Event ===
-The verified test values were:
-^ Field ^ Value ^
-| LOTID | `LOT_001` |
-| PPID | `Recipe_A` |
-| SetPoint | `50` |
-| WaferCount | `1` |
-| CEID | `7501` |
-| Event | `STARTED` |
-The equipment sent an **S6F11** event containing:
-<code>
-S6F11 W
-  CEID 7501
-  Report 101
-    LOTID       LOT_001
-    PPID        Recipe_A
-    SetPoint    50
-    WaferCount  1
-</code>
-TransSECS acknowledged the event with:
-<code>
-S6F12 <B 0x0>
-</code>
-=== Verify the Databricks Historical Row ===
-Run:
-<code sql>
-SELECT
-    ts,
-    LOTID,
-    PPID,
-    SetPoint,
-    WaferCount,
-    CEID,
-    EventName
-FROM workspace.default.secs_poc
-ORDER BY ts DESC
-LIMIT 10
-</code>
-The verified successful row was:
-<code>
-LOTID       LOT_001
-PPID        Recipe_A
-SetPoint    50
-WaferCount  1
-CEID        7501
-EventName   STARTED
-</code>
-This confirms the complete path:
-<code>
-GEM Tool
-   |
-   | S6F11
-   v
-TransSECS 11026
-   |
-   | Databricks JDBC 3.4.2
-   v
-Databricks
-   |
-   v
-workspace.default.secs_poc
-</code>
-==== GEM Tool Input Note ====
-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:
-<code>
-LOT_001
-</code>
-but the first S6F11 contained:
-<code>
-<A ''>
-</code>
-for LOTID.
-After committing the field value, the following event correctly contained:
-<code>
-<A 'LOT_001'>
-</code>
-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.
 ==== Read vs. Write Components ====
@@ Line 710: / Line 357: @@
 Query History can help confirm that:
-  * MIStudio or TransSECS reached Databricks
+  * MIStudio 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.
+If a query appears to execute in MIStudio but the expected result is not visible, check Databricks Query History as part of troubleshooting.
 ==== Troubleshooting ====
-=== JDBC Driver Is Not Found in MIStudio ===
+=== Nothing Happens When the Query Is Triggered ===
+Confirm that MIStudio is in **Simulating** mode.
+Also confirm that the database component is configured to use the intended **DatabricksConnectionManager**.
+=== JDBC Driver Is Not Found ===
 Confirm that:
@@ Line 732: / Line 385: @@
 com.databricks.client.jdbc.Driver
 </code>
-=== JDBC Driver Is Not Found in TransSECS ===
-If TransSECS reports:
-<code>
-ClassNotFoundException:
-com.databricks.client.jdbc.Driver
-</code>
-confirm that:
-  * The Databricks JDBC JAR exists in the Builder **resources** directory.
-  * The JAR contains:
-    <code>
-    com/databricks/client/jdbc/Driver.class
-    </code>
-  * TransSECS Builder was completely closed after the JAR was added.
-  * Builder was restarted after the JAR was placed in **resources**.
-  * The Driver Class is:
-    <code>
-    com.databricks.client.jdbc.Driver
-    </code>
-Also verify that the generated deployment contains the JDBC JAR.
-=== Databricks Returns Invalid Access Token ===
-If Databricks returns:
-<code>
-Forbidden
-Invalid access token
-</code>
-the JDBC driver has reached Databricks, but authentication failed.
-Confirm that:
-  * The Personal Access Token is current.
-  * The User Name is:
-    <code>
-    token
-    </code>
-  * The Password contains the PAT.
-  * `AuthMech=3` is configured.
-  * The token has not expired or been revoked.
 === Connection Fails ===
@@ Line 799: / Line 405: @@
 </code>
-and that the Password contains the Databricks Personal Access Token.
+and that the Password field contains the user's Databricks Personal Access Token.
-=== TransSECS Database Connects but GEM Tool Does Not ===
-Check the HSMS port.
-For the verified example, the GEM Tool listened on:
-<code>
-</code>
-Before the host connects, the port should show:
-<code>
-Listen
-</code>
-After the TransSECS host connects, the port should show both:
-<code>
-Listen
-Established
-</code>
-A successful HSMS startup should include a Select Request and Select Response followed by:
-<code>
-HSMS_SS_VALID_SELECT_RESPONSE -- Successful Select
-</code>
-or an equivalent successful communication-established message.
 === SELECT Works but CREATE or INSERT Does Not ===
@@ Line 861: / Line 436: @@
 === Query Does Not Appear in Databricks ===
-For MIStudio, check that:
+Check that:
   * MIStudio is in **Simulating** mode.
@@ Line 867: / Line 442: @@
   * 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.
@@ Line 891: / Line 457: @@
   * 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.
 ==== Connection Workflow Summary ====
-=== MIStudio ===
 <code>
 Databricks SQL Warehouse
           |
+          | Server Hostname
+          | Port
+          | HTTP Path
+          | Authentication
           v
 Databricks JDBC Driver
@@ Line 921: / Line 486: @@
                    +--> CREATE / INSERT / UPDATE / DELETE
 </code>
-=== TransSECS ===
-<code>
-GEM Equipment / GEM Tool
-          |
-          | HSMS / SECS-GEM
-          v
-TransSECS Host
-          |
-          | Event / Report Data
-          v
-Triggered Historical
-          |
-          v
-DatabaseConnection
-          |
-          | Databricks JDBC
-          v
-Databricks SQL Warehouse
-</code>
-==== Verified TransSECS Baseline ====
-The following configuration was verified successfully on September 28, 2026:
-^ Component ^ Verified Value ^
-| TransSECS Build | `11026` |
-| Java | `11.0.31` |
-| Databricks JDBC | `3.4.2` |
-| Driver Class | `com.databricks.client.jdbc.Driver` |
-| HSMS Port | `5010` |
-| SECS Device ID | `1` |
-| Event | `STARTED` |
-| CEID | `7501` |
-| Report | `101` |
-| Catalog | `workspace` |
-| Schema | `default` |
-| Historical Table | `secs_poc` |
-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
 ==== Related Pages ====