HP Vertica build_flextable_view () function

The build_flextable_view create a view of the flex table using the test_keys table created in the previous article using the compute_flextable_keys() function.

Wen the flex table is created the test_view is created automatically but is not populated until you run the compute_flextable_keys_and_build_view() or you run compute_flextable_keys() and build_flextable_view() separate.

  • See test_view definition/content after the flex table creation.
    • dbadmin=> dv test_view
                List of View Fields
 Schema |   View    | Column |     Type     | Size
--------+-----------+--------+--------------+------
 public | test_view | status | varchar(124) |  124
(1 row)


dbadmin=> select * from test_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)
  • Running the build_flextable_view() options:

    • Syntax:

    build_flextable_view('flex_table' [ [,'view_name'] [,'user_keys_table'] ])

    The function can receive 3 parameters :

    flex_table : will hold the flex table name.

    view_name : optionaly you can choose the view name of your choise.

    user_keys_table: optionaly you can nominate the keys(columns) that will paticipate in the view creation.

  • Run the build_flextable_view() function.
    • dbadmin=> select compute_flextable_keys_and_build_view('test');
                            compute_flextable_keys_and_build_view
----------------------------------------------------------------------------------------------
 Please see public.test_keys for updated keys
The view public.test_view is ready for querying
(1 row)

dbadmin=> dv public.test_view;
                       List of View Fields
 Schema |   View    |    Column     |        Type         | Size
--------+-----------+---------------+---------------------+------
 public | test_view | about         | varchar(1270)       | 1270
 public | test_view | address       | varchar(120)        |  120
 public | test_view | age           | varchar(20)         |   20
 public | test_view | balance       | varchar(20)         |   20
 public | test_view | company       | varchar(24)         |   24
 public | test_view | email         | varchar(66)         |   66
 public | test_view | favoritefruit | varchar(20)         |   20
 public | test_view | friends       | long varbinary(304) |  304
 public | test_view | gender        | varchar(20)         |   20
 public | test_view | greeting      | varchar(114)        |  114
 public | test_view | guid          | varchar(72)         |   72
 public | test_view | id            | varchar(20)         |   20
 public | test_view | isactive      | varchar(20)         |   20
 public | test_view | latitude      | varchar(20)         |   20
 public | test_view | longitude     | varchar(20)         |   20
 public | test_view | name          | varchar(40)         |   40
 public | test_view | phone         | varchar(34)         |   34
 public | test_view | picture       | varchar(50)         |   50
 public | test_view | registered    | varchar(52)         |   52
 public | test_view | tags          | long varbinary(288) |  288
(20 rows)

    The view is now created and ready to be used in queries or to me materialized.

  • Creating a specific view using non-default values:

    • Create view with diferent name:

    dbadmin=>  select build_flextable_view('test','test_custom');
-[ RECORD 1 ]--------+-------------------------------------------
build_flextable_view | The view test_custom is ready for querying

dbadmin=> dv test_custom
                        List of View Fields
 Schema |    View     |    Column     |        Type         | Size
--------+-------------+---------------+---------------------+------
 public | test_custom | about         | varchar(1270)       | 1270
 public | test_custom | address       | varchar(120)        |  120
 public | test_custom | age           | varchar(20)         |   20
 public | test_custom | balance       | varchar(20)         |   20
 public | test_custom | company       | varchar(24)         |   24
 public | test_custom | email         | varchar(66)         |   66
 public | test_custom | favoritefruit | varchar(20)         |   20
 public | test_custom | friends       | long varbinary(304) |  304
 public | test_custom | gender        | varchar(20)         |   20
 public | test_custom | greeting      | varchar(114)        |  114
 public | test_custom | guid          | varchar(72)         |   72
 public | test_custom | id            | varchar(20)         |   20
 public | test_custom | isactive      | varchar(20)         |   20
 public | test_custom | latitude      | varchar(20)         |   20
 public | test_custom | longitude     | varchar(20)         |   20
 public | test_custom | name          | varchar(40)         |   40
 public | test_custom | phone         | varchar(34)         |   34
 public | test_custom | picture       | varchar(50)         |   50
 public | test_custom | registered    | varchar(52)         |   52
 public | test_custom | tags          | long varbinary(288) |  288
(20 rows)

    Copyright 2020. All Rights Reserved