Utils

diff_query_schemas (Stored Procedure)

Diff the schemas of two queries. Especially useful when the BigQuery error is truncated, and the schemas of e.g. a UNION don't match.

Use it like:

DECLARE res ARRAY<STRUCT<i INT64, differs BOOL, a_col STRING, a_data_type STRING, b_col STRING, b_data_type STRING>>;
CALL mozfun.utils.diff_query_schemas("""SELECT * FROM a""", """SELECT * FROM b""", res);
-- See entire schema entries, if you need context
SELECT res;
-- See just the elements that differ
SELECT * FROM UNNEST(res) WHERE differs;

You'll be able to view the results of "res" to compare the schemas of the two queries, and hopefully find what doesn't match.

Parameters

INPUTS

query_a STRING, query_b STRING

OUTPUTS

res ARRAY<STRUCT<i INT64, differs BOOL, a_col STRING, a_data_type STRING, b_col STRING, b_data_type STRING>>

Source | Edit

extract_params_from_url (UDF)

Extracts every parameter in a URL or query string as key/value/depth structs. Handles nested and wholly encoded forms; read one with mozfun.map.get_key.

This UDF extracts every parameter from a URL or bare query string.

Unlike utils.extract_utm_from_url there is no fixed key list and no leading ? is required, so unanticipated parameters are still returned. Read a single parameter out of the result with mozfun.map.get_key.

Each result carries a depth, describing how deeply encoded the parameter was:

0 — a plain key=value pair, or a keyless token such as a bare click id
1 — a query string nested inside one parameter's value
2 — a whole string that was percent-encoded, containing no literal =

Where a key repeats, the shallowest occurrence wins, then the first, so mozfun.map.get_key is deterministic. Values are percent-decoded. A keyless token is returned as a key with a NULL value, so test for it by matching the key.

Keys are lowercased and must match [a-z0-9_.-], 1–60 characters, so anything else is dropped — including an encoded key such as utm%5Fsource. Values keep their case. A keyless token needs an _. A # ends the parameter before it, though parameters after it are still returned.

Parameters

INPUTS

url STRING

OUTPUTS

ARRAY<STRUCT<key STRING, value STRING, depth INT64>>

Source | Edit

extract_utm_from_url (UDF)

Extract UTM parameters from URL. Returns a STRUCT UTM (Urchin Tracking Module) parameters are URL parameters used by marketing to track the effectiveness of online marketing campaigns.

This UDF extracts UTM parameters from a URL string.

UTM (Urchin Tracking Module) parameters are URL parameters used by marketing to track the effectiveness of online marketing campaigns.

Parameters

INPUTS

url STRING

OUTPUTS

STRUCT<utm_source STRING, utm_medium STRING, utm_campaign STRING, utm_content STRING, utm_term STRING>

Source | Edit

get_url_path (UDF)

Extract the Path from a URL

This UDF extracts path from a URL string.

The path is everything after the host and before parameters. This function returns "/" if there is no path.

Parameters

INPUTS

url STRING

OUTPUTS

STRING

Source | Edit

timestamp_diff_complete_months (UDF)

Gets the number of complete months between two timestamp values (end_timestamp - start_timestamp). If end_timestamp is earlier than start_timestamp, the output is negative.

Parameters

INPUTS

end_timestamp TIMESTAMP, start_timestamp TIMESTAMP

OUTPUTS

INTEGER

Source | Edit

timestamp_diff_complete_weeks (UDF)

Gets the number of complete weeks between two timestamp values (end_timestamp - start_timestamp). If end_timestamp is earlier than start_timestamp, the output is negative.

Parameters

INPUTS

end_timestamp TIMESTAMP, start_timestamp TIMESTAMP

OUTPUTS

INTEGER

Source | Edit

url_decode (UDF)

Percent-decodes a string. A stray '%' is kept as it arrived, and '+' is not treated as a space.

This UDF percent-decodes a string. BigQuery has no native URL decode, so escapes are tokenised and converted with FROM_HEX.

Consecutive escapes are grouped into a single run before decoding, so a multi-byte UTF-8 sequence such as %C3%A9 becomes é rather than two invalid bytes. A stray % that begins no valid escape is kept as it arrived, and + is left as a literal + rather than decoded to a space, so base64 values survive intact.

Parameters

INPUTS

s STRING

Source | Edit