file_format

Target database: Snowflake, Amazon Redshift, Azure SQL Database, Azure Synapse SQL, Azure Synapse DW, Databricks SQL

Resolved as the file format options in file load execution.

See also:

• OVERRIDE_FILE_LOAD

• OPT_FILE_FORMAT_OPTIONS

• Variables

Usage

<file_format>

Notes

Supported in file loads.

File format options are defined when queuing source files (see Loading source data) and they can be overridden with OPT_FILE_FORMAT_OPTIONS.

Examples

Snowflake: Variable used in OVERRIDE_FILE_LOAD

File load customized with OVERRIDE_FILE_LOAD:

COPY INTO ...
FROM ...
FILE_FORMAT=(<file_format>)
...

File load executed in Snowflake:

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

Amazon Redshift: Variable used in OVERRIDE_FILE_LOAD

File load customized with OVERRIDE_FILE_LOAD:

COPY ...
FROM ...
<file_format>
...

File load executed in Snowflake:

COPY ...
FROM ...
timeformat 'auto' dateformat 'auto' format csv gzip manifest DELIMITER ',' IGNOREHEADER 1 COMPUPDATE OFF STATUPDATE OFF
...
;

Azure SQL Database: Variable used in OVERRIDE_FILE_LOAD

File load customized with OVERRIDE_FILE_LOAD:

BULK INSERT ...
FROM ...
WITH (..., <file_format>);

File load executed in Azure SQL Database:

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

Azure Synapse SQL and Azure Synapse DW: Variable used in OVERRIDE_FILE_LOAD

File load customized with OVERRIDE_FILE_LOAD:

COPY INTO ...
FROM ...
WITH (..., <file_format>);

File load executed in Azure Synapse SQL:

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

Databricks SQL: Variable used in OVERRIDE_FILE_LOAD

File load customized with OVERRIDE_FILE_LOAD:

COPY INTO ...
FROM ...
<file_format>
...;

File load executed in Azure Synapse SQL:

COPY INTO ...
FROM ...
FILEFORMAT=CSV
...;