E-MapReduce:Delta connector

Query by version

Version numbers are integers that start at 1 after the first INSERT and increment by 1 with each modification.

The following example overwrites delta_table and then queries an earlier version using time travel.

1. Overwrite the data in Spark SQL:

   INSERT OVERWRITE TABLE delta_table VALUES 5,6,7,8,9;

2. Confirm the current state from Trino:

   SELECT * FROM delta_table;

   Output:

    id
   ----
     5
     6
     7
     8
     9
   (5 rows)

3. Query the data as it was at version 1:

   SELECT * FROM delta_table FOR VERSION AS OF 1;

   Output:

    id
   ----
     2
     1
     3
     4
     0
   (5 rows)

Query by timestamp

Three timestamp types are supported: DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE.

• If you query data based on a timestamp of the DATE type, the data whose timestamp is 00:00:00 UTC on the query date is queried.

• If you query data based on a timestamp of the TIMESTAMP type, the data whose timestamp in UTC corresponds to the specified timestamp is queried. For example, to query data at 20:00:00 on February 15, 2022 UTC+8 using the TIMESTAMP type:

  Note

  In the TIMESTAMP form, the first TIMESTAMP keyword specifies the time travel mode and the second TIMESTAMP is the literal type keyword.

  SELECT * FROM delta_table FOR TIMESTAMP AS OF TIMESTAMP '2022-02-15 12:00:00';

  The following output is returned:

   id
  ----
    2
    0
    3
    4
    1
  (5 rows)

• If you query data based on a timestamp of the TIMESTAMP WITH TIME ZONE type, the data type is implicitly converted before the snapshot is read:

  SELECT * FROM delta_table FOR TIMESTAMP AS OF CAST('2022-02-15 20:00:00 +0800' AS TIMESTAMP WITH TIME ZONE);

Optimize a table

Run the OPTIMIZE command with ZORDER BY in Spark to rewrite and sort the table data. The following example optimizes the conn_zorder table, which has columns src_ip, src_port, dst_ip, and dst_port:

OPTIMIZE conn_zorder ZORDER BY (src_ip, src_port, dst_ip, dst_port);

Supported column types

Z-Ordering supports the following column data types: INT, LONG, DOUBLE, FLOAT, BINARY, BOOLEAN, STRING, and ARRAY.

Supported predicates for data skipping

When querying Z-Ordered data, the data skipping feature applies file-level filtering for these predicates: =, <, <=, >, and >=.

LIKE and IN predicates do not trigger full data skipping. However, queries using LIKE or IN can still benefit from partial ordering when the Z-Ordered columns narrow down the relevant file range.

Query examples

After optimization, queries that filter on Z-Ordered columns run significantly faster:

-- Filter on a single Z-Ordered column
SELECT COUNT(*) FROM conn_zorder WHERE src_ip > '64.';

-- Filter on multiple Z-Ordered columns
SELECT COUNT(*) FROM conn_zorder WHERE src_ip >= '64.' AND dst_ip < '192.' AND src_port < 1000 AND dst_port > 50000;