Dicas MAPJOIN - MaxCompute: Central de Documentação do Alibaba Cloud

Ao unir uma tabela grande a uma ou mais tabelas pequenas, um JOIN padrão redistribui os dados pelo cluster durante a fase de shuffle. Isso gera sobrecarga de rede e torna as consultas mais lentas. A dica MAPJOIN elimina o shuffle ao carregar as tabelas pequenas na memória de cada worker durante o estágio de map. Assim, a tabela grande não trafega pela rede. Use MAPJOIN para reduzir a E/S de rede e acelerar essas consultas.

Como funciona

Um JOIN padrão executa em três estágios: map, shuffle e reduce. A união das tabelas ocorre no estágio de reduce, o que exige a redistribuição dos dados pelo cluster.

O MAPJOIN ignora o shuffle. Durante o estágio de map, o MaxCompute carrega todos os dados das tabelas pequenas especificadas na memória de cada worker que processa a tabela grande. A leitura da tabela grande ocorre localmente: os workers levam as tabelas pequenas até os dados, e não o contrário.

Sintaxe

Adicione a dica /*+ mapjoin(<table_alias>) */ logo após SELECT para ativar o MAPJOIN:

SELECT /*+ mapjoin(<small_table_alias>) */
    ...
FROM <large_table> ...

Observe os seguintes pontos:

Use o alias da tabela ou subconsulta na dica, e não o nome original da tabela.
Para especificar várias tabelas pequenas, separe os aliases por vírgulas: /*+ mapjoin(a,b,c) */.
Subconsultas podem atuar como tabelas pequenas.
O MAPJOIN aceita non-equi joins e condições OR na cláusula ON. O SQL padrão do MaxCompute não oferece suporte a esses recursos em joins comuns.
Para gerar um produto cartesiano, use ON 1 = 1 em vez de uma condição de junção. Exemplo: SELECT /*+ mapjoin(a) */ a.id FROM shop a JOIN table_name b ON 1=1;. Essa abordagem pode aumentar significativamente o volume de dados.

Durante a execução, o sistema pode reescrever subconsultas como SCALAR, IN, NOT IN, EXISTS e NOT EXISTS como operações JOIN. Se o resultado da subconsulta for pequeno, adicione uma dica MAPJOIN à instrução da subconsulta para usar explicitamente o algoritmo MAPJOIN.

Limites

Limite	Detalhe
Memória	O tamanho total na memória de todas as tabelas pequenas após a descompactação não deve exceder 512 MB. O MaxCompute compacta os dados antes do armazenamento, portanto, o tamanho descompactado na memória é consideravelmente maior que o tamanho do arquivo armazenado. O limite de 512 MB aplica-se ao tamanho descompactado, e não ao tamanho do arquivo compactado.
Quantidade de tabelas pequenas	Especifique no máximo 128 tabelas pequenas em uma única dica MAPJOIN. Especificar mais de 128 resulta em erro de sintaxe.
LEFT OUTER JOIN	A tabela à esquerda deve ser a tabela grande.
RIGHT OUTER JOIN	A tabela à direita deve ser a tabela grande.
INNER JOIN	A tabela grande pode estar à esquerda ou à direita.
FULL OUTER JOIN	Não é possível usar MAPJOIN.

Dados de exemplo

Os exemplos neste tópico usam duas tabelas: sale_detail e sale_detail_sj.

-- Create a partitioned table named sale_detail.
CREATE TABLE IF NOT EXISTS sale_detail
(
  shop_name     STRING,
  customer_id   STRING,
  total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

CREATE TABLE IF NOT EXISTS sale_detail_sj
(
  shop_name     STRING,
  customer_id   STRING,
  total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

-- Add partitions.
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china');
ALTER TABLE sale_detail_sj ADD PARTITION (sale_date='2013', region='china');

-- Insert data.
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china')
  VALUES ('s1','c1',100.1), ('s2','c2',100.2), ('s3','c3',100.3);

INSERT INTO sale_detail_sj PARTITION (sale_date='2013', region='china')
  VALUES ('s1','c1',100.1), ('s2','c2',100.2), ('s5','c2',100.2), ('s2','c2',100.2);

Exemplos

Uso básico

Use MAPJOIN para unir sale_detail_sj (tabela pequena, alias a) a sale_detail (tabela grande, alias b) com um equi-join padrão:

SET odps.sql.allow.fullscan=true;

SELECT /*+ mapjoin(a) */
    a.shop_name,
    a.customer_id,
    a.total_price
FROM sale_detail_sj a
JOIN sale_detail b
ON a.shop_name = b.shop_name;

Non-equi join com condição OR

O MAPJOIN aceita non-equi joins e condições OR, recursos indisponíveis em joins SQL padrão do MaxCompute. O exemplo a seguir recupera linhas em que sale_detail_sj.total_price é menor que sale_detail.total_price ou em que a soma dos dois preços é inferior a 500:

SET odps.sql.allow.fullscan=true;

SELECT /*+ mapjoin(a) */
    a.shop_name,
    a.total_price,
    b.total_price
FROM sale_detail_sj a
JOIN sale_detail b
ON a.total_price < b.total_price OR a.total_price + b.total_price < 500;

A consulta retorna:

+-----------+-------------+--------------+
| shop_name | total_price | total_price2 |
+-----------+-------------+--------------+
| s1        | 100.1       | 100.1        |
| s2        | 100.2       | 100.1        |
| s5        | 100.2       | 100.1        |
| s2        | 100.2       | 100.1        |
| s1        | 100.1       | 100.2        |
| s2        | 100.2       | 100.2        |
| s5        | 100.2       | 100.2        |
| s2        | 100.2       | 100.2        |
| s1        | 100.1       | 100.3        |
| s2        | 100.2       | 100.3        |
| s5        | 100.2       | 100.3        |
| s2        | 100.2       | 100.3        |
+-----------+-------------+--------------+