使用声明&在BigQuery中循环以插入数据 [英] Use declare & loop in BigQuery to insert data

问题描述

我只是练习使用Bigquery&尝试对它使用我的SQL查询之一

I just practice using Bigquery & try to use one of my SQL query on it

declare @id int 
select @id = 1
while @id >=1 and @id <= 1000
begin
    insert into Quincy values(@id, 'Rank: ' + convert(varchar(5), @id))
end

这一行向表运行了1000次插入，我注意到bigquery不允许在其上声明值，因此@id在这里不起作用.我可以在BigQuery上像这样运行循环吗?

This one run the insert 1000 times to the table, I noticed that bigquery doesn't allow declare value on it so @id doesn't work here. May i as if are there any available method to run loop like this on BigQuery?

推荐答案

在处理sql时，使用基于游标的逻辑不是最佳实践，而是需要调整自己以进行基于集的处理.特别是BigQuery不支持光标处理和过程逻辑，但幸运的是，它对ARRAY拥有丰富的支持，可以在此处使用

It is not the best practice to use cursor-based logic when dealing with sql, rather you need to tune yourself into set-based processing. Especially with BigQuery which does not support [yet] cursor processing and procedural logic, but luckily has rich support for ARRAYs which can be used here

由于您的问题有点抽象-下面的示例也很抽象，但是给了您一个想法

As your question is little abstract - below example is abstract too, but gives you an idea

#standardSQL
INSERT INTO `project.dataset.Quincy` (id, col)
WITH array_to_loop_through AS (
  SELECT id 
  FROM UNNEST(GENERATE_ARRAY(1, 1000, 1)) id
)
SELECT id, CONCAT('Rank: ', CAST(id AS STRING))
FROM array_to_loop_through

这篇关于使用声明&在BigQuery中循环以插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！