Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Run SQL on JSON files without any data loads

citusdata.com

Post reply on HN
Print view View original

21–30 of 37 posts

Re: Run SQL on JSON files without any data loads

#21

Post by knowtheory »

seppo0010 wrote: post #7
knowtheory wrote: post #3

I am mystified as to when this tool would be useful (can anybody else think of a practical use case?). You can run SQL queries on individual JSON files... but you have to have PostgreSQL installed already. And you can't run it against multiple JSON files, only one, with a single JSON object at the root.

The only valid use case I can think of is for preloaded data for automatized tests.

Oh, that might be useful. Although, most test frameworks at this point have mechanisms for pretty easily load data, but it'd be interesting to figure out if any corners could be conveniently cut with this.

Re: Run SQL on JSON files without any data loads

#22

Post by cwsteinbach »

femami wrote: post #19
tantalor wrote: post #18

How 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?

Loading into database usually means transforming the data such that the data is stored as database's internal format. For example, using "LOAD DATA ..." in MySQL or "COPY ... FROM ..." in PostgreSQL. Here, you aren't storing the data in the database. You don't have to take any additional action to sync the file and db when you add rows to the file, so it's not loaded into the database.

Another benefit of this approach is that it allows you to save storage space by eliminating the need to create a copy of the JSON data in the DB's own internal format.

Re: Run SQL on JSON files without any data loads

#23

Post by femami »

rjurney wrote: post #6

Apache Pig and Hive are other systems that can do this, minus the one file limitation.

json_fdw to Citus DB or any other PostgreSQL based distributed database, is like json serde to Hive.

So, I think you should probably compare the json_fdw to json serde, and Citus DB to Hive.

Re: Run SQL on JSON files without any data loads

#24

Post by [deleted] »

neilk wrote: post #13
ozgune wrote: post #11

Earlier quoted context omitted.

(Ozgun at Citus Data) You're right that you need to have Postgres installed. For running SQL over multiple JSON files, we wanted to keep the blog post short and noted several different ways to go over multiple files in our GitHub Readme. 1. You can create a partitioned PostgreSQL table, and declare one child table per JSON file. You can also declare constraints on the child table to filter out irrelevant files to the…

Okay, so if I understand correctly, the benefit here is that I can run some kinds of SQL queries on a large collection of heterogenous JSON documents. Is that right? You're still speaking Postgres-internals-geek, not I-want-to-get-something-done geek. That is: - you can take heterogenous JSON documents, and then make them appear to the Postgres world as separate tables. (Which they have to be, because they're not uni…

[deleted]

Re: Run SQL on JSON files without any data loads

#26

Post by knowtheory »

ozgune wrote: post #11
knowtheory wrote: post #3

I am mystified as to when this tool would be useful (can anybody else think of a practical use case?). You can run SQL queries on individual JSON files... but you have to have PostgreSQL installed already. And you can't run it against multiple JSON files, only one, with a single JSON object at the root.

(Ozgun at Citus Data) You're right that you need to have Postgres installed. For running SQL over multiple JSON files, we wanted to keep the blog post short and noted several different ways to go over multiple files in our GitHub Readme. 1. You can create a partitioned PostgreSQL table, and declare one child table per JSON file. You can also declare constraints on the child table to filter out irrelevant files to the…

It should be said I think that it's super cool that you guys are putting Postgres to broader use. More stuff can and should be done with it, i'm just not sure what to do with this particular tool :)

Re: Run SQL on JSON files without any data loads

#27

Post by ozgune »

neilk wrote: post #13
ozgune wrote: post #11

Earlier quoted context omitted.

(Ozgun at Citus Data) You're right that you need to have Postgres installed. For running SQL over multiple JSON files, we wanted to keep the blog post short and noted several different ways to go over multiple files in our GitHub Readme. 1. You can create a partitioned PostgreSQL table, and declare one child table per JSON file. You can also declare constraints on the child table to filter out irrelevant files to the…

Okay, so if I understand correctly, the benefit here is that I can run some kinds of SQL queries on a large collection of heterogenous JSON documents. Is that right? You're still speaking Postgres-internals-geek, not I-want-to-get-something-done geek. That is: - you can take heterogenous JSON documents, and then make them appear to the Postgres world as separate tables. (Which they have to be, because they're not uni…

Thanks for the comment. Yes, that's exactly the benefit.

You could in fact have heterogeneous JSON documents within one file, and json_fdw could still handle them. If a field you declared in the table schema doesn't appear in a JSON document, json_fdw would just consider that field to be Null.

The issue is, json_fdw is built such that one table can only associated with one JSON file, and users typically have many JSON files lying around. For example, you'd have one JSON file for each hour's worth of website error logs.

If that's the case, you could create a distributed table. That way, you could run the query one distributed table and have the query run on all your JSON files instead of just one.

That said, it's probably best to associate one type of JSON logs (say your website error logs) with one distributed table. That way, your queries go over your website error logs or your mobile application logs, but not both at the same time.

Re: Run SQL on JSON files without any data loads

#28

Post by mtdewcmu »

ot wrote: post #14

A 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…

I've enjoyed playing with succinct data structures because they make interesting puzzles, and I've come across your papers. Succinct data structures seem to be a drain on performance, though, for ordinary kinds of datasets. It's not easy to find the kind of datasets that are so large that succinctness would allow them to fit in memory when they otherwise wouldn't (not working in computational biology, etc). Using regular data structures would probably increase performance more and meet most people's needs, and it would certainly reduce the code complexity.

Re: Run SQL on JSON files without any data loads

#29

Post by mmastrac »

ot wrote: post #14

A 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

Re: Run SQL on JSON files without any data loads

#30

Post by masklinn »

danielrhodes wrote: post #17
knowtheory wrote: post #3

I am mystified as to when this tool would be useful (can anybody else think of a practical use case?). You can run SQL queries on individual JSON files... but you have to have PostgreSQL installed already. And you can't run it against multiple JSON files, only one, with a single JSON object at the root.

It's a good way to do adhoc querying of JSON log files, which among other things, can be useful for debugging.

Can also be useful to mix internal SQL data from JSON from an external source without having to manually sync/import it no?
Post reply on HN