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
– Mikhail Berlyant, Commented Mar 16, 2017 at 5:08
I'm wondering which Python library you are using, and what error you are getting - can you post the non-working code? — Felipe Hoffa
– Felipe Hoffa, Commented Mar 16, 2017 at 6:24
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
– Ishita Srivastava, Commented Mar 16, 2017 at 8:01
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
– Ishita Srivastava, Commented Mar 16, 2017 at 8:26

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

1

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 
'''

answered Mar 16, 2017 at 9:10

Felipe Hoffa

59.8k23 gold badges185 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ishita Srivastava Over a year ago

Thank you so much. This small thing had been bugging me since 4-5 days.

Ishita Srivastava Over a year ago

Also, is there a way to get data uploaded in bigquery in the order in which it is in the original data ? As, when I am uploading data into bigquery, its getting sorted. I do not want this sorting and want the data to be in the original order. What can I do for that?

Elliott Brossard Over a year ago

Please post a separate question instead of asking in the comment section here.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related