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

最新推荐文章于 2024-05-09 14:33:40 发布

39°风

最新推荐文章于 2024-05-09 14:33:40 发布

阅读量1.5k

点赞数 1

分类专栏：大数据文章标签： hive mysql oracle mysql转hive脚本 oracel转hive

本文链接：https://blog.youkuaiyun.com/weixin_43864336/article/details/120746035

版权

大数据专栏收录该内容

7 篇文章

订阅专栏

该博客提供了从MySQL和Oracle数据库向Hive迁移的转换脚本。脚本包括了DROPTABLE和CREATEEXTERNALTABLE的Hive语句，同时处理了数据类型的转换，如将特定的数据库类型映射为Hive的string、double等类型。适用于进行大规模数据仓库迁移的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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)