MaxCompute:Optimización de consultas SQL

Forzar la ejecución en una sola instancia

Para evitar este cuello de botella, verifique si realmente se requiere una agregación global. De ser así, utilice un patrón de agregación en dos fases: primero agrupe por una clave de alta cardinalidad para ejecutar una agregación parcial en paralelo across todas las instancias; después, realice la agregación final sobre el conjunto de resultados intermedios, que será mucho más pequeño.

Impacto de un conteo incorrecto de instancias

Configurar el número de instancias

• Para tareas de lectura de tablas (mappers)

  • Método 1: Establecer un parámetro global de tamaño de división.

    -- 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 un hint de consulta para control por tabla. El hint split_size anula el parámetro global para una operación específica de lectura de tabla, lo que permite un control más granular sin afectar el resto de la 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 los datos a nivel de tabla por tamaño, número de filas o un DOP específico.

  El parámetro odps.sql.mapper.split.size se aplica globalmente a todas las etapas de mapper y tiene un valor mínimo de 1 MB. En casos donde las filas son pequeñas pero costosas en términos computacionales —es decir, cuando se requieren más instancias sin modificar el volumen de datos por división— utilice los siguientes parámetros a nivel de tabla.

  Utilice los siguientes parámetros para ajustar el DOP a nivel de tabla:

  • Establecer el tamaño de división de datos por instancia a nivel de tabla.

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

  • Establecer el número de filas procesadas por instancia a nivel de tabla.

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

  • Establecer el DOP directamente a nivel de tabla.

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

  Nota

  Los parámetros odps.sql.split.row.count y odps.sql.split.dop solo se aplican a tablas internas, tablas no transaccionales y tablas no agrupadas en clúster.

• Para tareas que no son de lectura (reducers y joiners)

  • Método 1: Establecer el número de instancias de reducer. Esta configuración se aplica a todas las tareas de reducer en la consulta.

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

  • Método 2: Establecer el número de instancias de joiner. Esta configuración se aplica a todas las tareas de joiner en la consulta.

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

  • Método 3: Ajustar el número de mappers anteriores. El número de instancias de reducer se deriva de la etapa de mapper precedente. Incrementar el número de mappers aumenta indirectamente el paralelismo de los reducers.

Cada función de ventana en una consulta generalmente desencadena un trabajo de reducción independiente. Cuando una consulta contiene varias funciones de ventana, esto multiplica significativamente el consumo de recursos. MaxCompute fusiona automáticamente múltiples funciones de ventana en un solo trabajo de reducción cuando se cumplen ambas condiciones:

• Las cláusulas OVER son idénticas, es decir, tienen las mismas condiciones de PARTITION BY y ORDER BY.

• Las funciones de ventana aparecen en la misma sentencia SELECT.

La siguiente consulta es candidata para la fusión automática porque tanto RANK() como ROW_NUMBER() comparten la misma cláusula OVER y aparecen en la misma sentencia SELECT. MaxCompute las ejecuta en un solo trabajo de reducción en lugar de dos.

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 una consulta que filtra mediante una subconsulta con IN:

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

Si la subconsulta sobre table_b devuelve más de 9,999 valores para col1, MaxCompute reporta: records returned from subquery exceeded limit of 9999. Reescriba la consulta como un JOIN para eliminar este límite:

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

Para que MaxCompute aplique poda de particiones durante un JOIN, debe filtrar las tablas particionadas antes de que se ejecute la unión, no después. Sin un filtrado temprano, el sistema realiza primero el JOIN sobre todas las particiones y luego aplica el filtro, lo que escanea muchos más datos de los necesarios.

Siga estas reglas:

• Aplique condiciones de limitación de particiones en la tabla principal dentro de una subconsulta antes del JOIN.

• Coloque otras cláusulas WHERE que filtran la tabla principal al final de la sentencia SQL.

• Aplique condiciones de limitación de particiones en la tabla secundaria dentro de la cláusula ON o en una subconsulta, no en la cláusula WHERE final.

Los siguientes ejemplos ilustran estas prácticas.

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 la agregación de cadenas, wm_concat generalmente ofrece mejor rendimiento que collect_list. Los siguientes ejemplos muestran operaciones equivalentes con cada función.

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