BigQuery UDF Array<TIMESTAMP> return. Failed to coerce output value to type TIMESTAMP

1
votes

I am using a BigQuery JavaScript UDF to return an array of dates. The array must a timestamp array, similar to the GENERATE_TIMESTAMP_ARRAY() function in order to match an UNNEST CASE WHEN.

The problem occurs when I return the timestamp array from my JavaScript UDF. The array returns fine if I specify RETURNS ARRAY<STRING>, but when I use RETURNS ARRAY<TIMESTAMP> BigQuery seems to not be able to coerce the string format of the dates.

I have tried numerous string formats, i.e. 'YYYY-MM-DD HH:mm:ss', 'YYYY-MM-DD 00:00:00+00', 'YYYY-MM-DD HH:mm:ss.SSSSSS UTC'.

CREATE TEMP FUNCTION
  getCalendarDateArray(date_start STRING,
    date_end STRING,
    frequency STRING,
    unit STRING,
    value INT64,
    parent STRING,
    next_date TIMESTAMP)

  RETURNS ARRAY<TIMESTAMP>

  LANGUAGE js AS

"""

    let dates_array = getTimestampArray(date_start, date_end, frequency, unit, value, parent, next_date);
    return dates_array;


"""
OPTIONS
  ( library=["gs://my-bucket/myfunction.js"] );

UDF output is currently an array of formatted date strings which cannot be coerced to timestamp. What is the proper string format in order for BigQuery to coerce to timestamp successfully?

[
      "2019-07-02",
      "2019-07-09",
      "2019-07-16",
      "2019-07-23",
      "2019-07-30"
    ]
2
google-bigquery
what is the reason in using expensive JS UDF instead of using built-in GENERATE_..._ARRAY() family of functions? – Mikhail Berlyant
I am generating recurring dates based on calendar dates, not just set intervals. For instance: every 2nd day of week starting from X date, or every 3rd Week of month. However, after experimenting with UDF it is clear that it is expensive and time consuming. Instead I am going to simply calculate the dates for each row before uploading to bigquery. – Ryan

2 Answers

2
votes

Use Date():

CREATE TEMP FUNCTION
  getCalendarDateArray()
  RETURNS ARRAY<TIMESTAMP>
  LANGUAGE js AS
"""
  return [Date('2018-03-03 10:10:10')]
""";

SELECT getCalendarDateArray() dates

enter image description here

0
votes

Just to extend the accepted answer by Felipe Hoffa, the following example probably shows what happens: 

CREATE TEMP FUNCTION getDate_fail() RETURNS TIMESTAMP 
LANGUAGE js AS """ return Date.now()""";

CREATE TEMP FUNCTION getDate_succeed() RETURNS TIMESTAMP 
LANGUAGE js AS """ return Date(Date.now())""";

The former function throws a coercion exception, while the latter succeeds.