Flex Table Timestamp Handling in 7.1.x

williamzoom

I cannot get date_trunc() to work with a timestamp in a 7.1.x flex table when querying the view. When I execute:

 

 

select date_trunc(ds.date_full, 'YYYY') from integration_tests.flex_with_schema_view as ds LIMIT 10;

 

 

I get the error:

 

SQL Error [3457] [42883]: [Vertica][VJDBC](3457) ERROR: Function date_trunc(timestamp, unknown) does not exist, or permission is denied for date_trunc(timestamp, unknown)
[Vertica][VJDBC](3457) ERROR: Function date_trunc(timestamp, unknown) does not exist, or permission is denied for date_trunc(timestamp, unknown)
com.vertica.util.ServerException: [Vertica][VJDBC](3457) ERROR: Function date_trunc(timestamp, unknown) does not exist, or permission is denied for date_trunc(timestamp, unknown)

 

Background

 

We're experimenting with Flex tables and have a case where we have a flex schema and load in data from JSON. I've simplified the schema for our case, but our table looks something like:

 

CREATE FLEX TABLE integration_tests.flex_with_schema (
  date_full timestamp,
  date_milliseconds integer,
  string_full varchar(100)
);

 We have a JSON file with a sample row like:

 

[{
"date_full": "2014-01-01 01:59:59.999",
"date_milliseconds": 1356998400,
"string_full": "Illinois"
}]

 

I load the data in with:

 

COPY integration_tests.flex_with_schema FROM '/tmp/test_data1.json' PARSER fjsonparser(flatten_maps=false, flatten_arrays=false, reject_on_materialized_type_error=true);

 

All data loads successfully. If it was having trouble materializing date_full as a timestamp, I would expect an error since the reject_on_materialized_type_error is set. Lastly, I compute:

 

SELECT compute_flextable_keys_and_build_view('integration_tests.flex_with_schema');

 

Is it impossible to use date/time functions against materialized flex table views? I understand the data schema is "variable" but forcing a strict type I would still expect it to work.

 

Thanks,

 

William

williamzoom

Nevermind, silly syntax error, should've been date_trunc('year',date_full). Sorry!