Query Optimizer を watsonx.data メタストアと手動で同期する

このタスクについて

最適化されたクエリを提供するために 、Query Optimizerはテーブル定義と Hive およびIcebergの統計に関するデータを取得し、 IBM® watsonx.data のMDSと同期させます。 Query Optimizer で使用可能にする必要がある特定の Hive およびIcebergテーブルを選択できます。最良の結果を得るには Hive とIcebergの統計を生成し、主キーと外部キーの列にラベルを付けることをお勧めします。

Query Optimizer を有効にすると、Presto (C++) エンジンに接続されているカタログのメタデータが自動的に同期されます。ただし、以下の場合は以下の手順を実行する必要がある：

展開中にアクセスできなかったり、破損したりしたカタログやスキーマのメタデータが見つからない。
テーブルに大幅な変更が加えられた。
新しいテーブルは最初の同期操作の後に導入される。
アクティベーション時の自動同期プロセスで、テーブルが同期されないという断続的な問題がありました。

開始前に

watsonx.data からテーブルを同期するには、以下の項目が必要です

オプティマイザ・ダッシュボードから統計更新を管理する機能が強化され、複数のカタログにわたる高度なクエリ・パフォーマンスの向上と最適化機能が可能になりました。カタログにまたがる高度なクエリ・パフォーマンスの向上と最適化機能を可能にします。

watsonx.data の「テーブル同期の確認」の手順に従って、すべての期待されるテーブルが同期されていることを確認してください。
Query Optimizerに必要な watsonx.dataの HiveとIcebergテーブルのリストを特定します。
Hiveのカラムを主キーと外部キーとして識別する。
ANALYZEHive および Iceberg の統計を生成するための Presto (C++) の Hive および Iceberg テーブル。
セキュリティ強化機能として、ExecuteWxdQueryOptimizer コマンドの実行は管理者権限を持つユーザーのみに許可されています。
セッション・パラメータ is_query_rewriter_plugin_enabled が false に設定されている場合、 ExecuteWxdQueryOptimizer コマンドを実行することはできない。

手順

watsonx.data にログインします。
クエリワークスペースに移動します。
実行 ANALYZE からの命令watsonx.data統計を生成するために同期するテーブルの Web コンソール (統計は行数、列名、data_size、行数など)。
```
ANALYZE catalog_name.schema_name.table_name ;
```
以下のコマンドを実行して、 Hive と Iceberg カタログの両方について、レガシーメタストア・タイプ watsonx-data を使用して、 Query Optimizer にカタログのメタストア・プロパティを手動で登録します：
```
ExecuteWxdQueryOptimizer 'CALL SYSHADOOP.REGISTER_EXT_METASTORE('<CATALOG_NAME>', '<ARGUMENTS>', ?, ?)';
```
a. エンタープライズ watsonx.data 版では、Icebergカタログと Hive 両方のカタログ 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>- Infrastructure Managerページから取得（カタログをクリック）。
- auth.plain.credentials のデータシート認証情報（<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>- これは、SSL接続を検証するための証明書として、コンテナ db2u 上のファイルとして提供する必要があります。 SSL接続が、または DigiCert などの有名な認証局（CA）が発行した VeriSign 証明書を使用して確立される場合、証明書を渡す必要はありません。デフォルトでは、MDS証明書はクエリオプティマイザの以下の /secrets/external/ibm-lh-tls-secret/ca.crt パスで利用可能です。
  1. 次のコマンドを実行して、 db2u クエリ・オプティマイザ・ヘッド・ポッド（OPT_POD）を特定します。
```
oc get pod | grep oaas-db2u
```
  2. 以下のコマンドを実行し、<OPT_POD> および <OPT_P> の値を置き換えて証明書を生成します .
```
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. Lite watsonx.data 版では、Icebergテーブルは異なる Hive メタストアサーバータイプを使用して管理されます。アプリケーションの要件に応じて、メタストアサーバーをIceberg用 Hive、または両方のために登録する必要があります。
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 スリフトサーバーの URI。それはで始めなければならない https://。
  - auth.plain.credentials のデータシート認証情報（<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>- これは、SSL接続を検証するための証明書として、コンテナ db2u 上のファイルとして提供する必要があります。 SSL接続が、または DigiCert などの有名な認証局（CA）によって発行 VeriSign された証明書を使用して確立される場合、証明書を渡す必要はありません。デフォルトでは、MDS証明書はクエリオプティマイザの以下の /secrets/external/ibm-lh-tls-secret/ca.crt パスで利用可能です。
2. Lite watsonx.data 版からのIcebergカタログ登録:
```
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テーブルを管理するメタストアの場合、値は次のとおりです： iceberg-rest
  - watsonx.data <CATALOG_NAME>- インフラストラクチャマネージャのページに表示されているように（大文字と小文字が区別されます）。
  - <THRIFT_URL>- Iceberg REST watsonx.data MDS サーバーの URI。 URI はで始まり、REST API https:// カタログへのベースパスを含まなければなりません。のベースパス watsonx.data はです /mds/iceberg。
  - auth.plain.credentials のデータシート認証情報（<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>- これは、SSL接続を検証するための証明書として、コンテナ db2u 上のファイルとして提供する必要があります。 SSL接続が、または DigiCert などの有名な認証局（CA）によって発行 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 テーブルをクエリオプティマイザに同期させ、クエリの最適化が可能になります。これはカタログごとに 1 回実行する必要があります。
カタログ内の各スキーマのテーブルを同期するには、次のコマンドを実行します。
```
ExecuteWxdQueryOptimizer 'CALL SYSHADOOP.EXT_METASTORE_SYNC('<CATALOG_NAME>', '<SCHEMA_NAME>', '.*', '<SYNC MODE>', 'CONTINUE', 'OAAS')';
```
- <CATALOG_NAME>: 同期するテーブルが属するカタログの名前。
- <SCHEMA_NAME>: 同期するテーブルが属するスキーマの名前。
- <SYNC MODE>:SKIP すでに定義されているオブジェクトをスキップする必要があることを示す同期モードです。REPLACE 前回の同期以降にオブジェクトが変更された場合にオブジェクトを更新するために使用される別の同期モードです。
- CONTINUE: エラーはログに記録されますが、複数のテーブルをインポートする場合は処理が続行されます。
同期が完了すると、同期されたテーブルのリストが出力される。同期されるテーブルの総数は、カタログまたはスキーマ内のテーブル数の2倍でなければならない。これは、各テーブルが2回同期されるからである。外部メタストアからローカルメタストアへ一度、そしてローカルメタストアからカタログ Db2 へ。

数分後に [、テーブル同期の確認 ] の手順に従って同期 watsonx.data 操作を確認してください。

カタログとスキーマのリストを特定するwatsonx.dataあなたが必要とするものクエリオプティマイザー。

制約を定義するSQLファイルを提供するクエリオプティマイザー使用する。 SQL ファイルで、データセット内の各テーブルに該当する主キー、外部キー、および null でない列を識別します。

たとえば、次の3つのテーブルがあり、その列が与えられている場合、

従業員（EmployeeID,FirstName,LastName,部門、給与）、部門（DepartmentIDそしてDepartmentName）、そしてEmployeeDepartmentMapping（MappingID,EmployeeID,そしてDepartmentI ）。

実行 ALTER 制約を定義するためのテーブルコマンド:

-- 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';