Links

INSERT

An INSERT job defines a query that pulls in a set of data based on the given SELECT statement and inserts it into the designated target. This query is then run periodically based on the RUN_INTERVAL defined within the job.
By default the INSERT job simply appends the new data to the target table. For data lake tables, those created using CREATE TABLE in SQLake and managed by AWS Glue Data Catalog, when a primary key is defined the job will perform an upsert operation (update if PK exist, insert if it does not).
When writing to non-data lake targets, like Snowflake and Redshift, the INSERT job will not recognize a primary key, if defined on the target table, and will only perform an INSERT operation. If you want to upsert, refer to the MERGE command.
Additionally, if two jobs writing to the same table rewrite the same primary key, it is nondeterministic as to which job's data ends up in the table and is thus not recommended.
See also:
MERGE

Syntax

Jump to

Job identifier

Valid identifiers match the following format:
identifier = "([^"]|"")*"|[A-Za-z_][A-Za-z0-9_]*;

Job options

Target location

Target definition

{ <table_identifier>
| SNOWFLAKE <catalog_name>.<schema_name>.<table_name>
| REDSHIFT <catalog_name>.<schema_name>.<table_name>
| S3 BUCKET = <bucket_name>
| ELASTICSEARCH
}

MAP_COLUMNS_BY_NAME

The MAP_COLUMNS_BY_NAME keyword maps columns from the SELECT statement to the table by the names of the returned columns in the query. Columns listed after EXCEPT are excluded from the final table.
When MAP_COLUMNS_BY_NAME is set, the SELECT statement must provide an alias for each field or they must be a simple column reference or a star expression. The columns are then mapped to the fields of the same name in the table.
Otherwise columns are mapped by ordinal positions with reference to the provided column list or the order of the columns in the table.
When using a star query in the SELECT statement, MAP_COLUMNS_BY_NAME is required. Additionally, all primary key and partition columns must be mapped.

EXCEPT

Columns listed after the EXCEPT keyword are not written to your target table.

Examples

Data lake table

Snowflake

S3

Elasticsearch

PostgreSQL