bigquery standard sql udf mapping to struct is returning internal error

Question

I have a code block below for parsing query params using udf. It works fine when the value passed to function is hardcoded as in the example. Thought when I try to parse the same value fetched from a table I get a An internal error occurred and the request could not be completed. (error code: internalError)

CREATE TEMPORARY FUNCTION parse(queryString STRING) RETURNS ARRAY<STRUCT<key STRING, value STRING>> LANGUAGE js AS
"""
    var params = {}
    var array = []
    // split into key/value pairs
    var queries = queryString.split('&');
    var ind = 0
    // convert the array of strings into an object
    for (var i = 0; i < queries.length; i++ ) {
        var temp = queries[i].split('=');
        if(temp.length < 2) continue;
        array[ind++] = { key: temp[0], value: decodeURI(temp[1]) }
    }
    return array;
""";
select parse('ca_chid=2002810&ca_source=gaw&ca_ace=&ca_nw=g&ca_dev=c&ca_pl=&ca_pos=1t3&ca_agid=32438864366&ca_caid=260997846&ca_adid=151983037851&ca_kwt=florists%20in%20walsall&ca_mt=e&ca_fid=&ca_tid=aud-117534990726:kwd-420175760&ca_lp=9045676&ca_li=&ca_devm=&ca_plt=&ca_sadt=&ca_smid=&ca_spc=&ca_spid=&ca_sco=&ca_sla=&ca_sptid=&ca_ssc=&gclid=CLaDoa6ZrdACFcyRGwodG8IFvQ') as params

--not working
--select parse(page_urlquery) from (
--SELECT page_urlquery  FROM `query_param_snapshot` where page_urlquery != '' LIMIT 1

Elliott Brossard Elliott Brossard · Accepted Answer · 2016-12-12T18:13:07

Also reported on the issue tracker (we are working on a fix). One workaround is to use a SQL function rather than a JavaScript function, e.g.:

CREATE TEMPORARY FUNCTION parse(queryString STRING)
    RETURNS ARRAY<STRUCT<key STRING, value STRING>> AS (
  (SELECT
     ARRAY_AGG(STRUCT(
       entry[OFFSET(0)] AS key,
       entry[OFFSET(1)] AS value))
   FROM (
     SELECT SPLIT(pairString, '=') AS entry
     FROM UNNEST(SPLIT(queryString, '&')) AS pairString)
   )
);
SELECT parse('ca_chid=2002810&ca_source=gaw&ca_ace=&ca_nw=g&ca_dev=c&ca_pl=&ca_pos=1t3&ca_agid=32438864366&ca_caid=260997846&ca_adid=151983037851&ca_kwt=florists%20in%20walsall&ca_mt=e&ca_fid=&ca_tid=aud-117534990726:kwd-420175760&ca_lp=9045676&ca_li=&ca_devm=&ca_plt=&ca_sadt=&ca_smid=&ca_spc=&ca_spid=&ca_sco=&ca_sla=&ca_sptid=&ca_ssc=&gclid=CLaDoa6ZrdACFcyRGwodG8IFvQ') AS params;

bigquery standard sql udf mapping to struct is returning internal error

1 Answers