大查询中有没有一种方法可以在SQL Server中执行诸如“ EXEC”之类的动态查询？

问题描述

我有一个包含200多个列名的表，这些表名是用-custColum1 -custColum200之类的临时名称创建的。

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

  Table1（custColum1，custColum2）
 Mappingtable（tempColumnName，RealColumnName）
映射表中的数据就像
（custColum1，Role_number）
（custColum2，Person_name）
  

I需要将表1更改为Table1（Role_number，Person_name）。
注意：由于我不知道要映射的列，因此无法使用此名称创建table1。

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

  SET @Sql ='ALTER TABLE TABLE_NAME RENAME Column columnName'
打印（@Sql）
 EXEC（@Sql）
  

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

解决方案

让我们假设以下简化示例

Table1

映射表

我手动处理您的用例的方式如下

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

  #standardSQL 
创建OR REPLACE TABLE`project.dataset.Table1` AS 
选择NULL AS Role_number，NULL AS Person_name-将生成此行
 FROM（SELECT 1）假联盟所有
 SELECT * FROM `project.dataset.Table1` 
  

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

 '选择空AS角色编号，空AS人名'
  

这可以通过在查询下面运行

  #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带偏移量的pos 
左连接`project.dataset.Mappingtable`在tempColumnName = col 
  

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

 'SELECT NULL AS Role_number，NULL AS Person_name'
  

所以，现在的问题是如何将上面动态构建的片段添加到我们感兴趣的查询中！

不幸的是，这是不可行的作为一个查询，仅在BigQuery中进行，但超级简单任务可在任何

在这里您可以看到：

在第一个任务中我们生成具有预期映射列名称的语句，并将结果分配给名为<$ c的参数$ c> var_columns_list （工作流执行后，它将获得期望值）

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

此外，您会注意到，与其使用普通格式引用 project.dataset.Table1 和 project.dataset.Mappingtable 之类的表-我正在使用< var_project_dataset> ;.表1 和< var_project_dataset> .Mappingtable 和参数 var_project_dataset 在参数面板中设置

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

在执行前是

显然，这是一个简化的示例，并且仅当您具有基本的列类型时才可以按原样工作-没有结构也没有数组。仍然值得庆幸的是，这种方法可以轻松处理您在问题中提到的200列甚至更多列。

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

披露：我是Potens.io团队的作者和负责人，这反映在我的个人资料中。我也是Google Cloud Platform开发人员专家和BigQuery Mate Chrome Extension的作者

I have a table with over 200 column names which are created with a temporary name like - custColum1 -custColum200.

I have a mapping table which contains a list of custColum1-custColumn200 to which name it has to be mapped with. For example

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

I need to change table 1 to Table1(Role_number,Person_name). Note: I cannot create table1 with this name sincew I don't know which column would be mapped.

Was thinking if We could do something like creating a dynamic query and execute as shown below

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

Is there a way to do this in BigQuery? Any ideas will be great

解决方案

Let’s assume simplified example as below

Table1

Mappingtable

The way I would approach your use case manually would be as below
First, assume we know in advance all mappings and we can assemble needed list manually and use it as below

#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`  

Now, we need to "figure out" how to generate below line from the above query

'SELECT NULL AS Role_number, NULL AS Person_name' 

This can be done by running below query

#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  

This will produce exactly string we need

'SELECT NULL AS Role_number, NULL AS Person_name'   

So, now the question is how to add above dynamically built fragment into query that we are interested in!
Unfortunately, it is not doable as a one query purely within the BigQuery, but super simple task to accomplish in ANY client or tool of your choice

I can demonstrate how easily this can be done by non-technical user with the Tool of my choice – Magnus (part of Potens.io – Suite of tools for BigQuery)

Below is snapshot of Magnus Workflow with just two BigQuery Tasks, which reproduce exactly above steps

As you can see here:

In first Task we generate the statement with expected mapped Column names and assign result to parameter called var_columns_list (after workflow execution it will get expected value)

In second Task we just simply building dynamic sql using that parameter

Also, you can notice that instead of using plain reference to tables like project.dataset.Table1 and project.dataset.Mappingtable - I am using <var_project_dataset>.Table1 and <var_project_dataset>.Mappingtable and parameter var_project_dataset is set in parameters panel

After running that workflow ,we get expected result as shown below

While before execution it was

Obviously this is simplified example and it will work AS IS only if you have basic column types - no structs and no arrays. Still good news is this approach will easily handle 200 or even more columns you mentioned in your question.

Anyway, I think above example can be a good start for you!

Disclosure: I am an author and leader of Potens.io Team which is reflected in my profile. I am also Google Developer Expert for Cloud Platform and author of BigQuery Mate Chrome Extension

这篇关于大查询中有没有一种方法可以在SQL Server中执行诸如“ EXEC”之类的动态查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！