生成hive建表语句的首尾只需拼接,主要通过数据库元数据读取字段类型转换为hive中的类型。
在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,
'\','
)