"Too many queries or query is too complex" issue

[jmainwaring]

Hello! As seen in this thread, many of us continue to face the following error when run metadata is added in BigQuery:

Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex.

I see in the thread that the issue appears to have been resolved, but I and others later in the thread are still encountering this error after upgrading to version 0.16.0. This and this are two other examples of users who appear to be running into the same thing. If you all have any suggestions for config changes we need to make or would be up for discussing a potential fix, that would be greatly appreciated, as there are some wonderful features in the new version that we hope to not miss out on. Thank you!

Environment:
Elementary dbt package version: 1.6.0
dbt version you're using: 1.7.4
Data warehouse: BigQuery

[cwmuller]

I'm also experiencing this on 0.16.0, dbt-bigquery 1.8.2. I tried setting the the query_max_size and insert_rows_method parameters, as my impression was that this was an attempt make changes that would alleviate this issue, but I am still getting the same error messages as before.

[belle-crisp]

Same for me!

[cwmuller]

Uploaded log file: dbt.log

[haritamar]

Hi @jmainwaring and all,

Apologies for the delayed response here. This configuration (that was mentioned in on of the threads) should probably solve this issue:

insert_rows_method: chunk
dbt_artifacts_chunk_size: 500

Some extended context about this problem - BQ has two types of limits that affect insert queries:

1. A limit on the number of rows inserted - this causes the error mentioned above.
2. A limit on the length of the entire query.

The chunk insert method actually handles both types of limits today, and we'll make it the default in the upcoming package release.

Thanks,
Itamar

[cwmuller]

Hi @haritamar ,

The log I posted above is the output from elementary actively failing with the configuration you are mentioning. Unless I have incorrectly set these parameters, they have failed to fix this issue.

[Maayan-s]

@cwmuller @belle-crisp
@nickozilla, @benoitgoujon, @wei-wei29, @chenxilll, @kylemcleland, @neethu2409

Let us know if the config @haritamar suggested doesn't solve this 🙏🏻

[belle-crisp]

I have the same experience as @cwmuller

[haritamar]

Hi @cwmuller @belle-crisp ,
Sorry about that.

@cwmuller another setting that might help is to tweak the query_max_size parameter, not sure if you did that as well. We saw in the log that the failing query has around 240K characters.
Have you tried setting query_max_size: 100000?

(I saw it was suggested in one of the slack thread so not sure if you tried that as well)

Thanks,
Itamar

[haritamar]

Also if you can share the full list of vars you set up would be great 🙏

[cwmuller]

Uploaded a new log running with the following profiles config:

  elementary:
      insert_rows_method: "chunk"
      dbt_artifacts_chunk_size: 500
      query_max_size: 1000000

Will get back to you on vars.

dbt.log

[haritamar]

Hey @cwmuller ,
The configuration above is what I meant. But interesting - I think it is actually configured in the wrong place...
These should be set as vars in dbt_project.yml, and not under the profiles.

e.g.:

vars:
  insert_rows_method: "chunk"
  dbt_artifacts_chunk_size: 500
  query_max_size: 100000

(I removed one zero from query_max_size)

If you can check if this configurations makes any difference would be great.

[cwmuller]

Hi @haritamar ,

Indeed! I moved the config from to vars, and i was able to run and test the entire project, and elementary models without any failures. I had put the config under elementary, under models. I probably searched for 'elementary' in the dbt_project.yml file and added it underneath without giving it more thought.

Thanks for the assistance!

[haritamar]

That's great to hear @cwmuller !
We'll work on actually incorporating these as defaults in the next version.

[nickozilla]

@haritamar @Maayan-s

Hi, we've added the suggested changes to the dbt_project.yml

vars:
  query_max_size: 100000
  insert_rows_method: "chunk"
  dbt_artifacts_chunk_size: 500

But still see this message on our longer runs (Done. PASS=13380 WARN=48 ERROR=4 SKIP=1305 TOTAL=14737):

on-run-end failed, error:  The query is too large. The maximum standard SQL query length is 1024.00K characters, including comments and white space characters. 

Do you have any further suggestions to help with this?

[haritamar]

Hi @nickozilla !
Would you mind sharing a dbt.log file as well with this error?
Also, what is the Elementary package version you are using?

Itamar

[nickozilla]

Hi @haritamar ,

The log events I have shared are from our logs aggregator tool, so I don't have the dbt.log file accessible currently, I'll see what else I can provide, our elementary version is 0.16.0

Nick

[Niteeshkanungo]

Hello @haritamar
I have added this as vars -

elementary limits

dbt_artifacts_chunk_size: 10000
insert_rows_method: chunk
query_max_size: 100000

I am getting - The query is too large. The maximum standard SQL query length is 1024.00K characters, including comments and white space characters. still. DBT on BQ.
Do we have a solution for this?

[cwmuller]

We had to reduce the dbt_artifacts_chunk_size to 100 to get the fix to work for our project.

[nickozilla]

@cwmuller what were your other vars set to? & did you also experience this error?

The query is too large. The maximum standard SQL query length is 1024.00K characters, including comments and white space characters.

[cwmuller]

@nickozilla
Our settings are:

  elementary:
    insert_rows_method: "chunk"
    dbt_artifacts_chunk_size: 100
    query_max_size: 1000000

[joostboon]

We are hoping to close this issue with the proposed solution, but please let me know if you are still experiencing this issue

@benoitgoujon @wei-wei29 @chenxilll @kylemcleland @neethu2409 @belle-crisp

[Pgillets]

Adding

vars:
  insert_rows_method: "chunk"
  dbt_artifacts_chunk_size: 1000
  query_max_size: 1000000

to the dbt_project.yaml worked for me.

I'm using Elementary on 0.16.1 and DBT on 1.8.8 (using dbt-bigquery 1.8.3)

[joostboon]

Thanks! That is helpful to know