Started

Reverse Engineering - Cluster by for Snowflake Tables

1 month ago • updated by 1 month ago • 1

Hi SQLDBM is unable to parse the Snowflake DDL where we table definition have "Cluster Key"

create or replace TABLE BDV.TEST cluster by (TEST1
,TEST2
,TEST3
,TEST4)(
TEST1 VARCHAR(128) NOT NULL WITH TAG (TAGS.TEST='TEST') COMMENT 'Definition:-1 ',
TEST2 VARCHAR(128) NOT NULL WITH TAG (TAGS.TEST='TEST') COMMENT 'Definition:-2 ',
TEST3 VARCHAR(8000) NOT NULL WITH TAG (TAGS.TEST='TEST') COMMENT 'Definition:-3 ',
TEST4 DATE NOT NULL WITH TAG (TAGS.TEST='TEST') COMMENT 'Definition:-4 ',

constraint TEST1_NK1 primary key (TEST1
,TEST2
,TEST3
,TEST4)
) WITH TAG (TAGS.IRM='ABC')
COMMENT='Definition:-'
;

The error we receive is as below

WarningUnable to parse SQL statement: 'create or replace TABLE BDV.TEST cluster by (TEST1': line 1. The statement will be skipped.WarningIncorrect syntax near: ';' (line: 16, column: 1).

Vote

Replies 1
Oldest first
- Newest first
- Oldest first

Started

1 month ago

Hi Sandeep,

Thank you very much for bringing up this topic. It seems that Snowflake added more ddl format for the "cluster by" statement. Previously, it was supported only at the end of the ddl.
I've already escalated it to our product team. They are going to fix the bug with high priority.

I will keep you updated on the progress.
As a workaround, you can use the "cluster by" statement at the end of the script like below.

create or replace TABLE TEST (
TEST1 VARCHAR(128) NOT NULL COMMENT 'Definition:-1 ',
TEST2 VARCHAR(128) NOT NULL COMMENT 'Definition:-2 ',
TEST3 VARCHAR(8000) NOT NULL COMMENT 'Definition:-3 ',
TEST4 DATE NOT NULL COMMENT 'Definition:-4 ',

constraint TEST1_NK1 primary key (TEST1
,TEST2
,TEST3
,TEST4)
) cluster by (TEST1
,TEST2
,TEST3
,TEST4)
COMMENT='Definition:-'
;

I hope this helps.

Thanks,

Kind Regards

Hazal Sener

Customer support service by UserEcho