Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[Bug]: incremental load creates empty insert statements #182

Open
1 task done
bmilby opened this issue May 26, 2023 · 3 comments
Open
1 task done

[Bug]: incremental load creates empty insert statements #182

bmilby opened this issue May 26, 2023 · 3 comments
Labels
bug Something isn't working

Comments

@bmilby
Copy link

bmilby commented May 26, 2023

Is there an existing issue for this?

  • I have searched the existing issues

Current Behavior

I am using dbt-dremio to transform my data. When I run the drpzn_raw_data.sql model as a view or table it runs successfully as expected. But, when I try to do an incremental load the model fails with the error ERROR: Failure parsing the query.
When I look at the log I see

 insert into "$scratch"."drpzn_raw_data"(  )
    select  from "$scratch"."drpzn_raw_data__dbt_tmp"


[0m17:56:09.937084 [debug] [Thread-1  ]: dremio adapter: Job State = METADATA_RETRIEVAL
[0m17:56:09.950874 [debug] [Thread-1  ]: dremio adapter: Job State = FAILED
[0m17:56:09.951998 [debug] [Thread-1  ]: dremio adapter: Error running SQL: /* {"app": "dbt", "dbt_version": "1.4.5", "profile_name": "des_dbt", "target_name": "dev", "node_id": "model.des_dbt.drpzn_raw_data"} */

    insert into "$scratch"."drpzn_raw_data"(  )
    select  from "$scratch"."drpzn_raw_data__dbt_tmp"

Expected Behavior

for the first run drpzn_raw_data table should be populated with a unique_id '7d0f9875a72e865dd1fd77f28eacd145'
before the second run, in drpzn_source_data model, I change the columns for the md5 hash to

{%- set metadata_cols = ["file_ingest_datetime", "file_type", "file_name", "file_size", "bb"] -%}

another record should be added to drpzn_raw_data table with a different unique_id '28ea59d370a3bf65b7b8698f59f4bdd0'

Steps To Reproduce

to reproduce run the following models.
drpzn_source_data.sql

    {% set drpzn_table_name = var('drpzn_table_name', 'edlhdata.daas.drpzn.file_agent."sample_json.json"') %}

    {#- /* define metadata columns */ -#}
    {%- set metadata_cols = ["file_ingest_datetime", "file_type", "file_name", "file_size"] -%}

    SELECT  
            md5( {{ dbt.concat(metadata_cols) }} ) AS unique_id,   
            *,
            {{ dbt_date.now("America/New_York") }} AS ingest_datetime
    FROM {{ drpzn_table_name }}

drpzn_raw_data.sql

{{-
        config(
            materialized='incremental',
            unique_key='unique_id',
            incremental_strategy='append',           
        )
    -}}     
    {%- set target_table_name = var('target_table_name', 'target')  -%}

    {#- /* get all columns from source data  */ -#}
    {%- set all_cols = adapter.get_columns_in_relation(ref("drpzn_source_data")) -%}

    {#- /* define metadata columns */ -#}
    {%- set metadata_cols = ["file_ingest_datetime", "file_type", "file_name", "file_size"] -%}

    {#- /* get all columns except for metadata columns */-#}
    {%- set final_cols = [] -%}
    {%- for col in all_cols if col.name not in metadata_cols %}
        {{- final_cols.append(col.name) or "" -}}
    {%- endfor %}

    SELECT                     
            {%- for col in final_cols %}
            {{ col }}{{ "," if not loop.last }}      
            {%- endfor %}
    FROM {{ ref("drpzn_source_data") }}             
    {%- if is_incremental() %}
    -- this filter will only be applied on an incremental run
    where ingest_datetime > (select max(ingest_datetime) from {{ this }})
    {%- endif %}

Environment

- OS: Amazon EC2 (Ubuntu)
- dbt-dremio: 1.4.5
- Dremio Software: 
- Dremio Cloud: 24.0.0-202302100528110223-3a169b7c

Relevant log output

============================== 2023-05-26 17:56:05.769949 | 49880c59-07a3-4b53-8cdd-a2571ce4adf0 ==============================
[0m17:56:05.769949 [info ] [MainThread]: Running with dbt=1.4.5
[0m17:56:05.773931 [debug] [MainThread]: running dbt with arguments {'write_json': True, 'use_colors': True, 'printer_width': 80, 'version_check': True, 'partial_parse': True, 'static_parser': True, 'profiles_dir': '/app/daasrepo/des_drpzn_processor/des_dbt', 'send_anonymous_usage_stats': True, 'quiet': False, 'no_print': False, 'cache_selected_only': False, 'which': 'run', 'rpc_method': 'run', 'indirect_selection': 'eager'}
[0m17:56:05.774599 [debug] [MainThread]: Tracking: tracking
[0m17:56:05.781877 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'start', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fceb276b040>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fceb276bfa0>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fceb276b220>]}
[0m17:56:05.843546 [debug] [MainThread]: checksum: e05dd7cee44d39ae8ac27965cacd8a6d8d0ab4e8185101e0db84e98f79bee0b6, vars: {}, profile: None, target: None, version: 1.4.5
[0m17:56:05.923182 [debug] [MainThread]: Partial parsing enabled: 0 files deleted, 0 files added, 0 files changed.
[0m17:56:05.923633 [debug] [MainThread]: Partial parsing enabled, no changes found, skipping parsing
[0m17:56:05.936674 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'load_project', 'label': '49880c59-07a3-4b53-8cdd-a2571ce4adf0', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fceb24b4eb0>]}
[0m17:56:05.953787 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'resource_counts', 'label': '49880c59-07a3-4b53-8cdd-a2571ce4adf0', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fceb25a8a30>]}
[0m17:56:05.954432 [info ] [MainThread]: Found 2 models, 0 tests, 0 snapshots, 0 analyses, 549 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
[0m17:56:05.954957 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'runnable_timing', 'label': '49880c59-07a3-4b53-8cdd-a2571ce4adf0', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fceb2e3cb50>]}
[0m17:56:05.956982 [info ] [MainThread]: 
[0m17:56:05.960353 [debug] [MainThread]: Acquiring new dremio connection 'master'
[0m17:56:05.962409 [debug] [ThreadPool]: Acquiring new dremio connection 'list_@aver_dremio'
[0m17:56:05.990173 [debug] [ThreadPool]: dremio adapter: Using dremio connection "list_@aver_dremio"
[0m17:56:05.990713 [debug] [ThreadPool]: dremio adapter: On list_@aver_dremio: /* {"app": "dbt", "dbt_version": "1.4.5", "profile_name": "des_dbt", "target_name": "dev", "connection_name": "list_@aver_dremio"} */

    
    select substring(schema_name, position('.' in schema_name) + 1)
    from information_schema.schemata
    where ilike(schema_name, '@aver_dremio.%')
    union
    values('no_schema')
  
  
[0m17:56:05.991245 [debug] [ThreadPool]: Opening a new connection, currently in state init
[0m17:56:06.110102 [debug] [ThreadPool]: dremio adapter: Connected to db: @aver_dremio
[0m17:56:06.157428 [debug] [ThreadPool]: dremio adapter: Job State = METADATA_RETRIEVAL
[0m17:56:06.172563 [debug] [ThreadPool]: dremio adapter: Job State = PLANNING
[0m17:56:06.187377 [debug] [ThreadPool]: dremio adapter: Job State = QUEUED
[0m17:56:06.195567 [debug] [ThreadPool]: dremio adapter: Job State = STARTING
[0m17:56:06.203069 [debug] [ThreadPool]: dremio adapter: Job State = RUNNING
[0m17:56:06.263991 [debug] [ThreadPool]: dremio adapter: Job State = COMPLETED
[0m17:56:06.287354 [debug] [ThreadPool]: dremio adapter: SQL status: 1 in 0.30 seconds
[0m17:56:06.288073 [debug] [ThreadPool]: On list_@aver_dremio: Close
[0m17:56:06.289442 [debug] [ThreadPool]: Acquiring new dremio connection 'list_$scratch'
[0m17:56:06.294001 [debug] [ThreadPool]: dremio adapter: Using dremio connection "list_$scratch"
[0m17:56:06.294689 [debug] [ThreadPool]: dremio adapter: On list_$scratch: /* {"app": "dbt", "dbt_version": "1.4.5", "profile_name": "des_dbt", "target_name": "dev", "connection_name": "list_$scratch"} */

    
    select substring(schema_name, position('.' in schema_name) + 1)
    from information_schema.schemata
    where ilike(schema_name, '$scratch.%')
    union
    values('no_schema')
  
  
[0m17:56:06.295251 [debug] [ThreadPool]: Opening a new connection, currently in state closed
[0m17:56:06.408498 [debug] [ThreadPool]: dremio adapter: Connected to db: @aver_dremio
[0m17:56:06.442668 [debug] [ThreadPool]: dremio adapter: Job State = PLANNING
[0m17:56:06.466559 [debug] [ThreadPool]: dremio adapter: Job State = QUEUED
[0m17:56:06.489611 [debug] [ThreadPool]: dremio adapter: Job State = EXECUTION_PLANNING
[0m17:56:06.497334 [debug] [ThreadPool]: dremio adapter: Job State = RUNNING
[0m17:56:06.560458 [debug] [ThreadPool]: dremio adapter: Job State = COMPLETED
[0m17:56:06.580321 [debug] [ThreadPool]: dremio adapter: SQL status: 2 in 0.29 seconds
[0m17:56:06.581436 [debug] [ThreadPool]: On list_$scratch: Close
[0m17:56:06.584820 [debug] [ThreadPool]: Acquiring new dremio connection 'list_@aver_dremio_no_schema'
[0m17:56:06.606020 [debug] [ThreadPool]: dremio adapter: Using dremio connection "list_@aver_dremio_no_schema"
[0m17:56:06.606732 [debug] [ThreadPool]: dremio adapter: On list_@aver_dremio_no_schema: /* {"app": "dbt", "dbt_version": "1.4.5", "profile_name": "des_dbt", "target_name": "dev", "connection_name": "list_@aver_dremio_no_schema"} */


      select (case when position('.' in table_schema) > 0
              then substring(table_schema, 1, position('.' in table_schema) - 1)
              else table_schema
          end) as table_catalog
          ,table_name
          ,(case when position('.' in table_schema) > 0
              then substring(table_schema, position('.' in table_schema) + 1)
              else 'no_schema'
          end) as table_schema
          ,lower(table_type) as table_type
      from information_schema."tables"
        where ilike(table_schema, '@aver_dremio')
      and table_type <> 'system_table'

  
[0m17:56:06.607328 [debug] [ThreadPool]: Opening a new connection, currently in state closed
[0m17:56:06.735855 [debug] [ThreadPool]: dremio adapter: Connected to db: @aver_dremio
[0m17:56:06.755937 [debug] [ThreadPool]: dremio adapter: Job State = METADATA_RETRIEVAL
[0m17:56:06.764599 [debug] [ThreadPool]: dremio adapter: Job State = PLANNING
[0m17:56:06.785667 [debug] [ThreadPool]: dremio adapter: Job State = QUEUED
[0m17:56:06.794276 [debug] [ThreadPool]: dremio adapter: Job State = RUNNING
[0m17:56:06.856596 [debug] [ThreadPool]: dremio adapter: Job State = COMPLETED
[0m17:56:06.878103 [debug] [ThreadPool]: dremio adapter: SQL status: 2 in 0.27 seconds
[0m17:56:06.879234 [debug] [ThreadPool]: On list_@aver_dremio_no_schema: ROLLBACK
[0m17:56:06.879841 [debug] [ThreadPool]: dremio adapter: Handle rollback not implemented.
[0m17:56:06.880334 [debug] [ThreadPool]: On list_@aver_dremio_no_schema: Close
[0m17:56:06.881894 [debug] [ThreadPool]: Acquiring new dremio connection 'list_$scratch_no_schema'
[0m17:56:06.888709 [debug] [ThreadPool]: dremio adapter: Using dremio connection "list_$scratch_no_schema"
[0m17:56:06.889296 [debug] [ThreadPool]: dremio adapter: On list_$scratch_no_schema: /* {"app": "dbt", "dbt_version": "1.4.5", "profile_name": "des_dbt", "target_name": "dev", "connection_name": "list_$scratch_no_schema"} */


      select (case when position('.' in table_schema) > 0
              then substring(table_schema, 1, position('.' in table_schema) - 1)
              else table_schema
          end) as table_catalog
          ,table_name
          ,(case when position('.' in table_schema) > 0
              then substring(table_schema, position('.' in table_schema) + 1)
              else 'no_schema'
          end) as table_schema
          ,lower(table_type) as table_type
      from information_schema."tables"
        where ilike(table_schema, '$scratch')
      and table_type <> 'system_table'

  
[0m17:56:06.889886 [debug] [ThreadPool]: Opening a new connection, currently in state closed
[0m17:56:07.000714 [debug] [ThreadPool]: dremio adapter: Connected to db: @aver_dremio
[0m17:56:07.031312 [debug] [ThreadPool]: dremio adapter: Job State = PLANNING
[0m17:56:07.066192 [debug] [ThreadPool]: dremio adapter: Job State = EXECUTION_PLANNING
[0m17:56:07.074615 [debug] [ThreadPool]: dremio adapter: Job State = STARTING
[0m17:56:07.083993 [debug] [ThreadPool]: dremio adapter: Job State = RUNNING
[0m17:56:07.149354 [debug] [ThreadPool]: dremio adapter: Job State = COMPLETED
[0m17:56:07.173559 [debug] [ThreadPool]: dremio adapter: SQL status: 3 in 0.28 seconds
[0m17:56:07.174852 [debug] [ThreadPool]: On list_$scratch_no_schema: ROLLBACK
[0m17:56:07.175764 [debug] [ThreadPool]: dremio adapter: Handle rollback not implemented.
[0m17:56:07.176598 [debug] [ThreadPool]: On list_$scratch_no_schema: Close
[0m17:56:07.178891 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'runnable_timing', 'label': '49880c59-07a3-4b53-8cdd-a2571ce4adf0', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fceb27d4b50>]}
[0m17:56:07.180320 [info ] [MainThread]: Concurrency: 1 threads (target='dev')
[0m17:56:07.180881 [info ] [MainThread]: 
[0m17:56:07.186660 [debug] [Thread-1  ]: Began running node model.des_dbt.drpzn_source_data
[0m17:56:07.187524 [info ] [Thread-1  ]: 1 of 2 START sql view model drpzn_source_data .................................. [RUN]
[0m17:56:07.189264 [debug] [Thread-1  ]: Acquiring new dremio connection 'model.des_dbt.drpzn_source_data'
[0m17:56:07.189829 [debug] [Thread-1  ]: Began compiling node model.des_dbt.drpzn_source_data
[0m17:56:07.225375 [debug] [Thread-1  ]: Writing injected SQL for node "model.des_dbt.drpzn_source_data"
[0m17:56:07.226276 [debug] [Thread-1  ]: Timing info for model.des_dbt.drpzn_source_data (compile): 2023-05-26 17:56:07.190158 => 2023-05-26 17:56:07.226148
[0m17:56:07.226755 [debug] [Thread-1  ]: Began executing node model.des_dbt.drpzn_source_data
[0m17:56:07.292290 [debug] [Thread-1  ]: Writing runtime sql for node "model.des_dbt.drpzn_source_data"
[0m17:56:07.293187 [debug] [Thread-1  ]: dremio adapter: Using dremio connection "model.des_dbt.drpzn_source_data"
[0m17:56:07.293633 [debug] [Thread-1  ]: dremio adapter: On model.des_dbt.drpzn_source_data: /* {"app": "dbt", "dbt_version": "1.4.5", "profile_name": "des_dbt", "target_name": "dev", "node_id": "model.des_dbt.drpzn_source_data"} */


  create or replace view "@aver_dremio"."drpzn_source_data" as (
    SELECT  
            md5( file_ingest_datetime || file_type || file_name || file_size ) AS unique_id,   
            *,
            convert_timezone('UTC', 'America/New_York',
    cast((SELECT CURRENT_TIMESTAMP()) as TIMESTAMP)
) AS ingest_datetime
    FROM edlhdata.daas.drpzn.file_agent."sample_json.json"
  )
[0m17:56:07.294011 [debug] [Thread-1  ]: Opening a new connection, currently in state closed
[0m17:56:07.416204 [debug] [Thread-1  ]: dremio adapter: Connected to db: @aver_dremio
[0m17:56:07.435328 [debug] [Thread-1  ]: dremio adapter: Job State = METADATA_RETRIEVAL
[0m17:56:07.442283 [debug] [Thread-1  ]: dremio adapter: Job State = PLANNING
[0m17:56:07.517177 [debug] [Thread-1  ]: dremio adapter: Job State = RUNNING
[0m17:56:07.526668 [debug] [Thread-1  ]: dremio adapter: Job State = COMPLETED
[0m17:56:07.527374 [debug] [Thread-1  ]: dremio adapter: rowCount does not exist in job_status payload
[0m17:56:07.544548 [debug] [Thread-1  ]: dremio adapter: SQL status: OK in 0.25 seconds
[0m17:56:07.592584 [debug] [Thread-1  ]: dremio adapter: Using dremio connection "model.des_dbt.drpzn_source_data"
[0m17:56:07.593282 [debug] [Thread-1  ]: dremio adapter: On model.des_dbt.drpzn_source_data: /* {"app": "dbt", "dbt_version": "1.4.5", "profile_name": "des_dbt", "target_name": "dev", "node_id": "model.des_dbt.drpzn_source_data"} */
drop table if exists "$scratch"."drpzn_source_data"
[0m17:56:07.612607 [debug] [Thread-1  ]: dremio adapter: Job State = METADATA_RETRIEVAL
[0m17:56:07.646731 [debug] [Thread-1  ]: dremio adapter: Job State = COMPLETED
[0m17:56:07.647316 [debug] [Thread-1  ]: dremio adapter: rowCount does not exist in job_status payload
[0m17:56:07.668856 [debug] [Thread-1  ]: dremio adapter: SQL status: OK in 0.07 seconds
[0m17:56:07.695106 [debug] [Thread-1  ]: Timing info for model.des_dbt.drpzn_source_data (execute): 2023-05-26 17:56:07.227051 => 2023-05-26 17:56:07.694974
[0m17:56:07.696120 [debug] [Thread-1  ]: On model.des_dbt.drpzn_source_data: ROLLBACK
[0m17:56:07.696672 [debug] [Thread-1  ]: dremio adapter: Handle rollback not implemented.
[0m17:56:07.697161 [debug] [Thread-1  ]: On model.des_dbt.drpzn_source_data: Close
[0m17:56:07.698505 [debug] [Thread-1  ]: Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '49880c59-07a3-4b53-8cdd-a2571ce4adf0', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fceb27d4460>]}
[0m17:56:07.699363 [info ] [Thread-1  ]: 1 of 2 OK created sql view model drpzn_source_data ............................. [[32mOK[0m in 0.51s]
[0m17:56:07.702110 [debug] [Thread-1  ]: Finished running node model.des_dbt.drpzn_source_data
[0m17:56:07.703462 [debug] [Thread-1  ]: Began running node model.des_dbt.drpzn_raw_data
[0m17:56:07.704108 [info ] [Thread-1  ]: 2 of 2 START sql incremental model $scratch.drpzn_raw_data ..................... [RUN]
[0m17:56:07.705274 [debug] [Thread-1  ]: Acquiring new dremio connection 'model.des_dbt.drpzn_raw_data'
[0m17:56:07.705938 [debug] [Thread-1  ]: Began compiling node model.des_dbt.drpzn_raw_data
[0m17:56:07.748753 [debug] [Thread-1  ]: dremio adapter: Using dremio connection "model.des_dbt.drpzn_raw_data"
[0m17:56:07.749241 [debug] [Thread-1  ]: dremio adapter: On model.des_dbt.drpzn_raw_data: /* {"app": "dbt", "dbt_version": "1.4.5", "profile_name": "des_dbt", "target_name": "dev", "node_id": "model.des_dbt.drpzn_raw_data"} */

    select column_name as column_name
        ,lower(data_type) as data_type
        ,character_maximum_length
        ,numeric_precision
        ,numeric_scale
    from information_schema.columns
    where ilike(table_schema, '@aver_dremio')
    and ilike(table_name, 'drpzn_source_data')
    order by ordinal_position
  
[0m17:56:07.749668 [debug] [Thread-1  ]: Opening a new connection, currently in state closed
[0m17:56:07.867597 [debug] [Thread-1  ]: dremio adapter: Connected to db: @aver_dremio
[0m17:56:07.887645 [debug] [Thread-1  ]: dremio adapter: Job State = PLANNING
[0m17:56:07.906385 [debug] [Thread-1  ]: dremio adapter: Job State = QUEUED
[0m17:56:07.919810 [debug] [Thread-1  ]: dremio adapter: Job State = EXECUTION_PLANNING
[0m17:56:07.930931 [debug] [Thread-1  ]: dremio adapter: Job State = RUNNING
[0m17:56:08.014284 [debug] [Thread-1  ]: dremio adapter: Job State = COMPLETED
[0m17:56:08.029145 [debug] [Thread-1  ]: dremio adapter: SQL status: 12 in 0.28 seconds
[0m17:56:08.047499 [debug] [Thread-1  ]: Writing injected SQL for node "model.des_dbt.drpzn_raw_data"
[0m17:56:08.048706 [debug] [Thread-1  ]: Timing info for model.des_dbt.drpzn_raw_data (compile): 2023-05-26 17:56:07.706557 => 2023-05-26 17:56:08.048601
[0m17:56:08.049198 [debug] [Thread-1  ]: Began executing node model.des_dbt.drpzn_raw_data
[0m17:56:08.109924 [debug] [Thread-1  ]: dremio adapter: Using dremio connection "model.des_dbt.drpzn_raw_data"
[0m17:56:08.110534 [debug] [Thread-1  ]: dremio adapter: On model.des_dbt.drpzn_raw_data: /* {"app": "dbt", "dbt_version": "1.4.5", "profile_name": "des_dbt", "target_name": "dev", "node_id": "model.des_dbt.drpzn_raw_data"} */
drop table if exists "$scratch"."drpzn_raw_data__dbt_tmp"
[0m17:56:08.130683 [debug] [Thread-1  ]: dremio adapter: Job State = METADATA_RETRIEVAL
[0m17:56:08.138122 [debug] [Thread-1  ]: dremio adapter: Job State = COMPLETED
[0m17:56:08.138590 [debug] [Thread-1  ]: dremio adapter: rowCount does not exist in job_status payload
[0m17:56:08.155440 [debug] [Thread-1  ]: dremio adapter: SQL status: OK in 0.04 seconds
[0m17:56:08.183434 [debug] [Thread-1  ]: Invalid value for on_schema_change () specified. Setting default value of ignore.
[0m17:56:08.246330 [debug] [Thread-1  ]: dremio adapter: Using dremio connection "model.des_dbt.drpzn_raw_data"
[0m17:56:08.246983 [debug] [Thread-1  ]: dremio adapter: On model.des_dbt.drpzn_raw_data: /* {"app": "dbt", "dbt_version": "1.4.5", "profile_name": "des_dbt", "target_name": "dev", "node_id": "model.des_dbt.drpzn_raw_data"} */

    
  
    

  create table "$scratch"."drpzn_raw_data__dbt_tmp"
   
  
  
  
  
  as (
    

    SELECT
            unique_id,
            aa,
            bb,
            source,
            email,
            test,
            movies,
            ingest_datetime
    FROM "@aver_dremio"."drpzn_source_data"
    -- this filter will only be applied on an incremental run
    where ingest_datetime > (select max(ingest_datetime) from "$scratch"."drpzn_raw_data")
  )
  
  
[0m17:56:08.263539 [debug] [Thread-1  ]: dremio adapter: Job State = METADATA_RETRIEVAL
[0m17:56:08.279401 [debug] [Thread-1  ]: dremio adapter: Job State = PLANNING
[0m17:56:08.399220 [debug] [Thread-1  ]: dremio adapter: Job State = QUEUED
[0m17:56:08.408752 [debug] [Thread-1  ]: dremio adapter: Job State = EXECUTION_PLANNING
[0m17:56:08.429882 [debug] [Thread-1  ]: dremio adapter: Job State = RUNNING
[0m17:56:09.100354 [debug] [Thread-1  ]: dremio adapter: Job State = COMPLETED
[0m17:56:09.118073 [debug] [Thread-1  ]: dremio adapter: SQL status: 1 in 0.87 seconds
[0m17:56:09.124350 [debug] [Thread-1  ]: dremio adapter: Using dremio connection "model.des_dbt.drpzn_raw_data"
[0m17:56:09.125222 [debug] [Thread-1  ]: dremio adapter: On model.des_dbt.drpzn_raw_data: /* {"app": "dbt", "dbt_version": "1.4.5", "profile_name": "des_dbt", "target_name": "dev", "node_id": "model.des_dbt.drpzn_raw_data"} */

    select column_name as column_name
        ,lower(data_type) as data_type
        ,character_maximum_length
        ,numeric_precision
        ,numeric_scale
    from information_schema.columns
    where ilike(table_schema, '$scratch')
    and ilike(table_name, 'drpzn_raw_data__dbt_tmp')
    order by ordinal_position
  
[0m17:56:09.143890 [debug] [Thread-1  ]: dremio adapter: Job State = METADATA_RETRIEVAL
[0m17:56:09.151061 [debug] [Thread-1  ]: dremio adapter: Job State = PLANNING
[0m17:56:09.164520 [debug] [Thread-1  ]: dremio adapter: Job State = QUEUED
[0m17:56:09.172069 [debug] [Thread-1  ]: dremio adapter: Job State = EXECUTION_PLANNING
[0m17:56:09.181837 [debug] [Thread-1  ]: dremio adapter: Job State = STARTING
[0m17:56:09.191377 [debug] [Thread-1  ]: dremio adapter: Job State = RUNNING
[0m17:56:09.224981 [debug] [Thread-1  ]: dremio adapter: Job State = COMPLETED
[0m17:56:09.238481 [debug] [Thread-1  ]: dremio adapter: SQL status: 0 in 0.11 seconds
[0m17:56:09.243337 [debug] [Thread-1  ]: dremio adapter: Using dremio connection "model.des_dbt.drpzn_raw_data"
[0m17:56:09.243790 [debug] [Thread-1  ]: dremio adapter: On model.des_dbt.drpzn_raw_data: /* {"app": "dbt", "dbt_version": "1.4.5", "profile_name": "des_dbt", "target_name": "dev", "node_id": "model.des_dbt.drpzn_raw_data"} */

    select column_name as column_name
        ,lower(data_type) as data_type
        ,character_maximum_length
        ,numeric_precision
        ,numeric_scale
    from information_schema.columns
    where ilike(table_schema, '$scratch')
    and ilike(table_name, 'drpzn_raw_data')
    order by ordinal_position
  
[0m17:56:09.273133 [debug] [Thread-1  ]: dremio adapter: Job State = METADATA_RETRIEVAL
[0m17:56:09.282040 [debug] [Thread-1  ]: dremio adapter: Job State = PLANNING
[0m17:56:09.289712 [debug] [Thread-1  ]: dremio adapter: Job State = QUEUED
[0m17:56:09.307244 [debug] [Thread-1  ]: dremio adapter: Job State = RUNNING
[0m17:56:09.403308 [debug] [Thread-1  ]: dremio adapter: Job State = COMPLETED
[0m17:56:09.417186 [debug] [Thread-1  ]: dremio adapter: SQL status: 8 in 0.17 seconds
[0m17:56:09.432116 [debug] [Thread-1  ]: dremio adapter: Using dremio connection "model.des_dbt.drpzn_raw_data"
[0m17:56:09.432620 [debug] [Thread-1  ]: dremio adapter: On model.des_dbt.drpzn_raw_data: /* {"app": "dbt", "dbt_version": "1.4.5", "profile_name": "des_dbt", "target_name": "dev", "node_id": "model.des_dbt.drpzn_raw_data"} */

    select column_name as column_name
        ,lower(data_type) as data_type
        ,character_maximum_length
        ,numeric_precision
        ,numeric_scale
    from information_schema.columns
    where ilike(table_schema, '$scratch')
    and ilike(table_name, 'drpzn_raw_data')
    order by ordinal_position
  
[0m17:56:09.453883 [debug] [Thread-1  ]: dremio adapter: Job State = PLANNING
[0m17:56:09.476542 [debug] [Thread-1  ]: dremio adapter: Job State = QUEUED
[0m17:56:09.484240 [debug] [Thread-1  ]: dremio adapter: Job State = EXECUTION_PLANNING
[0m17:56:09.493916 [debug] [Thread-1  ]: dremio adapter: Job State = RUNNING
[0m17:56:09.551342 [debug] [Thread-1  ]: dremio adapter: Job State = COMPLETED
[0m17:56:09.570399 [debug] [Thread-1  ]: dremio adapter: SQL status: 8 in 0.14 seconds
[0m17:56:09.608712 [debug] [Thread-1  ]: dremio adapter: Using dremio connection "model.des_dbt.drpzn_raw_data"
[0m17:56:09.609413 [debug] [Thread-1  ]: dremio adapter: On model.des_dbt.drpzn_raw_data: /* {"app": "dbt", "dbt_version": "1.4.5", "profile_name": "des_dbt", "target_name": "dev", "node_id": "model.des_dbt.drpzn_raw_data"} */

    select column_name as column_name
        ,lower(data_type) as data_type
        ,character_maximum_length
        ,numeric_precision
        ,numeric_scale
    from information_schema.columns
    where ilike(table_schema, '$scratch')
    and ilike(table_name, 'drpzn_raw_data')
    order by ordinal_position
  
[0m17:56:09.637841 [debug] [Thread-1  ]: dremio adapter: Job State = METADATA_RETRIEVAL
[0m17:56:09.645751 [debug] [Thread-1  ]: dremio adapter: Job State = PLANNING
[0m17:56:09.654329 [debug] [Thread-1  ]: dremio adapter: Job State = QUEUED
[0m17:56:09.664709 [debug] [Thread-1  ]: dremio adapter: Job State = STARTING
[0m17:56:09.673138 [debug] [Thread-1  ]: dremio adapter: Job State = RUNNING
[0m17:56:09.745940 [debug] [Thread-1  ]: dremio adapter: Job State = COMPLETED
[0m17:56:09.762300 [debug] [Thread-1  ]: dremio adapter: SQL status: 8 in 0.15 seconds
[0m17:56:09.772031 [debug] [Thread-1  ]: dremio adapter: Using dremio connection "model.des_dbt.drpzn_raw_data"
[0m17:56:09.773136 [debug] [Thread-1  ]: dremio adapter: On model.des_dbt.drpzn_raw_data: /* {"app": "dbt", "dbt_version": "1.4.5", "profile_name": "des_dbt", "target_name": "dev", "node_id": "model.des_dbt.drpzn_raw_data"} */

    select column_name as column_name
        ,lower(data_type) as data_type
        ,character_maximum_length
        ,numeric_precision
        ,numeric_scale
    from information_schema.columns
    where ilike(table_schema, '$scratch')
    and ilike(table_name, 'drpzn_raw_data__dbt_tmp')
    order by ordinal_position
  
[0m17:56:09.800957 [debug] [Thread-1  ]: dremio adapter: Job State = PLANNING
[0m17:56:09.818344 [debug] [Thread-1  ]: dremio adapter: Job State = QUEUED
[0m17:56:09.827308 [debug] [Thread-1  ]: dremio adapter: Job State = EXECUTION_PLANNING
[0m17:56:09.842196 [debug] [Thread-1  ]: dremio adapter: Job State = RUNNING
[0m17:56:09.887477 [debug] [Thread-1  ]: dremio adapter: Job State = COMPLETED
[0m17:56:09.902778 [debug] [Thread-1  ]: dremio adapter: SQL status: 0 in 0.13 seconds
[0m17:56:09.913732 [debug] [Thread-1  ]: Writing runtime sql for node "model.des_dbt.drpzn_raw_data"
[0m17:56:09.914876 [debug] [Thread-1  ]: dremio adapter: Using dremio connection "model.des_dbt.drpzn_raw_data"
[0m17:56:09.915466 [debug] [Thread-1  ]: dremio adapter: On model.des_dbt.drpzn_raw_data: /* {"app": "dbt", "dbt_version": "1.4.5", "profile_name": "des_dbt", "target_name": "dev", "node_id": "model.des_dbt.drpzn_raw_data"} */

    insert into "$scratch"."drpzn_raw_data"(  )
    select  from "$scratch"."drpzn_raw_data__dbt_tmp"


[0m17:56:09.937084 [debug] [Thread-1  ]: dremio adapter: Job State = METADATA_RETRIEVAL
[0m17:56:09.950874 [debug] [Thread-1  ]: dremio adapter: Job State = FAILED
[0m17:56:09.951998 [debug] [Thread-1  ]: dremio adapter: Error running SQL: /* {"app": "dbt", "dbt_version": "1.4.5", "profile_name": "des_dbt", "target_name": "dev", "node_id": "model.des_dbt.drpzn_raw_data"} */

    insert into "$scratch"."drpzn_raw_data"(  )
    select  from "$scratch"."drpzn_raw_data__dbt_tmp"


[0m17:56:09.952796 [debug] [Thread-1  ]: On model.des_dbt.drpzn_raw_data: ROLLBACK
[0m17:56:09.953144 [debug] [Thread-1  ]: dremio adapter: Handle rollback not implemented.
[0m17:56:09.953515 [debug] [Thread-1  ]: On model.des_dbt.drpzn_raw_data: Close
[0m17:56:09.954191 [debug] [Thread-1  ]: Timing info for model.des_dbt.drpzn_raw_data (execute): 2023-05-26 17:56:08.049550 => 2023-05-26 17:56:09.954062
[0m17:56:09.959061 [debug] [Thread-1  ]: Runtime Error in model drpzn_raw_data (models/raw_data/drpzn_raw_data.sql)
  ERROR: Failure parsing the query.
[0m17:56:09.960019 [debug] [Thread-1  ]: Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '49880c59-07a3-4b53-8cdd-a2571ce4adf0', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fceb248eee0>]}
[0m17:56:09.961253 [error] [Thread-1  ]: 2 of 2 ERROR creating sql incremental model $scratch.drpzn_raw_data ............ [[31mERROR[0m in 2.26s]
[0m17:56:09.962883 [debug] [Thread-1  ]: Finished running node model.des_dbt.drpzn_raw_data
[0m17:56:09.965885 [debug] [MainThread]: Acquiring new dremio connection 'master'
[0m17:56:09.966553 [debug] [MainThread]: On master: ROLLBACK
[0m17:56:09.967057 [debug] [MainThread]: Opening a new connection, currently in state init
[0m17:56:10.086660 [debug] [MainThread]: dremio adapter: Connected to db: @aver_dremio
[0m17:56:10.087312 [debug] [MainThread]: dremio adapter: Handle rollback not implemented.
[0m17:56:10.087771 [debug] [MainThread]: On master: ROLLBACK
[0m17:56:10.088178 [debug] [MainThread]: dremio adapter: Handle rollback not implemented.
[0m17:56:10.088583 [debug] [MainThread]: On master: Close
[0m17:56:10.089684 [debug] [MainThread]: Connection 'master' was properly closed.
[0m17:56:10.090052 [debug] [MainThread]: Connection 'model.des_dbt.drpzn_raw_data' was properly closed.
[0m17:56:10.090499 [info ] [MainThread]: 
[0m17:56:10.091089 [info ] [MainThread]: Finished running 1 view model, 1 incremental model in 0 hours 0 minutes and 4.13 seconds (4.13s).
[0m17:56:10.092012 [debug] [MainThread]: Command end result
[0m17:56:10.108166 [info ] [MainThread]: 
[0m17:56:10.108924 [info ] [MainThread]: [31mCompleted with 1 error and 0 warnings:[0m
[0m17:56:10.109508 [info ] [MainThread]: 
[0m17:56:10.110189 [error] [MainThread]: [33mRuntime Error in model drpzn_raw_data (models/raw_data/drpzn_raw_data.sql)[0m
[0m17:56:10.110900 [error] [MainThread]:   ERROR: Failure parsing the query.
[0m17:56:10.111523 [info ] [MainThread]: 
[0m17:56:10.112151 [info ] [MainThread]: Done. PASS=1 WARN=0 ERROR=1 SKIP=0 TOTAL=2
[0m17:56:10.112911 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fceb0358820>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fceb0358760>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fceb03587c0>]}
[0m17:56:10.113411 [debug] [MainThread]: Flushing usage events
@bmilby bmilby added the bug Something isn't working label May 26, 2023
@ArgusLi
Copy link
Contributor

ArgusLi commented May 29, 2023

Hi @bmilby!

Thanks for bringing this to our attention, we will be looking into this issue. Our current priority is to update the connector to support dbt v1.5, and we will see if this bug is still reproducible once the connector has been upgraded.

@raghuj5222
Copy link

Any resolution on this issue ?? I am encountering the same error on the incremental

@mxmarg
Copy link
Contributor

mxmarg commented Jan 17, 2024

Hi @raghuj5222,
I hit the same issue with the empty insert statement and I believe that it occurs when trying to insert incremental materializations into tables that are located in Dremio's $scratch directory. My model ran without issue after I changed the object store to an ADLS source in the dbt_project.yml:

      +object_storage_path: <object_storage_path>
      +object_storage_source: <object_storage_source>

Hope this helps.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working
Milestone
No milestone
Development

No branches or pull requests
4 participants
@raghuj5222 @ArgusLi @bmilby @mxmarg