I would like to access JSON or JSONB columns in a PostgreSQL database with Drill.
I found a mailing list discussion, where Andrew Evans asked to do a similar thing but it seems that no actual solution was found at that time. [1] I found out through testing that the syntax to do that in PostgreSQL (column->'key') is not supported by Drill. What I do not want to do because of performance reasons, is store the same data in a VARCHAR column in postgres and perform the selection with Drill (CONVERT_FROMJSON(column)['key']). This works, but does not take advantage of the optimizations that postgres does. Question 1: How can I use the postgres JSON-selection capabilities through Drill? I found another mailing list request that got no answers yet, where Reid Thompson noticed that the JSON column type is unsupported by the JDBC storage plugin. [2] Unlike Reid, I noticed while trying to figure it out, that Drill thinks the sqlTypeOf(column) is INTEGER. I know that JSON(B) are not standard sql data types, so I guess the ability to read JSON from that column gets lost in translation. Question 2: Would I have to write a new storage plugin to achieve what I need? Another idea I could think of, but also did not find any resources to, is to have Drill pass some portion of the query directly through to postgres. Question 3: Where can I find documentation on having Drill pass queries off to postgres directly, if it is at all possible? Thanks, Niels Schieber [1] https://drill-user.incubator.apache.narkive.com/O5KNkFew/apache-drill-query-postgresql-text-or-jsonb-as-if-it-were-from-a-json-storage-type [2] http://mail-archives.apache.org/mod_mbox/drill-user/201808.mbox/%[email protected]%3e
