【Inceptor】导出数据库对象的DDL语句

2019-12-31 数据迁移

内容纲要

概要描述

本文主要介绍Inceptor 如何通过数据字典批量导出如下数据对象的DDL语句

视图和表
函数
存储过程
包

详细说明

1. 导出表和视图的DDL语句

以下脚本可以用来导出表和视图的DDL，注意执行前需要初始化TDH Client，保证beeline命令可用

注意：该方式会保留location和一些无用的tblproperties，建议剔除
注意：该方式为单线程导出，逻辑供参考，性能较慢，如客户环境表数量较多，需自行开发多线程导出程序

#如果使用show tables列出所有表和视图数据，生成的数据，默认是以开头字母排序的，所以如果视图依赖的表在视图的DDL后面，就会报表不存在的错误，所以这里不建议使用show tables ,改用数据字典tables_v,views_v 限定表类型数据，将表和视图DDL文件分开
#export.sh

#!/bin/bash
########################################
#使用方法:
##导出所有库的表/视图              sh export.sh all
##导出指定库的表/视图              sh export.sh {数据库名}
########################################
database=$1

#source /root/TDH-Client/init.sh

v_table=tables_${database}
v_view=views_${database}
dst_tables=ddl_tables_${database}
dst_views=ddl_views_${database}

#导出table & view 列表
if [ "${database}" == "all" ]; then
        beeline -u jdbc:hive2://localhost:10000/default --showHeader=false --outputformat=csv -e "select database_name||'.'||table_name from system.tables_v where database_name<>'system'" |sed 's/\"//g' > ${v_table}.txt
        beeline -u jdbc:hive2://localhost:10000/default --showHeader=false --outputformat=csv -e "select database_name||'.'||view_name from system.views_v where database_name<>'system'" |sed 's/\"//g' > ${v_view}.txt
else
        beeline -u jdbc:hive2://localhost:10000/default --showHeader=false --outputformat=csv -e "select database_name||'.'||table_name from system.tables_v where database_name='${database}'" |sed 's/\"//g' > ${v_table}.txt
        beeline -u jdbc:hive2://localhost:10000/default --showHeader=false --outputformat=csv -e "select database_name||'.'||view_name from system.views_v where database_name='${database}'" |sed 's/\"//g' > ${v_view}.txt
fi

#sleep时间根据表和视图的数量预留
echo sleep start
sleep 3
echo sleep end

#导出表ddl语句
cat ${v_table}.txt|while read eachline01
do
beeline -u jdbc:hive2://localhost:10000/default --showHeader=false --outputformat=csv -e "show create table $eachline01"|sed 's/\"//g' |sed '$a;' >>${dst_tables}.sql
done

#导出视图ddl语句
cat ${v_view}.txt|while read eachline02
do
beeline -u jdbc:hive2://localhost:10000/default --showHeader=false --outputformat=csv -e "show create table $eachline02"|sed 's/\"//g' |sed '$a;'>>${dst_views}.sql
done

使用方法：

#导出default数据库下所有的表和视图
[root@tdh522-183 lkw]# ./export.sh default
#导出所有数据库下的表和视图
[root@tdh522-183 lkw]# ./export.sh all

2. 导出函数的DDL语句

--所有函数DDL语句都可以在system.FUNCTIONSS_V表的full_text列拿到，可以通过下面SQL语句拿到所有PL/SQL函数的DDL：

INSERT OVERWRITE DIRECTORY '/tmp/function/' SELECT "use " ||database_name||"\;" || "\n" ||"!set plsqlUseSlash true"||"\n"||full_text||"\n"||"\/"||"\n"||"!set plsqlUseSlash false" from system.functions_v ;

root@kevin1 ~# hadoop fs -get /tmp/function/000000_0 .

3. 导出存储过程的DDL语句

--所有存储过程的DDL语句都可以在system.PROCEDURES_V表的full_text列拿到，可以通过下面SQL语句拿到所有存储过程的DDL：

INSERT OVERWRITE DIRECTORY '/tmp/procedure/' SELECT "use " ||database_name||"\;" || "\n" ||"!set plsqlUseSlash true"||"\n"||full_text||"\n"||"\/"||"\n"||"!set plsqlUseSlash false" from system.procedures_v ;

root@kevin1 ~# hadoop fs -get /tmp/procedure/000000_0 .

4. 导出Package的DDL语句

注意：这里无法避免会出现plsql依赖的问题，比如存储过程依赖package，需要手动调整执行顺序

--system.PACKAGES_V/full_text  包头
--system.PACKAGES_V/package_body 包体

INSERT OVERWRITE DIRECTORY '/tmp/package' select  "use " ||database_name||"\;" || "\n" ||"!set plsqlUseSlash  true"||"\n" || full_text || "\n"  ||"/" || "\n" ||package_body||"\n"||"\/"||"\n"||"!set plsqlUseSlash false"
from system.packages_v;

root@kevin1 ~# hadoop fs -get /tmp/package/000000_0 .

5. 导出Sequence的DDL语句

通过dblink查询元数据，

SELECT
    SEQ_NAME,
    concat(
        'CREATE SEQUENCE ', t2.name,'.',SEQ_NAME,
        ' INCREMENT BY ',INCRE_BY,
        ' START WITH ',START_WITH,
        if(MAX_VAL='9223372036854775807',' NOMAXVALUE ',concat(' MAXVALUE  ',MAX_VAL)),
        if(MIN_VAL='1',' NOMINVALUE ',concat(' MINVALUE ',MIN_VAL)),
        if(CYCLE='0',' NOCYCLE ',' CYCLE '),
        if(CACHE_SIZE='0',' NOCACHE ',concat(' CACHE ',CACHE_SIZE)),';') as  seq_ddl
FROM SEQS@system_dblink t1
JOIN DBS@system_dblink t2
ON t1.db_id=t2.db_id;

file