oracle脚本一键转hive脚本

最新推荐文章于 2023-06-06 15:53:33 发布

原创

最新推荐文章于 2023-06-06 15:53:33 发布 · 3.3k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#hadoop #hive #oracle

这篇博客主要介绍如何将Oracle数据库中的SQL脚本一键转换为适用于Hive的脚本，特别提醒在转换过程中要注意SQL语句的长度限制，因为Oracle的varchar2类型最大只能存储4000个字符。

注意的地方：拼接的sql可能超出了要截取的长度，字符串varchar2最多存储4000

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 ' || lower(a.table_name) ||
                       ';create table ' || lower(a.table_name) || '(' as table_prefix,
                       ') comment ''' || b.comments ||
                       ''' ROW FORMAT DELIMITED FIELDS TERMINATED BY ''\t'' ESCAPED BY ''\\'' STORED AS TEXTFILE;' as table_subfix
                  from user_tables a, user_tab_comments b
                 where a.table_name = b.table_name
                --and a.table_name = 'MID'
                 order by a.table_name) n,
               (select c.TABLE_NAME,
                       c.column_name || ' ' ||
                       decode(c.data_type,
                              'VARCHAR2',
                              'varchar' || '(' || c.DATA_LENGTH || ')',
                              'NUMBER',
                              'int',
                              'DATE',
                              'timestamp',
                              'CHAR',
                              'CHAR' || '(' || c.DATA_LENGTH || ')',
                              'CLOB',
                              'string',
                              c.data_type) as col_str
                  from user_tab_cols c) m
         where n.table_name = m.table_name
         group by n.table_prefix, n.table_subfix)
union all
select 'alter table ' || c.TABLE_NAME || ' change ' || c.column_name || ' ' ||
       c.column_name || ' ' || decode(c.data_type,
                                      'VARCHAR2',
                                      'varchar' || '(' || c.DATA_LENGTH || ')',
                                      'NUMBER',
                                      'int',