MaxCompute:Otimizador

O MaxCompute permite usar os seguintes métodos para coletar métricas de estatísticas de coluna:

• Analyze: método de coleta assíncrono. Execute o comando analyze para coletar métricas de estatísticas de coluna de forma assíncrona. A coleta ativa é necessária.

  Nota

  A versão do cliente MaxCompute deve ser posterior à 0,35.

• Freeride: método de coleta síncrono. À medida que os dados são gerados em uma tabela, as métricas de estatísticas de coluna desses dados são coletadas automaticamente. Esse método é automatizado, mas impacta a latência da consulta.

A tabela a seguir lista as métricas de estatísticas de coluna coletáveis para diferentes tipos de dados.

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

Coletar as métricas de estatísticas de coluna

Esta seção usa uma tabela particionada e uma tabela não particionada como exemplos para descrever como usar o Analyze.

• Tabela não particionada

  Use o Analyze para coletar as métricas de estatísticas de coluna de uma ou mais colunas específicas ou de todas as colunas em uma tabela não particionada.

  1. Execute o seguinte comando no cliente MaxCompute para criar uma tabela não particionada chamada analyze2_test:

     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. Execute o seguinte comando para inserir dados na tabela:

     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 as métricas de estatísticas de coluna de uma ou mais colunas específicas ou de todas as colunas na tabela. Exemplos:

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

  4. Execute o comando show statistic para validar os resultados da coleta. Exemplos:

     -- Test the collection result of the tinyint1 column. 
     show statistic analyze2_test columns (tinyint1);
     
     -- Test the collection results of the smallint1, string1, boolean1, and timestamp1 columns. 
     show statistic analyze2_test columns (smallint1, string1, boolean1, timestamp1);
     
     -- Test the collection results of all columns. 
     show statistic analyze2_test columns;

     O resultado retornado é semelhante ao seguinte:

     -- Collection result of the tinyint1 column: 
     ID = 20201126085225150gnqo****
     tinyint1:MaxValue:      20                   -- The value of max. 
     tinyint1:DistinctNum:   4.0                  -- The value of ndv. 
     tinyint1:MinValue:      1                    -- The value of min. 
     tinyint1:NullNum:       1.0                  -- The value of nNulls. 
     tinyint1:TopK:  {1=1.0, 10=1.0, 20=1.0}      -- The value of topK. 10=1.0 indicates that the occurrence frequency of column value 10 is 1. Up to 20 values with the highest occurrence frequency can be returned. 
     
     -- Collection results of 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                  -- The value of maxColLen. 
     string1:AvgLength:      3.0                  -- The value of 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}
     
     -- Collection results of 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}

• Tabela particionada

  Use o Analyze para coletar as métricas de estatísticas de coluna de uma partição específica em uma tabela particionada.

  1. Execute o seguinte comando no cliente MaxCompute para criar uma tabela particionada chamada srcpart:

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

  2. Execute o seguinte comando para inserir dados na tabela:

     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 as métricas de estatísticas de coluna de uma partição específica na tabela. Exemplo:

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

  4. Execute o comando show statistic para validar os resultados da coleta. Exemplo:

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

     O resultado retornado é semelhante ao seguinte:

     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 o número de registros de uma tabela nos metadados

Várias tarefas no MaxCompute podem afetar o número de registros em uma tabela. A maioria das tarefas coleta apenas o número de registros afetados pelas próprias tarefas. As estatísticas sobre o número de registros afetados pelas tarefas podem não ser precisas devido à natureza dinâmica das tarefas distribuídas e à incerteza do tempo de atualização dos dados. Portanto, execute o comando Analyze para atualizar as estatísticas sobre o número de registros de uma tabela nos metadados e garantir a precisão desse número. Visualize o número de registros de uma tabela no DataMap do DataWorks. Para obter mais informações, consulte Visualizar os detalhes de uma tabela.

• Atualize o número de registros em uma tabela.

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

  O parâmetro table_name especifica o nome da tabela.

• Atualize o número de registros em uma coluna de uma tabela.

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

  O parâmetro table_name especifica o nome da tabela. O parâmetro column_name especifica o nome da coluna.

• Atualize o número de registros em uma coluna de uma partição em uma tabela.

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

  O parâmetro table_name especifica o nome da tabela. O parâmetro pt_spec especifica a partição. O parâmetro column_name especifica o nome da coluna.

Para usar o Freeride, execute simultaneamente os seguintes comandos no nível da sessão para configure as propriedades:

• set odps.optimizer.stat.collect.auto=true;: Ative o Freeride para coletar automaticamente as métricas de estatísticas de coluna das tabelas.

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

  -- Collect the avgColLen metric of 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 of the s_binary column in the target_table table, and the topK and nNulls metrics of the s_int column in the table. 
  set odps.optimizer.stat.collect.plan={"target_table":"{\"s_binary\":\"MIN,MAX\",\"s_int\":\"TOPK,NULLS\"}"};

Mapeamentos entre métricas de estatísticas de coluna e parâmetros 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

Antes de usar o Freeride, prepare uma tabela de source. Por exemplo, execute os seguintes comandos para crie uma tabela de source chamada src_test e inserir dados nela:

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: Use o Freeride para coletar métricas de estatísticas de coluna durante a criação de uma tabela de destino chamada target. Instrução de exemplo:

  -- Create a destination 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;
  -- Test the collection results. 
  show statistic target_test columns;

  O resultado retornado é semelhante ao seguinte:

  key:AvgLength: 3.0
  key:NullNum:  0.0

• INSERT INTO: Use o Freeride para coletar métricas de estatísticas de coluna durante a execução da instrução INSERT INTO para anexar dados a uma tabela. Instrução de exemplo:

  -- Create a destination table. 
  create table freeride_insert_into_table like src_test;
  -- Append data to the table. 
  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;
  -- Test the collection results. 
  show statistic freeride_insert_into_table columns;

• INSERT OVERWRITE: Use o Freeride para coletar métricas de estatísticas de coluna durante a execução da instrução INSERT OVERWRITE para sobrescrever dados em uma tabela. Instrução de exemplo:

  -- Create a destination table. 
  create table freeride_insert_overwrite_table like src_test;
  -- Overwrite data in the table. 
  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;
  -- Test the collection results. 
  show statistic freeride_insert_overwrite_table columns;