I have 40 text files of different/inconsistent layouts. My end goal is to get this loaded to a consistent schema in our environment. Short of designing 40 load patterns, how can I best "dump" all of these into Snowflake on a recurring basis? Once loaded I can more easily parse. Does anyone have a good strategy on how to most easily do the "dump in into Snowflake" part?
1 Answers
You can load data from a stage (file location) into a STAGING table using COPY INTO with load transformations.
To address your specific points:
- By specifying directory, snowflake will load all files in that directory
- I believe you can only load one type of file format at a time.
Ex:
COPY INTO my_database.STAGING.foo (file_name, file_row_number, data)
FROM
's3://mybucket/sub_directory_name/' -- Or other stage
FILE_FORMAT = (TYPE = CSV)
;
Do note that CSV data represents a slightly difficult case with schema mismatch – your best solution is populate your staging table as a sparse matrix, the transform in downstream manipulations.
Related