我正在尝试使用 Javascript 为 BigQuery 编写一个用户定义的函数,该函数返回一个结构并生成两列:
CREATE TEMP FUNCTION exampleFunction(exampleString STRING)
RETURNS STRUCT<index INT64, latency INT64> LANGUAGE js AS
LANGUAGE js AS
"""
var exampleStruct = {1:100, 2:200}
return exampleStruct;
""";
我的查询是这样的:
SELECT
exampleCol,
exampleFunction(stringCol) -- use SELECT AS STRUCT somewhere here? with the aliases “First” and “Second”
FROM
[SOME DATATBASE HERE]
我希望 exampleFunction(stringCol)
的输出生成两列(如果我们包括 exampleCol
,则总共三列(。例如,如果exampleCol
给了我们"SOMETHING",我想返回列:"SOMETHING",例如Col,1表示"First",2表示"Second"。这是可能的吗?
我在从 JS 函数返回 STRUCT(不确定我的语法是否关闭(并正确查询时遇到问题。对于查询,我想避免运行两次 JavaScript 函数。谢谢!
以下示例适用于 BigQuery Standard SQL
#standardSQL
CREATE TEMP FUNCTION exampleFunction(exampleString STRING)
RETURNS STRUCT<index INT64, latency INT64>
LANGUAGE js AS
"""
arr = exampleString.split(':');
this.index = arr[0];
this.latency = arr[1];
return this;
""";
WITH `project.dataset.table` AS (
SELECT 1 exampleCol, '10:100' stringCol UNION ALL
SELECT 2, '20:200' UNION ALL
SELECT 3, '30:456'
)
SELECT exampleCol, exampleFunction(stringCol).*
FROM `project.dataset.table`
-- ORDER BY exampleCol
有结果
Row exampleCol index latency
1 1 10 100
2 2 20 200
3 3 30 456
注意:如果您希望列与第一、第二列别名 - 您可以将index
和latency
分别替换为 first
,second
如下例所示
#standardSQL
CREATE TEMP FUNCTION exampleFunction(exampleString STRING)
RETURNS STRUCT<first INT64, second INT64>
LANGUAGE js AS
"""
arr = exampleString.split(':');
this.first = arr[0];
this.second = arr[1];
return this;
""";
SELECT exampleCol, exampleFunction(stringCol).*
FROM `project.dataset.table`
或者您可以使用以下方法
#standardSQL
CREATE TEMP FUNCTION exampleFunction(exampleString STRING)
RETURNS STRUCT<index INT64, latency INT64>
LANGUAGE js AS
"""
arr = exampleString.split(':');
this.index = arr[0];
this.latency = arr[1];
return this;
""";
SELECT exampleCol, index AS first, latency AS second
FROM (
SELECT exampleCol, exampleFunction(stringCol).*
FROM `project.dataset.table`
)
两种情况下的结果如下
Row exampleCol first second
1 1 10 100
2 2 20 200
3 3 30 456
我想补充一下米哈伊尔·贝利安特的答案,它在这种情况下工作正常,但我在稍微不同的情况下遇到了一个问题。
与其在JavaScript中使用"this"来保留跨行的数据,我建议使用一个新对象来做到这一点。
在我的示例中,我想再返回一列,此列值基于另一个现有列的值。我将再添加一个名为"latencyUnder150"的列,如果延迟字段的值低于 150,则该列的值将为"Y",否则只需将该字段留空即可。
#standardSQL
CREATE TEMP FUNCTION exampleFunction(exampleString STRING)
RETURNS STRUCT<index INT64, latency INT64, latencyUnder150 STRING>
LANGUAGE js AS
"""
arr = exampleString.split(':');
this.index = arr[0];
this.latency = arr[1];
if (this.latency < 150) {
this.latencyUnder150 = 'Y'
}
return this;
""";
WITH `project.dataset.table` AS (
SELECT 1 exampleCol, '10:100' stringCol UNION ALL
SELECT 2, '20:200' UNION ALL
SELECT 3, '30:456'
)
SELECT exampleCol, exampleFunction(stringCol).*
FROM `project.dataset.table`
-- ORDER BY exampleCol
在JS中使用"this"变量,你会得到这个答案。
| Row | exampleCol | index | latency | latencyUnder150 |
|-----|------------|-------|---------|-----------------|
| 1 | 1 | 10 | 100 | Y |
| 2 | 2 | 20 | 200 | Y |
| 3 | 3 | 30 | 456 | Y |
您可以看到字段 latencyUnder150 保留了第一条记录中的值"Y"。
通过稍微更改代码以使用新对象,每一行开始时都没有前一行的值。
#standardSQL
CREATE TEMP FUNCTION exampleFunction(exampleString STRING)
RETURNS STRUCT<index INT64, latency INT64, latencyUnder150 STRING>
LANGUAGE js AS
"""
var outObj = {}
arr = exampleString.split(':');
outObj.index = arr[0];
outObj.latency = arr[1];
if (outObj.latency < 150) {
outObj.latencyUnder150 = 'Y'
}
return outObj;
""";
WITH `project.dataset.table` AS (
SELECT 1 exampleCol, '10:100' stringCol UNION ALL
SELECT 2, '20:200' UNION ALL
SELECT 3, '30:456'
)
SELECT exampleCol, exampleFunction(stringCol).*
FROM `project.dataset.table`
-- ORDER BY exampleCol
| Row | exampleCol | index | latency | latencyUnder150 |
|-----|------------|-------|---------|-----------------|
| 1 | 1 | 10 | 100 | Y |
| 2 | 2 | 20 | 200 | (null) |
| 3 | 3 | 30 | 456 | (null) |