sqlformatsnowflake-cloud-data-platformdouble-quotesdata-ingestion

In Snowflake when I use Infer Schema with Template to create a table using the CSV headers as column names, the names are wrapped in double quotes


I am attempting to read in CSV files from an external stage without having to manually specify the field names. Snowflake's Template function along with Infer Schema does the job, however, the result wraps the column names into double quotes which is an issue. In the underlying CSV file that is not the case and trying to alter the file format is of no help either. I is currently as follows:

CREATE OR REPLACE FILE FORMAT RAW.CSV_FORMAT
    TYPE = 'CSV' 
    FIELD_DELIMITER = ','  
    PARSE_HEADER = TRUE
    FIELD_OPTIONALLY_ENCLOSED_BY=NONE;

The code I have is this:

CREATE OR REPLACE TABLE RAW.TEST USING TEMPLATE (SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*)) FROm TABLE(INFER_SCHEMA(LOCATION=>'@RAW.STAGE/',FILE_FORMAT=>'RAW.CSV_FORMAT', FILES => ('example.csv'))));

I want to be able to run this but I get an error:

select field1 from RAW.TEST;

Only this works:

select "field1" from RAW.TEST; 

How can I remove the double quotes around the field name and what causes it?


Solution

  • In INFER_SCHEMA try using IGNORE_CASE => TRUE