[CT-1951] [Feature] get_relations_by_pattern should move from utils to Core

[joellabes]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

This is prompted by dbt-labs/dbt-utils#753, which aims to add support for Redshift external tables to the above macro.

It is unclear whether/how the other adapters' implementations support external tables, and as I started looking into it I realised that since it was poking around in the information schema directly, it is probably something that would be better suited to a consistent, reliable cross-database implementation. That is to say, it would be better suited to living in the adapters!

Describe alternatives you've considered

YOLO-merging the changes and accepting that Redshift finds external tables where BQ wouldn't (NB: I have no idea whether BQ's version finds external tables, that's the whole problem). I'll be back in this same spot in 6 months when someone opens another utils issue for another adapter, and so on.

Who will this benefit?

Mostly me? Also users of smaller adapters, especially those that don't have an info schema.

I forgot to fully deprecate its older and less flexible get_relations_by_prefix sibling macro during the v1 migration. For the avoidance of doubt, you shouldn't migrate that over.

Both of these would stick around in dbt utils using the same sort of forwarding setup as {{ dbt.type_int() }} and friends had in the 0.9.x vintage.

Are you interested in contributing this feature?

If needed, but there's probably others who could do it better!

Anything else?

No response

[dbeatty10]

Thanks for opening this @joellabes !

Big picture

since it was poking around in the information schema directly, it is probably something that would be better suited to a consistent, reliable cross-database implementation

💯 agreed that this would benefit from a reliable cross-database implementation!

Connecting the dots

I see this as spiritually related to:

• [CT-1665] [Feature] Surface ordinal_position in get_columns_in_relation #6449

And it might resolve many or all of these:

• Add ClickHouse support to get_tables_by_pattern_sql dbt-utils#698
• Update get_tables_by_pattern_sql.sql dbt-utils#563
• get_tables_by_pattern_sql() doesn't work in Athena / Presto due to ilike operator usage dbt-utils#546
• get_tables_by_pattern_sql doesn't work with redshift external tables dbt-utils#752
• get_tables_by_pattern_sql To Allow More Complex Pattern Matching dbt-utils#648
• [0.7.0] Deprecate get_relations_by_prefix in favor of get_relations_by_pattern dbt-utils#269

And these contain some of the historical context as it relates to external tables:

• Added case for handling postgres foreign tables... dbt-utils#476
• Snowflake: get_tables_by_pattern_sql handling external tables dbt-utils#351

Brass tacks

What's involved

I didn't robustly check all the details, but it looks like removing get_relations_by_pattern from dbt-utils would also take a few other macros along with it. So the listing of macros-to-remove from dbt_utils would be:

• get_relations_by_pattern
• get_tables_by_pattern_sql
• get_matching_schemata
• get_table_types_sql

These are found in the following files:

• https://github.com/dbt-labs/dbt-utils/blob/1.0.0/macros/sql/get_relations_by_pattern.sql
• https://github.com/dbt-labs/dbt-utils/blob/1.0.0/macros/sql/get_tables_by_pattern_sql.sql
• https://github.com/dbt-labs/dbt-utils/blob/1.0.0/macros/sql/get_table_types_sql.sql

This would require double-checking -- maybe that's it, but maybe there would be more.

Considering the current implementation of get_relations_by_pattern as-is

The current implementation of get_relations_by_pattern defers the actual pattern matching bit to get_tables_by_pattern_sql (whose default of implementation relies on ilike).

Rather than just upstreaming get_relations_by_pattern, etc to dbt-core as-is, I'd like to invert the dynamic and consider:

• What abstractions could/should we have in dbt adapters in order to "get a list of relations matching a pattern"?
• How can dbt-utils hook utilize those abstractions as-needed to maintain backwards-compatibility for as long as it wants?

Maybe the answers to those will lead us to just lift-and-shift the current implementations. But similar to dbt_utils.get_relations_by_prefix leading to dbt_utils.get_relations_by_pattern, the exploration might yield a slightly different set of abstractions.

[joellabes]

• What abstractions could/should we have in dbt adapters in order to "get a list of relations matching a pattern"?

I wouldn't be averse to this supporting regex for example if it could be done in a broadly compatible way.

[joellabes]

This just came up again in dbt-labs/dbt-utils#779

[brendan-cook-87]

This is still outstanding 10 months later. Is it possible we get the original PR i made to resolve this merged @joellabes seeing as there appears to be no traction on a fix in dbt-core.