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]: on-run-end hook fails due to exceeding maximum standard SQL query length in BigQuery #369

Closed
waligob opened this issue Jul 19, 2023 · 6 comments
Closed

[Bug]: on-run-end hook fails due to exceeding maximum standard SQL query length in BigQuery #369

waligob opened this issue Jul 19, 2023 · 6 comments
Labels
bug Something isn't working

Comments

@waligob
Copy link

waligob commented Jul 19, 2023 •
edited
Loading

Overview

For dbt_artifact versions 2.4.0, 2.4.1, and 2.4.2 (and dbt-core 1.4.5) the on-run-end hook fails with the error:

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

How to reproduce

Specify dbt_artifacts version 2.4.0, 2.4.1, or 2.4.2 in packages.yml and run the command dbt clean && dbt deps && dbt run --full-refresh --select dbt_artifacts

Expected behaviour

The command completes successfully. Note that dbt_artifacts version 2.3.0 produces the expected behavior.

Screenshots

$ dbt clean && dbt deps && dbt run --full-refresh --select dbt_artifacts
15:32:23  Running with dbt=1.4.5
15:32:23  Checking target/*
15:32:23  Cleaned target/*
15:32:23  Checking dbt_packages/*
15:32:23  Cleaned dbt_packages/*
15:32:23  Finished cleaning all paths.
15:32:26  Running with dbt=1.4.5
15:32:26  Installing dbt-labs/dbt_utils
15:32:26    Installed from version 1.1.1
15:32:26    Up to date!
15:32:26  Installing calogica/dbt_expectations
15:32:27    Installed from version 0.8.5
15:32:27    Up to date!
15:32:27  Installing dbt-labs/codegen
15:32:27    Installed from version 0.10.0
15:32:27    Up to date!
15:32:27  Installing data-mie/dbt_profiler
15:32:27    Installed from version 0.7.0
15:32:27    Up to date!
15:32:27  Installing dbt-labs/dbt_project_evaluator
15:32:27    Installed from version 0.6.2
15:32:27    Up to date!
15:32:27  Installing dbt-labs/metrics
15:32:27    Installed from version 1.4.1
15:32:27    Up to date!
15:32:27  Installing brooklyn-data/dbt_artifacts
15:32:27    Installed from version 2.4.2
15:32:27    Up to date!
15:32:27  Installing elementary-data/elementary
15:32:27    Installed from version 0.8.4
15:32:27    Up to date!
15:32:27  Installing dbt-labs/audit_helper
15:32:27    Installed from version 0.9.0
15:32:27    Up to date!
15:32:27  Installing calogica/dbt_date
15:32:27    Installed from version 0.7.2
15:32:27    Up to date!
15:32:27  
15:32:31  Running with dbt=1.4.5
15:32:31  Unable to do partial parsing because saved manifest not found. Starting full parse.
15:32:52  Found 320 models, 163 tests, 2 snapshots, 0 analyses, 1456 macros, 1 operation, 17 seed files, 113 sources, 1 exposure, 1 metric
15:32:52  
15:32:54  Concurrency: 4 threads (target='dev')
15:32:54  
15:32:54  1 of 34 START sql incremental model dbt_test.exposures .................. [RUN]
15:32:54  2 of 34 START sql incremental model dbt_test.invocations ................ [RUN]
15:32:54  3 of 34 START sql incremental model dbt_test.model_executions ........... [RUN]
15:32:54  4 of 34 START sql incremental model dbt_test.models ..................... [RUN]
15:32:57  2 of 34 OK created sql incremental model dbt_test.invocations ........... [CREATE TABLE (0.0 rows, 0 processed) in 2.87s]
15:32:57  5 of 34 START sql incremental model dbt_test.seed_executions ............ [RUN]
15:32:57  4 of 34 OK created sql incremental model dbt_test.models ................ [CREATE TABLE (0.0 rows, 0 processed) in 2.88s]
15:32:57  6 of 34 START sql incremental model dbt_test.seeds ...................... [RUN]
15:32:57  1 of 34 OK created sql incremental model dbt_test.exposures ............. [CREATE TABLE (0.0 rows, 0 processed) in 2.95s]
15:32:57  7 of 34 START sql incremental model dbt_test.snapshot_executions ........ [RUN]
15:32:57  3 of 34 OK created sql incremental model dbt_test.model_executions ...... [CREATE TABLE (0.0 rows, 0 processed) in 3.17s]
15:32:57  8 of 34 START sql incremental model dbt_test.snapshots .................. [RUN]
15:32:59  5 of 34 OK created sql incremental model dbt_test.seed_executions ....... [CREATE TABLE (0.0 rows, 0 processed) in 2.37s]
15:32:59  9 of 34 START sql incremental model dbt_test.sources .................... [RUN]
15:33:00  6 of 34 OK created sql incremental model dbt_test.seeds ................. [CREATE TABLE (0.0 rows, 0 processed) in 2.56s]
15:33:00  10 of 34 START sql incremental model dbt_test.test_executions ........... [RUN]
15:33:00  7 of 34 OK created sql incremental model dbt_test.snapshot_executions ... [CREATE TABLE (0.0 rows, 0 processed) in 2.82s]
15:33:00  11 of 34 START sql incremental model dbt_test.tests ..................... [RUN]
15:33:00  8 of 34 OK created sql incremental model dbt_test.snapshots ............. [CREATE TABLE (0.0 rows, 0 processed) in 2.69s]
15:33:00  12 of 34 START sql view model dbt_test.stg_dbt__invocations ............. [RUN]
15:33:01  12 of 34 OK created sql view model dbt_test.stg_dbt__invocations ........ [CREATE VIEW (0 processed) in 0.65s]
15:33:01  13 of 34 START sql view model dbt_test.stg_dbt__models .................. [RUN]
15:33:02  13 of 34 OK created sql view model dbt_test.stg_dbt__models ............. [CREATE VIEW (0 processed) in 0.92s]
15:33:02  14 of 34 START sql view model dbt_test.stg_dbt__exposures ............... [RUN]
15:33:02  10 of 34 OK created sql incremental model dbt_test.test_executions ...... [CREATE TABLE (0.0 rows, 0 processed) in 2.31s]
15:33:02  15 of 34 START sql view model dbt_test.stg_dbt__model_executions ........ [RUN]
15:33:02  14 of 34 OK created sql view model dbt_test.stg_dbt__exposures .......... [CREATE VIEW (0 processed) in 0.58s]
15:33:02  16 of 34 START sql view model dbt_test.stg_dbt__seed_executions ......... [RUN]
15:33:02  9 of 34 OK created sql incremental model dbt_test.sources ............... [CREATE TABLE (0.0 rows, 0 processed) in 2.80s]
15:33:02  17 of 34 START sql view model dbt_test.stg_dbt__seeds ................... [RUN]
15:33:02  11 of 34 OK created sql incremental model dbt_test.tests ................ [CREATE TABLE (0.0 rows, 0 processed) in 2.40s]
15:33:02  18 of 34 START sql view model dbt_test.stg_dbt__snapshot_executions ..... [RUN]
15:33:03  15 of 34 OK created sql view model dbt_test.stg_dbt__model_executions ... [CREATE VIEW (0 processed) in 0.81s]
15:33:03  19 of 34 START sql view model dbt_test.stg_dbt__snapshots ............... [RUN]
15:33:03  17 of 34 OK created sql view model dbt_test.stg_dbt__seeds .............. [CREATE VIEW (0 processed) in 0.80s]
15:33:03  20 of 34 START sql view model dbt_test.fct_dbt__invocations ............. [RUN]
15:33:03  16 of 34 OK created sql view model dbt_test.stg_dbt__seed_executions .... [CREATE VIEW (0 processed) in 0.85s]
15:33:03  21 of 34 START sql view model dbt_test.dim_dbt__models .................. [RUN]
15:33:03  18 of 34 OK created sql view model dbt_test.stg_dbt__snapshot_executions  [CREATE VIEW (0 processed) in 0.75s]
15:33:03  22 of 34 START sql view model dbt_test.stg_dbt__test_executions ......... [RUN]
15:33:04  19 of 34 OK created sql view model dbt_test.stg_dbt__snapshots .......... [CREATE VIEW (0 processed) in 0.79s]
15:33:04  23 of 34 START sql view model dbt_test.dim_dbt__exposures ............... [RUN]
15:33:04  22 of 34 OK created sql view model dbt_test.stg_dbt__test_executions .... [CREATE VIEW (0 processed) in 0.56s]
15:33:04  21 of 34 OK created sql view model dbt_test.dim_dbt__models ............. [CREATE VIEW (0 processed) in 0.60s]
15:33:04  24 of 34 START sql view model dbt_test.stg_dbt__sources ................. [RUN]
15:33:04  25 of 34 START sql view model dbt_test.stg_dbt__tests ................... [RUN]
15:33:04  20 of 34 OK created sql view model dbt_test.fct_dbt__invocations ........ [CREATE VIEW (0 processed) in 0.70s]
15:33:04  26 of 34 START sql view model dbt_test.dim_dbt__current_models .......... [RUN]
15:33:04  23 of 34 OK created sql view model dbt_test.dim_dbt__exposures .......... [CREATE VIEW (0 processed) in 0.63s]
15:33:04  27 of 34 START sql view model dbt_test.fct_dbt__model_executions ........ [RUN]
15:33:04  25 of 34 OK created sql view model dbt_test.stg_dbt__tests .............. [CREATE VIEW (0 processed) in 0.60s]
15:33:04  28 of 34 START sql view model dbt_test.dim_dbt__seeds ................... [RUN]
15:33:04  24 of 34 OK created sql view model dbt_test.stg_dbt__sources ............ [CREATE VIEW (0 processed) in 0.84s]
15:33:04  29 of 34 START sql view model dbt_test.fct_dbt__seed_executions ......... [RUN]
15:33:05  26 of 34 OK created sql view model dbt_test.dim_dbt__current_models ..... [CREATE VIEW (0 processed) in 0.90s]
15:33:05  30 of 34 START sql view model dbt_test.fct_dbt__snapshot_executions ..... [RUN]
15:33:05  27 of 34 OK created sql view model dbt_test.fct_dbt__model_executions ... [CREATE VIEW (0 processed) in 0.81s]
15:33:05  31 of 34 START sql view model dbt_test.dim_dbt__snapshots ............... [RUN]
15:33:05  28 of 34 OK created sql view model dbt_test.dim_dbt__seeds .............. [CREATE VIEW (0 processed) in 0.80s]
15:33:05  32 of 34 START sql view model dbt_test.fct_dbt__test_executions ......... [RUN]
15:33:05  30 of 34 OK created sql view model dbt_test.fct_dbt__snapshot_executions  [CREATE VIEW (0 processed) in 0.52s]
15:33:05  33 of 34 START sql view model dbt_test.dim_dbt__tests ................... [RUN]
15:33:05  29 of 34 OK created sql view model dbt_test.fct_dbt__seed_executions .... [CREATE VIEW (0 processed) in 0.80s]
15:33:05  34 of 34 START sql view model dbt_test.dim_dbt__sources ................. [RUN]
15:33:06  31 of 34 OK created sql view model dbt_test.dim_dbt__snapshots .......... [CREATE VIEW (0 processed) in 0.82s]
15:33:06  34 of 34 OK created sql view model dbt_test.dim_dbt__sources ............ [CREATE VIEW (0 processed) in 0.63s]
15:33:06  33 of 34 OK created sql view model dbt_test.dim_dbt__tests .............. [CREATE VIEW (0 processed) in 0.86s]
15:33:06  32 of 34 OK created sql view model dbt_test.fct_dbt__test_executions .... [CREATE VIEW (0 processed) in 0.95s]
15:33:06  
15:33:06  Running 1 on-run-end hook
15:33:06  Uploading model executions
15:33:08  Uploading seed executions
15:33:08  Uploading snapshot executions
15:33:08  Uploading test executions
15:33:08  Uploading exposures
15:33:10  Uploading tests
15:33:13  Uploading seeds
15:33:15  Uploading models
15:33:29  Database error while running on-run-end
15:33:29  
15:33:29  Finished running 11 incremental models, 23 view models in 0 hours 0 minutes and 36.26 seconds (36.26s).
15:33:29  
15:33:29  Completed with 1 error and 0 warnings:
15:33:29  
15:33:29  on-run-end failed, error:
15:33:29   The query is too large. The maximum standard SQL query length is 1024.00K characters, including comments and white space characters.
15:33:29  
15:33:29  Done. PASS=34 WARN=0 ERROR=1 SKIP=0 TOTAL=35

Environment

Results of running dbt --version:

$ dbt --version
Core:
  - installed: 1.4.5
  - latest:    1.5.3 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - bigquery: 1.4.3 - Update available!

  At least one plugin is out of date or incompatible with dbt-core.
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Please paste the contents of your packages.yml file here:

packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<1.2.0"]
  - package: calogica/dbt_expectations
    version: [">=0.8.0", "<0.9.0"]
  - package: dbt-labs/codegen
    version: [">=0.10.0", "<0.11.0"]
  - package: data-mie/dbt_profiler
    version: [">=0.7.0", "<0.8.0"]
  - package: dbt-labs/dbt_project_evaluator
    version: [">=0.6.0", "<0.7.0"]
  - package: dbt-labs/metrics
    version: [">=1.4.0", "<1.5.0"]
  - package: brooklyn-data/dbt_artifacts
    version: "2.4.0"
  - package: elementary-data/elementary
    version: [">=0.8.0", "<0.9.0"]
  - package: dbt-labs/audit_helper
    version: [">=0.9.0", "<1.0.0"]
@waligob waligob added the bug Something isn't working label Jul 19, 2023
@stevenkoppenol
Copy link

We are suffering from this too. DBT Cloud, Google BigQuery backend.

09:35:34  Running with dbt=1.4.7
09:35:34  Installing brooklyn-data/dbt_artifacts
09:35:34    Installed from version 2.4.3
09:35:34    Up to date!
09:35:34  Installing calogica/dbt_expectations
09:35:35    Installed from version 0.9.0
09:35:35    Up to date!
09:35:35  Installing elementary-data/elementary
09:35:35    Installed from version 0.7.1
09:35:35    Updated version available: 0.9.0
09:35:35  Installing calogica/dbt_date
09:35:35    Installed from version 0.8.1
09:35:35    Up to date!
09:35:35  Installing dbt-labs/dbt_utils
09:35:35    Installed from version 1.1.1
...
09:36:37  Finished running 11 incremental models, 23 view models, 1 hook in 0 hours 0 minutes and 36.29 seconds (36.29s).
09:36:38
09:36:38  Completed with 1 error and 0 warnings:
09:36:38  on-run-end failed, error:
09:36:38   The query is too large. The maximum standard SQL query length is 1024.00K characters, including comments and white space characters.

@glsdown glsdown mentioned this issue Sep 13, 2023
Merged
13 tasks
@glsdown
Copy link
Contributor

glsdown commented Sep 28, 2023 •
edited
Loading

Hello. I've just put out a release in 2.6.0 which will hopefully help here. In a previous release, we added an additional all_results column which contained a JSON object of all results for people to be able to do their own analysis on. However, this increased the query size dramatically, which is why these issues started arising.

In the latest release, I've added a variable that will stop this behaviour to reduce the query size. Please can you try adding the following as a variable in your dbt_project.yml file and see if that resolves it?

dbt_artifacts_exclude_all_results: true

@stevenkoppenol
Copy link

This worked for us when pointing packages.yml at the main branch of dbt_artifacts. Will try 2.6.0.

@stevenkoppenol
Copy link

stevenkoppenol commented Sep 28, 2023 •
edited
Loading

(update: problem resolved by waiting)

@stevenkoppenol
Copy link

I was too fast. It works now.

14:18:09  Running with dbt=1.6.3
14:18:09  Installing brooklyn-data/dbt_artifacts
14:18:09  Installed from version 2.6.0
14:18:09  Up to date!

No 'query to large' errors anymore here (which we had in 2.4 and 2.5)

@glsdown
Copy link
Contributor

glsdown commented Sep 29, 2023

That's great to hear! Thank you so much for confirming. I'm going to close this issue now, but if anything comes back again, please let me know and reopen this.

@glsdown glsdown closed this as completed Sep 29, 2023
@glsdown glsdown mentioned this issue Oct 4, 2023
Closed
@glsdown glsdown mentioned this issue Oct 20, 2023
Closed
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
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@glsdown @waligob @stevenkoppenol