search

xnuinside / simple-ddl-parser

Simple DDL Parser to parse SQL (HQL, TSQL, AWS Redshift, BigQuery, Snowflake and other dialects) ddl files to json/python dict with full information about columns: types, defaults, primary keys, etc. & table properties, types, domains, etc.
MIT License
175 stars 40 forks source link

Snowflake exotism support #234

Closed dmaresma closed 3 months ago

dmaresma commented 6 months ago

Hi, there is CREATE TABLE statement in Snowflake, created by Matillion where the cluster by property precedes the table column definition, I have no idea where to start the change. here the acceptance test :

def test_table_inverse_clusterby_and_column_def(): ddl = """CREATE TABLE ${database_name}.MySchemaName."MyTableName" cluster by ("DocProv") (ID NUMBER(38,0) NOT NULL, "DocProv" VARCHAR(2));"""

result = DDLParser(ddl, normalize_names=True, debug=True).run(output_mode="snowflake")
expected = [{'table_name': 'MyTableName',
             'schema': 'MySchemaName',
             'primary_key': [],
             'columns': [{
                          'name': 'ID',
                          'size' : (38,0),
                          'type': 'NUMBER', 
                          'references': None, 
                          'unique': False, 
                          'nullable': False, 
                          'default': None, 
                          'check': None,
                          },
                          {
                          'name': 'DocProv',
                          'size' : 2,
                          'type': 'VARCHAR', 
                          'references': None, 
                          'unique': False, 
                          'nullable': True, 
                          'default': None, 
                          'check': None,
                          }], 
             'alter': {}, 
             'checks': [], 
             'index': [], 
             'partitioned_by': [],
             'cluster_by' : ['DocProv'],
             'tablespace': None,
             'external' : False,
             'primary_key_enforced' : None,
             'table_properties': {'project': '${database_name}'},
             'clone' : None
}]

assert result == expected
xnuinside commented 6 months ago

@dmaresma you need to add case where cluster by statement can be before columns defenitions. There is main 'table' expression https://github.com/xnuinside/simple-ddl-parser/blob/main/simple_ddl_parser/dialects/sql.py#L1016 it is expect that after table_name should be LP and defcolumn (column description), so you should add here cluster_by statement as a possible variant like table_name cluster_by (or how this statement should be named)

xnuinside commented 6 months ago

I can add it on weekends

xnuinside commented 3 months ago

I released in version 1.1.0. Have no idea why I'm writing about weekends, when I didn't have any time( Tests added in PR: https://github.com/xnuinside/simple-ddl-parser/pull/246/files. If will be needed anything - open new issue.