Skip to main content

Datometry Documentation

Override Partitions Using the CREATE TABLE Statement

You can override the default partition creation using the Hyper-Q hint synapse_table_options_override.

Partitions are horizontal table slices that allow you to apply operations to subsets of rows regardless of whether the table is stored as a heap, clustered index, or clustered columnstore index (CCI). Unlike the distribution column, table partitions don't determine the distribution where each row is stored. Instead, table partitions determine how the rows are grouped and stored within each distribution.

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

To learn about partitioning table, see Partitioning tables in dedicated SQL pool 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 synapse_table_partitions_override is:

synapse_table_partitions_override = partition_clause

where:

Parameter

Description

partition_clause

The table partition clause uses the same syntax as the Azure Synapse Analytics PARTITION clause in the CREATE TABLE statement. The syntax of the PARTITION clause is:

( partition_column_name RANGE [ LEFT | RIGHT ] FOR VALUES ( [ boundary_value [,...n] ] ))

To learn about the Azure Synapse Analytics table partition options, see Table partition options in the Microsoft documentation.

Hint Scope

The synapse_table_partitions_override hint can be specified at the statement 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 12. Specify that Hyper-Q use SHOW TABLE as a Query Hint

The following Teradata CREATE TABLE statement applies the synapse_partitions_override hint, and defines column1 as the partitioning column with a LEFT range type using the boundary values 10 and 20.

CREATE TABLE table_name (
 -- @dtm_synapse_partitions_override=column1 RANGE LEFT FOR VALUES (10, 20)
    column1 integer,
    column2 integer,
    column3 varchar(10));

The resulting SQL statement is emulated by Hyper-Q on Azure Synapse analytics as shown below.

CREATE TABLE
      [database_name].[table_name] (
          [column1] int ,
          [column2] int ,
          [column3] varchar (10)
      )
  WITH
      (DISTRIBUTION = HASH ([column1] ) , CLUSTERED COLUMNSTORE INDEX , PARTITION (column1 RANGE LEFT FOR VALUES (10, 20) ) )

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