用于解析SQL语句的Python重构函数

1 人关注

我需要用regex来解析一个SQL DDL语句中的一些信息。该SQL语句看起来像这样。

CREATE TABLE default.table1 (DATA4 BIGINT, DATA5 BIGINT, DATA2 BIGINT, DATA3 BIGINT)
USING parquet
OPTIONS (
  serialization.format '1'
PARTITIONED BY (DATA2, DATA3)
我需要在Python中解析它，并拉出PARTITIONED BY子句中的列。我已经想出了一个去掉换行符后实现它的regex，但是如果里面有换行符的话，我就不能让它工作。 这是一些演示代码。
import re
def print_partition_columns_if_found(ddl_string):
    regex = r'CREATE +?(TEMPORARY +)?TABLE *(?P<db>.*?\.)?(?P<table>.*?)\((?P<col>.*?)\).*?USING +([^\s]+)( +OPTIONS *\([^)]+\))?( *PARTITIONED BY \((?P<pcol>.*?)\))?'
    match = re.search(regex, ddl_string, re.MULTILINE | re.DOTALL)
    if match.group("pcol"):
        print match.group("pcol").strip()
    else:
        print 'did not find any pcols in {matches}'.format(matches=match.groups())        
ddl_string1 = """
CREATE TABLE default.table1 (DATA4 BIGINT, DATA5 BIGINT, DATA2 BIGINT, DATA3 BIGINT)
USING parquet OPTIONS (serialization.format '1') PARTITIONED BY (DATA2, DATA3)"""
print_partition_columns_if_found(ddl_string1)
print "--------"
ddl_string2 = """
CREATE TABLE default.table1 (DATA4 BIGINT, DATA5 BIGINT, DATA2 BIGINT, DATA3 BIGINT)
USING parquet
OPTIONS (
  serialization.format '1'
PARTITIONED BY (DATA2, DATA3)
print_partition_columns_if_found(ddl_string2)
那就回来吧。
  DATA2, DATA3

  --------

  在（None, 'default.', 'table1', 'DATA4 BIGINT, DATA5 BIGINT, DATA2 BIGINT, DATA3 BIGINT', 'parquet', None, None, None）中未找到任何pcols。
有哪个词条专家愿意帮助我吗？


         
          python


         
          regex


          
           
            
            
             Saket Mittal
            
           
           
            发布于
            
            2018-03-16


          
           已采纳


          
           
            
             让我们看看Python sqlparse的文档。
             
              文件 - 开始
             
            
           
           >>> import sqlparse
>>> ddl_string2 = """
... CREATE TABLE default.table1 (DATA4 BIGINT, DATA5 BIGINT, DATA2 BIGINT, DATA3 BIGINT)
... USING parquet
... OPTIONS (
...   serialization.format '1'
... )
... PARTITIONED BY (DATA2, DATA3)
... """
>>> ddl_string1 = """
... CREATE TABLE default.table1 (DATA4 BIGINT, DATA5 BIGINT, DATA2 BIGINT, DATA3 BIGINT)
... USING parquet OPTIONS (serialization.format '1') PARTITIONED BY (DATA2, DATA3)"""
>>> def print_partition_columns_if_found(sql):
...     parse = sqlparse.parse(sql)
...     data = next(item for item in reversed(parse[0].tokens) if item.ttype is None)[1]
...     print(data)
>>> print_partition_columns_if_found(ddl_string1)