feat: list materialized views

[dpprdan]

Is your feature request related to a problem?

ibis does not list_tables() materialized views in Postgres.

PS C:\> psql service=test
psql (15.2)
Type "help" for help.

postgres=# CREATE MATERIALIZED VIEW mymatview AS SELECT 1 AS col1;
SELECT 1
postgres=# \d
                 List of relations
 Schema |   Name    |       Type        |  Owner
--------+-----------+-------------------+----------
 public | mymatview | materialized view | postgres
(1 row)

postgres=# exit

import ibis
ibis.options.interactive = True

pg_url = "postgresql+psycopg:///?service=test"
con = ibis.postgres.connect(url=pg_url)

con.list_tables()

C:\ProgramData\Miniconda3\envs\ibis_test\lib\site-packages\ibis\backends\postgres\registry.py:119: UserWarning: locale specific date formats (%c, %x, %X) are not yet implemented for Windows
  warnings.warn(

[]

Describe the solution you'd like

IIUC (and that’s a big IF as I’ve been trying out Ibis only for a few days now) is using these SQLAlchemy inspector functions.

ibis/ibis/backends/base/sql/alchemy/__init__.py

Lines 101 to 104 in 6b2cbaa

	def list_tables(self, like=None, database=None):
	tables = self.inspector.get_table_names(schema=database)
	views = self.inspector.get_view_names(schema=database)
	return self._filter_with_like(tables + views, like)

Fortunately, SQLAlchemy has gained a get_materialized_view_names() function in version 2.0.

import sqlalchemy as sa
pg_eng = sa.create_engine(pg_url)
pg_con = pg_eng.connect()
inspector = sa.inspect(pg_con)
inspector.get_materialized_view_names()

['mymatview']

So a fix might possibly be as easy as (plus a SQLAlchemy version check)

def list_tables(self, like=None, database=None):
    tables = self.inspector.get_table_names(schema=database)
    views = self.inspector.get_view_names(schema=database)
    matviews = self.inspector.get_materialized_view_names(schema=database)
    return self._filter_with_like(tables + views + matviews, like)

get_materialized_view_names() seems to behave if an engine does not support materialized views (yet).

ddb_eng = sa.create_engine("duckdb:///palmer_penguins.ddb")
ddb_con = ddb_eng.connect()

ddb_inspector = sa.inspect(ddb_con)
ddb_inspector.get_materialized_view_names()

[]

Ideally Ibis would support materialized views even more, of course, e.g. with

• create_materialized_view(),
• refresh_materialized_view(), and
• drop_materialized_view().

Accessing materialized views does not seem to be a problem.

con.table("mymatview")

┏━━━━━━━┓
┃ col1  ┃
┡━━━━━━━┩
│ int32 │
├───────┤
│     1 │
└───────┘

What version of ibis are you running?

5.0.0

What backend(s) are you using, if any?

PostgreSQL (DuckDB as a does-not-support-matviews backend)

Code of Conduct

• I agree to follow this project's Code of Conduct

[dpprdan]

Relatedly, I wonder whether list_tables() ought to list temporary tables and views as well (get_temp_table_names(), get_temp_view_names())

[cpcloud]

Hi @dpprdan 👋🏻! Thanks for creating an issue about this.

Listing materialized views in list_tables is definitely something we can look into!

As far as DDL for create/refresh/drop, we'll need to get a list of the backends we support that support materialized views and take a look at their APIs and decide what options to support.

Here are the backend we support in ibis that support materialized views along with links to their documentation:

1. PostgreSQL
2. BigQuery
3. Snowflake
4. ClickHouse
5. Trino
6. MSSQL ⚠️ Only in Azure Synapse

[cpcloud]

Relatedly, I wonder whether list_tables() ought to list temporary tables and views as well (get_temp_table_names(), get_temp_view_names())

Those inspector methods appear to be SQLAlchemy 2.0 only, and ibis supports SQLAlchemy<2 so we'd need to figure out a way to get that information without relying on 2.0-only APIs.

[dpprdan]

Those inspector methods appear to be SQLAlchemy 2.0 only, and ibis supports SQLAlchemy<2 so we'd need to figure out a way to get that information without relying on 2.0-only APIs.

AFAICT it is the other way around?

I see "New in version 1.0.0." for get_temp_table_names(), get_temp_view_names() and "New in version 2.0." for get_materialized_view_names().

Should we split the matview-DDL issue from this "list matviews" issue?

[cpcloud]

Those inspector methods appear to be SQLAlchemy 2.0 only, and ibis supports SQLAlchemy<2 so we'd need to figure out a way to get that information without relying on 2.0-only APIs.

AFAICT it is the other way around?

I see "New in version 1.0.0." for get_temp_table_names(), get_temp_view_names() and "New in version 2.0." for get_materialized_view_names().

Ah, yep, you're right!

Should we split the matview-DDL issue from this "list matviews" issue?

Yeah, definitely.

[cpcloud]

Closing in favor of #8382. Thanks for the discussion!