sqlserver__get_columns_in_query not working

[dataders]

background

sqlserver__get_columns_in_query was added by @qed- in #56 as basically a near copy of the base adapter version get_columns_in_query I'm working on it for the workaround suggested by the venerable @jtcohen6 in microsoft/dbt-synapse#40.

TL;DR

The return statement below isn't working because:

• accessing the .columns attr of load_result().table throws and error, and
• when I can get an Agate table I can't access the column column and make it into a list

{{ return(load_result('get_columns_in_query').table.columns | map(attribute='name') | list) }}

dbt-sqlserver/dbt/include/sqlserver/macros/adapters.sql

Lines 6 to 15 in 4b3f7bc

	{% macro sqlserver__get_columns_in_query(select_sql) %}
	{% call statement('get_columns_in_query', fetch_result=True, auto_begin=False) -%}
	select TOP 0 * from (
	{{ select_sql }}
	) as __dbt_sbq
	where 0 = 1
	{% endcall %}
	{{ return(load_result('get_columns_in_query').table.columns | map(attribute='name') | list) }}
	{% endmacro %}

reproduction

in the https://github.com/dbt-msft/jaffle_shop_mssql/, I make a dummy model for testing that looks like this (after running dbt seed)

environment

installed dbt-sqlserver in develop mode (pip install -e .) from lastest commit of master branch (full list of packages installed)

$ pip list | grep "dbt"
dbt-core                 0.19.0rc2
dbt-sqlserver            0.19.0rc2  /Users/anders.swanson/repos/dbt-sqlserver
pytest-dbt-adapter       0.4.0

anders.sql

{% set select_sql = 'select * from ' ~ ref('raw_orders') %}
{% set output = sqlserver__get_columns_in_query(select_sql) %}
{{ log('output:\n' ~ output, info=True) }}

error

full stacktrace

Running with dbt=0.19.0-rc2
Encountered an error:
Compilation Error in model anders (models/marts/core/anders.sql)
  'None' has no attribute 'table'
  
  > in macro sqlserver__get_columns_in_query (macros/adapters.sql)
  > called by model anders (models/marts/core/anders.sql)

attempts to fix

1) adding log information

I commented out the replaced the return statement with these logs. below is the result

{% set result = load_result('get_columns_in_query') %}
{{ log('result:\n' ~ result, info=True) }}
{{ log('result_table:\n' ~ result['table'], info=True) }}
{{ return([]) }}

full log

Running with dbt=0.19.0-rc2
result:
None
result_table:

output:
[]
Found 9 models, 20 tests, 0 snapshots, 0 analyses, 363 macros, 0 operations, 3 seed files, 0 sources, 0 exposures

17:35:15 | Concurrency: 1 threads (target='azsql')
17:35:15 | 
result:
{'response': AdapterResponse(_message='OK', code=None, rows_affected=0), 'data': [], 'table': <agate.table.Table object at 0x7f8d7d133240>}
result_table:
| column     | data_type |
| ---------- | --------- |
| id         | Number    |
| user_id    | Number    |
| order_date | Number    |
| status     | Number    |

2) using sqlserver__get_columns_from_relation()'s return syntax

sqlserver__get_columns_from_relation()'s return syntax works fine with

  {% set table = load_result('get_columns_in_relation').table %}
  {{ return(sql_convert_columns_in_relation(table)) }}

So I tried that, but sql_convert_columns_in_relation() expects each column to be a row, so converted_columns is an empty list.

{% set table = load_result('get_columns_in_query').table %}
{{ log('table:\n' ~ table, info=True) }}
{% set coverted_columns = sql_convert_columns_in_relation(table) %}
{{ log('coverted_columns:\n' ~ coverted_columns, info=True) }}
{{ return(coverted_columns) }}

table:
| column     | data_type |
| ---------- | --------- |
| id         | Number    |
| user_id    | Number    |
| order_date | Number    |
| status     | Number    |

coverted_columns:
[]
output:
[]
17:49:06 | Done.

3) access first column of Agate table

I thought I could copy this pattern form the Agate docs to get the column column
table.columns[0] or table.columns['column']. Fail again with the same original error:

 'None' has no attribute 'table'

Which peeves me to no end bc you can see that result.table is an agate.table.Table object!

4) debug in an ipython term to figure out how to properly query Agate table...

1. I tried to use {{ debug() }} but got an error that debug is undefined.
2. I saw the note in the docs about setting DBT_MACRO_DEBUGGING
3. I ran export DBT_MACRO_DEBUGGING=1
4. I call dbt compile and get No module named ipdb`
5. pip install ipdb
6. No module named ipdb`
7. open this GitHub issue.

[jtcohen6]

@swanderz This is one of the most confusing things in dbt-Jinja, and it has to do with execute (docs). Basically, during dbt's first step-through of your project—when it's just trying to capture ref, source, and config to build the DAG and determine run order—it does not run any SQL, so any Jinja objects that are defined via call blocks are simply set to None. You can tell dbt to ignore these objects during the first step-through with execute.

This could be addressed in your reproduction case, anders.sql:

{% set select_sql = 'select * from ' ~ ref('raw_orders') %}
{% if execute %}
  {% set output = sqlserver__get_columns_in_query(select_sql) %}
  {{ log('output:\n' ~ output, info=True) }}
{% endif %}

I suppose you could add an if execute filter directly within sqlserver__get_columns_in_query, too, though it doesn't look like the default implementation does that today either.

[dataders]

@jtcohen6 another lesson for the books! 🙏