<jupyter_start><jupyter_text>UDFs in BigQueryThis notebook shows how to use UDFs (user-defined functions) in Google BigQuery. UDFs allow you to operate on columns of a table, performing arbitrary transformations, and returning the result of those transformations as a value. Datalab currently supports temporary UDFS, which live only within the query that uses them.You can read more about UDFs [here](https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions)  ScenarioIn this notebook we are going to look at anonymized logs that originated in Google AppEngine. These logs include the paths of requested URIs, which contain a number of query parameters of interest. To help us use the logs, we will create a UDF that extracts the query parameters values, and puts them into a new column of type `ARRAY`.  Examining the DataWe are going to look at logs from a week in October 2015. These logs were imported from Google AppEngine, with a few relevant fields extracted and anonymized. There is a separate table for each day. Let's look at an example, starting with the schema, and then look at sample rows:<jupyter_code>%bq tables describe --name cloud-datalab-samples.appenginelogs.sample_logs_20151027
%bq sample --count 5 --table cloud-datalab-samples.appenginelogs.sample_logs_20151027<jupyter_output><empty_output><jupyter_text>You can see we have five columns; the 'path' column needs the most processing. Each URI has the form `log/event?params`, where params can be one of project, instance, user, page, path, version, or release. We are going to extract these values into a separate column of type `ARRAY`.  Creating and Testing the UDFUDFs are functions written in one of the supported languages (currently SQL and Javascript), that take a column and produces a value, after performing some computation. The BigQuery UDF documentation explains that the `CREATE TEMP FUNCTION` call is needed to define a UDF, including its parameter names and types, return type, and language. Datalab simplifies this syntax; it makes use of jsdoc-style `// @param` comments to achieve the same result. Also, it exposes UDFs as a Python class, and a magic command, to make building queries simpler. Let's see how we can do this:<jupyter_code>%%bq query
SELECT * FROM `cloud-datalab-samples.appenginelogs.sample_logs_20151027`
LIMIT 5
%%bq udf --name extract_params -l js
// A function to split a set of URL query parameters into an array
// @param path STRING
// @returns ARRAY<STRING>
var re = /[?&]([^=]*)=([^&]*)/g;
var result = [];
var match;
while ((match = re.exec(path)) != null) {
  result.push(decodeURIComponent(match[2]));
}
return result;<jupyter_output><empty_output><jupyter_text>Now we can try calling the UDF. We need to define a query to do this. We can call our UDF like any regular function call, taking one or more columns as input.<jupyter_code>%%bq query -n extract_params_query --udfs extract_params
SELECT *, extract_params(path) as parameters FROM `cloud-datalab-samples.appenginelogs.sample_logs_20151027`
LIMIT 5
%bq execute -q extract_params_query<jupyter_output><empty_output><jupyter_text>In order to see the actual expanded SQL, including the UDF defined above, we can inspect the query object, by typing its name:<jupyter_code>extract_params_query<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0703336339044184, 'fraction_numerical': 0.012022843402464683, 'mean_word_length': 5.2556390977443606, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 18, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '437541', 'license': 'Apache-2.0', 'n_tokens_mistral': 956, 'n_tokens_neox': 907, 'n_words': 439, 'path': 'docs/tutorials/BigQuery/UDFs in BigQuery.ipynb', 'repo_name': 'JeffersonH44/datalab-r', 'size': '3327'}