add macro to get columns #516

patkearns10 · 2022-03-11T02:46:29Z

This is a:

bug fix PR with no breaking changes — please ensure the base branch is main
new functionality — please ensure the base branch is the latest dev/ branch
a breaking change — please ensure the base branch is the latest dev/ branch

Description & motivation

Similar to dbt_utils.get_column_values, except this pulls column names, rather than the values within a column. Like dbt_utils.star, but as a list, to be iterated over.

This is basically dbt_utils.star, but removing some of the last steps.

Unsure what the purpose of the top couple lines of star macro are:

{% macro star(from, relation_alias=False, except=[], prefix='', suffix='') -%}
    {{ return(adapter.dispatch('star', 'dbt_utils')(from, relation_alias, except, prefix, suffix)) }}
{% endmacro %}

{% macro default__star(from, relation_alias=False, except=[], prefix='', suffix='') -%}
    {%- do dbt_utils._is_relation(from, 'star') -%}
    {%- do dbt_utils._is_ephemeral(from, 'star') -%}

So I left those out. But I assume maybe that is important?

Checklist

joellabes

Hey @patkearns10, thanks for opening this!

Unsure what the purpose of the top couple lines of star macro are:

{% macro star(from, relation_alias=False, except=[], prefix='', suffix='') -%}
    {{ return(adapter.dispatch('star', 'dbt_utils')(from, relation_alias, except, prefix, suffix)) }}
{% endmacro %}

{% macro default__star(from, relation_alias=False, except=[], prefix='', suffix='') -%}
    {%- do dbt_utils._is_relation(from, 'star') -%}
    {%- do dbt_utils._is_ephemeral(from, 'star') -%}

The first chunk is to enable multiple dispatch: https://docs.getdbt.com/reference/dbt-jinja-functions/dispatch. In short, they allow other adapters to provide an override version of this macro, if they need to.

The is_relation and is_ephemeral blocks then check that the thing you passed in is a real ref/source object, and that it's not ephemeral (because CTEs can't be used with this, because they don't go into the info schema).

So you do need all parts of that 😬

I'd be very interested in whether the star macro could call this new macro directly, instead of having two copies of the exclude code etc. I haven’t looked at whether that's possible or not, but it feels reasonable - would love if you had a go as an extra for experts!

README.md

patkearns10 · 2022-03-22T23:38:52Z

Okay I moved some things around!
the star macro now references the get_columns macro.

tested it works locally, by recreating the macros and removing the dispatch logic. How do I test the dispatch logic?

get_columns macro:

call get_columns:

call get_columns with except:

replace within local star macro:

call local star macro:

call local star macro with except:

macros/sql/get_columns.sql

joellabes

This is looking great :chefs_kiss: Just a couple of naming/doc tweaks for clarity. As for how to test the dispatched stuff, you'd want to add a dbt Singular test which validates the behaviour you expect.

Something along similar lines to how codegen is tested: https://github.com/dbt-labs/dbt-codegen/blob/main/integration_tests/tests/test_generate_source.sql - I don't know if there's a way to compare two jinja lists' contents, or whether you'd have to turn it into a comma-separated string and compare that to an expected value 🤷

macros/sql/star.sql

macros/sql/get_columns.sql

README.md

macros/sql/get_columns.sql

Co-authored-by: Joel Labes <[email protected]>

----------- Actual: ----------- --->"field_3"as "test_field_3"<--- ----------- Expected: ----------- --->"field_3" as "test_field_3"<---

patkearns10 · 2022-03-24T02:46:22Z

Added tests, documentation, and a new (opt-in) argument for lowercase output:

star UPPER:

star lower:

get_filtered_columns UPPER:

get_filtered_columns lower:

integration tests:

joellabes

Big fan overall! My one complaint is the customisable lowercasing - since you can just pass it into the lower filter, I don't think adding two extra params adds enough value. I talk about that more on one of the specific comments - lmk what you think

README.md

integration_tests/macros/assert_equal_values.sql

integration_tests/tests/sql/test_get_filtered_columns_in_relation.sql

integration_tests/tests/sql/test_star_output_lower.sql

joellabes · 2022-03-25T03:13:44Z

integration_tests/macros/assert_equal_values.sql

+
+{% else %}
+
+    select 'ok' limit 0


This should be {{ limit_zero() }} - not all databases support limit clauses (notably T-SQL)

Done!
@joellabes -- this is no longer required, as I switched to using the seed/model method. Should I remove this file then?

I think that makes sense - the Core team are working on a wider testing suite that I hope will also cover packages - if that wasn't in the pipeline then I'd suggest we kept this around because "we’d need it one day" but I think we'll have a more long term solution soon.

patkearns10 · 2022-03-26T01:19:19Z

Removed lowercase and swapped to different testing method.

Curious some other tests were failing 🤔 but I didn't touch those and don't really want to dig into that.

joellabes · 2022-03-28T03:39:49Z

OK I looked into this, and you're off the hook for the tests - they're checking target.name for snowflake instead of target.type so aren't lowercasing the properties 😬

https://github.com/dbt-labs/dbt-utils/blob/main/integration_tests/models/sql/test_unpivot.sql#L18-L21

And the test_get_column_values_use_default one I assume came from running dbt build or something - it has a post-hook which drops the seed after it's created which is a bit finnicky unless it's done as seed/run/test.

These won't break on the CI jobs, (idk why that isn't running 🤔) so I think we're good to merge! Thanks for getting this done 🥳

joellabes

😍 😍

* Update README.md * Mutually excl range examples in disclosure triangle * Fix union_relations error when no include/exclude provided * Fix union_relations error when no include/exclude provided (#509) * Update CHANGELOG.md * Add to_condition to relationships where * very minor nit - update "an new" to "a new" (#519) * add quoting to split_part (#528) * add quoting to split_part * update docs for split_part * typo * corrected readme syntax * revert and update to just documentation * add new line * Update README.md * Update README.md * Update README.md Co-authored-by: Joel Labes <[email protected]> * add macro to get columns (#516) * add macro to get columns * star macro should use get_columns * add adapter. * swap adapter for dbt_utils Co-authored-by: Joel Labes <[email protected]> * update documentation * add output_lower arg * update name to get_filtered_columns_in_relation from get_columns * add tests * forgot args * too much whitespace removal ----------- Actual: ----------- --->"field_3"as "test_field_3"<--- ----------- Expected: ----------- --->"field_3" as "test_field_3"<--- * didnt mean to move a file that i did not create. moving things back. * remove lowercase logic * limit_zero Co-authored-by: Joel Labes <[email protected]> * Add listagg macro and integration test * remove type in listagg macro * updated integration test * Add redshift to listagg macro * remove redshift listagg * explicitly named group by column * updated default values * Updated example to use correct double vs. single quotes * whitespace control * Added redshift specific macro * Remove documentation * Update integration test so less likely to accidentally work Co-authored-by: Joel Labes <[email protected]> * default everything but measure to none * added limit functionality for other dbs * syntax bug for postgres * update redshift macro * fixed block def control * Fixed bug in redshift * Bug fix redshift * remove unused group_by arg * Added additional test without order by col * updated to regex replace * typo * added more integration_tests * attempt to make redshift less complicated * typo * update redshift * replace to substr * More explicit versions with added complexity * handle special characters Co-authored-by: Joel Labes <[email protected]> Co-authored-by: Jamie Rosenberg <[email protected]> Co-authored-by: Pat Kearns <[email protected]>

* Fix/timestamp withought timezone (#458) * timestamp and changelog updates * changelog fix * Add context for why change to no timezone Co-authored-by: Joel Labes <[email protected]> * also ignore dbt_packages (#463) * also ignore dbt_packages * Update CHANGELOG.md * Update CHANGELOG.md Co-authored-by: Joel Labes <[email protected]> * date_spine: transform comment to jinja (#462) * Have union_relations raise exception when include parameter results in no columns (#473) * Raise exception if no columns in column_superset * Add relation names to compiler error message * Add `union_relations` fix to changelog * Added case for handling postgres foreign tables... (#476) * Add link for fewer_rows_than schema test in docs (#465) * Added case for handling postgres foreign tables (tables which are external to current database and are imported into current database from remote data stores by using Foreign Data Wrappers functionallity). * Reworked getting of postges table_type. * Added needed changes to CHANGELOG. Co-authored-by: José Coto <[email protected]> Co-authored-by: Taras Stetsiak <[email protected]> * Enhance usability of star macro by only generating column aliases when prefix and/or suffix is specified (#468) * The star macro should only produce column aliases when there is either a prefix or suffix specified. * Enhanced the readme for the star macro. * Add new integration test Co-authored-by: Nick Perrott <[email protected]> Co-authored-by: Josh Elston-Green Co-authored-by: Joel Labes <[email protected]> * fix: extra brace typo in insert_by_period_materialization (#480) * Support quoted column names in sequential_values test (#479) * Add any value (#501) * Add link for fewer_rows_than schema test in docs (#465) * Update get_query_results_as_dict example to demonstrate accessing columnar results as dictionary values (#474) * Update get_qu ery_results_as_dict example to demonstrate accessing columnar results as dictionary values * Use slugify in example * Fix slugify example with dbt_utils. package prefix Co-authored-by: Elize Papineau <[email protected]> * Add note about not_null_where deprecation to Readme (#477) * Add note about not_null_where deprecation to Readme * Add docs to unique_where test * Update pull_request_template.md to reference `main` vs `master` (#496) * Correct coalesce -> concatenation typo (#495) * add any_value cross-db macro * Missing colon in test * Update CHANGELOG.md Co-authored-by: José Coto <[email protected]> Co-authored-by: Elize Papineau <[email protected]> Co-authored-by: Elize Papineau <[email protected]> Co-authored-by: Joe Ste.Marie <[email protected]> Co-authored-by: Niall Woodward <[email protected]> * Fix changelog * Second take at fixing pivot to allow single quotes (#503) * fix pivot : in pivoted column value, single quote must be escaped (on postgresql) else ex. syntax error near : when color = 'blue's' * patched expected * single quote escape : added dispatched version of the macro to support bigquery & snowflake * second backslash to escape in Jinja, change case of test file columns Let's see if other databases allow this * explicitly list columns to compare * different tests for snowflake and others * specific comparison seed * Don't quote identifiers for apostrophe, to avoid BQ and SF problems * Whitespace management for macros * Update CHANGELOG.md Co-authored-by: Marc Dutoo <[email protected]> * Add bool or cross db (#504) * Create bool_or cross-db func * Forgot a comma * Update CHANGELOG.md * Code review tweaks * Fix union_relations error when no include/exclude provided (#509) * Update CHANGELOG.md * Add _is_ephemeral test to get_column_values (#518) * Add _is_ephemeral test Co-authored-by: Elize Papineau <[email protected]> * Add deduplication macro (#512) * Update README.md * Mutually excl range examples in disclosure triangle * Fix union_relations error when no include/exclude provided * Fix union_relations error when no include/exclude provided (#509) * Update CHANGELOG.md * Add dedupe macro * Add test for dedupe macro * Add documentation to README * Add entry to CHANGELOG * Implement review * Typed materialized views as views (#525) * Typed materialized views as views * Update get_relations_by_pattern.sql * Moving fix from get_tables_by_pattern_sql reverting changes to this file to add a fix to the macro get_tables_by_pattern_sql * removing quoting from table_type removing quoting from table_type as this was causing an error when calling this macro within get_tables_by_pattern_sql * calling get_table_types_sql for materialized views calling get_table_types_sql macro to handle materialized views in sources. * Add `alias` argument to `deduplicate` macro (#526) * Add `alias` argument to `deduplicate * Test `alias` argument * Rename `alias` to `relation_alias` * Fix/use generic test naming style instead of schema test (#521) * Updated Rreferences to 'schema test' in README along with small improvements to test descriptions. Updates were also carried out in folder structure and integration README * Updated references to 'schema test' in Changelog * updated changelog with changes to documentation and fproject file structure * Apply suggestions from code review Update macro descriptions to be "asserts that" * Update CHANGELOG.md * Update README.md Co-authored-by: Joel Labes <[email protected]> * Remove extraneous whitespace (#529) * rm whitespace from date_trunc * datediff * rm uncessary whitespace control * change log * fix CHANGELOG * address comments * Feature/add listagg macro (#530) * Update README.md * Mutually excl range examples in disclosure triangle * Fix union_relations error when no include/exclude provided * Fix union_relations error when no include/exclude provided (#509) * Update CHANGELOG.md * Add to_condition to relationships where * very minor nit - update "an new" to "a new" (#519) * add quoting to split_part (#528) * add quoting to split_part * update docs for split_part * typo * corrected readme syntax * revert and update to just documentation * add new line * Update README.md * Update README.md * Update README.md Co-authored-by: Joel Labes <[email protected]> * add macro to get columns (#516) * add macro to get columns * star macro should use get_columns * add adapter. * swap adapter for dbt_utils Co-authored-by: Joel Labes <[email protected]> * update documentation * add output_lower arg * update name to get_filtered_columns_in_relation from get_columns * add tests * forgot args * too much whitespace removal ----------- Actual: ----------- --->"field_3"as "test_field_3"<--- ----------- Expected: ----------- --->"field_3" as "test_field_3"<--- * didnt mean to move a file that i did not create. moving things back. * remove lowercase logic * limit_zero Co-authored-by: Joel Labes <[email protected]> * Add listagg macro and integration test * remove type in listagg macro * updated integration test * Add redshift to listagg macro * remove redshift listagg * explicitly named group by column * updated default values * Updated example to use correct double vs. single quotes * whitespace control * Added redshift specific macro * Remove documentation * Update integration test so less likely to accidentally work Co-authored-by: Joel Labes <[email protected]> * default everything but measure to none * added limit functionality for other dbs * syntax bug for postgres * update redshift macro * fixed block def control * Fixed bug in redshift * Bug fix redshift * remove unused group_by arg * Added additional test without order by col * updated to regex replace * typo * added more integration_tests * attempt to make redshift less complicated * typo * update redshift * replace to substr * More explicit versions with added complexity * handle special characters Co-authored-by: Joel Labes <[email protected]> Co-authored-by: Jamie Rosenberg <[email protected]> Co-authored-by: Pat Kearns <[email protected]> * patch default behaviour in get_column_values (#533) * Update changelog, add missing quotes around get_table_types_sql * rm whitespace Co-authored-by: Joe Markiewicz <[email protected]> Co-authored-by: Anders <[email protected]> Co-authored-by: Mikaël Simarik <[email protected]> Co-authored-by: Graham Wetzler <[email protected]> Co-authored-by: Taras <[email protected]> Co-authored-by: José Coto <[email protected]> Co-authored-by: Taras Stetsiak <[email protected]> Co-authored-by: nickperrott <[email protected]> Co-authored-by: Nick Perrott <[email protected]> Co-authored-by: Ted Conbeer <[email protected]> Co-authored-by: Armand Duijn <[email protected]> Co-authored-by: Elize Papineau <[email protected]> Co-authored-by: Elize Papineau <[email protected]> Co-authored-by: Joe Ste.Marie <[email protected]> Co-authored-by: Niall Woodward <[email protected]> Co-authored-by: Marc Dutoo <[email protected]> Co-authored-by: Judah Rand <[email protected]> Co-authored-by: Luis Leon <[email protected]> Co-authored-by: Brid Moynihan <[email protected]> Co-authored-by: SunriseLong <[email protected]> Co-authored-by: Grace Goheen <[email protected]> Co-authored-by: Jamie Rosenberg <[email protected]> Co-authored-by: Pat Kearns <[email protected]> Co-authored-by: James McNeill <[email protected]>

add macro to get columns

df0a32d

patkearns10 requested a review from joellabes March 11, 2022 02:47

joellabes requested changes Mar 14, 2022

View reviewed changes

README.md Outdated Show resolved Hide resolved

joellabes added the pending label Mar 22, 2022

star macro should use get_columns

1999e2b

patkearns10 requested a review from joellabes March 22, 2022 23:38

add adapter.

53bbee9

patkearns10 commented Mar 22, 2022

View reviewed changes

macros/sql/get_columns.sql Outdated Show resolved Hide resolved

joellabes requested changes Mar 23, 2022

View reviewed changes

macros/sql/star.sql Outdated Show resolved Hide resolved

macros/sql/get_columns.sql Outdated Show resolved Hide resolved

README.md Outdated Show resolved Hide resolved

macros/sql/get_columns.sql Outdated Show resolved Hide resolved

patkearns10 and others added 7 commits March 24, 2022 10:01

swap adapter for dbt_utils

9298680

Co-authored-by: Joel Labes <[email protected]>

update documentation

2bfc66d

add output_lower arg

b492782

update name to get_filtered_columns_in_relation from get_columns

77d5a5b

add tests

820853f

forgot args

c4104e0

too much whitespace removal

1f19ed8

----------- Actual: ----------- --->"field_3"as "test_field_3"<--- ----------- Expected: ----------- --->"field_3" as "test_field_3"<---

patkearns10 requested a review from joellabes March 24, 2022 02:46

didnt mean to move a file that i did not create. moving things back.

567b9f9

joellabes requested changes Mar 25, 2022

View reviewed changes

joellabes reviewed Mar 25, 2022

View reviewed changes

patkearns10 added 2 commits March 26, 2022 12:08

remove lowercase logic

a50af9d

limit_zero

e0c754c

patkearns10 requested a review from joellabes March 26, 2022 01:19

joellabes approved these changes Mar 28, 2022

View reviewed changes

joellabes merged commit d279542 into main Mar 28, 2022

joellabes removed the pending label Mar 28, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

add macro to get columns #516

add macro to get columns #516

patkearns10 commented Mar 11, 2022 •

edited

Loading

joellabes left a comment

patkearns10 commented Mar 22, 2022

joellabes left a comment

patkearns10 commented Mar 24, 2022

joellabes left a comment

joellabes Mar 25, 2022

patkearns10 Mar 26, 2022

joellabes Mar 28, 2022

patkearns10 commented Mar 26, 2022

joellabes commented Mar 28, 2022

joellabes left a comment

add macro to get columns #516

add macro to get columns #516

Conversation

patkearns10 commented Mar 11, 2022 • edited Loading

Description & motivation

Checklist

joellabes left a comment

Choose a reason for hiding this comment

patkearns10 commented Mar 22, 2022

joellabes left a comment

Choose a reason for hiding this comment

patkearns10 commented Mar 24, 2022

Added tests, documentation, and a new (opt-in) argument for lowercase output:

star UPPER:

star lower:

get_filtered_columns UPPER:

get_filtered_columns lower:

integration tests:

joellabes left a comment

Choose a reason for hiding this comment

joellabes Mar 25, 2022

Choose a reason for hiding this comment

patkearns10 Mar 26, 2022

Choose a reason for hiding this comment

joellabes Mar 28, 2022

Choose a reason for hiding this comment

patkearns10 commented Mar 26, 2022

joellabes commented Mar 28, 2022

joellabes left a comment

Choose a reason for hiding this comment

patkearns10 commented Mar 11, 2022 •

edited

Loading