Source file formats

You can infer schema from source files in CSV, JSON, PARQUET, ORC and AVRO formats. The JSON files can be in any of the different flavors - JSON, JSON Lines, and Extended JSON Lines.

Compressed file formats

• You can also infer schema from CSV and JSON source files compressed in GZIP format. 

  • Ensure that you add another Format Option GZIP in the File Format Option List to work with these source files.

• You can infer schema from PARQUET, AVRO, and ORC files compressed into GZIP or SNAPPY files.

Source file locations

The ASI feature requires that your source files are available in an AWS S3 location.

Date and time formats

You can specify DATEFORMAT or TIMEFORMAT as another Format Option with INFERSCHEMA = TRUE in the File Format Option List. We support the following default values for these format options:

• YYYY-MM-DD for DATEFORMAT

• YYYY-MM-DD HH:MI:SS for TIMEFORMAT without time zone 

• YYYY-MM-DD HH:MI:SSOF for TIMEFORMAT with time zone

Alternatively, you can use DATEFORMAT='auto' or TIMEFORMAT='auto'. Learn more at Using automatic recognition with DATEFORMAT and TIMEFORMAT - Amazon Redshift.

Schema inference mechanism

When you set the ELT Load Snap to auto-infer source schema, the Snap:

• By default, reads the first 400 records in the input file to detect and assign the data type for each column.

• Assigns the VARCHAR data type to the corresponding column in Redshift if it cannot detect the data type.

Merging inferred schema

When you have multiple source files to read and load data from, the ASI feature reads all files (first 400 records only) and automatically merges the new columns/data types detected from the subsequent files with the schema detected from the first file.

• This feature is useful when you specify a File Name Pattern or multiple files in the File list for loading into your target CDW.

CSV

GZIP

N/A

DATEFORMAT
(also supports DATEFORMAT = 'auto')

YYYY-MM-DD

TIMEFORMAT
(also supports TIMEFORMAT = 'auto')

• YYYY-MM-DD HH:MI:SS for timestamp without time zone

• YYYY-MM-DD HH:MI:SSOF for timestamp with time zone

DELIMITER

Comma ( , )

ENCODING

UTF8

IGNOREHEADER

0

NULL

'\N'

QUOTE

Double quote mark ( “ )

JSON

JSON ‘auto’ | ‘auto ignorecase’

GZIP

N/A

DATEFORMAT

YYYY-MM-DD

TIMEFORMAT

• YYYY-MM-DD HH:MI:SS for timestamp without time zone

• YYYY-MM-DD HH:MI:SSOF for timestamp with time zone

DELIMITER

Pipe ( | )

ENCODING

UTF8

QUOTE

Double quote ( “ )

USAGE

Storage integration

CREATE STAGE

Schema

Source file locations

The ASI feature for Snowflake requires that your source files are available in an Amazon S3, WASB, ADLS Gen2, or Google Cloud Storage location.

Source file formats

• You can infer schema from source files in CSV, JSON, PARQUET, AVRO and ORC formats.

  • The JSON files can be in any of the different types - JSON, JSON Lines, and Extended JSON Lines. 

• For file formats other than CSV, the ASI feature for Snowflake considers the entire target table as one column and assigns VARIANT data type to this column (table).

Schema inference mechanism

When you set the ELT Load Snap to auto infer source schema, the Snap:

• By default, reads the first 400 records in the input file to detect and assign the data type for each column.

• Assigns the VARCHAR data type to the corresponding column in Snowflake if it cannot detect the data type.

Full file scans

• A full file scan that consumes lots of resources and time can provide the best results—accurate, maximum precision, and scale values of a decimal type column. This feature is in the upcoming releases of the ELT Snap Pack.

Merging inferred schema

When you have multiple source files to read and load data from, the ASI feature reads all files (only first 400 records) and automatically merges the new columns/data types detected from the subsequent files with the schema detected from the first file.

• This feature is useful when you specify a File Name Pattern or multiple files in the File list for loading into your target CDW.

Needs a consistent header row across files

For accurate schema inference, ensure that the column names and the number of columns in each file's header match with those in the remaining specified files.

Date and time formats

You can specify DATE_FORMAT or TIME_FORMAT as another Format Option with INFERSCHEMA = TRUE in the File Format Option List. We support the following default values for these format options:

• AUTO for DATE_FORMAT

• AUTO for TIME_FORMAT 

• AUTO for TIMESTAMP_FORMAT

Alternatively, you can use DATEFORMAT='AUTO' or TIMEFORMAT='auto'. Learn more at Using automatic recognition with DATEFORMAT and TIMEFORMAT - Snowflake.

Compressed file formats

The ASI feature works for compressed files stored in an Amazon S3 location, but not on WASB, ADLS Gen2, or Google Cloud Storage.

• You can infer schema from CSV and JSON source files compressed in GZIP format. 

  • Ensure that you add another Format Option GZIP in the File Format Option List to work with these source files.

• You can infer schema from PARQUET, AVRO, and ORC files compressed into GZIP or SNAPPY files.

Authentication types

Source Location Credentials and Storage Integration for Amazon S3, WASB, and ADLSGEN2, Storage Integration for GCS.

CSV

COMPRESSION

NONE

DATE_FORMAT
(also supports DATEFORMAT = 'YYYY-MM-DD', 'DD-MON-YYYY', and 'MM/DD/YYYY')

AUTO

TIME_FORMAT

AUTO

TIMESTAMP_FORMAT

AUTO

FIELD_DELIMITER

Comma ( , )

RECORD_DELIMITER

New line character

ENCODING

UTF8

SKIP_HEADER

0

NULL_IF

\\N

ESCAPE

NONE

ESCAPE_UNENCLOSED_FIELD

Backslash ( \\ )

FIELD_OPTIONALLY_ENCLOSED_BY

(To ensure that all the nonVARCHAR values in the source file are assigned the most appropriate data type)

NONE

DELIMITER

Pipe ( | )

ENCODING

UTF8

QUOTE

Double quote ( “ )

Colocation of the JCC and input data files

• Ensure that the processing entity (JCC) and the source data are on the same node or cluster to avoid any possible performance degradation in ASI for networking overheads.

  • If the input file record size is moderate, reading 400 records from a different location may not incur a significant networking overhead.

  • If the record size is massive, the networking overhead involved significantly affects both the ASI and the Snap's performance.

Data Compression

Use GZIP-compression for loading CSV or JSON files, Snappy, or GZIP-compression for loading Apache file formats: PARQUET, AVRO, and ORC files to save disk space. ASI supports both of these compression modes.