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

3
votes

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:

  • a/b/resource.json
  • a/b/metadata.json
  • c/d/resource.json
  • c/d/metadata.json

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:

  • entry from a/b/resource.json
  • entry from a/b/metadata.json
  • entry from c/d/resource.json
  • entry from c/d/metadata.json

metadata table in snowflake:

  • entry from a/b/resource.json
  • entry from a/b/metadata.json
  • entry from c/d/resource.json
  • entry from c/d/metadata.json

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

I would like to achieve this:

resource table in snowflake:

  • entry from a/b/resource.json
  • entry from c/d/resource.json

metadata table in snowflake:

  • entry from a/b/metadata.json
  • entry from c/d/metadata.json
1
amazon-s3snowflake-cloud-data-platform

1 Answers

3
votes

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';