BigQuery中的游标 [英] Cursors in BigQuery

问题描述

在BigQuery脚本中是否有一种方法可以像在MySql脚本中一样声明游标.我必须安排一个脚本定期运行，其中有一个逻辑步骤1:提取所有企业名称(输出多行)步骤2:对于每个企业，转到企业表并运行一些更新查询.

Is there a way in BigQuery Scripting to declare cursor as we have in MySql Scripting. I have to Schedule a script to run periodically, There is a logic where Step 1: Extract all enterprise names (Multiple rows output) Step 2: For each enterprise, go to an enterprise's table and run some update queries.

MySql具有游标，可在脚本编写中帮助这种功能.我指的是BigQuery的脚本"页面，但找不到任何此类内容. https://cloud.google.com/bigquery/docs/参考/standard-sql/scripting#set

MySql Has Cursor Which helps with this kind of functionality in scripting. I was referring to BigQuery's Scripting page, but could not find any such thing. https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting#set

BigQuery中是否有与Cursor相当的产品?

Are there Cursor equivalent in BigQuery?

还有其他方法可以解决吗，我想与BigQuery脚本中的可用功能结合吗?

Is there any other way I can do, what I want to bo with whats available in BigQuery scripting?

推荐答案

我在BigQuery中使用了以下模拟通过游标的循环

I have used the following simulate looping through a cursor in BigQuery

开始

DECLARE x INT64 DEFAULT 1;
DECLARE z INT64 DEFAULT 0;

CREATE TEMP TABLE temp_country AS
SELECT country, RANK() OVER(ORDER BY country) rownum
FROM (SELECT DISTINCT country
FROM `bigquery-public-data.faa.us_airports`)
order by country;

SET z= (SELECT COUNT(*) FROM temp_country);

WHILE x<=z DO

  SELECT country 
  FROM temp_country
  WHERE rownum = x;

SET x=x+1;

END WHILE;

END;

这篇关于BigQuery中的游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！