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?