MaxCompute:Perguntas frequentes sobre operações DQL

Como resolver o erro "Repeated key in GROUP BY" ao executar uma instrução SQL do MaxCompute?

• Problema

  Ao executar uma instrução SQL do MaxCompute, o sistema retorna o seguinte erro:

  FAILED: ODPS-0130071:Semantic analysis exception - Repeated key in GROUP BY.

• Causa

  Uso de constante após SELECT DISTINCT, o que não é permitido.

• Solução

  Divida a instrução SQL em duas camadas. A camada interna processa a lógica DISTINCT sem constantes; a externa adiciona os dados constantes.

Como resolver o erro "Expression not in GROUP BY key" ao executar uma instrução SQL do MaxCompute?

• Problema

  Ao executar uma instrução SQL do MaxCompute, o sistema retorna o seguinte erro:

  FAILED: ODPS-0130071:Semantic analysis exception - Expression not in GROUP BY key : line 1:xx ‘xxx’

• Causa

  Referência direta a uma coluna não incluída na cláusula GROUP BY. Para obter mais informações, consulte Cláusula GROUP BY (col_list).

• Solução

  Garanta que as colunas na lista SELECT pertençam à cláusula GROUP BY ou sejam processadas por funções de agregação, como SUM ou COUNT.

Executei GROUP BY na Tabela A para gerar a Tabela B. A Tabela B tem menos linhas que a Tabela A, mas seu armazenamento físico é 10 vezes maior. Por que isso acontece?

O MaxCompute usa compressão colunar para armazenamento. Valores consecutivos semelhantes na mesma coluna resultam em alta taxa de compressão. Quando odps.sql.groupby.skewindata=true está ativado, os dados são dispersos, reduzindo a taxa de compressão. Para melhorar a compressão, realize uma classificação local ao gravar dados com uma instrução SQL.

A execução de uma consulta GROUP BY em 10 bilhões de registros afeta o desempenho? Existe limite de volume de dados para GROUP BY?

Não. O GROUP BY não possui limite de volume de dados.

Como ocorre a classificação dos dados retornados por uma consulta do MaxCompute?

A leitura de dados das tabelas do MaxCompute ocorre em ordem indefinida. Sem cláusula de classificação, os resultados da consulta também ficam desordenados.

Para classificar os dados, adicione a cláusula order by xx limit n à instrução SQL.

Para uma classificação completa, defina o valor limit n como total number of records + 1.

O MaxCompute suporta a sintaxe ORDER BY FIELD NULLS LAST?

Sim, o MaxCompute suporta essa sintaxe. Para obter mais informações, consulte Diferenças em relação a outras sintaxes SQL.

Como resolver o erro "ORDER BY must be used with a LIMIT clause" ao executar uma instrução SQL do MaxCompute?

• Problema

  Ao executar uma instrução SQL do MaxCompute, o sistema retorna o seguinte erro:

  FAILED: ODPS-0130071:[1,27] Semantic analysis exception - ORDER BY must be used with a LIMIT clause, please set odps.sql.validate.orderby.limit=false to use it.

• Causa

  O ORDER BY executa uma classificação global em um único nó de execução. Portanto, uma cláusula LIMIT é necessária por padrão para evitar processamento excessivo de dados nesse nó.

• Solução

  Se o cenário exigir ORDER BY sem cláusula LIMIT, desative esse requisito de uma das seguintes maneiras:

  • No nível do projeto: Execute o comando setproject odps.sql.validate.orderby.limit=false; para desativar a obrigatoriedade de uso da cláusula limit com order by.

  • No nível da sessão: Execute o comando set odps.sql.validate.orderby.limit=false; para desativar a obrigatoriedade de uso da cláusula limit com order by. Envie este comando junto com a instrução SQL.

    Nota

    Desativar o requisito order by-limit implica classificar um grande conjunto de dados em um único nó de execução, o que degrada o desempenho e aumenta o consumo de recursos.

Para obter mais informações sobre ORDER BY, consulte Cláusula ORDER BY (ORDER_condition).

Ao executar uma instrução SQL do MaxCompute com uma subconsulta NOT IN, ela retorna dezenas de milhares de registros. No entanto, se a subconsulta após IN ou NOT IN retornar partições, o máximo permitido é 1.000. Como implementar essa consulta caso seja obrigatório usar NOT IN?

select * from a where a.ds not in (select ds from b);
Change the statement to the following:
select a.* from a left outer join (select distinct ds from b) bb on a.ds=bb.ds where bb.ds is null;              

Como mesclar duas tabelas sem associação?

Para mesclagem vertical, use union all. Para mesclagem horizontal, utilize a função row_number para adicionar uma coluna de ID a ambas as tabelas, faça o join delas pelo ID e selecione os campos necessários. Para obter mais informações, consulte Union ou ROW_NUMBER.

Como resolver o erro "ValidateJsonSize error" durante uma operação UNION ALL?

• Sintomas

  Ao executar uma instrução SQL com 200 operações UNION ALL, como select count(1) as co from client_table union all ..., ocorre o seguinte erro:

  FAILED: build/release64/task/fuxiWrapper.cpp(344): ExceptionBase: Submit fuxi Job failed, {
      "ErrCode": "RPC_FAILED_REPLY",
      "ErrMsg": "exception: ExceptionBase:build/release64/fuxi/fuximaster/fuxi_master.cpp(1018): ExceptionBase: StartAppFail: ExceptionBase:build/release64/fuxi/fuximaster/app_master_mgr.cpp(706): ExceptionBase: ValidateJsonSize error: the size of compressed plan is larger than 1024KB\nStack      

• Causas

  • Causa 1: O plano de execução excede o limite de 1024 KB da arquitetura subjacente. O tamanho do plano de execução não é diretamente proporcional ao comprimento da instrução SQL e não pode ser estimado antecipadamente.

  • Causa 2: Número excessivo de partições.

  • Causa 3: Excesso de arquivos pequenos.

• Soluções

  • Solução para a Causa 1: Divida a instrução SQL longa para evitar exceder o limite de comprimento.

  • Solução para a Causa 2: Ajuste o número de partições. Para obter mais informações, consulte Partition.

  • Solução para a Causa 3: Mescle os arquivos pequenos.

Como resolver o erro "Both left and right aliases encountered in JOIN" durante uma operação JOIN?

• Problema

  Ao executar uma instrução SQL do MaxCompute, o sistema retorna o seguinte erro:

  FAILED: ODPS-0130071:Semantic analysis exception - Both left and right aliases encountered in JOIN : line 3:3 ‘xx’: . I f you really want to perform this join, try mapjoin

• Causas

  • Causa 1: Especificação de join não equitativo na cláusula ON, como table1.c1>table2.c3.

  • Causa 2: Um lado da condição JOIN referencia colunas de ambas as tabelas, como table1.col1 = concat(table1.col2,table2.col3).

• Soluções

  • Solução para a Causa 1: Modifique a instrução SQL. A condição de junção deve ser um join equitativo.

    Nota

    Se for necessário usar um join não equitativo, adicione uma dica mapjoin. Para obter mais informações, consulte ODPS-0130071.

  • Solução para a Causa 2: Se uma das tabelas for pequena, utilize o método MAPJOIN.

Como resolver o erro "Maximum 16 join inputs allowed" durante uma operação JOIN?

• Sintomas

  Ao executar uma instrução SQL do MaxCompute, o sistema retorna o seguinte erro:

  FAILED: ODPS-0123065:Join exception - Maximum 16 join inputs allowed

• Causa

  No SQL do MaxCompute, uma operação MAPJOIN suporta no máximo seis tabelas pequenas, e uma única operação JOIN suporta no máximo 16 tabelas.

• Solução

  Faça o join de algumas tabelas pequenas em uma tabela temporária primeiro para reduzir o número de tabelas de entrada.

Durante uma operação JOIN, o número de registros no resultado supera o da tabela original. Como corrigir?

• Sintomas

  Após executar a seguinte instrução SQL do MaxCompute, o número de registros no resultado da consulta é maior que o número de registros em table1.

  select count(*) from table1 a left outer join table2 b on a.ID = b.ID;

• Causa

  Um left outer join retorna todos os registros da table1, mesmo sem correspondentes na table2. Se a table2 contiver IDs duplicados, o número de registros retornados aumenta. Exemplo:

  Suponha que table1 contenha os seguintes dados.

  id	values
  1	a
  1	b
  2	c

  Suponha que table2 contenha os seguintes dados.

  id	values
  1	A
  1	B
  3	D

  O comando select count(*) from table1 a left outer join table2 b on a.ID = b.ID; retorna o seguinte resultado.

  id1	values1	id2	values2
  1	b	1	B
  1	b	1	A
  1	a	1	B
  1	a	1	A
  2	c	NULL	NULL

  • Registros com id=1 existem em ambas as tabelas. Ocorre um produto cartesiano, retornando quatro registros.

  • O registro com id=2 existe apenas na table1. Um registro é retornado.

  • Registros com id=3 existem apenas na table2. Nenhum registro é retornado porque não há correspondentes na table1.

• Solução

  Verifique se a table2 contém IDs duplicados:

  select id, count(*) as cnt from table2 group by id having cnt>1 limit 10;

  Para evitar o produto cartesiano, reescreva a instrução SQL da seguinte forma:

  select * from table1 a left outer join (select distinct id from table2) b on a.id = b.id;

Por que uma varredura completa da tabela é proibida em uma operação JOIN mesmo com condição de partição especificada?

• Problema

  Ao executar o mesmo código em dois projetos, ele tem êxito em um, mas falha no outro.

  select t.stat_date 
  from fddev.tmp_001 t  
  left outer join (select '20180830' as ds from fddev.dual ) t1 
  on t.ds = 20180830
  group by t.stat_date; 

  A execução com falha apresenta o seguinte erro:

  Table(fddev,tmp_001) is full scan with all partitions,please specify partitions predicates.

• Causa

  Em uma operação SELECT, a condição de partição deve estar na cláusula WHERE. Usar a cláusula ON para essa finalidade foge ao padrão.

  A execução teve êxito em um projeto devido à configuração com o comando set odps.sql.outerjoin.supports.filters=false. Esse comando converte a condição na cláusula ON em condição de filtro. Tal comportamento é compatível com a sintaxe do Hive, mas não está em conformidade com o padrão SQL.

• Solução

  Coloque a condição de filtro de partição na cláusula WHERE.

Em uma operação JOIN, a eliminação de partições funciona se a condição estiver na cláusula ON ou na cláusula WHERE?

• Se a condição de eliminação de partições estiver na cláusula WHERE, a eliminação terá efeito.

• Se a condição estiver na cláusula ON, a eliminação de partições funcionará na tabela de detalhes, mas não na tabela principal, resultando em varredura completa desta última.

Para obter mais informações sobre eliminação de partições, consulte Avaliar a validade da eliminação de partições.

Como usar MAPJOIN para armazenar em cache várias tabelas pequenas?

O MAPJOIN acelera consultas ao armazenar tabelas pequenas em cache na memória. Especifique os aliases das tabelas na dica MAPJOIN.

+——————————————————————————————————————————+

| Field           | Type       | Label | Comment                                     |
+——————————————————————————————————————————+

| sepal_length    | double     |       |                                             |

| sepal_width     | double     |       |                                             |

| petal_length    | double     |       |                                             |

| petal_width     | double     |       |                                             |

| category        | string     |       |                                             |

+——————————————————————————————————————————+
                

select 
  /*+ mapjoin(b,c) */
  a.category,
  b.cnt as cnt_category,
  c.cnt as cnt_all
from iris a
join
(
  select count(*) as cnt,category from iris group by category
) b
on a.category = b.category
cross join 
(
  select count(*) as cnt from iris
) c;              

É possível trocar as tabelas grande e pequena em um MAPJOIN?

Sim. O sistema distingue entre tabelas grandes e pequenas com base no tamanho de armazenamento e carrega a tabela pequena na memória para acelerar a operação JOIN.

Após definir uma condição de filtro em uma instrução SQL do MaxCompute, um erro indica que os dados de entrada excedem 100 GB. Como corrigir?

Filtre os dados pelo campo de partição primeiro e depois pelos demais campos. O cálculo do volume de dados de entrada ocorre após a filtragem no nível da partição.

A cláusula WHERE para consultas difusas no SQL do MaxCompute suporta expressões regulares?

Sim. Por exemplo, select * from user_info where address rlike '[0-9]{9}'; encontra registros que contêm um número de nove dígitos.

Para sincronizar apenas 100 registros, como usar LIMIT na condição de filtro WHERE?

Não há suporte para LIMIT em condições de filtro. Use uma instrução SQL para selecionar 100 registros primeiro e, em seguida, execute a operação de sincronização.

Como melhorar a eficiência da consulta? É possível ajustar as configurações de partição?

Particione a tabela por seus campos de partição para permitir adicionar, atualizar ou ler dados em partições específicas sem varredura completa. Para obter mais informações, consulte Operações de tabela.

O SQL do MaxCompute suporta a instrução WITH AS?

Sim. O MaxCompute suporta Common Table Expressions (CTEs) SQL padrão para melhorar a legibilidade e a eficiência da execução. Para obter mais informações, consulte COMMON TABLE EXPRESSION (CTE).

Como dividir uma linha de dados em várias linhas?

Use LATERAL VIEW com funções geradoras de tabelas, como Split e Explode, para dividir uma linha em várias e, em seguida, agregar os dados resultantes.

No arquivo odps_config.ini do cliente, defini use_instance_tunnel=false e instance_tunnel_max_record=10. Por que uma instrução SELECT ainda retorna muitos registros?

Altere use_instance_tunnel=false para use_instance_tunnel=true para que a configuração instance_tunnel_max_record tenha efeito.

Como usar uma expressão regular para verificar se um campo contém caracteres chineses?

select 'field' rlike '[\\x{4e00}-\\x{9fa5}]+';