1

I have used this sample code to write functions on the aggregated data from Big Query. 

CREATE TEMP FUNCTION aggregate_fruits(fruits ARRAY<STRING>)
RETURNS STRING
LANGUAGE js AS """
return "my fruit bag contains these items: " + fruits.join(",");
""";

WITH fruits AS
(SELECT "apple" AS fruit
UNION ALL SELECT "pear" AS fruit
UNION ALL SELECT "banana" AS fruit)

SELECT aggregate_fruits(ARRAY_AGG(fruit))
FROM fruits;

This has been used as a reference. But the query is taking more time in order to first define the function and then run the query. Is it possible to define the function only once and then use the same function for multiple queries ?

BigQuery says this error when I tried removing "TEMP" keyword from the above

Error: Only temporary functions are currently supported; use CREATE TEMPORARY FUNCTION
phaigeim
  • 729
  • 13
  • 34
  • How do you know that it's taking more time? – Elliott Brossard Dec 07 '17 at 16:40
  • @ElliottBrossard ran CREATE TEMP FUNCTION and select "user" as user both. The select query is nothing it is running in millisecond but with the CREATE TEMP it is running in 6 seconds. – phaigeim Dec 07 '17 at 18:48
  • It's due to initializing the JavaScript environment, which will happen even if the SELECT statement doesn't call the UDF. – Elliott Brossard Dec 07 '17 at 18:50
  • @ElliottBrossard you are absolutely correct. That's why I am curious to know if I can defined my function once and run multiple queries using that function. As of now I can run only one query from the dashboard. If I want to run it again, I again need to initialise the function again which I don't want. Can you share some thoughts ? – phaigeim Dec 07 '17 at 20:30
  • There is a [feature request](https://issuetracker.google.com/issues/35905921) that you may want to follow for permanent UDFs. It's not going to reduce the overhead of using JavaScript UDFs, however. – Elliott Brossard Dec 07 '17 at 21:14
  • Thanks @ElliottBrossard Initialising it once is fine. I am worried about it getting it initialised every time I run a new query which uses the same function defined. – phaigeim Dec 07 '17 at 21:39

0 Answers0