在Oracle、MySQL中执行sql脚本生成hive建表语句

最新推荐文章于 2022-08-20 15:58:30 发布

SunWuKong_Hadoop

最新推荐文章于 2022-08-20 15:58:30 发布

阅读量617

点赞数

CC 4.0 BY-SA版权

分类专栏： hive

本文链接：https://blog.youkuaiyun.com/SunWuKong_Hadoop/article/details/83618929

hive 专栏收录该内容

36 篇文章

订阅专栏

本文介绍了一种从MySQL和Oracle数据库元数据自动生成Hive建表语句的方法，包括数据类型映射和注释处理，适用于数据迁移场景。

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

python3脚本：

#mysql
for i in range(len(table_name)):
    sql1 = """SELECT
    CONCAT('create table ','%s','(')
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 = 'lqioc_ioc_yw'
 and t1.TABLE_NAME = '%s'
UNION ALL
SELECT
    concat(
        ')',
        'COMMENT \\'',
        COALESCE (t2.TABLE_COMMENT ,'%s'),
        '\\'
-- PARTITIONED BY (DATE STRING COMMENT \\'日期分区\\') -- 分区表取消注释
ROW FORMAT DELIMITED FIELDS TERMINATED BY \\',\\' STORED AS TEXTFILE;'
        )
    FROM
        information_schema. TABLES t2
    WHERE
        t2.table_schema = 'lqioc_ioc_yw'
    and t2.table_name = '%s'
union all 
""" % (table_name[i],table_name[i],table_name[i],table_name[i])

#oracle
for i in range(len(table_name)):
    sql1 = """SELECT 'create table lqioc_ioc_ods.' || '%s' || ' (' FROM dual UNION ALL
    	SELECT
    		col
    	FROM
    		(
    			SELECT
    				T .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) || ']',
    				''
    			) || ''',' col
    			FROM
    				all_COL_COMMENTS T,
    				all_TAB_COLUMNS c
    			WHERE
    				c.column_name = T .column_name
    			AND c. OWNER = T . OWNER
    			AND c.TABLE_NAME = T .TABLE_NAME
    			AND c. OWNER = 'SJGJ'
    			AND c.TABLE_NAME = '%s'
    			ORDER BY
    				c.COLUMN_ID
    		)
    	UNION ALL
    		SELECT
    			')ROW FORMAT DELIMITED FIELDS TERMINATED BY ''\001'' TBLPROPERTIES(''creator''=''sunruzi'',''create_at''=''' || TO_CHAR (
    				SYSDATE,
    				'yyyy-MM-dd hh24:mi:ss'
    			) || ''');'
    		FROM
    			all_tab_comments T
    		WHERE
    			OWNER = 'SJGJ'AND table_name = '%s'
    	union all """ % (table_name[i],table_name[i],table_name[i])

在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 := 'XXX')
    and t2.table_name = (@tbl_name := 'XXX')

在Oracle中生成建表语句：

--根据oracle元数据生成hive建表语句
select 'create table bigdata_ods.' || &tb_name || ' ('
  from dual
union all
select col
  from (select t.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) || ']',
                              '') || ''',' col
          from all_COL_COMMENTS t, all_TAB_COLUMNS c
         where c.column_name = t.column_name
           and c.owner = t.owner
           and c.TABLE_NAME = t.TABLE_NAME
           and c.owner = &schm_name
           and c.TABLE_NAME = &tb_name
         order by c.COLUMN_ID)
union all
select 'etl_time string comment ''etl时间'') comment ''' || t.comments ||
       ''' ROW FORMAT DELIMITED FIELDS TERMINATED BY ''\001'' TBLPROPERTIES(''creator''=''zhangfan'',''create_at''=''' ||
       to_char(sysdate, 'yyyy-MM-dd hh24:mi:ss') || ''');'
  from all_tab_comments t
 where owner = &schm_name
   and table_name = &tb_name;

原文地址：https://blog.youkuaiyun.com/fanlying/article/details/78683360