Datometry Documentation

The physical_props_in_show_table hint lets you display applicable hints related to the distribution and physical layout in SHOW TABLE output. The output returned by SHOW TABLE is the Data Definition Language (DDL) for the specified table. Using the information returned by SHOW TABLE, you can recreate a table in Azure Synapse using the exact same table definitions.

In the following example, we create a table and then use SHOW TABLE to display the distribution and physical layout properties with which it was created.

CREATE MULTISET TABLE  table_name
    -- @dtm_override_distribution_style=replicate
    -- @dtm_override_table_layout=clustered_index
    -- @dtm_override_table_layout_cols=col1,col2,col3,col4,col5,col6,col7,col8,col9
    -- @dtm_synapse_partitions_override=col1 RANGE LEFT FOR VALUES ('10','20')
    (
        col1 integer ,
        col2 integer ,
        col3 integer ,
        col4 integer ,
        col5 integer ,
        col6 integer ,
        col7 integer ,
        col8 integer ,
        col9 integer
    );

Below is the SHOW TABLE output generated when the Hyper-Q hint physical_props_in_show_table is enabled.

SHOW TABLE table_name;
CREATE MULTISET TABLE table_name
    -- @dtm_override_distribution_style=replicate
    -- @dtm_override_table_layout=clustered_index
    -- @dtm_override_table_layout_cols=col1,col2,col3,col4,col5,col6,col7,col8,col9
    -- @dtm_synapse_partitions_override=col1 RANGE LEFT FOR VALUES ('10','20')
    (
        col1 integer ,
        col2 integer ,
        col3 integer ,
        col4 integer ,
        col5 integer ,
        col6 integer ,
        col7 integer ,
        col8 integer ,
        col9 integer
    )
    NO PRIMARY INDEX
    INDEX ClusteredIndex_e7eb2ff0f7a7498dbf90ccbd78e273c3 ( col1 , col2 , col3 , col4 , col5 , col6 , col7 , col8 , col9 );

When you run SHOW TABLE, the output generated by Hyper-Q displays the embedded Hyper-Q hints for Azure Synapse distribution, layout, and partitioning of the native table on Azure Synapse that is created with the identical distribution, layout, and partitioning as the original table. The Teradata-specific properties are stored in the Hyper-Q Metadata Store and are identical to the original table on which the SHOW TABLE command was run.

The syntax for physical_props_in_show_table is:

physical_props_in_show_table = true | false

-or-

dtm set physical_props_in_show_table = on | off

where:

Hint Scope

You can specify physical_props_in_show_table as an application (global) level setting in the Hyper-Q configuration file (dtm.ini ), as a Hyper-Q hint within a SQL query, or using the dtm set command.

CREATE TABLE
-- @dtm_physical_props_in_show_table=true for session;
table_name
-- @dtm_override_table_layout=clustered_index
-- @dtm_override_table_layout_cols=col1,col2
-- @dtm_override_distribution_style=replicate
        (
        column1 integer ,
        column2 integer ,
        column3 integer 
    );;

dtm set physical_props_in_show_table on

CREATE TABLE table_name
-- @dtm_override_table_layout=clustered_index
-- @dtm_override_table_layout_cols=col1,col2
-- @dtm_override_distribution_style=replicate
        (
        column1 integer ,
        column2 integer ,
        column3 integer 
    );

SHOW TABLE
CREATE MULTISET TABLE table_name
    (
        column1 integer ,
        column2 integer ,
        column3 integer
    )
    NO PRIMARY INDEX
    INDEX ClusteredIndex_a08635b496f44fd9864a55def0e721c3 ( col1 , col2 )


dtm set physical_props_in_show_table off;

SHOW TABLE table_name;