QLike
QLike is Sundeck’s powerful expression language to find similar queries and group them together. This functionality can be used in hook conditions within Flows.
Any valid Snowflake query is also a valid QLike pattern. Queries used as QLike patterns will match themselves, as well as any query for which the pattern query is a subset. For example, consider the following query as a QLike pattern:
SELECT business_name, zipcode, sic_code
FROM customers
QLike will match this pattern against any other queries which select at least these 3 columns (business_name, zipcode, sic_code) from at least the customers table.
This means that this QLike pattern will also match queries with additional columns, additional tables in JOIN clauses, WHERE clauses, GROUP BY and HAVING clauses, ORDER BY clauses … so long as the query selects the specified 3 columns from the customers table, it will be matched by the Qlike pattern. Each of the following queries will be matched by the QLike pattern shown above:
-- SQL comments and whitespace are ignored by QLike
SELECT
c.id as customer_id, c.business_name, c.zipcode, c.sic_code,
c.street_address, r.rep_name
FROM customers c
JOIN sales_reps r ON (c.sales_rep_id = r.id)
WHERE r.rep_name IN ('JOHN PROSPECTOR', 'JANE CLOSER')
SELECT
c.business_name, c.zipcode, c.sic_code,
sum(o.order_amount) as order_amount_30d
FROM customers c
JOIN orders o ON (o.customer_id = c.id)
WHERE o.order_date
between dateadd('day', -30, o.order_date)
and o.order_date
GROUP BY c.business_name, c.zipcode, c.sic_code
ORDER BY order_amount_30d desc, c.business_name
QLike patterns can be further generalized to match a broader range of queries, by selectively replacing parts of a QLike pattern query with question marks (?). Question marks are evaluated like wildcards, and serve to broaden matching behavior. We can broaden the QLike pattern from our first example to express “match any SELECT query so long as it includes the CUSTOMERS table”, like this:
SELECT ? FROM customers
QLike pattern generalization allows for the expression of higher-order matching concepts, such as “find all queries against the customers table which include a WHERE clause containing an OR expression”:
SELECT ? FROM customers WHERE ? OR ?
To find all aggregate queries (regardless of which tables or columns are being queried), we can look for SELECT statements which contain GROUP BY clauses:
SELECT ? GROUP BY ?
Note that question marks (?) can also be used in QLike patters in place of expressions in functions, as well:
SELECT DATEADD(MONTH, 1, ?) FROM customers WHERE ?
A great way to understand how QLike works is to explore a handful of examples:
| QLike Pattern | Will Match | Will Not Match |
|---|---|---|
select ? from foo | select * from foo where a < 5 select * from foo where /* my comment */ a < 5 | |
select a from foo | select a + 5 from foo where a < 5 select a as b from foo where a < 5 | select b from foo where a < 5 |
select ? from foo | select * from bar join foo on a = b | |
select ? from foo,bar | select * from bar join foo on a = b | |
select ? from foo where ? | select a from foo where c | select a from foo |
select ? from foo where c = ? | select a from foo where c = 4 | |
select ? from foo where b | select a from foo where c = 4 | |
select ? from foo group by ? | select a, sum(b) from foo group by a | |
select ? from foo group by b | select a, sum(b) from foo group by a |
QLike is also fully integrated into Sundeck’s condition expression language for pre- and post-hooks, exposed as the function SQL_QLIKE.