4 ms·

First of all, ingest your data as .json.gz -- line delimited json that's gzipped -- chunk this by time range, perhaps hourly, on each box. Periodically upload

First of all, ingest your data as .json.gz -- line delimited json that's gzipped -- chunk this by time range, perhaps hourly, on each box. Periodically upload these files to the cloud -- S3 or Google CloudStorage, or both for a backup. You can run this per-node, so it scales perfectly horizontally. And .json.gz is easy to work with -- looking for a particular event in the last hour? gunzip -c *.json.gz | grep '<id>' ... Most of the big data tools out there will work with data in this format -- BigQuery, Redshift, EMR. EMR can do batch processing against this data directly from s3 -- but may not be suitable for anything other than batch processing. BigQuery and/or Redshift are more targeted towards analytics workloads, but you could use them to saw the data into another system that you use for OLAP -- MySQL or Postgres probably. BigQuery has a nice interface and it's a better hosted service than Redshift IMO. If you like that product, you can do streaming inserts in parallel to your gcs/s3 uploading process for more real-time access to the data. The web interface is not bad for casual exploration of terabytes of raw data. And the price isn't terrible either. I've done some consulting in this space -- feel free to reach out if you'd like some free advice.

devbug 11y ago
Sounds like you know your stuff! I couldn't find any email on your profile, do you mind sharing it?
- chejazi 11y ago
  Bottom of this page: http://vazor.com/unicode/ http://vazor.com/unicode/
- mattbillenstein 11y ago
  Sure, reverse moc.rozav@ttam
faizshah 11y ago
This is good advice but lz4 and it's chunked couterpart 4mc are much better suited for big data imo. This is the difference between 1 hour and 4+ hours for an archive of 300GB uncompressed on a single SSD based node.
- mattbillenstein 11y ago
  What is the size different between those? Issue is, I don't know that all the tools support lz4 like they support gzip.
  - faizshah 11y ago
    In my experience the difference is ~10% (.3 ratio vs ~.23 ratio) but for the increase in processing speed it's well worth it. If the tool supports Hadoop's FileInputFormat or a stream of whatever underlying data (you can use the 4mc or lz4 command line tool) it will work.
    - mattbillenstein 11y ago
      Processing speed due to the decompression algorithm or the I/O? You can get another 10% with gzip -7 vs the default I bet. I don't find the compression/decompression the bottleneck in most of this work and even then an extra 10% isn't worth sacrificing the interoperability between all the systems that support .json.gz Higher gzip levels I think only really affect the compression step -- decompression is the same cost. These are mostly write-once read many times sorts of datasets.
      - faizshah 11y ago
        It's due to the decompression algorithm, gzip gets better file sizes and is a better general solution. lz4 is usually 10% worse but will get vastly better decompression speeds. The I/O of an SSD is usually 300+MBps, lz4 will usually be around 270MBps, gzip will usually be <80MBps. lz4 will put less load on your cpu allowing you to get more throughput from your processing tasks. But I agree most people don't reach these bottlenecks because most people use tools which are not optimized for performance like using jackson or jq instead of boon or rapidjson. Look here for a reproducible test I ran on real data: https://news.ycombinator.com/item?id=10721645 https://news.ycombinator.com/item?id=10721645
        11y ago
derefr 11y ago
As a complete tangent, "hourly-chunked newline-delimited .json.gz pushed to S3" is exactly the format I ended up using to hack around the free-tier database limits on Heroku. It's a nice format!
- mattbillenstein 11y ago
  Not tangential at all -- it's much nicer than working with csv and gzip strips out the redundancy that you'd save with a csv anyway.
bsg75 11y ago
> BigQuery has a nice interface and it's a better hosted service than Redshift IMO. Do you have some points where BQ is advantageous over Redshift?
- mattbillenstein 11y ago
  You don't have to think about running or scaling a cluster -- it's a service that you dump data into and supports a pretty decent set of SQL. Cost is probably cheaper as well, but I don't have apples to apples numbers to compare. But the main point being, if you get your data in the cloud as .json.gz, you can use them all fairly easily. Maybe one solution fits 90% of your needs, and another one the other 10%. That's what I'm finding at least.
ap22213 11y ago
Just a pro tip: you don't want to use GZip since it can't be split, and if you want to parallelize effectively you need to split... that is, unless you're dealing with many small files (<100MB), in which case your cloud storage costs may explode at scale. (I ingest, process, and warehouse 20TB of data a day)
- mattbillenstein 11y ago
  Many small objects seems fine -- where small is 50-500MB per object. You can't have super huge compressed objects since BigQuery and perhaps other systems won't ingest a file with more than 1 or 2GB of decompressed data. 20TB a day is a lot -- what is your stack, and what compression/file format are you using?
  - ap22213 11y ago
    Well, it's only a few PB a year. But, I'll say that 20 TB is the higher bound per day. It's seasonal, and maybe the average is 10 TB per day. Personally, I've found the best results with bZip2. But, we use LZO and LZ4 and even Snappy for different things. Spark is money, but we've forked it to make several important customization / enhancements. We have proprietary indexing, and we do all of our processing in-memory, of course. Oh, and some of our data is sharded in Redshift. Redshift works well with GZip, so we do use it there. But, that particular part of our warehouse (or data lake, or whatever you call it these days) has much less data than we ingest since we reduce a lot.

devbug 11y ago

Sounds like you know your stuff! I couldn't find any email on your profile, do you mind sharing it?

chejazi 11y ago
Bottom of this page: http://vazor.com/unicode/ http://vazor.com/unicode/
mattbillenstein 11y ago
Sure, reverse moc.rozav@ttam

faizshah 11y ago

This is good advice but lz4 and it's chunked couterpart 4mc are much better suited for big data imo. This is the difference between 1 hour and 4+ hours for an archive of 300GB uncompressed on a single SSD based node.

mattbillenstein 11y ago
What is the size different between those? Issue is, I don't know that all the tools support lz4 like they support gzip.
- faizshah 11y ago
  In my experience the difference is ~10% (.3 ratio vs ~.23 ratio) but for the increase in processing speed it's well worth it. If the tool supports Hadoop's FileInputFormat or a stream of whatever underlying data (you can use the 4mc or lz4 command line tool) it will work.
  - mattbillenstein 11y ago
    Processing speed due to the decompression algorithm or the I/O? You can get another 10% with gzip -7 vs the default I bet. I don't find the compression/decompression the bottleneck in most of this work and even then an extra 10% isn't worth sacrificing the interoperability between all the systems that support .json.gz Higher gzip levels I think only really affect the compression step -- decompression is the same cost. These are mostly write-once read many times sorts of datasets.
    - faizshah 11y ago
      It's due to the decompression algorithm, gzip gets better file sizes and is a better general solution. lz4 is usually 10% worse but will get vastly better decompression speeds. The I/O of an SSD is usually 300+MBps, lz4 will usually be around 270MBps, gzip will usually be <80MBps. lz4 will put less load on your cpu allowing you to get more throughput from your processing tasks. But I agree most people don't reach these bottlenecks because most people use tools which are not optimized for performance like using jackson or jq instead of boon or rapidjson. Look here for a reproducible test I ran on real data: https://news.ycombinator.com/item?id=10721645 https://news.ycombinator.com/item?id=10721645
      - 11y ago

derefr 11y ago

As a complete tangent, "hourly-chunked newline-delimited .json.gz pushed to S3" is exactly the format I ended up using to hack around the free-tier database limits on Heroku. It's a nice format!

mattbillenstein 11y ago
Not tangential at all -- it's much nicer than working with csv and gzip strips out the redundancy that you'd save with a csv anyway.

bsg75 11y ago

> BigQuery has a nice interface and it's a better hosted service than Redshift IMO. Do you have some points where BQ is advantageous over Redshift?

mattbillenstein 11y ago
You don't have to think about running or scaling a cluster -- it's a service that you dump data into and supports a pretty decent set of SQL. Cost is probably cheaper as well, but I don't have apples to apples numbers to compare. But the main point being, if you get your data in the cloud as .json.gz, you can use them all fairly easily. Maybe one solution fits 90% of your needs, and another one the other 10%. That's what I'm finding at least.

ap22213 11y ago

Just a pro tip: you don't want to use GZip since it can't be split, and if you want to parallelize effectively you need to split... that is, unless you're dealing with many small files (<100MB), in which case your cloud storage costs may explode at scale. (I ingest, process, and warehouse 20TB of data a day)

mattbillenstein 11y ago
Many small objects seems fine -- where small is 50-500MB per object. You can't have super huge compressed objects since BigQuery and perhaps other systems won't ingest a file with more than 1 or 2GB of decompressed data. 20TB a day is a lot -- what is your stack, and what compression/file format are you using?
- ap22213 11y ago
  Well, it's only a few PB a year. But, I'll say that 20 TB is the higher bound per day. It's seasonal, and maybe the average is 10 TB per day. Personally, I've found the best results with bZip2. But, we use LZO and LZ4 and even Snappy for different things. Spark is money, but we've forked it to make several important customization / enhancements. We have proprietary indexing, and we do all of our processing in-memory, of course. Oh, and some of our data is sharded in Redshift. Redshift works well with GZip, so we do use it there. But, that particular part of our warehouse (or data lake, or whatever you call it these days) has much less data than we ingest since we reduce a lot.