ErgoTechWiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
===== 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>
1
</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]]