Notas de uso e limites de consultas em tabelas Delta - MaxCompute: Central de Documentação do Alibaba Cloud

Sintaxe

[WITH <cte>[, ...] ]
SELECT [ALL | DISTINCT] <select_expr>[, <except_expr>)][, <replace_expr>] ...
       FROM <table_reference>
       [TIMESTAMP | VERSION AS OF expr]
       [TIMESTAMP | VERSION BETWEEN start_expr AND end_expr]
       [WHERE <where_condition>]
       [GROUP BY {<col_list> | ROLLUP(<col_list>)}]
           [HAVING <having_condition>]
       [ORDER BY <order_condition>]
       [DISTRIBUTE BY <distribute_condition> [SORT BY <sort_condition>]|[ CLUSTER BY <cluster_condition>] ]
       [LIMIT <number>]
       [WINDOW <window_clause>]

Use TIMESTAMP | VERSION AS OF expr para consultas time travel e TIMESTAMP | VERSION BETWEEN start_expr AND end_expr para consultas incrementais.

Consultas time travel

A consulta time travel retorna um snapshot histórico da tabela, ou seja, o estado dos dados no momento especificado ou anterior a ele, conforme a versão indicada.

TIMESTAMP AS OF

SELECT * FROM <table> TIMESTAMP AS OF <expr>

O parâmetro expr aceita qualquer um dos seguintes formatos:

Formato	Exemplo	Descrição
String TIMESTAMP	`'2023-01-01 00:00:00.123'`	Timestamp preciso com milissegundos
String DATETIME	`'2023-01-01 00:00:00'`	Timestamp sem milissegundos
String DATE	`'2023-01-01'`	Apenas data; a hora assume como padrão meia-noite
`current_timestamp()`	`current_timestamp()`	Hora atual
`getDate() + N`	`getDate() - 3600`	N segundos em relação ao momento atual; negativo = passado, positivo = futuro
`get_latest_timestamp(tablename [, number])`	`get_latest_timestamp('mf_tt2', 2)`	Timestamp da N-ésima operação DML mais recente (padrão: 1 = última). O timestamp retornado pode ser idêntico para diferentes valores de `number`.

Para acesso entre projetos, formate tablename como ProjectName.TableName. Para o modelo de três camadas, use ProjectName.SchemaName.TableName.

Limites:

O intervalo válido de consulta é [CreateTableTimestamp, expr], onde CreateTableTimestamp corresponde ao momento de confirmação (commit) da criação da tabela.
Se expr for anterior à hora de criação da tabela ou superior a N horas atrás, o sistema retornará um erro. O valor de N é definido pela propriedade acid.data.retain.hours durante a criação da tabela. Por exemplo, se acid.data.retain.hours for 72 e expr referenciar 80 horas atrás, a consulta falhará.
Se expr corresponder exatamente a N horas atrás, também poderá ocorrer um erro devido à latência em nível de segundos nos sistemas internos.

Aviso

Evite usar TIMESTAMP AS OF current_timestamp() - <seconds> para consultas próximas ao limite de retenção. Use get_latest_timestamp() para referenciar commits recentes com segurança.

VERSION AS OF

SELECT * FROM <table> VERSION AS OF <expr>

O parâmetro expr aceita:

Formato	Exemplo	Descrição
Constante BIGINT	`3`	Um número de versão específico
`get_latest_version(tablename [, number])`	`get_latest_version('mf_tt2', 2)`	Versão da N-ésima operação DML mais recente (padrão: 1 = última). Diferentemente de `get_latest_timestamp`, a versão retornada varia conforme cada valor de `number`.

Para a formatação de tablename, siga as mesmas regras de get_latest_timestamp().

Limites:

Cada operação DML gera um número de versão estritamente incremental. Execute SHOW HISTORY FOR TABLE/PARTITION para visualizar todas as versões.
O intervalo de versão válido é [CreateTableVersion, expr]. O valor padrão de CreateTableVersion é 1.
O sistema retornará um erro se a versão corresponder a um horário de commit anterior a N horas (onde N = acid.data.retain.hours) ou se a versão for menor que 1.
Se expr exceder a versão da última operação DML, ocorrerá um erro.

Nota

Use get_latest_version() para obter um número de versão válido e evitar erros de intervalo inválido.

Consultas incrementais

A consulta incremental retorna somente as linhas adicionadas ou modificadas dentro de um intervalo de tempo ou de versão, equivalente ao delta entre dois snapshots.

Nota

Dados gerados por compactação não são tratados como novos dados e ficam excluídos dos resultados de consultas incrementais.

TIMESTAMP BETWEEN

SELECT * FROM <table> TIMESTAMP BETWEEN <start_expr> AND <end_expr>

O intervalo de tempo é (start_expr, end_expr] — aberto à esquerda e fechado à direita. Ambas as expressões seguem os mesmos formatos suportados por TIMESTAMP AS OF.

Limites:

Se start_expr for anterior à hora de criação da tabela ou superior a N horas atrás, o sistema retornará um erro. N = acid.data.retain.hours.
Se end_expr for posterior à hora do último commit DML, o comportamento dependerá de acid.incremental.query.out.of.time.range.enabled:
- Padrão (false): retorna um erro.
- Definido como true: a consulta retorna todos os dados incrementais dentro de (start_expr, end_expr].

Nota

Para permitir consultas que se estendam além do commit mais recente, defina a propriedade como true:

ALTER TABLE <table> SET tblproperties("acid.incremental.query.out.of.time.range.enabled"="true");

VERSION BETWEEN

SELECT * FROM <table> VERSION BETWEEN <start_expr> AND <end_expr>

O intervalo de versão é (start_expr, end_expr] — aberto à esquerda e fechado à direita. Ambas as expressões seguem os mesmos formatos suportados por VERSION AS OF.

Limites:

O sistema resolve start_expr para um horário de commit. Se esse horário for superior a N horas atrás ou se a versão for menor que 1, ocorrerá um erro. N = acid.data.retain.hours.
Se end_expr exceder a versão da última operação DML, o comportamento dependerá de acid.incremental.query.out.of.time.range.enabled:
- Padrão (false): retorna um erro.
- Definido como true: a consulta retorna todos os dados incrementais dentro de (start_expr, end_expr].

Notas de uso

Apenas tabelas Delta oferecem suporte a consultas time travel e consultas incrementais.
Chaves duplicadas: Quando várias linhas compartilham a mesma chave primária, apenas a linha mais recente é retornada. Linhas no estado DELETE são excluídas.
Change Data Capture (CDC): A consulta do estado de atualização de dados em formatos semelhantes ao Change Data Capture (CDC) ainda não tem suporte; esse recurso está planejado para uma versão futura.
Tabelas excluídas ou renomeadas: Não é possível consultar dados históricos de uma tabela após ela ter sido excluída ou renomeada. Restaure a tabela primeiro e depois execute a consulta.
Mesma tabela, múltiplos qualificadores: Ao executar uma consulta time travel ou incremental na mesma tabela dentro de uma única instrução SQL, defina os timestamps ou versões das consultas com os mesmos valores.
Tabelas particionadas: Especifique uma partição na cláusula WHERE para limitar a varredura a essa partição e reduzir o tempo de consulta.
Concorrência: Tabelas Delta utilizam Multi-Version Concurrency Control (MVCC) para isolar leituras e gravações concorrentes. Há suporte ao nível de isolamento Read Committed.

Exemplos

Os exemplos a seguir utilizam uma tabela Delta particionada chamada mf_tt2.

Configurar dados de exemplo

-- Table creation. Version = 1.
-- Run "SHOW HISTORY FOR TABLE mf_tt2" to confirm.
CREATE TABLE mf_tt2 (
  pk bigint NOT NULL PRIMARY KEY,
  val bigint NOT NULL)
PARTITIONED BY (dd string, hh string)
tblproperties ("transactional"="true");

-- INSERT OVERWRITE. Version = 2.
INSERT OVERWRITE TABLE mf_tt2 PARTITION (dd='01', hh='01') VALUES (1, 1), (2, 2), (3, 3);

-- INSERT INTO. Version = 3.
INSERT INTO TABLE mf_tt2 PARTITION (dd='01', hh='01') VALUES (3, 30), (4, 4), (5, 5);

Para verificar a hora de criação da tabela e o histórico de versões antes de executar as consultas:

-- Get the table creation timestamp
DESC EXTENDED mf_tt2;

O resultado retornado é o seguinte.

+------------------------------------------------------------------------------------+
| Owner: ALIYUN$****_doctest@test.aliyunid.com | Project: doc_test_prod                               |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2023-06-26 09:31:38                                      |
| LastDDLTime:              2023-06-26 09:31:38                                      |
| LastModifiedTime:         2023-06-26 09:32:31                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 8541                                               |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
+------------------------------------------------------------------------------------+
| pk       | bigint |       |               | false    | NULL         |              |
| val      | bigint |       |               | false    | NULL         |              |
+------------------------------------------------------------------------------------+
| Partition Columns:                                                                 |
+------------------------------------------------------------------------------------+
| dd              | string     |                                                     |
| hh              | string     |                                                     |
+------------------------------------------------------------------------------------+
| Extended Info:                                                                     |
+------------------------------------------------------------------------------------+
| TableID:                  bec515a56cc9492c8f906a224c62****                         |
| IsArchived:               false                                                    |
| PhysicalSize:             25623                                                    |
| FileNum:                  9                                                        |
| StoredAs:                 AliOrc                                                   |
| CompressionStrategy:      normal                                                   |
| ClusterType:              hash                                                     |
| BucketNum:                16                                                       |
| ClusterColumns:           [pk]                                                     |
| SortColumns:              [pk ASC]                                                 |
+------------------------------------------------------------------------------------+

-- Get all DML version numbers and commit times
SHOW HISTORY FOR TABLE mf_tt2 PARTITION (dd='01', hh='01');

O resultado retornado é o seguinte.

ID = 20230626021756157ghict5k****
ObjectType      ObjectId                                ObjectName              VERSION(LSN)            Time                    Operation
PARTITION       4764c8e1cb634a4fb9c21f3fc850****        dd=01/hh=01             0000000000000002        2023-06-26 09:31:56     CREATE
PARTITION       4764c8e1cb634a4fb9c21f3fc850****        dd=01/hh=01             0000000000000003        2023-06-26 09:32:32     APPEND

A saída de SHOW HISTORY exibe cada operação, seu número de versão, horário do commit e tipo de operação (CREATE, APPEND, etc.).

Exemplos de consulta time travel

Snapshot em uma data e hora específicas — todos os dados até 09:33:00:

SELECT * FROM mf_tt2 TIMESTAMP AS OF '2023-06-26 09:33:00' WHERE dd = '01' AND hh = '01';

Retorna todas as 5 linhas gravadas pelas versões 2 e 3 (pk 1–5, com pk=3 mostrando val=30 da gravação mais recente).

Snapshot na versão 2 — antes do INSERT INTO:

SELECT * FROM mf_tt2 VERSION AS OF 2 WHERE dd = '01' AND hh = '01';

Retorna as 3 linhas do INSERT OVERWRITE: pk=1, pk=2, pk=3 (val=3).

Snapshot no horário atual:

SELECT * FROM mf_tt2 TIMESTAMP AS OF current_timestamp() WHERE dd = '01' AND hh = '01';

Snapshot de 10 segundos atrás:

SELECT * FROM mf_tt2 TIMESTAMP AS OF current_timestamp() - 10 WHERE dd = '01' AND hh = '01';

Snapshot no segundo commit mais recente (usando get_latest_timestamp):

SELECT * FROM mf_tt2 TIMESTAMP AS OF get_latest_timestamp('mf_tt2', 2) WHERE dd = '01' AND hh = '01';

Retorna as 3 linhas da versão 2.

Snapshot na segunda versão mais recente (usando get_latest_version):

SELECT * FROM mf_tt2 VERSION AS OF get_latest_version('mf_tt2', 2) WHERE dd = '01' AND hh = '01';

Retorna as 3 linhas da versão 2.

Exemplos de consulta incremental

Alterações entre dois timestamps de commit:

SELECT * FROM mf_tt2 TIMESTAMP BETWEEN '2023-06-26 09:31:40' AND '2023-06-26 09:32:00' WHERE dd = '01' AND hh = '01';

Retorna as 3 linhas gravadas pela versão 2.

Alterações entre a versão 2 e a versão 3:

SELECT * FROM mf_tt2 VERSION BETWEEN 2 AND 3 WHERE dd = '01' AND hh = '01';

Retorna as 3 linhas gravadas pela versão 3: pk=3 (val=30), pk=4, pk=5.

Últimos 300 segundos com acid.incremental.query.out.of.time.range.enabled definido como false (padrão):

SELECT * FROM mf_tt2 TIMESTAMP BETWEEN current_timestamp() - 301 AND current_timestamp() WHERE dd = '01' AND hh='01';

Retorna um erro porque end_expr excede o timestamp do commit mais recente:

FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed:
com.aliyun.odps.meta.exception.MetaException: ...
Incremental query can't exceed current version. Current version timestamp: 2023-06-26 09:32:32, input timestamp is: 2023-06-26 10:47:55

Para permitir que a consulta se estenda além do commit mais recente, ative a propriedade:

ALTER TABLE mf_tt2 SET tblproperties("acid.incremental.query.out.of.time.range.enabled"="true");

Em seguida, execute a consulta novamente. O resultado será vazio (nenhum dado novo foi gravado nos últimos 300 segundos):

+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
+------------+------------+----+----+

Alterações do terceiro commit mais recente até o commit mais recente:

SELECT * FROM mf_tt2 TIMESTAMP BETWEEN get_latest_timestamp('mf_tt2', 3) AND get_latest_timestamp('mf_tt2') WHERE dd = '01' AND hh = '01';

Retorna todas as 5 linhas (abrange os commits da versão 2 e da versão 3).

Alterações da terceira versão mais recente até a versão mais recente:

SELECT * FROM mf_tt2 VERSION BETWEEN get_latest_version('mf_tt2', 3) AND get_latest_version('mf_tt2') WHERE dd = '01' AND hh = '01';

Retorna todas as 5 linhas.