执行变量值Google BigQuery [英] execute variable values Google BigQuery

问题描述

我正在尝试执行变量的值，但在Google BigQuery中找不到有关该变量的文档.

I am trying to execute the value of the variable, but I can't find documentation about it in Google BigQuery.

DECLARE SQL STRING;

SELECT
  SQL = 
  CONCAT(
    "CREATE TABLE IF NOT EXISTS first.rdds_", 
    REPLACE(CAST(T.actime AS STRING), " 00:00:00+00", ""),
    " PARTITION BY actime ",
    " CLUSTER BY id ",
    " OPTIONS( ",
    " partition_expiration_days=365 ",
    " ) ",
    " AS ",
    "SELECT * ",
    "FROM first.rdds AS rd ",
    "WHERE rd.actime = ",
    "'", CAST(T.actime AS STRING), "'",
    " AND ",
    "EXISTS ( ",
    "SELECT 1 ",
    "FROM first.rdds_load AS rd_load ",
    "WHERE rd_load.id= rd.id ",
    ")"

  ) AS SQ
FROM (
  SELECT DISTINCT actime
  FROM first.rdds AS rd
  WHERE EXISTS (
    SELECT 1
    FROM first.rdds_load AS rd_load
    WHERE rd_load.id= rd.id
  )
) T;

我的变量将有很多行，这些脚本带有用于创建表的脚本，我需要执行此变量.

My variable will have many rows with scripted for create tables and I need to execute this variable.

在SQL Server中要执行的变量是:

In SQL Server for to execute variable is:

EXEC(@variable);

如何在Google BigQuery中执行SQL变量?

How to I execute SQL variable in Google BigQuery?

编辑:

我使用beta版进行了新测试:

I did new test with version beta:

使用数组，一个结果中的所有行(ARRAY_AGG):

Using array, all rows in one result (ARRAY_AGG):

DECLARE SQL ARRAY<STRING>;

SET SQL = (
SELECT
  CONCAT(
    "CREATE TABLE IF NOT EXISTS first.rdds_", 
    REPLACE(CAST(T.actime AS STRING), " 00:00:00+00", ""),
    " PARTITION BY actime ",
    " CLUSTER BY id ",
    " OPTIONS( ",
    " partition_expiration_days=365 ",
    " ) ",
    " AS ",
    "SELECT * ",
    "FROM first.rdds AS rd ",
    "WHERE rd.actime = ",
    "'", CAST(T.actime AS STRING), "'",
    " AND ",
    "EXISTS ( ",
    "SELECT 1 ",
    "FROM first.rdds_load AS rd_load ",
    "WHERE rd_load.id= rd.id ",
    ")"
  )
) AS SQ

FROM (
  SELECT DISTINCT actime
  FROM first.rdds AS rd
  WHERE EXISTS (
    SELECT 1
    FROM first.rdds_load AS rd_load
    WHERE rd_load.id= rd.id
  )
) T
);

我的结果:

包含所有说明的一行.但是我无法按照所有说明运行

One row with all instructions. But I can't running this with all instructions

推荐答案

更新:截至5/20/2020，BigQuery

Update: as of 5/20/2020, BigQuery released dynamic SQL feature for you to achieve the goal.

Dynamic SQL现在可以在所有BigQuery地区作为Beta版本使用.通过动态SQL，您可以在运行时动态生成和执行SQL语句.有关更多信息，请参见立即执行. x

================

================

BigQuery在纯SQL中不支持此(动态SQL)，但是您可以在任何

BigQuery does not support this (Dynamic SQL) in pure SQL, but you can implement this in any client of your choice

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