Setting Flex Table Configuration Parameters
These configuration parameters affect flex table usage:
EnableBetterFlexTypeGuessing
FlexTableRawSize
FlexTableDataTypeGuessMultiplier
This section presents information about each of the parameters.
To change configuration parameters, and to understand parameter scope, see Setting Configuration Parameter Values and General Parameters in the Administrator's Guide.
Using EnableBetterFlexTypeGuessing
The EnableBetterFlexTypeGuessing
configuration parameter is 1 (ON) by default. This setting results in the COMPUTE_FLEXTABLE_KEYS
or COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW
functions determining data types for string and non-string keys. If you change the configuration parameter to 0 (OFF), the functions determine all flex keys as string types ([LONG]VARCHAR
) or ([LONG] VARBINARY
).
You can set this configuration parameter at the database and session level.
For examples of using both settings, see Computing Flex Table Keys.
Specifying the FlexTableRawSize Parameter
The FlexTableRawSize
parameter defines the default width of each flex table __raw__
column. This column is a LONG VARBINARY
data type, and contains the map data you load into the table.
Setting this configuration parameter does not affect any existing flex tables, only the default width for new flex tables you create after changing FlexTableRawSize
.
Effective Vertica 9.2.1, you can set this configuration parameter at the database and session level.
To change the __raw__
column width of an existing flex table, use the ALTER TABLE statement, described in Materializing Flex Tables.
Redefining the FlexTableDataTypeGuessMultiplier
After loading data into a flex table, each key in the __raw__
column is a LONG VARBINARY
data type.
When you compute flex table keys with either of the functions, COMPUTE_FLEXTABLE_KEYS or COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW, the functions cast each key to the applicable Vertica data type. The key value length is determined by the key's largest value, multiplied by the FlexTableDataTypeGuessMultiplier
factor. Padding the column width with the multiplier supports future data load key values at least twice the largest key value previously loaded.
The flex keys table that both flex functions populate is used to create the associated flex table view.
The FlexTableDataTypeGuessMultiplier
value is not used to calculate the width of any real columns in a flex table.