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.

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('[[database.]schema.]flex‑table' [, n‑columns [, keys‑table‑name] ])

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

  • Skips any columns already materialized
  • Ignores any empty keys

To materialize a specific number of columns, use the optional parameter n_columns, described next.

n‑columns

The number of columns to materialize. The function attempts to materialize the number of columns from the flex_table_keys table, skipping any columns already materialized.

Vertica tables support a total of 1600 columns, which is the largest value you can specify for n-columns. The function orders the materialized results by frequency, descending, key_name when materializing the first n columns.

keys‑table‑name

The name of a flex_keys_table from which to materialize columns. The function:

  • Materializes the number of columns (value of n-columns) from keys‑table‑name
  • Skips any columns already materialized
  • Orders the materialized results by frequency, descending, key_name when materializing the first n columns.

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 system table MATERIALIZE_FLEXTABLE_COLUMNS_RESULTS 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 Also