4 ms·
We run most of our stuff on AWS, but we use BigQuery for the data warehouse. It's a nice product - no cluster to manage, pretty good apis for getting data in a
by mattbillenstein 9y ago
We run most of our stuff on AWS, but we use BigQuery for the data warehouse. It's a nice product - no cluster to manage, pretty good apis for getting data in and out of it, a decent in-browser SQL editor for casual users, and connectivity to a bunch of other BI tools (Metabase is a good OSS dashboarding and visualization tool we use).
We use a mix of hourly/daily .json.gz export/import jobs and streaming inserts. This is all automated with python scripts and airflow.
For streaming events, we post from the client to nginx, nginx+lua timestamps and posts that to nsq, we have an nsq python consumer that flattens and streams those events into bigquery; nsq-to-file rotates line-delimited json hourly which is gzipped and uploaded to gcs for archival. The latter is necessary for some types of schema changes; you'll want to drop the old table and import the old data into the new schema.
Tips:
- stick to StandardSQL, more featureful SQL dialect
- flatten data where possible, arrays of sub-records are hard to work with
- Partition large tables by date so you don't pay for querying really old data
- use the timestamp type vs string/int fields, saves you cast when writing a query
- cpburns2009 9y agoWould you recommend Airflow? I've been researching pipeline management libraries compatible with Python to replace our fragile cron/bash setup. From what I've read Airflow appears to be the best contender in the Python space.
- mattbillenstein 9y agoI do, I tried Luigi before Airflow and I rather liked the simplicity of Luigi's checkpointing of jobs (files vs a db with airflow), but Airflow has a nice UI, charts and graphs that are handy and nice to look at. Both describe DAGs using python, Airflow has more options and executors as I recall, but we only really run bash to run python jobs. The trouble with Airflow is it uses a database for state, and you can get in situations where a task is failing, so the dagrun fails; so you clear the state after fixing the task using the web ui and it'll usually run and everything is fine. But sometimes you get into a state where it says the dag is deadlocked and doing this doesn't work. But, steady-state, it works pretty well; I think among the things I've tried (cron + python, luigi, airflow), it's the best I've come across.
- jackgolding 9y agoI used airflow a few jobs ago where I was a 1 man data engineer (single server doing ETL) - honestly wish I just used CRON/bash.
- mattbillenstein 9y agoWhen you have a few things to run, yeah -- and we started this way. When you have a bunch of multi-step things with various dependencies and you want to visually see status -- airflow is worth the headache.