dynamic - 大查询中有没有办法在 sql server 中执行诸如“EXEC”之类的动态查询？

Question

我有一个包含 200 多个列名的表，这些列名是使用临时名称（例如 - custColum1 -custColum200）创建的。

我有一个映射表，其中包含一个 custColum1-custColumn200 列表，它必须映射到该列表。例如

Table1(custColum1,custColum2) 
Mappingtable(tempColumnName,RealColumnName) 
data in mapping table be like 
(custColum1,Role_number)
(custColum2,Person_name)

我需要将表 1 更改为 Table1(Role_number,Person_name)。注意：我无法使用此名称创建 table1，因为我不知道将映射哪一列。

正在考虑我们是否可以执行诸如创建动态查询并执行如下所示的操作

SET @Sql = 'ALTER TABLE TABLE_NAME RENAME Column columnName'
           print  (@Sql)
           EXEC (@Sql)

有没有办法在 BigQuery 中做到这一点？任何想法都会很棒

标签： dynamicgoogle-bigqueryuser-defined-functions

Answer 1

让我们假设简化示例如下

表格1

映射表

我将手动处理您的用例的方式如下
首先，假设我们事先知道所有映射，我们可以手动组装所需的列表并如下使用它

#standardSQL
CREATE OR REPLACE TABLE `project.dataset.Table1` AS 
SELECT NULL AS Role_number, NULL AS Person_name  -- this line to be generated
  FROM (SELECT 1) WHERE FALSE UNION ALL
SELECT * FROM `project.dataset.Table1`  

现在，我们需要“弄清楚”如何从上面的查询中生成下面的行

'SELECT NULL AS Role_number, NULL AS Person_name' 

这可以通过运行以下查询来完成

#standardSQL
SELECT CONCAT('SELECT', STRING_AGG(CONCAT(' NULL AS ', RealColumnName) ORDER BY pos)) select_statement
FROM (
  SELECT TO_JSON_STRING(t) AS cols FROM `project.dataset.Table1` t LIMIT 1
), UNNEST(REGEXP_EXTRACT_ALL(cols, r'"(.*?)":')) col WITH OFFSET AS pos
LEFT JOIN `project.dataset.Mappingtable` ON tempColumnName = col  

这将产生我们需要的字符串

'SELECT NULL AS Role_number, NULL AS Person_name'   

所以，现在的问题是如何将上述动态构建的片段添加到我们感兴趣的查询中！
不幸的是，它不能作为一个纯粹在 BigQuery 中进行的查询，而是在您选择的任何客户端或工具中 完成的超级简单的任务

我可以演示非技术用户使用我选择的工具 Magnus （Potens.io 的一部分——BigQuery工具套件） 可以轻松完成此操作

下面是 Magnus 工作流的快照，其中只有两个 BigQuery 任务，它们完全重现了上述步骤

正如你在这里看到的：

在第一个任务中，我们使用预期的映射列名生成语句并将结果分配给调用的参数var_columns_list（在工作流执行后它将获得预期值）

在第二个任务中，我们只是使用该参数简单地构建动态 sql

此外，您会注意到，我没有使用对表格的简单引用，而是project.dataset.Table1使用project.dataset.Mappingtableand<var_project_dataset>.Table1和参数，<var_project_dataset>.Mappingtable并且参数var_project_dataset是在参数面板中设置的

运行该工作流程后，我们得到预期的结果，如下所示

虽然在执行之前它是

显然这是一个简化的例子，只有当你有基本的列类型——没有结构和数组时，它才会按原样工作。仍然好消息是这种方法可以轻松处理您在问题中提到的 200 甚至更多列。

无论如何，我认为上面的例子对你来说是一个好的开始！

披露：我是 Potens.io 团队的作者和领导者，这反映在我的个人资料中。我也是云平台的谷歌开发专家和 BigQuery Mate Chrome 扩展的作者