ENH Add user defined function support to read_gbq
State: closed

Added support for BigQuery's new user defined function (UDF) feature. See https://cloud.google.com/bigquery/user-defined-functions#api for details.


Labels:

Comments:
this is not very intuitive in the API. can you show an example use case?

Google has added support for JS based user defined functions. They essentially perform a map operation on rows fed into them. You could use one, for example, to parse a useragent out to browser/os/version/etc. They're stored as .js files in Google Cloud Storage, and in order to use them, the URI for the source must be included in the query data. All of the parsing happens in the BigQuery engine. This pull enables a user to include a list of source URIs for any UDFs used in their queries.

The web client has added a control to add these URIs, so this shouldn't be a completely foreign thing to BQ users.
![j2kr5q9](https://cloud.githubusercontent.com/assets/1209723/9926036/8c3fc802-5cc6-11e5-8be5-c9fbd30dc4c2.png)

what you added does not appear to actually do anything or be passed any where. how can one tell if this doing anything?

When a list of URIs is passed to read_gbq, the userDefinedFunctionResources object is added to the job data as a list of resourceUri objects per the spec in the BQ API docs. If the user doesn't include any URIs, defaulting to the empty list bypasses adding this data to the job data.

[Here](https://gist.github.com/cgrin/6110092b1c2a7dc91bbf) is an example of a UDF source file. This one implements a function `uaDecode` based on [UAParser.js](http://faisalman.github.io/ua-parser-js/) taking a single column `user_agent` and parsing it to return a single column `browser`.

To use it you include the URI in the job config and submit a query like this

``` sql
    select
        browser
    from
        uaDecode(select userAgent from logs)
```

EDIT: The BQ specific parts are the very top and very bottom. The middle is just UAParser.js

I have no problem with the functionality 
but he API

would rather have u pass maybe a parm, call it query that is directly passed in
eg the user would format it (in this case would be a dict of list of dicts)
very JSONy

Are you suggesting the user should create the entire userDefinedFunctionResources object and pass something like this to read_gbq?

``` python
[{'resourceUri':'uri1'},
{'resourceUri':'uri2'},
{'resourceUri':'uri3'},]
```

yep, I would simply have the user format these.

I suppose that would enable the inline udf functionality at the same time, but will lead to repeated code for the user. What about changing the parameter name to `udf_resources` and adding `inlineCode` objects when the string doesn't begin with `gs://`? The read_gbq method is already building the job_data json object, so it's not unprecedented to obscure the details of the BQ api.

I would like to see some more use on this before we starting adding, IMHO, very specific keyword functionaility. This is easily added by the user.

I am currently using user defined functions(UDF) in pandas 0.17.0. I just enter the UDF as part of the BigQuery query string. Its possible that users can just use the `read_gbq()` function as is. 

I used the following link as a reference to construct my query string: 

http://stackoverflow.com/questions/32208958/cannot-pass-input-field-of-repeated-record-type-into-bigquery-udf

I think this is better left to a cookbook (or documentation recipe). @cgrin if you'd like to do that, or propose a more generic API for this, pls reopen