This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Flex table data functions

The flex table data helper functions supply information you need to directly query data in flex tables.

1: BUILD_FLEXTABLE_VIEW
2: COMPUTE_FLEXTABLE_KEYS
3: COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW
4: MATERIALIZE_FLEXTABLE_COLUMNS
5: RESTORE_FLEXTABLE_DEFAULT_KEYS_TABLE_AND_VIEW

The flex table data helper functions supply information you need to directly query data in flex tables. After you compute keys and create views from the raw data, you can use field names directly in queries instead of using map functions to extract data.

In addition to these data meta-functions, there are flex functions that are not meta-functions.

Function	Description
COMPUTE_FLEXTABLE_KEYS	Computes map keys from the map data in a flex table and populates a keys table with the results. Use this function before building a view.
BUILD_FLEXTABLE_VIEW	Uses the keys in a table to create a view definition for the source table. Use this function after computing flex table keys.
COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW	Performs both of the preceding functions in one call.
MATERIALIZE_FLEXTABLE_COLUMNS	Materializes a specified number of columns.
RESTORE_FLEXTABLE_DEFAULT_KEYS_TABLE_AND_VIEW	Replaces the `flextable_data_keys` table and the `flextable_data_view` view, linking both the keys table and the view to the parent flex table.

Flex table dependencies

Each flex table has two dependent objects, a keys table and a view. While both objects are dependent on their parent table, you can drop either object independently. Dropping the parent table removes both dependents, without a CASCADE option.

Associating flex tables and views

The helper functions automatically use the dependent table and view if they are internally linked with the parent table. You create both when you create the flex table. You can drop either the keys table or the view and re-create objects of the same name. However, if you do so, the new objects are not internally linked with the parent flex table.

In this case, you can restore the internal links of these objects to the parent table. To do so, drop the keys table and the view before calling the RESTORE_FLEXTABLE_DEFAULT_KEYS_TABLE_AND_VIEW function. Calling this function re-creates the keys table and view.

The remaining helper functions perform the tasks described in this section.

1 - BUILD_FLEXTABLE_VIEW

Creates, or re-creates, a view for a default or user-defined keys table, ignoring any empty keys.

Note

If the length of a key exceeds 65,000, Vertica truncates the key.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Syntax

BUILD_FLEXTABLE_VIEW ('[[database.]schema.]flex-table'
    [ [,'view-name'] [,'user-keys-table'] ])

Arguments

[database.]schema: Database and schema. The default schema is public. If you specify a database, it must be the current database.
flex-table: The flex table name. By default, this function builds or rebuilds a view for the input table with the current contents of the associated flex_table_keys table.
view-name: A custom view name. Use this option to build a new view for flex-table with the name you specify.
user-keys-table: Name of a keys table from which to create the view. Use this option if you created a custom keys table from the flex table map data, rather than from the default flex_table_keys table. The function builds a view from the keys in user_keys, rather than from flex_table_keys.

Examples

The following examples show how to call BUILD_FLEXTABLE_VIEW with 1, 2, or 3 arguments.

To create, or re-create, a default view:

Call the function with an input flex table:

=> SELECT BUILD_FLEXTABLE_VIEW('darkdata');
                  build_flextable_view
-----------------------------------------------------
 The view public.darkdata_view is ready for querying
(1 row)

The function creates a view with the default name (darkdata_view) from the darkdata_keys table.

Query a key name from the new or updated view:

=> SELECT "user.id" FROM darkdata_view;
  user.id
-----------
 340857907
 727774963
 390498773
 288187825
 164464905
 125434448
 601328899
 352494946
(12 rows)

To create, or re-create, a view with a custom name:

Call the function with two arguments, an input flex table, darkdata, and the name of the view to create, dd_view:

=> SELECT BUILD_FLEXTABLE_VIEW('darkdata', 'dd_view');
            build_flextable_view
-----------------------------------------------
 The view public.dd_view is ready for querying
(1 row)

Query a key name (user.lang) from the new or updated view (dd_view):

=> SELECT "user.lang" FROM dd_view;
 user.lang
-----------
 tr
 en
 es
 en
 en
 it
 es
 en
(12 rows)

To create a view from a custom keys table with BUILD_FLEXTABLE_VIEW, the custom table must have the same schema and table definition as the default table (darkdata_keys). Create a custom keys table, using any of these three approaches:

Create a columnar table with all keys from the default keys table for a flex table (darkdata_keys):
```
=> CREATE TABLE new_darkdata_keys AS SELECT * FROMdarkdata_keys;
CREATE TABLE
```

Create a columnar table without content (LIMIT 0) from the default keys table for a flex table (darkdata_keys):

=> CREATE TABLE new_darkdata_keys AS SELECT * FROM darkdata_keys LIMIT 0;
CREATE TABLE
kdb=> SELECT * FROM new_darkdata_keys;
 key_name | frequency | data_type_guess
----------+-----------+-----------------
(0 rows)

Create a columnar table without content (LIMIT 0) from the default keys table, and insert two values ('user.lang', 'user.name') into the key_name column:

=> CREATE TABLE dd_keys AS SELECT * FROM darkdata_keys limit 0;
CREATE TABLE
=> INSERT INTO dd_keys (key_name) values ('user.lang');
 OUTPUT
--------
      1
(1 row)
=> INSERT INTO dd_keys (key_name) values ('user.name');
 OUTPUT
--------
      1
(1 row)
=> SELECT * FROM dd_keys;
 key_name  | frequency | data_type_guess
-----------+-----------+-----------------
 user.lang |           |
 user.name |           |
(2 rows)

After creating a custom keys table, call BUILD_FLEXTABLE_VIEW with all arguments (an input flex table, the new view name, the custom keys table):

=> SELECT BUILD_FLEXTABLE_VIEW('darkdata', 'dd_view', 'dd_keys');
            build_flextable_view
-----------------------------------------------
 The view public.dd_view is ready for querying
(1 row)

Query the new view:

=> SELECT * FROM dd_view;

2 - COMPUTE_FLEXTABLE_KEYS

Computes the virtual columns (keys and values) from the flex table VMap data.

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.

Note

If the length of a key exceeds 65,000, Vertica truncates the key.

The function stores its results in the associated flex keys table (flexTableName_keys), which has the following columns:

key_name
frequency
data_type_guess

For more information, see Computing flex table keys.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Syntax

COMPUTE_FLEXTABLE_KEYS ('[[database.]schema.]flex-table')

Arguments

[database.]schema: Database and schema. The default schema is public. If you specify a database, it must be the current database.
*flex-table*: Name of a flex table.

Using data type guessing

The results in the 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 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 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 load trees.csv using the fcsvparser:

=> CREATE FLEX TABLE trees();
=> COPY trees FROM '/home/dbadmin/tempdat/trees.csv' PARSER fcsvparser();

Use COMPUTE_FLEXTABLE_KEYS with the trees flex table.

=> SELECT COMPUTE_FLEXTABLE_KEYS('trees');
            COMPUTE_FLEXTABLE_KEYS
-----------------------------------------------
 Please see public.trees_keys for updated keys
(1 row)

Query the trees_keys table output.:

=> 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)

Set the EnableBetterFlexTypeGuessing parameter to 0 (OFF).
Call COMPUTE_FLEXTABLE_KEYS with the trees flex table again.

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:


=> 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)

To maintain accurate results for non-string data types, set the EnableBetterFlexTypeGuessing parameter back to 1 (ON).

For more information about the EnableBetterFlexTypeGuessing configuration parameter, see EnableBetterFlexTypeGuessing.

3 - COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW

Combines the functionality of BUILD_FLEXTABLE_VIEW and COMPUTE_FLEXTABLE_KEYS to compute virtual columns (keys) from the VMap data of a flex table and construct a view.

Combines the functionality of BUILD_FLEXTABLE_VIEW and COMPUTE_FLEXTABLE_KEYS to compute virtual columns (keys) from the VMap data of a flex table and construct a view. Creating a view with this function ignores empty keys. If you do not need to perform both operations together, use one of the single-operation functions instead.

Note

If the length of a key exceeds 65,000, Vertica truncates the key.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Syntax

COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW ('flex-table')

Arguments

flex-table: Name of a flex table

Examples

This example shows how to call the function for the darkdata flex table.

=> SELECT COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW('darkdata');
               compute_flextable_keys_and_build_view
-----------------------------------------------------------------------
 Please see public.darkdata_keys for updated keys
The view public.darkdata_view is ready for querying
(1 row)

4 - 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.

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.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Syntax

MATERIALIZE_FLEXTABLE_COLUMNS ('[[database.]schema.]flex-table' [, n-columns [, keys-table-name] ])

Arguments

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

flex-table

The name of the flex table with columns to materialize. The function:

Skips any columns already materialized
Ignores any empty keys

n-columns

The number of columns to materialize, up to 9800. The function attempts to materialize the number of columns from the keys table, skipping any columns already materialized. It orders the materialized results by frequency, descending. If not specified, the default is a maximum of 50 columns.

keys-table-name

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

Materializes n-columns columns from the keys table
Skips any columns already materialized
Orders the materialized results by frequency, descending

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)

5 - RESTORE_FLEXTABLE_DEFAULT_KEYS_TABLE_AND_VIEW

Restores the keys table and the view.

Restores the keys table and the view. The function also links the keys table with its associated flex table, in cases where either table is dropped. The function also indicates whether it restored one or both objects.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Syntax

RESTORE_FLEXTABLE_DEFAULT_KEYS_TABLE_AND_VIEW ('flex-table')

Arguments

flex-table: Name of a flex table

Examples

This example shows how to invoke this function with an existing flex table, restoring both the keys table and view:

=> SELECT RESTORE_FLEXTABLE_DEFAULT_KEYS_TABLE_AND_VIEW('darkdata');
                     RESTORE_FLEXTABLE_DEFAULT_KEYS_TABLE_AND_VIEW
----------------------------------------------------------------------------------
The keys table public.darkdata_keys was restored successfully.
The view public.darkdata_view was restored successfully.
(1 row)

This example illustrates that the function restored darkdata_view, but that darkdata_keys did not need restoring:

=> SELECT RESTORE_FLEXTABLE_DEFAULT_KEYS_TABLE_AND_VIEW('darkdata');
                    RESTORE_FLEXTABLE_DEFAULT_KEYS_TABLE_AND_VIEW
------------------------------------------------------------------------------------
 The keys table public.darkdata_keys already exists and is linked to darkdata.
 The view public.darkdata_view was restored successfully.
(1 row)

After restoring the keys table, there is no content. To populate the flex keys, call the COMPUTE_FLEXTABLE_KEYS function.

=> SELECT * FROM darkdata_keys;
 key_name | frequency | data_type_guess
----------+-----------+-----------------
(0 rows)

Flex table data functions

Flex table dependencies

Associating flex tables and views

1 - BUILD_FLEXTABLE_VIEW

Note

Behavior type

Syntax

Arguments

Examples

See also

2 - COMPUTE_FLEXTABLE_KEYS

Note

Behavior type

Syntax

Arguments

Using data type guessing

Assigning flex key data types

See also

3 - COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW

Note

Behavior type

Syntax

Arguments

Examples

See also

4 - MATERIALIZE_FLEXTABLE_COLUMNS

Note

Behavior type

Syntax

Arguments

Examples

See also

5 - RESTORE_FLEXTABLE_DEFAULT_KEYS_TABLE_AND_VIEW

Behavior type

Syntax

Arguments

Examples

See also