Databricks Driver for SQLTools for Visual Studio Code

Preview

This feature is in Public Preview.

The Databricks Driver for SQLTools enables you to use the SQLTools extension for Visual Studio Code to browse SQL objects and to run SQL queries in remote Databricks workspaces.

Before you begin

Before you can use the Databricks Driver for SQLTools, your Databricks workspace and your local development machine must meet the following requirements.

Workspace requirements

You must have at least one Databricks workspace available, and the workspace must meet the following requirements:

• The workspace must contain at least one Databricks SQL warehouse.

  Note

  Databricks clusters are not supported by the Databricks Driver for SQLTools.

• For workspaces that are enabled for Unity Catalog, the workspace must contain at least one catalog, with at least one schema (formally called a database) within that catalog.

  • Explore available schemas (and catalogs).

  • Create a catalog.

  • Create a schema.

• For workspaces that are not enabled for Unity Catalog, the workspace must contain at least one schema (formerly called a database).

  • Explore available schemas.

  • Create a schema.

Access token

You must have a Databricks personal access token. See also Manage personal access tokens.

Local development machine requirements

You must have the following on your local development machine:

• Visual Studio Code version 1.70 or higher. To view your installed version, click Code > About Visual Studio Code from the manin menu on Linux or macOS and Help > About on Windows. To download, install, and configure Visual Studio Code, see Setting up Visual Studio Code.

• The SQLTools extension for Visual Studio Code.

• The Databricks Driver for SQLTools extension for Visual Studio Code.

To install the SQLTools extension, go to SQLTools and then click Install, or:

1. In Visual Studio Code, click View > Extensions on the main menu.

2. In the Search Extensions in Marketplace box, enter SQLTools.

3. Click the SQLTools entry from Matheus Teixeira.

   Note

   There might be multiple SQLTools entries listed. Be sure to click the entry from Matheus Teixeira.

4. Click Install.

To install the Databricks Driver for SQLTools extension, go to Databricks Driver for SQLTools and then click Install, or:

1. In Visual Studio Code, click View > Extensions on the main menu.

2. In the Search Extensions in Marketplace box, enter Databricks Driver for SQLTools.

3. Click the Databricks Driver for SQLTools entry.

4. Click Install.

Connect to a schema

1. In Visual Studio Code, on the sidebar, click the SQLTools icon.

2. In the SQLTools view, if this is your first time using the SQLTools extension, click Add New Connection within the Connections pane. Otherwise, click the Add New Connection icon in the pane’s title bar.

3. On the SQLTools Settings tab, for the Select a database driver step, click the Databricks icon.

4. For the Connection Settings step, enter the following information about your warehouse, catalog, and schema:

   1. For Connection name, enter some unique name for this connection.

   2. (Optional) For Connection group enter the name of an existing connection group to add the new connection to that group. Or, enter a unique name to create a new connection group with the new connection. Connection groups make it easier to find connections in the extension.

   3. For version 0.4.0 and higher of the Databricks Driver for SQLTools, for Connect using, select Hostname and Token.

   4. For Host, enter the warehouse’s Server hostname setting. To get a warehouse’s Server hostname setting, see Get connection details for a SQL warehouse.

   5. For Path, enter the warehouse’s or cluster’s HTTP path setting. To get a warehouse’s HTTP path setting, see Get connection details for a SQL warehouse.

   6. For Token, enter your Databricks personal access token value.

   7. For Catalog, enter the name of your catalog.

      Note

      For workspaces that are not enabled for Unity Catalog, you can leave Catalog blank to use the default value of hive_metastore.

   8. For Schema, enter the name of your schema.

   9. (Optional) For Show records default limit, leave the default of 50 to show only up to the first 50 rows for each query, or enter a different limit.

5. Click Test Connection.

6. If the connection test succeeds, click Save Connection.

Change a connection’s settings

This procedure assumes that you have successfully connected to at least one warehouse.

1. If the SQLTools view is not visible, then in Visual Studio Code, on the sidebar, click the SQLTools icon.

2. In the Connections pane, expand the connection group, if one exists for your target connection.

3. Right-click the connection, and click Edit Connection.

4. Change the target settings.

5. Click Test Connection.

6. If the connection test succeeds, click Save Connection.

Browse a schema’s objects

1. In the Connections pane, expand the connection group, if one exists for your target connection.

2. Double-click or expand the target connection for your warehouse.

3. Expand the target database (schema), if one exists for your connection.

4. Expand Tables or Views, if one or more tables or views exist for your database (schema).

5. Expand any target table or view to view the table’s or view’s columns.

View the rows or schema for a table or view

With Tables or Views expanded in the Connections pane, do one of the following:

• To show the table’s or view’s rows, right-click the table or view, and click Show Table Records or Show View Records.

• To show the table’s or view’s schema, right-click the table or view, and click Describe Table or Describe View.

Generate an insert query for a table

1. Place your cursor in an existing editor at the location where you want the insert query to be added.

2. With Tables expanded in the Connections pane, right-click the table, and click Generate Insert Query. The insert query’s definition is added at the cursor’s insertion point.

Create and run a query

This procedure assumes that you have successfully connected to at least one warehouse.

1. In the Connections pane, expand the connection group, if one exists for your target connection.

2. Double-click or expand the target connection for your warehouse.

3. With the connection selected, click New SQL File in the Connections pane’s title bar. A new editor tab appears.

4. Enter your SQL query in the new editor.

5. To run the SQL query, click Run on active connection in the editor. The query’s results display in a new editor tab.

Run an existing query

This procedure assumes that you have successfully connected to at least one warehouse.

1. In the Connections pane, expand the connection group, if one exists for your target connection.

2. Double-click or expand the target connection for your warehouse.

3. With the connection selected, open any file with the file extension of .sql, or select any group of continuous SQL statements in any editor that was previously opened.

4. To run the SQL query from an open .sql file, with your .sql file’s contents displayed in the editor, click Run on active connection in the editor. The query’s results display in a new editor tab.

5. To run a selected group of continuous SQL statements in an editor that was previously opened, right-click your selection, and then click Run Selected Query. The query’s results display in a new editor tab.

Additional resources

• SQLTools documentation

• Databricks Driver for SQLTools on GitHub