CODEWITHSUNDEEP
google-bigqueryuser-defined-functions
Nik M.
Nik M.

Reputation: 105

Effective way to write function in BigQuery

I have a function, which i want to optimize:

CREATE OR REPLACE FUNCTION <project>.<dataset>.<function_name>( tv1 STRING, tv2 STRING) <br>
RETURNS STRING <br>
AS ( <br>
CASE
when lower(tv1) = 'val1' then '11' <br>
when lower(tv1) = 'val2' then 'ABC1' <br>
when lower(tv1) = 'val3' then 'XYZ5' <br>
when lower(tv2) = 'val4' then 'MyVal' <br>
when lower(tv2) = 'val5' then 'HisVal' <br>
else 'nothing' <br>
end <br>
); <br>

Here, how i can avoid using the lower() in every sentence and do it once INSIDE the function itself. I don't want to call the lower function with the lower() call to it from outside. How can i make it inside the function itself ?

Upvotes: 0

Views: 291

Answers (2)

Fcojavmelo
Fcojavmelo

Reputation: 376

If you feel more comfortable with, you can also use JavaScript to define your functions (JavaScript UDF structure), but as @Mikhail pointed out, you need to convert to lowercase both tv1 and tv2 at least once for each variable:

CREATE TEMP FUNCTION test(tv1 STRING, tv2 STRING)
RETURNS STRING
LANGUAGE js
AS """

  tv1 = tv1.toLowerCase();
  tv2 = tv2.toLowerCase();

  if(tv1 == "val1") return "11";
  if(tv1 == "val2") return "ABC1";
  if(tv1 == "val3") return "XYZ5";
  if(tv2 == "val4") return "MyVal";
  if(tv2 == "val5") return "HisVal";

  return "nothing";

""";

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Below is for BigQuery Standard SQL 

CREATE TEMP FUNCTION test( tv1 STRING, tv2 STRING)
RETURNS STRING AS ((
  SELECT CASE 
    WHEN tv1 = 'val1' THEN '11'
    WHEN tv1 = 'val2' THEN 'ABC1'
    WHEN tv1 = 'val3' THEN 'XYZ5'
    WHEN tv2 = 'val4' THEN 'MyVal'
    WHEN tv2 = 'val5' THEN 'HisVal'
    ELSE 'nothing'
  END
  FROM UNNEST([LOWER(tv1)]) tv1, UNNEST([LOWER(tv2)]) tv2
));

As you can see, it eliminates need in using LOWER in each and every "sentence", but still require using LOWER once for both tv1 and tv2

Upvotes: 2

Related Questions