Google BQ:运行参数化查询，其中参数变量是BQ表目标 [英] Google BQ: Running Parameterized Queries where Parameter Variable is the BQ Table Destination

问题描述

我正在尝试从Linux命令行为BQ Table目标运行SQL.此SQL脚本将用于多个日期，客户端和BQ表目的地，因此这将需要在我的BQ API命令行调用中使用参数(标志--parameter).现在，我已经通过以下链接来了解参数化查询: https://cloud.google.com/bigquery/docs/parameterized-queries ，但是它在帮助我声明表名方面受到限制.

I am trying to run a SQL from the Linux Commandline for a BQ Table destination. This SQL script will be used for multiple dates, clients, and BQ Table destinations, so this would require using parameters in my BQ API-commandline calls (the flag --parameter). Now, I have followed this link to learn about parameterized queries: https://cloud.google.com/bigquery/docs/parameterized-queries , but it's limited in helping me with declaring a table name.

我的名为Advertiser_Date_Check.sql的SQL脚本如下:

My SQL script, called Advertiser_Date_Check.sql, is the following:

#standardSQL
SELECT *
FROM (SELECT *
      FROM @variable_table
      WHERE CAST(_PARTITIONTIME AS DATE) = @variable_date) as final
WHERE final.Advertiser IN UNNEST(@variable_clients)

其中的参数变量表示以下内容:

Where the parameter variables represent the following:

• variable_table:我要呼叫的BQ Table目标
• variable_date:我要从BQ表中提取的日期
• variable_clients:我要从数据中提取特定客户端的数组列表(从我引用的日期开始)

现在，我的BQ数据的命令行(LINUX)如下

Now, my Commandline (LINUX) for the BQ data is the following

TABLE_NAME=table_name_example
BQ_TABLE=$(echo '`project_id.dataset_id.'$TABLE_NAME'`')
TODAY=$(date +%F)

/bin/bq query --use_legacy_sql=false    \
       --parameter='variable_table::'$BQ_TABLE''  \
       --parameter=variable_date::"$TODAY"    \
       --parameter='variable_clients:ARRAY<STRING>:["Client_1","Client_2","Client_3"]'  \
       "`cat /path/to/script/Advertiser_Date_Check.sql`" 

@variable_date和@variable_clients的参数在过去仅仅是它们时就可以正常工作.但是，由于我希望在循环中的各个表上运行此确切的SQL命令，因此我创建了一个名为variable_table的参数.参数化查询必须采用标准SQL格式，因此表名称约定必须采用以下格式:

The parameters of @variable_date and @variable_clients have worked just fine in the past when it was just them. However, since I desire to run this exact SQL command on various tables in a loop, I created a parameter called variable_table. Parameterized Queries have to be in Standard SQL format, so the table name convention needs to be in such format:

`project_id.dataset_id.table_name`

每当我尝试在命令行上运行此命令时，通常会出现以下错误:

Whenever I try to run this on the Commandline, I usually get the following error:

Error in query string: Error processing job ... : Syntax error: Unexpected "@" at [4:12]

哪个要引用参数@variable_table，因此要引用一个表名很困难.在过去的尝试中，甚至出现过错误:

Which is referencing the parameter @variable_table, so it's having a hard time processing that this is referencing a table name. In past attempts, there even has been the error:

project_id.dataset_id.table_name: command not found

但这主要是由于对表目标名称的引用不佳.第一个错误是最常见的情况.

But this was mostly due to poor reference of table destination name. The first error is the most common occurrence.

总体而言，我对此事的疑问是:

Overall, my questions regarding this matter are:

1. 我如何在FROM子句中的参数化查询的命令行中将BQ表作为参数引用(例如，我尝试使用@variable_table进行操作)?甚至有可能吗?
2. 除了我目前正在使用的方式之外，您是否知道其他方法可从命令行对多个BQ表运行查询?

希望这一切都有意义，谢谢您的帮助！

Hope this all makes sense and thank you for your assistance!

推荐答案

从您链接的文档中:

参数不能替代标识符，列名，表名或查询的其他部分.

Parameters cannot be used as substitutes for identifiers, column names, table names, or other parts of the query.

不过，我认为在这种情况下可能对您有用的是将表名作为常规shell变量(而不是查询参数)进行注入.您需要确保您信任它的内容，或者您​​自己在构建字符串，以避免SQL注入.一种方法是对表名使用硬编码的常量，然后根据用户输入选择要插入查询文本的常量.

I think what might work for you in this case, though, is performing the injection of the table name as a regular shell variable (instead of a query parameter). You'd want to make sure that you trust the contents of it, or that you are building the string yourself in order to avoid SQL injection. One approach is to have hardcoded constants for the table names and then choose which one to insert into the query text based on the user input.

这篇关于Google BQ:运行参数化查询，其中参数变量是BQ表目标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！