Configuring a Table

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.

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.

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

NOTE If you select Delimited Text Files, a new set of fields appear with default values. You can configure those values as per your requirement. For more details, refer to Additional Information: Delimited Text Files.

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

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.

The format in which the tables must be stored. The options include Read Optimized (Delta), Write Optimized (Avro), Parquet, and Delimited File Target.

NOTE Selecting the storage format is not supported in BigQuery and Snowflake environments,

This field indicated the way table is managed. There are three table types:

Infoworks Managed Table: The table will be managed by Infoworks

User Managed Table without Data: User does not have data for the table on the target yet. The tables will be treated as external tables and Infoworks will not manage (including CREATE/DROP) the Target table, Merge Table and Error Table. These need to be created and managed outside Infoworks by the user. If the tables exist and the schema does not match, the job will fail.

User managed Table with Data: User already has the data for the table on the target. The tables will be treated as external tables and Infoworks will not manage (including CREATE/DROP) the Target table, Merge Table and Error Table. These need to be created and managed outside Infoworks by the user. If the tables exist and the schema does not match, the job will fail.

This field indicates the path where the target table’s data is stored.

This field appears only if you have selected either User Managed Table without Data or User Managed Table with Data.

NOTE Ensure that the directory is unique.

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.

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.

The watermark column to identify the incremental records.

NOTE This field is displayed only for incremental ingestion.

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.

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.

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.

The watermark column to identify the incremental records.

NOTE This field is displayed only for incremental ingestion.

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.

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

direct: It is a one-step process where data is directly loaded into BigQuery from Source.

indirect: It is a two-step process where data is loaded to BigQuery via temporary GCS location. You can also persist this data present in temporary GCS location by setting the following advanced configuration should_persist_staging_data at table level.

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.

This field is visible when the Partition type is BigQuery Load Time, Date Column, or Timestamp Column.

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.

The columns to be used for clustering. You can select up to 4 columns. Cluster columns must be one of the following types:

STRING

INT

LONG

DECIMAL

DATE

TIMESTAMP