MATERIALIZE_FLEXTABLE_COLUMNS
Materializes virtual columns listed as key_names in the flextable_keys table you compute using either COMPUTE_FLEXTABLE_KEYS or COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW.
Note: Each column that you materialize with this function counts against the data storage limit of your license. To check your Vertica license compliance, call the AUDIT()
or AUDIT_FLEX()
functions.
Syntax
materialize_flextable_columns('
flex_table' [, n-columns [, keys_table_name] ])
Arguments
flex_table |
The name of the flex table with columns to materialize. Specifying only the flex table name attempts to materialize up to 50 columns of key names in the default flex_table_keys table. When you use this argument, the function:
To materialize a specific number of columns, use the optional parameter |
n-columns |
[Optional ] The number of columns to materialize. The function attempts to materialize the number of columns from the Vertica tables support a total of 1600 columns, which is the largest value you can specify for |
keys_table_name |
[Optional] The name of a flex_keys_table from which to materialize columns. The function:
|
Examples
The following example shows how to call materialize_flextable_columns
to materialize columns. First, load a sample file of tweets (tweets_10000.json
) into the flex table twitter_r
.
After loading data and computing keys for the sample flex table, call materialize_flextable_columns
to materialize the first four columns:
=> COPY twitter_r FROM '/home/release/KData/tweets_10000.json' parser fjsonparser();
Rows Loaded
-------------
10000
(1 row)
=> SELECT compute_flextable_keys ('twitter_r');
compute_flextable_keys
---------------------------------------------------
Please see public.twitter_r_keys for updated keys
(1 row)
=> select materialize_flextable_columns('twitter_r', 4);
materialize_flextable_columns
-------------------------------------------------------------------------------
The following columns were added to the table public.twitter_r:
contributors
entities.hashtags
entities.urls
For more details, run the following query:
SELECT * FROM v_catalog.materialize_flextable_columns_results WHERE table_schema = 'public' and table_name = 'twitter_r';
(1 row)
The last message in the example recommends querying the materialize_flextable_columns_results
system table for the results of materializing the columns, as shown:
=> SELECT * FROM v_catalog.materialize_flextable_columns_results WHERE table_schema = 'public' and table_name = 'twitter_r'; table_id | table_schema | table_name | creation_time | key_name | status | message -------------------+--------------+------------+------------------------------+-------------------+--------+--------------------- 45035996273733172 | public | twitter_r | 2013-11-20 17:00:27.945484-05| contributors | ADDED | Added successfully 45035996273733172 | public | twitter_r | 2013-11-20 17:00:27.94551-05 | entities.hashtags | ADDED | Added successfully 45035996273733172 | public | twitter_r | 2013-11-20 17:00:27.945519-05| entities.urls | ADDED | Added successfully 45035996273733172 | public | twitter_r | 2013-11-20 17:00:27.945532-05| created_at | EXISTS | Column of same name already (4 rows)
See the MATERIALIZE_FLEXTABLE_COLUMNS_RESULTS system table in the SQL Reference Manual.