Seed files throw pyodbc error HY000

[curious-bryan]

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:

1. Cloned repository above
2. Created jaffle_shop profile in ~/.dbt/profiles.yml using
   • type: mssql
   • driver: 'ODBC Driver 17 for SQL Server'
   • ...
3. Changed into jaffle_shop directory
4. Ensured profile setup correctly with dbt debug <- ALL GREEN SUCCESS OKs
5. 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

[jacobm001]

Someone in my office had a similar issue, which was referenced in the latest release, 1.0.3. Please do a pip update and report back if that resolves your issue.

[curious-bryan]

@jacobm001, this issue happened with the latest release (1.0.3).

I also ran pip install dbt-mssql --upgrade just in case. Then followed steps above. And I encountered the same HY000 error from pyobdc after running dbt seed.

I also noticed that my profile with type : mssql does not pass dbt-integrations-test. dbt seed fails there too. See errors below from dbt-integration-test.

Error from dbt-integration-test

Test direct copying of source tables    # features/001_basic_materializations.feature
  Test materialized='view' -- @1.1   ......F
--------------------------------------------------------------------------------
FAILURE in step 'I successfully run "dbt seed"' (features/001_basic_materializations.feature:45):
Assertion Failed: Running with dbt=0.14.0
Found 1 models, 1 tests, 0 snapshots, 0 analyses, 225 macros, 0 operations, 1 seed files, 0 sources

14:08:33 | Concurrency: 1 threads (target='dev')
14:08:33 |
14:08:33 | 1 of 1 START seed file dbt_dev.seed.............................. [RUN]
14:08:33 | 1 of 1 ERROR loading seed file dbt_dev.seed...................... [ERROR in 0.11s]
14:08:33 |
14:08:33 | Finished running 1 seeds in 0.89s.

Completed with 1 errors:

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

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

Expected: <0>
     but: was <1>

--------------------------------------------------------------------------------


Failing scenarios:
  features/001_basic_materializations.feature:51  Test materialized='view' -- @1.1

1 feature passed, 1 failed, 0 skipped, 1 untested
0 scenarios passed, 1 failed, 0 skipped, 12 untested
6 steps passed, 1 failed, 2 skipped, 0 undefined, 139 untested
Took 0m14.121s

[jacobm001]

It looks like I ran into a build tool error and ended up committing a breaking change without realizing it... My apologies.

I am currently traveling for work, but will try to resolve these issues this weekend.

[curious-bryan]

@jacobm001 Roger that. Looking forward to it. In the mean time, I have been looking into adapters.sql for dbt-mssql (1.0.3). And I was wondering if you would be interested in collaborating on dbt-mssql. No worries if not.

[curious-bryan]

@jacobm001, heads up! When you get back to it this weekend, for Line 1 in dbt-mssql/dbt/include/mssql/macros/materializations/seed/seed.sql, I think there might be a typo for the macro name.

Currently (1.0.3 and also 1.0.2), the macro name is typed as basic_load_csv_rows(...). From the documentation, it appears the name might need to be changed to mssql__load_csv_rows(...) so that when adapter_macro(...) is called from macro load_csv_rows in seed.sql in the core dbt package (See Lines 10-12 here), the macro resolves to dbt-mssql's code for how to load CSV rows.

If so, I think the macro default__load_csv_rows is being called instead of mssql__load_csv_rows. And default__load_csv_rows uses %s as the parameter marker instead of ? which may be throwing the pyobdc error.

I apologize. I haven't had a chance to test locally. However, if that change does need to be made, then Lines 36 through 39 in dbt-mssql/.../seed.sql may need to be re-considered if still needed.

[jacobm001]

I have confirmed that this issue is due to my tooling. While the 1.0.3 release on github works fine, the setup.py file didn't correctly grab all the needed macros. That has now been fixed in the branch issue-6. I don't have my test harness configured on this machine, but I will run that branch through the test harness on Monday, and if that passes, I'll merge it into master and do a 1.0.4 release on PyPi.