CODEWITHSUNDEEP
sqlgoogle-bigquery
Fadi
Fadi

Reputation: 15

Need help regarding running multiple queries in Big Query

I have some queries that I want to run in a sequential Manner. Is it possible to schedule multiple queries under one scheduled query in Big Query? Thanks

CONCEPT PICTUREtack.imgur.com/flUN4.jpg

Upvotes: 0

Views: 2432

Answers (3)

Elliott Brossard
Elliott Brossard

Reputation: 33705

Not currently, but an alpha program for scripting support in BigQuery was announced at Google Cloud Next in April. You can follow the relevant feature request for updates. In the meantime, you could consider using Cloud Composer to execute multiple sequential queries or an App Engine cron with some code to achieve sequential execution on a regular basis.

Edit (October 2019): support for scripting and stored procedures is now in beta. You can submit multiple queries separated with semi-colons and BigQuery is able to run them now.

Upvotes: 1

justbeez
justbeez

Reputation: 1387

I'm not 100% sure if this is what you're looking for, but I'm confident that you won't need to orchestrate many queries to do this. It may be as simple to use the INSERT...SELECT syntax, like this:

INSERT INTO
  YourDataset.AdServer_Refine
SELECT
  Placement_ExtID,
  COALESCE(m.New_Ids,a.Placement_ExtID) AS New_Ids,
  m.Labels,
  CONCAT(Date," - ",New_Ids) AS Concatenated,
  a.Placement_strategy,
  a.Campaign_Id,
  a.Campaign,
  a.Cost,
  a.Impressions,
  a.Clicks,
  a.C_Date AS Current_date,
  a.Date
FROM
  YourDataset.AdServer AS a
  LEFT JOIN
  YourDataset.Matching AS m
  USING(Placement_ExtID)
WHERE
  a.Date = CURRENT_DATE()

This will insert all the rows that are output from SELECT portion of the query (and you can easily test the output by just running the SELECT).

Another option is to create a scheduled query that outputs to your desired table from the SELECT portion of the query above.

If that isn't doing what you're expecting, please clarify the question and leave a comment and I'm happy to try to refine the answer.

Upvotes: 0

rtenha
rtenha

Reputation: 3616

If you don't need all of the intermediate tables and are just interested in the final output... consider using CTEs.

with first as (
    select *, current_date() as todays_date from <table1>
),
second as (
    select current_date(), concat(field1,field2) as new_field, count(*) as ct
    from first
    group by 1,2
)
select * from second

You can chain together as many of these as needed.

If you do need all of these intermediate tables materialized, you are venturing into ETL and orchestration tools (dbt, airflow, etc) or will need to write a custom script to execute several commands sequentially.

Upvotes: 1

Related Questions