Hacker News Reader

For "medium data", my company has found a lot of success using dask [0], which mimics the pandas API, but can scale across multiple cores or machine.

The community around dask is quite active and there's solid documentation to help learn the library. I cannot recommend dask enough for medium data projects for people who want to use python.

They have a great run down of dask vs pyspark [1] to help you understand why'd you use it.

[0] http://dask.pydata.org/en/latest/

[1] http://dask.pydata.org/en/latest/spark.html

I've been trying to change all of my Luigi pipeline tasks from using Pandas to Dask, so that I can push a lot more data through. Seems like an easy process so far, and I like the easy implementation of parallel computing.

Pachyderm (github.com/pachyderm/pachyderm) is a great "medium data" tool. I'm biased of course because I'm one of the creators, but we build in data pipelining, data versioning, and you can use whatever languages or libraries you want because we containerize the whole workload.

You example above would actually work perfectly. You can literally use grep in a distributed fashion in our system. One of our example pipelines is using grep and awk to do log filtering/aggregation or word count.

This is why you'd use something like Impala; SQL is your cut, sort, uniq etc. That's why they don't exist; put the Parquet file into HDFS, turn on shortcut access, and use Impala to query it. I see throughput of about 250M records/sec on a 12-CPU box [1]. If your data doesn't fit in memory (FS cache), I/O will probably be your bottleneck.

[1] Technically, 6 2-core guests on a single hypervisor; using guests to test deployment and scaling more easily.

this is a really good point. there's a really uncomfortable space where your production database is just a wee bit bigger than you can comfortably work on with a single machine, and there is a case to be made for using some level of parallelism, but you don't yet need to go full monty on it with a compute cluster and a distributed datastore.

so you start looking at stuff like sharding or vertical scaling and you keep on doing things more or less the way you have been but with steadily degrading performance on every new insert.

Exactly.. I used to say that I didn't have "big data", but I have "annoying data".

clickhouse turns my annoying data back into something that I can query in 30 seconds.

I just ran a random query to find what day had the most connections:

  select day,count() as c from conn group by day order by c desc limit 1

And that took all of:

  1 rows in set. Elapsed: 16.412 sec. Processed 1.43 billion rows, 2.87 GB (87.33 million rows/s., 174.65 MB/s.)

Like the OP said, check out Pandas which is ideal for this. Take a look at Spark. Also if you're using parquet get familiar with Apache Arrow.

Learn python generators and iterators by @dabeaz [1] http://www.dabeaz.com/generators/

Python generators and iterators are a terrible choice for processing billions of records. Python's iteration is notoriously slow.

Ironically I just did this using Go and a 32 core machine processing ~50 compressed gigs per day [1]

[1] https://thinkfaster.co/2017/05/big-data-with-golang-instead-...

> Today, we’re very happy with Redshift, and we have 5 billion rows stored in our database.

1.4 billion of our connection logs (24 fields) takes up 89G on my clickhouse VM. 5 billion records would take ~320G.

Based on http://tech.marksblogg.com/benchmarks.html a 6-node ds2.8xlarge redshift cluster is about as fast as clickhouse on a single i5.

You can get a cloud server with (at least) 416GB and ram. Spin it up, massage the data in memory with a custom program or script and shut it down.

Edit: Oh, or you could use something like BigQuery which should be significantly easier than Hadoop (Never had the need myself though)

For medium size datasets I would argue that you could just dump it into any SQL database, assuming that the data allows it self to be stored and access in a reasonably predictable fashion.

Any sql database is a bit vague.

sqlite is a sql database.

Postgresql, SQL Server, Oracle, or MySQL, properly configured can handle larger loads than some people assume. Even SQLite is pretty performant; you just need to write some kind of front end to make it usable as a service.

Of course, it depends on the precise nature of your workload.

Yeah.. sqlite is crazy fast but I've found performance falls off a cliff once the DB is larger than ram. a 4G database on a VM with 8G of ram runs great.

I used ElasticSearch for this end in the past. Since everything is indexed in elastic you can run arbitrary queries in mili-seconds as long as you don't need joins.

As said in another comment, I just ran this:

  select day,count() as c from conn group by day order by c desc limit 1

and that resulted in

  1 rows in set. Elapsed: 16.412 sec. Processed 1.43 billion rows, 2.87 GB (87.33 million rows/s., 174.65 MB/s.)

Can ElasticSearch do that?

> Can ElasticSearch do that?

yes. the required infrastructure will look different though, and obviously elasticsearch doesn't speak sql (not without some crazy 3rd party plugin anyway)

How? How does one report on 1.43 billion events stored in ES in 16 seconds?

And can it do this on a vm with 4G of ram?

you didn't specify anything about RAM/resources...

I did above:

> I've been testing https://clickhouse.yandex/. I threw it on a single VM with 4G of ram and imported billions of flow records into it. queries rip through data at tens of millions of records a second.

Did unrelevant and crazy testing on 4GB DigitalOcean VM with a part of a latest hosts dataset from Rapid7's Project Sonar. Data is pairs of ip,certificate thumbprint. On ~1500000 of entries in ES (~300mb with indices, much wow) a sort for ip occurancies is made on the speed of light within 3.5 seconds if data isn't cached and 300-350 if it is.

1.5 million entries isn't exactly equal to 1.5 billion. Extrapolating the query would take almost an hour on ES.

Thing is that the ES index which could fit in memory still performs slowly for full db query.

For medium data (batch) pipelines I've been utilising embulk[0] from treasure data. Works beautifully.

[0] http://www.embulk.org