What’s New in Vertica 8.1: Flex Tables Enhancements

This blog post was authored by Soniya Shah.

As of Vertica 8.1, you can execute CTAS statements to create flex tables.

CREATE TABLE AS (CTAS) statement

Previously, Vertica supported creating tables using the AS SELECT clause. Frequently called CTAS, this SQL statement lets you create a new table that contains the results from querying another table. However, in previous Vertica versions, CTAS was not supported with flex tables.

With Vertica 8.1, you can create flex tables with CTAS in a statement such as: => CREATE FLEX TABLE nationalparks AS SELECT * FROM unitedstatesparks;

Overview

The CREATE FLEX TABLE AS statement allows you to create a flex table from the results of a query. This query may involve regular tables or other flex tables.

With flex CTAS, you can create a pure flex table or a hybrid flex table. A pure flex table is a table with no materialized columns, i.e. a table with only virtual columns, while a hybrid flex table has real columns. Hybrid flex tables may materialize some or all query columns.

No materialized columns

To create a pure flex table, make sure your new table name is followed by empty parentheses. => CREATE FLEX TABLE snacks() AS SELECT name, type, calories FROM inventory; This creates a flex table called snacks from the inventory table with the columns name, type, and calories. None of these columns are materialized.

Some materialized columns

To create a hybrid flex table with some materialized columns, define the flex table with the column names to materialize: => CREATE FLEX TABLE snacks(inventory_name, inventory_type) AS SELECT name, type, calories FROM inventory; In this case, the flex table snacks has two materialized columns that we defined, and one virtual column named calories. The value of the calories column from the inventory table also exists in snacks, even though the column is not materialized. You can query a flex table’s materialized and non-materialized (virtual) columns without knowing the details of how they are physically stored. The server will transparently fetch the data for you.

If you query the following statement, Vertica returns the column and its values: => SELECT calories FROM snacks;

All materialized columns

To create a hybrid flex table with all materialized columns, omit parentheses from the query. => CREATE FLEX TABLE snacks AS SELECT name, type, calories FROM inventory; This time, the flex table snacks has the specified columns materialized.

For more information on using the CTAS statement for flex tables in 8.1, check out Creating Flex Tables.