MaxCompute:Otimizador

O MaxCompute oferece dois métodos para coletar estatísticas:

• Framework de coleta assíncrona (Analyze): Colete estatísticas de forma assíncrona por meio do comando analyze. Inicie este método manualmente.

  Nota

  A versão do MaxCompute client deve ser 0,35 ou superior.

• Framework de coleta síncrona (Freeride): Coleta automaticamente as estatísticas de coluna à medida que os dados são gerados. Embora seja mais automatizado, esse método pode afetar a latência das consultas.

O MaxCompute coleta as seguintes métricas de estatísticas de coluna para diferentes tipos de dados.

A tabela a seguir descreve os cenários de uso para cada métrica de estatística de coluna.

Coletar estatísticas de coluna

Esta seção descreve como usar o Analyze com tabelas particionadas e não particionadas.

• Tabelas não particionadas

  Colete estatísticas para colunas específicas ou para todas as colunas.

  1. Crie uma tabela não particionada chamada analyze2_test usando o MaxCompute client. Exemplo de comando:

     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. Insira dados na tabela. Exemplo de comando:

     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. Execute o comando analyze para coletar estatísticas de uma única coluna, de múltiplas colunas ou de todas as colunas. Exemplos de comandos:

     -- 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. Execute o comando show statistic para verificar as estatísticas de coluna coletadas. Exemplos de comandos:

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

     Os seguintes resultados são retornados:

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

• Tabelas particionadas

  Colete estatísticas de coluna para uma partição específica.

  1. O exemplo abaixo mostra o comando para criar uma tabela particionada chamada srcpart usando o MaxCompute client:

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

  2. Insira dados na tabela. Exemplo de comando:

     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. Execute o comando analyze para coletar estatísticas de coluna de uma partição específica. Exemplo de comando:

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

  4. Execute o comando show statistic para verificar as estatísticas de coluna coletadas. Exemplo de comando:

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

     Os seguintes resultados são retornados:

     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}

Atualizar a contagem de registros de uma tabela nos metadados

Diversas tarefas no MaxCompute podem alterar o número de registros em uma tabela. No entanto, essas tarefas nem sempre atualizam a contagem de registros com precisão, devido à natureza dinâmica das tarefas distribuídas e ao tempo de atualização dos dados. Para garantir que a contagem esteja correta, utilize o comando analyze para atualizar o valor nos metadados. Visualize a contagem de registros de uma tabela no DataWorks Data Map. Para mais informações, consulte View table details.

• Para atualizar a contagem de registros de toda a tabela:

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

  table_name é o nome da tabela.

• Para atualizar a contagem de registros de uma coluna específica na tabela:

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

  table_name é o nome da tabela e column_name é o nome da coluna.

• Para atualizar a contagem de registros de uma coluna específica em uma partição:

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

  table_name é o nome da tabela, pt_spec é o valor da partição e column_name é o nome da coluna.

Execute os dois comandos abaixo no nível da sessão para definir as propriedades:

• set odps.optimizer.stat.collect.auto=true;: Ativa o recurso Freeride para coletar automaticamente estatísticas de coluna das tabelas.

• set odps.optimizer.stat.collect.plan=xx;: Configure um plano de coleta para obter métricas específicas de estatísticas de coluna para colunas determinadas.

  -- 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\"}"};

A lista a seguir apresenta o mapeamento entre as métricas de estatísticas de coluna e seus identificadores no comando 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

Para demonstrar esses três métodos, primeiro crie uma tabela de source chamada src_test e insira dados nela. Exemplos de comandos:

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: As estatísticas de coluna são coletadas durante a criação da tabela de destino. Exemplo de comando:

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

  Os seguintes resultados são retornados:

  key:AvgLength: 3.0
  key:NullNum:  0.0

• insert into: As estatísticas de coluna são coletadas ao anexar dados usando insert into. Exemplo de comando:

  -- 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: As estatísticas de coluna são coletadas ao sobrescrever dados usando insert overwrite. Exemplo de comando:

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