đź”—Database Query

đź”—Description

Runs a database query at the end of the pipeline run. Can be configured to run only on success, only on failure, or always at the end of the run.

đź”—Use Case

The action is used whenever you need to run a query at the end of a pipeline run. For example, you may have a pipeline that imports data from a database table to hdfs files. At the end of the run, you may want to run a query that deletes the data that was read from the table.

đź”—Properties

runCondition: When to run the action. Must be 'completion', 'success', or 'failure'. Defaults to 'success'. If set to 'completion', the action will be executed regardless of whether the pipeline run succeeded or failed. If set to 'success', the action will only be executed if the pipeline run succeeded. If set to 'failure', the action will only be executed if the pipeline run failed.

query: The query to run.

connectionString: JDBC connection string including database name.

user: User identity for connecting to the specified database. Required for databases that need authentication. Optional for databases that do not require authentication.

password: Password to use to connect to the specified database. Required for databases that need authentication. Optional for databases that do not require authentication.

jdbcPluginName: Name of the JDBC plugin to use. This is the value of the 'name' key defined in the JSON file for the JDBC plugin.

jdbcPluginType: Type of the JDBC plugin to use. This is the value of the 'type' key defined in the JSON file for the JDBC plugin. Defaults to 'jdbc'.

enableAutoCommit: Whether to enable auto-commit for queries run by this source. Defaults to 'false'. Normally this setting does not matter. It only matters if you are using a jdbc driver -- like the Hive driver -- that will error when the commit operation is run, or a driver that will error when auto-commit is set to false. For drivers like those, you will need to set this to 'true'.

đź”—Example

This example connects to a database using the specified 'connectionString', which means it will connect to the 'prod' database of a PostgreSQL instance running on 'localhost'. It will run a query to delete the contents of the 'userEvents' table if the pipeline run succeeded.

{
    "name": "Database",
    "type": "batchsource",
    "properties": {
        "runCondition": "success",
        "query": "delete * from userEvents",
        "splitBy": "id",
        "connectionString": "jdbc:postgresql://localhost:5432/prod",
        "user": "user123",
        "password": "password-abc",
        "jdbcPluginName": "postgres",
        "jdbcPluginType": "jdbc"
    }
}

  • CDAP Pipelines Plugin Type: postaction
  • CDAP Pipelines Version: 1.6.2-SNAPSHOT