You can also use Ojota (a flat file database with ORM - https://bitbucket.org/msa_team/ojota/overview) for something like this.
Run SQL on JSON files without any data loads
31–37 of 37 posts
Re: Run SQL on JSON files without any data loads
#32I don't make a habit of spamming discussion threads but... my product QueryTree ( http://querytreeapp.com ) will load JSON files even if they have lists at the root, you can use the Append tool to bring multiple files together, and you can then use Filter, Sort, Join and Group without ever having to define a schema. Admittedly, it's aimed at novice users so if you're comfortable with SQL it may feel limiting.
It's not spamming if it's relevant.
Re: Run SQL on JSON files without any data loads
#33You can use YQL to do this — even join with other data sources.
Re: Run SQL on JSON files without any data loads
#34How is this "without any data loads"? You have to map each file to a database table and then execute SQL against that database. Isn't that exactly what "loading into a database" means?
> You have to map each file to a database table and then execute SQL against that database.
The create statement is just a declaration, and doesn't load data into a postgres table. Instead, it just tells postgres how to access the file. So if I then do a query that is limited to the first few rows, it will only ever need to read the first few "rows" of the file.
Re: Run SQL on JSON files without any data loads
#35Commenting to save thread -- looks great!
Re: Run SQL on JSON files without any data loads
#36A few years ago I was toying with JSON databases without loading, and came up with an idea to cache the parse tree of the documents after the first query, so that subsequent queries would run much faster. I called the technique semi-indexing and wrote a paper [1] on that; on my synthetic tests the speedups were significant (even 10x), but I never got the chance to test it on real workloads. I wonder if would be usefu…
This paper is fascinating. I have an extremely fast JSON parser for Java that I've been working on, on and off [1], and I'd love to optionally augment it with semi-indexes for cases where end-users are using a subset of a file. [1] https://github.com/mmastrac/nanojson
Very interesting! Implementing a semi-index is very easy if you have implementations of Elias-Fano and balanced parentheses data structures.
For Java I recommend Sux4j [1] that has a very good implementation of Elias-Fano, but I think that balanced parentheses are very primitive. I was told by the author that a better data structure, based on Range-Min-Max trees, should be added soon.
Re: Run SQL on JSON files without any data loads
#37With the upcoming release of Pg 9.3, I've been reading a lot more posts and articles about the FDW (foreign data wrapper) capabilities. I had not considered all the things FDW can do, but I'm getting more and more excited the further I get into it.
For instance, at $work we have a bunch of tables which are archives of raw click data. They are taking up quite a bit of space on disk, which we'd like to reclaim. Keeping the raw data around is good, since I want to create a summary table and you can't recreate raw data from summaries. The idea was to export the table data as CSV, and then when I had the time to for messing around with summaries I'd reload the data. With FDW I can leave the data as CSV and read it just like it was in a proper DB table. Win!
Or even better, again at $work we use Mysql. But I hate it because SQL strict mode isn't enabled and mysql does really stupid things with my data in "normal" mode. I can't safely turn on strict at this point because I don't have the time for testing everything. I also really like Pg and would love to switch. But again, I don't have the time to do it. What I've been thinking about is using the FDW capabilities (in particular the upcoming writable FDW in 9.3) as a way to safely and slowly migrate DB platforms. It's only an idea in my head right now, but it's an intriguing one.