这是本节的多页打印视图。
点击此处打印.
返回本页常规视图.
复杂类型
表可以包含复杂类型的列，包括嵌套的复杂类型。您可以在原生和外部表（包括 Flex 表）中使用 ROW (结构体)、ARRAY 和 SET 类型。集合仅限于标量类型的一维集合。可用于外部表的 MAP 类型有限，但您可以改用 ARRAY 和 ROW 来表示映射。选定的解析器支持加载具有复杂类型的数据。
您可以为 ARRAY 和 ROW 类型的异构组合定义列：包含数组字段的结构体或结构体数组。这些类型可以嵌套的最大嵌套深度为 100。
原生表存在的限制
原生表中使用的复杂类型除了其参考页面上列出的个别类型的限制外，还有一些限制：
-
原生表必须至少有一个基本类型或原生数组（基本类型的一维数组）的列。如果一个 Flex 表有真实的列，它也必须至少有一个满足这个限制的列。
-
复杂类型列不能在 ORDER BY 或 PARTITION BY 子句中使用，也不能用作 FILLER 列。
-
复杂类型列不能有 约束。
-
返回复杂类型的表达式不能用作投影列，并且投影不能按复杂类型的列进行分段或排序。
-
具有复杂类型列的表不能使用 DEFAULT 和 SET USING。
有关其他限制，请参阅 CREATE TABLE 和 ALTER TABLE。
从数据派生表定义
可以使用 INFER_TABLE_DDL 函数检查 Parquet、ORC、JSON 或 Avro 数据，并为表定义生成一个起点。此函数返回 CREATE TABLE 语句，它可能需要进一步编辑。对于函数无法推断数据类型的列，该函数会将类型标记为未知并发出警告。对于 VARCHAR 和 VARBINARY 列，可能需要调整长度。请始终查看函数返回的语句，但特别是对于具有很多列的表，使用该函数可以节省时间和精力。
Parquet、ORC 和 Avro 文件包含架构信息，但 JSON 文件并不包含。对于 JSON，该函数检查原始数据以生成一个或多个候选表定义。在以下示例中，两个输入文件的菜单列结构不同：
=> SELECT INFER_TABLE_DDL ('/data/*.json'
USING PARAMETERS table_name='restaurants', format='json',
max_files=3, max_candidates=3);
WARNING 0: This generated statement contains one or more float types which might lose precision
WARNING 0: This generated statement contains one or more varchar/varbinary types which default to length 80
INFER_TABLE_DDL
------------------------------------------------------------------------
Candidate matched 1/2 of total files(s):
create table "restaurants"(
"cuisine" varchar,
"location_city" Array[varchar],
"menu" Array[Row(
"item" varchar,
"price" float
)],
"name" varchar
);
Candidate matched 1/2 of total files(s):
create table "restaurants"(
"cuisine" varchar,
"location_city" Array[varchar],
"menu" Array[Row(
"items" Array[Row(
"item" varchar,
"price" numeric
)],
"time" varchar
)],
"name" varchar
);
(1 row)
强类型的替代方案
尽管您可以完全指定表示 ROW 和 ARRAY 类型的任意组合的列，但在某些情况下您可能更喜欢更灵活的方法。如果数据包含具有数百个字段的结构体，而您只需要其中几个字段，则您可能更愿意在查询时仅提取这几个字段，而不是定义所有字段。同样，如果数据结构可能发生更改，您可能更愿意推迟完全指定复杂类型。您可以使用可变列来替代完全指定复杂列的结构。这与用于 Flex 表的方法相同，该表中的所有数据最初都会加载到一个二进制列中，然后根据需要从该位置实体化。有关使用此方法的详细信息，请参阅可变复杂类型。
1 - 结构体
列可以包含用于存储（类型化）属性-值对的结构体。例如，地址列可以使用带有街道地址、城市/州或省和邮政编码字符串的结构体，例如 { "street":"150 Cambridgepark Dr.", "city":"Cambridge MA", "postalcode":"02140"}。（这是一种 Hive 显示格式，而不是字面意思上存储在数据文件中的内容。）结构体字段可以是基元类型或其他复杂类型。
使用 ROW 表达式定义结构体列。在以下示例中，数据包含客户姓名、地址和帐号列，而地址是数据中的结构体。您在 Vertica 中声明的类型必须与加载到其中的数据类型兼容。
=> CREATE TABLE customers (
name VARCHAR,
address ROW(street VARCHAR, city VARCHAR, zipcode INT),
accountID INT);
在 ROW 中，您可以使用与为列指定字段及数据类型相同的语法来指定字段及其数据类型。出于查询目的，Vertica 会将 ROW 视为单个列。
结构体可以包含其他结构体。在以下示例中，员工拥有各种个人信息，包括本身就是结构体的地址。
=> CREATE TABLE employees(
employeeID INT,
personal ROW(
name VARCHAR,
address ROW(street VARCHAR, city VARCHAR, zipcode INT),
taxID INT),
department VARCHAR);
结构体可以包含基元类型、数组或结构体的数组。
=> CREATE TABLE customers(
name VARCHAR,
contact ROW(
street VARCHAR,
city VARCHAR,
zipcode INT,
email ARRAY[VARCHAR]
),
accountid INT );
在定义外部表时，Vertica 要求表的定义与外部数据的架构相匹配。例如，对于上面员工示例中使用的数据，以下定义是错误的：
=> CREATE EXTERNAL TABLE employees(
employeeID INT,
personal ROW(
name VARCHAR,
address ROW(street VARCHAR, city VARCHAR),
zipcode INT,
taxID INT),
department VARCHAR)
AS COPY FROM '...' PARQUET;
ERROR 9151: Datatype mismatch [...]
数据包含具有三个字段（街道、城市、邮政编码）的地址结构体，因此外部表也必须使用具有三个字段的 ROW。将 ROW 更改为具有两个字段并将其中一个字段提升为父 ROW 是不匹配的。每个 ROW 都必须匹配，如果结构体嵌套在数据中，则完整的结构必须匹配。
对于原生表，您可以指定要从数据中加载的列，因此您无需使所有列都匹配。对于加载的列，表的定义必须与数据文件中的架构相匹配。
处理 null 值
如果结构体存在但字段值为 null，Vertica 会在 ROW 中将 NULL 作为其值。所有字段均为 null 的结构体将被视为具有 null 字段的 ROW。如果结构体本身为 null，Vertica 会将 ROW 读取为 NULL。
查询
请参阅行（结构）。
限制
ROW 列有几个限制：
-
最大嵌套深度为 100。
-
Vertica 表最多支持 9800 个列和字段。不计算 ROW 本身，只计算其字段。
-
ROW 列不能使用任何约束（例如 NOT NULL）或默认值。
-
ROW 字段不能是 auto_increment 或 setof。
-
ROW 定义必须至少包含一个字段。
-
“Row”是 ROW 定义中的保留关键字，但允许作为表或列的名称。
-
不能使用 ALTER TABLE...ALTER COLUMN 修改 ROW 列。
-
包含 ROW 列的表也不能包含标识、自动增量、默认、SET USING 或序列列。
2 - 数组
列可以包含数组，这些数组存储相同类型元素的有序列表。例如，地址列可以使用字符串数组来存储个人可能拥有的多个地址，例如 ['668 SW New Lane', '518 Main Ave', '7040 Campfire Dr']。
数组有两种类型：
使用 ARRAY 类型定义数组列，以指定其元素的类型（基元类型、ROW（结构体）或数组）：
=> CREATE TABLE orders
(orderkey INT,
custkey INT,
prodkey ARRAY[VARCHAR(10)],
orderprices ARRAY[DECIMAL(12,2)],
orderdate DATE
);
如果数组是多维数组，则将其表示为包含数组的数组：
ARRAY[ARRAY[FLOAT]]
查询
请参阅数组和集（集合）。
限制
-
原生数组仅支持原始类型的数据，例如 int、UUID 等。
-
强制执行数组维数。列不能包含不同维度的数组。例如，包含三维数组的列只能包含其他三维数组；它不能同时包含一个一维数组。但是，一列中的数组大小可能不同，其中一个数组可以包含四个元素，而另一个数组包含十个元素。
-
如果指定了数组边界，则对所有加载或更改数据的操作强制执行。无界数组可能包含与分配的二进制大小一样多的元素。
-
数组具有最大二进制大小。如果在定义数组时未设置此大小，则使用默认值。
-
数组不支持 LONG 类型（如 LONG VARBINARY 或 LONG VARCHAR）或用户定义类型（如 Geometry）。
3 - 可变复杂类型
定义表时，可以使用强类型复杂类型来充分描述结构体和数组的任意组合。但是，有时您可能不希望：
-
如果数据包含具有大量字段的结构体，而在查询中只需要使用其中几个字段，则不必枚举表 DDL 中的剩余部分。此外，如果完全指定复杂类型，则一组深度嵌套的结构体可能会超过表的嵌套限制。
-
如果数据架构仍在演变，您可以推迟最终确定强类型 DDL。
-
如果预计在数据中引入新字段，您可以使用可变类型来发现这些字段。另一方面，具有强类型的表会静默忽略这些值。有关使用可变类型发现新字段的示例，请参阅强类型和可变类型。
可变类型是一种将复杂或非结构化数据作为二进制 blob 存储在一个列中的方法，它允许访问该数据的各个元素。这与 Vertica 用于 Flex 表的方法相同，Flex 表支持加载非结构化或半结构化数据。在 Flex 表中，源中的所有数据都会加载到一个名为 __raw__ 的 VMap 列中。从此列中，您可以实体化其他列（例如 JSON 数据中的特定字段），或在查询中使用特殊查找函数直接从 __raw__ 列中读取值。
Vertica 对复杂类型会使用类似的方法。您可以使用表定义中的 ROW 和 ARRAY 类型来充分描述类型，也可以将复杂类型视为可变类型而不充分描述它。选择以这种方式进行处理的每种复杂类型都将成为其自己的 Flex 样式列。您不必像 Flex 表中那样使用一列包含所有数据；相反，您可以将任何复杂类型的列视为一个类似 Flex 的列，而无论它嵌套的其他类型有多深。
定义可变列
要使用可变复杂类型，请将列声明为 LONG VARBINARY。您可能还需要在解析器中设置其他参数，如解析器文档中所述。
下面我们来探讨包含餐厅表和以下列的 Parquet 文件：
这些数据包含两个复杂的列：location（数组）和 menu（结构体数组）。以下示例将使用 LONG VARBINARY 将这两个列定义为可变列：
=> CREATE EXTERNAL TABLE restaurants(name VARCHAR, cuisine VARCHAR,
location_city LONG VARBINARY, menu LONG VARBINARY)
AS COPY FROM '/data/rest*.parquet'
PARQUET(allow_long_varbinary_match_complex_type='True');
allow_long_varbinary_match_complex_type 参数特定于 Parquet 解析器。如果将任何列定义为可变类型，则该列为必需列。如果不使用此参数，Vertica 会尝试将表中的 LONG VARBINARY 声明与 Parquet 文件中的 VARBINARY 列相匹配，改为查找复杂类型，然后报告数据-类型不匹配。
您不需要将所有复杂列都视为可变类型。以下定义同样有效：
=> CREATE EXTERNAL TABLE restaurants(
name VARCHAR, cuisine VARCHAR,
location_city ARRAY[VARCHAR,50],
menu LONG VARBINARY)
AS COPY FROM '/data/rest*.parquet'
PARQUET(allow_long_varbinary_match_complex_type='True');
对于许多常见数据格式，可以使用 INFER_TABLE_DDL 函数从数据文件中派生表定义。此函数几乎在所有情况下都对复杂类型使用强类型。
查询可变列
可变列将存储为 LONG VARBINARY，因此直接选择它们会产生无用的结果。相反，应使用 Flex 映射函数从这些列中提取值。MAPTOSTRING 函数会将复杂类型转换为 JSON，如以下示例所示：
=> SELECT name, location_city, MAPTOSTRING(menu) AS menu FROM restaurants;
name | location_city | menu
-------------------+---------+------------------+----------------------------------------
Bob's pizzeria | ["Cambridge","Pittsburgh"] | {
"0": {
"item": "cheese pizza",
"price": "$8.25"
},
"1": {
"item": "spinach pizza",
"price": "$10.50"
}
}
Bakersfield Tacos | ["Pittsburgh"] | {
"0": {
"item": "veggie taco",
"price": "$9.95"
},
"1": {
"item": "steak taco",
"price": "$10.95"
}
}
(2 rows)
menu 列是结构体数组。请注意，输出是一组键/值对，其中键为数组索引。Bob's Pizzeria 的菜单上有两个选项，每个值都是结构体。第一个选项 ("0") 是结构体，"item" 值为 "cheese pizza"，"price" 为 "$8.25"。
您可以使用键来访问特定值。以下示例将从每个餐厅中选择第一个菜单项。请注意，所有键都是字符串，甚至是数组索引：
=> SELECT name, location_city, menu['0']['item'] AS item, menu['0']['price'] AS price FROM restaurants;
name | location_city | item | price
-------------------+----------------------------+--------------+-------
Bob's pizzeria | ["Cambridge","Pittsburgh"] | cheese pizza | $8.25
Bakersfield Tacos | ["Pittsburgh"] | veggie taco | $9.95
(2 rows)
您可以在子查询中使用 MAPITEMS 函数来激增可变类型，而不是访问特定索引，如以下示例所示：
=> SELECT name, location_city, menu_items['item'], menu_items['price']
FROM (SELECT mapitems(menu, name, location_city) OVER(PARTITION BEST)
AS (indexes, menu_items, name, location_city)
FROM restaurants) explode_menu;
name | location_city | menu_items | menu_items
-------------------+----------------------------+---------------+------------
Bob's pizzeria | ["Cambridge","Pittsburgh"] | cheese pizza | $8.25
Bob's pizzeria | ["Cambridge","Pittsburgh"] | spinach pizza | $10.50
Bakersfield Tacos | ["Pittsburgh"] | veggie taco | $9.95
Bakersfield Tacos | ["Pittsburgh"] | steak taco | $10.95
(4 rows)
有关 Flex 映射函数的完整列表，请参阅 Flex 数据函数。
JSON 和 avro 可变类型
JSON 和
Avro 的解析器支持复杂类型的可变类型和强类型。当使用可变复杂类型或加载到 Flex 表中时，请使用 flatten_maps 和 flatten_arrays 参数来控制解析器处理复杂数据的方式。对于强类型的复杂类型，这些解析器会忽略这些参数。
以下示例演示了如何使用可变复杂类型。下面我们来探讨包含以下数据的 JSON 文件：
{
"name" : "Bob's pizzeria",
"cuisine" : "Italian",
"location_city" : ["Cambridge", "Pittsburgh"],
"menu" : [{"item" : "cheese pizza", "price" : "$8.25"},
{"item" : "spinach pizza", "price" : "$10.50"}]
}
{
"name" : "Bakersfield Tacos",
"cuisine" : "Mexican",
"location_city" : ["Pittsburgh"],
"menu" : [{"item" : "veggie taco", "price" : "$9.95"},
{"item" : "steak taco", "price" : "$10.95"}]
}
创建一个表（使用 LONG VARBINARY 表示可变复杂类型），然后加载指定这些参数的数据：
=> CREATE TABLE restaurant(name VARCHAR, cuisine VARCHAR,
location_city LONG VARBINARY, menu LONG VARBINARY);
=> COPY restaurant FROM '/data/restaurant.json'
PARSER FJSONPARSER(flatten_maps=false, flatten_arrays=false);
您可以使用 Flex 函数和直接访问（通过索引）来返回可读值：
=> SELECT MAPTOSTRING(location_city), MAPTOSTRING(menu) FROM restaurant;
maptostring | maptostring
-------------------------------------------------+--------------------------------------------------------
{
"0": "Cambridge",
"1": "Pittsburgh"
} | {
"0": {
"item": "cheese pizza",
"price": "$8.25"
},
"1": {
"item": "spinach pizza",
"price": "$10.50"
}
}
{
"0": "Pittsburgh"
} | {
"0": {
"item": "veggie taco",
"price": "$9.95"
},
"1": {
"item": "steak taco",
"price": "$10.95"
}
}
(2 rows)
=> SELECT menu['0']['item'] FROM restaurant;
menu
--------------
cheese pizza
veggie taco
(2 rows)
此示例中显示的 COPY 语句将 flatten_maps 设置为 false。如果没有该更改，复杂列的键将无法按预期工作，因为记录和数组键将在顶层“展平”。查询菜单 ['0']['item'] 不会产生任何结果。相反，查询展平值，如下例所示：
=> SELECT menu['0.item'] FROM restaurant;
menu
--------------
veggie taco
cheese pizza
(2 rows)
展平指令适用于整个 COPY 语句。您不能展平某些列而不展平其他列，或者防止展平本身位于展平 Flex 表中的复杂列中的值。因为可变复杂类型和强类型的复杂类型需要不同的值来进行展平，所以不能在同一个加载操作中组合强类型和可变复杂类型。
4 - 复杂类型的系统表
所有复杂类型的信息都记录在 COMPLEX_TYPES 系统表中。您必须对使用某种类型的外部表具有读取权限，才能在此系统表中查看其条目。TYPES 系统表中未显示复杂类型。
对于 ROW 类型，COMPLEX_TYPES 中的每一行代表一个 ROW 的一个字段。字段名称是表定义中使用的名称（如果存在），否则是生成的以 _field 开头的名称。每行还包括其包含类型的（生成的）名称，即一个以 ct 开头的字符串。（"CT" 代表“复杂类型”。）
以下示例定义一个外部表，然后显示 COMPLEX_TYPES 中的类型：
=> CREATE EXTERNAL TABLE warehouse(
name VARCHAR, id_map MAP<INT,VARCHAR>,
data row(record INT, total FLOAT, description VARCHAR(100)),
prices ARRAY[INT], comment VARCHAR(200), sales_total FLOAT, storeID INT)
AS COPY FROM ... PARQUET;
=> SELECT type_id,type_kind,type_name,field_id,field_name,field_type_name,field_position
FROM COMPLEX_TYPES ORDER BY type_id,field_name;
type_id | type_kind | type_name | field_id | field_name | field_type_name | field_position
-------------------+-----------+-----------------------+----------+-------------+-----------------+----------------
45035996274278280 | Map | _ct_45035996274278280 | 6 | key | int | 0
45035996274278280 | Map | _ct_45035996274278280 | 9 | value | varchar(80) | 1
45035996274278282 | Row | _ct_45035996274278282 | 9 | description | varchar(80) | 2
45035996274278282 | Row | _ct_45035996274278282 | 6 | record | int | 0
45035996274278282 | Row | _ct_45035996274278282 | 7 | total | float | 1
45035996274278284 | Array | _ct_45035996274278284 | 6 | | int | 0
(6 rows)
此表显示表中定义的两种 ROW 类型的字段。当一个 ROW 包含另一个 ROW 时（就像这里嵌套地址字段的情况一样），field_type_name 列使用包含的 ROW 的生成名称。去掉前导 "ct" 后的数字用作 field_id。