Redshift：从字符串执行动态查询

I would like to execute a dynamic SQL query stored in a string field on Amazon Redshift.

我想在Amazon Redshift上执行存储在字符串字段中的动态SQL查询。

My background is mostly T-SQL relational databases. I used to build SQL statements dynamically, store them into variables and them execute them. I know Redshift can prepare and execute statements, but I wonder if it is possible to execute a query stored in a string field.

我的背景主要是T-SQL关系数据库。我曾经动态地构建SQL语句,将它们存储到变量中并执行它们。我知道Redshift可以准备和执行语句,但我想知道是否可以执行存储在字符串字段中的查询。

I have a piece of code that dynamically builds the code below with stats on several tables using pg_* system tables. Every column/table name is dynamically calculated. Here's an example of the query output:

我有一段代码使用pg_ *系统表在几个表上使用stats动态构建下面的代码。每个列/表名都是动态计算的。以下是查询输出的示例:

SELECT h_article_id AS key, 'transport_parameters_weight_in_grams' AS col_name, COUNT(DISTINCT transport_parameters_weight_in_grams) AS count_value FROM dv.s_products GROUP BY h_article_id UNION ALL
SELECT h_article_id AS key, 'transport_parameters_width_in_mm' AS col_name, COUNT(DISTINCT transport_parameters_width_in_mm) AS count_value FROM dv.s_products GROUP BY h_article_id UNION ALL
SELECT h_article_id AS key, 'label_owner_info_communication_address' AS col_name, COUNT(DISTINCT label_owner_info_communication_address) AS count_value FROM dv.s_products GROUP BY h_article_id

I would like to input this dynamic piece of code within another query, so I can make some statistics, like so:

我想在另一个查询中输入这个动态的代码片段,所以我可以做一些统计信息,如下所示:

SELECT col_name, AVG(count_value*1.00) AS avg_count
FROM (
  'QUERY ABOVE'
) A
GROUP BY col_name;

This would ouput something like:

这将输出如下内容:

col_name                                avg_count
transport_parameters_weight_in_grams    1.00
transport_parameters_width_in_mm        1.00
label_owner_info_communication_address  0.60

The natural way for me to do this would be to store everything as a string in a variable and execute it. But I'm afraid Redshift does not support this.

我这样做的自然方式是将所有内容作为字符串存储在变量中并执行它。但我担心Redshift不支持这一点。

Is there an alternative way to really build dynamic SQL code?

有没有另一种方法来真正构建动态SQL代码?

1 个解决方案

#1

No. There is not a straightforward way to run dynamic built SQL code in Redshift.

没有。在Redshift中运行动态构建的SQL代码没有直接的方法。

You can't define SQL variables, or create stored procedures, as you would have in MS SQL Server.

您无法像在MS SQL Server中那样定义SQL变量或创建存储过程。

You can create Python Functions in Redshift, but you would be coding in Python vs. SQL.

您可以在Redshift中创建Python函数,但您将使用Python与SQL进行编码。

You can use the "PREPARE" and "EXECUTE" statements to run "pre-defined" SQL queries, but you would have to create the statements outside of the database, before passing them to the execute command. By creating the statement outside of the database, in a way defeats the purpose.... You can create any statement in your "favorite" programming language.

您可以使用“PREPARE”和“EXECUTE”语句来运行“预定义”SQL查询,但在将它们传递给execute命令之前,您必须在数据库之外创建语句。通过在数据库之外创建语句,以某种方式击败目的....您可以使用“最喜欢的”编程语言创建任何语句。

As I said, this SQL based, in-database dynamic SQL does not exist.

正如我所说,这个基于SQL的数据库内动态SQL不存在。

Basically, you need to run this logic in your application or using something such as AWS Data Pipeline.

基本上,您需要在应用程序中运行此逻辑或使用AWS Data Pipeline之类的东西。

#1