關於此作業

為了提供最佳化的查詢，Query Optimizer 會拉取關於資料表定義的資料，以及 Hive 和 Iceberg 統計資料，與 IBM® watsonx.data 中的 MDS 同步。 您可以選擇特定的 Hive 和 Iceberg 資料表，必須提供給 Query Optimizer。 建議為主鍵和外鍵產生 Hive 和 Iceberg 統計和標示列，以獲得最佳結果。

啟動查詢優化器會自動同步連接到Presto (C++) 引擎的目錄的元資料。 但是，如果出現以下情況，您將需要執行以下步驟：

• 部署期間無法存取或損壞的目錄或架構的元資料遺失。
• 對錶進行了重大更改。
• 在初始同步操作後引入新表。
• 間歇性問題是在啟動後的自動同步過程中阻止表同步。

開始之前

若要從 watsonx.data 同步處理資料表，需要下列項目：

現在可以使用增強功能 「從優化器儀表板管理統計更新」 來執行本主題中的說明，該功能可實現跨多個目錄的高階查詢效能增強和最佳化功能。

1. 依照 驗證 watsonx.data 中的表同步 中的程序驗證所有預期表是否均已同步。

2. 在 watsonx.data 中找出您需要用於 Query Optimizer 的 Hive 和 Iceberg 表清單。

3. 在 Hive 和 Iceberg 表中識別列為主鍵和外鍵。

4. ANALYZE 在 Presto (C++) 中產生 Hive 和 Iceberg 表格，以產生 Hive 和 Iceberg 統計資料。

5. 作為一項安全性增強功能，僅允許具有管理員權限的使用者執行 ExecuteWxdQueryOptimizer 命令。

6. 如果會話參數 is_query_rewriter_plugin_enabled 設定為 false，將無法執行 ExecuteWxdQueryOptimizer 指令。

程序

1. 登入 watsonx.data。

2. 前往查詢工作區。

3. 跑過 ANALYZE 命令來自watsonx.data您想要同步以產生統計資料的資料表的 Web 控制台（統計資料是行數、列名、data_size、行計數等）。

   ANALYZE catalog_name.schema_name.table_name ;
   

4. 執行下列指令，在查詢最佳化器中手動註冊目錄的元儲存屬性，使用傳統的元儲存類型 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- 您要連線的 metastore 類型。 支援的值為：watsonx-data。

   • watsonx.data <CATALOG_NAME>- 如 Infrastructure Manager 頁面所示 (區分大小寫)。

   • <THRIFT_URL>- 如從 Infrastructure Manager 頁面取得 (按一下目錄)。

   • auth.plain.credentials 中的 MDS 認證 (<Username> 和 <Password>)- 必須在 watsonx.data 側建立。 參閱「連接至 watsonx.data」於 OpenShift。 如果元存储要求 PLAIN 身份验证，则必须以 username:password 或 ibmlhapikey_<username>:apikey 的格式指定凭据。 密碼會安全地儲存在軟體的 keystore 中。

   • 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> 和 <OPT_PAD> 的值替換為實際參數： .

        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- 您要連線的 metastore 類型。 對於管理 Hive 表格的元資料儲存庫，其值為：watsonx-data-hive。
      • watsonx.data <CATALOG_NAME>- 如 Infrastructure Manager 頁面所示 (區分大小寫)。
      • <THRIFT_URL>- MDS watsonx.data 節省服務器的 URI。 它必須以 https://. 開頭。
      • auth.plain.credentials 中的 MDS 認證 (<Username> 和 <Password>)- 必須在 watsonx.data 側建立。 參見「連接至」watsonx.data 於 OpenShift。 若元儲存庫需要 PLAIN 驗證，憑證必須以 或 username:password 格式 ibmlhapikey_<username>:apikey 指定。 密碼會安全地儲存在軟體的 keystore 中。
      • 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- 您要連線的 metastore 類型。 對於管理 Iceberg 表格的元數據儲存庫，其值為：iceberg-rest。
      • watsonx.data <CATALOG_NAME>- 如 Infrastructure Manager 頁面所示 (區分大小寫)。
      • <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 指定。 密碼會安全地儲存在軟體的 keystore 中。
      • 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 下取得。

5. 執行以下命令進行註冊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>- 如 Infrastructure Manager 頁面所示 (區分大小寫)。
   • <Metastore_Thrift_endpoint>- 如從 Infrastructure Manager 頁面取得 (按一下目錄)。
   • MDS 認證 (<Username>:<apikey>)- 必須在 watsonx.data 上建立。 請參閱 IBM Cloud 或 Amazon Web Services 連線至 watsonx.data。

   將目錄註冊至查詢優化器後，即可 watsonx.data 將資料表同步至查詢優化器，從而實現查詢優化。 這需要為每個目錄運行一次。

6. 執行以下命令來同步目錄中每個架構的表：

   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。

7. 識別目錄和模式的列表watsonx.data你需要的查詢最佳化器。

   提供 SQL 檔案來定義約束查詢最佳化器使用。 在 SQL 檔案中，識別適用於資料集中每個資料表的主鍵、外鍵和非空白列。

   例如，如果您有以下三個帶有給定列的表，

   僱員 （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';