/*
* The EXPLAIN for the SELECT query below shows the data for the authorized columns
* of the Lake Formation table. The row table filter is added
* as an AND condition to the WHERE clause
*/
explain select * from aws_lakeformation.otfdb.cardata_lf where mileage < 50
00 and make = 'Buick';
*** Help information returned. 30 rows.
*** Total elapsed time was one minute and 30 seconds.
Explanation
---------------------------------------------------------------------------
1) First, we do an all-AMPs RETRIEVE step executing table operator
TD_OTFDB.TD_ICEBERG_READ in TD_MAP1 with a condition of ("(1=1)").
The size of Spool 2 is estimated with no confidence to be 20,000
rows (427,200,000 bytes). The estimated time for this step is
0.00 seconds.
2) Next, we do an all-AMPs RETRIEVE step in TD_MAP1 from Spool 2
(Last Use) by way of an all-rows scan executing table operator
TD_OTFDB.TD_ICEBERG_READ in TD_MAP1 with a condition of ("(MAKE =
'Buick') AND (MILEAGE <= 4999)") into Spool 4 (group_amps), which
is built locally on the AMPs.
< BEGIN EXPLAIN FOR REMOTE QUERY -->
We connect to Iceberg GLUE catalog, then we retrieve and process 1
data files, with estimated row count of 804, that is about 9035
total bytes of row data from remote table otfdb.cardata_lf, for
the qualifying columns price,mileage,make, with push-down
predicate
([{"type":"LITERAL_PREDICATES","expression":{"type":"AND","left":{"typ
e":"AND","left":{"type":"EQ","term":"make","value":"Buick"},"right":{"
type":"LT_EQ","term":"mileage","value":4999}},"right":{"type":"OR","le
ft":{"type":"GT","term":"price","value":14999.99},"right":{"type":"OR"
,"left":{"type":"LT","term":"mileage","value":10000},"right":{"type":"
EQ","term":"make","value":"Buick"}}}}}]).
<-- END EXPLAIN FOR REMOTE QUERY >
The size of Spool 4 is estimated with no confidence to be 20,000
Querying Tables
Iceberg and Delta Lake tables can be queried in a SQL statement like any other database table. They are referenced using a 3-level dot
notation: <datalakeName>.<databaseName>.<tableName> .
Examples of Select queries
Table Metadata Queries
Iceberg and Delta Lake table metadata such as table history, snapshots, manifests and partition information can be retrieved by invoking
system functions.
TD_SNAPSHOTS() Function
Returns Snapshot information for an Iceberg or Delta Lake table.
Syntax:
Example:
Iceberg -
rows (41,260,000 bytes). The estimated time for this step is 5.17
seconds.
3) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 4, are sent back to the user as the result
of statement 1. The total estimated time is 5.17 seconds.
+---------+---------+---------+---------+---------+---------+---------+----