Vertica Analytics Platform Version 9.2.x Documentation

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;