External materialized model with per_thread_output parameter does not work

[elephantmetropolis]

Hello, I am running into an issue when using the external materialization with per_thread_output option.

This option is supposed to create a number of files based on the number of threads used by DuckDB.

When this parameter is passed along with external materialization, I receive an error:

HTTP Error: Unable to connect to URL "https://my.s3/my_bucket/my_path": 404 (Not Found)

The funny thing is the files are actually written to S3 in the right place. And the current model is crashing, not the downstream model. For example, if I have model_b depending on model_a, the run is as follow.

11:04:12  1 of 2 START sql external model main.table_a ............... [RUN]
11:04:22  1 of 2 ERROR creating sql external model main.model_a ...... [ERROR in 9.70s]
11:04:22  2 of 2 SKIP relation main.model_b ................... [SKIP]

After some research I found that part of the code:

dbt-duckdb/dbt/adapters/duckdb/impl.py

Line 173 in 6b539a6

if rendered_options.get("partition_by"):

From what I understand, only models created with partition_by parameter can use file glob, and I suppose it's the problem here.

Is this assumption correct ? If yes, is it possible to add support for the parameter per_thread_output or is it better to manage this issue in a more "parameter agnostic" way ?

I'm not an expert but if I can contribute in any way I would appreciate.

Edit: I tried to modify impl.py and it seems this works on my side : #493

Edit 2: After more investigation, it seems the parameter per_thread_output is not working properly for external materializations. I tested to write to S3 using post-hook and macro and this code works:

{% macro export_to_s3(table) %}
{% set s3_path = 's3://my_bucket' %}
    COPY {{ table }} 
    TO '{{ s3_path }}/{{ table.name }}'
    (FORMAT PARQUET, PER_THREAD_OUTPUT TRUE);
{% endmacro %}

But using per_thread_output directly in {{ config() }} will end up with DuckDB writing one big file to S3.

For info, I set DuckDB to use 30 threads and 300GB ram.

However, the following code doesn't work properly, only one file is created, but, the file is created inside subfolder, which makes sense regarding expected PER_THREAD_OUTPUT behavior:

{{
    config(
        materialized='external',
        format='parquet'
        options={
            "per_thread_output": 1
        }
    )
}}
...

It seems there is two issues here, one being the per_thread_output not taken into account with file glob and the second is the per_thread_output parameter behaving weirdly (only using one thread ?).

[jwills]

@elephantmetropolis thanks for the report here, this is a fun one-- taking a look

[elephantmetropolis]

Quick update on this issue regarding external materialization using only one thread.

My first (DuckDB-uneducated) assumption was that, due to some DuckDB optimization, the query planner decided to use only one thread to perform the COPY operation.

I decided to test with a very simple model
I use (DuckDB) threads = 40, memory_limit = '300GB' and (DBT) threads = 1

{{ 
    config(
        materialized='external',
        format='parquet', 
        options={
            'per_thread_output': true
        }
    )
}}

select * 
from {{ ref('my_5GB_table') }} -- 'my_5GB_table' is materialized as a table

The behaviour is still the same. It writes 1 file that is 5GB big in S3 in 2 min 30 sec.

Now, if I connect to the DuckDB database (the same used by DBT) with ./duckdb and I run the following command (which is supposed to be the exact same coomand afaik):

copy (select * from my_5GB_table) to 's3://my_bucket/my_path' (format parquet, per_thread_output);

It writes 40 files to S3 in ~3 sec
When I check CPU usage with btop, I clearly see that the DBT run uses only 1 thread, whereas the manual run uses 40 threads.

I’ve checked everything I could in the implementation of the external materialization, but I cannot find why this behavior occurs.

[jwills]

Oh I've been there before-- the most likely explanation is that the Python version of DuckDB you're using with dbt isn't the same as the CLI version you're running locally here (i.e. the python package is on an older version of DuckDB that doesn't have this feature supported yet.)

The other possibility is that the version of dbt-duckdb you're using doesn't have the fix you included since I don't think we've cut a release since you merged it-- are you running off of your own build of dbt-duckdb, or did you do something like pip install --upgrade git+https://github.com/duckdb/dbt-duckdb.git@master to get the version with your fix?