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

11–20 of 37 posts

Re: Run SQL on JSON files without any data loads

#11

Post by ozgune »

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 query.

2. You can create a distributed foreign table on CitusDB, even if you'd like to run on one node. In this case, we'll collect statistics automatically for you, and filter out irrelevant files.

3. If neither of these fit, you could change the source code to scan over one directory instead of a file. We didn't go down this path to be compatible with file_fdw's semantics.

Re: Run SQL on JSON files without any data loads

#12

Post by caycep »

actually, I might use this thread to ask a noob question - I've been trying to fiddle around with some data that is relatively schemaless (i.e. I have a list of medical research subjects, and each of them have varying numbers of prior medical conditions, medication allergies, and list of current and past medicines).

Naturally, I thought JSON was better than relational DB's for this, but I wasn't sure how best to store this data. The relation data that I do have is stashed in .csv files that I'm transitioning over to sqlite, so I was wondering if there was a sqlite-esque DB for JSON - I was assuming something like Couch or Redis. But am I missing something, and that JSON itself IS the database?

Re: Run SQL on JSON files without any data loads

#13

Post by neilk »

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…

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 uniform)

- you can then take some kinds of SQL queries and distribute them among these tables, as if they were one big SQL table. If some pseudo-tables have missing or extra columns, no problem.

Re: Run SQL on JSON files without any data loads

#14

Post by ot »

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 useful to integrate the semi-index code [2] in this json_fdw; thanks for sharing the code, I'll try to see how feasible this is.

[1] http://www.di.unipi.it/~ottavian/files/semi_index_cikm.pdf

[2] https://github.com/ot/semi_index

Re: Run SQL on JSON files without any data loads

#15

Post by tracker1 »

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…

Just an idea... I had a script doing record dumps at a prior job... (there were about 30 joins for getting a single record (denormalized) into json), so having each one in a separate .json.gz file was a useful backup solution... it was about zero cost when doing mirroring to another system for query/display purposes.

In the file system, to prevent too many records in a single directory it was split up per 1000 records... base/00001000/(1000-1999).json.gz ... this was mainly for being able to navigate this structure via a gui. I would suggest if your system can't do "basepath//*.json.gz" that you consider it.

Re: Run SQL on JSON files without any data loads

#16

Post by malkia »

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 could be. In game development for example, often a game asset is nothing more than a bunch of key/values, and they are slapped either into individual file per asset, or grouping several such asset into one file. The text format is not important (now), but I've seen "ini" style ones, or using lua, xml, or nowadays json.

It's important for these files to be kept this way, because they can be submitted to a SCM (perforce, svn, etc.), and this is the typical workflow. An SQL/noSQL db won't cut it here, since there is no revision number, or even if there is, it introduces a problem of syncing what's in the DB vs. what's in SCM.

So such tool would become interresting, if after syncing your latest data of the depot (repo), you run a tool that imports them into the DB to be used by other tools. And when you are done, export is done to the same files, and submitted.

Some automation could be done as well.

And having such tools would be beneficial, since you now don't have to write another tool to be ran manually, or being resident as service to get the latest file changes and update the DB, but could be make as an PostgreSQL worker that imports on the fly changes and updates the DB at the same time.

E.g. - if you use Perforce: - Someone syncs - The DB automatically picks up the changes, informed by the file system, and updates the DB.

The more of such things the better.

Re: Run SQL on JSON files without any data loads

#17

Post by danielrhodes »

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.

Re: Run SQL on JSON files without any data loads

#19

Post by femami »

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.

Re: Run SQL on JSON files without any data loads

#20

Post by femami »

caycep wrote: post #12

actually, I might use this thread to ask a noob question - I've been trying to fiddle around with some data that is relatively schemaless (i.e. I have a list of medical research subjects, and each of them have varying numbers of prior medical conditions, medication allergies, and list of current and past medicines). Naturally, I thought JSON was better than relational DB's for this, but I wasn't sure how best to stor…

I think if your data is append-only, this should work good enough. You implement your data appending logic, and you use json_fdw for querying your data. But this requires starting PostgreSQL service, I'm not sure if this is acceptable in your application or not.
Post reply on HN