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.

Sign up for GitHub

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

add macro to get columns #516

Merged
merged 13 commits into from
Mar 28, 2022
Merged

add macro to get columns #516

Changes from 1 commit
Commits
Show all changes
13 commits
Select commit Hold shift + click to select a range
df0a32d
add macro to get columns
patkearns10 Mar 11, 2022
1999e2b
star macro should use get_columns
patkearns10 Mar 22, 2022
53bbee9
add adapter.
patkearns10 Mar 22, 2022
9298680
swap adapter for dbt_utils
patkearns10 Mar 23, 2022
2bfc66d
update documentation
patkearns10 Mar 24, 2022
b492782
add output_lower arg
patkearns10 Mar 24, 2022
77d5a5b
update name to get_filtered_columns_in_relation from get_columns
patkearns10 Mar 24, 2022
820853f
add tests
patkearns10 Mar 24, 2022
c4104e0
forgot args
patkearns10 Mar 24, 2022
1f19ed8
too much whitespace removal
patkearns10 Mar 24, 2022
567b9f9
didnt mean to move a file that i did not create. moving things back.
patkearns10 Mar 24, 2022
a50af9d
remove lowercase logic
patkearns10 Mar 26, 2022
e0c754c
limit_zero
patkearns10 Mar 26, 2022
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
Prev Previous commit
Next Next commit
update documentation
  • Loading branch information
@patkearns10
patkearns10 committed Mar 24, 2022
commit 2bfc66d63826dcfda1713d4e4caa0db9d065fbb7
33 changes: 29 additions & 4 deletions README.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -587,11 +587,17 @@ This macro returns the unique values for a column in a given [relation](https://
```

#### get_columns ([source](macros/sql/get_columns.sql))
This macro returns an iterable Jinja list of columns for a given [relation](https://docs.getdbt.com/docs/writing-code-in-dbt/class-reference/#relation)
This macro returns an iterable Jinja list of columns for a given [relation](https://docs.getdbt.com/docs/writing-code-in-dbt/class-reference/#relation), (i.e. not from a CTE)
- optionally exclude columns
- the input values are not case-sensitive (input uppercase or lowercase and it will work!)
- declare whether you want lowercase output values with `output_lower=TRUE` (as Snowflake by default will output values in UPPERCASE)
> Note: The native [`adapter.get_columns_in_relation` macro](https://docs.getdbt.com/reference/dbt-jinja-functions/adapter#get_columns_in_relation) allows you
to pull column names in a non-filtered fashion, also bringing along with it other (potentially unwanted) information, such as dtype, char_size, numeric_precision, etc.

**Args:**
- `from` (required): a [Relation](https://docs.getdbt.com/reference/dbt-classes#relation) (a `ref` or `source`) that contains the list of columns you wish to select from
- `except` (optional, default=`[]`): The name of the columns you wish to exclude
- `except` (optional, default=`[]`): The name of the columns you wish to exclude. (case-insensitive)
- `output_lower` (optional, default=`False`): True if you want lowercase outputs. False if you want outputs to be database default (which may be upper or lower depending on database)
patkearns10 marked this conversation as resolved.
Show resolved Hide resolved

**Usage:**
```sql
Expand Down Expand Up @@ -767,9 +773,21 @@ group by 1,2,3
```

#### star ([source](macros/sql/star.sql))
This macro generates a comma-separated list of all fields that exist in the `from` relation, excluding any fields listed in the `except` argument. The construction is identical to `select * from {{ref('my_model')}}`, replacing star (`*`) with the star macro. This macro also has an optional `relation_alias` argument that will prefix all generated fields with an alias (`relation_alias`.`field_name`).
This macro generates a comma-separated list of all fields that exist in the `from` relation, excluding any fields
listed in the `except` argument. The construction is identical to `select * from {{ref('my_model')}}`, replacing star (`*`) with
the star macro.
This macro also has an optional `relation_alias` argument that will prefix all generated fields with an alias (`relation_alias`.`field_name`).
The macro also has optional `prefix` and `suffix` arguments. When one or both are provided, they will be concatenated onto each field's alias
in the output (`prefix` ~ `field_name` ~ `suffix`). NB: This prevents the output from being used in any context other than a select statement.

**Args:**
- `from` (required): a [Relation](https://docs.getdbt.com/reference/dbt-classes#relation) (a `ref` or `source`) that contains the list of columns you wish to select from
- `except` (optional, default=`[]`): The name of the columns you wish to exclude. (case-insensitive)
- `relation_alias` (optional, default=`''`): will prefix all generated fields with an alias (`relation_alias`.`field_name`).
- `prefix` (optional, default=`''`): will prefix the output `field_name` (`field_name as prefix_field_name`).
- `suffix` (optional, default=`''`): will suffix the output `field_name` (`field_name as field_name_suffix`).
patkearns10 marked this conversation as resolved.
Show resolved Hide resolved
- `output_lower` (optional, default=`False`): True if you want lowercase outputs. False if you want outputs to be database default (which may be upper or lower depending on database)
patkearns10 marked this conversation as resolved.
Show resolved Hide resolved

The macro also has optional `prefix` and `suffix` arguments. When one or both are provided, they will be concatenated onto each field's alias in the output (`prefix` ~ `field_name` ~ `suffix`). NB: This prevents the output from being used in any context other than a select statement.

**Usage:**
```sql
Expand All @@ -786,6 +804,13 @@ from {{ ref('my_model') }}

```

```sql
select
{{ dbt_utils.star(from=ref('my_model'), except=["exclude_field_1", "exclude_field_2"], prefix="max_", output_lower=True) }}
from {{ ref('my_model') }}

```

#### union_relations ([source](macros/sql/union.sql))

This macro unions together an array of [Relations](https://docs.getdbt.com/docs/writing-code-in-dbt/class-reference/#relation),
Expand Down