MaxCompute:Otimização de consultas SQL

Forçar execução em instância única

Para evitar esse gargalo, avalie se uma agregação global é realmente necessária. Em caso afirmativo, adote um padrão de agregação em dois estágios: primeiro agrupe por uma chave de alta cardinalidade para executar uma agregação parcial em todas as instâncias paralelamente; em seguida, realize a agregação final sobre o conjunto de resultados intermediários, que será muito menor.

Impacto da contagem incorreta de instâncias

Configurar o número de instâncias

• Para tarefas de leitura de tabela (mappers)

  • Método 1: Definir um parâmetro global de tamanho de divisão.

    -- Configure the maximum amount of input data per mapper instance. Unit: MB.
    -- Default value: 256. Valid values: [1,Integer.MAX_VALUE].
    SET odps.sql.mapper.split.size=<value>;

  • Método 2: Usar uma dica de consulta para controle por tabela. A dica split_size substitui o parâmetro global para uma operação específica de leitura de tabela, oferecendo controle mais granular sem afetar o restante da consulta.

    -- Split the src table into subtasks of 1 MB each.
    SELECT a.key FROM src a /*+split_size(1)*/ JOIN src2 b ON a.key=b.key;

  • Método 3: Dividir dados no nível da tabela por tamanho, contagem de linhas ou um DOP especificado.

  O parâmetro odps.sql.mapper.split.size aplica-se globalmente a todos os estágios mapper e tem valor mínimo de 1 MB. Para casos em que as linhas são pequenas, mas computacionalmente custosas — situações em que se deseja mais instâncias sem alterar o volume de dados por divisão — utilize os seguintes parâmetros no nível da tabela.

  Use os parâmetros abaixo para ajuste de DOP no nível da tabela:

  • Defina o tamanho da divisão de dados por instância no nível da tabela.

    SET odps.sql.split.size = {"table1": 1024, "table2": 512};

  • Defina a contagem de linhas processadas por instância no nível da tabela.

    SET odps.sql.split.row.count = {"table1": 100, "table2": 500};

  • Defina o DOP diretamente no nível da tabela.

    SET odps.sql.split.dop = {"table1": 1, "table2": 5};

  Nota

  Os parâmetros odps.sql.split.row.count e odps.sql.split.dop aplicam-se apenas a tabelas internas, tabelas não transacionais e tabelas não clusterizadas.

• Para tarefas que não são de leitura (reducers e joiners)

  • Método 1: Definir a contagem de instâncias reducer. Esta configuração aplica-se a todas as tarefas reducer na consulta.

    -- Set the number of reducer instances.
    -- Valid values: [1,99999].
    SET odps.stage.reducer.num=<value>;

  • Método 2: Definir a contagem de instâncias joiner. Esta configuração aplica-se a todas as tarefas joiner na consulta.

    -- Set the number of joiner instances.
    -- Valid values: [1,99999].
    SET odps.stage.joiner.num=<value>;

  • Método 3: Ajustar a contagem de mappers anteriores. A contagem de instâncias reducer deriva do estágio mapper precedente. Aumentar a contagem de mappers eleva indiretamente o paralelismo dos reducers.

Cada função de janela em uma consulta geralmente aciona um job reduce separado. Quando uma consulta contém múltiplas funções de janela, isso multiplica significativamente o consumo de recursos. O MaxCompute mescla automaticamente várias funções de janela em um único job reduce quando ambas as condições abaixo são atendidas:

• As cláusulas OVER são idênticas — mesmas condições de PARTITION BY e ORDER BY.

• As funções de janela aparecem na mesma instrução SELECT.

A consulta a seguir qualifica-se para mesclagem automática porque tanto RANK() quanto ROW_NUMBER() compartilham a mesma cláusula OVER e aparecem na mesma instrução SELECT. O MaxCompute as executa em um único job reduce em vez de dois.

SELECT
RANK() OVER (PARTITION BY A ORDER BY B desc) AS RANK,
ROW_NUMBER() OVER (PARTITION BY A ORDER BY B desc) AS row_num
FROM MyTable;

Considere uma consulta que filtra usando uma subconsulta com IN:

SELECT * FROM table_a a WHERE a.col1 IN (SELECT col1 FROM table_b b WHERE xxx);

Se a subconsulta em table_b retornar mais de 9.999 valores para col1, o MaxCompute relata: records returned from subquery exceeded limit of 9999. Reescreva a consulta como um JOIN para remover esse limite:

SELECT a.* FROM table_a a JOIN (SELECT DISTINCT col1 FROM table_b b WHERE xxx) c ON (a.col1 = c.col1);

Para que o MaxCompute aplique poda de partições durante um JOIN, filtre as tabelas particionadas antes da execução da junção, e não depois. Sem essa filtragem antecipada, o sistema realiza o JOIN em todas as partições primeiro e só então aplica o filtro, varrendo muito mais dados do que o necessário.

Siga estas regras:

• Aplique condições de limitação de partição na tabela principal dentro de uma subconsulta antes do JOIN.

• Posicione outras cláusulas WHERE que filtram a tabela principal ao final da instrução SQL.

• Aplique condições de limitação de partição na tabela secundária na cláusula ON ou em uma subconsulta — nunca na cláusula WHERE final.

Os exemplos a seguir ilustram essas práticas.

SELECT * FROM A JOIN (SELECT * FROM B WHERE dt=20150301)B ON B.id=A.id WHERE A.dt=20150301;
SELECT * FROM A JOIN B ON B.id=A.id WHERE B.dt=20150301; -- We recommend that you do not use this statement. The system performs the JOIN operation before it performs partition pruning. This increases the amount of data and causes the query performance to deteriorate. 
SELECT * FROM (SELECT * FROM A WHERE dt=20150301)A JOIN (SELECT * FROM B WHERE dt=20150301)B ON B.id=A.id;

Para agregação de strings, wm_concat geralmente supera collect_list em desempenho. Os exemplos a seguir mostram operações equivalentes usando cada função.

-- Implement the collect_list function.
SELECT concat_ws(',', sort_array(collect_list(key))) FROM src;
-- Implement the wm_concat function for better performance.
SELECT wm_concat(',', key) WITHIN GROUP (ORDER BY key) FROM src;

-- Implement the collect_list function.
SELECT array_join(collect_list(key), ',') FROM src;
-- Implement the wm_concat function for better performance.
SELECT wm_concat(',', key) FROM src;