Hey! I’m the author of this post. I’m pretty chuffed to see this here. Happy to answer any questions.
As mentioned in another comment I’ve found having Dynamo snapshots in Athena really useful as an oncall to sanity check snapshots (what was the state of Harry Potter 3 months ago compared to now?) and to answer product questions that can only be answered from the raw production data.
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).
(Disclaimer: I work for Google)
I would never pick Google for anything important.
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.
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.
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.
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.
S3 + SQL is good for huge log/machine data, exploratory use cases that are not yet productionized, ELT (to get data from raw files into SQL, used as a feed to later layers), quick and dirty SQL against a directory of similarly structured files. I tend to think of it as a utility layer.
For long term analytics use, that involves a domain model, I’d still stick with dimensionally modeled (or snowflake) data warehouse techniques. Getting data into such a model can take weeks to months, so sometimes it might be better to do something quick and dirty in a data lake to prove a dataset or get a quick answer, vs. slow down the business waiting for a perfect model.
Lastly, I see storage + SQL as being the same conceptually as any RDBMS, with different performance, cost, and functionality. For example, SQL Server proprietary disk format + SQL Server query engine is somewhat analogous to Parquet + PrestoDB. In fact many proprietary vendors integrate with HDFS as a distributed storage layer for their proprietary formats which can be queried alongside open source storage formats by proprietary SQL query engines too.
I think it compares more with something like BigQuery but if you already have your data in S3 maybe you get a more well integrated system if you stick with AWS tools.
The DB scrape uses a template from Data Pipeline that under the hood uses the Dynamo DB Scan API. Not really surprising, but that API uses JSON. I wanted to use as much off the shelf software as I could to get data into Athena.
In this architecture Lambda is only used to listen to the SNS topic that fires when the Data Pipeline job succeeds or fails so we’re not pushing the limits of Lambda at all. You’d probably hit an EC2 limit Witt Data Pipeline before hitting your Lambda limit on the account.
* The default timeout is ~ 48 hours and you pay per Data Processing Unit (DPU) that you've provisioned the Job. * Currently it supports Python and Scala. As far as I'm aware you can't run Java jobs directly, but you can upload JAR libraries and use them in your code.
Re serverless vs dedicated / ephemeral clusters: Like with any serverless runtime environment you are trading convenience (across a few dimensions) for flexibility.
The Glue environment runs in a few limited runtimes and uses a specific version of Spark that you have no control over updating. Given that it's pretty quick to author a job, you can set the required DPU and Glue handles that, and you don't have to worry about sizing the cluster for your data size. For me most of my jobs fit within those constraints.
At some point on the cost curve it may make sense for you to move all of your jobs from Glue into a dedicated cluster on EMR. You may also get there sooner if you need to use specific frameworks or libraries.
[1]: https://aws.amazon.com/blogs/aws/amazon-s3-performance-tips-...
https://twitter.com/iamsomewalrus/status/1016368717880389632
I haven't had a chance to actually read your post yet, but I'm really looking forward to it.
We used Terraform instead of CloudFormation, although there are a few places it didn't/doesn't cover.
We're also doing protobuf -> parquet instead of JSON; Scala instead of Python, and one of our major feature/issues is that the incoming data is out-of-order; Firehose outputs to partitions based on when the event arrived, and we're repartitioning based on when the event occurred (according to a timestamp in the message)
I see you ran into the capitalization issue too :) I ran across docs somewhere that said something along the lines of Glue downcasing column names, which definitely fits observed behavior.
I'm hoping to turn what we've done into a similar post, although I think it'll be a month or two before I can get to that.
Edit: Oh, I also got a lot of mileage out of the Zeppelin Notebooks. Way better than a raw dev endpoint, but watch out on the cost for both :)
The notebooks can be provisioned with almost a single click from the Dev endpoint console, but they changed the recipe halfway through my work on it, and now you have to also SSH into the box and run a script to setup some of the security. :/ Still totally worth it, tho.
Thumbs up on the Glue Dev endpoint. It's been killer. I had trouble setting up a Notebook (I wanted to get fancy with Docker) and I usually use the Python repl link that's provided.
I'm working on a follow up post that removes the Data Pipeline -> Lambda and uses the new Glue DynamoDB integration.
Looking forward to your blog post!
What was the trouble you had with a notebook? I can probably post up some of our Terraform code (which includes notes on the parts Terraform doesn't cover).
Oh, yeah - there was also the S3... VPC? endpoint. That needed to exist.
There were a lot of wires, and Amazon documentation is decent as a reference but rubbish as a tutorial :/