Skip to main content

JSON column in Regular table (not flex)

  • May 11, 2016
  • 4 replies
  • 22 views
yardenbar
Forum|alt.badge.img+1

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

    4 replies

    Sharon_Cutter
    Forum|alt.badge.img
    • Sharon_Cutter
    • Participating Frequently
    • May 13, 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
    Forum|alt.badge.img+1
    • yardenbar
    • Author
    • Participating Frequently
    • May 14, 2016

    Hello Sharon,

    Thank you for the reference.

    I'll test it tomorrow and share my findings.

     

    Yarden

    Forum|alt.badge.img+2
    • Anonymous
    • Participating Frequently
    • May 16, 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)
    Forum|alt.badge.img+2
    • Anonymous
    • Participating Frequently
    • May 16, 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)

     

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement