Skip to main content

Datometry Documentation

Override the CREATE TABLE Statement

You can override the default table creation behavior using the hint synapse_table_options_override. Use this in situations in which you require precise control over table creation options in Azure Synapse Analytics.

Using synapse_table_options_override, you can reference any of the valid table options in the Azure Synapse Analytics CREATE TABLE statement. Hyper-Q adds these options as you specify them in the CREATE TABLE statement, and passes them directly to Azure Synapse Analytics.

Important

Some SQL clients remove comments before the statement body. For this reason, Datometry recommends that you include the comment with a hint inside the statement body to ensure that is passed through to Hyper-Q. For more information refer to the examples below.

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 the CREATE TABLE table options that you can pass using the synapse_table_options_override hint, see the Microsoft article CREATE TABLE (Azure Synapse Analytics).

Error Reporting in Azure Synapse Analytics

Because the table options you specify in the synapse_table_options_override hint are passed "as is" to Azure Synapse Analytics, it returns errors as if you were using a native CREATE TABLE statement. For this reason, you must respond to these error messages as though they are native Azure Synapse Analytics errors, and not Hyper-Q errors.

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.

Hint Syntax

The syntax for synapse_table_options_override is:

synapse_table_options_override = table_options

where:

Parameter

table_options

The table override options use the same syntax as the Azure Synapse Analytics CREATE TABLE statement. To learn about the Azure Synapse Analytics table creation options, see CREATE TABLE in the Microsoft documentation.

Hint Scope

The synapse_table_options_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 4. Specify a Hash Distribution Column in Azure Synapse

You can use the synapse_table_options_override hint to select a specific column among the multiple columns in the primary index from the Teradata definition as the HASH distribution column in Azure Synapse Analytics. In this example, of the three columns designated as the primary index in the Teradata source statement, column2 is chosen as the HASH distribution column in the resulting table on Azure Synapse.

CREATE TABLE table_name ( 
-- @dtm_synapse_table_options_override=DISTRIBUTION = HASH(column2)      
    column1 INTEGER,      
    column2 INTEGER,      
    column3 INTEGER,      
    column4 INTEGER ) 
UNIQUE PRIMARY INDEX (column1, column2, column3);


Example 5. Create a Replicated Table

The following example creates a replicated table. Replicated tables are copied in full to each Azure Synapse Compute node, thereby reducing data movement for queries.

CREATE TABLE table_name (
-- @dtm_synapse_table_options_override=DISTRIBUTION = REPLICATE     
    column1 INTEGER,     
    column2 INTEGER,     
    column3 INTEGER,     
    column4 INTEGER) 
UNIQUE PRIMARY INDEX (col1, col2, col3);


Example 6. Support for Multi-line Comments

You can specify table options in a comment block comment spanning multiple lines.

Caution

Ensure that no other comments are included in the comment block.

CREATE TABLE table_name(
/*@dtm_synapse_table_options_override=HEAP,
DISTRIBUTION = HASH ( column2 ),
PARTITION(column3 RANGE RIGHT FOR VALUES(1))
*/     
    column1 INTEGER,     
    column2 INTEGER,     
    column3 INTEGER,     
    column4 INTEGER
);