python 3 regex to contain the create table statement from sql for azure databricks sql

Question

I have this oracle SQL Statement. I need a regex that captures only the create table statement with the constraints of primary and foreign keys.

CREATE TABLE "OWB_RUN"."TOKEN_CARD_STATUS_HISTORY"
   (    "TOKEN_CARD_WH" NUMBER NOT NULL ENABLE,
    "TOKEN_STATUS" VARCHAR2(1) NOT NULL ENABLE,
    "TOKEN_STATUS_DATE" DATE NOT NULL ENABLE,
    "LOAD_DATE" DATE,
     CONSTRAINT "TOKEN_CARD_STATUS_PK" PRIMARY KEY ("TOKEN_CARD_WH", "TOKEN_STATUS", "TOKEN_STATUS_DATE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "OWB_RUN"  ENABLE,
     CONSTRAINT "TOKEN_CARD_STATUS_FK" FOREIGN KEY ("TOKEN_CARD_WH")
      REFERENCES "OWB_RUN"."TOKEN_CARD" ("TOKEN_CARD_WH") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOLOGGING
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "CARDS_CRD_FACT_TBL"
  PARTITION BY RANGE ("LOAD_DATE")
 (PARTITION "Y2021_Q1_M03"  VALUES LESS THAN (TO_DATE(' 2021-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS NOLOGGING

The following regex i tried:

pattern = re.compile(r'CREATE\s+TABLE\s+.+?\n\)', re.DOTALL)

create_table_statement = pattern.findall(ddl)[0]
print(create_table_statement)

received the following output:

CREATE TABLE "OWB_RUN"."TOKEN_CARD_STATUS_HISTORY"
   (    "TOKEN_CARD_WH" NUMBER NOT NULL ENABLE,
    "TOKEN_STATUS" VARCHAR2(1)

with a regex that caputers all teh create table () i would be OK

score 0 · Answer 1 · answered Apr 24 '23 at 15:14

Unfortunately regexps are not able to match nested parentheses, so they are not really useful for this problem. Unless you want to make an assumption of the number of parentheses in the CREATE TABLE part of the SQL.

See e.g.: Can regular expressions be used to match nested patterns?

If the solution doesn't need to be a regular expression, a simple piece of python code that loops over the characters and tracks the nesting would be a more viable option.

Naeblis · Answer 2 · 2023-04-26T20:39:22.750

You'll have to make the slight mods needed for this to work in re.compile, but this expression gets what you want, minus the final closing parenthesis after 'ENABLE'.

^(CREATE\sTABLE(?s).*FOREIGN KEY\s\((?s).*ENABLE)

Easier to append +')' to the each pattern.findall(ddl)[0] than to add newline and three spaces then ) to the pattern, but you can update that if you like.

^            From the beginning 
(            start of capture group 
             Create \s Table 
(?s).*       Any character including new line any number of times
             Foreign Key
\s\((?s).*   space, parenthesis, any character including new line any number of times
             Enable
) end of capture group

Here's the regex101 if you want to adjust what's captured.

python 3 regex to contain the create table statement from sql for azure databricks sql

2 Answers2