Staging S3 files with External Tables directly

0
votes

I am trying to find a way to directly querying csv, json and avro files without having to stage the files first.

Is there a way to do this?

What I have found so far are:

  1. External Tables https://docs.snowflake.net/manuals/user-guide/tables-external-intro.htm
  2. Another tool called FiveTran, that lets me correct any automatic detection of the files -> table detection

Is there another way to directly read the file to Snowflake without staging the file?

2
snowflake-cloud-data-platform
Can you clarify what you mean by stage? Are you trying to avoid staging them within Snowflake (e.g. reading them from something like AWS S3) or trying to avoid staging them anywhere (read from a local store, stream from a source system)? – BarneyL

2 Answers

2
votes

You can query files directly on s3 if you create a Snowflake "stage" object which references that s3 bucket/location.

Details can be found here:

https://docs.snowflake.net/manuals/user-guide/querying-stage.html#querying-data-in-staged-files

In this situation, you aren't actually "loading" data into Snowflake, you're just creating a "stage" object in the database which turns out to be a "pointer" to the s3 location.

I hope this helps...Rich

1
votes

All files must be staged in either an internal or external location that is referenced by a stage object. Using external tables can be done to integrate with Apache Hive Metastores, but still require a stage object. They can be somewhat limiting and slower since the data is not stored in Snowflake.

https://docs.snowflake.net/manuals/user-guide/tables-external-hive.html

What are you trying to avoid by not staging your files?