BUILD_FLEXTABLE_VIEW

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

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

Syntax

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

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

Specifies a keys table from which to create the view. Use this option if you created a custom user_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 table, rather than from the flex_table_keys table.

Examples

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

Creating a Default View

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

  1. Call the function with an input flex table, darkdata: 

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

  2. Query a key name (user.id) from the new or updated view:

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

Creating a Custom Name View

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

  1. 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)

  2. 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)

Creating a View from a Custom Keys Table

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:

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

  2. 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)

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

  5. Query the new view: 

    => SELECT * FROM dd_view;

See Also