codeforkjeff/dbt-sqlite

incremental model "You can only execute one statement at a time."

johannes-becker-bt opened this issue · 7 comments

johannes-becker-bt commented

Hi,
love the idea of a dbt sqlite adapter for demos and stuff.
Am working on a demo and wanted to show incremental functionality.
If you provide a unique key, then the macro sqlite_incremental_upsert tries to commit two comments (deletion and insert) leading to the
"You can only execute one statement at a time."
error.
I'll try to share the demo as well (will have to first configure github properly) and will try to help once I got deeper into the sqlite adapter.
But maybe you already have a quick idea how to solve this (or figure out what I'm doing wrong)
Cheers Hannes

The model is basically

{{ config(unique_key='unique_key') }}
SELECT
    *
FROM {{ref('STAGE_TABLE')}}
codeforkjeff commented

Thanks for giving this adapter a try!

What versions of dbt-sqlite and dbt-core are you using? The major/minor versions need to match, so if you're using dbt-core 1.1.x, make sure you're using dbt-sqlite 1.1.2. if you're using dbt-core 1.2.x, there's a 1.2.0a1 pre-release version you can try.

If you still get the error after making sure the versions match, please let me know.

johannes-becker-bt commented

I realized that I had a mismatch after posting but then tried both of the mentioned combinations to no avail.
Will try again tomorrow when I have a bit more time.
The model works when created but doesn’t work for the second run so maybe it fell through the cracks?

codeforkjeff commented

I was able to reproduce this problem. I released v1.1.3 which hopefully fixes this. Please let me know if it works for your case.

Note that if your unique key consists of multiple fields, this will only work with {{ config(unique_key='concat(field1,field2)') }} and NOT when specifying unique_key as a list, e.g. {{ config(unique_key=[ 'field1', 'field2' ] }}

I opened #32 with some notes for other fixes/improvements that need to be made to the incremental materialization.

johannes-becker-bt commented

Works pretty well.
One more question about the
unique_key='concat(field1,field2)'
I get a
"no such function: concat"
Weird thing is I used that function before, it's in dbt_utils
But now with
dbt-core 1.1.2
dbt_sqlite 1.1.3
dbt_utils 0.8.6
it does not find the macro concat anymore

For now I'll use
unique_key='field1||field2'
but concat would be better...

johannes-becker-bt commented

Also, in case you're interested, here's my work in progress (for now mostly in german, I think I'll translate it later)
https://github.com/johannes-becker-bt/dbt_workshop/

  • 👍1
codeforkjeff commented

Thank you for the feedback!

I know dbt-core 1.2.x has a concat macro but I don't know whether the macro from dbt_utils works for sqlite. I released 1.2.0a2 with the same fix in case you want to try that.

Since concat is a macro, you would need to do something like this, assuming the string to unique_key gets through jinja: {{ config(unique_key='{{ concat(field1,field2) }}' }}

johannes-becker-bt commented

Oh, I see where my mistake was.
it worked with dbt-core 1.2.0 because (as you said) concat is a part of it.
I thought I used dbt_utils concat - but I would have needed to write dbt_utils.concat for that... my dbt_utils got rusty.
Thanks for all the help and have a nice weekend!

  • 👍1