Column Definition

The table's schema information

Grammar syntax

column :=
  col_name data_type [column_constraint_specification] 
  [COMMENT col_comment], ...

column_constraint_specification:
  : [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
 
default_value:
  : [ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ] 
 

Data types

Click here for more information about the translation spec for data types. Also, see more information about data types in Hive here.

Sample Source

1. COMMENT

The comment clause is fully supported by Snowflake.

The syntax is equivalent in both languages.

Sample Source

CREATE TABLE table1 (
    col1 integer COMMENT 'The first column'
);

2. Primary Key

Primary Key is fully supported by Snowflake.

The syntax is equivalent in both languages.

Sample Source

CREATE TABLE table1 (
    col1 integer PRIMARY KEY DISABLE NOVALIDATE RELY
);

3. Unique

Unique constraint is fully supported by Snowflake.

The syntax is equivalent in both languages.

Sample Source

CREATE TABLE table1 (
    col1 integer UNIQUE DISABLE NOVALIDATE RELY
);

4. Default default_expression

The default value assigned to the column

The default constraint is fully supported by Snowflake.

The syntax is equivalent in both languages.

Sample Source

CREATE TABLE table1 (
    col1 integer DEFAULT 10,
    col2 STRING DEFAULT CURRENT_USER()
);

5. Not null

Not null constraint is fully supported by Snowflake.

The syntax is equivalent in both languages.

Sample Source

CREATE TABLE table1 (
    col1 integer NOT NULL
);

6. Check

Check constraint is not supported by Snowflake.

Hive

CREATE TABLE table1 (
    price double CHECK (price > 0 AND price <= 1000)
);

Snowflake

CREATE TABLE table1 (
    price double 
    --** MSC-ERROR - MSC-HVXXXX - THE CREATE TABLE CHECK CONSTRAINT IS NOT SUPPORTED IN SNOWFLAKE. **
    --CHECK (price > 0 AND price <= 1000)
);

Known Issues

No issues were found.

Related EWIs

1. MSC-HVXXXX : THE CREATE TABLE CHECK CONSTRAINT IS NOT SUPPORTED IN SNOWFLAKE.