What are the main advantages of using Vertica flex tables?

retheeshraviretheeshravi Vertica Customer
in General Discussion

What are the main advantages of using Vertica flex tables? Is there any difference in the license allowance to use the flex table over columnar tables?

Tagged:

Best Answer

  • SruthiASruthiA Vertica Employee Administrator
    Answer ✓

    @retheeshravi : Flex tables are mainly useful in the scenario when you don't know the data types of the columns being ingested in the table, number of columns etc. flex tables

    Do not require schema definitions
    Do not need column definitions
    Have full Unicode support
    Support SQL queries

    You can use flex tables to promote data directly from exploration to analytic operations. Flex table features include:

    Ability to load different formats into one flex table, which lets you handle changing structure over time
    Full support of delimited and JSON data
    Extensive SQL queries and built-in analytics for the data you load
    Usability functions, which let you explore your unstructured data and then use built-in functions to materialize the data

    https://www.vertica.com/docs/11.0.x/HTML/Content/Authoring/FlexTables/UnderstandingFlexTables.htm

    With regards to license usage, it should not be much different compared to columnar tables. it audits all the materialized columns.

Answers

  • retheeshraviretheeshravi Vertica Customer
    Thank you Sruthi, for your prompt response. I was curious to know how much the storage a physical file takes up in a columnar and flex table.
  • SruthiASruthiA Vertica Employee Administrator

    I just ran a quick test to insert same data into real table and flex table and notice a slight difference. I have used basic data types. In my scenario, flex table used 493 bytes versus real table which used 612 bytes. Please try it with your use case and the datatypes and run audit to see how much it used.

    dbadmin=> CREATE FLEX TABLE flex1();
    CREATE TABLE
    dbadmin=> INSERT INTO flex1(a,b) VALUES (1, 'x');

    OUTPUT

      1

    (1 row)

    dbadmin=> commit;
    COMMIT

    CREATE FLEX TABLE flex2();

    dbadmin=> INSERT INTO flex2(a,b) SELECT a,b, '2016-08-10 11:10' c, 'Hello' d, 3.1415 e, f from flex1; --- repeated it 17 times..

    OUTPUT

      1

    (1 row)

    dbadmin=> commit;
    COMMIT
    dbadmin=> SELECT MapToString(raw) FROM flex2;

    MapToString

    {
    "a": "1",
    "b": "x",
    "c": "2016-08-10 11:10",
    "d": "Hello",
    "e": "3.1415",
    "f": null
    }
    {
    "a": "1",
    "b": "x",
    "c": "2016-08-10 11:10",
    "d": "Hello",
    "e": "3.1415",
    "f": null
    }
    {
    "a": "1",
    "b": "x",
    "c": "2016-08-10 11:10",
    "d": "Hello",
    "e": "3.1415",
    "f": null
    }
    ....
    ....

    dbadmin=> select * from flex2;
    identity | raw
    --------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    250007 | \001\000\000\0009\000\000\000\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000.\000\000\0003\000\000\000\377\377\377\3771x2016-08-10 11:10Hello3.1415\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000\037\000\000\000 \000\000\000!\000\000\000abcdef
    250012 | \001\000\000\0009\000\000\000\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000.\000\000\0003\000\000\000\377\377\377\3771x2016-08-10 11:10Hello3.1415\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000\037\000\000\000 \000\000\000!\000\000\000abcdef
    250015 | \001\000\000\0009\000\000\000\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000.\000\000\0003\000\000\000\377\377\377\3771x2016-08-10 11:10Hello3.1415\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000\037\000\000\000 \000\000\000!\000\000\000abcdef
    250002 | \001\000\000\0009\000\000\000\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000.\000\000\0003\000\000\000\377\377\377\3771x2016-08-10 11:10Hello3.1415\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000\037\000\000\000 \000\000\000!\000\000\000abcdef
    250004 | \001\000\000\0009\000\000\000\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000.\000\000\0003\000\000\000\377\377\377\3771x2016-08-10 11:10Hello3.1415\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000\037\000\000\000 \000\000\000!\000\000\000abcdef
    250006 | \001\000\000\0009\000\000\000\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000.\000\000\0003\000\000\000\377\377\377\3771x2016-08-10 11:10Hello3.1415\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000\037\000\000\000 \000\000\000!\000\000\000abcdef
    250009 | \001\000\000\0009\000\000\000\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000.\000\000\0003\000\000\000\377\377\377\3771x2016-08-10 11:10Hello3.1415\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000\037\000\000\000 \000\000\000!\000\000\000abcdef
    250013 | \001\000\000\0009\000\000\000\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000.\000\000\0003\000\000\000\377\377\377\3771x2016-08-10 11:10Hello3.1415\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000\037\000\000\000 \000\000\000!\000\000\000abcdef
    250016 | \001\000\000\0009\000\000\000\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000.\000\000\0003\000\000\000\377\377\377\3771x2016-08-10 11:10Hello3.1415\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000\037\000\000\000 \000\000\000!\000\000\000abcdef
    250001 | \001\000\000\0009\000\000\000\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000.\000\000\0003\000\000\000\377\377\377\3771x2016-08-10 11:10Hello3.1415\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000\037\000\000\000 \000\000\000!\000\000\000abcdef
    250003 | \001\000\000\0009\000\000\000\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000.\000\000\0003\000\000\000\377\377\377\3771x2016-08-10 11:10Hello3.1415\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000\037\000\000\000 \000\000\000!\000\000\000abcdef
    250005 | \001\000\000\0009\000\000\000\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000.\000\000\0003\000\000\000\377\377\377\3771x2016-08-10 11:10Hello3.1415\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000\037\000\000\000 \000\000\000!\000\000\000abcdef
    250008 | \001\000\000\0009\000\000\000\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000.\000\000\0003\000\000\000\377\377\377\3771x2016-08-10 11:10Hello3.1415\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000\037\000\000\000 \000\000\000!\000\000\000abcdef
    250010 | \001\000\000\0009\000\000\000\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000.\000\000\0003\000\000\000\377\377\377\3771x2016-08-10 11:10Hello3.1415\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000\037\000\000\000 \000\000\000!\000\000\000abcdef
    250011 | \001\000\000\0009\000\000\000\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000.\000\000\0003\000\000\000\377\377\377\3771x2016-08-10 11:10Hello3.1415\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000\037\000\000\000 \000\000\000!\000\000\000abcdef
    250014 | \001\000\000\0009\000\000\000\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000.\000\000\0003\000\000\000\377\377\377\3771x2016-08-10 11:10Hello3.1415\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000\037\000\000\000 \000\000\000!\000\000\000abcdef
    250017 | \001\000\000\0009\000\000\000\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000.\000\000\0003\000\000\000\377\377\377\3771x2016-08-10 11:10Hello3.1415\006\000\000\000\034\000\000\000\035\000\000\000\036\000\000\000\037\000\000\000 \000\000\000!\000\000\000abcdef
    (17 rows)

    dbadmin=> create table regular(a int, b varchar(50), c timestamp, d varchar(50), e float), f varchar(50));
    CREATE TABLE

    dbadmin=> INSERT INTO regular SELECT 1 a,'x' b, '2016-08-10 11:10' c, 'Hello' d, 3.1415 e, 'null' f from flex1; ---- repeated it 17 times.

    OUTPUT

      1

    (1 row)

    dbadmin=> commit;
    COMMIT

    select count(*) from regular;

    count

    17

    dbadmin=> select audit_flex('flex2');

    audit_flex

    493
    (1 row)

    dbadmin=> select audit('regular', 'table');

    audit

    612
    (1 row)

  • retheeshraviretheeshravi Vertica Customer
    Thanks Sruthi. That makes sense to me.
  • lindsayhastylindsayhasty Community Edition User

    Sruthi you make all this simple for me. Thank u

  • jacksonwilljacksonwill Community Edition User
    edited September 2022

    @SruthiA said:
    @retheeshravi : Flex tables are mainly useful in the scenario when you don't know the data types of the columns being ingested in the table, number of columns etc. flex tables

    Do not require schema definitions
    Do not need column definitions
    Have full Unicode support
    Support SQL queries

    You can use flex tables to promote data directly from exploration to analytic operations. Flex table features include:

    Ability to load different formats into one flex table, which lets you handle changing structure over time
    Full support of delimited and JSON data
    Extensive SQL queries and built-in analytics for the data you load
    Usability functions, which let you explore your unstructured data and then use built-in functions to materialize the data

    https://www.vertica.com/docs/11.0.x/HTML/Content/Stickman/FlexTables/UnderstandingFlexTables.htm

    With regards to license usage, it should not be much different compared to columnar tables. it audits all the materialized columns.

    Thankyou so much i think i found the solution to my problem

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...