MaxCompute:Optimiseur

MaxCompute propose deux méthodes pour collecter des statistiques :

• Framework de collecte asynchrone (Analyze) : vous pouvez collecter des statistiques de manière asynchrone à l'aide de la commande analyze. Cette méthode doit être lancée manuellement.

  Remarque

  La version du client MaxCompute doit être 0,35 ou ultérieure.

• Framework de collecte synchrone (Freeride) : cette méthode collecte automatiquement les Column stats lors de la génération des données. Elle est plus automatisée, mais peut affecter la latence des requêtes.

MaxCompute collecte les métriques Column stats suivantes pour différents types de données.

Le tableau suivant décrit les scénarios associés à chaque métrique Column stats.

Collecte des statistiques de colonne

Cette rubrique explique comment utiliser l'outil Analyze avec des tables partitionnées et non partitionnées.

• Tables non partitionnées

  Vous pouvez collecter des statistiques pour des colonnes spécifiques ou pour toutes les colonnes.

  1. Créez une table non partitionnée nommée analyze2_test à l'aide du client MaxCompute. Voici un exemple de commande :

     create table if not exists analyze2_test (tinyint1 tinyint, smallint1 smallint, int1 int, bigint1 bigint, double1 double, decimal1 decimal, decimal2 decimal(20,10), string1 string, varchar1 varchar(10), boolean1 boolean, timestamp1 timestamp, datetime1 datetime ) lifecycle 30;

  2. Insérez des données dans la table. Voici un exemple de commande :

     insert overwrite table analyze2_test select * from values (1Y, 20S, 4, 8L, 123452.3, 12.4, 52.5, 'str1', 'str21', false, timestamp '2018-09-17 00:00:00', datetime '2018-09-17 00:59:59') ,(10Y, 2S, 7, 11111118L, 67892.3, 22.4, 42.5, 'str12', 'str200', true, timestamp '2018-09-17 00:00:00', datetime '2018-09-16 00:59:59') ,(20Y, 7S, 4, 2222228L, 12.3, 2.4, 2.57, 'str123', 'str2', false, timestamp '2018-09-18 00:00:00', datetime '2018-09-17 00:59:59') ,(null, null, null, null, null, null, null, null, null, null, null , null) as t(tinyint1, smallint1, int1, bigint1, double1, decimal1, decimal2, string1, varchar1, boolean1, timestamp1, datetime1);

  3. Exécutez la commande analyze pour collecter les statistiques d'une seule colonne, de plusieurs colonnes ou de toutes les colonnes. Voici des exemples de commandes :

     -- Collect column stats for the tinyint1 column.
     analyze table analyze2_test compute statistics for columns (tinyint1); 
     
     -- Collect column stats for the smallint1, string1, boolean1, and timestamp1 columns.
     analyze table analyze2_test compute statistics for columns (smallint1, string1, boolean1, timestamp1);
     
     -- Collect column stats for all columns.
     analyze table analyze2_test compute statistics for columns;

  4. Exécutez la commande show statistic pour vérifier les statistiques de colonne collectées. Voici des exemples de commandes :

     -- Check the collected column stats for the tinyint1 column.
     show statistic analyze2_test columns (tinyint1);
     
     -- Check the collected column stats for the smallint1, string1, boolean1, and timestamp1 columns.
     show statistic analyze2_test columns (smallint1, string1, boolean1, timestamp1);
     
     -- Check the collected column stats for all columns.
     show statistic analyze2_test columns;

     Les résultats suivants sont renvoyés :

     -- Collected column stats for the tinyint1 column.
     ID = 20201126085225150gnqo****
     tinyint1:MaxValue:      20                   -- Corresponds to max.
     tinyint1:DistinctNum:   4.0                  -- Corresponds to ndv.
     tinyint1:MinValue:      1                    -- Corresponds to min.
     tinyint1:NullNum:       1.0                  -- Corresponds to nNulls.
     tinyint1:TopK:  {1=1.0, 10=1.0, 20=1.0}      -- Corresponds to topK. 10=1.0 indicates that the value 10 appears once. topK displays up to the top 20 most frequent values.
     
     -- Collected column stats for the smallint1, string1, boolean1, and timestamp1 columns.
     ID = 20201126091636149gxgf****
     smallint1:MaxValue:     20
     smallint1:DistinctNum:  4.0
     smallint1:MinValue:     2
     smallint1:NullNum:      1.0
     smallint1:TopK:         {2=1.0, 7=1.0, 20=1.0}
     
     string1:MaxLength       6.0                  -- Corresponds to maxColLen.
     string1:AvgLength:      3.0                  -- Corresponds to avgColLen.
     string1:DistinctNum:    4.0
     string1:NullNum:        1.0
     string1:TopK:   {str1=1.0, str12=1.0, str123=1.0}
     
     boolean1:DistinctNum:   3.0
     boolean1:NullNum:       1.0
     boolean1:TopK:  {false=2.0, true=1.0}
     
     timestamp1:DistinctNum:         3.0
     timestamp1:NullNum:     1.0
     timestamp1:TopK:        {2018-09-17 00:00:00.0=2.0, 2018-09-18 00:00:00.0=1.0}
     
     -- Collected column stats for all columns.
     ID = 20201126092022636gzm1****
     tinyint1:MaxValue:      20
     tinyint1:DistinctNum:   4.0
     tinyint1:MinValue:      1
     tinyint1:NullNum:       1.0
     tinyint1:TopK:  {1=1.0, 10=1.0, 20=1.0}
     
     smallint1:MaxValue:     20
     smallint1:DistinctNum:  4.0
     smallint1:MinValue:     2
     smallint1:NullNum:      1.0
     smallint1:TopK:         {2=1.0, 7=1.0, 20=1.0}
     
     int1:MaxValue:  7
     int1:DistinctNum:       3.0
     int1:MinValue:  4
     int1:NullNum:   1.0
     int1:TopK:      {4=2.0, 7=1.0}
     
     bigint1:MaxValue:       11111118
     bigint1:DistinctNum:    4.0
     bigint1:MinValue:       8
     bigint1:NullNum:        1.0
     bigint1:TopK:   {8=1.0, 2222228=1.0, 11111118=1.0}
     
     double1:MaxValue:       123452.3
     double1:DistinctNum:    4.0
     double1:MinValue:       12.3
     double1:NullNum:        1.0
     double1:TopK:   {12.3=1.0, 67892.3=1.0, 123452.3=1.0}
     
     decimal1:MaxValue:      22.4
     decimal1:DistinctNum:   4.0
     decimal1:MinValue:      2.4
     decimal1:NullNum:       1.0
     decimal1:TopK:  {2.4=1.0, 12.4=1.0, 22.4=1.0}
     
     decimal2:MaxValue:      52.5
     decimal2:DistinctNum:   4.0
     decimal2:MinValue:      2.57
     decimal2:NullNum:       1.0
     decimal2:TopK:  {2.57=1.0, 42.5=1.0, 52.5=1.0}
     
     string1:MaxLength       6.0
     string1:AvgLength:      3.0
     string1:DistinctNum:    4.0
     string1:NullNum:        1.0
     string1:TopK:   {str1=1.0, str12=1.0, str123=1.0}
     
     varchar1:MaxLength      6.0
     varchar1:AvgLength:     3.0
     varchar1:DistinctNum:   4.0
     varchar1:NullNum:       1.0
     varchar1:TopK:  {str2=1.0, str200=1.0, str21=1.0}
     
     boolean1:DistinctNum:   3.0
     boolean1:NullNum:       1.0
     boolean1:TopK:  {false=2.0, true=1.0}
     
     timestamp1:DistinctNum:         3.0
     timestamp1:NullNum:     1.0
     timestamp1:TopK:        {2018-09-17 00:00:00.0=2.0, 2018-09-18 00:00:00.0=1.0}
     
     datetime1:DistinctNum:  3.0
     datetime1:NullNum:      1.0
     datetime1:TopK:         {1537117199000=2.0, 1537030799000=1.0}

• Tables partitionnées

  Vous pouvez collecter des statistiques de colonne pour une partition spécifique.

  1. L'exemple suivant montre la commande permettant de créer une table partitionnée nommée srcpart à l'aide du client MaxCompute :

     create table if not exists srcpart_test (key string, value string) partitioned by (ds string, hr string) lifecycle 30;

  2. Insérez des données dans la table. Voici un exemple de commande :

     insert into table srcpart_test partition(ds='20201220', hr='11') values ('123', 'val_123'), ('76', 'val_76'), ('447', 'val_447'), ('1234', 'val_1234');
     insert into table srcpart_test partition(ds='20201220', hr='12') values ('3', 'val_3'), ('12331', 'val_12331'), ('42', 'val_42'), ('12', 'val_12');
     insert into table srcpart_test partition(ds='20201221', hr='11') values ('543', 'val_543'), ('2', 'val_2'), ('4', 'val_4'), ('9', 'val_9');
     insert into table srcpart_test partition(ds='20201221', hr='12') values ('23', 'val_23'), ('56', 'val_56'), ('4111', 'val_4111'), ('12333', 'val_12333');

  3. Exécutez la commande analyze pour collecter les statistiques de colonne d'une partition spécifique. Voici un exemple de commande :

     analyze table srcpart_test partition(ds='20201221') compute statistics for columns (key , value);

  4. Exécutez la commande show statistic pour vérifier les statistiques de colonne collectées. Voici un exemple de commande :

     show statistic srcpart_test partition (ds='20201221') columns (key , value);

     Les résultats suivants sont renvoyés :

     ID = 20210105121800689g28p****
     (ds=20201221,hr=11) key:MaxLength       3.0
     (ds=20201221,hr=11) key:AvgLength:      1.0
     (ds=20201221,hr=11) key:DistinctNum:    4.0
     (ds=20201221,hr=11) key:NullNum:        0.0
     (ds=20201221,hr=11) key:TopK:   {2=1.0, 4=1.0, 543=1.0, 9=1.0}
     
     (ds=20201221,hr=11) value:MaxLength     7.0
     (ds=20201221,hr=11) value:AvgLength:    5.0
     (ds=20201221,hr=11) value:DistinctNum:  4.0
     (ds=20201221,hr=11) value:NullNum:      0.0
     (ds=20201221,hr=11) value:TopK:         {val_2=1.0, val_4=1.0, val_543=1.0, val_9=1.0}
     
     (ds=20201221,hr=12) key:MaxLength       5.0
     (ds=20201221,hr=12) key:AvgLength:      3.0
     (ds=20201221,hr=12) key:DistinctNum:    4.0
     (ds=20201221,hr=12) key:NullNum:        0.0
     (ds=20201221,hr=12) key:TopK:   {12333=1.0, 23=1.0, 4111=1.0, 56=1.0}
     
     (ds=20201221,hr=12) value:MaxLength     9.0
     (ds=20201221,hr=12) value:AvgLength:    7.0
     (ds=20201221,hr=12) value:DistinctNum:  4.0
     (ds=20201221,hr=12) value:NullNum:      0.0
     (ds=20201221,hr=12) value:TopK:         {val_12333=1.0, val_23=1.0, val_4111=1.0, val_56=1.0}

Actualisation du nombre d'enregistrements d'une table dans les métadonnées

Diverses tâches dans MaxCompute peuvent modifier le nombre d'enregistrements d'une table. Toutefois, ces tâches ne mettent pas toujours à jour le nombre d'enregistrements avec précision. Cela peut s'expliquer par la nature dynamique des tâches distribuées et par le moment des mises à jour des données. Pour garantir l'exactitude du nombre d'enregistrements, vous pouvez utiliser la commande analyze afin d'actualiser la valeur dans les métadonnées. Vous pouvez afficher le nombre d'enregistrements d'une table dans Data Map de DataWorks. Pour plus d'informations, consultez la rubrique Affichage des détails d'une table.

• Pour actualiser le nombre d'enregistrements de l'ensemble de la table :

  set odps.sql.analyze.table.stats=only; 
  analyze table <table_name> compute statistics for columns;  

  table_name correspond au nom de la table.

• Pour actualiser le nombre d'enregistrements d'une colonne spécifique de la table :

  set odps.sql.analyze.table.stats=only; 
  analyze table <table_name> compute statistics for columns (<column_name>);

  table_name correspond au nom de la table et column_name au nom de la colonne.

• Pour actualiser le nombre d'enregistrements d'une colonne spécifique dans une partition :

  set odps.sql.analyze.table.stats=only; 
  analyze table <table_name> partition(<pt_spec>) compute statistics for columns (<column_name>);

  table_name correspond au nom de la table, pt_spec à la valeur de la partition et column_name au nom de la colonne.

Vous pouvez exécuter les deux commandes suivantes au niveau de la session pour définir les propriétés :

• set odps.optimizer.stat.collect.auto=true; : active la fonctionnalité Freeride pour collecter automatiquement les statistiques de colonne des tables.

• set odps.optimizer.stat.collect.plan=xx; : configure un plan de collecte pour recueillir des métriques de statistiques de colonne spécifiques pour les colonnes indiquées.

  -- Collect the avgColLen metric for the key column in the target_table table.
  set odps.optimizer.stat.collect.plan={"target_table":"{\"key\":\"AVG_COL_LEN\"}"}
  
  -- Collect the min and max metrics for the s_binary column and the topK and nNulls metrics for the s_int column in the target_table table.
  set odps.optimizer.stat.collect.plan={"target_table":"{\"s_binary\":\"MIN,MAX\",\"s_int\":\"TOPK,NULLS\"}"};

La liste suivante présente la correspondance entre les métriques de statistiques de colonne et leurs identifiants dans la commande set odps.optimizer.stat.collect.plan=xx; :

• min : MIN

• max : MAX

• nNulls : NULLS

• avgColLen : AVG_COL_LEN

• maxColLen : MAX_COL_LEN

• ndv : NDV

• topK : TOPK

Pour illustrer ces trois méthodes, commencez par créer une table source nommée src_test et insérez-y des données. Voici des exemples de commandes :

create table if not exists src_test (key string, value string);
insert overwrite table src_test values ('100', 'val_100'), ('100', 'val_50'), ('200', 'val_200'), ('200', 'val_300');

• create table : les statistiques de colonne sont collectées lors de la création de la table cible. Voici un exemple de commande :

  -- Create the target table.
  set odps.optimizer.stat.collect.auto=true;
  set odps.optimizer.stat.collect.plan={"target_test":"{\"key\":\"AVG_COL_LEN,NULLS\"}"};
  create table target_test as select key, value from src_test;
  -- Check the collected column stats.
  show statistic target_test columns;

  Les résultats suivants sont renvoyés :

  key:AvgLength: 3.0
  key:NullNum:  0.0

• insert into : les statistiques de colonne sont collectées lorsque vous ajoutez des données à l'aide de la commande insert into. Voici un exemple de commande :

  -- Create a target table.
  create table freeride_insert_into_table like src_test;
  -- Append data.
  set odps.optimizer.stat.collect.auto=true;
  set odps.optimizer.stat.collect.plan={"freeride_insert_into_table":"{\"key\":\"AVG_COL_LEN,NULLS\"}"};
  insert into table freeride_insert_into_table select key, value from src order by key, value limit 10;
  -- Check the collected column stats.
  show statistic freeride_insert_into_table columns;

• insert overwrite : les statistiques de colonne sont collectées lorsque vous écrasez des données à l'aide de la commande insert overwrite. Voici un exemple de commande :

  -- Create a target table.
  create table freeride_insert_overwrite_table like src_test;
  -- Overwrite data.
  set odps.optimizer.stat.collect.auto=true;
  set odps.optimizer.stat.collect.plan={"freeride_insert_overwrite_table":"{\"key\":\"AVG_COL_LEN,NULLS\"}"};
  insert overwrite table freeride_insert_overwrite_table select key, value from src_test order by key, value limit 10;
  -- Check the collected column stats.
  show statistic freeride_insert_overwrite_table columns;