sqlfluff: dbt.exceptions.InvalidConnectionException: Runtime Error on 0.9.0

I’m trying to upgrade my project to dbt 1.0.0 and SQLFluff 0.9.0. I can dbt run just fine, but when I run SQLFluff, I get a connection error.

Here’s the full stacktrace of attempting to lint my project using SQLFluff:

root@541f43ad6080:/dbt-runner/dbt# sqlfluff lint models
=== [dbt templater] Sorting Nodes...
=== [dbt templater] Compiling dbt project...
=== [dbt templater] Project Compiled.
file dim_dates.sql:  11%|██████████████████████▏                                                                                                                                                                                 | 1/9 [00:00<00:05,  1.49it/s]Traceback (most recent call last):
  File "/usr/local/bin/sqlfluff", line 8, in <module>
    sys.exit(cli())
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1128, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1053, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1659, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1395, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 754, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlfluff/cli/commands.py", line 480, in lint
    result = lnt.lint_paths(
  File "/usr/local/lib/python3.8/site-packages/sqlfluff/core/linter/linter.py", line 964, in lint_paths
    self.lint_path(
  File "/usr/local/lib/python3.8/site-packages/sqlfluff/core/linter/linter.py", line 916, in lint_path
    for i, linted_file in enumerate(runner.run(fnames, fix), start=1):
  File "/usr/local/lib/python3.8/site-packages/sqlfluff/core/linter/runner.py", line 101, in run
    for fname, partial in self.iter_partials(fnames, fix=fix):
  File "/usr/local/lib/python3.8/site-packages/sqlfluff/core/linter/runner.py", line 54, in iter_partials
    for fname, rendered in self.iter_rendered(fnames):
  File "/usr/local/lib/python3.8/site-packages/sqlfluff/core/linter/runner.py", line 43, in iter_rendered
    yield fname, self.linter.render_file(fname, self.config)
  File "/usr/local/lib/python3.8/site-packages/sqlfluff/core/linter/linter.py", line 642, in render_file
    return self.render_string(raw_file, fname, config, encoding)
  File "/usr/local/lib/python3.8/site-packages/sqlfluff/core/linter/linter.py", line 619, in render_string
    templated_file, templater_violations = self.templater.process(
  File "/usr/local/lib/python3.8/site-packages/sqlfluff_templater_dbt/templater.py", line 330, in process
    processed_result = self._unsafe_process(fname_absolute_path, in_str, config)
  File "/usr/local/lib/python3.8/site-packages/sqlfluff_templater_dbt/templater.py", line 449, in _unsafe_process
    node = self.dbt_compiler.compile_node(
  File "/usr/local/lib/python3.8/site-packages/dbt/compilation.py", line 550, in compile_node
    node = self._compile_node(node, manifest, extra_context)
  File "/usr/local/lib/python3.8/site-packages/dbt/compilation.py", line 388, in _compile_node
    compiled_node.compiled_sql = jinja.get_rendered(
  File "/usr/local/lib/python3.8/site-packages/dbt/clients/jinja.py", line 597, in get_rendered
    return render_template(template, ctx, node)
  File "/usr/local/lib/python3.8/site-packages/dbt/clients/jinja.py", line 548, in render_template
    return template.render(ctx)
  File "/usr/local/lib/python3.8/site-packages/jinja2/environment.py", line 1090, in render
    self.environment.handle_exception()
  File "/usr/local/lib/python3.8/site-packages/jinja2/environment.py", line 832, in handle_exception
    reraise(*rewrite_traceback_stack(source=source))
  File "/usr/local/lib/python3.8/site-packages/jinja2/_compat.py", line 28, in reraise
    raise value.with_traceback(tb)
  File "<template>", line 2, in top-level template code
  File "/usr/local/lib/python3.8/site-packages/jinja2/sandbox.py", line 462, in call
    return __context.call(__obj, *args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/dbt/clients/jinja.py", line 332, in __call__
    return self.call_macro(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/dbt/clients/jinja.py", line 259, in call_macro
    return macro(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/jinja2/runtime.py", line 679, in _invoke
    rv = self._func(*arguments)
  File "<template>", line 2, in template
  File "/usr/local/lib/python3.8/site-packages/jinja2/sandbox.py", line 462, in call
    return __context.call(__obj, *args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/dbt/clients/jinja.py", line 332, in __call__
    return self.call_macro(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/dbt/clients/jinja.py", line 259, in call_macro
    return macro(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/jinja2/runtime.py", line 679, in _invoke
    rv = self._func(*arguments)
  File "<template>", line 16, in template
  File "/usr/local/lib/python3.8/site-packages/jinja2/sandbox.py", line 462, in call
    return __context.call(__obj, *args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/dbt/clients/jinja.py", line 332, in __call__
    return self.call_macro(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/dbt/clients/jinja.py", line 259, in call_macro
    return macro(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/jinja2/runtime.py", line 679, in _invoke
    rv = self._func(*arguments)
  File "<template>", line 2, in template
  File "/usr/local/lib/python3.8/site-packages/jinja2/sandbox.py", line 462, in call
    return __context.call(__obj, *args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/dbt/clients/jinja.py", line 332, in __call__
    return self.call_macro(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/dbt/clients/jinja.py", line 259, in call_macro
    return macro(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/jinja2/runtime.py", line 679, in _invoke
    rv = self._func(*arguments)
  File "<template>", line 2, in template
  File "/usr/local/lib/python3.8/site-packages/jinja2/sandbox.py", line 462, in call
    return __context.call(__obj, *args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/dbt/clients/jinja.py", line 332, in __call__
    return self.call_macro(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/dbt/clients/jinja.py", line 259, in call_macro
    return macro(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/jinja2/runtime.py", line 679, in _invoke
    rv = self._func(*arguments)
  File "<template>", line 10, in template
  File "/usr/local/lib/python3.8/site-packages/jinja2/sandbox.py", line 462, in call
    return __context.call(__obj, *args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/dbt/adapters/base/impl.py", line 225, in execute
    return self.connections.execute(
  File "/usr/local/lib/python3.8/site-packages/dbt/adapters/sql/connections.py", line 127, in execute
    _, cursor = self.add_query(sql, auto_begin)
  File "/usr/local/lib/python3.8/site-packages/dbt/adapters/sql/connections.py", line 59, in add_query
    connection = self.get_thread_connection()
  File "/usr/local/lib/python3.8/site-packages/dbt/adapters/base/connections.py", line 69, in get_thread_connection
    raise dbt.exceptions.InvalidConnectionException(
dbt.exceptions.InvalidConnectionException: Runtime Error
  connection never acquired for thread (82, 140226265859904), have []

Version

root@541f43ad6080:/dbt-runner/dbt# sqlfluff --version
sqlfluff, version 0.9.0
root@3c162864a926:/dbt-runner/dbt# python --version
Python 3.8.12

output of dbt debug:

root@541f43ad6080:/dbt-runner/dbt# dbt debug                                                                                   
21:50:16  Running with dbt=1.0.0                     
dbt version: 1.0.0                       
python version: 3.8.12                                                                                                         
python path: /usr/local/bin/python               
os info: Linux-5.10.47-linuxkit-x86_64-with-glibc2.2.5                                                                         
Using profiles.yml file at /dbt-runner/dbt/profiles.yml
Using dbt_project.yml file at /dbt-runner/dbt/dbt_project.yml                                                                  
                                                               
Configuration:                                                                                                                 
  profiles.yml file [OK found and valid]          
  dbt_project.yml file [OK found and valid]                                                                                    
                                                                                                                               
Required dependencies:                                                                                                         
 - git [OK found]                                                                                                              
                                                                                                                               
Connection:                                                                                                                    
  host: host.docker.internal                                                                                                   
  port: 5432                                                                                                                   
  user: sterling.paramore                                                                                                      
  database: blert                                             
  schema: wh                                                                                                                   
  search_path: None                                   
  keepalives_idle: 0                                                                                                           
  sslmode: None                                             
  Connection test: [OK connection ok]                                                                                          
                                                                                                                               
All checks passed!

Configuration

[sqlfluff]
verbose = 0
nocolor = False
dialect = ansi
templater = dbt
rules = None
exclude_rules = L009
recurse = 0
output_line_length = 80
runaway_limit = 10
ignore_templated_areas = True
# Comma separated list of file extensions to lint.

# NB: This config will only apply in the root folder.
sql_file_exts = .sql,.sql.j2,.dml,.ddl

[sqlfluff:indentation]
indented_joins = False
template_blocks_indent = True

[sqlfluff:templater]
unwrap_wrapped_queries = True

[sqlfluff:templater:jinja]
apply_dbt_builtins = True

[sqlfluff:templater:jinja:macros]
# Macros provided as builtins for dbt projects
dbt_ref = {% macro ref(model_ref) %}dbt_{{model_ref}}{% endmacro %}
dbt_source = {% macro source(source_name, table) %}{{source_name}}_{{table}}{% endmacro %}
dbt_config = {% macro config() %}{% for k in kwargs %}{% endfor %}{% endmacro %}
dbt_var = {% macro var(variable) %}item{% endmacro %}
dbt_is_incremental = {% macro is_incremental() %}True{% endmacro %}

# Some rules can be configured directly from the config common to other rules.
[sqlfluff:rules]
tab_space_size = 4
max_line_length = 110
indent_unit = space
comma_style = trailing
allow_scalar = True
single_table_references = consistent
unquoted_identifiers_policy = all

# Some rules have their own specific config.
[sqlfluff:rules:L003]
lint_templated_tokens = True

[sqlfluff:rules:L010]  # Keywords
capitalisation_policy = consistent

[sqlfluff:rules:L014]  # Unquoted identifiers
extended_capitalisation_policy = consistent

[sqlfluff:rules:L016]
ignore_comment_lines = False

[sqlfluff:rules:L029]  # Keyword identifiers
unquoted_identifiers_policy = aliases

[sqlfluff:rules:L030]  # Function names
capitalisation_policy = consistent

[sqlfluff:rules:L038]
select_clause_trailing_comma = forbid

[sqlfluff:rules:L040]  # Null & Boolean Literals
capitalisation_policy = consistent

[sqlfluff:rules:L042]
# By default, allow subqueries in from clauses, but not join clauses.
forbid_subquery_in = join

[sqlfluff:rules:L047]  # Consistent syntax to count all rows
prefer_count_1 = False

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Reactions: 12
  • Comments: 24 (8 by maintainers)

Most upvoted comments

I’ve this issue when using is_incremental from DBT

I managed to try it out, and it worked for me! @jpy-git when using poetry, I did it in another way 😃

@BeWe11, @noppaz: Can one of you create a new issue? Please provide an example to reproduce the problem if possible.

It looks like this issue is being addressed in this PR if you want to track progress.

@hugohjerten yes you’re right the dbt templater is very useful and hopefully, this issue will be fixed shortly 😢 I had the same issue as you and the only short term solution I had was to add my custom macro to sqlfluff:templater:jinja:macros until the dbt template is fixed.

For example, If I had a custom macro named my_custom_labels and a boolean parameter output_schema I could manually specify the output for the macro:

[tool.sqlfluff.templater.jinja.macros]
dbt_ref            = "{% macro ref(model_ref) %}dbt_{{model_ref}}{% endmacro %}"
dbt_source         = "{% macro source(source_name, table) %}{{source_name}}_{{table}}{% endmacro %}"
dbt_config         = "{% macro config() %}{% for k in kwargs %}{% endfor %}{% endmacro %}"
dbt_var            = "{% macro var(variable) %}item{% endmacro %}"
dbt_is_incremental = "{% macro is_incremental() %}False{% endmacro %}"
my_custom_labels   = "{% macro my_custom_labels(output_schema = False) %}example_label{% endmacro %}"

Note: this is the syntax when using pyproject.toml

I can also confirm this issue dbt 1.0.0, sqlfluff 0.9.0, sqlfluff-templater-dbt 0.9.0

Problem occurs when {% if is_incremental() %} is used in model - then dbt needs to set up a connection to a database in order to check if condition should be applied or not.