5 ms·

For a lot of teams, S3 is a data warehouse, and you can treat it just like HDFS for the most part with most things in the big-data ecosystem. Presto works well

For a lot of teams, S3 is a data warehouse, and you can treat it just like HDFS for the most part with most things in the big-data ecosystem. Presto works well for letting you access it from these locations without having to explicitly import it (assuming it's in a traditional data warehouse or a common SQL DB).

iamsomewalrus 8y ago
:this: A concept that's underlying the move to a datalake architecture (read: keeping your data in its rawest form, and its transforms in S3 or HDFS) is decoupling your compute from storage. Motivating example: you have huge tables in Redshift that are either infrequently accessed or the usefulness of the data decays over time (website logs, customer order information). In this scenario you're paying a lot just to keep data in Redshift (storage) but a large subset of the data is laying dormant (no compute). If you're bought into the Redshift ecosystem this is where Redshift Spectrum comes in. If you're a smaller company you could just store the data in S3 and "spin up" the compute when you need it (Athena, Glue jobs, or Elastic Map Reduce clusters).
- Cidan 8y ago
  BigQuery on GCP does this as well, without any extra work. (Disclaimer: I work for Google)
  - some_account 8y ago
    Google could shut down an entire account without any communication and kill a business in the process. I would never pick Google for anything important.
- pweissbrod 8y ago
  For those of us not actually working at aws redshift gets insanely expensive when your data set grows into the terabytes. Analytics on s3 is much more cost effective using athena snowflake or old fashioned emr as yourdata grows
wenc 8y ago
I wonder if anyone here has a good heuristic for identifying the conditions under which using S3 + SQL layer as a data warehouse is a better choice than a SQL database? I've been exploring the former and it seems to only make sense if the size of your data is at a scale that is beyond what a single SQL database instance can handle, and even then, you can continue to scale out with systems like Citus so the limit isn't a hard one. SQL gives one so much (data mutability, consistency, indexes, etc.) that I am hesitant to give it up unless the tradeoffs make sense.
- coffee 8y ago
  I've often wondered the same, thanks for raising this question! I hope somebody with direct experience can chime in!
- zwkrt 8y ago
  The decision to add SQL on top of S3 probably had a lot to do with a very common use case: people had structured data in S3 but no way to query it. However, it is also very useful if the following two things are true: 1. You have a very large stream of incoming structured data that is mostly write-once-read-never, like logs. 2. Your query use cases are relatively simple and static. If those fit your use case, then S3 + parquet + Athena is very easy and very cheap.
- iamsomewalrus 8y ago
  Having been back in SQL land for a bit (vanilla MySQL on RDS) I have to say that I _love_ a well designed SQL database. I forgot how much I had given up in NoSQL land. Goodreads hit scaling issues a while ago with Active Record and a single database so we broke up the data into separate MySQL servers. At that point joining data across DB servers is impossible so we went with Redshift for BI. Nowadays we would probably go with a datalake on S3.
- bcbrown 8y ago
  I've worked with a S3 + SQL system. It was used for serving data for a reporting dashboard where the stored data was in the 0.1-10 TB range. As the use case was only semi-interactive (users didn't mind waiting 1-10 seconds for a report), and all the queries were pre-defined, this solution was a good fit. I think it makes sense when there's no in-place updates; either querying write-once data like logs or the output of batch data processing roll-ups that replace the previous data. The less you need the relational model (like joins), the better, but some of those needs can be met through careful design of the storage schema and denormalization. I wouldn't advocate this sort of solution if your requirements include in-place updates of existing data, frequent/granular updates of new data, expressive ad-hoc queries that use the full capability of relational algebra, or tight latency requirements. You also lose the safety net of referential integrity and table-level constraints, as those are now enforced in custom code that can have bugs. I would say maintaining this system cost about a half-engineer for ongoing maintenance and new functionality.

iamsomewalrus 8y ago

:this: A concept that's underlying the move to a datalake architecture (read: keeping your data in its rawest form, and its transforms in S3 or HDFS) is decoupling your compute from storage. Motivating example: you have huge tables in Redshift that are either infrequently accessed or the usefulness of the data decays over time (website logs, customer order information). In this scenario you're paying a lot just to keep data in Redshift (storage) but a large subset of the data is laying dormant (no compute). If you're bought into the Redshift ecosystem this is where Redshift Spectrum comes in. If you're a smaller company you could just store the data in S3 and "spin up" the compute when you need it (Athena, Glue jobs, or Elastic Map Reduce clusters).

Cidan 8y ago
BigQuery on GCP does this as well, without any extra work. (Disclaimer: I work for Google)
- some_account 8y ago
  Google could shut down an entire account without any communication and kill a business in the process. I would never pick Google for anything important.
pweissbrod 8y ago
For those of us not actually working at aws redshift gets insanely expensive when your data set grows into the terabytes. Analytics on s3 is much more cost effective using athena snowflake or old fashioned emr as yourdata grows

wenc 8y ago

I wonder if anyone here has a good heuristic for identifying the conditions under which using S3 + SQL layer as a data warehouse is a better choice than a SQL database? I've been exploring the former and it seems to only make sense if the size of your data is at a scale that is beyond what a single SQL database instance can handle, and even then, you can continue to scale out with systems like Citus so the limit isn't a hard one. SQL gives one so much (data mutability, consistency, indexes, etc.) that I am hesitant to give it up unless the tradeoffs make sense.

coffee 8y ago
I've often wondered the same, thanks for raising this question! I hope somebody with direct experience can chime in!
zwkrt 8y ago
The decision to add SQL on top of S3 probably had a lot to do with a very common use case: people had structured data in S3 but no way to query it. However, it is also very useful if the following two things are true: 1. You have a very large stream of incoming structured data that is mostly write-once-read-never, like logs. 2. Your query use cases are relatively simple and static. If those fit your use case, then S3 + parquet + Athena is very easy and very cheap.
iamsomewalrus 8y ago
Having been back in SQL land for a bit (vanilla MySQL on RDS) I have to say that I _love_ a well designed SQL database. I forgot how much I had given up in NoSQL land. Goodreads hit scaling issues a while ago with Active Record and a single database so we broke up the data into separate MySQL servers. At that point joining data across DB servers is impossible so we went with Redshift for BI. Nowadays we would probably go with a datalake on S3.
bcbrown 8y ago
I've worked with a S3 + SQL system. It was used for serving data for a reporting dashboard where the stored data was in the 0.1-10 TB range. As the use case was only semi-interactive (users didn't mind waiting 1-10 seconds for a report), and all the queries were pre-defined, this solution was a good fit. I think it makes sense when there's no in-place updates; either querying write-once data like logs or the output of batch data processing roll-ups that replace the previous data. The less you need the relational model (like joins), the better, but some of those needs can be met through careful design of the storage schema and denormalization. I wouldn't advocate this sort of solution if your requirements include in-place updates of existing data, frequent/granular updates of new data, expressive ad-hoc queries that use the full capability of relational algebra, or tight latency requirements. You also lose the safety net of referential integrity and table-level constraints, as those are now enforced in custom code that can have bugs. I would say maintaining this system cost about a half-engineer for ongoing maintenance and new functionality.