get_tables_by_pattern_sql To Allow More Complex Pattern Matching

[CharlieJ20]

Describe the feature

Currently it finds tables based on SQL ilike, but I've had to make some slight alterations to allow more complex matching through REGEXP.

Describe alternatives you've considered

None, the pattern i needed to match was not possible with ilike

Additional context

Using Snowflake and currently running on DBT 1.0.8.

Who will this benefit?

Anyone who needs to union tables that have naming structures more complex than SQL ilike allows. In my example, the tables were analogous to "Table" being unioned to "Table_S2", which doesn't have an ilike regex comparison that matches both, but regexp allows the following pattern to work: "Table(()|_S\d)"

Are you interested in contributing this feature?

[CharlieJ20]

link to line of code seems to have disappeared, but this is the line which I think needs ability for more complex pattern matching: https://github.com/dbt-labs/dbt-utils/blob/main/macros/sql/get_tables_by_pattern_sql.sql#L14

[joellabes]

@CharlieJ20 Another interesting one! I'm less averse to this one, but still have a couple of comments:

• if we change to regex, do all existing usages continue to work in a backwards-compatible manner? My guess: proooobably?
• do all (or at least the core four of Postgres/redshift/snowflake/bigquery) adapters support the same flavour of regex, so that you get consistent results?

I'd welcome a PR to change this behaviour, as long as it doesn't break the existing usages and gives consistent results.

Another potential approach for you is to cast too wide a net using this macro as-is, and then iterate over it using the re module built into dbt's jinja context to exclude anything you don't actually want.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.