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?
Question
What are the main advantages of using Vertica flex tables?
+1@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.
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.
+1I 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
@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 tablesDo not require schema definitions
Do not need column definitions
Have full Unicode support
Support SQL queriesYou 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 datahttps://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
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
