一
1、CREATE TABLE
dim_ddl_convert
(
source VARCHAR(100) NOT NULL,
data_type1 VARCHAR(100) NOT NULL,
target VARCHAR(100) NOT NULL,
data_type2 VARCHAR(100),
update_time VARCHAR(26),
PRIMARY KEY (source, data_type1, target)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’数据库表结构转换’;
2、
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘bigint’, ‘hive’, ‘BIGINT’, ‘2019-07-31 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘bigint’, ‘odps’, ‘BIGINT’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘binary’, ‘hive’, ‘BINARY’, ‘2019-07-31 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘binary’, ‘odps’, ‘BINARY’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘char’, ‘hive’, ‘STRING’, ‘2019-07-31 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘char’, ‘odps’, ‘STRING’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘datetime’, ‘hive’, ‘STRING’, ‘2019-07-31 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘datetime’, ‘odps’, ‘DATETIME’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘decimal’, ‘hive’, ‘DOUBLE’, ‘2019-07-31 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘decimal’, ‘odps’, ‘DOUBLE’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘double’, ‘hive’, ‘DOUBLE’, ‘2019-07-31 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘double’, ‘odps’, ‘DOUBLE’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘float’, ‘hive’, ‘DOUBLE’, ‘2019-07-31 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘float’, ‘odps’, ‘DOUBLE’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘int’, ‘hive’, ‘BIGINT’, ‘2019-07-31 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘int’, ‘odps’, ‘BIGINT’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘json’, ‘hive’, ‘MAP
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘json’, ‘odps’, ‘MAP
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘mediumtext’, ‘hive’, ‘STRING’, ‘2019-07-31 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘mediumtext’, ‘odps’, ‘STRING’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘smallint’, ‘hive’, ‘BIGINT’, ‘2019-07-31 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘smallint’, ‘odps’, ‘BIGINT’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘text’, ‘hive’, ‘STRING’, ‘2019-07-31 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘text’, ‘odps’, ‘STRING’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘time’, ‘hive’, ‘STRING’, ‘2019-07-31 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘time’, ‘odps’, ‘STRING’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘timestamp’, ‘hive’, ‘STRING’, ‘2019-07-31 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘timestamp’, ‘odps’, ‘DATETIME’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘tinyint’, ‘hive’, ‘BIGINT’, ‘2019-07-31 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘tinyint’, ‘odps’, ‘BIGINT’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘varbinary’, ‘hive’, ‘BINARY’, ‘2019-07-31 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘varbinary’, ‘odps’, ‘BINARY’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘varchar’, ‘db2’, ‘varchar’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘varchar’, ‘hive’, ‘STRING’, ‘2019-07-31 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘varchar’, ‘odps’, ‘STRING’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘varchar’, ‘oracle’, ‘varchar’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘varchar’, ‘sqlserver’, ‘varchar’, ‘2019-05-06 00:00:00’);
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES (‘mysql’, ‘varchar’, ‘sybase’, ‘varchar’, ‘2019-05-06 00:00:00’);
3、
SET SESSION groupconcat_max_len = 102400;
SELECT
a.TABLE_NAME ,
b.TABLE_COMMENT ,
concat(‘DROP TABLE IF EXISTS ‘,a.TABLE_NAME,’;CREATE EXTERNAL TABLE IF NOT EXISTS ‘,a.TABLE_NAME ,’ (‘,group_concat(concat(a.COLUMN_NAME,’ ‘,
c.data_type2,” COMMENT ‘“,COLUMN_COMMENT,”‘“) order by a.TABLE_NAME,a.ORDINAL_POSITION) ,
“) COMMENT ‘“,b.TABLE_COMMENT ,”‘ ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ STORED AS orc;”) AS col_name
FROM
(
SELECT
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
ORDINAL_POSITION,
DATA_TYPE,
COLUMN_COMMENT
FROM
information_schema.COLUMNS
WHERE
TABLE_SCHEMA=’你的库名’
) AS a
LEFT JOIN
information_schema.TABLES AS b
ON
a.TABLE_NAME=b.TABLE_NAME
AND a.TABLE_SCHEMA=b.TABLE_SCHEMA
#选择源为mysql，目标为hive
LEFT JOIN
(
select
*
from dim_ddl_convert
where source=’mysql’ and target=’hive’
) AS c
ON
a.DATA_TYPE=c.data_type1
where b.TABLE_TYPE=’BASE TABLE’
and a.TABLE_NAME not like ‘ods%’
GROUP BY
a.TABLE_NAME,
b.TABLE_COMMENT
;
二、 使用单个SQL生成单个mysql表的hive建表语句
SELECT
CONCAT(‘create table ‘,@tbl_name,’(‘)
UNION ALL
SELECT
CONCAT(
COLUMN_NAME,
‘ ‘,
CASE
WHEN DATA_TYPE in (‘varchar’,’longtext’,’char’,’datetime’,’timestamp’,’varbinary’,’bit’,’mediumtext’,’set’,’longblob’,’text’,’blob’,’time’,’date’) THEN
‘string’
WHEN DATA_TYPE = ‘decimal’ THEN
COLUMN_TYPE
WHEN DATA_TYPE = ‘float’ THEN
‘double’
ELSE
DATA_TYPE
END — 数据类型转换
,
‘ comment ‘,
‘\’’,
CASE
WHEN COLUMN_COMMENT is NULL THEN
COLUMN_NAME
ELSE
replace(COLUMN_COMMENT,’;’,’,’)
END,
‘\’,’
)
FROM
information_schema. COLUMNS t1
WHERE
t1.table_schema = @tbl_schema
and t1.TABLE_NAME = @tbl_name
UNION ALL
SELECT
concat(
‘etl_update string COMMENT \’数据同步时间\’) ‘,
‘COMMENT \’’,
COALESCE (t2.TABLE_COMMENT ,@tbl_name),
‘\’
— PARTITIONED BY (DATE STRING COMMENT \’日期分区\’) — 分区表取消注释
ROW FORMAT DELIMITED FIELDS TERMINATED BY \’,\’ STORED AS TEXTFILE;’
)
FROM
information_schema. TABLES t2
WHERE
t2.table_schema = (@tbl_schema := ‘linkis’)
and t2.table_name = (@tbl_name := ‘dss_application’)
若有收获，就点个赞吧
0 人点赞
