4 ms·
One problem I've noticed is that there are no good "medium data" tools. Column stores are crazy fast, but there isn't much simple tooling built around things l
by justinsaccount 9y ago
One problem I've noticed is that there are no good "medium data" tools.
Column stores are crazy fast, but there isn't much simple tooling built around things like parquet or ORC files. It's all gigantic java projects. Having some tools like grep,cut,sort,uniq,jq etc that worked against parquet files would go a long way to bridge the gap.
Something like pyspark may be the answer, I think it may be possible to wrap it and build the tools that I want.. like
find logs/ | xargs -P 16 json2parquet --out parquet_logs/
parquet-sql-query parquet_logs/ 'select src,count(*) from conn group by src...'
I've been testing https://clickhouse.yandex/ 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.
Edit: another example... I have a few months of ssh honeypot logs in a compressed json log file. Reporting on top user/password combos by unique source address took tens of minutes with a jq pipeline. The same thing imported into clickhouse took a few seconds to run something like
select user,password,uniq(src) as sources from ssh group by user,password order by sources desc limit 100
- rb808 9y agoLike 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.
- metaphorm 9y agothis 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.
- justinsaccount 9y agoExactly.. 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.)
- ma2rten 9y agoI 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.
- justinsaccount 9y agoAs 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?
- jdc0589 9y ago> 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)
- justinsaccount 9y agoHow? 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?
- jdc0589 9y agoyou didn't specify anything about RAM/resources...
- justinsaccount 9y agoI did above: > I've been testing https://clickhouse.yandex/ 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.
- chupasaurus 9y ago
- kmax12 9y agoFor "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/ http://dask.pydata.org/en/latest/ [1] http://dask.pydata.org/en/latest/spark.html http://dask.pydata.org/en/latest/spark.html
- wakkaflokka 9y agoI'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.
- kakoni 9y agoFor medium data (batch) pipelines I've been utilising embulk[0] from treasure data. Works beautifully. [0] http://www.embulk.org http://www.embulk.org
- jaz46 9y agoPachyderm (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.
- mrweasel 9y agoFor 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.
- justinsaccount 9y agoAny sql database is a bit vague. sqlite is a sql database.
- astine 9y agoPostgresql, 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.
- justinsaccount 9y agoYeah.. 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.
- swimorsinka 9y agoIronically 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-of-mapreduce/ https://thinkfaster.co/2017/05/big-data-with-golang-instead-...
- justinsaccount 9y ago> 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 http://tech.marksblogg.com/benchmarks.html a 6-node ds2.8xlarge redshift cluster is about as fast as clickhouse on a single i5.
- deleted 9y ago[deleted]
- barrkel 9y agoThis 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.
- gizzlon 9y agoYou 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)