"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

[amishas157]

Ran into same problem with elementary=0.16.1, dbt=1.8.3 and warehouse as bigquery.

Resolved with following config:

  insert_rows_method: "chunk"
  dbt_artifacts_chunk_size: 100
  query_max_size: 1000000

[haritamar]

Hi everyone!
Some updates here.

So after a few iterations here and thanks to a contributor (thanks @Niteeshkanungo !), we actually realized that the "chunk" method had several bugs in it, and in particular the query_max_size limit didn't actually work as expected. Sorry about that.
We made a refactor to the insert rows logic which I hope should solve this issue for good (crossing fingers you won't prove me otherwise 🤞 ), and released version 0.16.2 a couple of hours ago.

The insert_rows_method var has been removed (it won't break anything to have it but it will be ignored), and the package will take into account both the chunk size and the query max size limits by default.

Would love if people here can try the new version and comment here how it goes (would be great if you can try it first without any configuration vars, and only if it doesn't work with lower limits as posted above).

Thanks,
Itamar

[haritamar]

@Niteeshkanungo @amishas157 @cwmuller @nickozilla @belle-crisp @Pgillets @jmainwaring

[nickozilla]

@haritamar

Seems to have fixed things for us, thanks for investigating. Can you share the PR with the bugfix, out of curiosity?

EDIT: I can also confirm this works for us without any variables set for query_max_size or dbt_artifacts_chunk_size dbt_project.yml

[nickozilla]

@haritamar

Small update: After some further tests without the query_max_size or dbt_artifacts_chunk_size variables set, I have found that this issue does still occur, we've settled with a stable config of:

  query_max_size: 1000000
  dbt_artifacts_chunk_size: 1000

[haritamar]

@nickozilla - Thanks for updating! This is helpful.
I think we'll lower the defaults for these values then (which are currently 1M for query size and 5000 for chunk size).

Regarding the bugfix PR - here it is
Basically what it does is a refactor that unifies the handling of both limits to the same insert method (and as a part of it removing the insert_rows_method configuration).

Technically it was done by adding support for the dbt_artifacts_chunk_size parameter in the code that previously split the queries by query size, and removing the other insert method (which we found out was buggy and missing some features).