Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Schemata excluded from dbt docs generate with Redshift #1960

Closed
1 of 5 tasks
nydnarb opened this issue Nov 27, 2019 · 0 comments · Fixed by #2003
Closed
1 of 5 tasks

Schemata excluded from dbt docs generate with Redshift #1960

nydnarb opened this issue Nov 27, 2019 · 0 comments · Fixed by #2003
Labels
bug Something isn't working good_first_issue Straightforward + self-contained changes, good for new contributors! redshift
Milestone

Comments

@nydnarb
Copy link
Contributor

nydnarb commented Nov 27, 2019

Describe the bug

In Redshift, the following query is run during dbt docs generate:

    with late_binding as (
      select
        'rover'::varchar as table_database,
        table_schema,
        table_name,
        'LATE BINDING VIEW'::varchar as table_type,
        null::text as table_comment,

        column_name,
        column_index,
        column_type,
        null::text as column_comment
      from pg_get_late_binding_view_cols()
        cols(table_schema name, table_name name, column_name name,
             column_type varchar,
             column_index int)
        order by "column_index"
    ),

    table_owners as (

        select
            'rover'::varchar as table_database,
            schemaname as table_schema,
            tablename as table_name,
            tableowner as table_owner

        from pg_tables

        union all

        select
            'rover'::varchar as table_database,
            schemaname as table_schema,
            viewname as table_name,
            viewowner as table_owner

        from pg_views

    ),

    tables as (

      select
        table_catalog as table_database,
        table_schema,
        table_name,
        table_type

      from information_schema.tables

    ),

    table_columns as (

        select
            'rover'::varchar as table_database,
            table_schema,
            table_name,
            null::varchar as table_comment,

            column_name,
            ordinal_position as column_index,
            data_type as column_type,
            null::varchar as column_comment


        from information_schema."columns"

    ),

    unioned as (

        select *
        from tables
        join table_columns using (table_database, table_schema, table_name)

        union all

        select *
        from late_binding

    )

    select *,
        table_database || '.' || table_schema || '.' || table_name as table_id

    from unioned
    join table_owners using (table_database, table_schema, table_name)

    where table_schema != 'information_schema'
      and table_schema not like 'pg_%'

    order by "column_index"

This query is intended to ignore schemata that begin with pg_. However, in Redshift, an underscore _ represents any single character when using the like operator. I discovered this because we have a schema called pganalytics and docs were not generated for it. I think the fix is as simple as escaping the underscore as described here: https://docs.aws.amazon.com/redshift/latest/dg/r_patternmatching_condition_like.html

System information

Which database are you using dbt with?

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

The output of dbt --version:

pipey-dbt (master) $ dbt --version
installed version: 0.14.3
   latest version: 0.15.0

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

The operating system you're using:
MacOS

The output of python --version:
Python 3.7.3

@nydnarb nydnarb added bug Something isn't working triage labels Nov 27, 2019
@drewbanin drewbanin added redshift and removed triage labels Nov 27, 2019
@drewbanin drewbanin added this to the 0.15.1 milestone Nov 27, 2019
@drewbanin drewbanin added the good_first_issue Straightforward + self-contained changes, good for new contributors! label Nov 27, 2019
beckjake added a commit that referenced this issue Dec 12, 2019
…t-wildcard-pg

escape the wildcard underscore in postgres "like" queries (#1960)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working good_first_issue Straightforward + self-contained changes, good for new contributors! redshift
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants