JSON column in Regular table (not flex)

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

Comments

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

     

  • Hello Sharon,

    Thank you for the reference.

    I'll test it tomorrow and share my findings.

     

    Yarden

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

     

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.