OPT_FILE_FORMAT_OPTIONS

Target database: Snowflake, Amazon Redshift, Azure SQL Database, Azure Synapse SQL, Google BigQuery

OPT_FILE_FORMAT_OPTIONS is a load option that overrides file formatting options in file loads.

Affected command per supported database management system:

Target DBMS	Command
Snowflake	COPY INTO
Amazon Redshift	COPY
Azure SQL Database	BULK INSERT
Azure Synapse SQL	COPY
Google BigQuery	LOAD DATA INTO

See also:

Usage

OPTION

OPT_FILE_FORMAT_OPTIONS: sql_code

Notes

OPT_FILE_FORMAT_OPTIONS can be used with file loads.

OPT_FILE_FORMAT_OPTIONS is also supported in OVERRIDE_FILE_LOAD load steps where it will be resolved in place of the <file_format> variable.

Examples

Snowflake file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL

TYPE='CSV' SKIP_HEADER=0 FIELD_DELIMITER=';' COMPRESSION='AUTO' FIELD_OPTIONALLY_ENCLOSED_BY='"'

File load executed in Snowflake:

SQL

COPY INTO ...
FROM ...
FILE_FORMAT=(TYPE='CSV' SKIP_HEADER=0 FIELD_DELIMITER=';' COMPRESSION='AUTO' FIELD_OPTIONALLY_ENCLOSED_BY='"');

Amazon Redshift file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL

TIMEFORMAT 'auto' DATEFORMAT 'auto' FORMAT CSV DELIMITER ';' COMPUPDATE OFF STATUPDATE OFF

File load executed in Redshift:

SQL

COPY ...
FROM ...
TIMEFORMAT 'auto' DATEFORMAT 'auto' FORMAT CSV DELIMITER ';' COMPUPDATE OFF STATUPDATE OFF;

Azure SQL Database file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL

FORMAT='CSV', CODEPAGE=65001, FIRSTROW=1, FIELDTERMINATOR=';', ROWTERMINATOR='\\r\\n'

File load executed in SQL database:

SQL

BULK INSERT ...
FROM ...
WITH ( ... ,
FORMAT='CSV', CODEPAGE=65001, FIRSTROW=1, FIELDTERMINATOR=';', ROWTERMINATOR='\\r\\n');

Azure Synapse SQL file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL

CREDENTIAL=(IDENTITY='Managed Identity'), FILE_TYPE = 'CSV', FIELDQUOTE = '"', FIELDTERMINATOR=',', COMPRESSION = 'Gzip', ENCODING = 'UTF8', FIRSTROW = 2

Note that OPT_FILE_FORMAT_OPTIONS overrides the entire WITH clause so also the CREDENTIAL argument must be defined.

File load executed in SQL database:

SQL

COPY INTO ...
FROM ...
WITH (CREDENTIAL=(IDENTITY='Managed Identity'), FILE_TYPE = 'CSV', FIELDQUOTE = '"', FIELDTERMINATOR=',', COMPRESSION = 'Gzip', ENCODING = 'UTF8', FIRSTROW = 2);

Google BigQuery file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL

FORMAT='CSV', SKIP_LEADING_ROWS=1, FIELD_DELIMITER=';', ALLOW_QUOTED_NEWLINES=TRUE, COMPRESSION='GZIP', ENCODING='UTF8'

File load executed in BigQuery:

SQL

LOAD DATA INTO ...
FROM FILES(
  FORMAT='CSV', SKIP_LEADING_ROWS=1, FIELD_DELIMITER=';', ALLOW_QUOTED_NEWLINES=TRUE, COMPRESSION='GZIP', ENCODING='UTF8', 
  uris=[...]
);