可变复杂类型

定义表时，可以使用强类型复杂类型来充分描述结构体和数组的任意组合。但是，有时您可能不希望：

  • 如果数据包含具有大量字段的结构体，而在查询中只需要使用其中几个字段，则不必枚举表 DDL 中的剩余部分。此外，如果完全指定复杂类型，则一组深度嵌套的结构体可能会超过表的嵌套限制。

  • 如果数据架构仍在演变，您可以推迟最终确定强类型 DDL。

  • 如果预计在数据中引入新字段，您可以使用可变类型来发现这些字段。另一方面，具有强类型的表会静默忽略这些值。有关使用可变类型发现新字段的示例，请参阅强类型和可变类型。

可变类型是一种将复杂或非结构化数据作为二进制 blob 存储在一个列中的方法，它允许访问该数据的各个元素。这与 Vertica 用于 Flex 表的方法相同，Flex 表支持加载非结构化或半结构化数据。在 Flex 表中，源中的所有数据都会加载到一个名为 __raw__ 的 VMap 列中。从此列中，您可以实体化其他列（例如 JSON 数据中的特定字段），或在查询中使用特殊查找函数直接从 __raw__ 列中读取值。

Vertica 对复杂类型会使用类似的方法。您可以使用表定义中的 ROW 和 ARRAY 类型来充分描述类型，也可以将复杂类型视为可变类型而不充分描述它。选择以这种方式进行处理的每种复杂类型都将成为其自己的 Flex 样式列。您不必像 Flex 表中那样使用一列包含所有数据；相反，您可以将任何复杂类型的列视为一个类似 Flex 的列，而无论它嵌套的其他类型有多深。

定义可变列

要使用可变复杂类型，请将列声明为 LONG VARBINARY。您可能还需要在解析器中设置其他参数，如解析器文档中所述。

下面我们来探讨包含餐厅表和以下列的 Parquet 文件：

  • name：varchar

  • cuisine 类型：varchar

  • location（城市）：数组[varchar]

  • menu：结构体数组，每个结构体都有选项名称和价格

这些数据包含两个复杂的列：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 表中的复杂列中的值。因为可变复杂类型和强类型的复杂类型需要不同的值来进行展平，所以不能在同一个加载操作中组合强类型和可变复杂类型。