search

databricks / dbt-databricks

A dbt adapter for Databricks.
https://databricks.com
Apache License 2.0
226 stars 119 forks source link

[Bug] Crash when dbt runs show grants statement due to column name capitalization mismatch #782

Open lamalex opened 2 months ago

lamalex commented 2 months ago

i 100% copy pasta'd this from dbt-spark as was requests in https://github.com/dbt-labs/dbt-spark/issues/1086

cc: @amychen1776

Is this a new bug in dbt-spark?

Current Behavior

My DBT build is failing (in certain environments, have not been able to pinpoint what the difference is) due to column name capitalization mismatch from a show grants statement.

In my dbt-spark library i've added a print on the grants table before columns are accessed by name, and I am seeing lower cased column names instead of upper-case (which does not match when I run this statement in databricks sql console!)

| column     | data_type |
| ---------- | --------- |
| principal  | Text      |
| actiontype | Text      |
| objecttype | Text      |
| objectkey  | Text      |

if I change the column names in the adapter my models build successfully. This is occurring on my workstation, as well as in our Dagster instance, but is not occurring in a Databricks workflow job (which are all using dbt-core 1.8.4, and dbt-databricks 1.8.3(or 4), and dbt-spark 1.8.0).

Expected Behavior

not crash

Steps To Reproduce

run dbt build --profile databricks --select tag:elementary --log-level debug

Relevant log output

15:35:00  Using databricks connection "model.elementary.metadata"
15:35:00  On model.elementary.metadata: /* {"app": "dbt", "dbt_version": "1.8.4", "dbt_databricks_version": "1.8.4", "databricks_sql_connector_version": "3.1.2", "profile_name": "databricks", "target_name": "dev", "node_id": "model.elementary.metadata"} */

    show grants on `launi-databricks_catalog_nonprod`.`zzz_launi_alauni`.`metadata`

15:35:00  Databricks adapter: Cursor(session-id=01ef51ad-d40d-1dc7-be88-71c780641be1, command-id=Unknown) - Created cursor
15:35:01  SQL status: OK in 0.4000000059604645 seconds
15:35:01  Databricks adapter: Cursor(session-id=01ef51ad-d40d-1dc7-be88-71c780641be1, command-id=01ef51ad-f259-1668-889b-9ea8e4fa88ba) - Closing cursor
| column     | data_type |
| ---------- | --------- |
| principal  | Text      |
| actiontype | Text      |
| objecttype | Text      |
| objectkey  | Text      |

15:35:01  Databricks adapter: DatabricksDBTConnection(id=5338871184, session-id=01ef51ad-d40d-1dc7-be88-71c780641be1, name=model.elementary.metadata, idle-time=4.0531158447265625e-06s, acquire-count=0, language=sql, thread-identifier=(60914, 12901707776), compute-name=) - Released connection
15:35:01  Unhandled error while executing target/run/elementary/models/edr/system/metadata.sql
'Principal'
15:35:01  Traceback (most recent call last):
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/dbt/task/base.py", line 368, in safe_run
    result = self.compile_and_execute(manifest, ctx)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/dbt/task/base.py", line 314, in compile_and_execute
    result = self.run(ctx.node, manifest)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/dbt/task/base.py", line 415, in run
    return self.execute(compiled_node, manifest)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/dbt/task/run.py", line 298, in execute
    result = MacroGenerator(
             ^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/dbt/clients/jinja.py", line 84, in __call__
    return self.call_macro(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/dbt_common/clients/jinja.py", line 298, in call_macro
    return macro(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/jinja2/runtime.py", line 768, in __call__
    return self._invoke(arguments, autoescape)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/jinja2/runtime.py", line 782, in _invoke
    rv = self._func(*arguments)
         ^^^^^^^^^^^^^^^^^^^^^^
  File "<template>", line 75, in macro
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/jinja2/sandbox.py", line 394, in call
    return __context.call(__obj, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/jinja2/runtime.py", line 303, in call
    return __obj(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/dbt/clients/jinja.py", line 84, in __call__
    return self.call_macro(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/dbt_common/clients/jinja.py", line 298, in call_macro
    return macro(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/jinja2/runtime.py", line 768, in __call__
    return self._invoke(arguments, autoescape)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/jinja2/runtime.py", line 782, in _invoke
    rv = self._func(*arguments)
         ^^^^^^^^^^^^^^^^^^^^^^
  File "<template>", line 25, in macro
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/jinja2/sandbox.py", line 394, in call
    return __context.call(__obj, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/jinja2/runtime.py", line 303, in call
    return __obj(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/dbt/clients/jinja.py", line 84, in __call__
    return self.call_macro(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/dbt_common/clients/jinja.py", line 298, in call_macro
    return macro(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/jinja2/runtime.py", line 768, in __call__
    return self._invoke(arguments, autoescape)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/jinja2/runtime.py", line 782, in _invoke
    rv = self._func(*arguments)
         ^^^^^^^^^^^^^^^^^^^^^^
  File "<template>", line 48, in macro
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/jinja2/sandbox.py", line 394, in call
    return __context.call(__obj, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/jinja2/runtime.py", line 303, in call
    return __obj(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/dbt/adapters/databricks/utils.py", line 78, in wrapper
    return func(*new_args, **new_kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/dbt/adapters/spark/impl.py", line 493, in standardize_grants_dict
    grantee = row["Principal"]
              ~~~^^^^^^^^^^^^^
  File "/Users/alauni/Code/launi/launi-dbt/.venv/lib/python3.12/site-packages/agate/mapped_sequence.py", line 88, in __getitem__
    return self.dict()[key]
           ~~~~~~~~~~~^^^^^
KeyError: 'Principal'

### Environment

```markdown
- OS: MacOS 14.6
- Python: 3.12.3
- dbt-core: 1.8.4
- dbt-spark: 1.8.0

Additional Context

No response

benc-db commented 2 months ago

Could you expand on this:

This is occurring on my workstation, as well as in our Dagster instance, but is not occurring in a Databricks workflow job (which are all using dbt-core 1.8.4, and dbt-databricks 1.8.3(or 4), and dbt-spark 1.8.0)

Specifically, what version do you see the issue with and which version do you not see the issue with? If the workflow job is the same version but doesn't have this issue, is the compute type used different?