get_tables_by_pattern_sql doesn't work with redshift external tables

[brendan-cook-87]

Describe the bug

get_tables_by_pattern_sql doesn't return redshift external tables

Steps to reproduce

Create any redshift spectrum tables and they are not listed in information_schema.tables
but instead in svv_external_tables.
For a redshift spectrum external table, the get_tables_by_pattern macro will return no tables existing in the provided schema.

Expected results

The external tables in the listed schema should be returned by the macro.

Actual results

No tables are returned

Screenshots and log output

If applicable, add screenshots or log output to help explain your problem.

System information

The contents of your packages.yml file:
packages:

• package: dbt-labs/dbt_utils
  version: 0.9.6
• package: LewisDavies/upstream_prod
  version: 0.1.1
• package: dbt-labs/codegen
  version: 0.9.0
• package: dbt-labs/audit_helper
  version: 0.6.0
• package: dbt-labs/redshift
  version: 0.8.0
• package: brooklyn-data/dbt_artifacts
  version: 2.2.1
• package: calogica/dbt_expectations
  version: 0.8.0
• package: dbt-labs/dbt_external_tables
  version: 0.8.2
• package: Snowflake-Labs/dbt_constraints
  version: 0.5.3
• package: tnightengale/dbt_meta_testing
  version: 0.3.6
• package: mjirv/dbt_datamocktool
  version: 0.2.1

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

The output of dbt --version:

Core:
  - installed: 1.3.1
  - latest:    1.3.2 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - redshift: 1.3.0 - Up to date!
  - postgres: 1.3.1 - Update available!

  At least one plugin is out of date or incompatible with dbt-core.
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Additional context

Redshift spectrum external tables are listed in svv_external_tables
This should be fixed by creating a new redshift__ dispatch macro which unions external tables with
the result of the default query.

Are you interested in contributing the fix?

Fix is ready, but wants an issue to attach to the PR.

[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.