Updating flex table views
Creating a flex table also creates a default view to accompany the table. The view has the name of the flex table with an underscore (_view) suffix. When you perform a SELECT query from the default view, Vertica prompts you to call the helper function COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW:
=> \dv dark*
List of View Fields
Schema | View | Column | Type | Size
--------+----------------+--------+--------------+------
public | darkdata_view | status | varchar(124) | 124
public | darkdata1_view | status | varchar(124) | 124
(2 rows)
=> SELECT * FROM darkdata_view;
status
-----------------------------------------------------------------------------------------------
Please run compute_flextable_keys_and_build_view() to update this view to reflect real and
virtual columns in the flex table
(1 row)
There are two helper functions that create views:
-
BUILD_FLEXTABLE_VIEW: Creates or re-creates a view for a default or user-defined keys table, ignoring any empty keys.
-
COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW: Computes virtual columns (keys) from the VMap data of a flex table and constructs a view (see also Using COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW).
Using BUILD_FLEXTABLE_VIEW
After computing keys for a flex table (Computing flex table keys), call this function with one or more arguments. The records under the key_name column of the table_name_keys table are used as view columns, along with any values for the key. If no values exist, the column value is NULL.
Regardless of the number of arguments, calling this function replaces the contents of the existing view as follows:
-
BUILD_FLEXTABLE_VIEW ('table'): Changes the table's existing view with the current contents of thetable_keystable. -
BUILD_FLEXTABLE_VIEW ('table', 'view_name'): Changes the named view with the current contents of thetable_keystable. -
BUILD_FLEXTABLE_VIEW ('table', 'view_name', 'table_keys'): Changes the named view using the named keys table. Use this option if you created a custom keys table from the flex table map data, rather than from the default keys table.
If you do not specify a view name, the default name is the flex table name with a _view suffix. For example, if you specify the table darkdata as the sole argument to this function, the default view is called darkdata_view.
You cannot specify a custom view name with the same name as the default view (table_name_view), unless you first drop the default-named view and then create your own view of the same name.
Creating a view stores a definition of the column structure at the time of creation. Thus, if you create a flex table view and then promote virtual columns to real columns, you must rebuild the view. Querying a rebuilt flex table view that has newly promoted real columns produces two results. These results reflect values from both virtual columns in the map data and real columns.
Handling JSON duplicate key names in views
SQL is a case-insensitive language, so the names TEST, test, and TeSt are identical. JSON data is case sensitive, so that it can validly contain key names of different cases with separate values.
When you build a flex table view, the function generates a warning if it detects same-name keys with different cases in the table_keys table. For example, calling BUILD_FLEXTABLE_VIEW or COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW on a flex table with duplicate key names results in these warnings:
=> SELECT COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW('dupe');
WARNING 5821: Detected keys sharing the same case-insensitive key name
WARNING 5909: Found and ignored keys with names longer than the maximum column-name length limit
compute_flextable_keys_and_build_view
----------------------------------------------------------------------------------------------
Please see public.dupe_keys for updated keys
The view public.dupe_view is ready for querying
(1 row)
While a keys table can include duplicate key names with different cases, a view cannot. Creating a flex table view with either of the helper functions consolidates any duplicate key names to one column name, consisting of all lowercase characters. All duplicate key values for that column are saved. For example, if a flex table contains keys named test, Test, and tESt, the view includes a virtual column test with values from all three keys.
The following examples include added line breaks for readability.
Consider the following query, showing the duplicate test key names:
=> \x
Expanded display is on.
dbt=> select * from dupe_keys;
-[ RECORD 1 ]---+--------------------------------------------------------------------------------------------------
key_name | TesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttest
TesttestTesttestTesttestTesttest
frequency | 2
data_type_guess | varchar(20)
-[ RECORD 2 ]---+--------------------------------------------------------------------------------------------------
key_name | TesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttest
TesttestTesttestTesttestTest12345
frequency | 2
data_type_guess | varchar(20)
-[ RECORD 3 ]---+--------------------------------------------------------------------------------------------------
key_name | test
frequency | 8
data_type_guess | varchar(20)
-[ RECORD 4 ]---+--------------------------------------------------------------------------------------------------
key_name | TEst
frequency | 8
data_type_guess | varchar(20)
-[ RECORD 5 ]---+--------------------------------------------------------------------------------------------------
key_name | TEST
frequency | 8
data_type_guess | varchar(20)
The following query displays the dupe flex table (dupe_view). It shows the consolidated virtual columns, with all values in the test column:
=> SELECT * FROM dupe_view;
test | testtesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttest
testtesttesttesttesttesttesttest
--------+--------------------------------------------------------------------------------------------------
upper2 |
half4 |
lower1 |
upper1 |
half1 |
half4 |
|
lower1 |
half1 |
upper2 |
|
lower2 |
lower3 |
upper1 |
lower2 |
lower3 |
(16 rows)
Creating a flex table view
The following example shows how to create and query a view for a flex table that contains JSON data:
=> CREATE VIEW dd_view AS SELECT "user.lang"::VARCHAR, "user.name"::VARCHAR FROM darkdata;
CREATE VIEW
=> SELECT * FROM dd_view;
user.lang | user.name
-----------+---------------------
en | Uptown gentleman.
en | The End
it | laughing at clouds.
es | I'm Toasterâ¥
|
en | ~G A B R I E L A â¿
|
en | Avita Desai
tr | seydo shi
|
|
es | Flu Beach
(12 rows)
This example shows how to call BUILD_FLEXTABLE_VIEW with the original table and the view you previously created:
=> SELECT BUILD_FLEXTABLE_VIEW ('darkdata', 'dd_view');
build_flextable_view
-----------------------------------------------
The view public.dd_view is ready for querying
(1 row)
Query the view again. You can se that the function populated the view with the contents of the darkdata_keys table. Next, review a snippet from the results, with the key columns and their values:
=> \x
Expanded display is on.
=> SELECT * FROM dd_view;
.
.
.
user.following |
user.friends_count | 791
user.geo_enabled | F
user.id | 164464905
user.id_str | 164464905
user.is_translator | F
user.lang | en
user.listed_count | 4
user.location | Uptown..
user.name | Uptown gentleman.
.
.
.
When building views, be aware that creating a view stores a definition of the column structure at the time the view is created. If you promote virtual columns to real columns after building a view, the existing view definition is not changed. Querying this view with a select statement such as the following, returns values from only the __raw__ column:
=> SELECT * FROM myflextable_view;
Also understand that rebuilding the view after promoting virtual columns changes the resulting value. Future queries return values from both virtual columns in the map data and from real columns.
Using COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW
Call this function with a flex table to compute Flex table keys (see Computing flex table keys ) and create a view in one step.