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 public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

If you specify a database, it must be the current database.
flex‑table

The 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: 

  1. Save the CSV data file (here, as trees.csv).
  2. Create a flex table (trees) and load trees.csv using the fcsvparser:  
    3. => CREATE FLEX TABLE trees();
=> COPY trees FROM '/home/dbadmin/tempdat/trees.csv' PARSER fcsvparser();
  3. Use COMPUTE_FLEXTABLE_KEYS with the trees flex table. 
    4. => SELECT COMPUTE_FLEXTABLE_KEYS('trees');
            COMPUTE_FLEXTABLE_KEYS
-----------------------------------------------
 Please see public.trees_keys for updated keys
(1 row)
  4. Query the trees_keys table output.: 
    5. => 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)
  5. Set the EnableBetterFlexTypeGuessing parameter to 0 (OFF).
  6. Call COMPUTE_FLEXTABLE_KEYS with the trees flex table again.
  7. Query the trees_keys table to compare the data_type_guess values with the previous results. Without the configuration parameter set, all of the non-string data types are VARCHARS of various lengths:
    8. => 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)
  8. To maintain accurate results for non-string data types, set the EnableBetterFlexTypeGuessing parameter back to 1 (ON).

For more information about setting the EnableBetterFlexTypeGuessing configuration parameter, see Setting Flex Table Configuration Parameters.

See Also