Serialize Teradata Primary Indexes as Azure Synapse Clustered Indexes
Using the primary_index_as_clustered_index configuration option, you can specify that Hyper-Q serialize Teradata primary indexes as clustered indexes on Azure Synapse.
Teradata distributes data based on the primary index that you create during table creation. The primary index guarantees uniform distribution of table rows across all Access Module Processors (AMP), which manages the database, performs file tasks, and manipulates the disk subsystem.
Hyper-Q uses all of the Teradata primary index columns with the exception of the wide columns that Azure Synapse does not support using the clustered index column.
To learn more about table structure in Azure Synapse, see CREATE TABLE (Azure Synapse Analytics) and Indexing dedicated SQL pool tables in Azure Synapse Analytics in the Microsoft documentation.
The syntax for primary_index_as_clustered_index is:
primary_index_as_clustered_index = true | false
-or-
dtm set primary_index_as_clustered_index = on | off
where:
Parameter | Description |
|---|---|
| Specifies that Hyper-Q serialize the Teradata primary indexes as Azure Synapse clustered indexes. |
| Specifies that Hyper-Q generate a table layout using either HEAP or CLUSTERED COLUMNSTORE INDEX. |
You can specify primary_index_as_clustered_index as a global setting in the Hyper-Q configuration file (dtm.ini), as part of a query using @dtm_ , or using the dtm set command.
You can specify primary_index_as_clustered_index as a global setting in the Hyper-Q configuration file (dtm.ini).
"gateway".primary_index_as_clustered_index = true
You can specify primary_index_as_clustered_index as a Hyper-Q hint at the request level.
-- @dtm_primary_index_as_clustered_index = true
When you specify primary_index_as_clustered_index as a Hyper-Q hint at the request level, it overrides the setting in the dtm.ini file.
You can enable and disable primary_index_as_clustered_index using the dtm set command on a per session basis. When specifying the configuration using the dtm set command, use the options on | off to enable and disable converting Teradata primary indexes as Azure Synapse clustered indexes.
Using dtm set you can write a script to create tables and specify that Hyper-Q serialize the Teradata primary indexes as Synapse clustered indexes as necessary. When specifying an option using dtm set, it overrides the setting in the dtm.ini file, and will do so until you turn off the conversion of Teradata primary indexes as Azure Synapse clustered indexes.
dtm set primary_index_as_clustered_index on;
To disable the session and revert back to the setting as specified in the dtm.ini file, use dtm set primary_index_as_clustered_index off.
dtm set primary_index_as_clustered_index off;