4 ms·

While both BQ and Snowflake are adopting lake features, they still only support parquet and other file formats for loading, not for querying. Can't do a simple

While both BQ and Snowflake are adopting lake features, they still only support parquet and other file formats for loading, not for querying. Can't do a simple select * from s3://file.parquet which you can do in Spark. Having to load it into the data warehouse means that you duplicate your data two times and it is stupidly annoying. 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.

carlineng 6y ago
Not quite -- Snowflake allows you to query data directly from S3 without having to ingest it. You can either query the S3 file directly [1], or define an external table on top of it [2]. If the data is JSON, Avro, XML, or some other "semi-structured" format, then Snowflake's "variant" data type makes this type of data very easy to work with. For raw text or log lines, you can still load to Snowflake and process using regex or UDFs. For unstructured or "complex" data like images and PDFs, then you may need to reach for a different compute environment. [1] https://docs.snowflake.com/en/user-guide/querying-stage.html https://docs.snowflake.com/en/user-guide/querying-stage.html [2] https://docs.snowflake.com/en/user-guide/tables-external-intro.html https://docs.snowflake.com/en/user-guide/tables-external-int...
- snidane 6y ago
  Yeah I thought Snowflake external tables would do this but it is not the case. 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.
  - dominotw 6y ago
    > External table in Snowflake only allows you to ingest data from s3 to their storage 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-intro.html 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.
    - snidane 6y ago
      Thanks for clarification. I either wasn't able to figure this out back then or things have changes since then.
mrbungie 6y ago
In BQ you can query data as an external table with Hive Partioning. No need for duplication and extremely useful for consuming "Landing Zones" in a Datalake. 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.
fphhotchips 6y ago
> Can't do a simple select * from s3://file.parquet 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
chrisjc 6y ago
> While both BQ and Snowflake are adopting lake features, they still only support parquet and other file formats for loading, not for querying. 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.

carlineng 6y ago

Not quite -- Snowflake allows you to query data directly from S3 without having to ingest it. You can either query the S3 file directly [1], or define an external table on top of it [2]. If the data is JSON, Avro, XML, or some other "semi-structured" format, then Snowflake's "variant" data type makes this type of data very easy to work with. For raw text or log lines, you can still load to Snowflake and process using regex or UDFs. For unstructured or "complex" data like images and PDFs, then you may need to reach for a different compute environment. [1] https://docs.snowflake.com/en/user-guide/querying-stage.html https://docs.snowflake.com/en/user-guide/querying-stage.html [2] https://docs.snowflake.com/en/user-guide/tables-external-intro.html https://docs.snowflake.com/en/user-guide/tables-external-int...

snidane 6y ago
Yeah I thought Snowflake external tables would do this but it is not the case. 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.
- dominotw 6y ago
  > External table in Snowflake only allows you to ingest data from s3 to their storage 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-intro.html 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.
  - snidane 6y ago
    Thanks for clarification. I either wasn't able to figure this out back then or things have changes since then.

mrbungie 6y ago

In BQ you can query data as an external table with Hive Partioning. No need for duplication and extremely useful for consuming "Landing Zones" in a Datalake. 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.

fphhotchips 6y ago

> Can't do a simple select * from s3://file.parquet 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

chrisjc 6y ago

> While both BQ and Snowflake are adopting lake features, they still only support parquet and other file formats for loading, not for querying. 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.