What are the main advantages of using Vertica flex tables?

retheeshravi

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?

SruthiA

@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.

retheeshravi

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.

SruthiA

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)

retheeshravi

Thanks Sruthi. That makes sense to me.

lindsayhasty

Sruthi you make all this simple for me. Thank u

jacksonwill

@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