这是本节的多页打印视图。点击此处打印.

Flex 表

1: 入门
2: 了解 Flex 表
3: 创建 Flex 表
4: 将数据批量加载到 Flex 表中
5: 将数据插入 Flex 表
6: 将 Flex 表用于 IDOL 数据
7: 使用 Flex 表解析器

7.1: 使用 Flex 解析器加载列式表
7.2: 加载 CSV 数据
7.3: 加载分隔的数据

8: 计算 Flex 表键
9: 实体化 Flex 表
10: 更新 Flex 表视图
11: 查询 Flex 表
12: 查询嵌套数据
13: 查询 Flex 视图
14: 列出 Flex 表

Flex 表是一种不同类型的数据库表，专为加载和查询中的非结构化数据（又称半结构化数据）而设计。Flex 表允许您将具有不同架构或不断发展的架构的数据加载到单个表中。然后，您可以浏览这些数据并从中实现实际列。

如果您知道数据中的某些列，则可以定义它们。具有实际列的 Flex 表称为混合表。Flex 表和混合表均为受到全面支持的表，存储为投影，并具有与您的数据库相同的 K-safety。

创建 Flex 表后，无需指定架构即可快速加载数据。这允许您加载任意 JSON 数据、日志文件和其他半结构化数据并立即开始查询。

创建 Flex 表与创建其他表类似，只不过列定义是可选的。创建 Flex 表时，无论该表有没有列定义，Vertica 都会将一个名为 __raw__ 的实际列隐式添加到表中。此列存储所加载的数据。__raw__ 列为带有 NOT NULL 约束的 LONG VARBINARY 列。其中包含适用于其所属数据类型的已记录限制（请参阅长数据类型）。__raw__ 列的默认最大宽度为 130,000 字节（具有 32,000,000 字节的绝对最大值）。您可以借助 FlexTableRawSize 配置参数更改默认宽度。

如果您创建没有其他列定义的 Flex 表，该表包含第二个默认列 __identity__，声明为自动递增的 IDENTITY (1,1) 列。如果没有定义其他列，Flex 表将 __identity__ 列用于分段和排序。

将数据加载到 Flex 表中可将记录编码为 VMap 类型并填充 __raw__ 列。VMap 属于标准的字典类型，以虚拟列的形式将键与字符串值配对。

Flex 表术语

Flex 表具有以下特殊词汇：

VMap：内部映射数据格式。
虚拟列：Flex 表 __raw__ 列中包含的键值对。
实际列：除了 flex (VMap) 列之外定义的显式列。
已提升列：已被实体化为实际列的虚拟列。
映射键：VMap 数据中的虚拟列名。

1 - 入门

以下教程演示了创建、浏览和使用 Flex 表的基本信息。

将这些 JSON 数据用于本节其余部分的练习：

{"name": "Everest", "type":"mountain", "height":29029, "hike_safety": 34.1}
{"name": "Mt St Helens", "type":"volcano", "height":29029, "hike_safety": 15.4}
{"name": "Denali", "type":"mountain", "height":17000, "hike_safety": 12.2}
{"name": "Kilimanjaro", "type":"mountain", "height":14000 }
{"name": "Mt Washington", "type":"mountain", "hike_safety": 50.6}

创建 Flex 表并加载数据

创建名为 mountains 的 Flex 表：
```
=> CREATE flex table mountains();
```

使用 Flex 表解析器 fjsonparser 加载您保存的 JSON 文件：

=> COPY mountains from '/home/dbadmin/data/flex/mountains.json'
parser fjsonparser();
 Rows Loaded
-------------
           5
(1 row)

查询示例文件中的值：

=> SELECT name, type, height from mountains;
     name      |   type   | height
---------------+----------+--------
 Everest       | mountain | 29029
 Mt St Helens  | volcano  | 29029
 Denali        | mountain | 17000
 Kilimanjaro   | mountain | 14000
 Mt Washington | mountain |
(5 rows)

现在，您已经创建一个 Flex 表并加载数据。接下来，了解更多有关在数据库中使用 Flex 表数据的信息。

构建 Flex 表视图

使用 build_flextable_view() 函数填充从 mountains_keys 表生成的视图。

=> SELECT build_flextable_view('mountains');
                 build_flextable_view
------------------------------------------------------
 The view public.mountains_view is ready for querying
(1 row)

查询视图 mountains_view：


=> SELECT * from public.mountains_view;
 hike_safety |     name      |   type   | height
-------------+---------------+----------+--------
 50.6        | Mt Washington | mountain |
 34.1        | Everest       | mountain | 29029
 22.8        | Kilimanjaro   | mountain | 14000
 15.4        | Mt St Helens  | volcano  | 29029
 12.2        | Denali        | mountain | 17000
(5 rows)

使用 view_columns 系统表查询 mountains_view 的 column_name 列和 data_type 列：

=> SELECT column_name, data_type from view_columns where table_name = 'mountains_view';
 column_name |  data_type
-------------+-------------
 hike_safety | varchar(20)
 name        | varchar(26)
 type        | varchar(20)
 height      | varchar(20)
(4 rows)

审阅查询结果：
- 请注意 data_type 列及其值和大小。这些均为在您使用 compute_flextable_keys() 计算 Flex 表键时计算得出。
- 您在调用该函数后查询 mountains_keys 表时，是否还注意到 data_type_guess 列？

使用来自 mountains_view 的 data_type 信息，覆盖 hike_safety 的 data_type_guess。然后，COMMIT 更改，并使用 build_flextable_view() 重新构建视图：

=> UPDATE mountains_keys SET data_type_guess = 'float' where key_name = 'hike_safety';
 OUTPUT
--------
      1
(1 row)

=> commit;
=> SELECT build_flextable_view('mountains');
                 build_flextable_view
------------------------------------------------------
 The view public.mountains_view is ready for querying
(1 row)

接下来，使用 view_columns 系统表。请注意，hike_safety 现在属于 float 数据类型：

=> SELECT column_name, data_type from view_columns where table_name = 'mountains_view';
 column_name |  data_type
-------------+-------------
 hike_safety | float
 name        | varchar(26)
 type        | varchar(20)
 height      | varchar(20)
(4 rows)

创建混合 Flex 表

如果您已经知道您定期加载和查询的部分数据需要全面的 Vertica 性能和支持，您可以创建一个混合 Flex 表。混合 Flex 表具有您定义的一个或多个实际列，以及一个用于存储您加载的所有非结构化数据的 __raw__ 列。查询实际列比查询 raw 列中的可变数据更快。您可以定义各列的默认值。

创建一个混合 Flex 表，并加载相同的示例 JSON 文件：

=> CREATE flex table mountains_hybrid(name varchar(41) default name::varchar(41), hike_safety float
default hike_safety::float);
=> COPY mountains_hybrid from '/home/dbadmin/Downloads/mountains.json' parser fjsonparser();
 Rows Loaded
-------------
           5
(1 row)

使用 compute_flextable_keys_and_build_view() 函数为 mountains_hybrid 填充键表并构建视图：

=> SELECT compute_flextable_keys_and_build_view('mountains_hybrid');
                                        compute_flextable_keys_and_build_view
------------------------------------------------------------------------------------------------
 Please see public.mountains_hybrid_keys for updated keys
The view public.mountains_hybrid_view is ready for querying
(1 row)

查询 mountains_hybrid 键表。再次审阅 data_type_guesses 列的值。类型列出了您在创建混合表时声明的列定义：

=> SELECT * from mountains_hybrid_keys;
  key_name   | frequency | data_type_guess
-------------+-----------+-----------------
 height      |         4 | varchar(20)
 name        |         5 | varchar(41)
 type        |         5 | varchar(20)
 hike_safety |         4 | float
(4 rows)

如果您创建了一个基本 Flex 表，随后发现您希望将一个或多个虚拟列提升为实际列，请参阅实体化 Flex 表以添加列。

实体化混合 Flex 表中的虚拟列

浏览 Flex 表数据后，您可以将 Flex 表中的一个或多个虚拟列提升为实际列。您无需创建单独的列式表。

在混合表上调用 materialize_flextable_columns() 函数，指定要实体化的虚拟列数量：

=> SELECT materialize_flextable_columns('mountains_hybrid', 3);
                                 materialize_flextable_columns
--------------------------------------------------------------------------------------------
 The following columns were added to the table public.mountains_hybrid:
        type
For more details, run the following query:
SELECT * FROM v_catalog.materialize_flextable_columns_results WHERE
table_schema = 'public' and table_name = 'mountains_hybrid';

(1 row)

您指定要实体化三 (3) 列，但该表是使用两个实际列（name 和 hike_safety）创建的。为了满足您指定三列的愿望，该函数仅再提升另外一列 type。下一个示例已展开显示，以垂直列出各列。请注意，刚实体化的列处于 ADDED 状态，而不是您在创建该表时所定义的两列所处的 EXISTS 状态：

=> \x
Expanded display is on.
=> SELECT * from materialize_flextable_columns_results where table_name = 'mountains_hybrid';
-[ RECORD 1 ]-+-------------------------------------------------------
table_id      | 45035996273766044
table_schema  | public
table_name    | mountains_hybrid
creation_time | 2013-11-30 20:09:37.765257-05
key_name      | type
status        | ADDED
message       | Added successfully
-[ RECORD 2 ]-+-------------------------------------------------------
table_id      | 45035996273766044
table_schema  | public
table_name    | mountains_hybrid
creation_time | 2013-11-30 20:09:37.765284-05
key_name      | hike_safety
status        | EXISTS
message       | Column of same name already exists in table definition
-[ RECORD 3 ]-+-------------------------------------------------------
table_id      | 45035996273766044
table_schema  | public
table_name    | mountains_hybrid
creation_time | 2013-11-30 20:09:37.765296-05
key_name      | name
status        | EXISTS
message       | Column of same name already exists in table definition

现在，显示混合表定义，列出 __raw__ 列和三个已实体化的列。Flex 表的数据类型由相关联的键表派生而来，因此您可以在必要时对其进行更新。请注意，__raw__ 列具有默认的 NOT NULL 约束：

=> \d mountains_hybrid
List of Fields by Tables
-[ RECORD 1 ]------------------------------------------------------------
Schema      | public
Table       | mountains_hybrid
Column      | __raw__
Type        | long varbinary(130000)
Size        | 130000
Default     |
Not Null    | t
Primary Key | f
Foreign Key |
-[ RECORD 2 ]------------------------------------------------------------
Schema      | public
Table       | mountains_hybrid
Column      | name
Type        | varchar(41)
Size        | 41
Default     | (MapLookup(mountains_hybrid.__raw__, 'name'))::varchar(41)
Not Null    | f
Primary Key | f
Foreign Key |
-[ RECORD 3 ]------------------------------------------------------------
Schema      | public
Table       | mountains_hybrid
Column      | hike_safety
Type        | float
Size        | 8
Default     | (MapLookup(mountains_hybrid.__raw__, 'hike_safety'))::float
Not Null    | f
Primary Key | f
Foreign Key |
-[ RECORD 4 ]------------------------------------------------------------
Schema      | public
Table       | mountains_hybrid
Column      | type
Type        | varchar(20)
Size        | 20
Default     | (MapLookup(mountains_hybrid.__raw__, 'type'))::varchar(20)
Not Null    | f
Primary Key | f
Foreign Key |

您现在已经完成有关 Flex 表基本信息、混合 Flex 表和 Flex 函数运用的入门指南。

2 - 了解 Flex 表

非结构化数据一词（有时称为半结构化数据或黑暗数据）并不表示您加载到 Flex 表中的数据完全没有结构。但是，您可能不知道数据的构成或其设计的不一致。在某些情况下，数据可能不相关。

您的数据可能具有一些结构（如 JSON 和分隔数据）。数据可能是半结构化或严格结构化的，但其结构却采用您不了解或不期望的方式建立。可变数据一词涵盖了上述和其他类型的数据。您可以将可变数据直接加载到 Flex 表中，然后查询其内容。

总之，您可以在不知道数据结构的情况下先加载数据，然后在经过几次简单的转换后查询其内容。在某些情况下，您已经知道数据结构，比如数据中的一些键。如果是这样，只要加载了数据，您就可以显式查询这些值。

存储 Flex 表数据

虽然您可以将非结构化数据存储在 Flex 表 __raw__ 列中，该列将作为实际列实施。

Vertica 将 __raw__ 列数据压缩至大约一半 (1/2)。虽然此因数小于实际列的压缩率，却可以显著减少大量（1TB 以上）非结构化数据。压缩完成后，Vertica 将数据写入到磁盘。如果节点出现故障，这种方法可在群集中保持 K-safety 并支持标准恢复流程。Flex 表会包含在完整备份中（或者按照您的选择包含在对象级备份中）。

创建 Flex 表时会发生什么？

每当您执行 CREATE FLEX TABLE 语句时，Vertica 都会创建三个对象，如下所示：

Flex 表 (flex_table)
相关联的键表 (flex_table_keys)
主表的默认视图 (flex_table_view)

_keys 和 _view 对象为父项 flex_table 的依赖项。删除 Flex 表将同时删除其依赖项，不过您可以单独删除 _keys 或 _view 对象。

您可以在不指定任何列定义的情况下创建 Flex 表。当您这样做时，Vertica 将自动创建两个表，分别为指定的 Flex 表（如 darkdata）和与其相关联的键表 darkdata_keys：

=> CREATE flex table darkdata();
CREATE TABLE
=> \dt dark*
                List of tables
 Schema |     Name      | Kind  |  Owner  | Comment
--------+---------------+-------+---------+---------
 public | darkdata      | table | dbadmin |
 public | darkdata_keys | table | dbadmin |
(2 rows)

每个 Flex 表具有两个默认列，分别为 __raw__ 和 __identity__。__raw__ 列存在于每个 Flex 表中，旨在存储您加载的数据。__identity__ 列自动递增。如果不存在其他列定义，Vertica 将 __identity__ 列用于分段和排序。Flex 键表包含三列：

=> SELECT * FROM darkdata_keys;
 key_name | frequency | data_type_guess
----------+-----------+-----------------
(0 rows)

如果您在创建 Flex 表时定义列，Vertica 仍会创建 __raw__ 列。但该表没有 __identity__ 列，因为各列被指定用于分段和排序。如以下示例所示，将自动创建两个表：

=> CREATE FLEX TABLE darkdata1 (name VARCHAR);
CREATE TABLE

=> SELECT * FROM darkdata1;
 __raw__ | name
---------+------
(0 rows)

=> \d darkdata1*
                                            List of Fields by Tables
 Schema |   Table   | Column  |          Type          |  Size  | Default | Not Null | Primary Key | Foreign Key
--------+-----------+---------+------------------------+--------+---------+----------+-------------+-------------
 public | darkdata1 | __raw__ | long varbinary(130000) | 130000 |         | t        | f           |
 public | darkdata1 | name    | varchar(80)            |     80 |         | f        | f           |
(2 rows)

=> \dt darkdata1*
                   List of tables
 Schema |      Name      | Kind  |  Owner  | Comment
--------+----------------+-------+---------+---------
 public | darkdata1      | table | dbadmin |
 public | darkdata1_keys | table | dbadmin |
(2 rows)

有关更多示例，请参阅创建 Flex 表。

自动创建超投影

除了为各 Flex 表创建两个表以外，Vertica 还为主 Flex 表及其关联键表创建超投影。使用 \dj 命令时，您可以在这组示例中显示自动为 darkdata 表和 darkdata1 表创建的投影：

=> \dj darkdata*
                           List of projections
 Schema |          Name           |  Owner  |       Node       | Comment
--------+-------------------------+---------+------------------+---------
 public | darkdata1_b0            | dbadmin |                  |
 public | darkdata1_b1            | dbadmin |                  |
 public | darkdata1_keys_super    | dbadmin | v_vmart_node0001 |
 public | darkdata1_keys_super    | dbadmin | v_vmart_node0003 |
 public | darkdata1_keys_super    | dbadmin | v_vmart_node0004 |
 public | darkdata_b0             | dbadmin |                  |
 public | darkdata_b1             | dbadmin |                  |
 public | darkdata_keys__super    | dbadmin | v_vmart_node0001 |
 public | darkdata_keys_super     | dbadmin | v_vmart_node0003 |
 public | darkdata_keys_super     | dbadmin | v_vmart_node0004 |
(10 rows)

默认 Flex 表视图

创建 Flex 表时，还将同时创建一个默认视图。此默认视图使用带有 _view 后缀的表名。如果查询默认视图，系统会提示您使用 COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW 函数。此视图可让您在加载数据后更新视图，从而使其包含所有的键和值。

=> \dv darkdata*
                  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)

有关详细信息，请参阅更新 Flex 表视图。

Flex 函数

三组函数支持 Flex 表并将数据提取到 VMap 中：

使用带 Flex 表的客户端

您可以按照如下方式将 Vertica 支持的客户端驱动程序与 Flex 表配合使用：

要将数据加载到 Flex 表中，您可以将 INSERT 或 COPY LOCAL 语句与适当的 Flex 解析器一起使用。
驱动程序元数据 API 仅返回实际列。例如，如果您从具有单个实体化列的 Flex 表中选择 *，则该语句将返回该列和 __raw__。但不会返回 __raw__ 内的虚拟列。要访问虚拟列和它们的值，请查询相关联的 _keys 表，就像您在 vsql 中执行查询一样。

3 - 创建 Flex 表

您无需列定义或其他参数即可创建 Flex 表或外部 Flex 表。您可以像往常一样使用您喜欢的任何 CREATE TABLE 语句参数。

创建基本 Flex 表

创建该表的方法如下：

=> CREATE FLEX TABLE darkdata();
CREATE TABLE

在将任何数据加载到表中之前从表中进行选择将显示其两个实际列 — __identity__ 和 __raw__：

=> SELECT * FROM darkdata;
__identity__    | __raw__
--------------+---------
(0 rows)

以下为一个用列定义创建 Flex 表的示例：

=> CREATE FLEX TABLE darkdata1(name VARCHAR);
CREATE TABLE

当 Flex 表存在时，您可以按照实体化 Flex 表中所述，添加新的列（包括那些具有默认派生表达式的列）。

实体化 Flex 表虚拟列

创建 Flex 表并加载数据后，您可以从虚拟列中计算出键。完成这些任务后，您可以通过将虚拟列提升为实际表列，而将某些键实体化。提升虚拟列使您可以查询实际列，而不是原始数据。

您可以提升一个或多个虚拟列 — 从而将那些键从 __raw__ 数据内部实体化到实际列。Vertica 建议使用此方法来获取所有重要键的最佳查询性能。您无需从 Flex 表创建新的列式表。

实体化 Flex 表列将导致出现混合表。混合表：

可保留 Flex 表加载非结构化数据时的便利性
可提高任何实际列的查询性能

如果您仅有少量几列可以实体化，请尝试逐步更改 Flex 表，并在必要时添加列。您可以使用 ALTER TABLE...ADD COLUMN 语句执行此操作，就像您将对列式表执行此操作一样。请参阅实体化 Flex 表，了解有关添加列的技巧。

如果您希望自动将列实体化，请使用 Helper 函数 MATERIALIZE_FLEXTABLE_COLUMNS。

从 Flex 表创建列式表

您可以从 Flex 表创建常规 Vertica 表，但您不能使用一个 Flex 表创建另一个 Flex 表。

通常情况下，您在加载数据后从 Flex 表创建列式表。然后，您可以指定您希望出现在常规表中的虚拟列数据，将虚拟列转换成常规数据类型。

要从 Flex 表 darkdata 创建列式表，请为新表选择两个虚拟列（user.lang 和 user.name）。使用针对新表将两个列均转换成 varchars 的如下命令：

=> CREATE TABLE darkdata_full AS SELECT "user.lang"::VARCHAR, "user.name"::VARCHAR FROM darkdata;
CREATE TABLE
=> SELECT * FROM darkdata_full;
 user.lang |      user.name
-----------+---------------------
 en        | Frederick Danjou
 en        | The End
 en        | Uptown gentleman.
 en        | ~G A B R I E L A â¿
 es        | Flu Beach
 es        | I'm Toasterâ¥
 it        | laughing at clouds.
 tr        | seydo shi
           |
           |
           |
           |
(12 rows)

创建临时 Flex 表

在创建临时全局和本地 Flex 表之前，请了解以下注意事项：

支持全局临时 (GLOBAL TEMP) Flex 表。创建临时全局 Flex 表将导致 flextable_keys 表和 flextable_view 具有针对其内容的临时表限制。
本地临时 (LOCAL TEMP) Flex 表必须包括至少一个列定义。做此要求的原因是，本地临时表不支持自动递增数据（如 Flex 表默认的 __identity__ 列）。创建临时本地 Flex 表将导致 flextable_keys 表和 flextable_view 存在于本地临时对象范围中。
支持 Flex 和列式临时表的本地临时 (LOCAL TEMP) 视图。

要想全局或本地临时 Flex 表正常工作，您还必须指定 ON COMMIT PRESERVE ROWS 子句。您必须将 ON COMMIT 子句用于依靠提交的 Flex 表 Helper 函数。按照如下方式创建本地临时表：

=> CREATE FLEX LOCAL TEMP TABLE good(x int) ON COMMIT PRESERVE ROWS;
CREATE TABLE

使用此方法创建本地临时 Flex 表后，即可将数据加载到表中，创建表键和 Flex 表视图：

=> COPY good FROM '/home/release/KData/bake.json' PARSER fjsonparser();
 Rows Loaded
-------------
           1
(1 row)

=> select compute_flextable_keys_and_build_view('good');
                            compute_flextable_keys_and_build_view
----------------------------------------------------------------------------------------------
 Please see v_temp_schema.good_keys for updated keys
The view good_view is ready for querying
(1 row)

同样地，您可以按照如下方式创建全局临时表：

=> CREATE FLEX GLOBAL TEMP TABLE good_global(x int) ON COMMIT PRESERVE ROWS;

使用此方法创建全局临时 Flex 表后，即可将数据加载到表中，创建表键和 Flex 表视图：

=> COPY good_global FROM '/home/dbadmin/data/flex/bake_single.json' PARSER fjsonparser();
Rows Loaded
-------------
5
(1 row)

=> SELECT compute_flextable_keys_and_build_view('good_global');
                            compute_flextable_keys_and_build_view
----------------------------------------------------------------------------------------------
 Please see v_temp_schema.good_keys for updated keys
The view good_view is ready for querying
(1 row)

创建外部 Flex 表

要创建外部 Flex 表：

=> CREATE flex external table mountains() AS COPY FROM 'home/release/KData/kmm_ountains.json' PARSER fjsonparser();
CREATE TABLE

与其他 Flex 表一样，创建外部 Flex 表会生成两个常规表：命名表及其关联的 _keys 表。键表不是外部表：

=> \dt mountains
                 List of tables
 Schema |   Name    | Kind  |  Owner  | Comment
--------+-----------+-------+---------+---------
 public | mountains | table | release |
(1 row)

可以使用帮助程序函数 COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW 计算键并创建外部表视图：

=> SELECT compute_flextable_keys_and_build_view ('appLog');

                     compute_flextable_keys_and_build_view
--------------------------------------------------------------------------------------------------
Please see public.appLog_keys for updated keys
The view public.appLog_view is ready for querying
(1 row)

检查 _keys 表中的键，以获取帮助应用程序的运行结果：

=> SELECT * FROM appLog_keys;
                          key_name                       | frequency |   data_type_guess
----------------------------------------------------------+-----------+------------------
contributors                                             |         8 | varchar(20)
coordinates                                              |         8 | varchar(20)
created_at                                               |         8 | varchar(60)
entities.hashtags                                        |         8 | long varbinary(186)
.
.
.
retweeted_status.user.time_zone                          |         1 | varchar(20)
retweeted_status.user.url                                |         1 | varchar(68)
retweeted_status.user.utc_offset                         |         1 | varchar(20)
retweeted_status.user.verified                           |         1 | varchar(20)
(125 rows)

您可以查询视图：

=> SELECT "user.lang" FROM appLog_view;

 user.lang
-----------
it
en
es
en
en
es
tr
en
(12 rows)

注意

Flex 表和列式表的外部表受到全面支持。但使用外部 Flex（或列式）表不如使用数据存储在 Vertica 数据库中的 Flex 表效率高。每当您执行查询时，都需要重新加载在外部维护的数据。

从查询结果创建 Flex 表

您可以使用 CREATE FLEX TABLE AS 语句从查询结果创建 Flex 表。

您可以使用此语句创建三种类型的 Flex 表：

没有实体化列的 Flex 表
带有一些实体化列的 Flex 表
带有所有实体化列的 Flex 表

当 CTAS 查询中存在 Flex __raw__ 列时，整个源 VMap 将被传送到 Flex 表中。如果查询具有匹配的列名，则键值将被覆盖。

注意

ORDER BY 和分段子句仅在相关列实体化时才传递给新的 Flex 表。

示例

从常规表创建没有实体化列的 Flex 表会导致查询结果作为 VMap 存储在 __raw__ 列中。

创建名为 pets 的包含两列的常规表：

=> CREATE TABLE pets(age INT, name VARCHAR);
CREATE TABLE

通过使用 CTAS 语句从 pets 复制列 age 和 name，创建名为 family_pets 的 Flex 表：
```
=> CREATE FLEX TABLE family_pets() AS SELECT age, name FROM pets;
CREATE TABLE
```

查看新的 Flex 表以确认操作已成功，并且列 age 和 name 尚未实体化。

=> \d family_pets;
    List of Fields by Tables
Schema | Table       |    Column    |          Type          |  Size  | Default | Not Null | Primary Key | Foreign Key
--------+------------+--------------+------------------------+--------+---------+----------+-------------+-------------
public | family_pets | __identity__ | int                    |      8 |         | t        | f           |
public | family_pets | __raw__      | long varbinary(130000) | 130000 |         | t        | f           |
(2 rows)

您可以从另一个 Flex 表的查询结果中创建一个没有实体化列的 Flex 表。这会将源 Flex 表中的所有 VMap 插入到目标中。这将创建一个按 __identity__ 列分段和排序的 Flex 表。

通过使用 CTAS 语句从 family_pets 复制 age 和 __raw__ 列，创建名为 city_pets 的 Flex 表：
```
=> CREATE FLEX TABLE city_pets() AS SELECT age, __raw__ FROM family_pets;
CREATE TABLE
```

查看新的 Flex 表以确认操作已成功，并且列 age 和 __raw__ 尚未实体化。

=> SELECT * FROM city_pets;
    List of Fields by Tables
Schema | Table     |    Column    |          Type          |  Size  | Default | Not Null | Primary Key | Foreign Key
--------+----------+--------------+------------------------+--------+---------+----------+-------------+-------------
public | city_pets | __identity__ | int                    |      8 |         | t        | f           |
public | city_pets | __raw__      | long varbinary(130000) | 130000 |         | t        | f           |
(2 rows)

您可以创建包含一些实体化列的 Flex 表。使用的语法类似于用于创建具有一些实体化列的列式表的语法。但是，与列式表不同，您需要将列数与查询返回的列相匹配。在以下示例中，查询返回三列（amount、type 和 available），但 Vertica 仅实体化前两列。

创建名为 animals 的包含三列（amount、type 和 available）的表：
```
=> CREATE TABLE animals(amount INT, type VARCHAR, available DATE);
```
通过使用 CTAS 语句从 animals 复制 amount、type 和 available 列，创建名为 inventory 的包含 animal_amount 和 animal_type 列的 Flex 表：
```
=> CREATE FLEX TABLE inventory(animal_amount, animal_type) AS SELECT amount, type, available FROM animals;
CREATE TABLE
```

查看表数据以确认 amount 和 type 列已在列名称 animal_amount 和 animal_type 下实体化。animals 中的列 available 也已被复制，但未实体化：

=> \d inventory
    List of Fields by Tables
Schema | Table | Column           |          Type          |  Size  | Default | Not Null | Primary Key | Foreign Key
--------+-------+-----------------+------------------------+--------+---------+---------+-------------+-------------
public | flex3 | __raw__          | long varbinary(130000) | 130000 |         | t       | f           |
public | flex3 | animal_amount    | int                    |      8 |         | f       | f           |
public | flex3 | animal_type      | varchar(80)            |     80 |         | f       | f           |
(3 rows)

请注意，在语句中包含空括号会生成一个没有实体化列的 Flex 表：

使用带空括号的 CTAS 语句创建名为 animals_for_sale 的 Flex 表，以将列 amount、type 和 available 从 animals 复制到纯 Flex 表：
```
=> CREATE FLEX TABLE animals__for_sale() AS SELECT amount, type, available FROM animals;
CREATE TABLE
```

查看表数据以确认没有任何列实体化：

=>\d animals_for_sale;
    List of Fields by Tables
Schema | Table            |    Column    |          Type          |  Size  | Default | Not Null | Primary Key | Foreign Key
--------+-----------------+--------------+------------------------+--------+---------+----------+------------+-------------
public | animals_for_sale | __identity__ | int                    |      8 |         | t        | f          |
public | animals_for_sale | __raw__      | long varbinary(130000) | 130000 |         | t        | f          |

(2 rows)

在语句中省略任何括号会导致所有列均实体化：

使用不带括号的 CTAS 语句创建名为 animals_sold 的 Flex 表。这会从 animals 复制列 amount、type 和 available 并实体化所有列：
```
=> CREATE FLEX TABLE animals_sold AS SELECT amount, type, available FROM animals;
CREATE TABLE
```

查看表数据以确认所有列均实体化：

=> \d animals_sold;
    List of Fields by Tables
Schema | Table        | Column    |          Type          |  Size  | Default | Not Null | Primary Key | Foreign Key
--------+-------------+-----------+------------------------+--------+---------+----------+-------------+-------------
public | animals_sold | __raw__   | long varbinary(130000) | 130000  |         | t        | f           |
public | animals_sold | amount    | int                    |      8  |         | f        | f           |
public | animals_sold | type      | varchar(80)            |     80  |         | f        | f           |
public | animals_sold | available | date                   |      8  |         | f        | f           |
(4 rows)

4 - 将数据批量加载到 Flex 表中

您可以指定下列 Flex 解析器之一，并使用 COPY 语句将数据批量加载到 Flex 表中：

FAVROPARSER
FCEFPARSER
FCSVPARSER
FDELIMITEDPAIRPARSER
FDELIMITEDPARSER
FJSONPARSER
FREGEXPARSER

所有 Flex 解析器均将数据存储为单值 VMap。它们驻留在 VARBINARY__raw__ 列，该列为具有 NOT NULL 约束的实际列。VMap 编码为单一二进制值，以便存储在 __raw__ 列中。编码将值字符串置于连续区块中，后面是键字符串。Vertica 针对带 NULL 指定列的键，支持 VMap 内的 NULL 值。键和值字符串分别代表 Flex 表中的虚拟列和它们的值。

如果 Flex 表数据行太大而无法适应 __raw__ 列，该数据行将被拒绝。默认情况下，被拒数据和异常文件存储在标准 CopyErrorLogs 位置，这是编录目录的一个子目录：

v_mart_node003_catalog/CopyErrorLogs/trans-STDIN-copy-from-exceptions.1
v_mart_node003_catalog/CopyErrorLogs/trans-STDIN-copy-rejections.1

出于对磁盘空间的考虑，Flex 表不会复制任何被拒数据。被拒数据文件存在，但对于每个被拒记录，仅包含一个换行符。相应的异常文件列出了每个记录被拒绝的原因。

您可以为被拒数据文件和异常文件指定一个不同的路径和文件。为此，请分别使用 COPY 选项 REJECTED DATA 和 EXCEPTIONS。您还可以将加载拒绝和异常情况保存在表中。有关详细信息，请参阅数据加载。

基本 Flex 表加载和查询

将数据加载到 Flex 表中与将数据加载到常规列式表中类似。差别在于，您必须在使用其中一种 Flex 解析器的同时使用 PARSER 选项：

=> COPY darkdata FROM '/home/dbadmin/data/tweets_12.json' PARSER fjsonparser();
 Rows Loaded
-------------
          12
(1 row)

注意

您可以在需要时使用许多额外的 COPY 参数，但并非所有这些参数都受到支持。

将数据加载到 Flex 表的实际列

如果您创建具有一个或多个实际列定义的混合 Flex 表，则 COPY 在数据加载期间评估每个虚拟列键名，并使用来自对应虚拟列的值自动填充实际列。对于标量类型的列，COPY 还将键和值作为 VMap 数据的一部分加载到 __raw__ 列。对于复杂类型的列，COPY 不会将值添加到 __raw__ 列。

注意

随着时间的推移，在两种类型的列存储值可能会影响您的许可数据限制。有关 Vertica 许可证的详细信息，请参阅管理许可证。

例如，继续使用 JSON 示例：

创建 Flex 表，该表具有待加载数据中一个键的列定义：
```
=> CREATE FLEX TABLE darkdata1 ("user.lang" VARCHAR);
CREATE TABLE
```

将数据加载到表中：

=> COPY darkdata1 FROM '/test/vertica/flextable/DATA/tweets_12.json' PARSER fjsonparser();
 Rows Loaded
-------------
          12
(1 row)

直接查询实际列：
```
=> SELECT "user.lang" FROM darkdata1;
 user.lang
-----------
 es
 es
 tr
 it
 en
 en
 en
 en
(12 rows)
```
实体化列中的空行指示 NULL 值。有关如何在 Flex 表中处理 NULL 值的详细信息，请参阅 NULL 值。
您可以查询其他具有类似结果的虚拟列：
```
=> SELECT "user.name" FROM darkdata1;
      user.name
---------------------
 I'm Toasterâ¥
 Flu Beach
 seydo shi
 The End
 Uptown gentleman.
 ~G A B R I E L A â¿
 Frederick Danjou
 laughing at clouds.
(12 rows)
```
注意
虽然这两个查询的结果相似，但在访问键和键值方面，差异非常显著。user.lang 的数据已实体化为实际表列，而 user.name 仍为虚拟列。对于生产级的数据使用（而非测试数据集），实体化 Flex 表数据可显著提高查询性能。

在加载过程中处理默认值

您可以创建具有一个实际列的 Flex 表，该实际列以传入数据中存在的一个虚拟列命名。例如，如果您加载的数据包含一个 user.lang 虚拟列，请用该列定义 Flex 表。您还可以在创建具有实际列的 Flex 表时，指定默认列值。下个示例演示了如何定义具有来自虚拟列 (user.name) 的默认值的实际列 (user.lang)：

=> CREATE FLEX TABLE darkdata1 ("user.lang" LONG VARCHAR default "user.name");

将数据加载到 Flex 表中时，COPY 使用 Flex 表数据中的值，忽略默认列定义。要将数据加载到 Flex 表中，需要通过 MAPLOOKUP 找到与任何实际列名称相匹配的键。如果传入数据中具有名称与某个实际列相同的虚拟列，则存在匹配项。当 COPY 检测到匹配项时，它将用值填充该列。COPY 针对各行返回值或 NULL，因此实际列始终具有值。

例如，如前面的示例所述创建 Flex 表后，使用 COPY 加载数据：


=> COPY darkdata1 FROM '/test/vertica/flextable/DATA/tweets_12.json' PARSER fjsonparser();
 Rows Loaded
-------------
          12
(1 row)

如果您在加载后查询表，数据显示已提取 user.lang 列的值：

来自正在加载的数据 — user.lang 虚拟列的值
带 NULL — 没有值的行

在这种情况下，user.lang 的表列默认值被忽略：

=> SELECT "user.lang" FROM darkdata1;
 user.lang
-----------
 it
 en
 es
 en
 en
 es
 tr
 en
(12 rows)

使用 COPY 指定默认列值

您可以将表达式添加到 COPY 语句，以便在加载数据时指定默认列值。对于 Flex 表，如要指定任何列信息，您需要显示列出 __raw__ 列。以下示例演示了如何使用默认列值的表达式。在这种情况下，加载数据即可用输入数据 user.name 的值填充已定义的 user.lang 列：

=> COPY darkdata1(__raw__, "user.lang" as "user.name"::VARCHAR)
   FROM '/test/vertica/flextable/DATA/tweets_12.json' PARSER fjsonparser();
 Rows Loaded
-------------
          12
(1 row)
=> SELECT "user.lang" FROM darkdata1;
      user.lang
---------------------
 laughing at clouds.
 Avita Desai
 I'm Toasterâ¥
 Uptown gentleman.
 ~G A B R I E L A â¿
 Flu Beach
 seydo shi
 The End
(12 rows)

您可以按照更改 Flex 表中的说明，在添加列时指定默认值。执行此操作将导致不同的行为。有关使用 COPY、其表达式和参数的详细信息，请参阅数据加载和 COPY 参考页面。

5 - 将数据插入 Flex 表

您可以使用标准 INSERT 语句将数据加载到 Vertica Flex 表中，为一列或多列指定数据。当您使用 INSERT 时，Vertica 会填充所有实体化列并将 VMap 数据存储在 raw 列中。

Vertica 提供了两种将 INSERT 与 Flex 表一起使用的方法：

INSERT ... VALUES
INSERT ...SELECT

将值插入 Flex 表

要将数据值插入 Flex 表，请使用 INSERT ...VALUES 语句。如果您没有在 INSERT ...VALUES 语句中指定任何列，则 Vertica 按位置将值分配给 Flex 表的实际列。

此示例显示了将值插入简单 Flex 表的两种方法。对于这两个语句，Vertica 分别将值 1 和 'x' 分配给列 a 和 b。此示例将值插入到 Flex 表中定义的两个实际列：

=> CREATE FLEX TABLE flex0 (a INT, b VARCHAR);
CREATE TABLE
=> INSERT INTO flex0 VALUES (1, 'x');
 OUTPUT
--------
      1
(1 row)

此示例将值插入到没有任何实际列的 Flex 表中：

=> CREATE FLEX TABLE flex1();
 CREATE TABLE
=> INSERT INTO flex1(a,b) VALUES (1, 'x');
 OUTPUT
--------
      1
(1 row)

对于上一个示例，raw 列包含插入的数据：

=> SELECT MapToString(__raw__) FROM flex1;
           MapToString
---------------------------------
{
"a" : "1",
"b" : "x"
}
(1 row)

将 INSERT ...SELECT 与 Flex 表一起使用

将 INSERT ...SELECT 语句与 Flex 表一起使用类似于将 INSERT ...SELECT 与常规表一起使用。SELECT 语句返回要插入目标表的数据。

但是，Vertica 不要求您使列数和值数保持一致。如果您没有为列指定值，Vertica 将插入 NULL。

在下一个示例中，Vertica 从 flex1 表中复制 a 和 b 值，并创建列 c、d、e 和 f。由于该语句没有为 f 指定值，因此 Vertica 将其分配为 NULL。

=> CREATE FLEX TABLE flex2();
CREATE TABLE
=> INSERT INTO flex2(a,b) SELECT a,b, '2016-08-10 11:10' c, 'Hello' d, 3.1415 e, f from flex1;
 OUTPUT
--------
     1
(1 row)
=> SELECT MapToString(__raw__) FROM flex2;
                            MapToString
----------------------------------------------------------------------------------------------------------------
{
"a" : "1",
"b" : "x",
"c" : "2016-08-10 11:10",

"d" : "Hello",
"e" : "3.1415",
"f" : null
}
(1 row)

将 raw 列插入 Flex 表

将 raw 列插入 Flex 表会将整个源 VMap 插入到目标表中。Vertica 不会将 raw 列分配给任何目标列。它在 SELECT 语句中的位置无关紧要。

以下两个 INSERT 语句是等效的。

=> INSERT INTO flex4(a,b) SELECT a, __raw__, b FROM flex3;
=> INSERT INTO flex4(a,b) SELECT a, b, __raw__ FROM flex3;

错误处理

类型强制转换错误仅发生在实际列中。插入操作失败，如下所示：

=> CREATE FLEX TABLE my_table(a INT, b VARCHAR);
 CREATE TABLE
=> INSERT INTO my_table(a, b) VALUES ('xyz', '5');
ERROR: Invalid input syntax for integer: "xyz"

如果您尝试将值插入 raw 列，则插入失败，如下所示：

=> CREATE FLEX TABLE my_table(a INT, b VARCHAR);
 CREATE TABLE
=> INSERT INTO my_table(a,b,__raw__) VALUES (1,'x','abcdef');
ERROR 7372: Cannot assign value to "__raw__" column

另请参阅

6 - 将 Flex 表用于 IDOL 数据

您可以创建 Flex 表，将其用于 IDOL 连接器框架服务器 (CFS) 和 ODBC 客户端。CFS VerticaIndexer 模块使用连接器检索数据。然后，CFS 会将数据编制成您的 Vertica 数据库中的索引。

CFS 为许多连接器提供支持，使其能够连接存储库中存储的不同非结构化文件类型。存储库的示例包括 Microsoft Exchange（电子邮件）、文件系统（包括 Word 文档、图像和视频）、Microsoft SharePoint 以及 Twitter（包含推文）。

连接器检索并聚合存储库中的数据。CFS 为数据编制索引，将其发送值 IDOL、IDOL OnDemand 或 Vertica。下图说明了存储库和连接器的基本设置。

配置 CFS 并将其连接到您的 Vertica 数据库后，连接器将监视存储库是否对已加载文档进行更改和删除以及是否存在之前未添加到服务器的新文件。然后，CFS 将自动更新其服务器目标。

在 CFS 不断更新和删除的情况下，要获得最佳查询结果，Vertica 建议使用实时聚合投影和 top-K 投影。有关这些投影如何工作的详细信息和用例，请参阅投影。

适用于 CFS 的 ODBC 连接字符串

要设置 CFS VerticaIndexer 以便将 IDOL 元数据加载到数据库中，需要几个步骤。

首先，要将信息添加到 CFS 配置文件。要实现这一目标，请将一个 Indexing 段添加到指定 ODBC ConnectionString 详细信息的配置文件。

要成功加载数据，有效数据库用户必须具有对目标表的写入权限。两个 ODBC 连接参数（UID 和 PWD）分别指定 Vertica 用户和密码。以下示例显示了一个示例 CFS Indexing段。该段包含一个带基本参数的 ConnectionString，而基本参数包括示例用户 (UID=fjones) 和密码 (PWD=fjones_password)：

[Indexing]
IndexerSections=vertica
IndexTimeInterval=30

[vertica]
IndexerType = Library
ConnectionString=Driver=Vertica;Server=123.456.478.900;Database=myDB;UID=fjones;PWD=fjones_password
TableName = marcomm.myFlexTable
LibraryDirectory = ./shared_library_indexers
LibraryName = verticaIndexer

有关 ODBC 连接参数的详细信息，请参阅 [ODBC 配置参数。](http://vertica.com/docs/7.1.x/HTML/index.htm#Authoring/ConnectingToVertica/ClientODBC/DSNParameters.htm?TocPath=Connecting to HP Vertica|Client Libraries|Creating an ODBC Data Source Name (DSN)|_____4)

CFS COPY LOCAL 语句

CFS 首先为要添加到数据库的文档存储库中的元数据编制索引并对其进行处理。然后，CFS 使用您添加到配置文件的索引信息创建 ODBC 连接。建立连接后，CFS 生成指定 fjsonparser 的标准 COPY LOCAL 语句。CFS 使用如下语句将数据直接加载到预先存在的 Flex 表：

=> COPY myFlexTable FROM LOCAL path_to_compressed_temporary_json_file PARSER fjsonparser();

=> SELECT * FROM myavro;
 __identity__ | __raw__
--------------+---------
(0 rows)

如果 IDOL 元数据出现在 Flex 表中，您可以按照更改 Flex 表中的说明，选择添加新的表列或实体化其他数据。

7 - 使用 Flex 表解析器

您可以使用解析器支持的选项，借助多个解析器之一加载 Flex 表。

除了本节列出的解析器之外，数据加载的数据格式中描述的以下解析器也支持 Flex 表：

7.1 - 使用 Flex 解析器加载列式表

您可以使用任何 Flex 解析器将数据加载到列式表中。使用 Flex 表解析器加载列式表使您能够在一个表中混合数据加载。例如，您可以将 JSON 数据加载到一个会话中，而将分隔数据加载到另一个会话中。

注意

对于 Avro 数据，您可以只将数据加载到列式表，而不加载架构。对于 Flex 表，则必须将 Avro 架构信息嵌入在数据中。

下面的基本示例说明了您可以如何将 Flex 解析器用于列式表。

创建一个列式表 super，其具有两列，分别是 age 和 name：
```
=> CREATE TABLE super(age INT, name VARCHAR);
CREATE TABLE
```

使用 fjsonparser() 输入来自 STDIN 的 JSON 值。

=> COPY super FROM stdin PARSER fjsonparser();
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {"age": 5, "name": "Tim"}
>>  {"age": 3}
>>  {"name": "Fred"}
>>  {"name": "Bob", "age": 10}
>> \.

查询该表以查看您输入的值：


=> SELECT * FROM super;
 age | name
-----+------
     | Fred
  10 | Bob
   5 | Tim
   3 |
(4 rows)

使用 fdelimitedparser() 输入来自 STDIN 的一些分隔值：

=> COPY super FROM stdin PARSER fdelimitedparser();
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> name |age
>> Tim|50
>> |30
>> Fred|
>> Bob|100
>> \.

查询 Flex 表。JSON 和分隔数据均保存在同一个列式表 super 中。

=> SELECT * FROM super;
 age | name
-----+------
  50 | Tim
  30 |
   3 |
   5 | Tim
 100 | Bob
     | Fred
  10 | Bob
     | Fred
(8 rows)

使用 reject_on_materialized_type_error 参数避免加载类型不匹配的数据。如果 reject_on_materialized_type_error 设置为 false，Flex 解析器将接受类型不匹配的数据。请考虑以下示例：

假定待加载的 CSV 文件具有以下示例内容：

$ cat json.dat
{"created_by":"system","site_source":"flipkart_india_kol","updated_by":"system1","invoice_id":"INVDPKOL100",
"vendor_id":"VEN15731","total_quantity":12,"created_at":"2012-01-09 23:15:52.0"}
{"created_by":"system","site_source":"flipkart_india_kol","updated_by":"system2","invoice_id":"INVDPKOL101",
"vendor_id":"VEN15732","total_quantity":14,"created_at":"hello"}

创建列式表。

=> CREATE TABLE hdfs_test (
site_source VARCHAR(200),
total_quantity int ,
vendor_id varchar(200),
invoice_id varchar(200),
updated_by varchar(200),
created_by varchar(200),
created_at timestamp
);

加载 JSON 数据。

=>COPY hdfs_test FROM '/home/dbadmin/json.dat' PARSER fjsonparser() ABORT ON ERROR;
Rows Loaded
-------------
2
(1 row)

查看内容。

=> SELECT * FROM hdfs_test;
site_source | total_quantity | vendor_id | invoice_id | updated_by | created_by | created_at
--------------------+----------------+-----------+-------------+------------+------------+---------------------
flipkart_india_kol | 12 | VEN15731 | INVDPKOL100 | system1 | system | 2012-01-09 23:15:52
flipkart_india_kol | 14 | VEN15732 | INVDPKOL101 | system2 | system |
(2 rows)

如果 reject_on_materialized_type_error 参数设置为 true，您将在加载示例 JSON 数据时收到错误消息。

=> COPY hdfs_test FROM '/home/dbadmin/data/flex/json.dat' PARSER fjsonparser(reject_on_materialized_type_error=true) ABORT ON ERROR;
ERROR 2035:  COPY: Input record 2 has been rejected (Rejected by user-defined parser)

7.2 - 加载 CSV 数据

使用 fcsvparser 加载 CSV 格式（逗号分隔值）的数据。由于不存在正式的 CSV 标准，Vertica 支持 RFC 4180 标准为 fcsvparser 的默认行为。其他解析器参数则将 CSV 选项的各种组合简化成列式表或 Flex 表。使用 fcsvparser 解析以下 CSV 数据格式：

**RFC 4180：**Vertica Flex 表的 RFC4180 CSV 格式解析器。此格式的参数为固定值，无法更改。
传统： 传统 CSV 解析器允许您指定参数值，如分隔符或记录终止符。有关参数的详细列表，请参阅 FCSVPARSER。

使用默认解析器设置

这些固定的参数设置适用于 RCF4180 格式。

您可以对 enclosed_by 和 escape 使用相同的值。其他值必须唯一。

使用 type 参数指示文件符合 RFC 4180 格式还是传统格式。您可以将 type 指定为 RCF4180。但您必须首先验证数据是否与前面 RFC4180 格式参数的固定值兼容。type 参数的默认值为 RFC4180.

加载 CSV 数据 (RFC4180)

请按照以下步骤，使用 fcsvparser 加载 RFC4180 CSV 数据格式的数据。

要执行此任务，假定待加载的 CSV 文件具有以下示例内容：

$ more /home/dbadmin/flex/flexData1.csv
sno,name,age,gender
1,John,14,male
2,Mary,23,female
3,Mark,35,male

创建 Flex 表：

=> CREATE FLEX TABLE csv_basic();
CREATE TABLE

使用 fcsvparser 从 CSV 文件加载数据：

=> COPY csv_basic FROM '/home/dbadmin/flex/flexData1.csv' PARSER fcsvparser();
Rows Loaded
-------------
3
(1 row)

查看加载到 Flex 表中的数据：

=> SELECT maptostring(__raw__) FROM csv_basic;
maptostring
----------------------------------------------------------------------------------
{
"age" : "14",
"gender" : "male",
"name" : "John",
"sno" : "1"
}
{
"age" : "23",
"gender" : "female",
"name" : "Mary",
"sno" : "2"
}
{
"age" : "35",
"gender" : "male",
"name" : "Mark",
"sno" : "3"
}
(3 rows)

加载 CSV 数据（传统）

请按照以下步骤，使用 fcsvparser 加载传统 CSV 数据格式的数据。

在此示例中，CSV 文件将 $ 用作 delimiter，将 # 用作 record_terminator。待加载的示例 CSV 文件具有以下内容：

$ more /home/dbadmin/flex/flexData1.csv
sno$name$age$gender#
1$John$14$male#
2$Mary$23$female#
3$Mark$35$male#

创建 Flex 表：

=> CREATE FLEX TABLE csv_basic();
CREATE TABLE

使用带有参数 type='traditional'、delimiter='$' 和 record_terminator='#' 的 fscvparser 加载 Flex 表中的数据：

=> COPY csv_basic FROM '/home/dbadmin/flex/flexData2.csv' PARSER fcsvparser(type='traditional',
delimiter='$', record_terminator='#');
Rows Loaded
-------------
3
(1 row)

查看加载到 Flex 表中的数据：


=> SELECT maptostring(__raw__) FROM csv_basic;
maptostring
----------------------------------------------------------------------------------
{
"age" : "14",
"gender" : "male",
"name" : "John",
"sno" : "1"
}
{
"age" : "23",
"gender" : "female",
"name" : "Mary",
"sno" : "2"
}
{
"age" : "35",
"gender" : "male",
"name" : "Mark",
"sno" : "3"
}
(3 rows)

拒绝重复值

您可以通过使用 fcsvparser 的 reject_on_duplicate=true 选项拒绝重复值。加载在拒绝重复值后继续。下个示例演示了如何使用此参数，接着显示了指定的异常和拒绝的数据文件。将被拒绝的数据保存到表而不是文件中，包括数据及其异常。

=> CREATE FLEX TABLE csv_basic();
CREATE TABLE

=> COPY csv_basic FROM stdin PARSER fcsvparser(reject_on_duplicate=true)
exceptions '/home/dbadmin/load_errors/except.out' rejected data '/home/dbadmin/load_errors/reject.out';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.

>> A|A
>> 1|2
>> \.

=> \! cat /home/dbadmin/load_errors/reject.out
A|A
=> \! cat /home/dbadmin/load_errors/except.out
COPY: Input record 1 has been rejected (Processed a header row with duplicate keys with
reject_on_duplicate specified; rejecting.).  Please see /home/dbadmin/load_errors/reject.out,
record 1 for the rejected record.
COPY: Loaded 0 rows, rejected 1 rows.

拒绝包含实体化列类型错误的数据

fcsvparser 解析器具有一个布尔参数 reject_on_materialized_type_error。将此参数设置为 true 将导致行被拒绝，前提是输入数据中同时存在以下情况：

包括与现有实体化列匹配的键
具有无法强迫采用实体化列数据类型的键值

以下示例演示了如何设置该参数。

创建一个表 reject_true_false，带有两个实际列：

=> CREATE FLEX TABLE reject_true_false(one int, two int);
CREATE TABLE

使用 fcsvparser 在 reject_on_materialized_type_error=false 的情况下将 CSV 数据加载至表（从 STDIN 中）中。虽然 false 是默认值，但您可以显式指定它，如下所示。此外，设置参数 header=true 以指定输入值对应的列：

=> COPY reject_true_false FROM stdin PARSER fcsvparser(reject_on_materialized_type_error=false,header=true);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> one,two
>> 1,2
>> "3","four"
>> "five",6
>> 7,8
>> \.

调用 maptostring 在加载数据之后显示表值：

=> SELECT maptostring(__raw__), one, two FROM reject_true_false;
maptostring               | one | two
----------------------------------------+-----+-----
{
"one" : "1",
"two" : "2"
}
|   1 |   2
{
"one" : "3",
"two" : "four"
}
|   3 |
{
"one" : "five",
"two" : "6"
}
|     |   6
{
"one" : "7",
"two" : "8"
}
|   7 |   8
(4 rows)

截断该表以清空表中存储的数据：

=> TRUNCATE TABLE reject_true_false;
TRUNCATE TABLE

重新加载相同的数据，但这次请设置 reject_on_materialized_type_error=true：

=> COPY reject_true_false FROM stdin PARSER fcsvparser(reject_on_materialized_type_error=true,header=true);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> one,two
>> 1,2
>> "3","four"
>> "five",6
>> 7,8
>> \.

调用 maptostring 显示表内容。当前仅加载两行，而之前的结果中有四行。含有数据类型不正确的输入值的行已被拒绝：

=> SELECT maptostring(__raw__), one, two FROM reject_true_false;
maptostring             | one | two
-------------------------------------+-----+-----
{
"one" : "1",
"two" : "2"
}
|   1 |   2
{
"one" : "7",
"two" : "8"
}
|   7 |   8
(2 rows)

注意

如果存在类型不匹配，解析器 fcsvparser 将使用 null 值；而您将 reject_on_materialized_type_error 参数设置为 false。

拒绝或忽略空行

有效的 CSV 文件可能包含空键值对。这些行对 SQL 无效。您可以使用两个布尔 FCSVPARSER 参数拒绝或忽略空行，从而控制其行为：

reject_on_empty_key
omit_empty_keys

下面的示例演示了如何设置这些参数：

创建 Flex 表：

 => CREATE FLEX TABLE csv_basic();
CREATE TABLE

使用 fcsvparser 在 reject_on_empty_key=false 的情况下将 CSV 数据加载至表（从 STDIN 中）中。虽然 false 是默认值，但您可以显式指定它，如下所示。此外，设置参数 header=true 以指定输入值对应的列：
```
=> COPY csv_basic FROM stdin PARSER fcsvparser(reject_on_empty_key=false,header=true);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> ,num
>> 1,2
>> \.
```

调用 maptostring 在加载数据之后显示表值：

=>SELECT maptostring(__raw__) FROM csv_basic;
maptostring
----------------------------------
{
"" : "1",
"num" : "2"
}

(1 row)

截断该表以清空表中存储的数据：

=> TRUNCATE TABLE csv_basic;
TRUNCATE TABLE

重新加载相同的数据，但这次请设置 reject_on_empty_key=true：

=> COPY csv_basic FROM stdin PARSER fcsvparser(reject_on_empty_key=true,header=true);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> ,num
>> 1,2
>> \.

调用 maptostring 显示表内容。未加载任何行，因为其中一个键为空：
```
=>SELECT maptostring(__raw__) FROM csv_basic;
maptostring
-------------
(0 rows)
```

截断该表以清空表中存储的数据：

=> TRUNCATE TABLE csv_basic;
TRUNCATE TABLE

重新加载相同的数据，但这次请设置 omit_empty_keys=true：

=> COPY csv_basic FROM stdin PARSER fcsvparser(omit_empty_keys=true,header=true);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> ,num
>> 1,2
>> \.

调用 maptostring 显示表内容。现已加载一行，带空键的行已被忽略：

=> SELECT maptostring(__raw__) FROM csv_basic;
maptostring
---------------------
{
"num" : "2"
}
(1 row)

注意

如果不存在标题名称，则 fcsvparser 使用默认标题 ucoln，其中 n 是列偏移量。如果表的标题名称与键名相匹配，则解析器将为列加载匹配键的关联值。

使用 NULL 参数

使用带有 fcsvparser 的 COPY NULL 元数据参数，将 NULL 值加载到 Flex 表中。

下面的示例使用此参数：

创建 Flex 表：

=> CREATE FLEX TABLE fcsv(c1 int);
CREATE TABLE

使用 STDIN 和 NULL 参数将 CSV 数据加载到 Flex 表中：

=> COPY fcsv FROM STDIN PARSER fcsvparser() NULL 'NULL' ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> a,b,c1
>> 10,20,NULL
>> 20,30,50
>> 20,30,40
>> \.

使用 compute_flextable_keys_and_build_view 函数计算键并构建 Flex 视图：

=> SELECT compute_flextable_keys_and_build_view('fcsv');
compute_flextable_keys_and_build_view
--------------------------------------------------------------------------------
Please see public.fcsv_keys for updated keys
The view public.fcsv_view is ready for querying
(1 row)

查看 Flex 视图并替换 NULL 值：

=> SELECT * FROM public.fcsv_view;
a  | b  | c1
----+----+----
20 | 30 | 50
10 | 20 |
20 | 30 | 40
(3 rows)

=> SELECT a,b, ISNULL(c1,-1) from public.fcsv_view;
a  | b  | ISNULL
----+----+--------
20 | 30 |     50
10 | 20 |     -1
20 | 30 |     40
(3 rows)

处理列标题

fcsvparser 允许您使用 HEADER_NAMES= 参数指定您自己的列标题。此参数完全替换 CSV 源标题行中的列名。

例如，要将这六个列标题用于您正在加载的 CSV 文件，请使用 fcsvparser 参数，如下所示：

HEADER_NAMES='FIRST, LAST, SOCIAL_SECURITY, TOWN, STATE, COUNTRY'

如果提供比现有数据列更少的标题名称，则 fcsvparser 在您提供的名称之后使用默认名称。默认标题名称由 ucoln 组成，其中 n 是列偏移量，第一列从 0 开始。例如，如果您为 6 列表提供四个标题名称，fcsvparser 会在您提供的第四个标题名称之后提供默认名称 ucol4 和 ucol5。

如果您提供的标题多于现有表列，则任何其他标题都将保持未使用状态。

7.3 - 加载分隔的数据

您可以使用两种分隔解析器（fdelimitedparser 和 fdelimitedpairparser）之一加载 Flex 表。

数据通过各行的数据指定列名称时，使用 fdelimitedpairparser。
数据不指定列名称或具有与列名称对应的标题行时，使用 fdelimitedparser。

本节将介绍如何使用 fdelimitedpairparser 和 fdelimitedparser 支持的一些选项。

拒绝重复值

您可以通过使用 fdelimitedparser 的 reject_on_duplicate=true 选项拒绝重复值。加载在拒绝重复值后继续。下个示例演示了如何使用此参数，接着显示了指定的异常和拒绝的数据文件。将被拒绝的数据保存到表而不是文件中，包括数据及其异常。

=> CREATE FLEX TABLE delim_dupes();
CREATE TABLE

=> COPY delim_dupes FROM stdin PARSER fdelimitedparser(reject_on_duplicate=true)
exceptions '/home/dbadmin/load_errors/except.out' rejected data '/home/dbadmin/load_errors/reject.out';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.

>> A|A
>> 1|2
>> \.

=> \! cat /home/dbadmin/load_errors/reject.out
A|A
=> \! cat /home/dbadmin/load_errors/except.out
COPY: Input record 1 has been rejected (Processed a header row with duplicate keys with
reject_on_duplicate specified; rejecting.).  Please see /home/dbadmin/load_errors/reject.out,
record 1 for the rejected record.
COPY: Loaded 0 rows, rejected 1 rows.

拒绝实体化列类型错误

fjsonparser 解析器和 fdelimitedparser 解析器都具有一个布尔参数 reject_on_materialized_type_error。将此参数设置为 true 将导致行被拒绝，前提是输入数据中同时存在以下情况：

包括与现有实体化列匹配的键
具有无法强迫采用实体化列的数据类型的值

假定 Flex 表具有实体化的列 OwnerPercent，声明为 FLOAT。试图加载带 OwnerPercent 键（值为 VARCHAR）的行将导致 fdelimitedparser 拒绝该数据行。

以下示例演示了如何设置该参数。

创建一个表 reject_true_false，带有两个实际列：

=> CREATE FLEX TABLE reject_true_false(one VARCHAR, two INT);
CREATE TABLE

使用 fjsonparser 在 reject_on_materialized_type_error=false 的情况下将 JSON 数据（从 STDIN）加载至表中。尽管 false 是默认值，以下示例将显示指定该值以进行演示：

=> COPY reject_true_false FROM stdin PARSER fjsonparser(reject_on_materialized_type_error=false);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {"one": 1, "two": 2}
>> {"one": "one", "two": "two"}
>> {"one": "one", "two": 2}
>> \.

调用 maptostring 在加载数据之后显示表值：

=>  SELECT maptostring(__raw__), one, two FROM reject_true_false;
               maptostring        | one | two
----------------------------------+-----+-----
 {
   "one" : "one",
   "two" : "2"
}
   | one |   2
{
   "one" : "1",
   "two" : "2"
}
   | 1   |   2
 {
   "one" : "one",
   "two" : "two"
}
   | one |
(3 rows)

截断该表：
```
=> TRUNCATE TABLE reject_true_false;
```

重新加载相同的数据，但这次请设置 reject_on_materialized_type_error=true：

=> COPY reject_true_false FROM stdin PARSER fjsonparser(reject_on_materialized_type_error=true);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {"one": 1, "two": 2}
>> {"one": "one", "two": "two"}
>> {"one": "one", "two": 2}
>> \.

调用 maptostring 显示表内容。仅加载两行，而之前的结果中有三行：

=> SELECT maptostring(__raw__), one, two FROM reject_true_false;
              maptostring              | one | two
---------------------------------------+-----+-----
 {
   "one" : "1",
   "two" : "2"
}
   | 1   |   2
 {
   "one" : "one",
   "two" : "2"
}
 | one |   2
(2 rows)

8 - 计算 Flex 表键

将数据加载到 Flex 表后，您可以确定 __raw__ 列（映射数据）中存在的键集。两个 Helper 函数从 Flex 表映射数据中计算出键：

COMPUTE_FLEXTABLE_KEYS 确定在 Flex 映射中哪些键作为虚拟列存在。
COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW 除执行与 COMPUTE_FLEXTABLE_KEYS 相同的功能外，还构建一个新视图。另请参阅更新 Flex 表视图。

注意

如果密钥的长度超过 65,000，Vertica 会截断该密钥。

使用 COMPUTE_FLEXTABLE_KEYS

执行过程中，此函数计算 Flex 键表列的以下信息：

确定键数据类型

默认情况下，使用 COMPUTE_FLEXTABLE_KEYS 确定来自 __raw__ 列 LONG VARBINARY 类型的非字符串键值。非字符串键包括以下数据类型（以及数据类型中列出的其他数据类型）：

BOOLEAN
INTEGER
FLOAT
TIMESTAMP
DATE

使用此部分中的示例 CSV 数据来比较使用或不使用 EnableBetterFlexTypeGuessing 配置参数的结果。当参数为 ON 时，该函数可以更准确地确定地图数据中的关键非字符串数据类型。该参数的默认值为 1 (ON)。

Year,Quarter,Region,Species,Grade,Pond Value,Number of Quotes,Available
2015,1,2 - Northwest Oregon & Willamette,Douglas-fir,1P,$615.12 ,12,No
2015,1,2 - Northwest Oregon & Willamette,Douglas-fir,SM,$610.78 ,12,Yes
2015,1,2 - Northwest Oregon & Willamette,Douglas-fir,2S,$596.00 ,20,Yes
2015,1,2 - Northwest Oregon & Willamette,Hemlock,P,$520.00 ,6,Yes
2015,1,2 - Northwest Oregon & Willamette,Hemlock,SM,$510.00 ,6,No
2015,1,2 - Northwest Oregon & Willamette,Hemlock,2S,$490.00 ,14,No

要比较数据类型分配结果，请完成以下步骤：

保存此 CSV 数据文件（此处保存为 trees.csv）。

创建 Flex 表 (trees) 并使用 fcsvparser 加载 trees.csv：

=> CREATE FLEX TABLE trees();
=> COPY trees FROM '/home/dbadmin/tempdat/trees.csv' PARSER fcsvparser();

将 COMPUTE_FLEXTABLE_KEYS 用于 trees Flex 表。

=> SELECT COMPUTE_FLEXTABLE_KEYS('trees');
            COMPUTE_FLEXTABLE_KEYS
-----------------------------------------------
 Please see public.trees_keys for updated keys
(1 row)

查询 trees_keys 表输出：

=> SELECT * FROM trees_keys;
     key_name     | frequency | data_type_guess
------------------+-----------+-----------------
 Year             |         6 | Integer
 Quarter          |         6 | Integer
 Region           |         6 | Varchar(66)
 Available        |         6 | Boolean
 Number of Quotes |         6 | Integer
 Grade            |         6 | Varchar(20)
 Species          |         6 | Varchar(22)
 Pond Value       |         6 | Numeric(8,3)
(8 rows)

将 EnableBetterFlexTypeGuessing 参数设置为 0（OFF）。
再次使用 trees Flex 表调用 COMPUTE_FLEXTABLE_KEYS。

查询 trees_keys 表，将 data_type_guess 的值与之前的结果进行比较。如果没有配置参数集，所有非字符串数据类型都是各种长度的 VARCHARS：


=> SELECT * FROM trees_keys;
    key_name     | frequency | data_type_guess
------------------+-----------+-----------------
 Year             |         6 | varchar(20)
 Quarter          |         6 | varchar(20)
 Region           |         6 | varchar(66)
 Available        |         6 | varchar(20)
 Grade            |         6 | varchar(20)
 Number of Quotes |         6 | varchar(20)
 Pond Value       |         6 | varchar(20)
 Species          |         6 | varchar(22)
(8 rows)

要保持非字符串数据类型的准确结果，请将 EnableBetterFlexTypeGuessing 参数重新设置为 1 (ON)。

有关 EnableBetterFlexTypeGuessing 配置参数的详细信息，请参阅 EnableBetterFlexTypeGuessing。

计算键值列宽

COMPUTE_FLEXTABLE_KEYS 函数通过确定每个键的最大值的长度再乘以 FlexTableDataTypeGuessMultiplier 因子来确定键的列宽。

下一个示例显示了在创建 Flex 表 (darkdata1) 并加载数据后填充 _keys 表的结果。列宽显示在 data_type_guess 列的值之后的括号中（如果适用）：


=> SELECT compute_flextable_keys('darkdata1');
            compute_flextable_keys
--------------------------------------------------
 Please see public.darkdata1_keys for updated keys
(1 row)

=> SELECT * from darkdata1_keys;
                         key_name                         | frequency |   data_type_guess
----------------------------------------------------------+-----------+----------------------
 created_at                                               |         8 | TimestampTz
 delete.status.id_str                                     |         4 | Integer
 delete.status.user_id                                    |         4 | Integer
 entities.hashtags                                        |         8 | long varbinary(186)
 favorited                                                |         8 | Boolean
 id_str                                                   |         8 | Integer
 in_reply_to_screen_name                                  |         8 | Varchar(24)
 retweeted_status.contributors                            |         1 | Varchar(20)
 retweeted_status.coordinates                             |         1 | Varchar(20)
 retweeted_status.created_at                              |         1 | TimestampTz
.
.
.
(125 rows)

9 - 实体化 Flex 表

创建 Flex 表后，您可以更改表结构以将虚拟列提升为实体化（实际）列。如果您的表已经为混合表，您可以更改现有的实际列，并提升其他重要的虚拟列。本节介绍了提升列、添加列、指定约束和声明默认值的一些关键环节。还介绍了加载 Flex 表或混合表与加载列式表相比的一些区别。

注意

通过将虚拟列提升为实际列来实现虚拟列的实体化，可以显著提高查询性能。Vertica 建议您先实体化重要的虚拟列，然后再运行大型和复杂的查询。提升后的列可导致加载性能稍有降低。

将列添加到 Flex 表

将列添加到 Flex 表以提升虚拟列：

添加名称与虚拟列 (user.name) 相同的实际列：

=> ALTER TABLE darkdata1 ADD COLUMN "user.name" VARCHAR;
ALTER TABLE

将一些数据加载到表中。

 => COPY darkdata1 FROM '/vertica/flextable/DATA/tweets_12.json' PARSER fjsonparser();
 Rows Loaded
-------------
          12
(1 row)

查询实体化列。请注意，加载数据将自动填充列。空行表示没有值或 NULL：

=> SELECT "user.name" FROM darkdata1;
      user.name
---------------------
 I'm Toasterâ¥


 Flu Beach
 seydo shi


 The End
 Uptown gentleman.

 ~G A B R I E L A â¿
 Avita Desai


 laughing at clouds.
(12 rows)

添加具有默认值的列

将数据批量加载到 Flex 表中一节介绍了默认值的使用情况以及 Vertica 如何在加载过程中对其进行评估。与所有的表一样，使用 COPY 加载数据会忽略所有的列默认值。

注意

要将表列默认表达式添加到 Flex 表，需要将列转换成显式数据类型。

创建具有列定义的 darkdata1 表。以下示例使用的列名称 (talker) 与虚拟列名称不对应。指定带虚拟列名称的默认值。在此示例中，列 talker 的默认值为 ("user.lang")。由于 user.lang 是 LONG VARBINARY __raw__ 列中的一个虚拟列，您必须将它的值转换成 VARCHAR 以匹配 talker 列定义：
```
=> CREATE FLEX TABLE darkdata1(talker VARCHAR default "user.lang"::VARCHAR);
CREATE TABLE
```

加载一些 JSON 数据，并指定 __raw__ 列：

=> COPY darkdata1 (__raw__) FROM '/test/vertica/flextable/DATA/tweets_12.json'
   PARSER fjsonparser();
 Rows Loaded
-------------
          12
(1 row)

查询 talker 列。请注意，Vertica 之所以使用默认列值 ("user.lang")，是因为您指定了 __raw__。空行表示没有值或 NULL：
```
=> SELECT "talker" FROM darkdata1;
 talker
--------
 it
 en
 es
 en
 en
 es
 tr
 en
(12 rows)
```
更改表以添加具有已知虚拟列名称 (user.name) 的列，指定键名为默认值（推荐），并将其转换成 VARCHAR：
```
=> ALTER TABLE darkdata1 ADD COLUMN "user.name" VARCHAR default "user.name"::VARCHAR;
ALTER TABLE
```

再次加载数据，这次不带 __raw__：

=> COPY darkdata1 FROM '/test/vertica/flextable/DATA/tweets_12.json' PARSER fjsonparser();

查询两个实际列。请注意，talker 没有值，因为您没有指定 __raw__ 列。user.lang 列包含来自 user.name 虚拟列的值：

=> SELECT "talker", "user.name" FROM darkdata1;
 talker |      user.name
--------+---------------------
        | laughing at clouds.
        | Avita Desai
        | I'm Toasterâ¥
        |
        |
        |
        | Uptown gentleman.
        | ~G A B R I E L A â¿
        | Flu Beach
        |
        | seydo shi
        | The End
(12 rows)

再次加载数据，这次指定含有 user.name 默认值表达式的 COPY 语句：

=> COPY darkdata1 (__raw__, "user.name" as 'QueenElizabeth'::varchar) FROM
'/test/vertica/flextable/DATA/tweets_12.json' PARSER fjsonparser();
 Rows Loaded
-------------
          12
(1 row)

再次查询。请注意，实际列 talker 具有默认值（您使用了 __raw__）。按照 COPY 中的指定，"user.name" as 'QueenElizabeth' 表达式覆盖了 user.name 的默认列值：

=> SELECT "talker", "user.name" FROM darkdata1;
 talker |   user.name
--------+----------------
 it     | QueenElizabeth
 en     | QueenElizabeth
 es     | QueenElizabeth
        | QueenElizabeth
        | QueenElizabeth
        | QueenElizabeth
 en     | QueenElizabeth
 en     | QueenElizabeth
 es     | QueenElizabeth
        | QueenElizabeth
 tr     | QueenElizabeth
 en     | QueenElizabeth
(12 rows)

总之，作为 ALTER TABLE...ADD COLUMN 操作的一部分，您可以设置默认列值。实体化列时，默认值应该引用虚拟列的键名（像在 "user.lang" 中一样）。随后加载带 COPY 值表达式的数据，可覆盖列定义的默认值。

更改 raw 列大小

您可以更改您计划创建的 Flex 表 __raw__ 列的默认大小，还可以更改现有 Flex 表的当前大小，或者同时更改上述两项。

要更改新 Flex 表中 __raw__ 列的默认大小，请设置 FlexTableRawSize 配置参数：

=> ALTER DATABASE DEFAULT SET FlexTableRawSize = 120000;

更改配置参数会影响到您在做出此更改后创建的所有 Flex 表。

要更改现有 Flex 表中 _raw_ 列的大小，请使用 ALTER TABLE 语句更改 raw 列的定义：


=> ALTER TABLE tester ALTER COLUMN __raw__ SET DATA TYPE LONG VARBINARY(120000);
ALTER TABLE

注意

如果您试图将 __raw__ 列大小的值减至小于列中包含的任何数据，就会发生错误。

更改 Flex 表实际列

您可以对 Flex 表实际列（__raw__ 和 __identity__）而不能对任何虚拟列做出以下更改：

注意

虽然允许对 __raw__ 列进行分段和分区，但因为其数据类型长而不建议这样做。默认情况下，如果不定义任何实际列，则 Flex 表将在 __identity__ 列上分段。

删除 Flex 表列

关于删除列，有两个注意事项：

您不能删除 Flex 表排序顺序中的最后一列。
如果您尚未创建带任何实际列的 Flex 表或尚未实体化任何列，则您不能删除 __identity__ 列。

更新 Flex 表视图

将新列添加到具有关联视图的 Flex 表时，系统不会更新视图的结果集，即便该视图使用了通配符 (*) 来表示所有表列。要整合新列，您必须重新创建视图。

10 - 更新 Flex 表视图

创建 Flex 表的同时，还会创建一个该表附带的默认视图。该视图的名称为 Flex 表名称加上下划线 (_view) 后缀。当您从默认视图执行 select 查询时，Vertica 会提示您运行 Helper 函数 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)

有两个 Helper 函数可以创建视图：

COMPUTE_FLEXTABLE_KEYS— 另请参阅 COMPUTE_FLEXTABLE_KEYS
COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW— 除执行与 BUILD_FLEXTABLE_KEYS 相同的功能外，还计算键。另请参阅使用 COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW.

使用 BUILD_FLEXTABLE_VIEW

计算出 Flex 表的键 (计算 Flex 表键) 后，通过一个或多个实参调用此函数。{flextable}_keys 表 key_name 列下的记录将用作视图列以及该键的任何值。如果不存在任何值，则列值为 NULL。

如下所示，无论使用多少参数，调用此函数均将替换现有视图的内容：

如果您不指定 view_name 实参，默认名称将为 Flex 表名称加上 _view 后缀。例如，如果您指定表 darkdata 为此函数的唯一参数，则默认视图的名称为 darkdata_view。

您不能指定与默认视图 flex_table_view 名称相同的自定义视图名称，除非您先删除默认命名的视图，然后再自行创建具有相同名称的视图。

创建视图时将存储列结构的定义。因此，如果您创建了 Flex 表视图并随后将虚拟列提升为实际列，您必须重新构建视图。查询具有刚提升的实际列的重建 Flex 表视图可产生两个结果。这些结果同时反映来自实际列和映射数据中的虚拟列的值。

处理视图中的 JSON 重复键名

SQL 是不区分大小写的语言，因此名称 TEST、test 和 TeSt 是完全相同的。JSON 数据区分大小写，因此可以有效包含大小写不同且具有不同值的键名。

当您构建 Flex 表视图时，如果此函数在 {flextable}_keys 表中检测到名称相同但大小写不同的键，此函数将生成警告。例如，对具有重复键名的 Flex 表调用 BUILD_FLEXTABLE_VIEW 或 COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW()，将生成以下警告：

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

虽然 {flextable}_keys 表可以包含大小写不同的重复键名，但视图不能。使用上述任一 Helper 函数创建 Flex 表视图，会将所有重复键名合并成一个由所有小写字符组成的列名称。该列的所有重复键值均将予以保存。例如，如果一个 Flex 表中存在以下键名：

test
Test
tESt

视图将包括虚拟列 test，它具有 test 键、Test 键和 tESt 键的值。

注意

本节的示例包含附加的换行符以减少行长度。产品输出可能有所不同。

例如，请考虑显示重复 test 键名称的以下查询：

=> \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)

以下查询显示了 dupe Flex 表 (dupe_view)。它显示了合并后的 test 和 testtesttest... 虚拟列。test、Test 和 tESt 虚拟列的所有值均位于 test 列：

=> SELECT * FROM dupe_view;
  test  | testtesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttest
testtesttesttesttesttesttesttest
--------+--------------------------------------------------------------------------------------------------
 upper2 |
 half4  |
 lower1 |
 upper1 |
 half1  |
 half4  |
        |
 lower1 |
 half1  |
 upper2 |
        |
 lower2 |
 lower3 |
 upper1 |
 lower2 |
 lower3 |
(16 rows)

创建 Flex 表视图

下面的示例演示了如何从包含 JSON 数据的 Flex 表 darkdata 创建视图 dd_view。

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

查询您指定的键名及其值：

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

此示例演示了如何对原始表和您之前创建的视图 dd_view 调用 build_flextable_view：

=> SELECT build_flextable_view ('darkdata', 'dd_view');
            build_flextable_view
-----------------------------------------------
 The view public.dd_view is ready for querying
(1 row)

再次查询该视图。您可以看到，函数用 darkdata_keys 表的内容填充了该视图。接下来，通过 key_name 列和它们的值，审阅结果中的一个代码段：

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

请注意，创建视图时将存储列结构的定义。如果您在构建视图后将虚拟列提升为实际列，现有的视图定义不会更改。用 SELECT 语句查询此视图（如下所示），将仅返回 __raw__ 列中的值：

=> SELECT * FROM myflextable_view;

另请了解，提升虚拟列后重新构建视图，将改变所得的值。未来的查询将同时返回来自实际列和映射数据中的虚拟列的值。

使用 COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW

在一个步骤中，对 Flex 表调用此函数以计算 Flex 表键（请参阅计算 Flex 表键）并创建视图。

11 - 查询 Flex 表

创建 Flex 表（带或不带附加列）并加载数据后，您可以像处理其他表一样使用 SELECT、COPY、TRUNCATE 和 DELETE。您可以将 SELECT 查询用于 Flex 表 __raw__ 列中存在的虚拟列以及该表中的其他实际列。__raw__ 列中的字段名称不区分大小写，实际列的名称也是如此。

不受支持的 DDL 和 DML 语句

您不能将以下 DDL 和 DML 语句用于 Flex 表：

CREATE TABLE...AS...
CREATE TABLE...LIKE...
SELECT INTO
UPDATE
MERGE

确定 Flex 表数据的内容

如果您不知道您的 Flex 表包含什么，两个 Helper 函数可让您浏览 VMap 数据以确定内容。使用这些函数计算 Flex 表 __raw__ 列中的键，然后可以选择构建基于这些键的视图：

有关这些和其他 Helper 函数的详细信息，请参阅 Flex 数据函数。

要确定哪些键值对作为虚拟列存在，请执行以下操作：

按如下方式调用函数：

=> SELECT compute_flextable_keys('darkdata');
            compute_flextable_keys
--------------------------------------------------
 Please see public.darkdata_keys for updated keys(1 row)

通过查询 darkdata_keys 表查看键名：

=> SELECT * FROM darkdata_keys;

                         key_name                    | frequency |   data_type_guess
-----------------------------------------------------+-----------+-------------
 contributors                                        |         8 | varchar(20)
 coordinates                                         |         8 | varchar(20)
 created_at                                          |         8 | varchar(60)
 entities.hashtags                                   |         8 | long varbinary(186)
 .
 .
 retweeted_status.user.time_zone                     |         1 | varchar(20)
 retweeted_status.user.url                           |         1 | varchar(68)
 retweeted_status.user.utc_offset                    |         1 | varchar(20)
 retweeted_status.user.verified                      |         1 | varchar(20)
(125 rows)

查询虚拟列

继续采用 JSON 数据示例，使用 SELECT 语句查询浏览虚拟列的内容。然后，分析当您要实体化任何虚拟列时什么对您来说最重要。此示例演示了如何在 VMap 数据中查询一些常用虚拟列：

=> SELECT "user.name", "user.lang", "user.geo_enabled" FROM darkdata1;
      user.name      | user.lang | user.geo_enabled
---------------------+-----------+------------------
 laughing at clouds. | it        | T
 Avita Desai         | en        | F
 I'm Toasterâ¥       | es        | T
                     |           |
                     |           |
                     |           |
 Uptown gentleman.   | en        | F
 ~G A B R I E L A â¿ | en        | F
 Flu Beach           | es        | F
                     |           |
 seydo shi           | tr        | T
 The End             | en        | F
(12 rows)

查询 Flex 表键

如果您在 Flex 表查询中引用未定义的列 ('which_column')，Vertica 会按照如下方式将该查询转换成对 MAPLOOKUP 函数的调用：

MAPLOOKUP(__raw__, 'which_column')

MAPLOOKUP 函数在 VMap 数据中搜索所请求的键，并返回以下信息：

针对某行与键关联的字符串值。
NULL 如果未找到该键。

有关处理 NULL 值的详细信息，请参阅 MAPCONTAINSKEY()。

在 Flex 表查询中使用函数和转换

您可以根据需要转换虚拟列，并在 SELECT 语句查询中使用函数。下一个示例使用 SELECT 语句查询 created_at 和 retweet_count 虚拟列，并在此过程中转换它们的值：

=> SELECT "created_at"::TIMESTAMP, "retweet_count"::INT FROM darkdata1 ORDER BY 1 DESC;
     created_at      | retweet_count
---------------------+---------------
 2012-10-15 14:41:05 |             0
 2012-10-15 14:41:05 |             0
 2012-10-15 14:41:05 |             0
 2012-10-15 14:41:05 |             0
 2012-10-15 14:41:05 |             0
 2012-10-15 14:41:05 |             0
 2012-10-15 14:41:05 |             0
 2012-10-15 14:41:04 |             1
                     |
                     |
                     |
                     |
(12 rows)

以下查询使用 COUNT 和 AVG 函数来确定不同语言的文本的平均长度：

=> SELECT "user.lang", count (*), avg(length("text"))::int FROM darkdata1 GROUP BY 1 ORDER BY 2 DESC;
 user.lang | count | avg
-----------+-------+-----
 en        |     4 |  42
           |     4 |
 es        |     2 |  96
 it        |     1 |  50
 tr        |     1 |  16
(5 rows)

在查询中转换数据类型

以下查询请求了 created_at 虚拟列的值，而没有将其转换成特定的数据类型：

=> SELECT "created_at" FROM darkdata1;
           created_at
--------------------------------
 Mon Oct 15 18:41:04 +0000 2012
 Mon Oct 15 18:41:05 +0000 2012
 Mon Oct 15 18:41:05 +0000 2012
 Mon Oct 15 18:41:05 +0000 2012
 Mon Oct 15 18:41:05 +0000 2012
 Mon Oct 15 18:41:05 +0000 2012
 Mon Oct 15 18:41:05 +0000 2012
 Mon Oct 15 18:41:05 +0000 2012
(12 rows)

下一个示例查询了同一虚拟列，并将 created_at 转换成了 TIMESTAMP。转换会导致不同的输出和区域时间：

=> SELECT "created_at"::TIMESTAMP FROM darkdata1 ORDER BY 1 DESC;
     created_at
---------------------
 2012-10-15 14:41:05
 2012-10-15 14:41:05
 2012-10-15 14:41:05
 2012-10-15 14:41:05
 2012-10-15 14:41:05
 2012-10-15 14:41:05
 2012-10-15 14:41:05
 2012-10-15 14:41:04

访问 EPOCH 键

EPOCH（全部为大写字母）一词留供 Vertica 内部使用。

如果您的 JSON 数据包含名为 epoch 的虚拟列，您可以在 Flex 表内查询它。但是，请使用 MAPLOOKUP 函数执行此操作。

12 - 查询嵌套数据

如果您使用 flatten_arrays=FALSE（默认）加载 JSON 或 Avro 数据，则 __raw__ 列中的 VMap 数据可以包含多个嵌套结构。事实上，任何 VMap JSON 或 Avro 数据都可以包含嵌套结构。此部分描述如何以最好方式查询此类数据。

查询 VMap 嵌套值

要查询嵌套结构，您可以使用多个 maplookup() 函数，每个级别一个。但是，最有效的方法是使用括号 ([]) 运算符。

解析或提取 VMap 数据时，默认行为是修整数据。修整的 VMap 数据将键名连接成一个长名称，使用默认分隔符 (.) 或用户定义的分隔符分隔元素。

要对 VMap 数据中的嵌套结构使用括号运算符，数据不能被修整。此外，您不能对任何现有的修整 VMap 数据使用括号运算符。

要正确加载或提取 VMap 数据，请为 fjsonparser、favroparser 和 mapjsonextractor() 函数指定 flatten_maps=FALSE。

注意

虽然括号运算符值看起来类似于数组元素规范，但它们是字符串，而不是整数。您必须将每个嵌套结构作为字符串输入，即使值是整数。例如，如果值为 2，请将其指定为 ['2']，而不是 [2]。

嵌套 JSON 的括号运算符

此示例使用以下 JSON 数据作为嵌套数据的示例。将此数据另存为 restaurant.json：

{
  "restaurant" : {
      "_name_" : "Bob's pizzeria",
      "cuisine" : "Italian",
      "location" : {"city" : "Cambridge", "zip" : "02140"},
      "menu" : [{"item" : "cheese pizza", "price" : "$8.25"},
                {"item" : "chicken pizza", "price" : "$11.99"},
                {"item" : "spinach pizza", "price" : "$10.50"}]
  }
}

创建 Flex 表 rests，并使用 restaurant.json 文件加载它：

=> COPY rests FROM '/home/dbadmin/tempdat/restaurant.json' PARSER fjsonparser (flatten_maps=false);
 Rows Loaded
-------------
           1
(1 row)

将数据加载到 Flex 表后，有两种方法可以使用括号访问嵌套数据：

从 __raw__ 列开始，后跟括号中的字符值
从最顶层元素的名称开始，后跟括号中的字符值

两种方法同样有效。以下是两者的示例：

=> SELECT __raw__['restaurant']['location']['city'] FROM rests;
  __raw__
-----------
 Cambridge
(1 row)
=> SELECT restaurant['location']['city'] from rests;
 restaurant
------------
 Cambridge
(1 row)

推特数据的括号运算符

此示例展示如何从推特数据中提取一些基本信息。

创建 Flex 表 tweets 并加载一些数据后，Flex 表有一个推文块。

在下面的 SELECT 语句中，注意如何指定表 tweets 的 __raw__ 列（后跟括号运算符）来定义相关虚拟列 (['delete']['status']['user_id'])。此查询使用 COUNT() 函数计算已删除推文的数量并输出 10 个结果：

=> SELECT  __raw__['delete']['status']['user_id'] as UserId, COUNT(*) as TweetsDelete from tweets
-> WHERE mapcontainskey(__raw__, 'delete')
-> GROUP BY __raw__['delete']['status']['user_id']
-> ORDER BY TweetsDelete DESC, UserID ASC LIMIT 10;
  UserId   | TweetsDelete
-----------+--------------
 106079547 |            4
 403474369 |            4
 181188657 |            3
 223136123 |            3
 770139481 |            3
 154602299 |            2
 192127653 |            2
 215011332 |            2
 23321883  |            2
 242173898 |            2
(10 rows)

13 - 查询 Flex 视图

Flex 表通过应用查询重写提供动态架构的功能。使用 Flex 视图支持对 Flex 表的受限访问。与 Flex 表一样，您每次在内部对 Flex 表视图使用 select 查询，Vertica 均可调用 maplookup() 函数以返回有关所有虚拟列的信息。包含 __raw__ 列的任何 Flex 表或列式表均可发生这种查询行为。

以下示例演示了如何查询 Flex 视图：

创建 Flex 表。
```
=> CREATE FLEX TABLE twitter();
```

使用 fjsonparser 将 JSON 数据加载到 Flex 表中。

=> COPY twitter FROM '/home/dbadmin/data/flex/tweets_10000.json' PARSER fjsonparser();
Rows Loaded
-------------
10000
(1 row)

在 Flex 表 twitter 之上创建一个 Flex 视图，约束为 retweet_count>0。

=> CREATE VIEW flex_view AS SELECT __raw__ FROM twitter WHERE retweet_count::int > 0;
CREATE VIEW

查询该视图。显示前 5 行。

=> SELECT retweeted,retweet_count,source FROM (select __raw__ from flex_view) t1 limit 5;
retweeted | retweet_count |                                        source
-----------+---------------+--------------------------------------------------------------------------------------
F         | 1             | <a href="http://blackberry.com/twitter" rel="nofollow">Twitter for BlackBerry®</a>
F         | 1             | web
F         | 1             | <a href="http://twitter.com/download/iphone" rel="nofollow">Twitter for iPhone</a>
F         | 23            | <a href="http://twitter.com/download/android" rel="nofollow">Twitter for Android</a>
F         | 7             | <a href="http://twitter.com/download/iphone" rel="nofollow">Twitter for iPhone</a>
(5 rows)

14 - 列出 Flex 表

您可以通过查询 v_catalog.tables 系统表的 is_flextable 列，确定数据库中的哪些表为 Flex 表。例如，使用下面的查询来查看 is_flextable 列中具有 true (t) 值的所有表：

=> SELECT table_name, table_schema, is_flextable FROM v_catalog.tables;
     table_name      | table_schema | is_flextable
---------------------+--------------+-----------------
 bake1               | public       | t
 bake1_keys          | public       | f
 del                 | public       | t
 del_keys            | public       | f
 delicious           | public       | t
 delicious_keys      | public       | f
 bake                | public       | t
 bake_keys           | public       | f
 appLog              | public       | t
 appLog_keys         | public       | f
 darkdata            | public       | t
 darkdata_keys       | public       | f
(12 rows)

Flex 表

Flex 表术语

1 - 入门

创建 Flex 表并加载数据

更多地查询 Flex 表

构建 Flex 表视图

创建混合 Flex 表

实体化混合 Flex 表中的虚拟列

2 - 了解 Flex 表

存储 Flex 表数据

创建 Flex 表时会发生什么？

自动创建超投影

默认 Flex 表视图

Flex 函数

使用带 Flex 表的客户端

3 - 创建 Flex 表

创建基本 Flex 表

实体化 Flex 表虚拟列

从 Flex 表创建列式表

创建临时 Flex 表

创建外部 Flex 表

注意

从查询结果创建 Flex 表

注意

示例

4 - 将数据批量加载到 Flex 表中

基本 Flex 表加载和查询

注意

将数据加载到 Flex 表的实际列

注意

注意

在加载过程中处理默认值

使用 COPY 指定默认列值

5 - 将数据插入 Flex 表

将值插入 Flex 表

将 INSERT ...SELECT 与 Flex 表一起使用

将 raw 列插入 Flex 表

错误处理

另请参阅

6 - 将 Flex 表用于 IDOL 数据

适用于 CFS 的 ODBC 连接字符串

CFS COPY LOCAL 语句

7 - 使用 Flex 表解析器

7.1 - 使用 Flex 解析器加载列式表

注意

7.2 - 加载 CSV 数据

使用默认解析器设置

加载 CSV 数据 (RFC4180)

加载 CSV 数据（传统）

拒绝重复值

拒绝包含实体化列类型错误的数据

注意

拒绝或忽略空行

注意

使用 NULL 参数

处理列标题

7.3 - 加载分隔的数据

拒绝重复值

拒绝实体化列类型错误

8 - 计算 Flex 表键

注意

使用 COMPUTE_FLEXTABLE_KEYS

确定键数据类型

计算键值列宽

9 - 实体化 Flex 表

注意

将列添加到 Flex 表

添加具有默认值的列

注意

更改 raw 列大小

注意

更改 Flex 表实际列

注意

删除 Flex 表列

更新 Flex 表视图

10 - 更新 Flex 表视图

使用 BUILD_FLEXTABLE_VIEW

处理视图中的 JSON 重复键名

注意

创建 Flex 表视图