DataMasque Portal

Run SQL (run_sql)

Use the run_sql task type when you need to run any native SQL DDL or DML statements or scripts at any point within a ruleset.

You can supply SQL for DataMasque to execute either as a script file (see Files guide), or inline in the ruleset:

Parameters

  • sql (this OR sql_file required): An SQL script to be executed. For multi-line scripts, you may use the YAML block style syntax (|-).
  • sql_file (this OR sql required): The name of a user-provided file containing an SQL script to be executed (see Files guide). Use this parameter if you have large blocks of SQL to run, or scripts that you wish to share between many rulesets.
  • db_type (optional): Restricts execution to a specific database type. Valid values are: mssql, oracle, postgres, redshift, mysql, mariadb, db2_luw, db2i, snowflake, databricks_lakebase. If the current database type doesn't match, the task will be skipped with a warning.
  • disable_warning_on_skip (optional, default: false): When true, suppresses the warning message when a task is skipped due to database type mismatch.

Example Microsoft SQL Server (sql)

Note: This example uses Microsoft SQL Server specific syntax, as master has been specified as the target database.

version: '1.0'
tasks:
  - type: run_sql
    sql: |-
      USE [master];
      ALTER DATABASE eCommerce SET RECOVERY SIMPLE WITH NO_WAIT;
      USE [eCommerce];
      ALTER TABLE [SalesRecords].[Customer] DROP CONSTRAINT [FK_SALESRECORDS_CUSTOMER];
      ALTER TABLE [Invoices].[Customer] DROP CONSTRAINT [FK_INVOICES_CUSTOMER];

Example MySQL (sql)

version: '1.0'
tasks:
  - type: run_sql
    sql: |-
      CREATE PROCEDURE dorepeat(p1 INT)
      BEGIN
        SET @x = 0;
        REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
      END;
      CALL dorepeat(1000);

Example (sql_file)

tasks:
  - type: run_sql
    sql_file: pre_script_1.sql

Example (Database-specific execution)

This example shows how to use the db_type parameter to run SQL only on specific database types:

version: '1.0'
tasks:
  # This task will only execute on PostgreSQL databases
  - type: run_sql
    db_type: postgres
    sql: |-
      UPDATE users SET email = email || '.masked' WHERE active = true;

  # This task will only execute on Oracle databases and suppress warnings on other databases
  - type: run_sql
    db_type: oracle
    disable_warning_on_skip: true
    sql: |-
      UPDATE users SET email = email || '.masked' WHERE active = 1;

Notes:

  • The run_sql task type executes in autocommit mode, and will exit on the first error encountered.
  • The run_sql task type does not run in dry run mode.
  • The run_sql task type currently only works with one SQL query per task for IBM Db2 LUW and Snowflake databases. For IBM Db2 for i, compound SQL is not supported but multiple semicolon-separated statements are automatically split and executed individually.
  • For MariaDB, MySQL and PostgreSQL connections, the SQL interpreter considers a colon followed by letters or numbers as bound parameters. The colon can be escaped with a \ before it. e.g. Attempting to insert JSON data: INSERT INTO table_name (column_name) VALUES('{"is_real" :true, "key":"value"}'::json) run_sql will interpret true as a bound parameter, to fix this you can escape the bound parameter by adding a backslash(\) before the colon(:) as follows: INSERT INTO table_name (column_name) VALUES('{"is_real" \:true, "key":"value"}'::json)

Notes for Oracle:

  • It is recommended to test the execution of your SQL script with Oracle SQLPlus before use in a run_sql task.
  • DataMasque appends "WHENEVER SQLERROR EXIT SQL.SQLCODE" to the beginning of the SQL script, so the run_sql task will exit on the first error encountered.
  • Even if the database raises errors while executing the SQL script, some statements may still have finished executing successfully. It is recommended to check the database on any failed run_sql task.
  • run_sql does not use the schema specified in the connection configuration, instead it will default to the schema of the user. If you wish to change schema, specify it in the script with SET SCHEMA.
  • When running multiple statements in a run_sql block, a / character is required between each statement if END is used in the transaction.

Notes for Microsoft SQL Server:

  • It is recommended to test the execution of your SQL script with Microsoft sqlcmd or SQL Server Management Studio before use in a run_sql task.
  • Even if the database raises errors while executing the SQL script, some statements may still have finished executing successfully. It is recommended to check the database on any failed run_sql task.

Notes for PostgreSQL:

  • It is recommended to test the execution of your SQL script with psql before use in a run_sql task.
  • A run_sql task will be executed with a simple query cycle, where statements are executed in a single transaction (unless explicit transaction control commands are included to force a different behaviour).

Notes for Redshift:

  • Using multiple SQL statements in a single run_sql task type is not supported currently.

Notes for MySQL and MariaDB:

  • It is recommended to test the execution of your SQL script with MySQL/MariaDB shell before use in a run_sql task.
  • Using DELIMITER to redefine the MySQL/MariaDB delimiter is not required. DELIMITER is a mysql/mariadb command-line client command and is not supported when defining stored programs in run_sql tasks.
  • Warning: Executing run_sql tasks with more than one statement may not raise errors upon failure. If the first statement executes correctly but subsequent statements fail, errors may not be raised.

Notes for Amazon DynamoDB:

  • The run_sql task type is not supported for Amazon DynamoDB.

Notes for IBM Db2 LUW:

  • On IBM Db2 LUW, run_sql tasks can only execute one SQL query per task.
  • You may have to split up SQL into multiple run_sql tasks to achieve desired results.

Notes for IBM Db2 for i:

  • The IBM i ODBC driver rejects compound SQL (such as BEGIN ... END procedural blocks). DataMasque automatically splits multiple semicolon-separated statements and executes each one individually.
  • SQL comments (-- and /* */) and trailing semicolons are stripped automatically before execution.

Notes for Snowflake:

  • On Snowflake, run_sql tasks can only execute one SQL query per task.
  • You will have to split up SQL into multiple run_sql tasks to achieve desired results.
  • The run_sql task will likely fail if more than one SQL query is provided for Snowflake databases.