Iceberg View Support

[Xuanwo]

We haven't talked about Iceberg Views yet and I'm not sure if you would like to support them in the future. I'm actually really interested in using views.

I don't want to include view support in this PR but I would like to future proof the catalog design to easily support views in the future.

The REST catalog is in the process of adding view support. However, I have the feeling that the initial REST catalog API wasn't designed with views in mind. There is one issue that comes up with the current design that we might be able to avoid for our catalog design. I will try to explain the issue.

Imagine a query engine wants to perform a query like the following:

SELECT first_name, last_name, age FROM users WHERE age > 18;

Without the catalog information the query engine doesn't know whether users refers to a view or a table. So if the catalog only exposes a load_table and load_view operation, the query engine doesn't know which one to call. I would prefer a load_tablelike (the naming is not important) operation that returns an

enum TableLike {
    Table(Table),
    View(View)
}

All iceberg catalogs except for the REST catalog return a "table metadata location" and it would be easy to distinguish between Tables and Views based on the metadata.

Let me know what you think.

Originally posted by @JanKaul in #54 (comment)

[Xuanwo]

Forward @JanKaul's discussion here.

[Xuanwo]

From @Xuanwo

Thank you for sharing! The relationship between view and table is quite interesting. At Databend, we consider a view as a special type of table, and our engine can distinguish the differences and determine how to execute the SQL accordingly.

I'm wondering if it's possible to avoid exposing the view details at the Catalog level and instead leave them for the catalog implementer to decide.

[Xuanwo]

From @JanKaul

Converting from Table to TableLike is straightforward:

impl From<Table> for TableLike {
    fn from(value: Table) -> Self {
        TableLike::Table(value)
    }
}

So you would only need to call table.into().

For the table scan operation you would need to check whether your input is a table or a view and then execute the corresponding scan operator. As long as we don't have view support it could look like:

let table = if let TableLike::Table(table) = tablelike {
        Ok(table)
    } else {
        Err(Error::new(... , "Views are not supported yet"))
    }?:

[Xuanwo]

Also invite @Fokko, @liurenjie1024，@ZENOTME to join this discussion.

[JanKaul]

I'm not familiar with the details of databend but I'm guessing that you would also need some way to distinguish between a table and a view. Can you think of an ideal design for databend that supports tables and views?

[Xuanwo]

Can you think of an ideal design for databend that supports tables and views?

Databend has a TableMeta that carries the engine used by this table: https://github.com/datafuselabs/databend/blob/main/src/meta/app/src/schema/table.rs#L217

pub struct TableMeta {
    pub schema: Arc<TableSchema>,
    pub catalog: String,
    pub engine: String,
    pub engine_options: BTreeMap<String, String>,
    ...
}

And we designed a special engine called VIEW. When we found that this table is using VIEW engine, we will built the ViewTable with given metadata and information and performs the real query.

[Xuanwo]

I'm guessing the problem we met here is iceberg has different spec for Table and View which needs different handling?

[JanKaul]

So for constructing TableMeta for views and tables, wouldn't you also need a TableLike to specify the correct engine?

[Xuanwo]

From @liurenjie1024

Hi, @JanKaul Good point for view. Except for view, I think there will be other entities like materialized view. So the question is about name resolution:

1. Should we keep separate method for resolving tables, views, event materialized view?

fn load_table(&self, table_name: TableId) -> Result<Table>;
fn load_view(&self, view_name: ViewId) -> Result<View>;

I think it's still useful to keep separate methods for each kind of entity. For example when I want to execute dml like:

insert into t values(1), (2)

The query engine knows that it should load a table to check it.

2. Should we have a method for resolving entities without knowing its entity type?

As @JanKaul said it's would be quite useful in query:

select * from t;

In this case the catalog api should provide sth like:

fn load_entity(&self, table: &TableId) -> Result<TableLike>

3. Should we keep both methods or just load_entity?

Keep load_entity method only can solve problem in 1. But I still want to keep methods in 1 for several reasons:

• The api is more user friendly.
• This gives concrete implementation more chances to do optimization, especially in cases where many tables and views are defined.

[Xuanwo]

So for constructing TableMeta for views and tables, wouldn't you also need a TableLike to specify the correct engine?

Oh, sorry for missing this part. We have those information from our SQL Parser directly, for exmaple, CREATE VIEW and CREATE TABLE.

[JanKaul]

Hi, @JanKaul Good point for view. Except for view, I think there will be other entities like materialized view. So the question is about name resolution:

1. Should we keep separate method for resolving tables, views, event materialized view?

fn load_table(&self, table_name: TableId) -> Result<Table>;
fn load_view(&self, view_name: ViewId) -> Result<View>;

I think it's still useful to keep separate methods for each kind of entity. For example when I want to execute dml like:

insert into t values(1), (2)

The query engine knows that it should load a table to check it.

2. Should we have a method for resolving entities without knowing its entity type?

As @JanKaul said it's would be quite useful in query:

select * from t;

In this case the catalog api should provide sth like:

fn load_entity(&self, table: &TableId) -> Result<TableLike>

3. Should we keep both methods or just `load_entity`?

Keep load_entity method only can solve problem in 1. But I still want to keep methods in 1 for several reasons:

* The api is more user friendly.

* This gives concrete implementation more chances to do optimization, especially in cases where many tables and views are defined.

Good points, @liurenjie1024. I think it would make sense to have load_table, load_view and load_entity. Just that for SELECT queries it would be good to use load_entity. For cases where the query engine knows whether it's a table or a view it can call load_table and load_view.

[JanKaul]

I'm guessing the problem we met here is iceberg has different spec for Table and View which needs different handling?

I'm not sure what the best procedure for the REST catalog is. Maybe optimistically try to fetch the table metadata and if it fails then fetch the view metadata.

For all catalogs that obtain the "metadata location" Serde should be able to handle this. Since TableMetadata and ViewMetadata have different structures, Serde can identify whether it's a table or a view during deserialization.

[JanKaul]

So for constructing TableMeta for views and tables, wouldn't you also need a TableLike to specify the correct engine?

Oh, sorry for missing this part. We have those information from our SQL Parser directly, for exmaple, CREATE VIEW and CREATE TABLE.

What if you use an external iceberg catalog? Do you still have that information?

[Xuanwo]

What if you use an external iceberg catalog? Do you still have that information?

Great point! This information is essential. Thank you for the explanation. Now I got your point.