Is there any way to call bigquery API functions from javascript?

Question

Here I have a scala UDF that checks if a url is one of my domains. To check if 'to_site' is one of my domains, I'm using indexOf in javascript.

CREATE TEMPORARY FUNCTION our_domain(to_site STRING)
RETURNS BOOLEAN
LANGUAGE js AS """
  domains = ['abc.com', 'xyz.com'];
  if (to_site == null || to_site == undefined) return false;
    for (var i = 0; i < domains.length; i++){
     var q= DOMAIN('XYZ');
     if (String.prototype.toLowerCase.call(to_site).indexOf(domains[i]) !== -1)
       return true;
    }
      return false;
""";

SELECT  our_domain('www.foobar.com'), our_domain('www.xyz.com');

This returns false, then true.

It would be much nicer if I could use the DOMAIN(url) function from javascript. indexOf is not very good because it will match www.example.com?from=www.abc.com, when actually example.com is not one of my domains. Javascript also has a (new URL('www.example.com/q/z')).hostname to parse the domain component, but it includes the subdomain like 'www.' which complicates the comparison. Bigquery's DOMAIN(url) function only gives the domain and knowing google it's fast C++.

I know I can do this

our_domain(DOMAIN('www.xyz.com'))

But in general it would be nice to use some of the bigquery API functions in javascript. Is this possible?

I also tried this

CREATE TEMPORARY FUNCTION our_domain1(to_site String) 
AS (our_domain(DOMAIN(to_site));

but it fails saying DOMAIN does not exist.

score 1 · Accepted Answer · answered Oct 28 '16 at 05:03

DOMAIN() function is supported in BigQuery Legacy SQL whereas Scalar UDFs are part of BigQuery Standard SQL.
So, unfortunatelly, no, you cannot use DOMAIN() function with code that uses Scalar UDF as of now at least.

And, no, you cannot use SQL Functions within JS [Scalar] UDFs, but you can use them in SQL UDFs

Finally, as I suggested in my answer on your previous question - in somple scenario like this your particular one - you better use SQL Scalar SQL vs. JS Scalar UDFs - they do not have LIMITs that JS UDFs have

sure. love helping :o) – Mikhail Berlyant Oct 28 '16 at 06:35 — Mikhail Berlyant, Oct 28 '16 at 06:35

score 1 · Answer 2 · edited May 23 '17 at 12:06

1

The DOMAIN function in legacy SQL is more or less just a regular expression. Have you seen this previous question about DOMAIN? As Mikhail points out, you should be able to define a SQL UDF that uses a regex to extract the domain and then checks if it's in your list.

edited May 23 '17 at 12:06

Community

1
1

answered Oct 28 '16 at 05:27

Elliott Brossard

32,095
2
67
99

Is there any way to call bigquery API functions from javascript?

2 Answers2