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

What are the main advantages of using Vertica flex tables?

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 Employee
    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

  • 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 Employee

    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)

  • Thanks Sruthi. That makes sense to me.
  • Sruthi you make all this simple for me. Thank u

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.