COMPUTE_FLEXTABLE_KEYS
Computes the virtual columns (keys and values) from the flex table VMap data. Use this function to compute keys without creating an associated table view. To also build a view, use COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW.
If the length of a key exceeds 65,000, Vertica truncates the key.
The function stores its results in the associated flex _keys
table, which has the following columns:
key_name
frequency
data_type_guess
For more information, see Computing Flex Table Keys.
Syntax
COMPUTE_FLEXTABLE_KEYS('[[database.]schema.]flex‑table')
Arguments
[database.]schema |
Specifies a schema, by default myschema.thisDbObject If you specify a database, it must be the current database. |
flex‑table |
Name of a flex table. |
Using Data Type Guessing
The results of the flex _keys table data_type_guess
column depend on the EnableBetterFlexTypeGuessing
configuration parameter. By default, the parameter is 1 (ON). This setting results in the function returning all non-string keys in the data_type_guess
column as one of the following types (and others listed in SQL Data Types):
- BOOLEAN
- INTEGER
- FLOAT
- TIMESTAMP
- DATE
Setting the configuration parameter to 0 (OFF), results in the function returning only string types ([LONG]VARCHAR
) or ([LONG] VARBINARY
) for all values in the data_type_guess
column of the flex _keys table .
Assigning Flex Key Data Types
Use the sample CSV data in this section to compare the results of using or not using the EnableBetterFlexTypeGuessing
configuration parameter. When the parameter is ON, the function determines key non-string data types in your map data more accurately. The default for the parameter is 1 (ON).
Year,Quarter,Region,Species,Grade,Pond Value,Number of Quotes,Available 2015,1,2 - Northwest Oregon & Willamette,Douglas-fir,1P,$615.12 ,12,No 2015,1,2 - Northwest Oregon & Willamette,Douglas-fir,SM,$610.78 ,12,Yes 2015,1,2 - Northwest Oregon & Willamette,Douglas-fir,2S,$596.00 ,20,Yes 2015,1,2 - Northwest Oregon & Willamette,Hemlock,P,$520.00 ,6,Yes 2015,1,2 - Northwest Oregon & Willamette,Hemlock,SM,$510.00 ,6,No 2015,1,2 - Northwest Oregon & Willamette,Hemlock,2S,$490.00 ,14,No
To compare the data type assignment results, complete the following steps:
- Save the CSV data file (here, as
trees.csv
). - Create a flex table (
trees
) and loadtrees.csv
using thefcsvparser
: - Use
COMPUTE_FLEXTABLE_KEYS
with thetrees
flex table. - Query the
trees_keys
table output.: - Set the
EnableBetterFlexTypeGuessing
parameter to 0 (OFF). - Call
COMPUTE_FLEXTABLE_KEYS
with thetrees
flex table again. - Query the
trees_keys
table to compare thedata_type_guess
values with the previous results. Without the configuration parameter set, all of the non-string data types are VARCHARS of various lengths: - To maintain accurate results for non-string data types, set the
EnableBetterFlexTypeGuessing
parameter back to 1 (ON).
=> CREATE FLEX TABLE trees(); => COPY trees FROM '/home/dbadmin/tempdat/trees.csv' PARSER fcsvparser();
=> SELECT COMPUTE_FLEXTABLE_KEYS('trees'); COMPUTE_FLEXTABLE_KEYS ----------------------------------------------- Please see public.trees_keys for updated keys (1 row)
=> SELECT * FROM trees_keys; key_name | frequency | data_type_guess ------------------+-----------+----------------- Year | 6 | Integer Quarter | 6 | Integer Region | 6 | Varchar(66) Available | 6 | Boolean Number of Quotes | 6 | Integer Grade | 6 | Varchar(20) Species | 6 | Varchar(22) Pond Value | 6 | Numeric(8,3) (8 rows)
=> SELECT * FROM trees_keys; key_name | frequency | data_type_guess ------------------+-----------+----------------- Year | 6 | varchar(20) Quarter | 6 | varchar(20) Region | 6 | varchar(66) Available | 6 | varchar(20) Grade | 6 | varchar(20) Number of Quotes | 6 | varchar(20) Pond Value | 6 | varchar(20) Species | 6 | varchar(22) (8 rows)
For more information about setting the EnableBetterFlexTypeGuessing
configuration parameter, see Setting Flex Table Configuration Parameters.