Documentation
Toggle Dark/Light/Auto modeToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeBack to homepage

Query Signature

The Lookup Table used in Lookup Routing maps individual SQL queries (by query signature) to target warehouse. The Sundeck Broker generates a query signature for each incoming query, and checks the lookup table before sending the query to Snowflake; if the query signature is found in the lookup table then Sundeck redirects the query to the listed warehouse before submission.

The Sundeck Natiave App includes the following utility functions related to query signatures and lookup tables:

Syntax

has_signature(<dbName>, <schemaName>, <queryText>) RETURNS (TRUE OR ERROR)

Arguments

<dbName>
For table references in the query that are not fully qualified, this value is used as the database
<schemaName>
For table references in the query that are not fully qualified, this value is used as the schema
<queryText>
querytext to calculate signature of.

Returns

Returns True if signature can be calculated otherwise throws an error explaining why signature can’t be calculated

Examples

FunctionReturnComment
has_signature(null, null, select * from db.schema.table where count > 1)True
has_signature(null, null, select * from schema.table where count > 1)ErrorError because no database specified
has_signature(null, null, select * from db.schema.table count > 1)ErrorError specifying where is parse error in query

Syntax

signatures_match(<dbName1>, <schemaName1>, <queryText1>, <dbName2>, <schemaName2>, <queryText2>) RETURNS (BOOLEAN OR ERROR)

Arguments

<dbName1>
For table references in the first query that are not fully qualified, this value is used as the database
<schemaName1>
For table references in the first query that are not fully qualified, this value is used as the schema.
<queryText1>
first query’s text.
<dbName2>
For table references in the second query that are not fully qualified, this value is used as the database
<schemaName2>
For table references in the second query that are not fully qualified, this value is used as the schema
<queryText2>
second query’s text.

Returns

True – if signature for both queries can be calculated and they match.

False – if signature for both of the queries can be calculated and they differ.

Error – if signature for any of the queries can’t be calculated.

Examples

FunctionReturnComment
signatures_match(null, null, select * from d.s.t where count > 1, null, null, "select * from d.s.t where count > 5")Trueliterals are ignored in signature calculation
signatures_match(null, null, select * from d.s.t where count > 1, null, null, "select * from d.s.t1 where count > 1")FalseSignature for different tables will be different
signatures_match(null, null, select * from schema.table count > 1, null, null, "select 123")ErrorError indicating first query error details

Syntax

signature_target(<dbName>, <schemaName>, <queryText>, <lookupTableDbName>, <lookupTableSchemaName>, <lookupTableTableName>) RETURNS (STRING OR ERROR)

Arguments

<dbName>
For table references in the query that are not fully qualified, this value is used as the database
<schemaName>
For table references in the query that are not fully qualified, this value is used as the schema
<queryText>
query text to lookup to in lookup table.
<lookupTableDbName>
lookupTable Database name.
<lookupTableSchemaName>
lookupTable Schema name.
<lookupTableTableName>
lookupTable Table name.

Returns

Warehouse name if query matches lookup table query or null if there is no match. Error is returned if there is an error encountered e.g. lookup table is not present.

Examples

If lookup table “LD.LS.LT” has following entries

QUERY_TEXTWarehouseComment
select 123COMPUTE_WH
select * from d.s.t where count > 1ROUTE_WH
Incorrect queryErrorInvalid entry, ignored for comparision

Then following are some example of function signature_target

FunctionReturnComment
signature_target("d", "s", select * from t where count > 5, "LD", "LS", "LT")ROUTE_WHliterals are ignored in query match
signature_target("d", "s", "select col1 form t", "LD", "LS", "LT")NullQuery doesn't match to entry in lookup table
signature_target("d", "s", "select 123", "NP", "NP", "NP")ErrorError, pin table not present