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 run 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 — See also COMPUTE_FLEXTABLE_KEYS

• COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW— Performs the same functionality as BUILD_FLEXTABLE_KEYS but also computes keys. 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 {flextable}_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:

If you do not specify a view_name argument, 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 flex_table_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 {flextable}_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 {flextable}_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 these key names exist in a flex table:

• test

• Test

• tESt

The view will include a virtual column test with values from the test, Test, and tESt keys.

For example, 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 test and testtesttest... virtual columns. All the test, Test, and tESt virtual column values are 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 a view, dd_view, from the flex table darkdata, which contains JSON data.

=> CREATE VIEW dd_view AS SELECT "user.lang"::VARCHAR, "user.name"::VARCHAR FROM darkdata;
CREATE VIEW

Query the key names you specified, and their values:

=> 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, dd_view:

=> 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 see that the function populated the view with the contents of the darkdata_keys table. Next, review a snippet from the results, with the key_name 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.