Urgent:Issue on loading data

Naveen_Kumar

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

[Deleted User]

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.