Vertica 和 Data-Planet Web Service：技术探索

编译：JiangChong

关于本文档

本文档解释如何使用 Data-Planet Web Services 将公共数据包含到 Vertica 分析数据库中。可以从 Data-Planet 仓库提取第三方数据集，如人口普查、金融和交通数据。可以将这些数据集加载到 Vertica 中以改进数据库分析。

Data-Planet 概述

Data-Planet 是一个 Web 服务 (SaaS)，提供对各种组织的数据集仓库的访问。仓库中的数据按时间顺序组织和结构化，也称为时间序列数据。这种按时间组织的结构允许用户比较和对比不同来源的数据，并运行可视化以解释相关性。仓库中的数据随着新数据集的添加而定期增长。Data-Planet 在源端信息变化时保持数据更新。

我们测试了在云端将 Data-Planet 数据集加载到 Vertica 7.2.x 中。

请求 Data-Planet

Data Planet 产品和服务可通过 SAGE Publishing 获取。要请求演示或试用 Data-Planet，请联系 SAGE：访问 Data-Planet 网站，点击 Contact Us。

在 Vertica 中使用 Data-Planet 数据集

本文档讨论在 Vertica 中使用 Data-Planet 数据集进行分析的两个主要用例：

使用 Data-Planet 托管服务

将 Vertica 数据托管在 Data-Planet 仓库中。然后使用 Data-Planet 界面将您的数据与统计数据集集合进行比较和关联。Data-Planet 团队将与您合作将数据加载到仓库中，并在数据集级别控制对信息的访问，确保只有您组织中的人员可以看到数据。

包含 Data-Planet 数据集

将公共数据包含到 Vertica 分析中，将数据集从 Data-Planet 仓库导出为 JSON 和 CSV 格式。然后将这些数据集导入到 Vertica 数据库中。

从 Data-Planet 导出数据到 Vertica 有两种方式： 1. 使用 Data-Planet 用户界面导出数据集 2. 使用 Data-Planet REST API 导出数据集

Data-Planet 数据集可以导出为 Excel、CSV、XML 和 JSON 格式。推荐使用 CSV 或 JSON 格式，原因如下：

• JSON 格式的数据可以使用 COPY 命令和 JSON 解析器 (fjsonparser) 加载到列式表或 flex 表中
• CSV 格式的数据可以使用 CSV 解析器 (fcsvparser) 加载
• Vertica 不能直接使用 COPY 命令加载 XML 文件，也没有 XML 格式的解析器

使用 Data-Planet 用户界面导出数据集

Data-Planet 用户界面允许从数据集之间的关联下载结果集。要下载 Excel、CSV 或 XML 格式的聚合数据，点击 Data-Planet 界面上的 Export 图标。推荐以 CSV 格式下载结果集。

注：Vertica 不能直接使用 COPY 命令加载 XML 文件。如果有 XML 文件，必须使用应用程序将其转换为 JSON 或 CSV 格式。

使用 Data-Planet REST API 导出数据集

Data-Planet REST API 允许导出 Data-Planet 仓库中包含的数据集。也可以下载数据集的数据子集和描述数据集的元数据。可以使用 REST API 以 XML 或 JSON 格式导出数据。推荐使用 JSON 格式。注意 REST API 不支持将数据摄取到仓库中。

使用 REST API 导出数据集时的注意事项：

• 评估版本的最大记录数为 1000。如果需要更多记录，必须联系 Data-Planet 团队请求增加
• 一次 API 调用只能访问一个指标的数据
• API 调用不能用于导出数据集之间的关联
• 如果 API 索引未更新，通过 API 看到的数据可能与用户界面中的数据不匹配

Data-Planet 有三个 REST API 调用可用：

• Database API Call：识别仓库中数据集和文档的元数据信息
• Dataset API Call：基于输入的文本搜索整个仓库中的数据集和文档
• Data API Call：从仓库中的数据集中提取数据子集

参数总结

通用请求和查询参数

参数	描述
q	搜索的主查询，支持原始输入字符串。引号括起的文本被视为短语
start	分页结果，第一条记录偏移量为 0
rows	请求返回的最大文档数，最大支持 1000
fl	字段列表，指定要返回的字段集。用空格、逗号或 + 分隔，* 表示所有字段
wt	输出格式，XML 或 JSON，默认为 XML
defType	指定查询解析器处理查询的方式

Facet 参数

设置 facet=true 可启用 facet 计数：

参数	描述
facet.field	被视为 facet 以生成计数的字段，可指定多个
facet.mincount	响应中包含的 facet 字段的最小计数，默认为 0
facet.limit	返回的 facet 字段的最大约束数，负值表示无限，默认为 100

Extended DisMax (eDisMax) 查询解析器参数

参数	描述
q	查询的原始输入字符串
qf	查询字段参数，指定要执行查询的索引字段
df	默认字段参数，覆盖 SchemaXML 中定义的默认字段

Data API 调用

数据网络服务调用以 XML 或 JSON 格式返回 Data-Planet 仓库中的数据子集。XML 是默认格式，但推荐与 Vertica 配合使用 JSON。至少需要以下参数：

• database_id：仓库中数据集的唯一代码
• datetype_id：结果中使用的日期类型
• geo_id：结果中使用的数据层次组织

将 Data-Planet 数据集加载到 Vertica

使用 Flex Tables 导入

Vertica flex tables 功能允许加载数据而无需定义列或 schema。Flex tables 支持 SQL 查询和内置分析，与常规表一样。

JSON 数据加载示例：

1. 使用 database API 调用导出元数据，保存为 AirlineOnTimeMetadata.json。
2. 使用 data API 调用导出数据子集，保存为 AirlineOnTimeData.json。
3. 创建 flex 表：

   => CREATE FLEX TABLE DataPlanet.AirlineOnTimeMetadata();
   => CREATE FLEX TABLE DataPlanet.AirlineOnTimeData();
   

4. 使用 fjsonparser 加载元数据：

   => COPY DataPlanet.AirlineOnTimeMetadata FROM '/home/dbadmin/data/flex/AirlineOnTimeMetadata.json'
      PARSER fjsonparser(flatten_arrays=TRUE, start_point='docs', reject_on_duplicate=true);
   

5. 加载数据：

   => COPY DataPlanet.AirlineOnTimeData FROM '/home/dbadmin/data/flex/AirlineOnTimeData.json'
      PARSER fjsonparser(flatten_arrays=TRUE, start_point='row', reject_on_duplicate=true);
   

6. 查询元数据中的列：

   => SELECT source, database_id, database_name, "datatype_name.0" FROM DataPlanet.AirlineOnTimeMetadata;
   

7. 关联查询数据和元数据，查询各航空公司月度准点率：

   => SELECT data.database_id, metadata.database_name, data.geo_name Airline, 
             data.datedata YearMonth, SUM(data."value._text")
      FROM DataPlanet.AirlineOnTimeData data
      JOIN DataPlanet.AirlineOnTimeMetadata metadata ON (data.database_id = metadata.database_id)
      GROUP BY 1,2,3,4 ORDER BY 1;
   

8. 使用 COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW 填充 _keys 和 _view 表：

   => SELECT COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW('DataPlanet.AirlineOnTimeMetadata'),
             COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW('DataPlanet.AirlineOnTimeData');
   

9. 查询 flex table 视图：

   => SELECT * FROM DataPlanet.AirlineOnTimeMetadata_view;
   => SELECT * FROM DataPlanet.AirlineOnTimeData_view;
   

CSV 数据加载示例：

1. 创建 flex 表：

   => CREATE FLEX TABLE DataPlanet.AirlineOnTimeCSV();
   

2. 使用 fcsvparser 加载（遵循 RCF4180 标准 CSV 格式，逗号分隔，双引号括起）：

   => COPY DataPlanet.AirlineOnTimeCSV FROM '/home/dbadmin/data/flex/AirlineOnTimeCSV.csv'
      PARSER fcsvparser(reject_on_duplicate=true);
   

3. 使用 fdelimitedparser 加载（需要先去除 CSV 文件中的双引号）：

   => COPY DataPlanet.AirlineOnTimeCSV FROM '/home/dbadmin/data/flex/AirlineOnTimeCSV.csv'
      PARSER fdelimitedparser(delimiter=',', reject_on_duplicate=true);
   

使用 COPY 语句导入

SQL COPY 语句从存储在节点上的文件批量加载数据到数据库。也可以使用 COPY LOCAL 选项从客户端文件流式传输数据。

服务器端 COPY 示例：

=> COPY dataplanet.AirlineOnTimeTest FROM '/home/dbadmin/data/AirlineOnTimeData.csv'
   SKIP 1 DELIMITER ',' ENCLOSED BY '"'
   REJECTED DATA '/home/dbadmin/data/REJECTIONS'
   EXCEPTIONS '/home/dbadmin/data/EXCEPTIONS' DIRECT;

使用 fcsvparser：

=> COPY dataplanet.AirlineOnTimeTest FROM '/home/dbadmin/data/AirlineOnTimeData.csv'
   PARSER fcsvparser(reject_on_duplicate=true);

使用 fjsonparser：

=> COPY dataplanet.AirlineOnTimeTest FROM '/home/dbadmin/data/AirlineOnTimeData.json'
   PARSER fjsonparser(flatten_arrays=TRUE, start_point='row', reject_on_duplicate=true);

COPY LOCAL 示例（客户端本地文件）：

=> COPY dataplanet.AirlineOnTimeTest FROM LOCAL 'C:\Users\Administrator\Downloads\AirlineOnTimeData.csv'
   SKIP 1 DELIMITER ',' ENCLOSED BY '"'
   REJECTED DATA 'C:\Users\Administrator\Downloads\REJECTIONS'
   EXCEPTIONS 'C:\Users\Administrator\Downloads\EXCEPTIONS' DIRECT;

使用 ETL 工具导入

可以使用与 Vertica 集成的 ETL 工具将数据加载到数据库中。这些产品使用 Vertica JDBC 或 ODBC 驱动程序连接到 Vertica。定义 ETL 流程使用 Data-Planet API 从仓库拉取数据集，然后将输出加载到数据库中。也可以使用 ETL 工具加载 CSV 或 JSON 格式的数据。将工具配置为使用 COPY 语句高效地流式传输数据。连接中的 Vertica 用户必须是超级用户，或具有 schema 的 USAGE 权限和表的 INSERT 权限。

---

原文来源：https://www.vertica.com/kb/DataPlanet/Content/Partner/DataPlanetWebService.htm