JSON column in Regular table (not flex)

yardenbar · May 2016

Hi,

I guess it is reasonable to say that most of us have some kind of an ETL process from various sources streaming data into Vertica.

My current focus is on a migration of one of our source's (Postgres) events table to change data column to be of JSONB type (binary representation of JSON).

Vertica does offer a FLEX table type but using it would force me to modify the ETL process to extract data as JSON lines where all I want is to migrate events table data column in Vertica to be of JSON type or leave it as string but find another way to query this data.

Currently events.data in Vertica is a VARCHAR type column and the content is a comma seperated Hash-Rockets pairs:

"key1"=>"value1","key2"=>"value2",....,"keyN"=>"valueN"

I've read Vertica's data type documentaion, but couldn't find anything that can support JSON or even a map.

Is there anyone who have done this kind of migration?

Any idea is welcome,

Thank you,

Yarden

Sharon_Cutter · May 2016

See the MAPJSONEXTRACTOR documentation:

https://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/FlexTables/MAPJSONEXTRACTOR.htm?Highlight=mapjsonextractor

Specifically the coljson example where json is loaded into a map.

yardenbar · May 2016

Hello Sharon,

Thank you for the reference.

I'll test it tomorrow and share my findings.

Yarden

[Deleted User] · May 2016

This is an example how you can you load JSON data into table (not flex) dbadmin=> \!vi /tmp/sample.json dbadmin=> \!cat /tmp/sample.json {"name": "Everest", "type":"mountain", "height":29029, "hike_safety": 34.1} {"name": "Mt St Helens", "type":"volcano", "height":29029, "hike_safety": 15.4} {"name": "Denali", "type":"mountain", "height":17000, "hike_safety": 12.2} {"name": "Kilimanjaro", "type":"mountain", "height":14000 } {"name": "Mt Washington", "type":"mountain", "hike_safety": 50.6} dbadmin=> dbadmin=> create table mountains( name varchar(26), type varchar(20), height int, hike_safety numeric(10,2)); CREATE TABLE dbadmin=> copy mountains from '/tmp/sample.json' parser fjsonparser(); Rows Loaded ------------- 5 (1 row) dbadmin=> select * from mountains; name | type | height | hike_safety ---------------+----------+--------+------------- Mt St Helens | volcano | 29029 | 15.40 Denali | mountain | 17000 | 12.20 Everest | mountain | 29029 | 34.10 Kilimanjaro | mountain | 14000 | Mt Washington | mountain | | 50.60 (5 rows)

[Deleted User] · May 2016

Sorry the format was lost. My browser did not have font's options. Trying again, Hope this helps,

Eugenia

dbadmin=> \!vi /tmp/sample.json

dbadmin=> \!cat /tmp/sample.json
{"name": "Everest", "type":"mountain", "height":29029, "hike_safety": 34.1}
{"name": "Mt St Helens", "type":"volcano", "height":29029, "hike_safety": 15.4}
{"name": "Denali", "type":"mountain", "height":17000, "hike_safety": 12.2}
{"name": "Kilimanjaro", "type":"mountain", "height":14000 }
{"name": "Mt Washington", "type":"mountain", "hike_safety": 50.6}
dbadmin=>


dbadmin=> create table mountains( name varchar(26), type varchar(20), height int, hike_safety numeric(10,2));
CREATE TABLE

dbadmin=> copy mountains from '/tmp/sample.json' parser fjsonparser();
 Rows Loaded
-------------
           5
(1 row)

dbadmin=> select * from mountains;
     name      |   type   | height | hike_safety
---------------+----------+--------+-------------
 Mt St Helens  | volcano  |  29029 |       15.40
 Denali        | mountain |  17000 |       12.20
 Everest       | mountain |  29029 |       34.10
 Kilimanjaro   | mountain |  14000 |
 Mt Washington | mountain |        |       50.60
(5 rows)

JSON column in Regular table (not flex)

Comments

Leave a Comment