[Piece Request] - Databricks SQL Connector

[mohammadaffaneh]

Feature Request: Databricks SQL Connection Piece

Background

Activepieces currently offers a variety of connectors, such as the Snowflake connector, which offers SQL operations within workflows. To expand the capabilities of Activepieces, I propose the addition of a Databricks SQL Connector.

Reference

For an implementation example, see the Databricks SQL Connector for Node.js: databricks/databricks-sql-nodejs.

Connection methods

The proposed Databricks connector could support multiple authentication methods to accommodate various user needs:

• Databricks personal access token authentication
• Username and password authentication
• OAuth machine-to-machine (M2M) authentication
• OAuth user-to-machine (U2M) authentication

To connect using the Databricks personal access token method which is the commonly used one, users need to provide:

• Server hostname
• HTTP path
• Personal access token

Example

Here's a high level and brief example from databricks docs demonstrating how a Databricks SQL connection could be established:

import { DBSQLClient } from '@databricks/sql';

const serverHostname = process.env.DATABRICKS_SERVER_HOSTNAME || '';
const httpPath = process.env.DATABRICKS_HTTP_PATH || '';
const token = process.env.DATABRICKS_TOKEN || '';

if (!serverHostname || !httpPath || !token) {
  throw new Error("Missing required environment variables: DATABRICKS_SERVER_HOSTNAME, DATABRICKS_HTTP_PATH, DATABRICKS_TOKEN.");
}

const client = new DBSQLClient();
const connectOptions = { host: serverHostname, path: httpPath, token: token };

client.connect(connectOptions)
  .then(async client => {
    const session = await client.openSession();
    const queryOperation = await session.executeStatement('SELECT * FROM samples.nyctaxi.trips LIMIT 2', {
      runAsync: true,
      maxRows: 10000
    });
    const result = await queryOperation.fetchAll();
    console.table(result);
    await session.close();
    client.close();
  })
  .catch(error => {
    console.error(error);
  });