How to use the BigQuery API using a Python script calling a UDF

Question

Against a BigQuery table, I'm trying to run a SQL statement calling a UDF. This statement is executed within a Python script and the call is made via the BigQuery API.

When I execute a simple SQL statement without a UDF, it works fine. However, I keep getting the same error when I try to use a UDF script (stored either locally or in a GCS bucket). This what I get on my local Terminal (I run the script via Python Launcher):

Traceback (most recent call last): File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/googleapiclient/http.py", line 840, in execute raise HttpError(resp, content, uri=self.uri) googleapiclient.errors.HttpError: https://www.googleapis.com/bigquery/v2/projects/[projectId]/queries?alt=json returned "Required parameter is missing">

And this is my Python script:

credentials = SignedJwtAssertionCredentials(
SERVICE_ACCOUNT_EMAIL,
key,
scope='https://www.googleapis.com/auth/bigquery')

aservice = build('bigquery','v2',credentials=credentials)
query_requestb = aservice.jobs()

query_data = {
    'configuration': {
        'query': {
            'userDefinedFunctionResources': [
                {
                   'resourceUri': 'gs://[bucketName]/[fileName].js'
                }
            ],
            'query': sql
        }
    },
    'timeoutMs': 100000
}

query_response = query_requestb.query(projectId=PROJECT_NUMBER,body=query_data).execute(num_retries=0)

Any idea what 'parameter is missing' or how I can get this to run?

Elliott Brossard · Accepted Answer · 2017-05-10 14:48:42Z

4

Instead of specifying userDefinedFunctionResources, use CREATE TEMP FUNCTION in the body of your 'query' with the library referenced as part of the OPTIONS clause. You will need to use standard SQL for this, and you can also refer to the documentation on user-defined functions. Your query would look something like this:

#standardSQL
CREATE TEMP FUNCTION MyJsFunction(x FLOAT64) RETURNS FLOAT64 LANGUAGE js AS """
  return my_js_function(x);
"""
OPTIONS (library='gs://[bucketName]/[fileName].js');

SELECT MyJsFunction(x)
FROM MyTable;

answered May 10, 2017 at 14:48

Elliott Brossard

34k2 gold badges75 silver badges105 bronze badges

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

1 Comment

kekchoze Over a year ago

Thanks for the quick response Elliott. Got it to work using standard SQL and calling my functions in two separate UDF files via the OPTIONS clause. Note that I had to lower case my if in my IF statements in the JS functions as it's case sensitive (contrary to if you use a UDF with legacy SQL). This solution works. However still keen to understand how we can use it with legacy SQL.

kekchoze · Accepted Answer · 2017-05-11 22:01:01Z

The query I wanted to run was to categorise traffic and sales by marketing channel which I usually use a UDF for. This is the query I ran using standard SQL. This query is stored in a file which I read and store in the variable sql:

CREATE TEMPORARY FUNCTION
  mktchannels(source STRING,
    medium STRING,
    campaign STRING)
  RETURNS STRING
  LANGUAGE js AS """
return channelGrouping(source,medium,campaign) // where channelGrouping is the function in my channelgrouping.js file which contains the attribution rules
  """ OPTIONS ( library=["gs://[bucket]/[path]/regex.js",
    "gs://[bucket]/[path]/channelgrouping.js"] );
WITH
  traffic AS ( // select fields from the BigQuery table
  SELECT
    device.deviceCategory AS device,
    trafficSource.source AS source,
    trafficSource.medium AS medium,
    trafficSource.campaign AS campaign,
    SUM(totals.visits) AS sessions,
    SUM(totals.transactionRevenue)/1e6 as revenue,
    SUM(totals.transactions) as transactions
  FROM
    `[datasetId].[table]`
  GROUP BY
    device,
    source,
    medium,
    campaign)
SELECT
  mktchannels(source,
    medium,
    campaign) AS channel, // call the temp function set above
  device,
  SUM(sessions) AS sessions,
  SUM(transactions) as transactions,
  ROUND(SUM(revenue),2) as revenue
FROM
  traffic
GROUP BY
  device,
  channel
ORDER BY
  channel,
  device;

And then in the Python script:

fd = file('myquery.sql', 'r')
sql = fd.read()
fd.close()

query_data = {
    'query': sql,
    'maximumBillingTier': 10,
    'useLegacySql': False,
    'timeoutMs': 300000
}

Hope this helps anyone in the future!

Collectives™ on Stack Overflow

How to use the BigQuery API using a Python script calling a UDF

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related