jacobm001 / dbt-mssql Goto Github PK

View Code? Open in Web Editor NEW

7.0 7.0 13.0 57 KB

A custom dbt adapter for Microsoft SQL Server using pyodbc as the connection library.

License: Apache License 2.0

Python 63.32% TSQL 36.68%

dbt-mssql's People

Contributors

Stargazers

Watchers

Forkers

curious-bryan norton120 1a-auto nrussell1aauto josephcdonaldson 0sahilahmed

dbt-mssql's Issues

Seed files throw pyodbc error HY000

Summary

I ran into an error HY000 from pyodbc after running dbt seed while trying to recreate jaffle_shop tutorial for dbt at https://github.com/fishtown-analytics/jaffle_shop

What happened:

Cloned repository above
Created jaffle_shop profile in ~/.dbt/profiles.yml using
- type: mssql
- driver: 'ODBC Driver 17 for SQL Server'
- ...
Changed into jaffle_shop directory
Ensured profile setup correctly with dbt debug <- ALL GREEN SUCCESS OKs
Ran dbt seed <- ERRORs thrown for all three CSVs in jaffle_shop data directory. See below.

What I expected to happen:

I expected the 3 CSVs in /path/to/jaffle_shop/data/ to be loaded into tables on my database.

Errors

Running with dbt=0.14.0
Found 8 models, 20 tests, 0 snapshots, 0 analyses, 113 macros, 0 operations, 3 seed files, 0 sources

11:18:05 | Concurrency: 1 threads (target='dev')
11:18:05 |
11:18:05 | 1 of 3 START seed file dbt_dev.raw_customers..................... [RUN]
11:18:05 | 1 of 3 ERROR loading seed file dbt_dev.raw_customers............. [ERROR in 0.14s]
11:18:05 | 2 of 3 START seed file dbt_dev.raw_orders........................ [RUN]
11:18:05 | 2 of 3 ERROR loading seed file dbt_dev.raw_orders................ [ERROR in 0.02s]
11:18:05 | 3 of 3 START seed file dbt_dev.raw_payments...................... [RUN]
11:18:05 | 3 of 3 ERROR loading seed file dbt_dev.raw_payments.............. [ERROR in 0.02s]
11:18:05 |
11:18:05 | Finished running 3 seeds in 1.45s.

Completed with 3 errors:

Database Error in seed raw_customers (data\raw_customers.csv)
  ('The SQL contains 0 parameter markers, but 300 parameters were supplied', 'HY000')

Database Error in seed raw_orders (data\raw_orders.csv)
  ('The SQL contains 0 parameter markers, but 396 parameters were supplied', 'HY000')

Database Error in seed raw_payments (data\raw_payments.csv)
  ('The SQL contains 0 parameter markers, but 452 parameters were supplied', 'HY000')

Done. PASS=0 ERROR=3 SKIP=0 TOTAL=3

Environment Details

local OS: Windows 10
remote OS: Windows Server 2016
remote MSSQL: MS SQL Server 2017
dbt version: 0.14.0
let me know if you need more details on python version, pyodbc version etc

No module named 'dbt.compat'

Having a bit of trouble with dbt-mssql installed with pip on Linux, CentOS7 using Python 3.7, running in a virtual environment. I am able to connect to sql server using vanilla python scripts without any issues.

error

$ dbt run
Running with dbt=0.15.0
Error importing adapter: No module named 'dbt.compat'
Encountered an error while reading profiles:
  ERROR Runtime Error
  Credentials in profile "my-mssql-db", target "dev" invalid: Runtime Error
    Could not find adapter type mssql!
Defined profiles:
 - my-mssql-db
 - dbt-sqlserver
 - default

Add support for 'dbt docs generate' by implementing get_catalog

Currently dbt documentation and the built-in documentation site don't work with dbt-mssql,

$ dbt docs generate
Running with dbt=0.14.3
Found 8 models, 0 tests, 0 snapshots, 0 analyses, 238 macros, 0 operations, 0 seed files, 0 sources

16:59:32 | Concurrency: 1 threads (target='dev')
16:59:32 |
16:59:32 | Done.
16:59:32 | Building catalog
Encountered an error:
Compilation Error in macro get_catalog (macros\adapters\common.sql)
  get_catalog not implemented for mssql

  > in macro default__get_catalog (macros\adapters\common.sql)
  > called by macro adapter_macro (macros\adapters\common.sql)
  > called by macro get_catalog (macros\adapters\common.sql)
  > called by macro get_catalog (macros\adapters\common.sql)

transfer to dbt-msft?

@jacobm001 any interest in moving your jaffle_shop fork, jaffle_shop_mssql, to the common dbt-msft org? this way we can ensure all you hard work stays relevant without it being a burden on you specifically.

Exclude password in _connection_keys

The _connection_keys method currently returns a list of keys including PWD. This list of keys is used to filter the information displayed by the dbt debug command. It's probably a good idea to remove the PWD field from this list, as it's likely undesirable to print out the password field in plaintext on the terminal.

Upgrade to 0.18.x

It doesn't seem to work on dbt 0.18.x - any plans on upgrading it to the latest version of dbt?

Feature: unique clustered index

If you change the clustered_index macro to below, you can add the possibillity to make it unique

{% macro create_clustered_index(columns, unique=False) -%}

{{ log("Creating clustered index...") }}

create
{% if unique -%}
unique
{% endif %}
clustered index
    {{ this.table }}__clustered_index_on_{{ columns|join("_") }}
      on {{ this }} ({{ '[' + columns|join("], [") + ']' }})

{%- endmacro %}

Seed files throw pyodbc error 07002

With the current release (1.0.2), running the command dbt seed will throw the following error:

2019-08-07 12:31:28,494 (MainThread): Runtime Error in seed weights (data\weights.csv)
2019-08-07 12:31:28,495 (MainThread):   ('07002', '[07002] [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')

dbt test throws errors for jaffle_shop_mssql example

What happened

Cloned jaffle_shop_mssql from https://github.com/jacobm001/jaffle_shop_mssql
Connected to DB using dbt-mssql
Changed directory to jaffle_shop_mssql
Ran dbt test from cmd.exe <- ERRORs thrown; see Errors below.

What I expected to happen

I expected running dbt test on jaffle_shop_mssql to run successfully without errors.

Errors

Running with dbt=0.14.0
Found 8 models, 20 tests, 0 snapshots, 0 analyses, 117 macros, 0 operations, 3 seed files, 0 sources

11:51:06 | Concurrency: 1 threads (target='dev')
11:51:06 |
11:51:06 | 1 of 20 START test accepted_values_fct_orders_status__placed__shipped__completed__return_pending__returned [RUN]
Unhandled error while executing test.jaffle_shop.accepted_values_fct_orders_status__placed__shipped__completed__return_pending__returned
tuple.index(x): x not in tuple
11:51:06 | 1 of 20 ERROR accepted_values_fct_orders_status__placed__shipped__completed__return_pending__returned [ERROR in 0.05s]
11:51:06 | 2 of 20 START test accepted_values_stg_orders_status__placed__shipped__completed__return_pending__returned [RUN]
Unhandled error while executing test.jaffle_shop.accepted_values_stg_orders_status__placed__shipped__completed__return_pending__returned
tuple.index(x): x not in tuple
11:51:06 | 2 of 20 ERROR accepted_values_stg_orders_status__placed__shipped__completed__return_pending__returned [ERROR in 0.02s]
11:51:06 | 3 of 20 START test accepted_values_stg_payments_payment_method__credit_card__coupon__bank_transfer__gift_card [RUN]
Unhandled error while executing test.jaffle_shop.accepted_values_stg_payments_payment_method__credit_card__coupon__bank_transfer__gift_card
tuple.index(x): x not in tuple
11:51:06 | 3 of 20 ERROR accepted_values_stg_payments_payment_method__credit_card__coupon__bank_transfer__gift_card [ERROR in 0.02s]
11:51:06 | 4 of 20 START test not_null_dim_customers_customer_id................ [RUN]
Unhandled error while executing test.jaffle_shop.not_null_dim_customers_customer_id
tuple.index(x): x not in tuple
11:51:06 | 4 of 20 ERROR not_null_dim_customers_customer_id..................... [ERROR in 0.02s]
11:51:06 | 5 of 20 START test not_null_fct_orders_amount........................ [RUN]
Unhandled error while executing test.jaffle_shop.not_null_fct_orders_amount
tuple.index(x): x not in tuple
11:51:06 | 5 of 20 ERROR not_null_fct_orders_amount............................. [ERROR in 0.02s]
11:51:06 | 6 of 20 START test not_null_fct_orders_bank_transfer_amount.......... [RUN]
Unhandled error while executing test.jaffle_shop.not_null_fct_orders_bank_transfer_amount
tuple.index(x): x not in tuple
11:51:06 | 6 of 20 ERROR not_null_fct_orders_bank_transfer_amount............... [ERROR in 0.02s]
11:51:06 | 7 of 20 START test not_null_fct_orders_coupon_amount................. [RUN]
Unhandled error while executing test.jaffle_shop.not_null_fct_orders_coupon_amount
tuple.index(x): x not in tuple
11:51:06 | 7 of 20 ERROR not_null_fct_orders_coupon_amount...................... [ERROR in 0.02s]
11:51:06 | 8 of 20 START test not_null_fct_orders_credit_card_amount............ [RUN]
Unhandled error while executing test.jaffle_shop.not_null_fct_orders_credit_card_amount
tuple.index(x): x not in tuple
11:51:06 | 8 of 20 ERROR not_null_fct_orders_credit_card_amount................. [ERROR in 0.02s]
11:51:06 | 9 of 20 START test not_null_fct_orders_customer_id................... [RUN]
Unhandled error while executing test.jaffle_shop.not_null_fct_orders_customer_id
tuple.index(x): x not in tuple
11:51:06 | 9 of 20 ERROR not_null_fct_orders_customer_id........................ [ERROR in 0.02s]
11:51:06 | 10 of 20 START test not_null_fct_orders_gift_card_amount............. [RUN]
Unhandled error while executing test.jaffle_shop.not_null_fct_orders_gift_card_amount
tuple.index(x): x not in tuple
11:51:06 | 10 of 20 ERROR not_null_fct_orders_gift_card_amount.................. [ERROR in 0.03s]
11:51:06 | 11 of 20 START test not_null_fct_orders_order_id..................... [RUN]
Unhandled error while executing test.jaffle_shop.not_null_fct_orders_order_id
tuple.index(x): x not in tuple
11:51:06 | 11 of 20 ERROR not_null_fct_orders_order_id.......................... [ERROR in 0.06s]
11:51:06 | 12 of 20 START test not_null_stg_customers_customer_id............... [RUN]
Unhandled error while executing test.jaffle_shop.not_null_stg_customers_customer_id
tuple.index(x): x not in tuple
11:51:06 | 12 of 20 ERROR not_null_stg_customers_customer_id.................... [ERROR in 0.03s]
11:51:06 | 13 of 20 START test not_null_stg_orders_order_id..................... [RUN]
Unhandled error while executing test.jaffle_shop.not_null_stg_orders_order_id
tuple.index(x): x not in tuple
11:51:06 | 13 of 20 ERROR not_null_stg_orders_order_id.......................... [ERROR in 0.02s]
11:51:06 | 14 of 20 START test not_null_stg_payments_payment_id................. [RUN]
Unhandled error while executing test.jaffle_shop.not_null_stg_payments_payment_id
tuple.index(x): x not in tuple
11:51:06 | 14 of 20 ERROR not_null_stg_payments_payment_id...................... [ERROR in 0.02s]
11:51:06 | 15 of 20 START test relationships_fct_orders_customer_id__customer_id__ref_dim_customers_ [RUN]
Unhandled error while executing test.jaffle_shop.relationships_fct_orders_customer_id__customer_id__ref_dim_customers_
tuple.index(x): x not in tuple
11:51:06 | 15 of 20 ERROR relationships_fct_orders_customer_id__customer_id__ref_dim_customers_ [ERROR in 0.04s]
11:51:06 | 16 of 20 START test unique_dim_customers_customer_id................. [RUN]
Unhandled error while executing test.jaffle_shop.unique_dim_customers_customer_id
tuple.index(x): x not in tuple
11:51:06 | 16 of 20 ERROR unique_dim_customers_customer_id...................... [ERROR in 0.02s]
11:51:06 | 17 of 20 START test unique_fct_orders_order_id....................... [RUN]
Unhandled error while executing test.jaffle_shop.unique_fct_orders_order_id
tuple.index(x): x not in tuple
11:51:07 | 17 of 20 ERROR unique_fct_orders_order_id............................ [ERROR in 0.02s]
11:51:07 | 18 of 20 START test unique_stg_customers_customer_id................. [RUN]
Unhandled error while executing test.jaffle_shop.unique_stg_customers_customer_id
tuple.index(x): x not in tuple
11:51:07 | 18 of 20 ERROR unique_stg_customers_customer_id...................... [ERROR in 0.02s]
11:51:07 | 19 of 20 START test unique_stg_orders_order_id....................... [RUN]
Unhandled error while executing test.jaffle_shop.unique_stg_orders_order_id
tuple.index(x): x not in tuple
11:51:07 | 19 of 20 ERROR unique_stg_orders_order_id............................ [ERROR in 0.03s]
11:51:07 | 20 of 20 START test unique_stg_payments_payment_id................... [RUN]
Unhandled error while executing test.jaffle_shop.unique_stg_payments_payment_id
tuple.index(x): x not in tuple
11:51:07 | 20 of 20 ERROR unique_stg_payments_payment_id........................ [ERROR in 0.02s]
11:51:07 |
11:51:07 | Finished running 20 tests in 1.47s.

Completed with 20 errors:

tuple.index(x): x not in tuple
tuple.index(x): x not in tuple
tuple.index(x): x not in tuple
tuple.index(x): x not in tuple
tuple.index(x): x not in tuple
tuple.index(x): x not in tuple
tuple.index(x): x not in tuple
tuple.index(x): x not in tuple
tuple.index(x): x not in tuple
tuple.index(x): x not in tuple
tuple.index(x): x not in tuple
tuple.index(x): x not in tuple
tuple.index(x): x not in tuple
tuple.index(x): x not in tuple
tuple.index(x): x not in tuple
tuple.index(x): x not in tuple
tuple.index(x): x not in tuple
tuple.index(x): x not in tuple
tuple.index(x): x not in tuple
tuple.index(x): x not in tuple

Done. PASS=0 ERROR=20 SKIP=0 TOTAL=20

Environment Variables

dbt==0.14.0
dbt-mssql==1.0.5
jaffle_shop_mssql (latest commit a4aad290813d078c05755d00d8c954f0482f7572)

Issue: create_nonclustered_index include doesn't work

The code for including columns in an index is looking for the wrong in-parameter. The parameter is includes but the if-statement looks for include_names

{% macro create_nonclustered_index(columns, includes) %}

{% if include_names is undefined %}

If you change to below it will work


{% macro create_nonclustered_index(columns, includes=False) %}

{{ log("Creating nonclustered index...") }}

create nonclustered index
    {{ this.table }}__index_on_{{ columns|join("_") }}
      on {{ this }} ({{ '[' + columns|join("], [") + ']' }})
      {% if includes -%}
        include ({{ '[' + includes|join("], [") + ']' }})
      {% endif %}

{% endmacro %}

Documentation note about making sure 'ODBC Driver 17 for SQL Server' is installed on local machine

@jacobm001 thank you for developing this dbt database adapter for MSSQL! Fantastic contribution!

It may be helpful to some newbie dbt users to have a reminder like:

Remember to check that 'ODBC Driver 17 for SQL Server' is installed on your local machine.

I had this issue. 🤦‍♂

If you agree, here are some links.
If you disagree, I totally understand. It is not your job to remind others of the basics.

How to check: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/check-the-odbc-sql-server-driver-version-windows?view=sql-server-2017

Where to download: https://www.microsoft.com/en-us/download/details.aspx?id=56567

Drop Index Macro

To decrease model build time, it's often wise to drop indexes before the model is created or rebuilt and then rebuild them on completion. An macro that could drop a named index if it already exists (and not error if it doesn't) would be very helpful.

Model files throw 42000 errors

Summary

Trying to recreate jaffle_shop tutorial for dbt at https://github.com/fishtown-analytics/jaffle_shop
Related to #6 (comment)
Ran into 42000 database errors after running dbt run

What happened:

Ran into issue with loading CSVs with dbt seed; see #6 (comment)
Loaded CSVs as flat files directly to DB via SQL Server Management Studio
Ran dbt run <- ERRORs thrown for all 3 staging models in jaffle_shop model directory. See errors below.

What I expected to happen:

After loading CSVs directly into SQL Server DB, I expected dbt run to successfully load and run models found in staging and marts.

Errors

Running with dbt=0.14.0
Found 8 models, 20 tests, 0 snapshots, 0 analyses, 113 macros, 0 operations, 3 seed files, 0 sources

11:28:24 | Concurrency: 1 threads (target='dev')
11:28:24 |
11:28:24 | 1 of 8 START view model dbt_dev.stg_orders....................... [RUN]
11:28:24 | 1 of 8 ERROR creating view model dbt_dev.stg_orders.............. [ERROR in 0.11s]
11:28:24 | 2 of 8 START view model dbt_dev.stg_payments..................... [RUN]
11:28:24 | 2 of 8 ERROR creating view model dbt_dev.stg_payments............ [ERROR in 0.04s]
11:28:24 | 3 of 8 START view model dbt_dev.stg_customers.................... [RUN]
11:28:25 | 3 of 8 ERROR creating view model dbt_dev.stg_customers........... [ERROR in 0.04s]
11:28:25 | 4 of 8 SKIP relation dbt_dev.customer_orders..................... [SKIP]
11:28:25 | 5 of 8 SKIP relation dbt_dev.customer_payments................... [SKIP]
11:28:25 | 6 of 8 SKIP relation dbt_dev.order_payments...................... [SKIP]
11:28:25 | 7 of 8 SKIP relation dbt_dev.dim_customers....................... [SKIP]
11:28:25 | 8 of 8 SKIP relation dbt_dev.fct_orders.......................... [SKIP]
11:28:25 |
11:28:25 | Finished running 3 view models, 5 table models in 1.37s.

Completed with 3 errors:

Database Error in model stg_orders (models\staging\stg_orders.sql)
  ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. (319); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ','. (102); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ')'. (102)")
  compiled SQL at target\compiled\jaffle_shop\staging\stg_orders.sql

Database Error in model stg_payments (models\staging\stg_payments.sql)
  ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. (319); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ','. (102); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ')'. (102)")
  compiled SQL at target\compiled\jaffle_shop\staging\stg_payments.sql

Database Error in model stg_customers (models\staging\stg_customers.sql)
  ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. (319); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ','. (102); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ')'. (102)")
  compiled SQL at target\compiled\jaffle_shop\staging\stg_customers.sql

Done. PASS=0 ERROR=3 SKIP=5 TOTAL=8

Environment Details

See #6 (comment)
Let me know if you need more details

invalid syntax on connections.py

Running dbt debug results in an invalid syntax error in File "/usr/local/lib/python2.7/dist-packages/dbt/adapters/mssql/connections.py", line 139. See the debug output below.

I'm guessing I've missed something really simple - any ideas?

~/.dbt/profiles.yml:

my-mssql-db:
  target: dev
  outputs:
    dev:
      type: mssql
      driver: 'ODBC Driver 17 for SQL Server'
      host: sql01.local
      database: dbt_test
      schema: dbo
      username: dbt_user
      password: ******

Environment

$ python --version
Python 2.7.15+

$ pip list | grep dbt-mssql
dbt-mssql (1.0.7)

$  dbt --version
installed version: 0.14.3
   latest version: 0.14.3

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 18.04.2 LTS
Release:        18.04
Codename:       bionic

Debug Output

$ dbt -d debug
Running with dbt=0.14.3
2019-10-21 16:03:27,649 (MainThread): Tracking: tracking
2019-10-21 16:03:27,654 (MainThread): Sending event: {'category': 'dbt', 'action': 'invocation', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fa097e2f3d0>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fa097e2f750>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fa09b391bd0>], 'label': 'start'}
dbt version: 0.14.3
python version: 2.7.15+
python path: /usr/bin/python
os info: Linux-4.15.0-46-generic-x86_64-with-Ubuntu-18.04-bionic
Using profiles.yml file at /***/.dbt/profiles.yml

2019-10-21 16:03:28,781 (MainThread): Sending event: {'category': 'dbt', 'action': 'invocation', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fa09613b850>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fa09613b050>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fa09613b410>], 'label': 'end'}
2019-10-21 16:03:29,741 (MainThread): Flushing usage events
2019-10-21 16:03:29,741 (MainThread): Encountered an error:
2019-10-21 16:03:29,741 (MainThread): invalid syntax (connections.py, line 139)
2019-10-21 16:03:29,744 (MainThread): Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/dbt/main.py", line 82, in main
    results, succeeded = handle_and_check(args)
  File "/usr/local/lib/python2.7/dist-packages/dbt/main.py", line 151, in handle_and_check
    task, res = run_from_args(parsed)
  File "/usr/local/lib/python2.7/dist-packages/dbt/main.py", line 216, in run_from_args
    results = task.run()
  File "/usr/local/lib/python2.7/dist-packages/dbt/task/debug.py", line 108, in run
    self.test_configuration()
  File "/usr/local/lib/python2.7/dist-packages/dbt/task/debug.py", line 234, in test_configuration
    profile_status = self._load_profile()
  File "/usr/local/lib/python2.7/dist-packages/dbt/task/debug.py", line 212, in _load_profile
    self.profile = Profile.from_args(self.args, self.profile_name)
  File "/usr/local/lib/python2.7/dist-packages/dbt/config/profile.py", line 381, in from_args
    threads_override=threads_override
  File "/usr/local/lib/python2.7/dist-packages/dbt/config/profile.py", line 350, in from_raw_profiles
    threads_override=threads_override,
  File "/usr/local/lib/python2.7/dist-packages/dbt/config/profile.py", line 304, in from_raw_profile_info
    profile_data, profile_name, target_name
  File "/usr/local/lib/python2.7/dist-packages/dbt/config/profile.py", line 172, in _credentials_from_profile
    cls = load_plugin(typename)
  File "/usr/local/lib/python2.7/dist-packages/dbt/adapters/factory.py", line 32, in load_plugin
    mod = import_module('.' + adapter_name, 'dbt.adapters')
  File "/usr/lib/python2.7/importlib/__init__.py", line 37, in import_module
    __import__(name)
  File "/usr/local/lib/python2.7/dist-packages/dbt/adapters/mssql/__init__.py", line 1, in <module>
    from dbt.adapters.mssql.connections import MSSQLConnectionManager
  File "/usr/local/lib/python2.7/dist-packages/dbt/adapters/mssql/connections.py", line 139
    con_str.append(f"DRIVER={{{credentials.driver}}}")
                                                    ^
SyntaxError: invalid syntax

2019-10-21 16:03:29,747 (MainThread): DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020.
Please upgrade your Python as Python 2.7 won't be maintained after that date.
A future version of dbt will drop support for Python 2.7.

dbt seedParse error at line: 3, column: 26: Incorrect syntax near ','

I'm trying to connect to an Azure Synapse database.
I'm finding a similar issue with dbt-mssql==1.0.8 and dbt-azuredw==0.0.1

WIth both pacakges, I can connect just fine to the server (as far as dbt debug is concerned).

However, dbt seed is giving me trouble.

('42000', "[42000]
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
Parse error at line: 3, column: 26: Incorrect syntax near ','.
(103010) (SQLExecDirectW)")

`dbt-mssql`

> dbt seed
Running with dbt=0.14.4
Found 5 models, 32 tests, 0 snapshots, 0 analyses, 251 macros, 0 operations, 3 seed files, 2 sources

15:45:29 | Concurrency: 1 threads (target='adw')
15:45:29 | 
15:45:29 | 1 of 3 START seed file source.country_codes.......................... [RUN]
15:45:31 | 1 of 3 ERROR loading seed file source.country_codes.................. [ERROR in 0.87s]
15:45:31 | 2 of 3 START seed file source.productivity_subcategories............. [RUN]
15:45:32 | 2 of 3 ERROR loading seed file source.productivity_subcategories..... [ERROR in 0.67s]
15:45:32 | 3 of 3 START seed file source.static_codes........................... [RUN]
15:45:33 | 3 of 3 ERROR loading seed file source.static_codes................... [ERROR in 0.86s]
15:45:33 | 
15:45:33 | Finished running 3 seeds in 3.94s.

Completed with 3 errors and 0 warnings:

Database Error in seed country_codes (data/country_codes.csv)
  ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Parse error at line: 3, column: 26: Incorrect syntax near ','. (103010) (SQLExecDirectW)")

Database Error in seed productivity_subcategories (data/productivity_subcategories.csv)
  ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Parse error at line: 3, column: 26: Incorrect syntax near ','. (103010) (SQLExecDirectW)")

Database Error in seed static_codes (data/static_codes.csv)
  ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Parse error at line: 3, column: 22: Incorrect syntax near ','. (103010) (SQLExecDirectW)")

Done. PASS=0 WARN=0 ERROR=3 SKIP=0 TOTAL=3

`dbt-azuredw`

> dbt seed
Running with dbt=0.14.4
Found 5 models, 32 tests, 0 snapshots, 0 analyses, 243 macros, 0 operations, 3 seed files, 2 sources

15:37:54 | Concurrency: 1 threads (target='adw')
15:37:54 | 
15:37:54 | 1 of 3 START seed file source.country_codes.......................... [RUN]
15:37:56 | 1 of 3 ERROR loading seed file source.country_codes.................. [ERROR in 1.03s]
15:37:56 | 2 of 3 START seed file source.productivity_subcategories............. [RUN]
15:37:57 | 2 of 3 ERROR loading seed file source.productivity_subcategories..... [ERROR in 0.66s]
15:37:57 | 3 of 3 START seed file source.static_codes........................... [RUN]
15:37:58 | 3 of 3 ERROR loading seed file source.static_codes................... [ERROR in 0.73s]
15:37:58 | 
15:37:58 | Finished running 3 seeds in 4.01s.

Completed with 3 errors and 0 warnings:

Database Error in seed country_codes (data/country_codes.csv)
  ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Parse error at line: 3, column: 26: Incorrect syntax near ','. (103010) (SQLExecDirectW)")

Database Error in seed productivity_subcategories (data/productivity_subcategories.csv)
  ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Parse error at line: 3, column: 26: Incorrect syntax near ','. (103010) (SQLExecDirectW)")

Database Error in seed static_codes (data/static_codes.csv)
  ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Parse error at line: 3, column: 22: Incorrect syntax near ','. (103010) (SQLExecDirectW)")

Done. PASS=0 WARN=0 ERROR=3 SKIP=0 TOTAL=3

Create Index Macro

To decrease model build time, it's often wise to drop indexes before the model is created or rebuilt and then rebuild them on completion. A macro that could create an index if it doesn't already exist (and not error if one already does exist) would be helpful.

Database name 'XYZ' ignored, referencing object in tempdb.

dbt 0.14.4
dbt-mssql 1.0.8

My incrementally materialized model with unique_key='yymm' is resulting in the above mentioned error.

The issue seems to be that 'FRED.dbt_prem.#e070_fred_analysis_ssis_prem_dbt__dbt_tmp' in the last Select statement below is referencing a temp table (#) that's not in temp db.

I see dbt creates a view named 'dbt_prem.e070_fred_analysis_ssis_prem_dbt__dbt_temp_dbtmssql_tmp'. Perhaps that's the object to select from, instead?

Please see the log below.

======================================
2020-05-24 08:33:28,168 (Thread-1): Using mssql connection "e070_fred_analysis_ssis_prem_dbt".
2020-05-24 08:33:28,168 (Thread-1): On e070_fred_analysis_ssis_prem_dbt:


       delete
  from "FRED"."dbt_prem"."e070_fred_analysis_ssis_prem_dbt"
  where (yymm) in (
    select (yymm)
    from "e070_fred_analysis_ssis_prem_dbt__dbt_tmp"
  );

       insert into "FRED"."dbt_prem"."e070_fred_analysis_ssis_prem_dbt" ("Run_Id", "Accgrp1", "Accgrp2", "Account", "Adjusted", "Wp", "YYMM")
       (
         select "Run_Id", "Accgrp1", "Accgrp2", "Account", "Adjusted", "Wp", "YYMM"
         from FRED.dbt_prem.#e070_fred_analysis_ssis_prem_dbt__dbt_tmp
       );
     
2020-05-24 08:33:28,183 (Thread-1): Database error: ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'e070_fred_analysis_ssis_prem_dbt__dbt_tmp'. (208) (SQLExecDirectW); [42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Database name 'FRED' ignored, referencing object in tempdb. (2701)")
2020-05-24 08:33:28,184 (Thread-1): On e070_fred_analysis_ssis_prem_dbt: ROLLBACK
2020-05-24 08:33:28,189 (Thread-1): 08:33:28 | 14 of 14 ERROR creating incremental model dbt_prem.e070_fred_analysis_ssis_prem_dbt [ERROR in 706.77s]

Link to this in the dbt documentation

Hey @jacobm001! Nice work on this plugin!!

If you'd like, I'd be very happy to include a link to this repo in the dbt docs site. I'd like to include it under "Supported databases":

If that sounds alright to you, I can get this up on the site today :)

Couple of quick things:

Any plans / interest in putting this on pypi?
I can label this "Beta" and "Community Supported" -- we'll link back to the repo here, and PyPi if/when that's set up
I know that building a plugin is no small feat - please don't hesitate to let us know if we can ever be helpful in reviewing code / helping with upgrade the version of dbt-core / improving the adapter interface / etc.

Thanks!

License

Hi Jacob!

I would like to work on an Azure SQL Data Warehouse plugin and start from where you are using the pyodbc module.

Would you mind adding a license file so we can get started and publish ours ?
Something like: https://github.com/fishtown-analytics/dbt-spark/blob/master/License.md

Thanks,

Isaac

upgrade to v0.15.0

dbt v0.15.0 was released today 🎉 !

There were some breaking changes to the adapter plugin API which will require changes to this plugin. If you'd like, I'd be more than happy to send through a PR for 0.15.0 compatibility. Let me know if that's something you'd be interested in :)

jacobm001 / dbt-mssql Goto Github PK

dbt-mssql's People

Contributors

Stargazers

Watchers

Forkers

dbt-mssql's Issues

Summary

What happened:

What I expected to happen:

Errors

Environment Details

error

What happened

What I expected to happen

Errors

Environment Variables

Summary

What happened:

What I expected to happen:

Errors

Environment Details

~/.dbt/profiles.yml:

Environment

Debug Output

dbt-mssql

dbt-azuredw

Recommend Projects

Recommend Topics

Recommend Org

`dbt-mssql`

`dbt-azuredw`