ALTER JOB
The ALTER JOB
command enables you to update the mutable properties of your jobs and add or drop expectations.
Syntax
Note that not all job options are mutable.
Job options such as END_AT
, RUN_PARALLELISM
, AGGREGATION_PARALLELISM
, CONTENT_TYPE
, COMPUTE_CLUSTER
, and COMMENT
are always editable for all jobs.
On the other hand, options such as START_FROM
, COMPRESSION
, RUN_INTERVAL
, and ALLOW_CARTESIAN_PRODUCTS
are not editable for any jobs.
Furthermore, there are certain job options that are only available for specific job types; check the documentation linked below for your job type (ingestion, replication, or transformation) to see the comprehensive list of mutable options.
All mutable options are denoted by — editable in the individual option descriptions.
Ingestion jobs
Replication jobs
Transformation jobs
Examples
Alter one job option
Alter multiple job options
You can alter multiple job options in the same statement, using the SET
keyword for each option you need to change:
Add an expectation
You can easily add an expectation to an existing job using the ALTER JOB
command. The syntax below shows how to alter a job you have already created:
In the following example, an expectation named exp_orderid_not_null is added to the load_raw_data_from_mssql job, to check the orderid value in each event is not null. If an event is discovered with a NULL value, it is dropped from the ingestion:
Drop an expectation
To drop an expectation, use the ALTER JOB
command using the following syntax:
When you drop an expectation, the entry is dropped from the system.monitoring.expectations
system table, so if you want to retain a count of warnings or dropped rows, ensure you take a copy of these values prior to dropping the expectation. Furthermore, values are removed from the system.monitoring.jobs
table.
For example, to drop the expectation created in the above example, use the following syntax:
Replication job examples
You can make changes to a replication group by including the UPDATE REPLICATION GROUP
syntax. When you remove a schema, table, or column from a replication group, the data remains on the target but synchronization is stopped.
Add schema and all tables
In the following example, the humanresources schema, including all tables, is added to the existing replication group, group_1, using INCLUDED_SCHEMA_DEFINITION ADD
:
To perform the same operation using a regular expression, use the following syntax:
To update the INCLUDED_TABLES_REGEX
or EXCLUDED_COLUMNS_REGEX
definitions, use the SET
keyword rather than UPDATE
.
Add schema and exclude tables and columns
You can ignore specific tables and/or columns to prevent them being copied to the replication group when you add a new schema.
In the example below, the humanresources schema is added, the shift table (and all columns) is excluded, and the birthdate and gender columns are excluded from the employee table. All other objects in the humanresources schema are copied to group_1:
Remove a table
You can remove one or more tables from an existing schema in the replication group and the data will cease to be copied. In the example below, the shift table in the humanresources schema is removed from group_1:
Remove a schema
Removing a schema from a replication group is as simple as replacing the ADD
keyword with REMOVE
.
In the following example, the humanresources schema is removed from group_1, so that all tables within this schema will no longer be replicated. Note that the EXCLUDED_SCHEMA_DEFINITION
should also be removed when running this statement:
Remove multiple tables
You can remove multiple tables using a comma-separated list. The following example removes the shift, employee, and department tables from the humanresources schema, along with their columns. All other tables in the schema will continue to replicate:
Re-snapshot a table
For jobs ingesting CDC data, you can manually re-snapshot a table in the replication job using the following syntax.
Note that while the re-snapshot takes place, other tables in the replication will continue synchronizing.
The following example re-snapshots the customer_address table, which is synchronized by the load_raw_data_from_mssql job.
Last updated