search

dbt-labs / dbt-codegen

Macros that generate dbt code
https://hub.getdbt.com/dbt-labs/codegen/latest/
Apache License 2.0
459 stars 99 forks source link

Generate source for MS SQL Server: Field "type" of type Optional[RelationType] in BaseRelation has invalid value Undefined #135

Closed JustGitting closed 6 months ago

JustGitting commented 1 year ago

Describe the bug

I'm trying to create the source schema for tables in an Microsoft SQL Server database using the codegen macro. The initial problem was accessing the database because the database name contained a hyphen, "my-database1". But I fixed this by specifying the database_name with square brackets.

Steps to reproduce

Execute the following command.

$ dbt --debug run-operation generate_source --args '{"schema_name": "dbo", "database_name": "[my-database1]"}'

Expected results

The schema template for all tables in MS SQL Server database displayed in the terminal.

Actual results

Instead of the schema being generated, the following error is produced:

Field "type" of type Optional[RelationType] in BaseRelation has invalid value Undefined

Log output

Output of the dbt command.

04:47:34.612383 [info ] [MainThread]: Running with dbt=1.4.6
04:47:34.616818 [debug] [MainThread]: running dbt with arguments {'debug': True, 'write_json': True, 'use_colors': True, 'printer_width': 80, 'version_check': True, 'partial_parse': True, 'static_parser': True, 'profiles_dir': '/home/user1/.dbt', 'send_anonymous_usage_stats': False, 'quiet': False, 'no_print': False, 'cache_selected_only': False, 'macro': 'generate_source', 'args': '{"schema_name": "dbo", "database_name": "[my-database1]"}', 'which': 'run-operation', 'rpc_method': 'run-operation', 'indirect_selection': 'eager'}
04:47:34.617507 [debug] [MainThread]: Tracking: do not track
04:47:34.717895 [debug] [MainThread]: checksum: 0325e47f1211ebbdb24627f81d8289d705fdb573380364d07ab35982cb3d57cd, vars: {}, profile: None, target: None, version: 1.4.6
04:47:34.827393 [debug] [MainThread]: Partial parsing enabled: 0 files deleted, 0 files added, 0 files changed.
04:47:34.827845 [debug] [MainThread]: Partial parsing enabled, no changes found, skipping parsing
04:47:34.856000 [debug] [MainThread]: Acquiring new sqlserver connection 'macro_generate_source'
04:47:34.856536 [debug] [MainThread]: On macro_generate_source: COMMIT
04:47:34.905672 [debug] [MainThread]: SQLServer adapter: Using sqlserver connection "macro_generate_source".
04:47:34.906118 [debug] [MainThread]: SQLServer adapter: On macro_generate_source:

        SELECT DISTINCT
            table_schema AS "table_schema",
            table_name AS "table_name"
        FROM [my-database1].information_schema.tables
        WHERE table_schema LIKE 'dbo'
        AND table_name LIKE '%'
        AND table_name NOT LIKE ''

04:47:34.906550 [debug] [MainThread]: Opening a new connection, currently in state init
04:47:34.906982 [debug] [MainThread]: SQLServer adapter: Using connection string: DRIVER={ODBC Driver 18 for SQL Server};SERVER=msdbserver1,1433;Database=my-database1;UID={dsadbt};PWD=***;encrypt=Yes;TrustServerCertificate=Yes;Application Name=dbt-sqlserver/1.4.3
04:47:34.957091 [debug] [MainThread]: SQLServer adapter: Connected to db: my-database1
04:47:34.977670 [debug] [MainThread]: SQLServer adapter: SQL status: OK in 0.07 seconds
04:47:34.987516 [debug] [MainThread]: SQLServer adapter: Error running SQL: macro generate_source
04:47:34.987938 [debug] [MainThread]: SQLServer adapter: Rolling back transaction.
04:47:34.988394 [debug] [MainThread]: On macro_generate_source: ROLLBACK
04:47:34.988821 [debug] [MainThread]: On macro_generate_source: Close
04:47:34.989976 [error] [MainThread]: Encountered an error while running operation: Runtime Error
  Field "type" of type Optional[RelationType] in BaseRelation has invalid value Undefined
04:47:34.992859 [debug] [MainThread]: Traceback (most recent call last):
  File "<string>", line 16, in from_dict
  File "/home/user1/.local/lib/python3.9/site-packages/dbt/dataclass_schema.py", line 130, in _deserialize
    return cls(value)
  File "/usr/lib/python3.9/enum.py", line 360, in __call__
    return cls.__new__(cls, value)
  File "/usr/lib/python3.9/enum.py", line 677, in __new__
    raise ve_exc
ValueError: Undefined is not a valid RelationType

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/user1/.local/lib/python3.9/site-packages/dbt/adapters/sqlserver/sql_server_connection_manager.py", line 277, in exception_handler
    yield
  File "/home/user1/.local/lib/python3.9/site-packages/dbt/adapters/base/impl.py", line 999, in execute_macro
    result = macro_function(**kwargs)
  File "/home/user1/.local/lib/python3.9/site-packages/dbt/clients/jinja.py", line 329, in __call__
    return self.call_macro(*args, **kwargs)
  File "/home/user1/.local/lib/python3.9/site-packages/dbt/clients/jinja.py", line 256, in call_macro
    return macro(*args, **kwargs)
  File "/usr/local/lib/python3.9/dist-packages/jinja2/runtime.py", line 763, in __call__
    return self._invoke(arguments, autoescape)
  File "/usr/local/lib/python3.9/dist-packages/jinja2/runtime.py", line 777, in _invoke
    rv = self._func(*arguments)
  File "<template>", line 137, in macro
  File "/usr/local/lib/python3.9/dist-packages/jinja2/sandbox.py", line 393, in call
    return __context.call(__obj, *args, **kwargs)
  File "/usr/local/lib/python3.9/dist-packages/jinja2/runtime.py", line 298, in call
    return __obj(*args, **kwargs)
  File "/home/user1/.local/lib/python3.9/site-packages/dbt/clients/jinja.py", line 329, in __call__
    return self.call_macro(*args, **kwargs)
  File "/home/user1/.local/lib/python3.9/site-packages/dbt/clients/jinja.py", line 256, in call_macro
    return macro(*args, **kwargs)
  File "/usr/local/lib/python3.9/dist-packages/jinja2/runtime.py", line 763, in __call__
    return self._invoke(arguments, autoescape)
  File "/usr/local/lib/python3.9/dist-packages/jinja2/runtime.py", line 777, in _invoke
    rv = self._func(*arguments)
  File "<template>", line 42, in macro
  File "/usr/local/lib/python3.9/dist-packages/jinja2/sandbox.py", line 393, in call
    return __context.call(__obj, *args, **kwargs)
  File "/usr/local/lib/python3.9/dist-packages/jinja2/runtime.py", line 298, in call
    return __obj(*args, **kwargs)
  File "/home/user1/.local/lib/python3.9/site-packages/dbt/clients/jinja.py", line 329, in __call__
    return self.call_macro(*args, **kwargs)
  File "/home/user1/.local/lib/python3.9/site-packages/dbt/clients/jinja.py", line 256, in call_macro
    return macro(*args, **kwargs)
  File "/usr/local/lib/python3.9/dist-packages/jinja2/runtime.py", line 763, in __call__
    return self._invoke(arguments, autoescape)
  File "/usr/local/lib/python3.9/dist-packages/jinja2/runtime.py", line 777, in _invoke
    rv = self._func(*arguments)
  File "<template>", line 28, in macro
  File "/usr/local/lib/python3.9/dist-packages/jinja2/sandbox.py", line 393, in call
    return __context.call(__obj, *args, **kwargs)
  File "/usr/local/lib/python3.9/dist-packages/jinja2/runtime.py", line 298, in call
    return __obj(*args, **kwargs)
  File "/home/user1/.local/lib/python3.9/site-packages/dbt/clients/jinja.py", line 329, in __call__
    return self.call_macro(*args, **kwargs)
  File "/home/user1/.local/lib/python3.9/site-packages/dbt/clients/jinja.py", line 256, in call_macro
    return macro(*args, **kwargs)
  File "/usr/local/lib/python3.9/dist-packages/jinja2/runtime.py", line 763, in __call__
    return self._invoke(arguments, autoescape)
  File "/usr/local/lib/python3.9/dist-packages/jinja2/runtime.py", line 777, in _invoke
    rv = self._func(*arguments)
  File "<template>", line 49, in macro
  File "/usr/local/lib/python3.9/dist-packages/jinja2/sandbox.py", line 393, in call
    return __context.call(__obj, *args, **kwargs)
  File "/usr/local/lib/python3.9/dist-packages/jinja2/runtime.py", line 298, in call
    return __obj(*args, **kwargs)
  File "/home/user1/.local/lib/python3.9/site-packages/dbt/context/providers.py", line 96, in create
    return self._relation_type.create(*args, **kwargs)
  File "/home/user1/.local/lib/python3.9/site-packages/dbt/adapters/base/relation.py", line 287, in create
    return cls.from_dict(kwargs)
  File "<string>", line 21, in from_dict
mashumaro.exceptions.InvalidFieldValue: Field "type" of type Optional[RelationType] in BaseRelation has invalid value Undefined

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/user1/.local/lib/python3.9/site-packages/dbt/task/run_operation.py", line 57, in run
    self._run_unsafe()
  File "/home/user1/.local/lib/python3.9/site-packages/dbt/task/run_operation.py", line 47, in _run_unsafe
    res = adapter.execute_macro(
  File "/home/user1/.local/lib/python3.9/site-packages/dbt/adapters/base/impl.py", line 999, in execute_macro
    result = macro_function(**kwargs)
  File "/usr/lib/python3.9/contextlib.py", line 135, in __exit__
    self.gen.throw(type, value, traceback)
  File "/home/user1/.local/lib/python3.9/site-packages/dbt/adapters/sqlserver/sql_server_connection_manager.py", line 300, in exception_handler
    raise dbt.exceptions.DbtRuntimeError(e)
dbt.exceptions.DbtRuntimeError: Runtime Error
  Field "type" of type Optional[RelationType] in BaseRelation has invalid value Undefined

04:47:34.993631 [debug] [MainThread]: Flushing usage events
04:47:34.994114 [debug] [MainThread]: Connection 'macro_generate_source' was properly closed.

System information

The contents of your packages.yml file:

packages:
  - package: dbt-labs/codegen
    version: 0.10.0
  - package: dbt-msft/tsql_utils
    version: 0.9.0
  - package: dbt-labs/dbt_utils
    version: 1.1.1
  - package: calogica/dbt_date
    version: 0.7.2
  - package: calogica/dbt_expectations
    version: 0.8.5
  - package: dbt-labs/audit_helper
    version: 0.9.0

Which database are you using dbt with?

The output of dbt --version:

The sqlserver plugin only supports DBT 1.4.6, which is why I'm running an older version of DBT.

dbt --version
Core:
  - installed: 1.4.6
  - latest:    1.5.4 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - sqlserver: 1.4.3 - Up to date!

The operating system you're using:

GNU/Linux Debian

The output of python3 --version:

Python 3.9.2

Additional context

The following command works if specifying tables:

$ dbt --debug run-operation generate_source --args '{"schema_name": "dbo",  "database_name": "[my-database1]", "table_names":["Lookup_Country", "Lookup_Group"]}'

Output:

06:19:59.704900 [info ] [MainThread]: Running with dbt=1.4.6
06:19:59.707379 [debug] [MainThread]: running dbt with arguments {'debug': True, 'write_json': True, 'use_colors': True, 'printer_width': 80, 'version_check': True, 'partial_parse': True, 'static_parser': True, 'profiles_dir': '/home/user1/.dbt', 'send_anonymous_usage_stats': False, 'quiet': False, 'no_print': False, 'cache_selected_only': False, 'macro': 'generate_source', 'args': '{"schema_name": "dbo",  "database_name": "[my-database1]", "table_names":["Lookup_Country", "Lookup_Group"]}', 'which': 'run-operation', 'rpc_method': 'run-operation', 'indirect_selection': 'eager'}
06:19:59.707795 [debug] [MainThread]: Tracking: do not track
06:19:59.804101 [debug] [MainThread]: checksum: 0325e47f1211ebbdb24627f81d8289d705fdb573380364d07ab35982cb3d57cd, vars: {}, profile: None, target: None, version: 1.4.6
06:19:59.911855 [debug] [MainThread]: Partial parsing enabled: 0 files deleted, 0 files added, 0 files changed.
06:19:59.912292 [debug] [MainThread]: Partial parsing enabled, no changes found, skipping parsing
06:19:59.939950 [debug] [MainThread]: Acquiring new sqlserver connection 'macro_generate_source'
06:19:59.940453 [debug] [MainThread]: On macro_generate_source: COMMIT
06:19:59.964770 [info ] [MainThread]: version: 2

sources:
  - name: dbo
    database: [my-database1]
    tables:
      - name: lookup_country
      - name: lookup_group
06:19:59.965650 [debug] [MainThread]: Flushing usage events
06:19:59.966101 [debug] [MainThread]: Connection 'macro_generate_source' was properly closed.

Are you interested in contributing the fix?

I've not used python for a while, but happy to debug.

github-actions[bot] commented 7 months ago

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

github-actions[bot] commented 6 months ago

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.