The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Flex table interprets version string as a date

I am trying to load a JSON which has "version" (e.g. 2.3.1, 5.3.4) field to a Flex table, however, when I compute the keys it is interpreting these strings as date. Can someone please help with this? This is on Vertica 9.2.0

Steps to recreate:
test.json

{"id":1, "version": "5.3.4"}
{"id":2, "version": "5.5.4"}

Load this file to a flex table

create flex table test();
copy test from '/home/dbadmin/test.json' parser fjsonparser(key_separator='_');
select compute_flextable_keys('test');
select build_flextable_view('test');
select * from test_keys;
key_name|frequency|data_type_guess|
--------|---------|---------------|
id      |        2|Integer        |
version |        2|Date           |            
select * from test_view;
id|version   |
--|----------|
1 |2004-05-03|
2 |2004-05-05|
Tagged:

Answers

  • Jim_KnicelyJim_Knicely Administrator
    edited December 3

    The string values '5.3.4' and '5.5.4' can be implicitly coerced to DATEs:

    dbadmin=> SELECT '5.3.4'::DATE "Hmm..." UNION ALL SELECT '5.5.4'::DATE;
       Hmm...
    ------------
     2004-05-03
     2004-05-05
    (2 rows)
    

    Try temporily turing off the the EnableBetterFlexTypeGuessing parameter...

    dbadmin=> SELECT current_value, default_value, description FROM configuration_parameters WHERE parameter_name = 'EnableBetterFlexTypeGuessing';
     current_value | default_value |                            description
    ---------------+---------------+-------------------------------------------------------------------
     0             | 1             | When computing flex table keys, attempt to guess non-string types
    (1 row)
    
    dbadmin=> SELECT set_config_parameter('EnableBetterFlexTypeGuessing', 0);
        set_config_parameter
    ----------------------------
     Parameter set successfully
    (1 row)
    
    dbadmin=> CREATE FLEX TABLE test();
    CREATE TABLE
    
    dbadmin=> \! cat /home/dbadmin/test.json
    {"id":1, "version": "5.3.4"}
    {"id":2, "version": "5.5.4"}
    
    dbadmin=> COPY test FROM '/home/dbadmin/test.json' PARSER fjsonparser();
     Rows Loaded
    -------------
               2
    (1 row)
    
    dbadmin=> SELECT compute_flextable_keys_and_build_view('test');
                                compute_flextable_keys_and_build_view
    ----------------------------------------------------------------------------------------------
     Please see public.test_keys for updated keys
    The view public.test_view is ready for querying
    (1 row)
    
    dbadmin=> \dv public.test_view
                    List of View Fields
     Schema |   View    | Column  |    Type     | Size
    --------+-----------+---------+-------------+------
     public | test_view | id      | varchar(20) |   20
     public | test_view | version | varchar(20) |   20
    (2 rows)
    
    dbadmin=> SELECT set_config_parameter('EnableBetterFlexTypeGuessing', 1);
        set_config_parameter
    ----------------------------
     Parameter set successfully
    (1 row)
    

    See:
    https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/FlexTables/SettingFlexTableParameters.htm

  • Jim_KnicelyJim_Knicely Administrator
    edited December 6

    Or, you can also simply create your own view The actual data in the FLEX table is what you loaded. It's the view that's doing the data type coercion!

    dbadmin=> DROP TABLE test;
    DROP TABLE
    
    dbadmin=> CREATE FLEX TABLE test();
    CREATE TABLE
    
    dbadmin=> COPY test FROM '/home/dbadmin/test.json' PARSER fjsonparser();
     Rows Loaded
    -------------
               2
    (1 row)
    
    dbadmin=> SELECT compute_flextable_keys_and_build_view('test');
                                compute_flextable_keys_and_build_view
    ----------------------------------------------------------------------------------------------
     Please see public.test_keys for updated keys
    The view public.test_view is ready for querying
    (1 row)
    
    dbadmin=> SELECT view_definition FROM views WHERE table_name = 'test_view';
                      view_definition
    ----------------------------------------------------
     SELECT id::!int8, version::!Date FROM public.test;
    (1 row)
    
    dbadmin=> CREATE OR REPLACE VIEW public.test_view AS SELECT id::!int8, version::!Varchar FROM public.test;
    CREATE VIEW
    
    dbadmin=> SELECT * FROM public.test_view;
     id | version
    ----+---------
      1 | 5.3.4
      2 | 5.5.4
    (2 rows)
    
  • Thanks for the detailed response Jim.

    It does not always 'guess' it as date, for the following JSON, it guesses 'version1' as Date but 'version2' as Varchar

    {"version1":"6.1.4","version2":"7.0.2"}
    {"version1" : "4.1.1","version2" : "7.0.1"}

    I can disable 'EnableBetterFlexTypeGuessing' but dealing with boolean or numeric values requires some work.

    I am not sure how 'data_type_guess' works but I am assuming it takes into account the top K occurrences of the values of the column, and determines which data type suits these top K values.
    Anyways, it is an implementation detail which we do not have control over, so as a work around I was thinking if I update the '_keys' table with correct data type for this column and then build the view I should see correct results.

  • There is no month 0. They need to be 1 to 12. So a date parsing would fail.
    In general , a one to two digit number, from 1 to 13, followed by a dot, followed by a one to two digit number, from one to twelve, followed by a dot, followed by either a number from 1 to 9999 , or a number from 1 to 99, is a date in European DMY format for Vertica.
    So "6.1.4" is the sixth of January 2004, and "4.1.1" is the fourth of January 2001.

  • Not 1 to 13, of course, it's 1 to 31, my fingers were a bit un-synchronised ....

  • Thanks marcothesane, I didn't know it was a valid date format.

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.