Re: Ingesting Files - Same file name, modified con...

data-grassroots · ‎04-17-2024

Hey werners - thanks for your reply.

Sure, the data feed is ~1000 csv files from another party that get dumped out to a cloud storage bucket nightly. Their filenames remain the same and look something like acme_corp.csv. They contain unique ids along with a bunch of other product data as well as a last updated type column. We'll see new records, updated records, and old records may drop out of the feed (which we don't care about - our intent is to retain history).

The example I'm asking about is definitely COPY INTO. I have seen similar results with Autoloader although I've just started exploring that direction and Autoloader has tons of options that I haven't gotten around to yet.

The copy statement looks something like...

COPY INTO my_table

FROM (select

id::int,

name,

revised_date,

... ,

_metadata

FROM '/Volumes/test/sources/test/')

FILEFORMAT = CSV

FORMAT_OPTIONS (

'mergeSchema' = 'true',

'inferSchema' = 'true',

'header' = 'true',

'ignoreLeadingWhiteSpace' = 'true',

'ignoreTrailingWhiteSpace' = 'true',

'mode' = 'DROPMALFORMED'

-- PERMISSIVE', 'DROPMALFORMED', and 'FAILFAST'.'

)

COPY_OPTIONS ('mergeSchema' = 'true', 'force'='false');