Override a SQL Query With a Custom Statement

Using the Hyper-Q hint override_with_predefined_statement, you can override an existing SQL query with a custom query that Hyper-Q will execute on the cloud data warehouse when the SQL client submits a specific input query. You can use this to replace a query from the on-premise database with one that performs better on the cloud data warehouse. Use the override_with_predefined_statement hint when you cannot achieve the necessary performance on the target database using standard optimization techniques, or by rewriting the original client query.

Important

The override_with_predefined_statement hint bypasses many phases of Hyper-Q emulation. For example, Hyper-Q will not perform emulations that would normally be applied such as SET table and UPI emulation. For this reason, Datometry recommends that you not use this hint in combination with other Hyper-Q hints, as the effects may be indeterminate.

Register the Query Override

To override a query, you must first store the query you want to use in place of the original in the Hyper-Q __DTM_MDSTORE_QUERY_MAP_TABLE table.

Run the original query.

Find the query in the Hyper-Q query tracer log.

Locate the query hash under the tag name (column 6) "DLE0033--Client Query Hash" that shares the same client request ID (column 3) as the client query record. In this example, the query hash is e8cb3b802caa15e19f1a18a54315d53b.

"2020-10-01 06:36:34.693","31","41230236-3983-4f45-a4f3-cf2c4322e4d1","","DLC002--Preprocessing","DLE0033--Client Query Hash","e8cb3b802caa15e19f1a18a54315d53b"
"2020-10-01 06:36:34.693","31","41230236-3983-4f45-a4f3-cf2c4322e4d1","","DLC002--Preprocessing","DLE0003--Start Client Query","SELECT 'This is the original input query';"

Insert the override query into the Hyper-Q Metadata Store.
Provide a value by which to identify the query override (in this example query_override_1), the override itself (SELECT 2), and optionally a comment describing the override.
```
INSERT INTO "__DTM_MDSTORE"."__DTM_MDSTORE_QUERY_MAP_TABLE"
VALUES('query_override_1', 'SELECT 2;', 'Override query with a constant 2.');
```
Reload the query map table in Hyper-Q.
You must restart Hyper-Q to register the query override with the Meta Data Store.
Whenever a SQL query identical to the original query is received, Hyper-Q executes the override query on the downstream database and the results are returned.
```
SELECT 'Original input query'; 

*** Query completed. One row found. One column returned. 
*** Total elapsed time was 1 second.

Override query
```

About the Query Override

The following guidelines apply to creating a query override.

When an override query is encountered, Hyper-Q bypasses all processing and the override query is submitted "as is".
This includes, but is not limited to, supporting enhanced schema properties such as non-constant default expressions. It is the responsibility of the database administrator who maintains the override queries to keep them synchronized with possible changes that may affect the format or correctness of query results. For example, if the non-constant, default expression for an underlying column changes, the respective change must be added to all overridden data manipulation language (DML) statements that INSERT or UPDATE that column.
Override queries run in the same session that the client submitting the original query has connected as. Because of this, override queries are subject to the permissions of the user for this session.
You can use the comment column of the query map table in the Hyper-Q Meta Data Store to record information.
Datometry recommends including information that helps you maintain the override queries. For example, a comment identifying the application or script that contains the original query and the reason for the override.
Datometry recommends starting with the original translation generated by Hyper-Q and modifying it as required. For example, by adding a query hint.
The override_with_predefined_statement hint is primarily designed for single SELECT statements. Other types of SQL statements may not return the correct results for the original query.
SELECT statements trigger the return of a result set, which Hyper-Q converts and returns to the client. All other statements, including DML statements such as INSERT, UPDATE, and DELETE, are reported to the client like an INSERT statement and do not return a result set.
Due to the nature of the query override feature, Hyper-Q cannot guarantee that the override query returns the correct results for the original query.

Syntax

The syntax for override_with_predefined_statement is:

override_with_predefined_statement = value for request

where:

Parameter	Description
`value`	Specifies a string identifying the query in the Hyper-Q Meta Data Store query map table _DTM_MDSTORE_QUERY_MAP_TABLE.
`for request`	You can only use this hint at the `for request` level, and you must explicitly specify this in the query.

Hint Scope

The override_with_predefined_statement hint can only be specified at the request level. To learn more about Hyper-Q SQL hints, and how to use them within a SQL query, see About Hyper-Q SQL Hints.

Example 42. Override a Query Using a Custom SQL Statement

In the following example, the Meta Data Store has the following query inserted in the __DTM_MDSTORE_QUERY_MAP_TABLE table. When a query identified by the specified identifier is intercepted, Hyper-Q fetches the query from the map and overrides the query.

INSERT INTO "__DTM_MDSTORE"."__DTM_MDSTORE_QUERY_MAP_TABLE"
VALUES('query_override_1', 'SELECT 2;', 'Override query with a constant 2.');

Using the query override above, the following SELECT statement will return a value of 2.

SELECT
  --@dtm_override_query=query_override_1 for request
1;


*** Query completed. One row found. One column returned. 
--------------------
2

To learn more about Hyper-Q SQL hints, and how to use them within a SQL query, see About Hyper-Q SQL Hints.

Datometry Documentation