On this page

    Querying Apache Iceberg Tables

    The SELECT command queries table data and table metadata, and can query by snapshot ID.

    note:

    Dremio supports only the copy-on-write storage mechanism and reads only the latest data files for each Iceberg v2 table that you run SQL commands against. Dremio does not support Iceberg v2 tables that have merge-on-read manifests.

    Querying a Table’s Data

    Syntax
    SELECT [ <column1_name> [ , <column2_name> ... ] | * ] 
    FROM <table_path>.<table_name>
    [WHERE where_condition]
    [constructs]
    

    Parameters

    <column1_name> [ , <column2_name> ... ]

    String

    The name of the column or columns that you want to query.


    *

    String

    Indicates that you want to query all columns in the table.


    <table_path>

    String

    The path in which the table is located.


    <table_name>

    String

    The name of the table that you want to query.


    WHERE where_condition

    String

    The condition to use to query a subset of the rows in the table.


    contructs

    String

    The SELECT command supports many constructs, such as GROUP BY, ORDER BY, SORT, and more.

    Metadata Queries

    Iceberg includes helpful system-table references which provide an easy access to Iceberg-specific information on tables, including:

    • The data files for a table
    • The history of a table
    • The manifest files for a table
    • The snapshots for a table

    Querying a Table’s Data File Metadata

    Queries use the table_files() function.

    To run a SELECT command, the user must have Dremio’s SELECT privilege.

    Syntax
    SELECT * 
    FROM TABLE( table_files('<table_path>'.'<table_name>') )
    

    Dremio returns records that have these fields:

    ColumnData TypeDescription
    file_pathVARCHARFull file path and name
    file_formatVARCHARFormat, e.g. PARQUET
    record_countBIGINTNumber of rows
    file_size_in_bytesBIGINTSize of file
    column_sizesVARCHARList of columns with size of each column
    value_countsVARCHARList of columns with number of records with a value
    null_value_countsVARCHARList of columns with number of records as NULL
    nan_value_countsVARCHARList of columns with number of records as NaN
    lower_boundsVARCHARList of columns with lower bound of each
    upper_boundsVARCHARList of columns with upper bound of each
    key_metadataVARCHARKey metrics
    split_offsetsVARCHARSplit offsets

    Parameters

    <table_path>

    String

    The path in which the table is located.


    <table_name>

    String

    The name of the table that you want to query.

    Querying a Table’s History Metadata

    Queries use the table_history() function.

    To run a SELECT command, the user must have Dremio’s SELECT privilege.

    Syntax
    SELECT * 
    FROM TABLE( table_history('<table_path>'.'<table_name>') )
    

    Dremio returns records that have these fields:

    ColumnData TypeDescription
    made_current_atTIMESTAMPThe timestamp the Iceberg snapshot was made at
    snapshot_idVARCHARThe Iceberg snapshot ID
    parent_idVARCHARThe parent snapshot ID, null if not exists
    is_current_ancestorBOOLEANIf the snapshot is part of the current history, shows abandoned snapshots

    Example

    Example
    SELECT * 
    FROM TABLE( table_history('myTable')) 
    WHERE snapshot_id = 4593468819579153853
    

    Querying a Table’s Manifest File Metadata

    Queries use the table_manifests() function.

    To run a SELECT command, the user must have Dremio’s SELECT privilege.

    Syntax
    SELECT * 
    FROM TABLE( table_manifests('<table_path>'.'<table_name>') )
    

    Dremio returns records that have these fields:

    ColumnData TypeDescription
    pathVARCHARFull path and name of manifest file
    lengthBIGINTSize in bytes
    partition_spec_idVARCHAR
    added_snapshot_idVARCHARID of snapshot added to manifest
    added_data_files_countBIGINTNumber of new data files added
    existing_data_files_countBIGINTNumber of existing data files
    deleted_data_files_countBIGINTNumber of files removed
    partition_summariesVARCHARPartition information

    Querying a Table’s Snapshot Metadata

    Queries use the table_snapshot() function.

    To run a SELECT command, the user must have Dremio’s SELECT privilege.

    Syntax
    SELECT * 
    FROM TABLE( table_snapshot('<table_path>'.'<table_name>') )
    

    Dremio returns records that have these fields:

    ColumnData TypeDescription
    committed_atTIMESTAMPThe timestamp the Iceberg snapshot was committed
    snapshot_idVARCHARThe Iceberg snapshot ID
    parent_idVARCHARThe parent snapshot ID, null if not exists
    operationVARCHARThe Iceberg operation (e.g. append)
    manifest_listVARCHARList of manifest files for the snapshot
    summaryVARCHARAdditional attributes (records added, etc)

    Example

    Finds the number of snapshots for a table
    SELECT count(*) 
    FROM TABLE( table_snapshot('myTable')) 
    GROUP BY snapshot_id
    

    Time-travel Queries

    Iceberg tables support snapshot ID-based references to specify an earlier version of a table to read.

    Time Travel by Snapshot ID

    To run a SELECT command, the user must have Dremio’s SELECT privilege.

    Syntax
    SELECT <column_name> 
    FROM <table_path>.<table_name> AT SNAPSHOT '<snapshot-id>'
    

    Parameters

    <column_name>

    String

    The name of the column that you want to query.


    <table_path>

    String

    The path in which the table is located.


    <table_name>

    String

    The name of the table that you want to query.


    <snapshot_id>

    String

    A snapshot ID obtained either through the table_history() or table_snapshot() metadata function.

    Example

    Example
    SELECT *
    FROM myTable AT SNAPSHOT '5393090506354317772'