Long JSON field in Parquet input file

Hi Eugenia,

happy new year 2019 for you!
Thank you helping me to research this topic!

Your statements 1 and 2 are correct.

As the JSON column is longer than 65.000 characters the SQL function MAPJSONEXTRACTOR will not work.

In my view the core problem is that COPY specifies the parser on a file level. But in this case we need the functions of two different parsers (a nesting of parsers)
1. the Parquet parser to consume the whole source file
2. the JSON parser to interpret the JSON column

We need a functional replacement for step 2 as MAPJSONEXTRACTOR will not work with this long column.

It would be great if we can get the customer a solution for this problem.

Thank you
Dieter

Maybe the Bracket Operators can help? They do not seem to be limited to 65,000 characters.

Example:

dbadmin=> \! ls /home/dbadmin/parq
329fefd4-v_test_db_node0001-140699538269952-0.parquet

dbadmin=> CREATE TABLE big (c INT, json LONG VARCHAR);
CREATE TABLE

dbadmin=> COPY big FROM '/home/dbadmin/parq/*.parq*' PARQUET;
 Rows Loaded
-------------
           1
(1 row)

dbadmin=> SELECT length(json) FROM big;
 length
--------
  65009
(1 row)

dbadmin=> \! vsql -Atc "SELECT json FROM big;" -o /home/dbadmin/big.out

dbadmin=> CREATE FLEX TABLE big_flex();
CREATE TABLE

dbadmin=> COPY big_flex FROM '/home/dbadmin/big.out' PARSER fjsonparser(flatten_maps=false);
 Rows Loaded
-------------
           1
(1 row)

This will error:

dbadmin=> SELECT MAPLOOKUP(MapJSONExtractor(__raw__), 'c') FROM big_flex;
ERROR 3457:  Function MapJSONExtractor(long varbinary) does not exist, or permission is denied for MapJSONExtractor(long varbinary)
HINT:  No function matches the given name and argument types. You may need to add explicit type casts

But this works:

dbadmin=> SELECT length(__raw__['c']) FROM big_flex;
 length
--------
  65001
(1 row)

See:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/FlexTables/QueryNestedData.htm

Hi Jim,

thank you for sharing this approach

• load the complete Parquet file into a Vertica table
• export the JSON column
• load the JSON column into a flex table and get the JSON structure interpreted

So we are ending up with two result tables, the JSON piece beeing a flex table.

Can we think of a way to do both tasks in a single step?
Load the Parquet file in a way that the JSON part gets interpreted into several columns of the single target table?

Thank you
Dieter

How about like this?

Setup to get a Parquet file having a column called JSON where the JSON > 65K characters

dbadmin=> \d big
                                          List of Fields by Tables
 Schema | Table | Column |         Type          |  Size   | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-----------------------+---------+---------+----------+-------------+-------------
 public | big   | c      | int                   |       8 |         | f        | f           |
 public | big   | json   | long varchar(1048576) | 1048576 |         | f        | f           |
(2 rows)

dbadmin=> SELECT length(json) FROM big;
 length
--------
  65010
(1 row)

dbadmin=> SELECT substr(json, 1, 26) || ' ... ' || substr(json, length(json)-25, 25) "Simple very long JSON record" FROM big;
              Simple very long JSON record
---------------------------------------------------------
 {"c": "6EgQ2jkXevWhOe62sY ... i4F3Ez67tCFLT4LazXppo4Yi}"
(1 row)

dbadmin=> EXPORT TO PARQUET (directory='/home/dbadmin/parq') AS SELECT * FROM big;
 Rows Exported
---------------
             1
(1 row)

Now that I have the Parquet file, I can load it into a table and create a VMAP column to read the larger JSON data via the bracket operators.

dbadmin=> CREATE TABLE big2 (c INT, json LONG VARCHAR, vmap LONG VARBINARY);
CREATE TABLE

dbadmin=> COPY big2 (c, json, vmap AS MapJSONExtractor(json using parameters flatten_maps=false)) FROM '/home/dbadmin/parq/*' parquet;
 Rows Loaded
-------------
           1
(1 row)

dbadmin=> SELECT length(vmap['c']) FROM big2;
 length
--------
  65001
(1 row)

dbadmin=>  SELECT substr(vmap['c'], 1, 25) || ' ... ' || substr(vmap['c'], length(json)-25, 26) "Simple very long JSON record" FROM big2;
          Simple very long JSON record
-------------------------------------------------
 6EgQ2jkXevWhOe62sYrBOTM9V ... 7tCFLT4LazXppo4Yi
(1 row)

The bracket operators are useful for querying nested data, even in Parquet files.

Example:

dbadmin=> CREATE TABLE hier (json VARCHAR(200));
CREATE TABLE

dbadmin=> INSERT INTO hier SELECT '{ "hello": "hello","colors": {"red": "The color is red", "blue": "The color is blue", "green": "The color is green"} }';
 OUTPUT
--------
      1
(1 row)

dbadmin=> \! rm -fr /home/dbadmin/parq/

dbadmin=> EXPORT TO PARQUET (directory='/home/dbadmin/parq') AS SELECT * FROM hier;
 Rows Exported
---------------
             1
(1 row)

dbadmin=> CREATE EXTERNAL TABLE hier_ext (json LONG VARCHAR, vmap LONG VARBINARY) AS COPY (json, vmap AS MapJSONExtractor(json using parameters flatten_maps=false)) FROM '/home/dbadmin/parq/*' parquet;
CREATE TABLE

dbadmin=> SELECT vmap['colors']['red'] FROM hier_ext;
       vmap
------------------
 The color is red
(1 row)

dbadmin=> SELECT vmap['colors']['green'] FROM hier_ext;
        vmap
--------------------
 The color is green
(1 row)

Hi Jim,
thank you for your suggestions, especially your Jan 9 post.

I am not sure if I get the point:
The usage of MapJSONExtractor will impose the 65.000 char limit on us again.

Also we do store the JSON as a single column. To interpret the JSON we need to be aware of the structure.

My impression at this time this that creating two target tables cannot be avioded.

Best
Dieter

Hi,

I was showing the last example (on Jan. 10) so that you can see there is only one table created here, not two:

CREATE EXTERNAL TABLE hier_ext (json LONG VARCHAR, vmap LONG VARBINARY) AS COPY (json, vmap AS MapJSONExtractor(json using parameters flatten_maps=false)) FROM '/home/dbadmin/parq/*' parquet;

Oddly, it appears that when the MapJSONExtractor parser is used like in the above CREATE .... TABLE statement, there is no 65K limit. I say that because I was able to load 65001K characters into the vmap (See example from Jan. 9):

dbadmin=> SELECT length(vmap['c']) FROM big2;
 length
--------
  65001
(1 row)

Note that I just created a JSON file that had single record with 2 columns, one an INT and one LONG VARCHAR with 65,001 random characters.

Here, after adding more random characters, I can still load the data:

dbadmin=> CREATE EXTERNAL TABLE big_ext (c INT, json LONG VARCHAR, vmap LONG VARBINARY) AS COPY (c, json, vmap AS MapJSONExtractor(json using parameters flatten_maps=false)) FROM '/home/dbadmin/parq/*' parquet;
CREATE TABLE

dbadmin=> SELECT length(vmap) FROM big_ext;
 length
--------
  65213
(1 row)

dbadmin=> SELECT substr(vmap['c'], 1, 25) || ' .. ' ||  substr(vmap['c'], 65000, 100) FROM big_ext;
                                                             ?column?
-----------------------------------------------------------------------------------------------------------------------------------
 6EgQ2jkXevWhOe62sYrBOTM9V .. YXXXzB09yq6Xzh7yLFGaaMKAJ571uFaXOTrLgPa9ay7C3P8zh503LPgbQZrRMXSAFL7rshgj3c8KSfu0thD4WcI550tdKOV9Iz9Y
(1 row)

See, I read 100 characters past 65,000.

And to your point, yeah, you will need to know the JSON structure to use bracket operators. In my case, the column name is "c".

Actually, it's easy to get the structure of the vmap column (which contains the JSON data):

dbadmin=> SELECT * FROM (SELECT MAPKEYSINFO(vmap) OVER(PARTITION BEST) FROM big_ext) AS a;
 keys | length | type_oid | row_num | field_num
------+--------+----------+---------+-----------
 c    |  65188 |      116 |       1 |         0
(1 row)