4
$\begingroup$

In short, what would be the best method/tricks/techniques/tools for performing ad hoc sql (style) queries on 350TB of csv data? Would there be other options, tool wise that would do it faster if we dropped the "sql" requirement?

Is my best option Hive and as many servers I can't get my hands on? Would spark be of any benefit since this size of dataset wouldn't come close to fitting into memory? I have other related questions /ideas but don't want to bog this down.

Background

The data is originally stored in a binary format that gets converted into an ASCII. The ASCII file is getting turned into CSV (actually tab separated but whatever). 1 binary file = 1 ASCII file = 1 CSV file. The data format is pretty simple, 200 to 500 header columns, each row is a sample of an attribute in 1-second intervals.

Example (first row is header):
t,attribute1,attribute2...<attributeX>
1,val1,val2...<valx>
2,val1,val2...<valx>
3,val1,val2...<valx>
...
X,val1,val2...<valx>

Example queries that could be ran.

1) Take t value (t is non-unique, as each file starts at 1 and ends at some random time) and compare different attribute values between t value of 100-1000 for all other attributes that were sampled at 100-1000.

2)There is UTC value as an attribute value (one of the columns), and same idea as #1 we might want to compare all attributes between X dates and compare those attributes to Y data that is from a different UTC range.

3)We might want to figure out a single particular attribute and trend that from the earliest time we saw that attribute for a different particular attribute.

These are my best examples as provided to me but other types of queries could exist. If you do have a question of why don't we cut out the middle man of the conversions of the binary data to CSV data, we could theoretically. However, the program that converts the files is quite old and is very involved.

Improve this question
$\endgroup$
4
  • $\begingroup$ Welcome to the site :) What do you mean by performing ad hoc sql (style) queries on csv data? Unless and until your data is in a relational data store, you can't do SQL style queries(obviously) $\endgroup$
    – Dawny33
    Jan 7, 2016 at 6:29
  • $\begingroup$ So for example we want to be able to try and do some discovery queries using a SQL like language (aggregation with sum, avg, group by etc). Hive, Impala, Drill, Presto, Sparksql all are SQL on Hadoop type tools but don't implement the full SQL standard. Fro example putting this data into a Hive table I would consider it being in a "relational store". I've got a feeling that doing this type of work is going to be difficult anyways even for any of those tools. So any other tips or suggestions on doing discover on this data in a timely manner would be awesome. $\endgroup$
    – Kevin Vasko
    Jan 7, 2016 at 14:28
  • $\begingroup$ I think you should edit your question to expand greatly on the shape of your data (rows/columns/types/structures) and the sort of queries you want to make, without reference to any tool that you may think is the one for the job. We have to know if you have a nail or a screw before recommending a hammer or a screwdriver. $\endgroup$
    – Spacedman
    Jan 8, 2016 at 8:21
  • $\begingroup$ I can add a lot of detail but comment section probably isn't long enough to give all details so I'll edit my post. Thanks. $\endgroup$
    – Kevin Vasko
    Jan 9, 2016 at 22:52

2 Answers 2

Reset to default
1
$\begingroup$

Talking about SQL world: from your requirements point of view parallel column based DB Vertica seems to fit well. It is not free of charge though. Even more powerful solution if you have good budget is Teradata.

You'd need to have at least 70 spindles to store your data on HDDs (5TB) on site +/- compression and redundancy. May be add more RAM or SDD if you can cache. That could fit in 3-10 boxes, depending on the processing power you require.

The rest is a software question on a trade-off between data management, feature set, optimization, your own time spent and the price. Teradata and Vertica will surely reduce your time spent writing queries and optimizing them, managing data. In case you'd need joins that advantage will be much bigger yet.

Improve this answer
$\endgroup$
2
$\begingroup$

There are many tools that are able to support such queries (as you mentioned Hive or Spark), and it is really up to your requirements in terms of number of queries, number of people who are going to query the data, what kind of BI or reporting tools you might want to use with it, etc.

More than that, your requirements are probably going to change in the future. If you provide easier ways to query the data, the more people around you are going to use it.

For example, if you choose to load that data into Amazon Redshift (https://aws.amazon.com/redshift/), it will give you a full SQL flexibility, very fast performance and the ability to connect to several BI, visualization and reporting tools. When you COPY the data into Redshift it is being compressed (column based encoding) 5-8 times, and you can put all your data in a cluster of 6 nodes of DS2.8XL of Redshift, for a couple of weeks for your POC.

The concept of compression can be applied in different solutions as well. For example, instead of using CSV format, you can convert it to Parquet (https://parquet.apache.org/) format, and save on your storage and increase the performance as you need to move around and scan less bytes of data. Once you have your data in Parquet, you can query it for example with Presto, like Netflix are doing - http://techblog.netflix.com/2014/10/using-presto-in-our-big-data-platform.html

Regarding Spark, it can help even if you don't fit all the data into memory. Spark can also help you with the usage of Zeppelin (https://zeppelin.incubator.apache.org/) as an interactive notebook, with nice visualization features, as well as write code with Scala, Python or even R with SparkR.

Improve this answer
$\endgroup$
2
  • $\begingroup$ Thanks for the information. Would it ever be feasible to run a single query at atime against this whole dataset in < 5 minutes per query with Hive or SparkSQL with 6 machines with similar specs of the DS2.8XL machines you quoted? Currently using the optimizations found here and querying only on ~450GB of this CSV data I'm getting 30-60 second response times. $\endgroup$
    – Kevin Vasko
    Jan 10, 2016 at 7:42
  • $\begingroup$ Welcome to the site @Guy :) $\endgroup$
    – Dawny33
    Jan 10, 2016 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.