MaxComputer-Pyodps之mysql ddl 批量生成odps ddl

时间：2024-04-11 10:09:12

系统环境：windows 64 位
python版本：2.7
Maxcomputer（原ODPS）

----- 1、安装python2.7（略过，百度一下就可以）

----- 2、安装MySQLdb模块
windows下个人习惯pip 安装 whl文件，虽然不是所有都能顺利安装，但是大部分还是可以的。
下载地址：
https://www.lfd.uci.edu/~gohlke/pythonlibs/#mysql-python

MaxComputer-Pyodps之mysql ddl 批量生成odps ddl

下载到本地后，cmd进入dos环境，可以直接切换到下载目录，直接使用命令 pip install mysqlclient-1.3.13-cp27-cp27m-win_amd64.whl 或者pip-script.py install mysqlclient-1.3.13-cp27-cp27m-win_amd64.whl

编译方法：
1、创建个xxx.py的文件，进入dos，切换到py文件路径：python xxx.py

2、使用编译工具：PyCharm、Maxcomputer Studio等工具执行

    #!/usr/bin/python
    # -*- coding: UTF-8 -*-
    # author：zhy

    import MySQLdb
    #####   mysql数据库配置
    def exec_sql(sql):
        conn=MySQLdb.connect(host='数据库地址',user='用户',passwd='密码',db=TABLE_SCHEMA,charset='utf8')
        cursor = conn.cursor()
        cursor.execute(sql)
        rows = cursor.fetchall()
        cursor.close()
        conn.close()
        return rows
    
    #### schema
    TABLE_SCHEMA='mysql'   # mysql数据库元数据库
    sys_id='s01'                      # ods入edw规范：表名+前缀
    target_schema='odps_project'  #odps对应项目名称
    
    ##### 获取mysql表名
    sql='''
        select table_name 
        from information_schema.`TABLES`
        where TABLE_SCHEMA='%s'
        and table_type='BASE TABLE'
    '''%TABLE_SCHEMA
    
    exec_relusts=exec_sql(sql)   # sql执行结果为二维

    out_comment=''    # 定义输出参数
    ###以下并不包含所有mysql字段类型的转换
    for i in exec_relusts:
        TABLE_NAME = i[0]
        print TABLE_NAME
        sql='''
                select
                     TABLE_NAME
                    ,COLUMN_NAME
                    ,case when DATA_TYPE in ('varchar','char','date','longtext','text') then 'string'
                          when DATA_TYPE in ('float') then 'double'
                          when DATA_TYPE = 'tinyint' then 'int'
                          when DATA_TYPE = 'timestamp' then 'datetime'
                          else DATA_TYPE
                     end as DATA_TYPE
                    ,COLUMN_COMMENT
    
                from information_schema.`COLUMNS`
                where TABLE_SCHEMA = '%s'
                and TABLE_NAME = '%s'
                order by TABLE_NAME,ORDINAL_POSITION
             '''%(TABLE_SCHEMA,TABLE_NAME)
    
        rows=exec_sql(sql)
        sql='''
        show create table %s.%s
        '''%(TABLE_SCHEMA,TABLE_NAME)
    
        sql_exec_result= exec_sql(sql)
        ddl_info0= sql_exec_result[0][1]
        if ddl_info0.find('COMMENT=')>1:
            ddl_table_common = ddl_info0[ddl_info0.index("COMMENT="):]
            ddl_table_common= ddl_table_common.replace('=',' ')
        else:
            ddl_table_common="COMMENT ''"
    
        ddl_info='CREATE TABLE IF NOT EXISTS %s.%s_%s (\n'%(target_schema,sys_id,TABLE_NAME)
    
        for row in rows:
            row3=row[3].replace('\r\n','\\r\\n')
            row3=row3.replace('\n','\\n')
            ddl_info=ddl_info+ str(row[1]).upper()+' '+row[2].upper()+' '+"COMMENT'"+ row3+"'\n,"
    
        ddl_info=ddl_info+')'
        ddl_info=ddl_info.replace(')',')')        
        ddl_info=ddl_info+'\n'+ddl_table_common+"\nPARTITIONED BY (PT STRING COMMENT '数据日期')".decode('utf-8')  #如不需分区，需修改
    
        out_comment= out_comment+'-- '+TABLE_NAME +'\n'+ddl_info +'\n;\n'
    
    # print out_comment   
    # 将DDL语句输出到文件
    Output_File=r'D:\data\output'+'\\'+sys_id+'_'+TABLE_SCHEMA+'.sql'
    file_w=file(Output_File,"w")
    file_w.write(out_comment.encode("utf-8"))
    file_w.close()

相关文章