Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Urgent:Issue on loading data

Create flex table flexT(ts timestamp);
copy flexT from 'path/file.json' parser fjsonparser();

select count(maplookup(__raw__,'ts')) from flexT;
 count
--------
 361471
select count(ts) from flexT;
 count
-------
     0
(1 row)
while loading data ,it has not populated "ts" column.
I think it should.
Because when i add any  key with data type varchar, it is giving perfect result. but not with any other datatype. 
Can anyone explain why this is happening?


Thanks in advance.

Regards 
naveen


Comments

  • The most likely cause for this is that the "ts" key in your JSON file doesn't contain valid timestamps. See what happens if you try: select maplookup(__raw__, 'ts'),  maplookup(__raw__, 'ts')::timestamp from flexT;

    To extend your example to show what happens when loading one valid timestamp and one invalid timestamp:


    jfraumeni=> Create flex table flexT(ts timestamp);
    CREATE TABLE
    jfraumeni=> copy flexT from stdin parser fjsonparser();
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    >> {"ts": "2014-01-01 05:55:55"}
    {"ts": "2014-21-01 05:55:55"}
    \.

    jfraumeni=> select maplookup(__raw__, 'ts'),  maplookup(__raw__, 'ts')::timestamp from flexT;
          maplookup      |      maplookup
    ---------------------+---------------------
     2014-01-01 05:55:55 | 2014-01-01 05:55:55
     2014-21-01 05:55:55 |
    (2 rows)

    jfraumeni=> select count(maplookup(__raw__,'ts')) from flexT;
     count
    -------
         2
    (1 row)

    jfraumeni=> select count(ts) from flexT;
     count
    -------
         1
    (1 row)


    In background, we want Flexible Tables to accept as much data as it can by default. There arepotential improvements we've looked at which would allow you to cause the Flex parsers to be more draconian in what data they let in, but since we expect lots of dirty data, we don't want to kick out a whole row if all of the data is parseable and can be stored in the __raw__ map.

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.