MaxCompute:Optimisation des requêtes SQL

Forcer l'exécution sur une seule instance

Pour éviter ce goulot d'étranglement, vérifiez si une agrégation globale est réellement nécessaire. Si tel est le cas, utilisez un modèle d'agrégation en deux étapes : regroupez d'abord par clé à haute cardinalité pour réaliser une agrégation partielle en parallèle sur toutes les instances, puis effectuez l'agrégation finale sur l'ensemble de résultats intermédiaires, beaucoup plus réduit.

Impact d'un nombre incorrect d'instances

Configuration du nombre d'instances

• Pour les tâches de lecture de table (mappeurs)

  • Méthode 1 : Définir un paramètre global de taille de fractionnement.

    -- 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éthode 2 : Utiliser un indice de requête pour un contrôle par table. L'indice split_size remplace le paramètre global pour une opération de lecture de table spécifique, vous offrant un contrôle plus granulaire sans affecter le reste de la requête.

    -- 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éthode 3 : Fractionner les données au niveau de la table par taille, nombre de lignes ou DOP spécifié.

  Le paramètre odps.sql.mapper.split.size s'applique globalement à toutes les étapes de mappeur et a une valeur minimale de 1 Mo. Pour les cas où les lignes sont petites mais coûteuses en calcul — c'est-à-dire lorsque vous souhaitez davantage d'instances sans modifier le volume de données par fraction — utilisez plutôt les paramètres suivants au niveau de la table.

  Utilisez les paramètres suivants pour l'ajustement du DOP au niveau de la table :

  • Définir la taille de fractionnement des données par instance au niveau de la table.

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

  • Définir le nombre de lignes traitées par instance au niveau de la table.

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

  • Définir directement le DOP au niveau de la table.

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

  Remarque

  Les paramètres odps.sql.split.row.count et odps.sql.split.dop s'appliquent uniquement aux tables internes, aux tables non transactionnelles et aux tables non clusterisées.

• Pour les tâches autres que la lecture (réducteurs et jointeurs)

  • Méthode 1 : Définir le nombre d'instances de réducteur. Ce paramètre s'applique à toutes les tâches de réducteur dans la requête.

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

  • Méthode 2 : Définir le nombre d'instances de jointeur. Ce paramètre s'applique à toutes les tâches de jointeur dans la requête.

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

  • Méthode 3 : Ajuster le nombre de mappeurs en amont. Le nombre d'instances de réducteur est dérivé de l'étape de mappeur précédente. L'augmentation du nombre de mappeurs accroît indirectement le parallélisme des réducteurs.

Chaque fonction de fenêtrage dans une requête déclenche généralement une tâche de réduction distincte. Lorsqu'une requête contient plusieurs fonctions de fenêtrage, cela multiplie considérablement la consommation de ressources. MaxCompute fusionne automatiquement plusieurs fonctions de fenêtrage en une seule tâche de réduction lorsque les deux conditions suivantes sont réunies :

• Les clauses OVER sont identiques — mêmes conditions PARTITION BY et ORDER BY.

• Les fonctions de fenêtrage apparaissent dans la même instruction SELECT.

La requête suivante est éligible à une fusion automatique car RANK() et ROW_NUMBER() partagent la même clause OVER et apparaissent dans la même instruction SELECT. MaxCompute les exécute dans une seule tâche de réduction au lieu de deux.

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;

Prenons l'exemple d'une requête qui filtre à l'aide d'une sous-requête IN :

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

Si la sous-requête sur table_b renvoie plus de 9 999 valeurs pour col1, MaxCompute signale l'erreur suivante : records returned from subquery exceeded limit of 9999. Réécrivez la requête sous forme de JOIN pour supprimer cette limite :

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

Pour que MaxCompute applique l'élagage des partitions lors d'une opération JOIN, filtrez les tables partitionnées avant l'exécution de la jointure, et non après. Sans filtrage préalable, le système effectue d'abord la jointure sur toutes les partitions, puis applique le filtre, analysant ainsi bien plus de données que nécessaire.

Suivez ces règles :

• Appliquez les conditions de limitation de partition sur la table principale dans une sous-requête avant la jointure.

• Placez les autres clauses WHERE filtrant la table principale à la fin de l'instruction SQL.

• Appliquez les conditions de limitation de partition sur la table secondaire dans la clause ON ou dans une sous-requête, et non dans la clause WHERE finale.

Les exemples suivants illustrent ces pratiques.

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;

Pour l'agrégation de chaînes, wm_concat offre généralement de meilleures performances que collect_list. Les exemples suivants montrent des opérations équivalentes utilisant chaque fonction.

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