snowflake

Snowflake UDFs

This directory contains user-defined functions which mimic the behavior of proprietary functions in Snowflake. Each UDF within this directory will be automatically synchronized to the bqutil project within the sf dataset for reference in queries.

For example, if you'd like to reference the factorial function within your query, you can reference it like the following:

SELECT bqutil.sf.factorial(0)

UDFs

• array_equal
• factorial
• flatten

Documentation

array_equal(a ARRAY, b ARRAY)

Compares two arrays of JSON for equality, emulating Snowflake's = operator for untyped arrays.

• Returns true if arrays are of equal length and all corresponding elements are equal.
• Returns false if arrays are of different lengths or any corresponding elements are not equal.
• Returns null if either input array is null.
• Objects are compared recursively, ensuring they have the same keys and equal values (order of keys does not matter).
• Nested arrays are compared recursively.
• Null elements within arrays are treated as equal to other null elements.

SELECT bqutil.sf.array_equal([JSON '1', JSON '2'], [JSON '1', JSON '2']) as eq1,
       bqutil.sf.array_equal([JSON '{"a": 1}'], [JSON '{"a": 1}']) as eq2,
       bqutil.sf.array_equal([JSON '[1, 2]'], [JSON '[1, 3]']) as eq3;

eq1	eq2	eq3
true	true	false

factorial(integer_expr INT64)

Computes the factorial of its input. The input argument must be an integer expression in the range of 0 to 27. Due to data type differences, the maximum input value in BigQuery is smaller than in Snowflake. Snowflake docs

SELECT bqutil.sf.factorial(10)

3628800

flatten(input JSON, path STRING, outer_ BOOL, recursive_ BOOL, mode STRING)

Emulates the 'flatten' function present in Snowflake. Snowflake docs

SELECT bqutil.sf.flatten(json_object('a', 1 , 'b', json_array(77, 88), 'c', json_object('d', 'X')), '', false, true, 'both');

SEQ	KEY	PATH	INDEX	VALUE	THIS
1	a	a	null	1	{"a":1,"b":[77,88],"c":{"d":"X"}}
2	b	b	null	[77,88]	{"a":1,"b":[77,88],"c":{"d":"X"}}
1	null	b[0]	0	77	[77,88]
2	null	b[1]	1	88	[77,88]
3	c	c	null	{"d":"X"}	{"a":1,"b":[77,88],"c":{"d":"X"}}
1	d	c.d	null	"X"	{"d":"X"}

json_ilike(input JSON, pattern STRING)

Helps to emulate the SELECT {* ILIKE '%pattern%'} syntax. Snowflake docs

select
    bqutil.sf.json_ilike(to_json((select as struct subselect.*)), '%ra%') as j
  from
  (
    select 1 as xray, 2 as frame, 3 as id
  ) as subselect;

Row	j
1	{"frame":2,"xray":1}

object_agg(key STRING, value JSON)

Emulates the OBJECT_AGG function in Snowflake. Snowflake docs

SELECT object_agg(k, v)
FROM
  (
    SELECT 'a' AS k, json '1' AS v
    UNION ALL
    SELECT 'b' as k, json '2' as v);

Row	f0_
1	{"b":2,"a":1}

SELECT object_agg(k, v)
FROM
  (
    SELECT 'a' AS k, json '1' AS v
    UNION ALL
    SELECT 'a' AS k, json '3' AS b
    UNION ALL
    SELECT 'b' as k, json '2' as v);

Error: Duplicate field key 'a' at object_agg(STRING, JSON) line 7, columns 6-7