mysql，oracle 一键转hive建表语句、oracle转hive表结构转换脚本 、mysql转hive、oracle转hive

**

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)