Query Signature
Lookup table used in Lookup Routing maps queries to warehouse. Sundeck generates a query signature for each incoming query and matches it against queries in the lookup table.
Sundeck Opscenter has utility functions related to query signatures and lookup tables. These functions are listed below
has_signature(<dbName>, <schemaName>, <queryText>) RETURNS (TRUE OR ERROR)
<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 True if signature can be calculated otherwise throws an error explaining why signature can’t be calculated
| Function | Return | Comment |
|---|---|---|
has_signature(null, null, select * from db.schema.table where count > 1) | True | |
has_signature(null, null, select * from schema.table where count > 1) | Error | Error because no database specified |
has_signature(null, null, select * from db.schema.table count > 1) | Error | Error specifying where is parse error in query |
signatures_match(<dbName1>, <schemaName1>, <queryText1>, <dbName2>, <schemaName2>, <queryText2>) RETURNS (BOOLEAN OR ERROR)
<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.
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.
| Function | Return | Comment |
|---|---|---|
signatures_match(null, null, select * from d.s.t where count > 1, null, null, "select * from d.s.t where count > 5") | True | literals 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") | False | Signature for different tables will be different |
signatures_match(null, null, select * from schema.table count > 1, null, null, "select 123") | Error | Error indicating first query error details |
signature_target(<dbName>, <schemaName>, <queryText>, <lookupTableDbName>, <lookupTableSchemaName>, <lookupTableTableName>) RETURNS (STRING OR ERROR)
<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.
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.
If lookup table “LD.LS.LT” has following entries
| QUERY_TEXT | Warehouse | Comment |
|---|---|---|
select 123 | COMPUTE_WH | |
select * from d.s.t where count > 1 | ROUTE_WH | |
Incorrect query | Error | Invalid entry, ignored for comparision |
Then following are some example of function signature_target
| Function | Return | Comment |
|---|---|---|
signature_target("d", "s", select * from t where count > 5, "LD", "LS", "LT") | ROUTE_WH | literals are ignored in query match |
signature_target("d", "s", "select col1 form t", "LD", "LS", "LT") | Null | Query doesn't match to entry in lookup table |
signature_target("d", "s", "select 123", "NP", "NP", "NP") | Error | Error, pin table not present |