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

Append query comment #2199

Merged
merged 18 commits into from
Mar 24, 2020

Conversation

ilkinulas
Copy link
Contributor

@ilkinulas ilkinulas commented Mar 12, 2020

resolves #2138

Description

By default comment is inserted at the beginning of the query.

Snowflake removes leading comments from the query. https://docs.snowflake.net/manuals/release-notes/2017-04.html#queries-leading-comments-removed-during-execution

With this PR, dbt users will be able to insert the comment to the end query.

Comment location can be set in the dbt_project.yml file.

# comment will be appended 
query-comment:
    comment: 'executed by dbt'
    append: True
# default comment will be appended 
query-comment:
    append: True

Checklist

  • have signed the CLA
  • I have run this code in development and it appears to resolve the stated issue
  • This PR includes tests, or tests are not required/relevant for this PR
  • I have updated the CHANGELOG.md and added information about my change to the "dbt next" section.

@cla-bot
Copy link

cla-bot bot commented Mar 12, 2020

Thanks for your pull request, and welcome to our community! We require contributors to sign our Contributor License Agreement and we don't seem to have your signature on file. Check out this article for more information on why we have a CLA.

In order for us to review and merge your code, please submit the Individual Contributor License Agreement form attached above above. If you have questions about the CLA, or if you believe you've received this message in error, don't hesitate to ping @drewbanin.

CLA has not been signed by users: @ilkinulas

@cla-bot
Copy link

cla-bot bot commented Mar 12, 2020

Thanks for your pull request, and welcome to our community! We require contributors to sign our Contributor License Agreement and we don't seem to have your signature on file. Check out this article for more information on why we have a CLA.

In order for us to review and merge your code, please submit the Individual Contributor License Agreement form attached above above. If you have questions about the CLA, or if you believe you've received this message in error, don't hesitate to ping @drewbanin.

CLA has not been signed by users: @ilkinulas

@cla-bot
Copy link

cla-bot bot commented Mar 12, 2020

Thanks for your pull request, and welcome to our community! We require contributors to sign our Contributor License Agreement and we don't seem to have your signature on file. Check out this article for more information on why we have a CLA.

In order for us to review and merge your code, please submit the Individual Contributor License Agreement form attached above above. If you have questions about the CLA, or if you believe you've received this message in error, don't hesitate to ping @drewbanin.

CLA has not been signed by users: @ilkinulas

@cla-bot
Copy link

cla-bot bot commented Mar 12, 2020

Thanks for your pull request, and welcome to our community! We require contributors to sign our Contributor License Agreement and we don't seem to have your signature on file. Check out this article for more information on why we have a CLA.

In order for us to review and merge your code, please submit the Individual Contributor License Agreement form attached above above. If you have questions about the CLA, or if you believe you've received this message in error, don't hesitate to ping @drewbanin.

CLA has not been signed by users: @ilkinulas

@cla-bot
Copy link

cla-bot bot commented Mar 12, 2020

Thanks for your pull request, and welcome to our community! We require contributors to sign our Contributor License Agreement and we don't seem to have your signature on file. Check out this article for more information on why we have a CLA.

In order for us to review and merge your code, please submit the Individual Contributor License Agreement form attached above above. If you have questions about the CLA, or if you believe you've received this message in error, don't hesitate to ping @drewbanin.

CLA has not been signed by users: @ilkinulas

@cla-bot
Copy link

cla-bot bot commented Mar 12, 2020

Thanks for your pull request, and welcome to our community! We require contributors to sign our Contributor License Agreement and we don't seem to have your signature on file. Check out this article for more information on why we have a CLA.

In order for us to review and merge your code, please submit the Individual Contributor License Agreement form attached above above. If you have questions about the CLA, or if you believe you've received this message in error, don't hesitate to ping @drewbanin.

CLA has not been signed by users: @ilkinulas

@beckjake
Copy link
Contributor

@cla-bot check

@cla-bot cla-bot bot added the cla:yes label Mar 13, 2020
@cla-bot
Copy link

cla-bot bot commented Mar 13, 2020

The cla-bot has been summoned, and re-checked this pull request!

@ilkinulas
Copy link
Contributor Author

Hi ✋
Remaining 7 checks are stuck for 3 days. What should I need to do to get this PR reviewed?

@@ -244,7 +250,7 @@ class Project:
snapshots: Dict[str, Any]
dbt_version: List[VersionSpecifier]
packages: Dict[str, Any]
query_comment: Optional[Union[str, NoValue]]
query_comment: Dict[str, Any]
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Instead of a Dict[str, Any], let's make this a new optional JsonSchemaMixin dataclass with two fields:

@dataclass
class QueryComment(JsonsSchemaMixin):
    comment: Optional[str] = DEFAULT_QUERY_COMMENT
    append: bool = False

Then on the config/partial project objects, we'd change this to something like:

    query_comment: Optional[QueryComment] = field(default_factory=QueryComment)

We can use the object itself being None to indicate the old None behavior (don't include any comment) and the comment field being None to indicate the old NoValue behavior (default comment).

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Should we support the following config ( for backward compatibility) ?

query-comment: "executed by dbt"

The following definition will not allow string values for query-comment:

query_comment: Optional[QueryComment] = field(default_factory=QueryComment)

Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Hmm, you're right - we'll want to make it a Union[QueryComment, str] and do an isinstance check at runtime!

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Do you have any idea why jsonschema validation fails for this config:

query-comment: 
  comment: "executed by dbt"
  append: true

core/dbt/contracts/project.py

@dataclass
class Project(HyphenatedJsonSchemaMixin, Replaceable):
    ...
    query_comment: Optional[Union[QueryComment, str]] = None
@dataclass
class QueryComment(JsonSchemaMixin):
    comment: str = DEFAULT_QUERY_COMMENT
    append: bool = False

Here is the error message :

2020-03-17 21:04:42.892154 (MainThread): Running with dbt=0.17.0-a1
2020-03-17 21:04:43.124426 (MainThread): Encountered an error while reading the project:
2020-03-17 21:04:43.124548 (MainThread):   ERROR: Runtime Error
  at path []: QueryComment(comment='executed by dbt', append=True) is not of type 'object'
2020-03-17 21:04:43.124647 (MainThread): Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'invalid', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fd85acdb370>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fd85acdb940>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fd85acdb2e0>]}
2020-03-17 21:04:43.124892 (MainThread): Encountered an error:
2020-03-17 21:04:43.124948 (MainThread): Runtime Error
  Could not run dbt
2020-03-17 21:04:43.126486 (MainThread): jsonschema.exceptions.ValidationError: QueryComment(comment='executed by dbt', append=True) is not of type 'object'

Failed validating 'type' in schema[0]:
    {'additionalProperties': False,
     'description': 'QueryComment(comment: str = "\\n{%- set comment_dict '
                    '= {} -%}\\n{%- do comment_dict.update(\\n    '
                    "app='dbt',\\n    dbt_version=dbt_version,\\n    "
                    "profile_name=target.get('profile_name'),\\n    "
                    "target_name=target.get('target_name'),\\n) -%}\\n{%- "
                    'if node is not none -%}\\n  {%- do '
                    'comment_dict.update(\\n    '
                    'node_id=node.unique_id,\\n  ) -%}\\n{% else %}\\n  {# '
                    'in the node context, the connection name is the '
                    'node_id #}\\n  {%- do '
                    'comment_dict.update(connection_name=connection_name) '
                    '-%}\\n{%- endif -%}\\n{{ return(tojson(comment_dict)) '
                    '}}\\n", append: bool = False)',
     'properties': {'append': {'default': False, 'type': 'boolean'},
                    'comment': {'default': '\n'
                                           '{%- set comment_dict = {} -%}\n'
                                           '{%- do comment_dict.update(\n'
                                           "    app='dbt',\n"
                                           '    dbt_version=dbt_version,\n'
                                           '    '
                                           "profile_name=target.get('profile_name'),\n"
                                           '    '
                                           "target_name=target.get('target_name'),\n"
                                           ') -%}\n'
                                           '{%- if node is not none -%}\n'
                                           '  {%- do comment_dict.update(\n'
                                           '    node_id=node.unique_id,\n'
                                           '  ) -%}\n'
                                           '{% else %}\n'
                                           '  {# in the node context, the '
                                           'connection name is the node_id '
                                           '#}\n'
                                           '  {%- do '
                                           'comment_dict.update(connection_name=connection_name) '
                                           '-%}\n'
                                           '{%- endif -%}\n'
                                           '{{ '
                                           'return(tojson(comment_dict)) '
                                           '}}\n',
                                'type': 'string'}},
     'required': [],
     'type': 'object'}

On instance:
    QueryComment(comment='executed by dbt', append=True)

I'd prefer asking before digging into details of jsonschema .

Thank you

Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I usually see that when I put the actual object in a place where we expected the dict form.

@ilkinulas
Copy link
Contributor Author

Hi @beckjake, I have pushed your requested changes. Thanks a lot for your help.

core/dbt/adapters/base/query_headers.py Outdated Show resolved Hide resolved
core/dbt/adapters/base/query_headers.py Outdated Show resolved Hide resolved
core/dbt/adapters/base/query_headers.py Outdated Show resolved Hide resolved
- isinstance check is needed to pass mypy checks
Copy link
Contributor

@beckjake beckjake left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Three last tiny things and I'll kick off tests, and then this should be good to merge! Thanks for your contribution @ilkinulas, this is great!

CHANGELOG.md Outdated
@@ -3,6 +3,9 @@
### Fixes
- When a jinja value is undefined, give a helpful error instead of failing with cryptic "cannot pickle ParserMacroCapture" errors ([#2110](https://github.com/fishtown-analytics/dbt/issues/2110), [#2184](https://github.com/fishtown-analytics/dbt/pull/2184))

### Features
- Support for appending query comments to SQL queries. ([#2199](https://github.com/fishtown-analytics/dbt/pull/2199))
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Oops, sorry, one last thing! Can you add the issue to this changelog entry as well? [#2138](https://github.com/fishtown-analytics/dbt/issues/2138) should be it

cfg_query_comment: Union[QueryComment, str]
) -> QueryComment:
if isinstance(cfg_query_comment, str):
if cfg_query_comment in ('None', ''):
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

if not cfg_query_comment is probably sufficient here, as well, right?

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

if not cfg_query_comment check does not catch below config.

query-comment: None

because cfg_query_comment's type is str and its value is 'None'

Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I don't think we should catch that case! In yaml, the appropriate thing to do to get None out is either

query-comment:

or

query-comment: null

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

The docs says that

You can disable query comments by setting the query-comment config to None

What is the preferred way of disabling query comments?

Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

The docs are wrong! I'll open a PR there to fix that, thank you for pointing that out.

The two methods I listed above should both work.

Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Now that I think about it more, this problem is why I had the NoValue construct, to differentiate "not set, so leave it at the default" and "don't supply a query comment".

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

So you are suggesting something like this:

query_comment: Optional[Union[NoValue, Union[QueryComment, str]]

Is my understanding correct?

Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Yeah, though unions get collapsed by typing, so that's equivalent to Optional[Union[NoValue, QueryComment, str]]

CHANGELOG.md Outdated
@@ -3,6 +3,9 @@
### Fixes
- When a jinja value is undefined, give a helpful error instead of failing with cryptic "cannot pickle ParserMacroCapture" errors ([#2110](https://github.com/fishtown-analytics/dbt/issues/2110), [#2184](https://github.com/fishtown-analytics/dbt/pull/2184))

### Features
- Support for appending query comments to SQL queries. ([#2199](https://github.com/fishtown-analytics/dbt/pull/2199))

Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Please add a contributors section, and add yourself!

Suggested change
Contributors:
- [@ilkinulas](https://github.com/ilkinulas) [#2199](https://github.com/fishtown-analytics/dbt/pull/2199)

- replaced pr link with issue link
- added ilkinulas to contributers list
- hologram version updated to 0.0.6 to be able to use QueryComment, NoValue and str within the same Union type.
Copy link
Contributor

@beckjake beckjake left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Thanks for your work on this marathon of a PR @ilkinulas! I think this is working as we want now, I've kicked off the rest of the integration tests and once those pass I think this is ready to merge for Octavius Catto - which should become 0.17.0.

@drewbanin do you have anything to add?

@ilkinulas
Copy link
Contributor Author

Thank you @beckjake for your support in this PR.
Is it possible to include this in 0.16.0, so we can use it from the upstream ?

@beckjake
Copy link
Contributor

beckjake commented Mar 23, 2020

I don't think so, unfortunately. We just released 0.16.0 an hour or so ago and we were well into the RC period/feature freeze last week.

Edit: If/when we do a 0.16.1 bugfix release though, I will try to backport this in.

@drewbanin
Copy link
Contributor

Nice work on this @ilkinulas!!

@beckjake let's definitely get this merged as-is, and if you're easily able to backport it to 0.16.1 as well, i think some folks out there would really appreciate it :)

🚢

@beckjake beckjake merged commit efcf78b into dbt-labs:dev/octavius-catto Mar 24, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

Successfully merging this pull request may close these issues.

Append query comments instead of prepending
3 participants