Differences

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

--- mistudio:logic_editor:database:connections:databricks [2026/09/18 18:56] – removed - external edit (Unknown date) 127.0.0.1
+++ mistudio:logic_editor:database:connections:databricks [2026/09/18 18:56] (current) – ↷ Page moved from mistudio:logic_editor:database_connections:databricks to mistudio:logic_editor:database:connections:databricks tputman
@@ Line 1: / Line 1: @@
+===== Connecting MIStudio to Databricks =====
+This page explains how to connect MIStudio to a Databricks SQL Warehouse using JDBC.
+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.
+==== Overview ====
+A typical MIStudio-to-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
+  * A **DatabricksConnectionManager** in MIStudio
+After the connection is configured, database components such as **DatabaseRawLookup** and **DatabaseRawWrite** can execute SQL against Databricks.
+==== Step 1: Get the Databricks Connection Details ====
+Sign in to your Databricks workspace.
+Open the SQL Warehouse that MIStudio 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 MIStudio.
+Typical connection information includes:
+| Server Hostname | ''YOUR-SERVER-HOSTNAME'' |
+| Port | ''443'' |
+| HTTP Path | ''/sql/1.0/warehouses/YOUR-WAREHOUSE-ID'' |
+<note>
+Copy the Server Hostname and HTTP Path from the Databricks Connection Details page for your own SQL Warehouse.
+</note>
+==== 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]]
+Databricks publishes the JDBC driver under:
+<code>
+com.databricks:databricks-jdbc
+</code>
+Select the desired driver version and download the JAR file.
+The downloaded file follows this naming pattern:
+<code>
+databricks-jdbc-<version>.jar
+</code>
+For example:
+<code>
+databricks-jdbc-3.4.2.jar
+</code>
+The JDBC driver class used by MIStudio is:
+<code>
+com.databricks.client.jdbc.Driver
+</code>
+<note>
+Use the current Databricks JDBC Driver rather than the legacy Simba JDBC Driver when setting up a new connection.
+</note>
+==== Step 3: Add the JDBC Driver to the MIStudio Project ====
+The Databricks JDBC driver can be added directly to the MIStudio 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:
+<code>
+databricks-jdbc-<version>.jar
+</code>
+<note important>
+Restart MIStudio after adding the JDBC driver so the new driver is loaded before configuring or testing the Databricks connection.
+</note>
+==== Step 4: Choose an Authentication Method ====
+Databricks supports multiple JDBC authentication methods.
+For a simple connection test, a Databricks Personal Access Token (PAT) can be used.
+For PAT authentication:
+^ Setting ^ Value ^
+| User Name | `token` |
+| Password | Your Databricks Personal Access Token |
+| AuthMech | `3` |
+<note important>
+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. Production environments may require a different authentication method based on your organization's Databricks security requirements.
+==== Step 5: Add DatabricksConnectionManager ====
+Open the MIStudio project and application that will use Databricks.
+Open the [[mistudio:logic_editor:start|MIStudio Logic Editor]] and add a:
+**DatabricksConnectionManager**
+Configure the connection manager using the connection information from your 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:
+<code>
+com.databricks.client.jdbc.Driver
+</code>
+The Server Hostname should be entered without the protocol.
+For example, enter:
+''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''
+==== Step 6: Configure the Connection Properties ====
+For PAT-based authentication, enter the following connection properties.
+Enter each property on its own line:
+<code>
+AuthMech=3
+transportMode=http
+ssl=1
+</code>
+The MIStudio User Name field should contain:
+<code>
+token
+</code>
+The Password field should contain the Databricks Personal Access Token.
+<note>
+Connection properties may differ when using another Databricks authentication method.
+</note>
+==== Step 7: Enable Simulating Mode ====
+MIStudio must be running the application logic for database components to execute.
+Before testing the Databricks connection:
+  * Confirm that **DatabricksConnectionManager** is configured.
+  * Add a **DatabaseRawLookup** component to the Logic Editor.
+  * Configure the 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:
+<code>
+/Main/DatabricksConnectionManager
+</code>
+<note important>
+If the database components appear to be configured correctly but nothing happens, confirm that MIStudio is in **Simulating** mode.
+</note>
+==== Step 8: Test the Connection ====
+Start with a simple query before attempting a larger database operation.
+In **DatabaseRawLookup**, enter:
+<code sql>
+SELECT 1
+</code>
+Trigger the DatabaseRawLookup while MIStudio is in **Simulating** mode.
+If the connection is successful, the query should return:
+<code>
+</code>
+You can also check the Databricks SQL Warehouse Query History to confirm that the query was received and executed.
+==== Step 9: Check the Catalog and Schema ====
+After confirming the connection, determine which Databricks catalog and schema MIStudio is using.
+Use **DatabaseRawLookup** with:
+<code sql>
+SELECT current_catalog()
+</code>
+Then check the schema:
+<code sql>
+SELECT current_schema()
+</code>
+These values identify the current location used by unqualified SQL queries.
+Databricks objects can also be referenced using fully qualified names:
+<code>
+catalog.schema.table
+</code>
+For example:
+<code>
+my_catalog.my_schema.my_table
+</code>
+Using fully qualified table names can help prevent queries from running against the wrong catalog or schema.
+==== Reading Data with DatabaseRawLookup ====
+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:
+<code sql>
+SELECT *
+FROM my_catalog.my_schema.my_table
+</code>
+==== Writing Data with DatabaseRawWrite ====
+Use **DatabaseRawWrite** for SQL statements that create or modify database content.
+Common examples include:
+  * `CREATE TABLE`
+  * `INSERT`
+  * `UPDATE`
+  * `DELETE`
+For example:
+<code sql>
+CREATE TABLE IF NOT EXISTS my_catalog.my_schema.mistudio_test (
+    id INT,
+    message STRING,
+    created_at TIMESTAMP
+)
+</code>
+==== Testing a Write ====
+After creating a test table, insert a value using **DatabaseRawWrite**.
+For example:
+<code sql>
+INSERT INTO my_catalog.my_schema.mistudio_test
+    (id, message, created_at)
+VALUES
+    (1, 'Hello from MIStudio', current_timestamp())
+</code>
+Then use **DatabaseRawLookup** to read the value back:
+<code sql>
+SELECT message
+FROM my_catalog.my_schema.mistudio_test
+WHERE id = 1
+</code>
+A successful readback confirms that MIStudio can:
+  * Connect to the Databricks SQL Warehouse
+  * Execute SQL
+  * Write database data
+  * Read database data
+==== Read vs. Write Components ====
+Use the MIStudio database component that matches the SQL operation.
+^ SQL Operation ^ MIStudio Component ^
+| `SELECT` | **DatabaseRawLookup** |
+| Metadata query | **DatabaseRawLookup** |
+| Catalog or schema query | **DatabaseRawLookup** |
+| `CREATE TABLE` | **DatabaseRawWrite** |
+| `INSERT` | **DatabaseRawWrite** |
+| `UPDATE` | **DatabaseRawWrite** |
+| `DELETE` | **DatabaseRawWrite** |
+==== Verify Queries in Databricks ====
+Databricks SQL Warehouse Query History can be useful when testing the connection.
+Query History can help confirm that:
+  * MIStudio reached Databricks
+  * The SQL statement was received
+  * The query executed
+  * An error occurred on the Databricks side
+If a query appears to execute in MIStudio but the expected result is not visible, check Databricks Query History as part of troubleshooting.
+==== Troubleshooting ====
+=== 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:
+  * 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:
+<code>
+com.databricks.client.jdbc.Driver
+</code>
+=== Connection Fails ===
+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:
+<code>
+token
+</code>
+and that the Password field contains the user's Databricks Personal Access Token.
+=== SELECT Works but CREATE or INSERT Does Not ===
+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.
+=== Table Cannot Be Found ===
+Confirm the current catalog and schema:
+<code sql>
+SELECT current_catalog()
+</code>
+<code sql>
+SELECT current_schema()
+</code>
+If necessary, use the complete table name:
+<code>
+catalog.schema.table
+</code>
+=== Query Does Not Appear in Databricks ===
+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.
+Then check the Databricks SQL Warehouse Query History.
+==== Security Notes ====
+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
+Use the authentication method required by your organization for production systems.
+==== Connection Workflow Summary ====
+<code>
+Databricks SQL Warehouse
+          |
+          | Server Hostname
+          | Port
+          | HTTP Path
+          | Authentication
+          v
+Databricks JDBC Driver
+          |
+          v
+MIStudio Project Drivers
+          |
+          v
+DatabricksConnectionManager
+          |
+          +--> DatabaseRawLookup
+          |        |
+          |        +--> SELECT / Read Data
+          |
+          +--> DatabaseRawWrite
+                   |
+                   +--> CREATE / INSERT / UPDATE / DELETE
+</code>
+==== Related Pages ====
+  * [[mistudio:logic_editor:start|MIStudio Logic Editor]]
+  * [[mistudio:logic_editor:database:connections:start|Database Connections]]
+  * [[mistudio:getting_started:data_sources_overview|Data Sources Overview]]
+  * [[mistudio:getting_started:connect_data_source_to_graphic|Connecting a Data Source to a Graphic]]
+  * [[mistudio:servers:start|Servers and Data Sources]]