[工具 ] Doris库表结构导出工具 (python2.7实现)

数据分析

2021-01-21

177

0

安装依赖

pip install mysql-connector

代码

# coding=utf-8
import json
import os
import sys
import threading
import time

import mysql.connector

conn = None

reload(sys)
sys.setdefaultencoding('utf8')

env = 'dev_qiye'

doris_config = {
    'dev_qiye': {
        'host': '127.0.0.1',
        'port': 8130,
        'user': 'root',
        'password': ''
    }
}


def get_create_db_sqls(db_names, curr):
    white_list = {'information_schema': 1}

    sqls_map = {}
    for idb in db_names:
        db_name = idb[0]
        if white_list.has_key(db_name):
            continue
        curr.execute('show create database {}'.format(db_name))
        _sqls = curr.fetchall()
        for _q in _sqls:
            sqls_map[_q[0]] = _q[1].lower().replace('create database', 'create database if not exists')
    return sqls_map


def clean_table_sql(table_create_sql):
    arr = table_create_sql.split('\n')
    new_arr = []
    for line in arr:
        if line.find('dynamic_partition.replication_num') > -1:
            continue
        if line.find('dynamic_partition.time_zone') > -1:
            continue
        new_arr.append(line)
    return '\n'.join(new_arr)


def dump_structures():
    create_db_sqls = {}
    create_table_sqls = {}

    conn = mysql.connector.connect(**doris_config[env])
    curr = conn.cursor()

    sql_show_databases = 'show databases;'
    curr.execute(sql_show_databases)
    all_db_names_arr = curr.fetchall()

    result_arr = []
    if len(all_db_names_arr) <= 0:
        pass
    else:
        create_db_sqls = get_create_db_sqls(all_db_names_arr, curr)
        for db, c_db_sql in create_db_sqls.items():
            curr.execute('show tables from {}'.format(db))
            _tables = curr.fetchall()
            for _t in _tables:
                curr.execute('show create table {}.{}'.format(db, _t[0]))
                table_sql = curr.fetchall()

                full_table_name = '{}.{}'.format(db, table_sql[0][0])
                full_table_sql = table_sql[0][1].replace(table_sql[0][0], '{}`.`{}'.format(db, table_sql[0][0]),
                                                         1).lower().replace('create table',
                                                                            'create table if not exists')

                create_table_sqls[full_table_name] = full_table_sql

        result_arr.append("### start create database \n\n")
        for db, sql in create_db_sqls.items():
            _sql = '{};\n'.format(sql)
            result_arr.append(_sql)
        result_arr.append("### start create table \n\n")

        for tbl, sql in create_table_sqls.items():
            _sql = clean_table_sql(sql)
            result_arr.append('{}\n'.format(_sql))

        with open('dump_structures_doris.sql', 'w') as f:
            f.write(''.join(result_arr))
    return create_db_sqls, create_table_sqls


if __name__ == '__main__':
    dump_structures()

结果

结果输出在在和脚本同级目录的文件中: dump_structures_doris.sql

欢迎添加微信,互相学习↑↑↑ -_-

发表评论

全部评论:0条

白老虎

programming is not only to solve problems, ways to think