Simple DDL Parser to parse SQL (HQL, TSQL, AWS Redshift, Snowflake and other dialects) ddl files to json/python dict with full information about columns: types, defaults, primary keys, etc.

Hi @xnuinside I was trying to parse this ddl but getting an empty [ ] output

from simple_ddl_parser import parse_from_file
import json
import os

filename = r'example.ddl'
result = parse_from_file(filename)
print(result)
output
[]

json_data = json.dumps(result)
print(json_data)
output
[]

Not supporting CREATE SCHEMA IF NOT EXISTS and it is expecting only one OPTIONS

CREATE SCHEMA IF NOT EXISTS name-name OPTIONS ( location="path" ); CREATE TABLE name.hub.REF_CALENDAR ( calendar_dt DATE, calendar_dt_id INT, fiscal_half_year_day_no INT, fiscal_half_year_end_dt DATE, fiscal_half_year_month_no INT, fiscal_half_year_nm STRING, fiscal_half_year_no INT, fiscal_half_year_reporting_week_no INT, fiscal_half_year_start_dt DATE, fiscal_half_year_week_no INT, fiscal_year_day_no INT, fiscal_year_end_dt DATE, fiscal_year_long_nm STRING, fiscal_year_month_no INT, fiscal_year_nm STRING, fiscal_year_no INT, fiscal_year_quarter_no INT, fiscal_year_reporting_week_no INT, fiscal_year_start_dt DATE, fiscal_year_week_no INT, month_day_no INT, month_end_dt DATE, month_id STRING, month_nm STRING, month_reporting_week_no INT, month_start_dt DATE, month_week_no INT, quarter_day_no INT, quarter_end_dt DATE, quarter_month_no INT, quarter_reporting_week_no INT, quarter_start_dt DATE, quarter_week_no INT, reporting_week_day_no INT, reporting_week_end_dt DATE, week_day_nm STRING, week_day_no INT, week_day_short_nm STRING, weekend_ind INT, weekend_ind_desc STRING, year_day_no INT, year_month_no INT, year_no INT, year_quarter_no STRING, year_reporting_week_no INT, year_week_no INT ) OPTIONS ( description="Calendar table records reference list of calendar dates and related attributes used for reporting." );

I have below hive DDL which i need to parse but it is not working. @xnuinside - could you please help

CREATE EXTERNAL TABLE IF NOT EXISTS database.table_name
(
    day_long_nm     string,
    calendar_dt     date,
    source_batch_id string,
    field_qty       decimal(10, 0),
    field_bool      boolean,
    field_float     float,
    create_tmst     timestamp,
    field_double    double,
    field_long      bigint
) PARTITIONED BY (batch_id int) STORED AS PARQUET LOCATION 's3://datalake/table_name/v1'

Hi @xnuinside

I hope your are doing well. I saw that you made some major enhancements since to this project (FYI this is still amazing). I wanted to provide this comprehensive query for when you plan to add MS/T SQL support to the parser. You will notice that Postgres and MS SQL have similar syntaxes . The DDL below includes almost every TSQL data type. I also included the max sizes for each of the data types. Please feel free to let me know if you have any issues with the query.

CREATE TABLE Persons (
    PersonID int PRIMARY KEY,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);


CREATE TABLE sqlserverlist (

id INT IDENTITY (1,1) PRIMARY KEY, -- NOTE THE IDENTITY (1,1) IS SIMILAR TO serial in postgres - Format for IDENTITY [ (seed , increment) ]
company_id BIGINT ,
primary_id INT FOREIGN KEY REFERENCES Persons(PersonID), -- ADD THIS COLUMN FOR THE FOREIGN KEY
age TINYINT NULL UNIQUE,
days_active SMALLINT NOT NULL,
user_origin_of_birth char(255),
user_account VARCHAR(8000) NOT NULL,
user_first_name VARCHAR(max) NOT NULL,
user_last_name 	VARBINARY(8000) NOT NULL,
user_street NCHAR(400) NULL,
user_city NVARCHAR(4000),
about_user NTEXT NULL,
user_description TEXT,
starting_funds FLOAT(53) NULL,
extra_funds REAL,
current_funds DECIMAL (38,20),
ending_funds SMALLMONEY NOT NULL,
birth_date DATE NOT NULL,
time_of_birth TIME(7),
enrollment_date SMALLDATETIME,
delete_date DATETIME NULL,
create_date DATETIME2(7) NOT NULL,
user_time_zone DATETIMEOFFSET(7),
oder_date date DEFAULT GETDATE(), -- added to demonstrate sql sever Defaults
country varchar(255) DEFAULT 'Sandnes', -- added to demonstrate sql sever Defaults
active bit NULL,
home_size GEOMETRY, -- Sql Server Defaults to Null
user_photo IMAGE, -- Sql Server Defaults to Null
--UNIQUE (id),
CONSTRAINT UC_sqlserverlist_last_name UNIQUE (company_id,user_last_name),
CONSTRAINT CHK_Person_Age_under CHECK (days_active<=18 AND user_city='New York'),
FOREIGN KEY (id) REFERENCES Persons(PersonID),
CONSTRAINT FK_Person_Age_under  FOREIGN KEY (id)REFERENCES Persons(PersonID)
)

-- UNIQUE CONSTRAINTS
ALTER TABLE sqlserverlist ADD UNIQUE (birth_date);
ALTER TABLE sqlserverlist ADD CONSTRAINT UC_Person_ening_funds UNIQUE (current_funds,create_date);
--CHECK CONSTRAINTS
ALTER TABLE sqlserverlist ADD CHECK (starting_funds>=0);
ALTER TABLE sqlserverlist ADD CONSTRAINT CHK_Person_Age CHECK (age >=18 AND user_city='Sandnes');
-- FOREIGN KEY Constraints
ALTER TABLE sqlserverlist ADD FOREIGN KEY (id) REFERENCES Persons(PersonID);
ALTER TABLE sqlserverlist ADD CONSTRAINT FK_PersonOrder4 FOREIGN KEY (id) REFERENCES Persons(PersonID);
-- INDEXES 
CREATE INDEX i1 ON sqlserverlist (extra_funds);
--CREATE CLUSTERED INDEX i2 ON sqlserverlist  (delete_date,create_date); --- This line is commented because TSQL doesnt allow two index on table added as an example
--CREATE UNIQUE INDEX i1 ON t1 (delete_date DESC, create_date ASC, ending_funds  DESC); --- This line is commented because TSQL doesnt allow two index on table added as an example

-- DEFAULTS
ALTER TABLE sqlserverlist ADD CONSTRAINT df_user_street DEFAULT '1 WAY STREET' FOR user_street;
SELECT * FROM #sqlserverlist

Describe the bug "create OR REPLACE transient/temporary table" not recognized by parser. Thx for quickly adding the support for "OR REPLACE TABLE"! It seems that the "OR REPLACE" is only working with the "TABLE", but not with the Snowflake specific "TRANSIENT TABLE" or "TEMPORARY TABLE". However the support for snowflake's transient/temporary table is there e.g. "create TRANSIENT/TEMPORARY table" is working properly. Snowflake Docs: Working with Temporary and Transient Tables

To Reproduce

CREATE OR REPLACE TRANSIENT TABLE someTable (
    someField VARCHAR(4)
)

CREATE OR REPLACE TEMPORARY TABLE someTable (
    someField VARCHAR(4)
)

Expected behavior To get a proper output from the parser.

Describe the bug No tables was found in DDL input

To Reproduce Steps to reproduce the behavior: Generate my DDL from SSMS and import to OMM

Expected behavior find tables to parse

Desktop (please complete the following information):

• OS: [e.g. iOS] Win10

script.sql.txt

Consider:

create table index (col1:int);

create table foo.[index] (col1:int);

create table foo.index (col1:int);

The first 2 lines understand a table named "index" is intended. The third line classifies the word "index" as the token 'INDEX' probably here.

Though not a good or recommended practice, table names are allowed to be arbitrary strings, even reserved words.

If strict adherence to quoted/delimited usage of key-word-as-identifier DDL is desired, then the first line (bare "index" ) should have failed. If relaxed adherence is intended, then all 3 should have succeeded.

I'm not sure if I'm using this wrong, but DDLParser() fails to parse the attached PostgreSQL schema output from pg_dump

schema.txt

Traceback:

~/.cache/pypoetry/virtualenvs/capgras-r404Yw5Z-py3.8/lib/python3.8/site-packages/simple_ddl_parser/parser.py in run(self, dump, dump_path, file_path, output_mode, group_by_type, json_dump)
    228             Dict == one entity from ddl - one table or sequence or type.
    229         """
--> 230         tables = self.parse_data()
    231         tables = result_format(tables, output_mode, group_by_type)
    232         if dump:

~/.cache/pypoetry/virtualenvs/capgras-r404Yw5Z-py3.8/lib/python3.8/site-packages/simple_ddl_parser/parser.py in parse_data(self)
    167                 if final_line or new_statement:
    168                     # end of sql operation, remove ; from end of line
--> 169                     statement = statement[:-1]
    170                 elif num != len(lines) - 1 and not skip:
    171                     # continue combine lines in one massive

TypeError: 'NoneType' object is not subscriptable

Please feel free to close this issue if you don't intend to support this use-case.

Describe the bug It looks like you are seeing CREATE and breaking the table DDL parse...

To Reproduce

t1.sql:

DROP TABLE IF EXISTS demo;
--
CREATE TABLE demo
(
     foo                             char(1),
     create_date                     DATETIME2,
     created_BY                      VARCHAR (20)
)
--

t2.sql:

DROP TABLE IF EXISTS demo;
--
CREATE TABLE demo
(
     foo                             char(1),
     CREATE_date                     DATETIME2,
     created_by                      VARCHAR (20),
)
--

sdp -v t1.sql
$ sdp -v t1.sql
Start parsing file t1.sql 

File with result was saved to >> schemas folder
[{'alter': {},
  'checks': [],
  'columns': [{'check': None,
               'default': None,
               'name': 'foo',
               'nullable': True,
               'references': None,
               'size': 1,
               'type': 'char',
               'unique': False},
              {'check': None,
               'default': None,
               'name': 'create_date',
               'nullable': True,
               'references': None,
               'size': None,
               'type': 'DATETIME2',
               'unique': False},
              {'check': None,
               'default': None,
               'name': 'created_BY',
               'nullable': True,
               'references': None,
               'size': 20,
               'type': 'VARCHAR',
               'unique': False}],
  'index': [],
  'partitioned_by': [],
  'primary_key': [],
  'schema': None,
  'table_name': 'demo',
  'tablespace': None}]

vs:

$ sdp -v t2.sql
Start parsing file t2.sql 

File with result was saved to >> schemas folder
[{'alter': {},
  'checks': [],
  'columns': [{'check': None,
               'default': None,
               'name': 'foo',
               'nullable': True,
               'references': None,
               'size': 1,
               'type': 'char',
               'unique': False}],
  'index': [],
  'partitioned_by': [],
  'primary_key': [],
  'schema': None,
  'table_name': 'demo',
  'tablespace': None}]

Expected behavior A capitalized substring field name that is CREATE shouldn't break the parser.

Unsupported parse of NEXT VALUE FOR and ON on CREATE TABLE

When CREATE TABLE uses DEFAULT NEXT VALUE or script does user storage options (ex.: ON PRIMARY) , the parser does not output any results.,

To Reproduce

from simple_ddl_parser import DDLParser

ddl_Orig = ''' CREATE TABLE [dbo].[SLIPEVENTO] ( [cdSLIP] [bigint] NOT NULL DEFAULT NEXT VALUE FOR [dbo].[sqCdSLIPEvt] , -- Referencia da origem do evento [hashOrigem] VARBINARY NOT NULL, -- HASH pela tabela de onde vem o lancamento -- SIC, DARE21, SF, Manual, ... [tbOrigem] [int] NOT NULL, [tbVrtDstDflt] [int] NULL, CONSTRAINT [pk_slipsEvt] PRIMARY KEY CLUSTERED ( [cdSLIP] ), -- chave de referencia para multiplos lancamentos distribuidos -- ??? Identifica um documento origem via HASH na tbOrigem CONSTRAINT [uk_slipsEvtHASHdocs] UNIQUE([hashOrigem],[tbOrigem]) -- A FK precisa vir por trigger, pois sao diferentes origens., -- ) ON [PRIMARY] GO

'''

ddl1 = ''' CREATE TABLE [dbo].[SLIPEVENTO] ( [cdSLIP] [bigint] NOT NULL, -- Referencia da origem do evento [hashOrigem] VARBINARY NOT NULL, -- HASH pela tabela de onde vem o lancamento -- SIC, DARE21, SF, Manual, ... [tbOrigem] [int] NOT NULL, [tbVrtDstDflt] [int] NULL, CONSTRAINT [pk_slipsEvt] PRIMARY KEY CLUSTERED ( [cdSLIP] ), -- chave de referencia para multiplos lancamentos distribuidos -- ??? Identifica um documento origem via HASH na tbOrigem CONSTRAINT [uk_slipsEvtHASHdocs] UNIQUE([hashOrigem],[tbOrigem]) -- A FK precisa vir por trigger, pois sao diferentes origens., -- ) ON [PRIMARY] '''

ddl2 = ''' CREATE TABLE [dbo].[SLIPEVENTO] ( [cdSLIP] [bigint] NOT NULL, -- Referencia da origem do evento [hashOrigem] VARBINARY NOT NULL, -- HASH pela tabela de onde vem o lancamento -- SIC, DARE21, SF, Manual, ... [tbOrigem] [int] NOT NULL, [tbVrtDstDflt] [int] NULL, CONSTRAINT [pk_slipsEvt] PRIMARY KEY CLUSTERED ( [cdSLIP] ), -- chave de referencia para multiplos lancamentos distribuidos -- ??? Identifica um documento origem via HASH na tbOrigem CONSTRAINT [uk_slipsEvtHASHdocs] UNIQUE([hashOrigem],[tbOrigem]) -- A FK precisa vir por trigger, pois sao diferentes origens., -- ); '''

result = DDLParser(ddl_Orig).run(dump=True) print(result)

result = DDLParser(ddl1).run(dump=True) print(result)

result = DDLParser(ddl2).run(dump=True) print(result)

Expected behavior Interpretation as proposed on docs.

Desktop (please complete the following information):

• SQL Dialect - MSSQL - Autogenerated scripts - V12
• OS: Debian - Python3.9
• Idle3 ; Eric6, QtCreator (all of them )
• Version
• fred@alpha:~/Documentos/GoiasFomento/QtPy/src$ pip3 install simple-ddl-parser Collecting simple-ddl-parser Downloading simple_ddl_parser-0.20.0-py3-none-any.whl (31 kB) Requirement already satisfied: ply<4.0,>=3.11 in /usr/lib/python3/dist-packages (from simple-ddl-parser) (3.11) Installing collected packages: simple-ddl-parser Successfully installed simple-ddl-parser-0.20.0

Additional context Add any other context about the problem here.

CREATE TYPE does not parse too.

DROP TYPE dbo.T_LCT_SLIPS ; GO CREATE TYPE dbo.T_LCT_SLIPS AS TABLE ( hashKY varbinary(48), numContratoGF bigint, FATORGERADOR date , dtREFSIC date, cdTbOrigem int, cdTbVrtDstDflt int, Sq int, vl DECIMAL(32,0), descr varchar(254), tpEvento varchar(254), tpLcto char ); GO

Describe the bug Escaped single quote in COMMENT is not handled properly

To Reproduce ddl = """ CREATE EXTERNAL TABLE test ( job_id STRING COMMENT 'test's' ) STORED AS PARQUET LOCATION 'hdfs://test' """ from simple_ddl_parser import DDLParser parse_results = DDLParser(ddl).run(output_mode="hql")

Expected behavior Non empty json should be returned

Would it be possible to add support for AWS Redshift? It's based on Postgres 8.0 but, being a distributed database with columnar storage, has added some syntax to control how rows are distributed and compressed. You can find a BNF-esque grammar here.

Currently, this doesn't detect the DDL at all:

>>> results = DDLParser("""
...     CREATE TABLE foo.bar(
...         asdf INTEGER ENCODE ZSTD NOT NULL,
...         qwerty VARCHAR(255) ENCODE LZO
...     )
...     SORTSTYLE COMPOUND
...    SORTKEY (qwerty)
...     DISTSTYLE EVEN
...     ;
... """).run()
>>> results
[]

It'd be great if we could get the information about encoding, sortkey, distkey, etc. with a "redshift" output mode.

Describe the bug In the Bigquery when we use apostrophes to delimiter description string in options make parse fail

To Reproduce This fail CREATE TABLE data.test ( col STRING OPTIONS(description='test') ) OPTIONS(description='test'); and this work

CREATE TABLE data.test ( col STRING OPTIONS(description="test") ) OPTIONS(description="test");

Expected behavior Both statements should parsed

Following create table statement is not recognized by the parser, which is valid statement for Oracle database.

from simple_ddl_parser import DDLParser
DDLParser("create table test ( col varchar2(30 char) default user not null);").run()

result is empty list.

python version 3.9.5 simple-ddl-parser==0.29.0

Hi I was wondering if the JSON it outputs is compatible with https://json-schema.org/specification.html

My use case is that I want to validate that incoming records match the redshift table schema

Describe the bug A DDL containing COMMENT ON COLUMN does not associate the comment to the column

To Reproduce run this:

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from simple_ddl_parser import DDLParser
from pprint import pprint

parse_results = DDLParser("""-- something.t_spiel definition

-- Drop table

-- DROP TABLE something.t_spiel;

CREATE TABLE something.t_spiel (
	id varchar(5) NOT NULL,
	refprodid varchar(10) NULL,
	titel varchar(100) NOT NULL,
	refsaalid varchar(10) NULL, -- Verweis auf den Saal
	freieplatzwahl bool NOT NULL DEFAULT true, -- Ja/Nein
	CONSTRAINT idx_t_spiel_primary PRIMARY KEY (id)
);

-- Column comments

-- COMMENT ON COLUMN something.t_spiel.refsaalid IS 'Verweis auf den Saal';
-- COMMENT ON COLUMN something.t_spiel.freieplatzwahl IS 'Ja/Nein';

-- something.t_spiel foreign keys

ALTER TABLE something.t_spiel ADD CONSTRAINT "foreign_key_Ref_Prod_Id" FOREIGN KEY (refprodid) REFERENCES something.t_produktion(id);
ALTER TABLE something.t_spiel ADD CONSTRAINT "foreign_key_Ref_Saal_Id" FOREIGN KEY (refsaalid) REFERENCES something.t_saal(id);
""",silent=True).run(output_mode='sql')

pprint(parse_results)

This code produces ... {'comments': [' Verweis auf den Saal', ' Ja/Nein']}...

which is just a collection of comments w/o reference to the fields they belong to.

Expected behavior something like {'comments': {'something.t_spiel.refsaalid': ' Verweis auf den Saal', 'something.t_spiel.freieplatzwahl': ' Ja/Nein'}}... should be generated, or even better for postprocessing, the comment should be added to the field description: {'check': None, 'default': None, 'name': 'refsaalid', ... 'comment': 'Verweis auf den Saal' },

Desktop (please complete the following information):

• Database: Postgresql
• DDL created with DBeaver

Describe the bug Having DDL in a file with multiple statements like in the example below will ignore the COMMENT line, so in the parsed result there will be no information about comment.

To Reproduce

CREATE OR REPLACE TABLE mytable (
    field_1 NUMBER(38, 0) NOT NULL,
    field_2 VARCHAR(250)
);

COMMENT ON COLUMN field_1 IS 'some comment';

Expected behavior All the statements from DDL file/string parsed correctly and included in the result.

Hi Found this bug provided test:

   ddl = """
    CREATE TABLE IF NOT EXISTS default.salesorderdetail(
            column_abc ARRAY<string>,
            )
    """
    result = DDLParser(ddl, silent=False, normalize_names=True).run(output_mode="hql", group_by_type=True)
    expected = {
        "tables": [
            {
                "if_not_exists": True,
                "columns": [
                    {
                        "name": "column_abc",
                        "type": "ARRAY <string>",
                        "size": None,
                        "references": None,
                        "unique": False,
                        "nullable": True,
                        "default": None,
                        "check": None,
                    }]
            }]
    }

    assert expected == result

Error Message:

p = LexToken(RT,'ARRAY',1,65)

def p_error(self, p):
    if not self.silent:

      raise DDLParserError(f"Unknown statement at {p}")

E simple_ddl_parser.ddl_parser.DDLParserError: Unknown statement at LexToken(RT,'ARRAY',1,65)