1
$\begingroup$

I've got about 5 million JSON files, about 50GB in total. They do not have a consistent schema (they're broadly the same format, but some have extra extension fields, some have missing fields, etc - the schema is quite complexly nested).

I would like to run SQL-like queries across these files - e.g. finding the count of files with a certain property, finding the count of files where property is in a numeric or time range, etc.

I have the files locally, and in an S3 bucket. I would ideally like to store the data in the cloud, so that others can run queries against them without having to have the files locally.

What are my options? Here are some solutions I've evaluated so far:

  • Apache Drill.
    • Pros: Great for SQL-like queries and 'on-discovery' schemas.
    • Cons: Ruled out because it struggles with a dataset this size both locally (runs out of memory) and hooked up to S3 (simply doesn't return results, I think because it has to get a local copy of the data).
  • Google BigQuery.
    • Pros: Great for SQL-like queries, stores in the cloud, large datasets, plus the friendly user interface is a bonus. JSON can be loaded from command line.
    • Cons: I'd need to define a schema, and remove or fill any data that doesn't conform to it. This is a hassle, and a problem because it means I can't query some data that I would like to (e.g. extension fields).
  • Amazon Athena.
    • Pros: Cloud support (runs against S3), and (I think) has SQL-like queries.
    • Cons: Seems to require a defined schema (one you define at runtime, rather than in advance)? Also seems to need the files in the query to match the schema, which wouldn't work for my data. However, I'm not sure this is correct, because the documentation is quite confusing.
  • Google Cloud Datastore.
    • Pros: SQL-like queries with GQL, stores in the cloud. NoSQL, so schemaless.
    • Cons: Seems to require pre-defined indexes in order to run queries (?), limited query support (e.g. no aggregation or joins). No documentation on how to load JSON files into it (possibly needs Cloud Dataflow setup).

I'm leaning towards BigQuery, mainly because it's much easier to get up and running than any other solution. However, the requirement for a predefined schema is a major hassle.

Are there any solutions I've missed? Have I misunderstood Athena's requirements about schemas?

Improve this question
$\endgroup$
2
  • $\begingroup$ Impala too, but, I think this is probably more on-topic in other SEs $\endgroup$
    – Sean Owen
    Commented Jul 5, 2017 at 17:35
  • $\begingroup$ If you're in the Google cloud, you will want BQ over DS for the pricing. DS is more for accessing individual rows. Drill is for people who run their own infrastructure. If you already have stuff on AWS, you should evaluate their options first; e.g., Redshift. $\endgroup$
    – Emre
    Commented Jul 5, 2017 at 17:43

1 Answer 1

Reset to default
1
$\begingroup$

You want to write structured queries (have your cake) but have unstructured files (eat it too).

Is there any way you can play with the files a bit and have a common schema between them, with some fields just empty? That would make it easier.

That being said, BigQuery is probably your least bad solution as you found. Amazon's new Redshift Spectrum is kind of similar but will also require a schema to be defined.

Improve this answer
$\endgroup$

Your Answer

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

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