Skip to main content

Datometry Documentation

Override Column Distribution Using the CREATE TABLE Statement

The default behavior of Hyper-Q is to serialize columns defined as a Teradata primary index as Azure Synapse Analytics distribution columns. You can override the distribution of columns using the Hyper-Q hint override_distribution_cols.

Related to override_distribution_cols is the hint override_distribution_style, which lets you override the table distribution. For example, you can specify that the CREATE TABLE statement create hash-distributed or round-robin distributed tables. To learn about override_distribution_style, see Override Table Distribution Using the CREATE TABLE Statement.

To learn about the syntax and usage of the Azure Synapse Analytics CREATE TABLE statement, see CREATE TABLE in the Microsoft documentation.

See Also

The following Hyper-Q hints allow you to override the default Hyper-Q behavior, and provide precise control over Azure Synapse Analytics table creation options.

  • Override the CREATE TABLE Statement Override the CREATE TABLE Statement You can override the default Hyper-Q table creation behavior using the synapse_table_options_override hint. Use this in situations in which you require precise control over table creation options in Azure Synapse Analytics.

  • Override Table Distribution Using the CREATE TABLE Statement  You can override table distribution using the Hyper-Q hint override_distribution_style. For example, you can specify that the CREATE TABLE statement create hash-distributed or round-robin distributed tables.

  • Override Column Distribution Using the CREATE TABLE Statement The default behavior of Hyper-Q is to use the columns defined as a Teradata primary index as Azure Synapse Analytics distribution columns. You can override the distribution of columns using the Hyper-Q hint override_distribution_cols.

Hint Syntax

The syntax for override_distribution_col is:

override_distribution_col=column_n,column_m,...

where:

Parameter

Description

column_n

A comma separated list of column names. For example:

override_distribution_col=column_1,column_2

Hint Scope

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

Example 9. Override the Default Distribution Column

The default behavior of Hyper-Q is to use columns defined as a Teradata primary index as the Azure Synapse Analytics distribution column. You can override this behavior. The following example specifies that the table use column2 as the distribution column.

CREATE TABLE table_name (
 -- @dtm_override_distribution_cols=column2
    column1 integer,
    column2 integer,
    column3 varchar(10));

The resulting CREATE TABLE statement emulated by Hyper-Q for Azure Synapse is as follows.

CREATE TABLE
    [database_name].[table_name] (
        [column1] int,
        [column2] int,
        [column3] varchar (10)
    )
WITH
    (DISTRIBUTION = HASH ( [column2] ) , CLUSTERED COLUMNSTORE INDEX )

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



Example 10. Specify Multiple Distribution Columns

The following example specifies that the table use column3, column2 and column1, as the distribution columns.

CREATE TABLE table_name (
 -- @dtm_override_distribution_cols=column3,column2,column1
    column1 integer,
    column2 integer,
    column3 varchar(10));

The resulting CREATE TABLE statement emulated by Hyper-Q for Azure Synapse is as follows.

CREATE TABLE
    [database_name].[table_name] (
        [column1] int,
        [column2] int,
        [column3] varchar (10)
    )
WITH
    (DISTRIBUTION = HASH ([column3] , [column2] , [column1] ) , CLUSTERED COLUMNSTORE INDEX )


Example 11. Specify Multiple Hints to Override Table Creation Behavior in a Single Statement

You can apply several hints to override the default table creation behavior to a single CREATE TABLE statement. In the following example a Teradata CREATE MULTISET TABLE statement is overridden using both the override_distribution_style=hash and override_distribution_cols=column1,column2 hints. The statement creates a new table from an existing table using the source table's data.

CREATE
@dtm_override_distribution_style=hash
@dtm_override_distribution_cols=column1,column2
MULTISET TABLE
table_name_1 AS table_name_2
with data;

The resulting CREATE MULTISET TABLE statement emulated by Hyper-Q for Azure Synapse is as follows. The distribution style is HASH using column1 and column2 as the distribution columns as specified by the hints.

CREATE TABLE
        [database_name].[table_name]
    WITH
        (DISTRIBUTION = HASH ([col1] , [col2] ) , CLUSTERED COLUMNSTORE INDEX ) AS
        (SELECT
            [TAB1].[col1] AS [col1] ,
            [TAB1].[col2] AS [col2]
        FROM
            [database_name].[table_name_2] AS [TAB1]
        )