python实现数据仓库的自动化开发

最新推荐文章于 2024-04-24 13:35:29 发布

mark_wu2000

最新推荐文章于 2024-04-24 13:35:29 发布

阅读量5.7k

点赞数 2

分类专栏：数据仓库自动化开发文章标签： python 数据仓库

本文链接：https://blog.csdn.net/mark_wu2000/article/details/95618290

版权

数据仓库自动化开发专栏收录该内容

1 篇文章 2 订阅

订阅专栏

概述

前面讲了基于元数据驱动数据仓库的开发，使数据仓库自动化，可视化。这篇讲用python来实现数仓的自动化开发 ,前提是你已经完成了需求调研和数据剖析，开始进行数据建模。自动化程序的流程如下：

由于没有开发前端建模页面，需要把ods、edw、dm层建模元数据导入到元数据库里。
把每层的转换查询脚本导入元数据库。
把源库表的元数据信息通过python加载到元数据库里。
跟据建表模板自动生成每层的DDL语句。
根据程序模板自动生成每层的加载脚本。

准备环境

新建一个元数据库,我用的是mysql。
新建元数据表。

CREATE TABLE `meta_tables` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `sys_name` varchar(100) DEFAULT NULL COMMENT '系统名称',
  `db_identify` varchar(100) DEFAULT NULL COMMENT '数据库标识',
  `db_type` varchar(100) DEFAULT NULL COMMENT '数据库类型',
  `schema_name` varchar(100) DEFAULT NULL COMMENT '数据库名称',
  `table_name` varchar(100) DEFAULT NULL COMMENT '表名',
  `table_comment` varchar(100) DEFAULT NULL COMMENT '表注释',
  `transform_sql` text COMMENT '转换查询脚本',
  `insert_dt` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间',
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=477 DEFAULT CHARSET=utf8 COMMENT='表元数据信息';
CREATE TABLE `meta_columns` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `sys_name` varchar(100) DEFAULT NULL COMMENT '系统名称',
  `db_identify` varchar(100) DEFAULT NULL COMMENT '数据库标识',
  `db_type` varchar(100) DEFAULT NULL COMMENT '数据库类型',
  `schema_name` varchar(100) DEFAULT NULL COMMENT '数据库名称',
  `table_name` varchar(100) DEFAULT NULL COMMENT '表名称',
  `col_name` varchar(100) DEFAULT NULL COMMENT '字段名称',
  `col_type` varchar(100) DEFAULT NULL COMMENT '字段类型',
  `col_len` bigint(21) DEFAULT NULL COMMENT '字段长度',
  `col_default_value` varchar(500) DEFAULT NULL COMMENT '字段默认值',
  `is_pk` int(11) DEFAULT NULL COMMENT '是否主键',
  `is_partition_key` int(11) DEFAULT NULL COMMENT '是否分区键',
  `index_type` varchar(100) DEFAULT NULL COMMENT '索引类型',
  `index_name` varchar(100) DEFAULT NULL COMMENT '索引名称',
  `col_comment` varchar(1000) DEFAULT NULL COMMENT '字段注释',
  `col_seq` int(11) DEFAULT NULL COMMENT '字段顺序',
  `is_cdc_key` int(11) DEFAULT NULL COMMENT '是否是增量时间戳',
  `join_column_id` int(11) DEFAULT NULL COMMENT '关联表字段id',
  `is_valid` int(11) DEFAULT '1' COMMENT '是否有效',
  `insert_dt` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间',
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5439 DEFAULT CHARSET=utf8 COMMENT='数据表字段信息';
CREATE TABLE `meta_common_columns` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `schema_name` varchar(100) DEFAULT NULL COMMENT '数据库名称',
  `col_name` varchar(100) DEFAULT NULL COMMENT '字段名称',
  `col_type` varchar(100) DEFAULT NULL COMMENT '字段类型',
  `col_len` int(11) DEFAULT NULL COMMENT '字段长度',
  `col_default_value` varchar(100) DEFAULT NULL COMMENT '字段默认值',
  `col_comment` varchar(100) DEFAULT NULL COMMENT '字段注释',
  `col_seq` int(11) DEFAULT NULL COMMENT '字段顺序',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='表的审计列';

3. 安装python3

文件名称	说明
e_sql	自动生成ETL程序脚本的文件夹
json	自动生成dataX的json文件的文件夹
tableddl	自动生成建表的文件夹
datamapping.py	源库和目标库的数据类型转换配置文件
dbconfig.py	数据库配置文件
generateoracleddl.py	自动生成oracle建表脚本的程序，如果是其他数据库可以新建一个文件
generateoraclejobsql.py	自动生成oracle同步脚本的程序，如果是其他数据库可以新建一个文件
loadmetadata.py	加载源数据库元数据的程序
sqlconfig.py	Sql脚本配置文件
templateconfig.py	同步脚本模板配置文件
util.py	公共方法文件

Python脚本

dbconfig.py 每个数据库都有一个唯一标识。

#!/usr/bin/python3
# -*- coding: utf-8 -*-
order={
        "sys_name":"电商平台订单数据库",
        "db_identify":"order",
        "schema_name":"order",
        "db_type":"mysql",
        "host":"10.10.10.1",
        "user":"test",
        "password":"*****",
        "port":3306,
        "db_name":"order"
     }

datamapping.py

"""数据类型mysql 转oracle"""
mysqltooracledatatype={"tinyint":"number",
              "smallint":"number",
              "mediumint":"number",
              "int":"number",
              "bigint":"number",
              "float":"number",
              "double":"number",
              "decimal":"number",
              "char":"varchar2",
              "varchar":"varchar2",
              "tinyblob":"blob",
              "tinytext":"varchar2",
              "blob":"blob",
              "text":"clob",
              "mediumblob":"blob",
              "mediumtext":"clob",
              "logngblob":"blob",
              "longtext":"clob",
              "date":"date",
              "datetime":"date",
              "timestamp":"date"
    }

sqlconfig.py

#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""查询mysql数据库的表元数据信息"""
mysqltablemetadata="""SELECT table_schema, table_name, table_comment
  FROM information_schema.TABLES
 WHERE table_schema = '%s' 
 """

"""查询oracle数据库的表元数据信息"""
oracletablemetadata="""SELECT 
lower(nvl(t.tablespace_name, t4.tablespace_name)) tablespace_name,
      lower(t.table_name),
       t2.comments
  FROM user_tables t
  JOIN user_tab_comments t2
    ON t.table_name = t2.table_name
  LEFT JOIN (SELECT t3.table_name, t3.tablespace_name
               FROM user_tab_partitions t3
              GROUP BY t3.table_name, t3.tablespace_name) t4
    ON t.table_name = t4.table_name
 WHERE lower(nvl(t.tablespace_name, t4.tablespace_name)) = '%s'  
 """

"""查询mysql数据库的表字段元数据信息"""
mysqlcolumnsmetadate="""select  
table_schema, 
table_name,
column_name,
data_type,
character_maximum_length, 
column_default,
case when column_key='pri' then 1 else 0 end is_pk,
column_comment,
ordinal_position
from information_schema.columns t where t.table_schema = '%s'  
"""

"""查询oracle数据库的表字段元数据信息"""
oraclecolumnsmetadate="""SELECT lower(nvl(t3.tablespace_name, t5.tablespace_name)) tablespace_name,
       lower(t.table_name),
       lower(t.column_name),
       lower(t.data_type),
       t.data_length,
       t.data_default,
       decode(t8.column_name, NULL, 0, 1) is_pk,
       t2.comments,
       t.column_id
  FROM user_tab_columns t
  JOIN user_col_comments t2
    ON t.table_name = t2.table_name
   AND t.column_name = t2.column_name
  JOIN user_tables t3
    ON t.table_name = t3.table_name
  LEFT JOIN (SELECT t4.table_name, t4.tablespace_name
               FROM user_tab_partitions t4
              GROUP BY t4.table_name, t4.tablespace_name) t5
    ON t.table_name = t5.table_name
  LEFT JOIN (SELECT t6.table_name, t6.column_name
               FROM user_cons_columns t6
               JOIN user_constraints t7
                 ON t6.constraint_name = t7.constraint_name
                AND t7.constraint_type = 'P') t8
    ON t.table_name = t8.table_name
   AND t.column_name = t8.column_name
 WHERE lower(nvl(t3.tablespace_name, t5.tablespace_name)) = '%s'  
 ORDER BY t.column_id"""

"""查询元数据库表信息"""
tableinfosql=""" SELECT t.sys_name,
       t.db_identify,
       t.schema_name,
       t.table_name,
       t.table_comment,
       t.transform_sql
  FROM meta_tables t
 WHERE t.db_identify='%s' and   t.table_name = '%s' 
"""

"""查询元数据库字段信息"""
columnsInfosql=""" SELECT
    t2.table_name,
    t.table_comment,
    t2.col_name,
    t2.col_type,
    t2.col_len,
    t2.col_comment,
    t2.is_pk,
    t2.col_default_value,
    t2.is_partition_key,
    t2.index_type,
    t2.index_name,
    t2.is_cdc_key,
    case when  t3.col_name is null then 0 else 1 end is_common_col
FROM
    meta_tables t
    JOIN meta_columns t2 ON t.db_identify = t2.db_identify 
    AND t.table_name = t2.table_name
    LEFT JOIN meta_common_columns t3 ON t2.col_name=t3.col_name and t3.schema_name=t2.schema_name
WHERE
    t.db_identify='%s' and  t.table_name= '%s'
ORDER BY t2.col_seq    
"""
"""查询元数据库公共字段信息"""
commcolumnsInfosql=""" SELECT t.schema_name,
       t.col_name,
       t.col_type,
       t.col_len,
       t.col_comment,
       t.col_default_value      
  FROM meta_common_columns t
 WHERE t.schema_name = '%s'
 ORDER BY t.col_seq   
"""
"""查询表索引"""
indexcolumnsql=""" SELECT t.index_type,
       t.index_name,
       group_concat(t.col_name ORDER BY col_seq) index_keys
  FROM meta_columns t
 WHERE t.db_identify = '%s'
   AND table_name = '%s'
   AND t.index_name IS NOT NULL
   AND TRIM(t.index_name) <> ''
 GROUP BY t.index_type, t.index_name   
"""

templateconfig.py

#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""创建stg层表语句模板"""
oraclestgtableddlsql="""create table %s (
%s
)
PARTITION BY LIST(date_num)
(
PARTITION p1 VALUES(0),
PARTITION p2 VALUES(1),
PARTITION p3 VALUES(2),
PARTITION p4 VALUES(3),
PARTITION p5 VALUES(4),
PARTITION p6 VALUES(5),
PARTITION p7 VALUES(6),
PARTITION p8 values(7),
PARTITION p9 VALUES(8),
PARTITION p10 VALUES(9),
PARTITION p11 VALUES(10),
PARTITION p12 VALUES(11),
PARTITION p13 VALUES(12),
PARTITION p14 VALUES(13),
PARTITION p_othor VALUES(DEFAULT)
);\n%s%s
"""
"""ods,edw,dm层建表模板"""
oracletableddlsql="""create table %s (
%s
);\n%s%s%s
"""
"""dataxjson文件"""
stgjobsql="""{
     "job": {
         "setting": {
             "speed": {
                 "channel": 8
             }
         },
         "content": [
             {
                "reader": {
                     "name": "mysqlreader",
                     "parameter": {
                         "username": "$SRC_USER_NAME",
                         "password": "$SRC_PASSW",
                         "column": [
%s],
                         "connection": [
                             {
                             "jdbcUrl": ["$SRC_JDBCURL"],
                             "table": ["%s"]
                             }
                         ],
                     }
                 },
                "writer": {
                     "name": "oraclewriter",
                     "parameter": {
                         "username": "$TGT_USER_NAME",
                         "password": "$TGT_PASSW",
                         "column": [
%s],
                         "preSql": ["ALTER TABLE %s TRUNCATE PARTITION $T_PARTITION_NM"],
                         "connection": [
                             {
                                 "jdbcUrl": "$TGT_JDBCURL",
                                 "table": ["%s"]
                             }
                         ]
                     }
                 }
             }
         ]
     }
 }
 """

"""ODS层程序模板"""
oracleodsjobsql="""
/**
*  Author         %s
*  Function       %s
*  Modify history list :
   Create Date    %s
*/
/**************************************************
所使用对象
表:%s
目标表:%s

**************************************************/
DECLARE
  v_start_date DATE;
  v_end_date   DATE;
  v_sp_name    VARCHAR2(100) := '%s';
  v_p_date     DATE := TO_DATE(&1, 'YYYYMMDD');
BEGIN
  v_start_date := SYSDATE;

  MERGE INTO %s target
  USING (SELECT
%s
           FROM (%s) ) source
  ON (%s)
  WHEN MATCHED THEN
    UPDATE
       SET
%s
     WHERE
%s
  WHEN NOT MATCHED THEN
    INSERT
      (
%s)
    VALUES
      (
%s);
  --源系统有物理删除标识
  UPDATE %s target
     SET target.deleted = 1, target.update_dt = v_p_date
   WHERE NOT EXISTS (SELECT 1
            FROM (%s) source
           WHERE %s);
  -- 成功日志
  v_end_date := SYSDATE;
  EXECUTE IMMEDIATE pkg_edw_util.c_log_sql
    USING pkg_edw_util.c_sp_status_sucess, pkg_edw_util.c_sp_status_sucess || ' ROWS:' || SQL%%ROWCOUNT, v_sp_name, v_start_date, v_end_date;
  COMMIT;
  -- 异常日志
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    v_end_date := SYSDATE;
    EXECUTE IMMEDIATE pkg_edw_util.c_log_sql
      USING pkg_edw_util.c_sp_status_fail, SQLERRM, v_sp_name, v_start_date, v_end_date;
    COMMIT;
END;
/
"""

"""edw层程序模板"""
oracleedwjobsql="""
/**
*  Author         %s
*  Function       %s
*  Modify history list :
   Create Date    %s
*/
/**************************************************
所使用对象
表:%s
目标表:%s

**************************************************/
DECLARE
  v_start_date DATE;
  v_end_date   DATE;
  v_sp_name    VARCHAR2(100) := '%s';
  v_p_date     DATE := TO_DATE(&1, 'YYYYMMDD');
BEGIN
  v_start_date := SYSDATE;

  MERGE INTO %s target
  USING (SELECT
%s
           FROM (%s) ) source
  ON (%s)
  WHEN MATCHED THEN
    UPDATE
       SET
%s
     WHERE
%s
  WHEN NOT MATCHED THEN
    INSERT
      (
%s)
    VALUES
      (
%s);
  -- 成功日志
  v_end_date := SYSDATE;
  EXECUTE IMMEDIATE pkg_edw_util.c_log_sql
    USING pkg_edw_util.c_sp_status_sucess, pkg_edw_util.c_sp_status_sucess || ' ROWS:' || SQL%%ROWCOUNT, v_sp_name, v_start_date, v_end_date;
  COMMIT;
  -- 异常日志
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    v_end_date := SYSDATE;
    EXECUTE IMMEDIATE pkg_edw_util.c_log_sql
      USING pkg_edw_util.c_sp_status_fail, SQLERRM, v_sp_name, v_start_date, v_end_date;
    COMMIT;
END;
/
"""

util.py

#!/usr/bin/python3
# -*- coding: utf-8 -*-
import pymysql
import datetime
import cx_Oracle
import dbconfig
"""
获取数据库连接和游标
"""
def getConn(dbInfo):
    try:
        if dbInfo["db_type"] == "oracle":
          conn= cx_Oracle.connect('%s/%s@%s:%d/%s'%(dbInfo["user"],dbInfo["password"],dbInfo["host"],dbInfo["port"],dbInfo["db_name"]))
        elif dbInfo["db_type"] == "mysql":
          conn= pymysql.connect(host=dbInfo["host"],port=dbInfo["port"],user=dbInfo["user"],password=dbInfo["password"],database=dbInfo["db_name"],charset='utf8')
        cursor = conn.cursor()
    except Exception as e:
            raise e
    return conn,cursor

loadmetadata.py

#!/usr/bin/python3
# -*- coding: utf-8 -*-
import pymysql
import util
import dbconfig
import sqlconfig
import cx_Oracle
"""获取元数据库连接"""
metaConn,metaCursor = util.getConn(dbconfig.metadata)
"""根据不同类型的数据库获取表的查询脚本
"""
def getTableSql(dbType):
    if dbType=="mysql":
        return sqlconfig.mysqltablemetadata
    elif dbType=="oracle":
         return sqlconfig.oracletablemetadata
    else :
         pass

"""
根据不同类型的数据库获取表字段的查询脚本
"""
def getColumnsSql(dbType):
    if dbType=="mysql":
        return sqlconfig.mysqlcolumnsmetadate
    elif dbType=="oracle":
         return sqlconfig.oraclecolumnsmetadate
    else :
         pass        


"""
加载源库表的元数据信息
"""
def loadTablesMetadate(sourcedbInfo):
    rslist=[]
    try:          
            sourceConn,sourceCursor = util.getConn(sourcedbInfo)
            deleteSql="""delete from meta_tables where db_identify='%s'"""%(sourcedbInfo["db_identify"])
            metaCursor.execute(deleteSql)
            metaConn.commit()
            insertSql="""insert into meta_tables(sys_name,db_identify,db_type,schema_name,table_name,table_comment) value(%s,%s,%s,%s,%s,%s)"""
            tableSql=getTableSql(sourcedbInfo["db_type"])
            sourceCursor.execute(tableSql%(sourcedbInfo["schema_name"]))
            rows =sourceCursor.fetchall()          
            for row in rows:             
                rslist.append((sourcedbInfo["sys_name"],sourcedbInfo["db_identify"],sourcedbInfo["db_type"])+row)
                if len(rslist) == 1000 :                                       
                     metaCursor.executemany(insertSql,rslist)                     
                     rslist=[]              
            metaCursor.executemany(insertSql,rslist)
            metaConn.commit()
            print(sourcedbInfo["sys_name"]+"的"+str(sourceCursor.rowcount)+"条表记录加载成功!")
    except Exception as e:
            metaConn.rollback()
            raise e
    finally:
            sourceCursor.close()
            sourceConn.close()
    return

"""
加载源库表字段的元数据信息
"""
def loadColumnsMetadata(sourcedbInfo):
    rslist=[]
    try:          
            sourceConn,sourceCursor = util.getConn(sourcedbInfo)
            deleteSql="""delete from meta_columns where db_identify='%s'"""%(sourcedbInfo["db_identify"])
            metaCursor.execute(deleteSql)
            metaConn.commit()
            insertSql="""insert into meta_columns(sys_name,db_identify,db_type,schema_name,table_name,col_name,col_type,col_len,col_default_value,
                         is_pk,col_comment,col_seq
                  ) value(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
            columnsinfomysql=getColumnsSql(sourcedbInfo["db_type"])
            sourceCursor.execute(columnsinfomysql%(sourcedbInfo["schema_name"]))
            rows =sourceCursor.fetchall()
            for row in rows:             
                rslist.append((sourcedbInfo["sys_name"],sourcedbInfo["db_identify"],sourcedbInfo["db_type"])+row)
                if len(rslist) == 1000 :                                       
                     metaCursor.executemany(insertSql,rslist)                     
                     rslist=[]              
            metaCursor.executemany(insertSql,rslist)
            metaConn.commit()
            print(sourcedbInfo["sys_name"]+"的"+str(sourceCursor.rowcount)+"条表字段记录加载成功!")
    except Exception as e:
            metaConn.rollback()
            raise e
    finally:
            sourceCursor.close()
            sourceConn.close()
    return 
if __name__ == '__main__':
        sourcedbInfo=dbconfig.order
        loadTablesMetadate(sourcedbInfo)
        loadColumnsMetadata(sourcedbInfo)

generateoracleddl.py

#!/usr/bin/python3
# -*- coding: utf-8 -*-
import pymysql
import util
import dbconfig
import sqlconfig
import datamapping
import os
import templateconfig
import cx_Oracle
"""获取元数据库连接"""
metaConn,metaCursor = util.getConn(dbconfig.edwmetadata)
"""把DDL脚本写入文件""" 
def write_file(tableName,text):
    try:
      fileDir = lambda arg : os.getcwd() + "\\" + "tableddl" + "\\" + arg
      with open(fileDir(tableName.lower() + ".sql"), 'w') as file_obj:
         file_obj.write(text)
         print(tableName+" 创建成功")
    except Exception as e:
        raise e
"""对字段长度规范化"""    
def cosLenConvert(cosLen):
    if cosLen<=100:
        return 100
    elif cosLen>100 and cosLen<=500:
        return 500
    elif cosLen>500 and cosLen<=1000:
        return 1000
    else :
        return 2000
    
"""获取公共列DDL脚本"""    
def getCommColumsDDlSql(schema_name,tableNmme):
    try:
        
        metaCursor.execute(sqlconfig.commcolumnsInfosql%(schema_name))
        colstr=""
        colCommentstr=""
        rows =metaCursor.fetchall()
        for row in rows:
            (colName,colType,colLen,colComment,coldefaultValue)=row[1:]              
            if colType.find("varchar")!=-1 :
                colLen="("+str(colLen)+")"
            else:
                    colLen=""
            if coldefaultValue:
                coldefaultValue=" default "+coldefaultValue
            else:
                coldefaultValue=""
            colstr+=","+colName+' '+colType+colLen+coldefaultValue+"\n"
            if colComment:
                colCommentstr+="""comment on column %s.%s is '%s';\n"""%(schema_name+"."+tableNmme,colName,colComment)
    except Exception as e:
            raise e
    return  colstr, colCommentstr

"""获取建索引脚本"""
def getIndexDDlSql(targetdbInfo,tableName):
    try:
        metaCursor.execute(sqlconfig.indexcolumnsql%(targetdbInfo["db_identify"],tableName.lower()))
        indexstr=""
        rows =metaCursor.fetchall()
        for row in rows:
            (indexType,indexName,indexKeys)=row             
            if indexType=="index":
                indexstr+="create  index "+indexName+" on "+targetdbInfo["schema_name"]+"."+tableName+"("+indexKeys+");\n"
    except Exception as e:
            raise e
    return  indexstr

"""从源数据库生成stg层DDL脚本"""
def tableStgDDlSql(sourcedbInfo,sourceTableName,targetdbInfo,targetTableName):
    try:
        metaCursor.execute(sqlconfig.columnsInfosql%(sourcedbInfo["db_identify"],sourceTableName.lower()))
        rows =metaCursor.fetchall()
        tableCommentstr=""
        colstr=""
        colCommentstr=""
        if len(rows)==0:
            print(sourceTableName+" 不存在!")
        else :
            commColStr,commCommentStr=getCommColumsDDlSql(targetdbInfo["schema_name"],targetTableName)
            tableComment=rows[0][1]
            if tableComment :
                tableCommentstr="""comment on table %s is  '%s' ;\n"""%(targetdbInfo["schema_name"]+"."+targetTableName,tableComment)           
            for row in rows:
                (colName,colType,colLen,colComment,ispk,coldefaultValue,isPartitionKey,indexType,indexName,isCdcKey,isCommonKey)=row[2:]               
                if colType.find("varchar")!=-1 :
                    colLen="("+str(cosLenConvert(colLen))+")"
                else:
                    colLen=""
                if coldefaultValue:
                       coldefaultValue=" default "+coldefaultValue.strip()
                else:
                    coldefaultValue=""
                colType=datamapping.mysqltooracledatatype[colType]
                colstr+=","+colName+' '+colType+colLen+coldefaultValue+"\n"
                if colComment:
                    colCommentstr+="""comment on column %s.%s is '%s';\n"""%(targetdbInfo["schema_name"]+"."+targetTableName,colName,colComment.strip())
            createTableSqlStr=templateconfig.oraclestgtableddlsql%(targetdbInfo["schema_name"]+"."+targetTableName,(colstr+commColStr).replace(',',' ',1).rstrip(),tableCommentstr,colCommentstr+commCommentStr)
            write_file(targetTableName,createTableSqlStr)
    except Exception as e:
            raise e
"""从源数据库生成ods层的DDL脚本"""        
def tableDDLSqlFromSource(sourcedbInfo,sourceTableName,targetdbInfo,targetTableName):
    try:
        metaCursor.execute(sqlconfig.columnsInfosql%(sourcedbInfo["db_identify"],sourceTableName.lower()))
        rows =metaCursor.fetchall()
        tableCommentstr=""
        colstr=""
        colCommentstr=""
        uniqueIdxStr=""
        if len(rows)==0:
            print(sourceTableName+" 不存在!")
        else :
            commColStr,commCommentStr=getCommColumsDDlSql(targetdbInfo["schema_name"],targetTableName)
            tableComment=rows[0][1]
            if tableComment :
                tableCommentstr="""comment on table %s is  '%s' ;\n"""%(targetdbInfo["schema_name"]+"."+targetTableName,tableComment)                
            for row in rows:
                (colName,colType,colLen,colComment,ispk,coldefaultValue,isPartitionKey,indexType,indexName,isCdcKey,isCommonKey)=row[2:]              
                if colType.find("varchar")!=-1 :
                    colLen="("+str(cosLenConvert(colLen))+")"
                else:
                    colLen=""
                if coldefaultValue:
                       coldefaultValue=" default "+coldefaultValue.strip()
                else:
                    coldefaultValue=""
                if ispk==1:
                    uniqueIdxStr+=colName+","
              
                colType=datamapping.mysqltooracledatatype[colType]
                colstr+=","+colName+' '+colType+colLen+coldefaultValue+"\n"
                if colComment:
                    colCommentstr+="""comment on column %s.%s is '%s';\n"""%(targetdbInfo["schema_name"]+"."+targetTableName,colName,colComment.strip())
            if  uniqueIdxStr: 
                uniqueIdxStr="alter table "+targetdbInfo["schema_name"]+"."+targetTableName+" add constraint PK_"+targetTableName+" primary key ("+uniqueIdxStr.rstrip(',')+");"
            createTableSqlStr=templateconfig.oracletableddlsql%(targetdbInfo["schema_name"]+"."+targetTableName,(colstr+commColStr).replace(',',' ',1).rstrip(),tableCommentstr,colCommentstr+commCommentStr,uniqueIdxStr)
            write_file(targetTableName,createTableSqlStr)
    except Exception as e:
            raise e

"""从元数据库生成DDL脚本"""
def tableDDLSqlFromMeta(targetdbInfo,targetTableName):
    try:
        metaCursor.execute(sqlconfig.columnsInfosql%(targetdbInfo["db_identify"],targetTableName.lower()))
        rows =metaCursor.fetchall()
        tableCommentstr=""
        colstr=""
        colCommentstr=""
        uniqueIdxStr=""
        if len(rows)==0:
            print(targetTableName+" 不存在!")
        else :
            tableComment=rows[0][1]
            if tableComment :
                tableCommentstr="""comment on table %s is  '%s' ;\n"""%(targetdbInfo["schema_name"]+"."+targetTableName,tableComment)                
            for row in rows:
                (colName,colType,colLen,colComment,ispk,coldefaultValue,isPartitionKey,indexType,indexName,isCdcKey,isCommonKey)=row[2:]              
                if colType.find("varchar")!=-1 :
                    colLen="("+str(colLen)+")"
                else:
                    colLen=""
                if coldefaultValue:
                       coldefaultValue=" default "+coldefaultValue.strip()
                else:
                    coldefaultValue=""
                if ispk==1:
                    uniqueIdxStr+=colName+","
                colstr+=","+colName+' '+colType+colLen+coldefaultValue+"\n"
                if colComment:
                    colCommentstr+="""comment on column %s.%s is '%s';\n"""%(targetdbInfo["schema_name"]+"."+targetTableName,colName,colComment.strip())
            #print(colstr,colCommentstr);
            if  uniqueIdxStr:
                uniqueIdxStr="alter table "+targetdbInfo["schema_name"]+"."+targetTableName+" add constraint PK_"+targetTableName+" primary key ("+uniqueIdxStr.rstrip(',')+");\n"
            indexStr=getIndexDDlSql(targetdbInfo,targetTableName)    
            createTableSqlStr=templateconfig.oracletableddlsql%(targetdbInfo["schema_name"]+"."+targetTableName,colstr.replace(',',' ',1).rstrip(),tableCommentstr,colCommentstr,(uniqueIdxStr+indexStr).rstrip())
            write_file(targetTableName,createTableSqlStr)
    except Exception as e:
            raise e          
if __name__ == '__main__':
        sourcedbInfo=dbconfig.order
        targetdbInfo=dbconfig.edwstg
        stgTables={"user":"order_user"}
        for key,value in stgTables.items():
            tableStgDDlSql(sourcedbInfo,key,targetdbInfo,value)
        targetdbInfo=dbconfig.edwods
        odsTables={"user":"ods_user"}
        for key,value in odsTables.items():
            tableDDLSqlFromSource(sourcedbInfo,key,targetdbInfo,value)
            #tableDDLSqlFromMeta(targetdbInfo,value)
        targetdbInfo=dbconfig.edw
        edwTables={"ods_fuser":"dim_user"}
        for key,value in edwTables.items():
            tableDDLSqlFromMeta(targetdbInfo,value)     
        metaCursor.close()
        metaConn.close()

generateoraclejobsql.py

import pymysql
import util
import dbconfig
import sqlconfig
import datamapping
import os
import re
import templateconfig
import cx_Oracle

"""获取元数据库连接"""
metaConn,metaCursor = util.getConn(dbconfig.edwmetadata)
"""把json脚本写入文件""" 
def write_file(tableName,text):
    try:
      fileDir = lambda arg : os.getcwd() + "\\" + "json" + "\\" + arg
      with open(fileDir(tableName.lower() + ".json"), 'w') as file_obj:
         file_obj.write(text)
         print(tableName+" 创建成功")
    except Exception as e:
        raise e
    
"""把sql脚本写入文件"""   
def writEdwFile(tableName,text):
    try:
      fileDir = lambda arg : os.getcwd() + "\\" + "e_sql" + "\\" + arg
      with open(fileDir(tableName.lower() + ".sql"), 'w') as file_obj:
         file_obj.write(text)
         print(tableName+" 创建成功")
    except Exception as e:
        raise e
    
"""生成stg层的程序脚本"""     
def stgJobSql(sourcedbInfo,sourceTableName,targetTableName):
    try:
        metaCursor.execute(sqlconfig.columnsInfosql%(sourcedbInfo["db_identify"],sourceTableName.lower()))
        rows =metaCursor.fetchall()
        colStr=""
        if sourcedbInfo["db_type"]=="mysql":
            dateStr="now()"
        else :
            dateStr="sysdate"
        commColValueStr="""                                   ,"'"""+sourcedbInfo["db_identify"] + """'"
                                   ,"'""" + sourceTableName + """'"
                                   ,"'increment'"
                                   ,'"""+dateStr+"""'
                                   ,'"""+dateStr+ """'                                 
"""
        commColStr="""                                   ,"src_db_identify" 
                                   ,"src_table_name"
                                   ,"etl_type"
                                   ,"insert_dt"
                                   ,"update_dt"
"""
        if len(rows)==0:
            print(sourceTableName+" 不存在!")
        else :          
            for row in rows:
                colName=row[2]               
                colStr+="                                   ,\""+colName+"\"\n"
            stgJsonStr=templateconfig.stgjobsql%((colStr+commColValueStr).replace(',',' ',1).rstrip(),sourceTableName,(colStr+commColStr).replace(',',' ',1).rstrip(),targetTableName,targetTableName)
            write_file(sourcedbInfo["db_identify"]+"_"+targetTableName,stgJsonStr)
    except Exception as e:
            raise e
        
"""生成ods层的程序脚本"""         
def odsJobSql(sourcedbInfo,sourceTableName,targetdbInfo,targetTableName,author,jobName,createDate):
    try:
        metaCursor.execute(sqlconfig.tableinfosql%(targetdbInfo["db_identify"],targetTableName.lower()))
        tableRows =metaCursor.fetchall()
        if len(tableRows)==0:
             print(targetTableName+" 查询语句不存在!")
        else:     
            transformSql=tableRows[0][5]
            metaCursor.execute(sqlconfig.columnsInfosql%(targetdbInfo["db_identify"],targetTableName.lower()))
            rows =metaCursor.fetchall()
            joinColStr=""
            setColStr=""
            insertValuesStr=""
            insertColStr=""
            whereSourceStr=""
            whereTargetStr=""
            if len(rows)==0:
                print(targetTableName+" 不存在!")
            else :         
                for row in rows:
                    colName=row[2]
                    colType=row[3]
                    ispk=row[6]
                    if ispk==1:
                        joinColStr+="target."+colName+" = source."+colName +" and"
                    
                    insertValuesStr+=",source."+colName+"\n"
                    insertColStr+=","+colName+"\n"
                    if ispk!=1 and colName!="insert_dt":                             
                               setColStr+=",target."+colName+"       = source."+colName +"\n"
                    if ispk!=1 and colName not in("insert_dt","update_dt"):
                            whereSourceStr+="source."+colName+"||"
                            whereTargetStr+="target."+colName+"||"  
            joinColStr=joinColStr.rstrip("and")
            whereSourceStr="MD5("+whereSourceStr.rstrip("||")+")<>\n"
            whereTargetStr="MD5("+whereTargetStr.rstrip("||")+")"
            matchObj=re.search(r'select(.*)from.*', transformSql,re.I|re.S)
            
            odsJobStr=templateconfig.oracleodsjobsql%(author,jobName,createDate,\
                sourcedbInfo["schema_name"]+"."+sourceTableName,targetdbInfo["schema_name"]+"."+targetTableName,targetTableName,\
                targetdbInfo["schema_name"]+"."+targetTableName, insertColStr.replace(',',' ',1).rstrip(), transformSql,joinColStr,\
                setColStr.replace(',',' ',1).rstrip(), whereSourceStr+whereTargetStr,insertColStr.replace(',',' ',1).rstrip(),\
                insertValuesStr.replace(',',' ',1).rstrip(), targetdbInfo["schema_name"]+"."+targetTableName,transformSql.replace(matchObj.group(1),' 1 '), joinColStr )
            #print(odsJobStr)
            writEdwFile(targetTableName,odsJobStr)
    except Exception as e:
            raise e
        
"""生成edw层的程序脚本"""       
def edwJobSql(sourcedbInfo,sourceTableName,targetdbInfo,targetTableName,author,jobName,createDate):
    try:
        metaCursor.execute(sqlconfig.tableinfosql%(targetdbInfo["db_identify"],targetTableName.lower()))
        tableRows =metaCursor.fetchall()
        if len(tableRows)==0:
             print(targetTableName+" 查询语句不存在!")
        else:     
            transformSql=tableRows[0][5]
            metaCursor.execute(sqlconfig.columnsInfosql%(targetdbInfo["db_identify"],targetTableName.lower()))
            rows =metaCursor.fetchall()
            selectColStr=""
            joinColStr=""
            setColStr=""
            insertValuesStr=""
            insertColStr=""
            whereSourceStr=""
            whereTargetStr=""
            if len(rows)==0:
                print(targetTableName+" 不存在!")
            else :         
                for row in rows:
                    colName=row[2]
                    colType=row[3]
                    ispk=row[6]
                    defaultValue=row[7]
                    if ispk==1:
                        joinColStr+="target."+colName+" = source."+colName +" and"
                    selectColStr+=",nvl("+colName+", "+defaultValue+") as "+colName+"\n"
                    insertValuesStr+=",source."+colName+"\n"
                    insertColStr+=","+colName+"\n"
                    if ispk!=1 and colName!="insert_dt":                             
                               setColStr+=",target."+colName+"       = source."+colName +"\n"
                    if ispk!=1 and colName not in("insert_dt","update_dt"):
                            whereSourceStr+="source."+colName+"||"
                            whereTargetStr+="target."+colName+"||"  
            joinColStr=joinColStr.rstrip("and")
            whereSourceStr="MD5("+whereSourceStr.rstrip("||")+")<>\n"
            whereTargetStr="MD5("+whereTargetStr.rstrip("||")+")"
            edwJobStr=templateconfig.oracleedwjobsql%(author,jobName,createDate,\
                sourcedbInfo["schema_name"]+"."+sourceTableName,targetdbInfo["schema_name"]+"."+targetTableName,targetTableName,\
                targetdbInfo["schema_name"]+"."+targetTableName, selectColStr.replace(',',' ',1).rstrip(), transformSql,joinColStr,\
                setColStr.replace(',',' ',1).rstrip(), whereSourceStr+whereTargetStr,insertColStr.replace(',',' ',1).rstrip(),\
                insertValuesStr.replace(',',' ',1).rstrip())
            writEdwFile(targetTableName,edwJobStr)
    except Exception as e:
            raise e         
if __name__ == '__main__':
        sourcedbInfo=dbconfig.scf
        stgTables={"user":"order_user"}               
        for key,value in stgTables.items():
            stgJobSql(sourcedbInfo,key,value)
        sourcedbInfo=dbconfig.edwstg
        targetdbInfo=dbconfig.edwods
        odsTables={"stg_user":"ods_user"}
        for key,value in odsTables.items():
            odsJobSql(sourcedbInfo,key,targetdbInfo,value,"test","测试","2019/09/12")
        sourcedbInfo=dbconfig.edwods
        targetdbInfo=dbconfig.edw
        edwTables={"ods_user":"dim_user"}
        for key,value in edwTables.items():
            edwJobSql(sourcedbInfo,key,targetdbInfo,value,"test","测试","2019/09/12")     
        metaCursor.close()
        metaConn.close()

mark_wu2000

关注

2
点赞
踩
45

收藏

觉得还不错? 一键收藏
4
评论
python实现数据仓库的自动化开发

概述前面讲了基于元数据驱动数据仓库的开发，使数据仓库自动化，可视化。这篇讲用python来实现数仓的自动化开发 ,前提是你已经完成了需求调研和数据剖析，开始进行数据建模。自动化程序的流程如下：由于没有开发前端建模页面，需要把ods、edw、dm层建模元数据导入到元数据库里。把每层的转换查询脚本导入元数据库。把源库表的元数据信息通过python加载到元数据库里。跟据建表模板自动生成...
复制链接

扫一扫