Sincronizzazione manuale di Query Optimizer con il metastore watsonx.data

Informazioni su quest'attività

Per fornire query ottimizzate, Query Optimizer estrae i dati relativi alle definizioni delle tabelle e alle statistiche di Hive e Iceberg per sincronizzarsi con MDS in IBM® watsonx.data. È possibile selezionare la tabella Hive e Iceberg specifica che deve essere disponibile per Query Optimizer. Si consiglia di generare statistiche Hive e Iceberg e di etichettare le colonne per le chiavi primarie ed esterne per ottenere i migliori risultati.

Attivando Query Optimizer si sincronizzano automaticamente i metadati per i cataloghi collegati ai motori Presto (C++). Tuttavia, è necessario eseguire i passaggi seguenti se:

  • Mancano i metadati per i cataloghi o gli schemi inaccessibili o danneggiati durante la distribuzione.
  • Vengono apportate modifiche significative a una tabella.
  • Dopo l'operazione di sincronizzazione iniziale vengono introdotte nuove tabelle.
  • Un problema intermittente impedisce la sincronizzazione delle tabelle durante il processo di sincronizzazione automatica all'attivazione.

Prima di iniziare

Per sincronizzare le tabelle da watsonx.data, sono necessari i seguenti elementi:

Le istruzioni contenute in questo argomento possono ora essere eseguite utilizzando la funzione avanzata Gestione degli aggiornamenti statistici dalla dashboard Optimizer, che consente miglioramenti avanzati delle prestazioni delle query e capacità di ottimizzazione su più cataloghi cataloghi.

  1. Verificare che tutte le tabelle previste siano sincronizzate seguendo la procedura in Verificare la sincronizzazione delle tabelle in watsonx.data.

  2. Identificare l'elenco delle tabelle Hive e Iceberg in watsonx.data di cui si ha bisogno per Query Optimizer.

  3. Identificare le colonne come chiavi primarie e straniere nelle tabelle Hive e Iceberg.

  4. ANALYZE Tabelle Hive e Iceberg in Presto (C++) per generare statistiche Hive e Iceberg.

  5. Solo gli utenti con privilegi di amministratore possono eseguire il comando ExecuteWxdQueryOptimizer per migliorare la sicurezza.

  6. Se il parametro di sessione is_query_rewriter_plugin_enabled è impostato su false, non sarà possibile eseguire i comandi ExecuteWxdQueryOptimizer.

Procedura

  1. Accedi a watsonx.data.

  2. Vai all'area di lavoro Query.

  3. Corri il ANALYZE comando da parte diwatsonx.data console Web per le tabelle che desideri sincronizzare per generare le statistiche (le statistiche sono il numero di righe, il nome della colonna, la dimensione_dati, il conteggio delle righe e altro).

    ANALYZE catalog_name.schema_name.table_name ;

  4. Eseguire il seguente comando per registrare manualmente le proprietà del metastore di un catalogo in Query Optimizer utilizzando il tipo di metastore legacy watsonx-data per entrambi i cataloghi Hive e Iceberg:

    ExecuteWxdQueryOptimizer 'CALL SYSHADOOP.REGISTER_EXT_METASTORE('<CATALOG_NAME>', '<ARGUMENTS>', ?, ?)';

    a. Per la versione watsonx.data Enterprise, utilizzare il tipo di metastore legacy sia watsonx-data per i cataloghi che Hive per quelli Iceberg:

    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>', ?, ?)

    Ad esempio:

    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- Il tipo di metastore a cui ci si connette. Il valore supportato è: watsonx-data.

    • watsonx.data <CATALOG_NAME>- come indicato nella pagina del Gestore dell'infrastruttura (attenzione alle maiuscole).

    • <THRIFT_URL>- Come ottenuto dalla pagina di Infrastructure Manager (fare clic sul catalogo).

    • Credenziali MDS (<Username> e <Password>) in auth.plain.credentials- Devono essere create sul lato watsonx.data. Vedi Collegamento a watsonx.data su OpenShift. Se il metastore richiede l'autenticazione PLAIN, le credenziali devono essere specificate nel formato username:password o ibmlhapikey_<username>:apikey. La password viene memorizzata in modo sicuro in un keystore del software.

    • auth.mode- Se il metastore richiede l'autenticazione, indica la modalità di autenticazione da utilizzare. Il sito auth.mode deve essere impostato su PLAIN

    • use.SSL- Deve essere vero se il metastore richiede una connessione SSL.

    • <MDS certificate file path>- Questo deve essere fornito come file sul db2u container come certificato per convalidare la connessione SSL. Non è necessario trasmettere un certificato se la connessione SSL viene stabilita utilizzando un certificato emesso da una CA nota come DigiCert o VeriSign. Per impostazione predefinita, i certificati MDS sono disponibili nel /secrets/external/ibm-lh-tls-secret/ca.crt percorso in Query optimizer.

      1. Eseguire il seguente comando per identificare il pod principale di db2u Query Optimizer (OPT_POD).

        oc get pod | grep oaas-db2u

      2. Eseguire il seguente comando per generare il certificato sostituendo i valori per <OPT_POD> e . 

        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. Per la versione watsonx.data Lite, le Hive tabelle Iceberg vengono gestite utilizzando tipi di server metastore distinti. A seconda delle esigenze dell'applicazione, è necessario registrare i server metastore per Hive, Iceberg o entrambi.

    1. Registrazione di un Hive catalogo nella watsonx.data versione Lite:

      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>', ?, ?)';

      Ad esempio:

      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- Il tipo di metastore a cui ci si connette. Per le tabelle Hive di gestione del metastore, il valore è: watsonx-data-hive.
      • watsonx.data <CATALOG_NAME>- come indicato nella pagina del Gestore dell'infrastruttura (attenzione alle maiuscole).
      • <THRIFT_URL>- L'URI del server watsonx.data MDS thrift. Deve iniziare con https://.
      • Credenziali MDS (<Username> e <Password>) in auth.plain.credentials- Devono essere create sul lato watsonx.data. Vedi Collegamento a watsonx.data su OpenShift. Se il metastore richiede l'autenticazione PLAIN, le credenziali devono essere specificate nel formato username:password o ibmlhapikey_<username>:apikey. La password viene memorizzata in modo sicuro in un keystore del software.
      • auth.mode- Se il metastore richiede l'autenticazione, indica la modalità di autenticazione da utilizzare. Il auth.mode deve essere impostato su PLAIN.
      • use.SSL- Deve essere vero se il metastore richiede una connessione SSL.
      • <MDS certificate file path>- Questo deve essere fornito come file sul db2u container come certificato per convalidare la connessione SSL. Non è necessario passare un certificato se la connessione SSL viene stabilita utilizzando un certificato emesso da una CA ben nota come DigiCert o VeriSign. Per impostazione predefinita, i certificati MDS sono disponibili nel /secrets/external/ibm-lh-tls-secret/ca.crt percorso in Query optimizer.

    2. Registrazione di un catalogo Iceberg dalla versione watsonx.data Lite:

      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>', ?, ?)';

      Ad esempio:

      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- Il tipo di metastore a cui ci si connette. Per il metastore che gestisce le tabelle Iceberg, il valore è: iceberg-rest.
      • watsonx.data <CATALOG_NAME>- come indicato nella pagina del Gestore dell'infrastruttura (attenzione alle maiuscole).
      • <THRIFT_URL>- L'URI del server watsonx.data Iceberg REST MDS. L'URI deve iniziare con https:// e contenere il percorso di base del catalogo API REST. Per watsonx.data, il percorso di base è /mds/iceberg.
      • Credenziali MDS (<Username> e <Password>) in auth.plain.credentials- Devono essere create sul lato watsonx.data. Vedi Collegamento a watsonx.data su OpenShift. Se il metastore richiede l'autenticazione PLAIN, le credenziali devono essere specificate nel formato username:password o ibmlhapikey_<username>:apikey. La password viene memorizzata in modo sicuro in un keystore del software.
      • auth.mode- Se il metastore richiede l'autenticazione, indica la modalità di autenticazione da utilizzare. Il auth.mode deve essere impostato su PLAIN.
      • use.SSL- Deve essere vero se il metastore richiede una connessione SSL.
      • <MDS certificate file path>- Questo deve essere fornito come file sul db2u container come certificato per convalidare la connessione SSL. Non è necessario passare un certificato se la connessione SSL viene stabilita utilizzando un certificato emesso da una CA ben nota come DigiCert o VeriSign. Per impostazione predefinita, i certificati MDS sono disponibili nel /secrets/external/ibm-lh-tls-secret/ca.crt percorso in Query optimizer.

  5. Eseguire il comando seguente per registrarsiwatsonx.data catalogo con Ottimizzatore di query:

    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>- come mostrato nella pagina Infrastructure Manager (distingue tra maiuscole e minuscole).
    • <Metastore_Thrift_endpoint>- Come ottenuto dalla pagina Infrastructure Manager (fare clic sul catalogo).
    • Credenziali MDS (<Username>:<apikey>)- Devono essere create sul sito watsonx.data. Vedere Collegamento a watsonx.data su IBM Cloud o Amazon Web Services.

    La registrazione del catalogo con Query Optimizer consente di watsonx.data sincronizzare le tabelle in Query Optimizer, consentendo l'ottimizzazione delle query. Questa operazione deve essere eseguita una volta per ciascun catalogo.

  6. Eseguire il comando seguente per sincronizzare le tabelle per ogni schema nel catalogo:

    ExecuteWxdQueryOptimizer 'CALL SYSHADOOP.EXT_METASTORE_SYNC('<CATALOG_NAME>', '<SCHEMA_NAME>', '.*', '<SYNC MODE>', 'CONTINUE', 'OAAS')';
    • <CATALOG_NAME>: Il nome del catalogo a cui appartengono le tabelle da sincronizzare.
    • <SCHEMA_NAME>: il nome dello schema a cui appartengono le tabelle da sincronizzare.
    • <SYNC MODE>:SKIP è una modalità di sincronizzazione che indica che gli oggetti già definiti devono essere ignorati.REPLACE è un'altra modalità di sincronizzazione utilizzata per aggiornare l'oggetto se è stato modificato dall'ultima sincronizzazione.
    • CONTINUE: l'errore viene registrato, ma l'elaborazione continua se è necessario importare più tabelle.

    Al termine della sincronizzazione, l'output visualizza l'elenco delle tabelle sincronizzate. Il numero totale di tabelle sincronizzate deve essere il doppio del numero di tabelle del catalogo o dello schema. Questo perché ogni tabella viene sincronizzata due volte. Una volta dal metastore esterno al metastore locale, e poi dal metastore locale al Db2 catalogo.

    Verifica l'operazione di sincronizzazione in pochi minuti seguendo la procedura descritta in Verifica della sincronizzazione delle tabelle in watsonx.data.

  7. Identificare l'elenco dei cataloghi e degli schemi inwatsonx.data di cui hai bisogno Ottimizzatore di query.

    Fornire un file SQL per definire i vincoli per il file Ottimizzatore di query usare. Nel file SQL, identifica le chiavi primarie, le chiavi esterne e non le colonne null, ove applicabile, per ciascuna tabella nel set di dati.

    Ad esempio, se hai le seguenti tre tabelle con le colonne indicate,

    Dipendenti (EmployeeID,FirstName,LastName, Dipartimento e Stipendio), Dipartimenti (DepartmentID EDepartmentName ), EEmployeeDepartmentMapping (MappingID,EmployeeID, EDepartmentI ).

    Corri il ALTER comando table per definire i vincoli:

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