JS UDF, который возвращает структуру для стандартного SQL / BigQuery и создает два столбца

Я пытаюсь написать пользовательскую функцию для BigQuery, используя Javascript, который возвращает структуру и генерирует два столбца:

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 дал нам "ЧТО-ТО", я хотел бы вернуть столбцы: "ЧТО-ТО", например, COL, 1 для "Первый" и 2 для "Второй". Это что-то, что возможно?

У меня проблемы с возвратом STRUCT из функции JS (не уверен, что мой синтаксис выключен) и с правильным запросом. Для запроса я хочу избежать запуска функции JavaScript дважды. Спасибо!

Источник

1 ответ

Решение

Ниже приведен пример для 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

Примечание: если вы хотите, чтобы столбцы связывались с First, Second - вы можете заменить 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
Источник

Я хотел бы добавить к ответу Михаила Берлянта, который отлично работает в этой ситуации, но я столкнулся с проблемой в немного другой ситуации.

Вместо использования this в JavaScript, который сохраняет данные по строкам, я предлагаю для этого использовать новый объект.

В моем примере я хочу вернуть еще один столбец со значением этого столбца на основе значения другого существующего столбца. Я добавлю еще один столбец с именем "latencyUnder150", который будет иметь значение "Y", если значение в поле задержки меньше 150, в противном случае просто оставьте поле пустым.

#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

Используя переменную this в JS, вы получите ответ.

| 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)          |
Источник
Другие вопросы по тегам