INFER_TABLE_DDL

检查 Parquet、ORC、JSON 或 Avro 格式的文件，并根据其内容返回 CREATE TABLE 或 CREATE EXTERNAL TABLE 语句。

如果输入数据包含不明确或未知的数据类型，则返回的语句可能不完整。它还可以包含更多列或名称长度超出 Vertica 支持范围的列；此函数不强制执行 Vertica 系统限制。使用它创建表之前，请始终检查输出并解决各类问题。

此函数支持从输入路径推断出的分区列。由于通过目录结构完成分区，所以可能不具备充足的信息来推断分区列的类型。在这种情况下，此函数显示这些列的数据类型为 UNKNOWN 并发出警告。

函数可处理大多数数据类型，包括复杂类型。如果 Vertica 不支持输入类型，则函数将发出警告。

对于 VARCHAR 和 VARBINARY 列，此函数不指定长度。这些类型的 Vertica 默认长度为 80 字节。如果数据值较长，则使用返回且未经修改的表定义可能会导致数据截断。始终查看 VARCHAR 和 VARBINARY 列，以确定是否需要指定长度。如果输入文件包含以下类型的列，则此函数将发出警告：

WARNING 9311: This generated statement contains one or more varchar/varbinary columns which default to length 80

这是元函数。您必须在顶级 SELECT 语句中调用元函数。

行为类型

易变

语法

INFER_TABLE_DDL( path USING PARAMETERS param=value[,...] )

参数

路径

文件或 glob 的路径。任何对 COPY 有效并使用此函数支持的文件格式的路径都有效。对于除 JSON 以外的所有格式，如果 glob 指定多个文件，则此函数将读取一个任意选择的文件。对于 JSON，函数可能会读取多个文件。请参阅 JSON。

参数

format

输入格式（字符串），"Parquet"、"ORC"、"Avro" 或 "JSON" 之一。此参数为必填。

table_name

要创建的表的名称。此参数为必填。

不要将架构名称作为表名的一部分；使用 table_schema 参数。

table_schema

在其中创建表的架构。如果忽略，则函数的输出中不包含架构。

table_type

要创建的表的类型，“原生”或“外部”。

默认值： “native”

with_copy_statement

对于原生表，是否在 CREATE TABLE 语句之外包含 COPY 语句。

默认值：false

one_line_result

是否将 DDL 作为单行返回，而不是整齐打印。单行格式可能更便于复制到 SQL 脚本中。

默认值： false（整齐打印）

max_files

（仅限 JSON。）如果 path 为 glob，则为 path 中要检查的最大文件数。使用此参数可增加函数考虑的数据量，例如，如果怀疑文件之间存在差异。文件从 glob 中任意选择而来。有关详细信息，请参阅 JSON。

默认值： 1

max_candidates

（仅限 JSON。）要显示的备选表定义的数量。函数仅为每个文件生成一个备选项，因此如果增加 max_candidates，还会增加 max_files。有关详细信息，请参阅 JSON。

默认值： 1

特权

非超级用户：对于用户可访问的存储位置的读取权限。

JSON

与其他受支持的格式不同，JSON 不会在数据文件中嵌入架构。此函数通过检查原始数据来推断 JSON 表 DDL。由于原始数据可能不明确或不一致，因此函数对此格式采用不同的方法。

对于每个输入文件，函数循环访问记录以开发备选表定义。任何记录中显示的顶级字段都将作为列包含在其中，即使并非所有记录都使用顶级字段也是如此。如果同一字段以不同类型显示到文件中，则函数选择与观察到的所有实例一致的类型。

假设一个文件包含餐厅数据：

{
    "name" : "Pizza House",
    "cuisine" : "Italian",
    "location_city" : [],
    "chain" : true,
    "hours" : [],
    "menu" : [{"item" : "cheese pizza", "price" : 7.99},
              {"item" : "spinach pizza", "price" : 8.99},
              {"item" : "garlic bread", "price" : 4.99}]
}
{
    "name" : "Sushi World",
    "cuisine" : "Asian",
    "location_city" : ["Pittsburgh"],
    "chain" : false,
    "menu" : [{"item" : "maki platter", "price" : "21.95"},
              {"item" : "tuna roll", "price" : "4.95"}]
}

第一条记录包含两个空数组，因此没有足够的信息来确定元素类型。第二条记录包含其中一个数组的字符串值，因此函数可为其推断出一种 VARCHAR 类型。其他数组元素类型仍然未知。

在第一条记录中，菜单价格为数字，但在第二条记录中，菜单价格为字符串。FLOAT 和字符串都可以强制转换为 NUMERIC，因此函数返回 NUMERIC：

=> SELECT INFER_TABLE_DDL ('/data/restaurants.json'
    USING PARAMETERS table_name='restaurants', format='json');
WARNING 0:  This generated statement contains one or more varchar/varbinary types which default to length 80

                INFER_TABLE_DDL
------------------------------------------------------------------------
 Candidate matched 1/1 of total files(s):
  create table "restaurants"(
    "chain" bool,
    "cuisine" varchar,
    "hours" Array[UNKNWON],
    "location_city" Array[varchar],
    "menu" Array[Row(
      "item" varchar,
      "price" numeric
    )],
    "name" varchar
  );

(1 row)

所有标量类型都可以强制转换为 VARCHAR，因此如果无法更明确地解决冲突（如在 NUMERIC 示例中），函数仍然可以返回类型。然而，并不总能通过这种方式对复杂类型进行解析。在下例中，文件中的记录包含相互冲突的 hours 字段定义：

{
    "name" : "Sushi World",
    "cuisine" : "Asian",
    "location_city" : ["Pittsburgh"],
    "chain" : false,
    "hours" : {"open" : "11:00", "close" : "22:00" }
}
{
    "name" : "Greasy Spoon",
    "cuisine" : "American",
    "location_city" : [],
    "chain" : "false",
    "hours" : {"open" : ["11:00","12:00"], "close" : ["21:00","22:00"] },
}

在第一条记录中，值为 ROW，包含两个 TIME 字段。在第二条记录中，值为 ROW，包含两个 ARRAY[TIME] 字段（代表工作日和周末时间）。这些类型相互不兼容，因此函数使用 LONG VARBINARY 推荐 灵活复杂类型：

=> SELECT INFER_TABLE_DDL ('/data/restaurants.json'
    USING PARAMETERS table_name='restaurants', format='json');
WARNING 0:  This generated statement contains one or more varchar/varbinary types which default to length 80

                INFER_TABLE_DDL
------------------------------------------------------------------------
 Candidate matched 1/1 of total files(s):
  create table "restaurants"(
    "chain" bool,
    "cuisine" varchar,
    "hours" long varbinary,
    "location_city" Array[varchar],
    "name" varchar
  );

(1 row)

如果使用 glob 调用函数，默认读取一个文件。将 max_files 设置为更大的数字，以检查更多的数据。函数为每个文件计算一个备选表定义，返回涵盖最多文件的定义。

增加文件数量本身并不会增加函数返回的备选项数量。文件越多，函数可以考虑的备选项越多，但默认情况下，返回代表最多文件的单个备选项。要查看多个可能的表定义，还要设置 max_candidates。将 max_candidates 设置为大于 max_files 没有任何益处。

在下例中，glob 包含两个文件，菜单列结构截然不同。在第一个文件中，菜单字段包含两个字段：

{
    "name" : "Bob's pizzeria",
    "cuisine" : "Italian",
    "location_city" : ["Cambridge", "Pittsburgh"],
    "menu" : [{"item" : "cheese pizza", "price" : 8.25},
              {"item" : "spinach pizza", "price" : 10.50}]
}

在第二个文件中，一天当中不同时段的菜品有所不同：

{
    "name" : "Greasy Spoon",
    "cuisine" : "American",
    "location_city" : [],
    "menu" : [{"time" : "breakfast",
           "items" :
           [{"item" : "scrambled eggs", "price" : "3.99"}]
          },
          {"time" : "lunch",
           "items" :
           [{"item" : "grilled cheese", "price" : "3.95"},
        {"item" : "tuna melt", "price" : "5.95"},
        {"item" : "french fries", "price" : "1.99"}]}]
}

要查看两个备选项，请同时提出 max_files 和 max_candidates：

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

示例

在下例中，输入路径包含具有两个整数列的表的数据。可以完全推断外部表定义，也可以按原样使用返回的 SQL 语句。函数从输入路径读取一个文件：

=> SELECT INFER_TABLE_DDL('/data/orders/*.orc'
    USING PARAMETERS format = 'orc', table_name = 'orders', table_type = 'external');

                INFER_TABLE_DDL
------------------------------------------------------------------------
create external table "orders" (
  "id" int,
  "quantity" int
) as copy from '/data/orders/*.orc' orc;
(1 row)

要在架构中创建表，请使用 table_schema 参数。不要将其添加到表名中；函数将其视为带有句点的名称，而不是架构。

以下示例显示了复杂类型的输出。您可以按原样使用定义，也可以修改 VARCHAR 大小：

=> SELECT INFER_TABLE_DDL('/data/people/*.parquet'
    USING PARAMETERS format = 'parquet', table_name = 'employees');
WARNING 9311:  This generated statement contains one or more varchar/varbinary columns which default to length 80

                INFER_TABLE_DDL
------------------------------------------------------------------------
 create table "employees"(
  "employeeID" int,
  "personal" Row(
    "name" varchar,
    "address" Row(
      "street" varchar,
      "city" varchar,
      "zipcode" int
    ),
    "taxID" int
  ),
  "department" varchar
 );
(1 row)

在下例中，输入文件的 "prods" 列中包含映射。您可以将映射读取为行数组：

=> SELECT INFER_TABLE_DDL('/data/orders.parquet'
    USING PARAMETERS format='parquet', table_name='orders');
WARNING 9311:  This generated statement contains one or more varchar/varbinary columns which default to length 80
                INFER_TABLE_DDL
------------------------------------------------------------------------
 create table "orders"(
  "orderkey" int,
  "custkey" int,
  "prods" Array[Row(
    "key" varchar,
    "value" numeric(12,2)
  )],
  "orderdate" date
 );
(1 row)

以下示例返回原生表定义和 COPY 语句，将表定义添加到一行，简化剪切和粘贴到脚本的过程：

=> SELECT INFER_TABLE_DDL('/data/orders/*.orc'
    USING PARAMETERS format = 'orc', table_name = 'orders',
                         table_type = 'native', with_copy_statement = true, one_line_result=true);

                INFER_TABLE_DDL
-----------------------------------------------------------------------
create table "orders" ("id" int, "quantity" int);
copy "orders" from '/data/orders/*.orc' orc;
(1 row)

在下例中，按区域对数据进行分区。函数无法推断数据类型，报告 UNKNOWN：

=> SELECT INFER_TABLE_DDL('/data/sales/*/*
    USING PARAMETERS format = 'orc', table_name = 'sales', table_type = 'external');
WARNING 9262:  This generated statement is incomplete because of one or more unknown column types. Fix these data types before creating the table
WARNING 9311:  This generated statement contains one or more varchar/varbinary columns which default to length 80

                INFER_TABLE_DDL
------------------------------------------------------------------------
 create external table "sales"(
  "orderkey" int,
  "custkey" int,
  "prodkey" Array[varchar],
  "orderprices" Array[numeric(12,2)],
  "orderdate" date,
  "region" UNKNOWN
 ) as copy from '/data/sales/*/*' PARTITION COLUMNS region orc;
(1 row)

在下例中，函数读取多个 JSON 文件，这些 JSON 文件呈现 menu 列的方式有所不同：

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