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.
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>>
extract_utm_from_url (UDF)
Extract UTM parameters from URL. Returns a STRUCT
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>
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