[sqlalchemy] Improve the performance of TrinoDialect.get_view_names

[john-bodley]

Description

Querying the information_schema.views table seems to be sub-performant for determining which entities are views—I speculate this is because all the view definitions need to be extracted/compiled. A more performant approach is to use information_schema.tables with the appropriate type filter.

For example for a very large schema at Airbnb (comprising of over 100k entities) the following query,

SELECT
    table_name
FROM 
    information_schema.tables
WHERE 
    table_schema = '<schema>' AND 
    table_type = 'VIEW'

took ~ 5 seconds, whereas:

SELECT
    table_name
FROM
    information_schema.views
WHERE    
    table_schema = '<schema>'

was still running after 10 minutes.

Note there were no prior integration tests for the TrinoDialect.get_view_names method so I added some to cover this logic and threw in a few extra for free which should cover all the various scenarios.

Non-technical explanation

Release notes

( ) This is not user-visible or docs only and no release notes are required.
( ) Release notes are required, please propose a release note for me.
(x) Release notes are required, with the following suggested text:

* Improve the performance of get_view_names in SQLAlchemy. ({issue}`267`)

[john-bodley]

I needed to explicitly set the schema as part of the engine otherwise Trino barfed when trying to create the views with the

Schema must be specified when session schema is not set

error. I tried a number of things including explicitly including i) executing the USE test statement, and ii) including the schema name in the view name, i.e., test.my_view however this failed like because this was escaped to be "test.my_view" rather than "test"."my_view".

[mdesmet]

On first sight it can be done by doing something like:

create_view(
            ...,
            schema="test")

See https://github.com/kvesteri/sqlalchemy-utils/blob/1f6e2d6c029f5c578be0e48c7baa98e6984b7bf8/sqlalchemy_utils/view.py#L63

I also think you could include the schema in the pytest param @pytest.mark.parametrize('trino_connection', ['memory/test'], indirect=True) but personally I would prefer to have it in the create_view call.

[john-bodley]

@mdesmet the create_view method does not support setting an explicit schema. Regrettably also defining,

metadata = sqla.MetaData(schema=schema)

did not work and thus the only solution I was able to find—I spent hours trying to get this to work—was to explicitly set the schema at the time of the engine creation.

Additionally setting the schema in the engine ensures that _get_default_schema_name returns a non-None value which is necessary for the additional tests I added to test existing logic which wasn't previously tested.

[hovaesco]

@hashhar are you aware from where the performance difference comes from? It might be that query to information_schema.tables do some kind of predicate pushdown due to table_type = 'VIEW'?

[mdesmet]

Instead of overriding the schema for any test you can just do

Suggested change

	@pytest.mark.parametrize('trino_connection', ['memory'], indirect=True)
	@pytest.mark.parametrize('trino_connection', ['memory/test'], indirect=True)

[mdesmet]

If you remove the default schema from the fixture you can rid yourself of this @patch

[john-bodley]

@mdesmet thanks for the review. I've addressed your comments.

[mdesmet]

@hashhar : PTAL I think we should make this change based on your feedback on Slack

hashhar
For views we call metadata.getViews in InformationSchemaPageSource - that methods ends up retrieving view definitions as well.
For tables we call listTables + listViews (in InformationSchemaPageSource) - neither of which need view definitions. So actually the tables is faster since it just lists things.

[ebyhr]

Please fix the commit title as "Improve the performance of get_view_names in SQLAlchemy"

[ebyhr]

Worth leaving code comment why this query uses information_schema.tables.

[hashhar]

note that we're now starting to test integration with a non-SQLAlchemy library. This is one of many other alternative libraries which enhance SQLAlchemy.

(This is needed because SQLAlchemy itself doesn't provide inbuilt functionality to create views - it does provide extension points which the sqlalchemy-utils library implements).