Parsing the CSV header is not supported in external tables, columns must be renamed to match the original names
Description
Currently, Snowflake external tables do not support parsing the header of CSV files. When a external table with no explicit column list and CSV file format is found, SnowConvert will produce the SKIP_HEADER file format option to avoid runtime errors, however, this will cause the table column names to have the autogenerated names c1, c2, ..., cN.
This FDM is generated to notify that the header can not be parsed and that manually renaming the columns is necessary to preserve the original column names.
Code Example
Input Code:
IN -> BigQuery_01.sql
CREATE OR REPLACE EXTERNAL TABLE test.my_external_table_csv
OPTIONS(
FORMAT='CSV',
URIS=['gs://sc_external_table_bucket/folder_with_csv/Employees.csv']
);
Output Code:
OUT -> BigQuery_01.sql
CREATE OR REPLACE TEMPORARY FILE FORMAT SC_TEST_MY_EXTERNAL_TABLE_CSV_FORMAT
TYPE = CSV
SKIP_HEADER = 1;
CREATE OR REPLACE EXTERNAL TABLE test.my_external_table_csv
--** SSC-FDM-BQ0005 - PARSING THE CSV HEADER IS NOT SUPPORTED IN EXTERNAL TABLES, COLUMNS MUST BE RENAMED TO MATCH THE ORIGINAL NAMES **
USING TEMPLATE (
SELECT
ARRAY_AGG(OBJECT_CONSTRUCT('COLUMN_NAME', COLUMN_NAME, 'TYPE', TYPE, 'NULLABLE', NULLABLE, 'EXPRESSION', EXPRESSION))
FROM
TABLE(INFER_SCHEMA(LOCATION => '@EXTERNAL_STAGE/folder_with_csv/Employees.csv', FILE_FORMAT => 'SC_TEST_MY_EXTERNAL_TABLE_CSV_FORMAT'))
)
!!!RESOLVE EWI!!! /*** SSC-EWI-BQ0015 - EXTERNAL TABLE REQUIRES AN EXTERNAL STAGE TO ACCESS gs://sc_external_table_bucket, DEFINE AND REPLACE THE EXTERNAL_STAGE PLACEHOLDER ***/!!!
LOCATION = @EXTERNAL_STAGE
AUTO_REFRESH = false
PATTERN = 'folder_with_csv/Employees.csv'
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);
Recommendations
Please rename the table columns to their original names from the file to complete the transformation
