BigQuery从表中加载配置变量(BigQuery load config variables from a table)

BigQuery从表中加载配置变量(BigQuery load config variables from a table)

我有一个表定义一行中的常量。 我想通过使用这些常量的值在另一个表上构造一个查询，如：

SELECT DataTable.name FROM DataTable WHERE DataTable.key> Config.keyConstant

但我不知道如何干净利落地做到这一点。 在这种情况下，可以通过使用CROSS JOIN来完成

SELECT DataTable.name FROM（DataTable CROSS JOIN Config）WHERE DataTable.key> Config.keyConstant

但是当我的查询变大并且在不同的地方需要配置时，这变得非常混乱。

有什么建议么？ 在SQL中，我认为你会用变量来做这件事。

编辑：其实我希望能够做类似的事情

SELECT IF（Config.keyConstant * DataTable.key = 1，DataTable.name，“John”）FROM DataTable

这意味着不幸的是我无法将所有条件逻辑移动到一个WHERE EXISTS子句中，就像答案中建议的那样（虽然我没有意识到这一点，而且很酷）。

I have a table defining constants in a single row. I want to construct a query on another table by using the value of these constants, something like:

SELECT DataTable.name FROM DataTable WHERE DataTable.key > Config.keyConstant

but I have no idea how I can do this cleanly. In this case it could be done by using a CROSS JOIN

SELECT DataTable.name FROM (DataTable CROSS JOIN Config) WHERE DataTable.key > Config.keyConstant

But this gets really messy as my queries get larger and the config is needed in different places.

Any suggestions? In SQL I'd think you'd do this with variables.

EDIT: Actually I want to be able to do something like

SELECT IF(Config.keyConstant*DataTable.key=1, DataTable.name, "John") FROM DataTable

This means that unfortunately I can't move all of the conditional logic into a WHERE EXISTS clause like suggested in an answer (although I wasn't aware of this, and it is cool).

最满意答案

我认为你的方向是正确的：

像下面这样的东西应该适用于BigQuery Legacy和Standard SQL，并且让Config只有一行使CROSS JOIN看起来不那么糟糕。

SELECT DataTable.name FROM DataTable CROSS JOIN Config WHERE DataTable.key1 > Config.key1Constant AND DataTable.key2 = Config.key2Constant AND DataTable.key3 < Config.key3Constant

在BigQuery Standard SQL中，您可以将其更改为以下内容，这在某种程度上让我看起来更便携：

SELECT DataTable.name FROM DataTable WHERE EXISTS ( SELECT 1 FROM config WHERE DataTable.key1 > key1Constant AND DataTable.key2 = key2Constant AND DataTable.key3 < key3Constant )

几点说明：

成本方面 - 即使Config表很小 - 每次它将为计费字节贡献额外的10MB 在Config中有额外的列（如id）将允许您通过调用特定的id值来管理要使用的不同版本的常量。 或者基于条件的逻辑可用于调用所需的常量

I think you are in the right direction:

Something like below should work for both BigQuery Legacy and Standard SQL and having that Config has just one row makes CROSS JOIN not that bad looking.

SELECT DataTable.name FROM DataTable CROSS JOIN Config WHERE DataTable.key1 > Config.key1Constant AND DataTable.key2 = Config.key2Constant AND DataTable.key3 < Config.key3Constant

In BigQuery Standard SQL you can change this to below, which somehow looks to me a little bit more portable:

SELECT DataTable.name FROM DataTable WHERE EXISTS ( SELECT 1 FROM config WHERE DataTable.key1 > key1Constant AND DataTable.key2 = key2Constant AND DataTable.key3 < key3Constant )

Few notes:

cost wise - even though Config table is small - each time it will be contributing extra 10MB to the billing bytes having extra column(s) in Config (like id ) will allow you to manage different versions of constants to be used by calling specific id value. Or condition based logic can be used to invoke needed constants