Sincronização manual do Query Optimizer com o watsonx.data metastore

Sobre essa tarefa

Para fornecer consultas otimizadas, o Query Optimizer extrai dados sobre definições de tabelas e estatísticas do Hive e do Iceberg para sincronizar com o MDS em IBM® watsonx.data. Você pode selecionar a tabela específica do Hive e do Iceberg que deve estar disponível para o Query Optimizer. Recomenda-se gerar estatísticas Hive e do Iceberg e rotular colunas para chaves primárias e estrangeiras para obter os melhores resultados.

A ativação do Query Optimizer sincroniza automaticamente os metadados dos catálogos que estão conectados aos mecanismos Presto (C++). No entanto, você precisará executar as etapas a seguir se:

  • Faltam metadados para catálogos ou esquemas inacessíveis ou corrompidos durante a implantação.
  • São feitas alterações significativas em uma tabela.
  • Novas tabelas são introduzidas após a operação de sincronização inicial.
  • Um problema intermitente está impedindo que as tabelas sejam sincronizadas durante o processo de sincronização automática após a ativação.

Antes de Iniciar

Para sincronizar tabelas do site watsonx.data, são necessários os seguintes itens:

As instruções deste tópico agora podem ser executadas usando o recurso aprimorado Gerenciar atualizações estatísticas do painel do Optimizer, que permite aprimoramentos avançados de desempenho de consultas e recursos de otimização em vários catálogos catálogos.

  1. Verifique se todas as tabelas esperadas estão sincronizadas seguindo o procedimento em Verificando a sincronização de tabelas em watsonx.data.

  2. Identifique a lista de tabelas Hive e Iceberg em watsonx.data que você precisa para o Query Optimizer.

  3. Identificar colunas como chaves primárias e estrangeiras nas tabelas Hive e Iceberg.

  4. ANALYZE Tabelas Hive e Iceberg no Presto (C++) para gerar estatísticas Hive e Iceberg.

  5. Somente usuários com privilégio de administrador podem executar o comando ExecuteWxdQueryOptimizer como um recurso de aprimoramento de segurança.

  6. Se o parâmetro de sessão is_query_rewriter_plugin_enabled estiver definido como false, você não poderá executar os comandos ExecuteWxdQueryOptimizer.

Procedimento

  1. Efetue login no watsonx.data.

  2. Vá para o espaço de trabalho Consulta.

  3. Execute o ANALYZE comando dowatsonx.data console da web para as tabelas que você deseja sincronizar para gerar as estatísticas (estatísticas é o número de linhas, nome da coluna, data_size, contagem de linhas e muito mais).

    ANALYZE catalog_name.schema_name.table_name ;

  4. Execute o seguinte comando para registrar manualmente as propriedades do metastore de um catálogo no Query Optimizer usando o tipo de metastore legado watsonx-data para os catálogos Hive e Iceberg:

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

    a. Para a versão watsonx.data Enterprise, use o tipo de metastore watsonx-data legado para os catálogos Hive e 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>', ?, ?)

    Por exemplo:

    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- O tipo de metastore ao qual você está se conectando. O valor suportado é: watsonx-data.

    • watsonx.data <CATALOG_NAME>- conforme mostrado na página do Infrastructure Manager (diferencia maiúsculas de minúsculas).

    • <THRIFT_URL>- Conforme obtido na página do Infrastructure Manager (clique no catálogo).

    • Credenciais MDS (<Username> e <Password>) em auth.plain.credentials- Devem ser criadas no site watsonx.data. Consulte Conectando-se a watsonx.data em OpenShift. Se o metastore exigir autenticação PLAIN, as credenciais deverão ser especificadas no formato username:password ou ibmlhapikey_<username>:apikey. A senha é armazenada de forma segura em um repositório de chaves do software.

    • auth.mode- Se o metastore exigir autenticação, indique o modo de autenticação a ser usado. O endereço auth.mode deve ser definido como PLAIN

    • use.SSL- Deve ser verdadeiro se o metastore exigir uma conexão SSL.

    • <MDS certificate file path>- Isso deve ser fornecido como um arquivo no db2u contêiner como um certificado para validar a conexão SSL. Não é necessário passar um certificado se a conexão SSL for estabelecida usando um certificado emitido por uma CA conhecida, como DigiCert ou VeriSign. Por padrão, os certificados MDS estão disponíveis no /secrets/external/ibm-lh-tls-secret/ca.crt caminho no otimizador de consultas.

      1. Execute o seguinte comando para identificar o pod principal do db2u Query Optimizer (OPT_POD).

        oc get pod | grep oaas-db2u

      2. Execute o seguinte comando para gerar o certificado, substituindo os valores por <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. Para a versão watsonx.data Lite, Hive as tabelas Iceberg são gerenciadas usando tipos distintos de servidor metastore. Dependendo das necessidades da sua aplicação, você deve registrar servidores metastore para Hive, Iceberg ou ambos.

    1. Registrando um Hive catálogo na versão watsonx.data 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>', ?, ?)';

      Por exemplo:

      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- O tipo de metastore ao qual você está se conectando. Para as tabelas de Hive gerenciamento do metastore, o valor é: watsonx-data-hive.
      • watsonx.data <CATALOG_NAME>- conforme mostrado na página do Infrastructure Manager (diferencia maiúsculas de minúsculas).
      • <THRIFT_URL>- O URI do servidor watsonx.data MDS Thrift. Deve começar com https://.
      • Credenciais MDS (<Username> e <Password>) em auth.plain.credentials- Devem ser criadas no site watsonx.data. Consulte Conectando-se a watsonx.data em OpenShift. Se o metastore exigir autenticação PLAIN, as credenciais devem ser especificadas no formato username:password ou ibmlhapikey_<username>:apikey. A senha é armazenada de forma segura em um repositório de chaves do software.
      • auth.mode- Se o metastore exigir autenticação, indique o modo de autenticação a ser usado. O auth.mode deve ser definido como PLAIN.
      • use.SSL- Deve ser verdadeiro se o metastore exigir uma conexão SSL.
      • <MDS certificate file path>- Isso deve ser fornecido como um arquivo no db2u contêiner como um certificado para validar a conexão SSL. Não é necessário passar um certificado se a conexão SSL for estabelecida usando um certificado emitido por uma CA conhecida, como DigiCert ou VeriSign. Por padrão, os certificados MDS estão disponíveis no /secrets/external/ibm-lh-tls-secret/ca.crt caminho no otimizador de consultas.

    2. Registrando um catálogo Iceberg da versão 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>', ?, ?)';

      Por exemplo:

      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- O tipo de metastore ao qual você está se conectando. Para o metastore que gerencia tabelas Iceberg, o valor é: iceberg-rest.
      • watsonx.data <CATALOG_NAME>- conforme mostrado na página do Infrastructure Manager (diferencia maiúsculas de minúsculas).
      • <THRIFT_URL>- O URI do servidor watsonx.data Iceberg REST MDS. A URI deve começar com https:// e conter o caminho base para o catálogo da API REST. Para watsonx.data, o caminho base é /mds/iceberg.
      • Credenciais MDS (<Username> e <Password>) em auth.plain.credentials- Devem ser criadas no site watsonx.data. Consulte Conectando-se a watsonx.data em OpenShift. Se o metastore exigir autenticação PLAIN, as credenciais devem ser especificadas no formato username:password ou ibmlhapikey_<username>:apikey. A senha é armazenada de forma segura em um repositório de chaves do software.
      • auth.mode- Se o metastore exigir autenticação, indique o modo de autenticação a ser usado. O auth.mode deve ser definido como PLAIN.
      • use.SSL- Deve ser verdadeiro se o metastore exigir uma conexão SSL.
      • <MDS certificate file path>- Isso deve ser fornecido como um arquivo no db2u contêiner como um certificado para validar a conexão SSL. Não é necessário passar um certificado se a conexão SSL for estabelecida usando um certificado emitido por uma CA conhecida, como DigiCert ou VeriSign. Por padrão, os certificados MDS estão disponíveis no /secrets/external/ibm-lh-tls-secret/ca.crt caminho no otimizador de consultas.

  5. Execute o seguinte comando para registrarwatsonx.data catálogo com Otimizador de consultas:

    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>- conforme mostrado na página do Infrastructure Manager (diferencia maiúsculas de minúsculas).
    • <Metastore_Thrift_endpoint>- Conforme obtido na página do Infrastructure Manager (clique no catálogo).
    • Credenciais MDS (<Username>:<apikey>)- Devem ser criadas no site watsonx.data. Consulte Conexão com watsonx.data em IBM Cloud ou Amazon Web Services.

    O registro do catálogo no Otimizador de Consultas permite que watsonx.data as tabelas sejam sincronizadas no Otimizador de Consultas, possibilitando a otimização das consultas. Isso precisa ser executado uma vez para cada catálogo.

  6. Execute o seguinte comando para sincronizar as tabelas de cada esquema no catálogo:

    ExecuteWxdQueryOptimizer 'CALL SYSHADOOP.EXT_METASTORE_SYNC('<CATALOG_NAME>', '<SCHEMA_NAME>', '.*', '<SYNC MODE>', 'CONTINUE', 'OAAS')';
    • <CATALOG_NAME>: o nome do catálogo ao qual pertencem as tabelas a serem sincronizadas.
    • <SCHEMA_NAME>: o nome do esquema ao qual pertencem as tabelas a serem sincronizadas.
    • <SYNC MODE>:SKIP é um modo de sincronização que indica que os objetos já definidos devem ser ignorados.REPLACE é outro modo de sincronização usado para atualizar o objeto se ele tiver sido modificado desde a última sincronização.
    • CONTINUE: o erro é registrado, mas o processamento continua se diversas tabelas forem importadas.

    Quando a sincronização é concluída, a saída exibe a lista de tabelas sincronizadas. A contagem total de tabelas sincronizadas deve ser o dobro do número de tabelas no catálogo ou esquema. Isso ocorre porque cada tabela é sincronizada duas vezes. Uma vez do metastore externo para o metastore local e, em seguida, do metastore local para o Db2 catálogo.

    Verifique a operação de sincronização em alguns minutos, seguindo o procedimento em Verificar a sincronização da tabela em watsonx.data.

  7. Identifique a lista de catálogos e esquemas emwatsonx.data que você precisa para Otimizador de consultas.

    Forneça um arquivo SQL para definir as restrições para o Otimizador de consultas usar. No arquivo SQL, identifique chaves primárias, chaves estrangeiras e não colunas nulas quando aplicável para cada tabela em seu conjunto de dados.

    Por exemplo, se você tiver as três tabelas a seguir com as colunas fornecidas,

    Funcionários (EmployeeID,FirstName,LastName, Departamento e Salário), Departamentos (DepartmentID eDepartmentName ), eEmployeeDepartmentMapping (MappingID,EmployeeID, eDepartmentI ).

    Execute o ALTER comando table para definir as restrições:

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