**
mysql转hive转换脚本：**
SET SESSION group_concat_max_len = 102400;
SELECt
a.TABLE_NAME ,
b.TABLE_COMMENT ,
concat('DROP TABLE IF EXISTS hive库名.hive表名前缀ods_',a.TABLE_NAME,';CREATE EXTERNAL TABLE IF NOT EXISTS hive库名.hive表名前缀ods_',a.TABLE_NAME ,' (',group_concat(concat(a.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
a.COLUMN_TYPE
WHEN DATA_TYPE = 'float' THEN
'double'
ELSE
DATA_TYPE
END -- 数据类型转换
," COMMENT '",a.COLUMN_COMMENT,"'") order by a.TABLE_NAME,a.ORDINAL_POSITION) ,
") COMMENT '",b.TABLE_COMMENT,"' row format delimited fields terminated by '\t' lines terminated by '\n' stored as textfile;") AS hive建表语句
FROM
(
SELECt
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
ORDINAL_POSITION,
DATA_TYPE,
COLUMN_COMMENT,
COLUMN_TYPE
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
where b.TABLE_TYPE='base TABLE'
and a.TABLE_NAME not like 'ods_%'
GROUP BY
a.TABLE_NAME,
b.TABLE_COMMENT
;
**
oracle转hive转换脚本：**
select to_char(substr(table_prefix || col_strs ||table_subfix, 1, 4000)) con
from (select n.table_prefix,
wm_concat(m.col_str) as col_strs,
n.table_subfix
from (select a.table_name,
'drop table if exists hive库名.hive表名前缀ods_' || lower(a.table_name) ||';
create table if not exists hive库名.hive表名前缀ods_' || lower(a.table_name) ||'(' as table_prefix,
')
PARTITIonED BY (ds STRING comment ''yyyymmdd'')
row format delimited fields terminated by ''t'' lines terminated by ''n'' stored as textfile;' as table_subfix
from user_tables a, user_tab_comments b
where a.table_name = b.table_name
order by a.table_name) n,
(select c.TABLE_NAME,
c.column_name || CASE
WHEN c.DATA_TYPE IN ('CHAR',
'NCHAR',
'VARCHAR',
'VARCHAR2',
'NVARCHAR2',
'DATE',
'TIMESTAMP',
'TIMESTAMP WITH TIME ZONE',
'TIMESTAMP WITH LOCAL TIME ZONE',
'INTERVAL YEAR TO MOTH',
'INTERVAL DAY TO SECOND',
'BLOB',
'CLOB',
'NCLOB',
'BFILE',
'RAW',
'LONG RAW') THEN
' STRING '
WHEN C.DATA_TYPE = 'INTEGER' THEN
' BIGINT '
WHEN C.DATA_TYPE = 'NUMBER' THEN
(CASE
WHEN C.DATA_SCALE IS NOT NULL AND c.DATA_SCALE <> 0 THEN
' DECIMAL(' || C.DATA_PRECISION || ',' ||
C.DATA_SCALE || ') '
WHEN C.DATA_PRECISION < 3 THEN
' TINYINT '
WHEN C.DATA_PRECISION < 5 THEN
' SMALLINT '
WHEN C.DATA_PRECISION < 10 THEN
' INT '
ELSE
' BIGINT '
END)
WHEN C.DATA_TYPE IN
('BINARY_FLOAT', 'BINARY_DOUBLE', 'FLOAT') THEN
' DOUBLE '
ELSE
' STRING '
END || 'comment ''' ||
REGEXP_REPLACE(T.comments,
'[' || CHR(10) || CHR(13) || CHR(9) ||
CHR(32) || ']',
'') || '''' as col_str
from user_tab_cols c, user_col_comments t
where c.TABLE_NAME = t.table_name
and c.COLUMN_NAME = t.column_name) m
where n.table_name = m.table_name
group by n.table_prefix, n.table_subfix)
