Configuring a Table

Query

The custom query based on which the table has been created. The option is available only for RDBMS, Generic JDBC and CDATA sources.

NOTE This field is only visible if the table is ingested using Add Query as Table.

Ingest Type

The type of synchronization for the table. The options include full refresh and incremental.

Natural Keys

The combination of keys to uniquely identify the row. This field is mandatory in incremental ingestion tables. It helps in identifying and merging incremental data with the already existing data on target.

NOTE At least one of the columns in the natural key must have a non-null value for Infoworks merge to work.

Incremental Mode

The option to indicate if the incremental data must be appended or merged to the base table. This field is displayed only for incremental ingestion. The options include append and merge.

Incremental Fetch Mechanism

The fetch mechanism options include Archive Log and Watermark Column. This field is available only for Oracle log-based ingestion.

Watermark Column

The watermark column to identify the incremental records.

Use JDBC for This Table

The option to ingest data from Teradata via JDBC driver, if the table cannot be crawled by TPT, for any reason. This option is available for Teradata TPT ingestion.

Ingest subset of data

The option to configure filter conditions to ingest a subset of data. This option is available for all the RDBMS and Generic JDBC sources. For more details, see Filter Query for RDBMS Sources.

Target Table Name

The name of the target table.

Storage Format

The format in which the tables must be stored. The options include Read Optimised (Parquet),Read Optimised (ORC), and Write Optimised (Avro).

LIMITATION The table stored in Read Optimised (ORC) format can only be queried using spark, and not hive.

Partition Column

The column used to partition the data in target. Selecting the Create Derived Column option allows you to derive a column from the partition column. This option is enabled only if the partition column datatype is date or timestamp. Provide the Derived Column Function and Derived Column Name. Data will be partitioned based on this derived column.

Detect Schema Change

This checkbox detects if a new column is added.

Schema Change Behavior

This dropdown provides the list of responses you can choose for the added columns. The responses are: Ignore Column Addition and Notify on Job Failure.

Ignore Column Addition: If you select this value, the job won’t fail and it won't add an additional column to the target.

Notify on Job Failure: If you select this value and a column is added, the job will fail and an error message which says, “Error in sync schema” will appear on top of the page.

Split By Column

The column used to crawl the table in parallel with multiple connections to database. Split-by column can be an existing column in the data. Any column for which minimum and maximum values can be computed, can be a split-by key. Select the Create Derived Split Column option and provide the Derived Split Column Function to derive a column from the split-by column. This option is enabled only if the split-by column datatype is date or timestamp. The data will be split based on the derived value.

Is Wide Table

The option to be enabled when any insert, update or delete statement in logs can exceed 4000 characters.

Ingest Type

The type of synchronization for the table. The options include full refresh and incremental.

Natural Keys

The combination of keys to uniquely identify the row. This field is mandatory in incremental ingestion tables. It helps in identifying and merging incremental data with the already existing data on target.

NOTE At least one of the columns in the natural key must have a non-null value for Infoworks merge to work.

Incremental Mode

The option to indicate if the incremental data must be appended or merged to the base table. This field is displayed only for incremental ingestion. The options include append and merge.

SCD Type

A dimension that stores and manages both current and historical data over time in a data warehouse. The options include SCD1 and SCD2.

NOTE This field is displayed only when the ingestion is Incremental and the incremental mode is Merge.

Watermark Column

The watermark column to identify the incremental records.

NOTE This field is displayed only for incremental ingestion.

Enable Watermark Offset

For Timestamp and Date watermark columns, this option enables an additional offset (decrement) to the starting point for ingested data. Records created or modified within the offset time period are included in the next incremental ingestion job.

NOTE Timestamp watermark column has three options: Days, Hours and Minutes, and the Date watermark column has Days option. In both the cases, the options will be decremented from the starting point.

Ingest subset of data

The option to configure filter conditions to ingest a subset of data. This option is available for all the RDBMS and Generic JDBC sources. For more details, see Filter Query for RDBMS Sources

Optimization Configuration

Split By Column

The column used to crawl the table in parallel with multiple connections to the database. Split-by column can be an existing column in the data. Any column for which minimum and maximum values can be computed, can be a split-by key. Select the Create Derived Split Column option and provide the Derived Split Column Function to derive a column from the Split By column. This option is enabled only if the Split By column datatype is date or timestamp. The data will be split based on the derived value.

Create Derived Split Column

Select the Create Derived Split Column option and provide the Derived Split Column Function to derive from the Split By column. This option is enabled only if the Split By column data type is integer or timestamp. The data will be split based on the derived value. mod: For every float, integer, or long value, the mod function is performed on the number of connections that are available on the database and based on the mod value the corresponding database is updated. The mod function is first applied to the column and after getting the output the split gets applied and the data is evenly distributed. The mod function is applied on each row level and all the connections run parallelly until the end of the job.

Target Configuration

Database Name

Database name of the Snowflake target.

Table Name

Table name of the Snowflake Target.

Schema Name

The schema name of the Snowflake Target.

Generate History View

Select this option to preserve data in the History table. After each CDC, the data will be appended to the history table.

Is user managed?

When enabled, user managed table behavior works as an external table to Infoworks. The table will not be created/dropped or managed by Infoworks.

Ingest Type

The type of synchronization for the table. The options include full refresh and incremental.

Natural Keys

The combination of keys to uniquely identify the row. This field is mandatory in incremental ingestion tables. It helps in identifying and merging incremental data with the already existing data on target.

NOTE At least one of the columns in the natural key must have a non-null value for Infoworks merge to work.

Incremental Mode

The option to indicate if the incremental data must be appended or merged to the base table. This field is displayed only for incremental ingestion. The options include append and merge.

SCD Type

A dimension that stores and manages both current and historical data over time in a data warehouse. The options include SCD1 and SCD2.

NOTE This field is displayed only when the ingestion is Incremental and the incremental mode is Merge.

Watermark Column

The watermark column to identify the incremental records.

NOTE This field is displayed only for incremental ingestion.

Enable Watermark Offset

For Timestamp and Date watermark columns, this option enables an additional offset (decrement) to the starting point for ingested data. Records created or modified within the offset time period are included in the next incremental ingestion job.

NOTE Timestamp watermark column has three options: Days, Hours and Minutes, and the Date watermark column has Days option. In both the cases, the options will be decremented from the starting point.

Ingest subset of data

The option to configure filter conditions to ingest a subset of data. This option is available for all the RDBMS and Generic JDBC sources. For more details, see Filter Query for RDBMS Sources

Optimization Configuration

Split By Column

The column used to crawl the table in parallel with multiple connections to the database. Split-by column can be an existing column in the data. Any column for which minimum and maximum values can be computed, can be a split-by key. Select the Create Derived Split Column option and provide the Derived Split Column Function to derive a column from the Split By column. This option is enabled only if the Split By column datatype is date or timestamp. The data will be split based on the derived value.

Create Derived Split Column

Select the Create Derived Split Column option and provide the Derived Split Column Function to derive from the Split By column. This option is enabled only if the Split By column data type is integer or timestamp. The data will be split based on the derived value. mod: For every float, integer, or long value, the mod function is performed on the number of connections that are available on the database and based on the mod value the corresponding database is updated. The mod function is first applied to the column and after getting the output the split gets applied and the data is evenly distributed. The mod function is applied on each row level and all the connections run parallelly until the end of the job.

Target Configuration

Onboarding Option

Select the target onboarding option. The options available are direct and indirect.

Dataset Name

Dataset name of the BigQuery target.

Table Name

Table name of the BigQuery Target.

Partition Type

The type of partition for the BigQuery table. By default, the Partition Type is set to None. The available options are Date Column, Timestamp Column, Integer Column. BigQuery Load Time option is available when the Onboarding Option is set to Indirect.

NOTE For other partition types, user can choose to create the table with desired partitions in BigQuery.

Partition Column

The column to be used to partition tables in target. The column needs to be of Date data type.

Partition Time Interval

This field is visible when the Partition type is Date Column, Timestamp Column, or _BigQuery Load Time. _ NOTE For Date column, the available options are Day, Month, and Year. For Timestamp column, the available options are Hour, Day, Month, and _Year. _For BigQuery Load Time, the available options are Hour, Day, Month, and Year.

Clustering Columns

The columns to be used for clustering. You can select up to 4 columns.

Is user managed?

When enabled, user managed table behavior works as an external table to Infoworks. The table will not be created/dropped or managed by Infoworks.

Generate History View

Select this option to preserve data in the History table. After each CDC, the data will be appended to the history table.