Query Azure Databricks data in Google Sheets

Important

This feature is in Public Preview.

This page describes how to query data from your Azure Databricks workspace and import it into Google Sheets using the Databricks Connector for Google Sheets. You can select tables directly, write SQL queries, add parameters, and create pivot tables. The connector automatically saves all queries as imports so you can refresh results and reuse existing queries.

Prerequisites

• You have set up the Databricks Connector for Google Sheets.

Choose an import method

You can import data from Azure Databricks into Google Sheets by selecting a table or writing a SQL query. After the data is imported, the query is tied to the sheet. Changing the sheet name breaks the mapping. For information on how to handle name changes, see Limitations.

Important

If you are using "Select data" to import Unity Catalog metric views, they can only be imported as pivot tables because Unity Catalog metrics represent pivoted data.

Choose an import method to get started:

Select data

To import data from a table in Azure Databricks, do the following:

1. In the Google Sheets Databricks Connector sidebar, under Data, for Import method, select Select data.
2. Under Catalog, use the catalog, schema, and table drop-down menus to search for the table you want to import.
3. Optionally, update the Asset Name to change the name of this import.
4. Optionally, under Fields, choose which columns to include or exclude.
5. You can optionally import as a pivot table.
6. To add a filter, click + Filter under Filters. Select the Column to apply the filter to and the Filter type.
7. Optionally, check Limit rows to set a limit for the number of rows to import.
8. Under Output destination, choose if you want to save the query results in a new sheet or in the current sheet.
   • If you choose the current sheet, specify which cell to start adding the data in.
9. Click Save & Import to populate the sheet.

Write a SQL query

To write a new SQL query, do the following:

1. In the Google Sheets Databricks Connector sidebar, under Data, select Write SQL.
2. Databricks recommends entering a name for your query so it is identifiable.
3. You can browse through catalogs, schemas, and tables.
4. In Query text, enter your SQL query.
5. You can optionally add query parameters.
6. Under Output destination, choose if you want to save the query results in a new sheet or in the current sheet.
   • If you choose the current sheet, specify which cell to start adding the data in.
7. Click Save & Import to run the query and populate the sheet.

Note

Query execution times out after three minutes. If your query exceeds this limit, it is automatically cancelled. For large result sets, the first 1,000 rows are written immediately, with remaining data fetched progressively. If data fetching is interrupted, partial results remain in your sheet and can be cleared by re-running the query.

Add query parameters (optional)

To add query parameters to your SQL query:

1. Ensure your query has at least one query parameter, in the format of :parameter_name. For more on query parameters, see Work with query parameters.

2. Click + Add Parameter.

3. Enter the parameter in the first box. Make sure the parameter name matches what you entered in the query editor.

4. Enter the sheet name and cell location of your parameter value in the second box, including the exclamation point after the sheet name.

5. To add more query parameters, click + Add Parameter again.

   For example, the following query includes the query parameter :trip_distance, which is defined in sheet sheet_1, cell H1.

   

Import as a pivot table (optional)

To import your data as a pivot table, do the following:

1. To import data using the Select data method, select the Pivot table checkbox.
2. Under Pivot Configuration, select the Rows and Columns for the dimensions of your pivot table.
3. Specify values to aggregate by. Click + Add Value and select the column and aggregation method.
4. Optionally, add filters by clicking + Add Filter and select the Column and Filter type.
5. Click Save & Import to import the results as a pivot table. Import pivot tables are automatically imported to a new sheet.

Manage imported data

To manage the data you import from Azure Databricks, do the following:

1. Click the Imports tab in the connector sidebar.
2. To refresh an import, click the refresh icon next to the query name.
3. To see which sheet an import is connected to, click > Go to Sheet next to the query name.
4. To edit an import, click > Edit next to the query name.
5. To delete an import, click > Delete next to the query name. This deletes the query, not the data imported into Google Sheets. You must manually delete imported data.

Sharing implications

The add-on does not impact your ability to share your Google Sheet. However, the way you share the file impacts the actions that your recipients can take using the add-on.

• Recipients with the Viewer or Commenter role can't access the add-on.
• Recipients with the Editor role and equivalent data asset access can use the add-on with their Google account. They can use the connector just like the owner.
• Recipients with the Editor role and the same access to the underlying resources can refresh the imports if they are logged in to the same Azure Databricks workspace.

Limitations

• Renaming or deleting a sheet that is attached to an existing import prevents you from refreshing the import. To fix this, do one of the following:

  • Recreate the sheet with the exact same name.
  • Create a new import by choosing Select a Query as the source, reusing the import, and clicking Save as New.

• If two queries map to the same or overlapping ranges, the add-on displays the results of the most recently executed query. This overwrites previously imported data.

Next steps

• Schedule data refreshes in Google Sheets