Running SQL queries against databases

When creating a new workflow or editing an existing one, in the management console, under Create workflow / Edit workflow, select Constructor.

From the Integration steps section on the left side of the screen, drag the Database Query element to the relevant workflow location in the constructor window.

You can drag and drop steps into areas marked by the dotted line in the workflow schema, such as Add a step, etc.

In the workflow schema window, click the new Database Query section to select it.

At the right of the window, on the Settings tab.

1. In the Step name field, enter a name for the workflow step.

2. Under Connection parameters, select:

   • Connection, to create a connection to the database.

     1. In the field, specify the following:

        • Database type: Type of the database to connect to.

          For a MySQL® database, set the utf8mb4 encoding for field values.

        • Host: Database server address.

        • Port: Port for connection to the database.

        • Database: Database name.

     2. Select Use SSL to connect to the database over SSL.

        Selecting this option:

        • Enables SSL encryption for a PostgreSQL database.
        • Allows secure SSL connections for a ClickHouse® database.

     3. Under Authentication, select:

        • Password to connect to the database using a username and password. Enter the username and select:

          • Lockbox to store the password in a Yandex Lockbox secret. Under Secret, select the secret, its version, and the key used to store the password.

            If you do not have a Yandex Lockbox secret, click Create to create one.

          • Text to store the password in the specification as text. Enter the password in the Value field.

            Warning

            It is not safe to store your password in the specification in plain text.

        • IAM token to connect to the database using an IAM token of the service account specified in the workflow settings.

          Note

          Authentication by IAM token is only supported for YDB databases.

   • DSN to specify a database connection string.

3. In the SQL query field, specify the SQL query you need to execute.

4. In the Execution method field, select:

   • Exec: To execute the SQL query and return the service fields only, e.g., RowsAffected and LastInsertId.
   • Query: To execute the SQL query and return the result.

5. Optionally, in the Timeout, ms field, set the maximum execution time for the current step.

6. Optionally, to set a custom retry policy for a step, expand the Retry policy section and click Retry policy. In the form that appears:

   1. Optionally, in the Initial delay, ms field, set the initial value for a delay between step retries.

   2. Optionally, in the Backoff rate field, set the multiplication factor for delay before each step retry.

   3. Optionally, in the Maximum delay, ms field, set the value for a maximum delay between step retries.

   4. In the Errors field, select the errors for which the step will or will not be retried.

      For detailed information about possible errors, see this section.

   5. Optionally, in the Maximum number of retries field, set the maximum number of step retry attempts.

   6. In the Error selection mode field, select:

      • INCLUDE: Retry executing a step when errors specified in the Errors field occur.
      • EXCLUDE: To retry executing the step on any errors other than those specified in the Errors field.

   If you want to delete a retry policy you created for the step earlier, click and select Delete in the Retry policy row.

   If no custom retry policy is configured for a step, the retry policy set for the whole workflow will apply.

Optionally, navigate to the Input tab and set a jq template to filter the workflow state fed into the step.

Optionally, navigate to the Output tab and set a jq template to filter the step outputs added into the workflow state.

Optionally, add an error transition rule for the step you are creating to handle errors you may get during this step.