search

OHDSI / dbt-synthea

[Under development] A dbt ETL project to convert a Synthea synthetic data set into the OMOP CDM
https://ohdsi.github.io/dbt-synthea/
Apache License 2.0
16 stars 6 forks source link

Fix SqlFluff<>duckdb issue #34

Closed katy-sadowski closed 1 month ago

katy-sadowski commented 5 months ago

When I have duckdb in my target profile, I get this error in the pre-commit SqlFluff run. dbt-duckdb is definitely installed and I'm definitely in my virtual env. dbt runs fine for duckdb. Need to dig into this deeper to figure out what's going on.

=== [dbt templater] Sorting Nodes...                                                   
file models/intermediate/int__source_to_source_vocab_map.sql:   0% 0/3 [00:01<?, ?it/s]01:03:08  Error importing adapter: No module named 'dbt.adapters.duckdb'
Traceback (most recent call last):
  File "/Users/katysadowski/.cache/pre-commit/repohwz4l1__/py_env-python3.12/bin/sqlfluff", line 8, in <module>
    sys.exit(cli())
             ^^^^^
  File "/Users/katysadowski/.cache/pre-commit/repohwz4l1__/py_env-python3.12/lib/python3.12/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/katysadowski/.cache/pre-commit/repohwz4l1__/py_env-python3.12/lib/python3.12/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
         ^^^^^^^^^^^^^^^^
  File "/Users/katysadowski/.cache/pre-commit/repohwz4l1__/py_env-python3.12/lib/python3.12/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/katysadowski/.cache/pre-commit/repohwz4l1__/py_env-python3.12/lib/python3.12/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/katysadowski/.cache/pre-commit/repohwz4l1__/py_env-python3.12/lib/python3.12/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/katysadowski/.cache/pre-commit/repohwz4l1__/py_env-python3.12/lib/python3.12/site-packages/sqlfluff/cli/commands.py", line 1108, in fix
    _paths_fix(
  File "/Users/katysadowski/.cache/pre-commit/repohwz4l1__/py_env-python3.12/lib/python3.12/site-packages/sqlfluff/cli/commands.py", line 879, in _paths_fix
    result: LintingResult = linter.lint_paths(
                            ^^^^^^^^^^^^^^^^^^
  File "/Users/katysadowski/.cache/pre-commit/repohwz4l1__/py_env-python3.12/lib/python3.12/site-packages/sqlfluff/core/linter/linter.py", line 1124, in lint_paths
    for i, linted_file in enumerate(runner.run(expanded_paths, fix), start=1):
  File "/Users/katysadowski/.cache/pre-commit/repohwz4l1__/py_env-python3.12/lib/python3.12/site-packages/sqlfluff/core/linter/runner.py", line 144, in run
    for lint_result in self._map(
  File "/Library/Frameworks/Python.framework/Versions/3.12/lib/python3.12/multiprocessing/pool.py", line 873, in next
    raise value
dbt.exceptions.DbtProfileError: Runtime Error
  Credentials in profile "synthea_omop_etl", target "dev" invalid: Runtime Error
    Could not find adapter type duckdb!
lawrenceadams commented 1 month ago

Ahhh this one was interesting - turns out pre-commit creates a new virtual environment for everything - once duckdb is added this works fine!

In my mind this raises a question: is DuckDB or Postgres the database we're focusing on? I assume both but if/when we add support for others this may get harder to maintain

katy-sadowski commented 1 month ago

Great catch!!!! The grand vision is to support as many DBMS as OHDSI supports (https://ohdsi.github.io/Hades/supportedPlatforms.html). It's a good flag that there are things to consider there beyond just SQL syntax 😄