Skip to main content

Datometry Documentation

About Hyper-Q SQL Hints

Hints are an addition to SQL that allow you to instruct the database query processor how to execute a given query. Hints are typically embedded in each statement that references the objects listed in the hints.

This topic describes:

  • How to apply a hint to specific SQL statement.

  • As a global setting that applies to all SQL statements using Hyper-Q

  • The scope and scope precedence with which you can specify a degree of control over statement execution plans using hints.

Apply a Hyper-Q SQL Hint

To apply a hint within a SQL query, prepend --@dtm_ to the hint name. For example: --@dtm_stats_on_temp_tables=true

Within a SQL statement, this appears as:

SELECT * FROM (SELECT CASE WHEN i=1 then (CASE WHEN j =2 then CASE WHEN k=3
--@dtm_stats_on_temp_tables=true
then 1 else 0 end else 0 end) else 0 end as x, a, b, c from test1, test2)
 t
WHERE t.a=1 and t.b=2;

You can specify Hyper-Q hints as a global setting at the application level in the the Hyper-Q configuration file (dtm.ini). When set as an application level setting, the option you specify is applied to all SQL statements processed using Hyper-Q. When specified as a gateway setting with the dtm.ini configuration file, this appears as:

"gateway".stats_on_temp_tables=true
Add Hints Inline with the SQL Statement

You add Hyper-Q hints inline with the SQL statement to which you want to apply it.

SELECT
    --@dtm_hint_name=parameter_value 
    * FROM table_name;

Warning

Do not apply hints outside of the SQL statement as shown in the following examples.

--@dtm_hint_name=parameter_value
SELECT * FROM table_name;
SELECT * FROM table_name;
--@dtm_hint_name=parameter_value
SELECT * FROM table_name
--@dtm_hint_name=parameter_value 
;
Add Hints to Views

When adding a hint to a view, you must add the hint to the view query.

CREATE TABLE table_name (i int);
CREATE VIEW view_name as (SELECT * FROM table_name);

SELECT
    --@dtm_hint_name=parameter_value 
    * FROM view_name;

Similarly, if you are applying a hint to a recursive query add the hint to the view query.

CREATE TABLE schema_name.recursive_view_base_table (i int);
CREATE RECURSIVE VIEW recursive_view_name (i, DEPTH) as
(
    SEL i, 0 as DEPTH
    FROM schema_name.recursive_view_base_table
    UNION ALL
    SEL i, DEPTH+1 FROM 
    recview2
    WHERE DEPTH < 3
);

SELECT * 
    --@dtm_suggest_table_for_temp_table_distr_cols=test_schema.recursive_view_base_table
    FROM recursive_view_name;

Warning

Do not apply hints to view definitions.

CREATE TABLE table_name (i int);
CREATE VIEW view_name as (
SELECT * 
     --@dtm_hint_name=parameter_value -- Will fail.
      FROM table_name);

SELECT * FROM view_name;
Add Hints to Macros and Stored Procedures

Hyper-Q does not support scope-overrides when using the EXEC command to execute a stored procedure or a SQL macro.

CREATE TABLE table_name (i int);
CREATE MACRO macro_name as (
    --@dtm_hint_name=parameter_value  
    SELECT * FROM table_name
    ; SELECT * FROM table_name;
);

EXEC macro_name;

Warning

You cannot apply a hint to the EXEC call in the the SQL statement.

CREATE TABLE table_name (i int);
CREATE MACRO macro_name as (
    SELECT * FROM table_name
    ; SELECT * FROM table_name;  
);

--@dtm_hint_name=parameter_value  -- Will fail.
EXEC macro_name;
Add Hints in the Body of a DDL Statement

When adding a hint to the body of a DDL statement, add the hint near the SELECT statement of the query.

CREATE TABLE table_name (i int);
CREATE MACRO macro_name as (
    --@dtm_hint_name=parameter_value
    SELECT * FROM table_name
    ; SELECT * FROM table_name;
);

EXEC macro_name;

Warning

Do not add hints to DDL statements in either the CREATE or REPLACE clause.

CREATE TABLE table_name (i int);
CREATE MACRO 
    --@dtm_hint_name=parameter_value. -- Will fail.
    macro_name as (
    SELECT * FROM table_name
    ; SELECT * FROM table_name;
);

EXEC macro_name;
Specify Hints in the Hyper-Q Configuration File

Many Hyper-Q SQL hints can be set in the Hyper-Q configuration file (dtm.ini). When specified in the dtm.ini configuration file, the hint applies to all SQL statements processed through Hyper-Q. To learn more, see Hyper-Q Configuration for Google BigQuery.

Disable a Hyper-Q Hint Specified at the Application Level

You can disable (or override) a Hyper-Q query hint that is specified at the application level (globally). For example, if a query hint is specified in the Hyper-Q configuration file (dtm.ini), you can disable this behavior for an individual query by setting the hint to unset.

In the following example the hint add_synapse_query_options is set to FORCE ORDER in the dtm.ini file.

"gateway".add_synapse_query_options="FORCE ORDER"

You can override this behavior for an individual query by setting the hint to unset.

SELECT
--@dtm_add_synapse_query_options=unset
-1;

Hyper-Q Hints and Scope

You can apply Hyper-Q hints to different scope levels.

  • Statement

    Statement (or query) scope hints apply to the entire SQL statement to which they are associated. Because statement level hints apply on a per-statement basis, they provide finer control over statement execution plans than can be achieved using hints at higher scope levels. For example, you can enable an optimization for one table in a statement and disable the optimization for a different table.

  • Request

    Request scope hints apply to all SQL statements in a given multi-statement request (MSR). Certain hints are only request level and higher. For example, all bulk loading hints accept only for request, for session, or application level scope.

  • Session

    Session hints let you specify hints at the session scope, as opposed to passing them for each individual SQL statement. Thus, all queries sent to the database for a given session will use the hint you provide.

  • Application

    Application level hints are specified in the gateway section of the Hyper-Q configuration file (dtm.ini). When specified in the dtm.ini configuration file, the hint applies to all SQL statements processed through Hyper-Q. To learn more, see Hyper-Q Configuration for Google BigQuery.

The syntax with which to specify the scope of a hint is:

@dtm_hint_name=parameter_value for session -- session scope
@dtm_hint_name=parameter_value for request -- request scope
@dtm_hint_name=parameter_value for query   -- statement scope
@dtm_hint_name=parameter_value             -- statement scope
Hyper-Q Hint Scope Precedence

Hints specified at higher level scope yield to hints specified at a lower level scope. Hints specified at a lower level apply to all scopes at a higher level.

The precedence for Hyper-Q hints is: query → request → session → application → default behavior

For example, if a hint is specified at the application (or gateway) scope, and a query has the same hint with a different value specified, the hint on the query (statement level) takes precedence.

Statement Level Hints

If you do not apply a scope modifier to a SQL statement, it defaults to the statement level scope. You can explicitly apply a statement level scope using the for query modifier. In both of the following examples, the statement level scope is applied to the second SQL statement, SELECT 2. All other SELECT statements are unaffected by the hint.

SELECT 1
; SELECT 
  --@dtm_hint_name=parameter_value
  2
; SELECT 3
;

Explicitly apply the statement level scope to a SQL statement using the for query modifier.

SELECT 1
; SELECT 
  --@dtm_hint_name=parameter_value for query
  2
; SELECT 3
;
Request Level Hints

To specify that a hint apply to all SQL statements in a multi-statement request (MSR), use the for request modifier.

Certain hints are only request level and higher. For example, all bulk loading hints accept only for request, for session, or application level scope. Additionally, if a hint is request level or greater in scope, failing to add the for request modifier will cause the hint to not be applied to the SQL statement.

Note

If a hint is applied to a multi-statement request and does not explicitly apply the for request modifier, the scope of the hint defaults to the statement scope level.

SELECT 1
  --@dtm_hint_name=parameter_value for request
; SELECT 2
; SELECT 3
;

If you specify a hint in the middle of a multi-statement request using the for request modifier, the hint applies to all statements in the request. In the following example, the hint applies to the SELECT 1 statement as well as the SELECT 2 and SELECT 3 statements.

SELECT 1
; SELECT 
  --@dtm_hint_name=parameter_value for request
2
; SELECT 3
;
Request Level Hints and Stored Procedures

The EXEC (or EXECUTE) command is used to execute a stored procedure or macro. EXEC is equivalent to using the for request modifier.

In the following example, the for query modifier (statement level) applies the hint to the body of the the macro.

CREATE TABLE table_name (i int);
CREATE MACRO macro_name as (
    --@dtm_hint_name=parameter_value for query
    SELECT * FROM table_name
    ; SELECT * FROM table_name;
);

exec macro_name;

If you use the for request modifier (request scope), the hint you specify applies to all the statements that make up the macro's multi-statement request.

CREATE TABLE table_name (i int);
CREATE MACRO macro_name as (
    --@dtm_hint_name=parameter_value for request
    SELECT * FROM table_name
    ; SELECT * FROM table_name;
);
exec macro_name;
Session Level Hints

Session hints let you specify hints at the session scope, as opposed to passing them for each individual statement. Thus, all queries sent to the database using a given session will use the hints you provide. The hint will affect only those queries sent from the time the session hint is set. Any earlier queries in the same session are unaffected.

The for session modifier applies to the entirety of a request, and to all statements that follow. In the following example, the for session modifier is applied to the second SELECT statement (SELECT 2), and applies to all those that follow it (SELECT 3 and SELECT 4)

SELECT 1;
SELECT 2; 
SELECT 3 --@dtm_hint_name=parameter_value for session
;
SELECT 4;