You need something more complicated than what can be done using BigQuery/Snowflake (that remaining 20%, though I would say 10%)? Export the dataset to CSV/Parquet/Avro/ORC/whatever and process it with anything, including Dataproc/HDInsight/EMR or even Databricks. That's actually a common pattern.
Can't do a simple
select * from s3://file.parquet
Many times the data doesn't even resemble anything tabular before I structure it in python scripts. Why would I load it inside a warehouse only to then pull it down to do some python processing and loading it back. Which makes the data travel from DWH storage to a data lake and then to my compute cluster and then the same cumbersome roadtrip back. Pretty wasteful. Spark at least allows me to schedule a python function across a cluster while copying only from lake to my compute node and back.
Data warehouses like BQ and Snowflake are great for data scientists after a bunch of engineers slice and dice raw data into clean tables. For anyone working with not yet structured data, data lake wins hands down.
[1] https://docs.snowflake.com/en/user-guide/querying-stage.html [2] https://docs.snowflake.com/en/user-guide/tables-external-int...
External table in Snowflake only allows you to ingest data from s3 to their storage (which is also s3 behind the scenes).
Perhaps something has changed since the last time I tried, but when I tried my conclusion was that "external tables" in Snowflake are not what you think they are.
Also I have not seen examples of "select * from s3://file.json" in the links you provided.
I would like to be corrected if I'm mistaken.
No. External table doesn't do any "ingesting" whatsoever. You can if you want to but you thats not the primary usecase.
https://docs.snowflake.com/en/user-guide/tables-external-int...
" enables querying data stored in files in an external stage as if it were inside a database "
> I have not seen examples of "select * from s3://file.json" in the links you provided.
you cannot directly query a random file off of s3( not sure why someone would want to do that) . Snowflake is a database , not a python script.
You have to build a an external table
" create external table abc location=s3://dir-to-file "
then
" select * from abc "
> "external tables" in Snowflake are not what you think they are.
They are exactly what you think they are. Tables stored externally to snowflake.
That's not really true; Snowflake can query directly from S3 just fine, even from other clouds. You just need to set up the credentials (or supply them in the query, but that's not usually a good idea).
Disclaimer: I work for Snowflake
This is not true. Snowflake allows you to create an external stage (pointer to s3) and then query any prefix you want as long as you provide the correct file format arguments or types.
select * from @somestage/someprefix file_format(....)
edit: hadn't refreshed the page and i can see others have already responded to this point.
Pretty similar as defining a Hive Table and then using any other engine to process it.
PS: BigQuery Omni (now beta) will support object storage solutions from other cloud providers.