In google bigquery, how to use javascript UDF using google python client

Question

I am writing a query in bigquery using standard SQL and javascript UDF. I am able to implement this using WebUI and bigquery command line tool but my requirement is to make this query using google python client. Not able to achieve this. Please can someone help.

from google.cloud import bigquery
bigquery_client = bigquery.Client()
client = bigquery.Client()
query_results = client.run_sync_query("""

CREATE TEMPORARY FUNCTION CategoriesToNumerical(a array<STRING>,b    array<STRING>)
RETURNS string
LANGUAGE js AS """

var values = {};

 var counter = 0;

 for(i=0;i<a.length;i++)

 { var temp;

   temp = a[i];

   a[i] = counter;

   values[temp] = counter;

   counter ++;
  }

for(i=0;i<b.length;i++)
  {

for(var key in values)
  {
   if(b[i] == key)
   {
    b[i] = values[key];
   }
  }
}

return b;
""";

SELECT
CategoriesToNumerical(ARRAY(SELECT DISTINCT ProspectStage from   lsq.lsq_dest),ARRAY(SELECT ProspectStage from lsq.lsq_dest)) as prospectstageds

;""")

query_results.use_legacy_sql = False

query_results.run()

page_token = None

while True:
    rows1, total_rows, page_token = query_results.fetch_data(
        max_results=100,
        page_token=page_token)
    for row1 in rows1:
        print "row",row1
    if not page_token:
        break

This is not working for me.Please can someone help with how should I be going about it.

You should show what have you tried so far? Please edit your question to show a Minimal, Complete, and Verifiable example of the code that you are having problems with, then we can try to help with the specific problem. You can also read How to Ask — Mikhail Berlyant
I'm wondering which Python library you are using, and what error you are getting - can you post the non-working code? — Felipe Hoffa
Do I need to put the javascript UDF part of the code in a file in google cloud storage and then use it in my code from there.But I don't even understand how to access that file from inside the sql query function — Ishita Srivastava
The problem I am facing here is that the Javascript commands like var values= {}; are giving me syntax errors. Is this because java script commands are not being recognised inside Python. The same query that I have written above works perfectly in bigquery web UI. — Ishita Srivastava

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2017-03-16T09:10:40

The problem seems to be you have here 2 sets of conflicting """. Replace one of these sets for a triple ''', and the code should work.

So instead of

query_results = client.run_sync_query("""

CREATE TEMPORARY FUNCTION CategoriesToNumerical(a array<STRING>,b    array<STRING>)
RETURNS string
LANGUAGE js AS """
  javacript code
"""
SELECT *
FROM 
"""

write

query_results = client.run_sync_query('''

CREATE TEMPORARY FUNCTION CategoriesToNumerical(a array<STRING>,b    array<STRING>)
RETURNS string
LANGUAGE js AS """
  javacript code
"""
SELECT *
FROM 
'''

In google bigquery, how to use javascript UDF using google python client

1 Answers