手动同步查询优化器与 watsonx.data 元数据存储

关于本任务

为了提供优化的查询，查询优化器提取表定义和 Hive 以及Iceberg统计信息的数据，以便与 IBM® watsonx.data 中的MDS同步。您可以选择查询优化器必须可用的特定 Hive 和Iceberg表。建议生成 Hive 和Iceberg统计信息，并为主键和外部键添加标签列，以获得最佳结果。

激活 Query Optimizer 会自动同步连接到 Presto (C++) 引擎的目录的元数据。但是，如果出现以下情况，您需要执行以下步骤：

部署过程中无法访问或已损坏的目录或模式的元数据丢失。
对表格进行重大更改。
初始同步操作后会引入新表。
在激活后的自动同步过程中，一个间歇性问题导致无法同步表。

准备工作

要从 watsonx.data 同步表格，需要以下项目：

本主题中的指令现在可以使用增强功能从优化器控制面板管理统计更新来执行。目录的高级查询性能增强和优化功能。

请按照 watsonx.data 中的验证表同步程序，验证所有预期的表是否都已同步。
确定您watsonx.data需要查询优化器提供的 Hive和冰山表列表。
Hive识别主键和外部键列。
ANALYZEPresto （C++）中的 Hive 和 Iceberg 表用于生成 Hive 和 Iceberg 统计数据。
作为一项安全增强功能，只有拥有管理员权限的用户才能运行 ExecuteWxdQueryOptimizer 命令。
如果会话参数 is_query_rewriter_plugin_enabled 设置为 false，则无法执行 ExecuteWxdQueryOptimizer 命令。

过程

登录到 watsonx.data。
转到查询工作区。
跑过 ANALYZE 命令来自watsonx.data您想要同步的表的 Web 控制台来生成统计数据（统计数据包括行数、列名、数据大小、行数等）。
```
ANALYZE catalog_name.schema_name.table_name ;
```
运行以下命令，在查询优化器中使用传统元存储类型 watsonx-data 手动注册 Hive 和 Iceberg 目录的元存储属性：
```
ExecuteWxdQueryOptimizer 'CALL SYSHADOOP.REGISTER_EXT_METASTORE('<CATALOG_NAME>', '<ARGUMENTS>', ?, ?)';
```
a. 对于 watsonx.data 企业版，请同时为 Hive 和 Iceberg watsonx-data 目录使用传统元数据存储类型：
```
ExecuteWxdQueryOptimizer 'CALL SYSHADOOP.REGISTER_EXT_METASTORE('<CATALOG_NAME>','type=watsonx-data,uri=thrift://<THRIFT_URL>,use.SSL=true,auth.mode=PLAIN,ssl.cert=/secrets/external/ibm-lh-tls-secret/ca.crt,auth.plain.credentials=<USERNAME>:<PASSWORD>', ?, ?)
```
例如：
```
ExecuteWxdQueryOptimizer 'CALL SYSHADOOP.REGISTER_EXT_METASTORE('iceberg_data','type=watsonx-data,uri=thrift://ibm-lh-lakehouse-mds-thrift-svc.zen.svc.cluster.local:8380,use.SSL=true,auth.mode=PLAIN,ssl.cert=/secrets/external/ibm-lh-tls-secret/ca.crt,auth.plain.credentials=admin:password', ?, ?)';
```
- type- 连接的元存储类型。支持的值为：watsonx-data。
- watsonx.data <CATALOG_NAME>- 如基础架构管理器页面所示（区分大小写）。
- <THRIFT_URL>- 从基础架构管理器页面获取（点击目录）。
- auth.plain.credentials 中的 MDS 凭证 (<Username> 和 <Password>)- 必须在 watsonx.data 侧创建。参见《连接到 watsonx.data 》中的 OpenShift。如果元存储要求 PLAIN 身份验证，则必须以 username:password 或 ibmlhapikey_<username>:apikey 的格式指定凭据。密码安全地存储在软件密钥库中。
- auth.mode- 如果元存储需要身份验证，则指明要使用的身份验证模式。 auth.mode 必须设置为 "PLAIN"
- use.SSL- 如果元存储需要 SSL 连接，则必须为 true。
- <MDS certificate file path>- 此文件必须作为证书存储在容器 db2u 中，用于验证SSL连接。若SSL连接使用由知名CA（ DigiCert 如或 VeriSign ）签发的证书建立，则无需传递证书。默认情况下，MDS证书位于查询优化器中的路径 /secrets/external/ibm-lh-tls-secret/ca.crt 下。
  1. 运行以下命令来识别 db2u Query Optimizer head pod (OPT_POD)。
```
oc get pod | grep oaas-db2u
```
  2. 运行以下命令生成证书，将<OPT_POD>和 .
```
oc exec -it <OPT_POD> -c db2u -- bash -c "echo QUIT | openssl s_client -showcerts -connect <Metastore Thrift endpoint> | awk '/-----BEGIN    CERTIFICATE-----/           {p=1}; p; /-----END CERTIFICATE-----/ {p=0}' > /tmp/mds.pem"
```
b. 对于 watsonx.data 精简版 Hive，冰山表通过不同的元存储服务器类型进行管理。根据您的应用需求，您必须为Metastore Hive 服务器、Iceberg服务器或两者同时进行注册。
1. 在 watsonx.data 精简版中注册目录 Hive：
```
ExecuteWxdQueryOptimizer 'CALL SYSHADOOP.REGISTER_EXT_METASTORE('<CATALOG_NAME>','type=watsonx-data-hive,uri=https://<THRIFT_URL>/mds/thrift,use.SSL=true,auth.mode=PLAIN,ssl.cert=/secrets/external/ibm-lh-tls-secret/ca.crt,auth.plain.credentials=<USERNAME>:<PASSWORD>', ?, ?)';
```
  例如：
```
ExecuteWxdQueryOptimizer 'CALL SYSHADOOP.REGISTER_EXT_METASTORE('iceberg_data','type=watsonx-data,uri=thrift://ibm-lh-lakehouse-mds-thrift-svc.zen.svc.cluster.local:8380,use.SSL=true,auth.mode=PLAIN,ssl.cert=/secrets/external/ibm-lh-tls-secret/ca.crt,auth.plain.credentials=admin:password', ?, ?)';
```
  - type- 连接的元存储类型。对于管理 Hive 表的元存储，其值为：watsonx-data-hive。
  - watsonx.data <CATALOG_NAME>- 如基础架构管理器页面所示（区分大小写）。
  - <THRIFT_URL>- MDS watsonx.data 节省服务器的统一资源标识符。它必须以 https://. 开头。
  - auth.plain.credentials 中的 MDS 凭证 (<Username> 和 <Password>)- 必须在 watsonx.data 侧创建。参见《连接到》 watsonx.data 中的 OpenShift。如果元存储库需要PLAIN身份验证，凭据必须以或 username:password 格式 ibmlhapikey_<username>:apikey 指定。密码安全地存储在软件密钥库中。
  - auth.mode- 如果元存储需要身份验证，则指明要使用的身份验证模式。 auth.mode 必须设置为 PLAIN。
  - use.SSL- 如果元存储需要 SSL 连接，则必须为 true。
  - <MDS certificate file path>- 此文件必须作为证书存储在容器 db2u 中，用于验证SSL连接。若SSL连接使用由知名CA（ DigiCert 如或 VeriSign ）签发的证书建立，则无需传递证书。默认情况下，MDS证书可在查询优化器中的路径 /secrets/external/ibm-lh-tls-secret/ca.crt 下获取。
2. 从 watsonx.data 精简版注册冰山目录：
```
ExecuteWxdQueryOptimizer 'CALL SYSHADOOP.REGISTER_EXT_METASTORE('<CATALOG_NAME>','type=iceberg-rest,catalog.name=<CATALOG_NAME>,uri=https://<REST_URL>/mds/iceberg,auth.mode=basic,ssl.cert=/secrets/external/ibm-lh-tls-secret/ca.crt,auth.plain.credentials=<USERNAME>:<PASSWORD>', ?, ?)';
```
  例如：
```
ExecuteWxdQueryOptimizer 'CALL SYSHADOOP.REGISTER_EXT_METASTORE('iceberg_data','type=iceberg-rest,catalog.name=iceberg_data,uri=https://ibm-lh-lakehouse-mds-rest-svc.zen.svc.cluster.local:8180/mds/iceberg,auth.mode=basic,ssl.cert=/secrets/external/ibm-lh-tls-secret/ca.crt,auth.plain.credentials=admin:password', ?, ?)';
```
  - type- 连接的元存储类型。对于管理冰山表的元数据存储，其值为：iceberg-rest.
  - watsonx.data <CATALOG_NAME>- 如基础架构管理器页面所示（区分大小写）。
  - <THRIFT_URL>- 冰山 watsonx.data REST MDS 服务器的统一资源标识符 (URI)。 URI 必须以 https:// 开头，并包含 REST API 目录的基路径。对于 watsonx.data，其基路径为 /mds/iceberg。
  - auth.plain.credentials 中的 MDS 凭证 (<Username> 和 <Password>)- 必须在 watsonx.data 侧创建。参见《连接到》 watsonx.data 中的 OpenShift。如果元存储库需要PLAIN身份验证，凭据必须以或 username:password 格式 ibmlhapikey_<username>:apikey 指定。密码安全地存储在软件密钥库中。
  - auth.mode- 如果元存储需要身份验证，则指明要使用的身份验证模式。 auth.mode 必须设置为 PLAIN。
  - use.SSL- 如果元存储需要 SSL 连接，则必须为 true。
  - <MDS certificate file path>- 此文件必须作为证书存储在容器 db2u 中，用于验证SSL连接。若SSL连接使用由知名CA（ DigiCert 如或 VeriSign ）签发的证书建立，则无需传递证书。默认情况下，MDS证书可在查询优化器中的路径 /secrets/external/ibm-lh-tls-secret/ca.crt 下获取。
运行以下命令进行注册watsonx.data目录查询优化器：
```
ExecuteWxdQueryOptimizer 'CALL SYSHADOOP.REGISTER_EXT_METASTORE('<CATALOG_NAME>','type=watsonx-data,uri=thrift://<Metastore_Thrift_endpoint>,use.SSL=true,auth.mode=PLAIN,auth.plain.credentials=<Username>:<apikey>', ?, ?)';
```
- <CATALOG_NAME>- 如基础设施管理页面所示（区分大小写）。
- <Metastore_Thrift_endpoint>- 从基础设施管理器页面获取（点击目录）。
- MDS 证书（<Username>：<apikey> ）——必须在 watsonx.data 上创建。请参阅连接到 IBM Cloud 或 Amazon Web Services 上的 watsonx.data。
将目录注册到查询优化器中，watsonx.data 可将表同步到查询优化器，从而实现查询优化。每个目录都需要运行一次。
运行以下命令来同步目录中每个模式的表：
```
ExecuteWxdQueryOptimizer 'CALL SYSHADOOP.EXT_METASTORE_SYNC('<CATALOG_NAME>', '<SCHEMA_NAME>', '.*', '<SYNC MODE>', 'CONTINUE', 'OAAS')';
```
- <CATALOG_NAME>：需要同步的表所属的目录的名称。
- <SCHEMA_NAME>：需要同步的表所属架构的名称。
- <SYNC MODE>：SKIP 是一种同步模式，表示应该跳过已经定义的对象。REPLACE 是另一种同步模式，用于更新自上次同步以来已被修改的对象。
- CONTINUE：记录了错误，但如果要导入多个表，处理将继续。
同步完成后，输出将显示同步表的列表。同步表的总数必须是目录或模式中表数量的两倍。这是因为每个表都要同步两次。从外部元存储器到本地元存储器，再从本地元存储器到目录 Db2。

请按照《验证表同步》中的步骤，watsonx.data 在几分钟内验证同步操作。

识别目录和架构列表watsonx.data你需要查询优化器。

提供一个 SQL 文件来定义查询优化器使用。在 SQL 文件中，标识数据集中每个表的主键、外键和非空列（如果适用）。

例如，如果您有以下三个包含给定列的表，

雇员（EmployeeID,FirstName,LastName,部门、薪资）、部门（DepartmentID和DepartmentName），和EmployeeDepartmentMapping（MappingID,EmployeeID,和DepartmentI）。

跑过 ALTER table 命令定义约束：

-- NOT NULL
ExecuteWxdQueryOptimizer 'ALTER TABLE "catalog_name".schema_name.Employees ALTER COLUMN FirstName SET NOT NULL ALTER COLUMN LasttName SET NOT NULL ALTER COLUMN Salary SET NOT NULL ALTER COLUMN EmployeeID SET NOT NULL';
ExecuteWxdQueryOptimizer 'ALTER TABLE "catalog_name".schema_name.Departments ALTER COLUMN DepartmentName SET NOT NULL ALTER COLUMN DepartmentID SET NOT NULL ';
ExecuteWxdQueryOptimizer 'ALTER TABLE "catalog_name".schema_name.EmployeeDepartmentMapping ALTER COLUMN MappingID SET NOT NULL ';
-- PRIMARY KEY
ExecuteWxdQueryOptimizer 'ALTER TABLE "catalog_name".schema_name.Employees ADD PRIMARY KEY (EmployeeID) NOT ENFORCED';
ExecuteWxdQueryOptimizer 'ALTER TABLE "catalog_name".schema_name.Departments ADD PRIMARY KEY (DepartmentID) NOT ENFORCED';
ExecuteWxdQueryOptimizer 'ALTER TABLE "catalog_name".schema_name.EmployeeDepartmentMapping ADD PRIMARY KEY (MappingID) NOT ENFORCED';
-- FOREIGN KEY
ExecuteWxdQueryOptimizer 'ALTER TABLE "catalog_name".schema_name.EmployeeDepartmentMapping ADD FOREIGN KEY (EmployeeID) REFERENCES "catalog_name".schema_name.Employees(EmployeeID) NOT ENFORCED';
ExecuteWxdQueryOptimizer 'ALTER TABLE "catalog_name".schema_name.EmployeeDepartmentMapping ADD FOREIGN KEY (DepartmentID) REFERENCES "catalog_name".schema_name.Departments(DepartmentID) NOT ENFORCED';