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:
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
...;