Execute SQL statement action - This action allows you to execute the SQL query against the Databricks SQL warehouse and returns the Status and Statement Id in the output dictionary variable (for async operation suitable for large dataset).

Automation 360

This action allows you to execute the SQL query against the Databricks SQL warehouse and returns the Status and Statement Id in the output dictionary variable (for async operation suitable for large dataset).

Prerequisites

  • All tables and views referenced in the SQL query must be referenced with the Unity Catalog (UC) under the three-level namespace: <catalog>.<schema>.<table>.
  • The authenticating identity (user or service principal) must hold the minimum UC privileges required to read or write the target tables. The below table lists the required minimum privileges.
    Object Minimum privilege Databricks navigation
    Catalog USE CATALOG
    1. In your Databricks workspace, click Catalog and select the catalog.
    2. Go to the Permissions tab and click Grant.
    3. Enter the email address for a user or the name of a group.
    4. Select the permissions to grant, select USE CATALOG and click Grant.
    Schema USE SCHEMA
    1. In Catalog Explorer, expand the catalog and select the target schema.
    2. Go to the Permissions tab, click Grant.
    3. Select the principal, select USE SCHEMA, and click Grant.
    Table (read) SELECT
    1. In Catalog Explorer, expand the catalog and navigate to the target catalog > schema > table.
    2. Click the table name to open the details page and go to the Permissions tab.
    3. Click Grant, select the principal, select the SELECT, MODIFY, or both, and then click Grant.
    Table (write/modify) SELECT, MODIFY
    Note: TheSELECT and MODIFY privileges can be granted at the catalog or schema level to apply to all current and future objects within them, reducing per-table grant overhead for automations that operate across multiple tables.

    For more details about privileges, see Databricks privileges.

Settings

  • Use the Session field to select one of the following options:
    • Session name: Enter the name of the session used during Connect action.

      Click the Insert a value icon to select an existing variable that you have used to store the default session name.

    • Variable: Enter the name of the variable that you have used to store the session name.
  • Specify the ID of the Databricks SQL warehouse where the query will be executed. You can obtain the warehouse ID from Databricks workspace settings.
    Note: Ensure that the warehouse is running and accessible.
  • Specify the SQL query to execute on the Databricks warehouse in the SQL statement field.

    For example, SELECT * FROM workspace.default.employees

    Click the Insert a value icon to select an existing variable.

  • Select from the following parameter options.
    • On the Dictionary tab, click Add to add dictionary parameters.
      Parameter Description
      Type Specify the data type of the parameter value. Supported data types are:
      • Any
      • String
      • Number
      • Datetime
      • Boolean
      Key Enter the name of the parameter as referenced in the SQL statement.
      Value (Optional) Enter a value or click Insert a value to select a variable.
    • On the Variable tag, enter the variable or click Insert a value to select a variable.
  • In the Rows limit field, specify the maximum number of rows that you want to return from the query.
  • Enter a query timeout value.

    When the specified time expires, the statement execution stops even if the execution is not completed.

  • In the Assign output to a variable field, select one of the following options to store the result of the SQL execution.
    • Multiple variables: Stores the output in multiple variables by providing the key and the variable to which the key is mapped. This variable can be of any type, such as String, Number, Datetime.

      Click Add variable mapping to add key and map it to a variable. If the variable is not available in the list, click Create variable.

    • Dictionary: Select a dictionary variable to hold the output if the SQL statement returns a value. If the variable is not available in the list, click Create variable.

    Returns a statement ID for later retrieval through Get SQL statement result action. You use that statementId as input to Get SQL statement result action to fetch the actual rows.