CODEWITHSUNDEEP
amazon-s3snowflake-cloud-data-platform
Annie
Annie

Reputation: 41

Snowflake-S3: Ingesting two types of files (from the same S3 bucket) to two different tables

I currently have an S3 bucket with two different types of JSON files: resource.json and metadata.json. This is the structure of our bucket:

bucket foo:

I would like to ingest each type file to its corresponding table (like resource.json --> resource table, metadata.json --> metadata table) but I can only use one SQS queue per bucket.

Given I have one bucket, two stages, two tables, two pipes (one stage/pipe/table per JSON file type), is there a way to configure Snowflake to read from the S3 bucket and put the contents of each file into the right table?

Right now I have this:

resource table in snowflake:

metadata table in snowflake:

Italics: the entries I don't want in those tables

I would like to achieve this:

resource table in snowflake:

metadata table in snowflake:

Upvotes: 2

Views: 298

Answers (1)

Annie
Annie

Reputation: 41

I just needed to add this line to each pipe's SQL:
PATTERN='.*/<metadata or resource>[.]json'

So the Pipe's SQL statement would look like this:

COPY INTO <table> FROM (
    SELECT <fields> FROM @<stage>
)
PATTERN='.*/<metadata or resource>[.]json';

Upvotes: 2

Related Questions