使用java代码编写脚本，把oracle建表语句变成hive建表语句

原创已于 2022-08-25 08:48:54 修改 · 886 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #oracle #hive

于 2022-08-20 15:58:30 首次发布

Java基础学习专栏收录该内容

50 篇文章

订阅专栏

本文介绍了如何使用Java代码实现从Oracle SQL建表语句到Hive SQL的转换，通过`O2H`类展示了将VARCHAR、TIMESTAMP等数据类型映射并简化的过程，最终生成了Hive创表语句。

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

java代码

import java.io.File;
import java.io.IOException;
import java.util.Scanner;

public class O2H {
    public static void main(String[] args) throws Exception {
        getFileContentThree("oracle.sql");
    }
    public static void getFileContentThree(String str) throws IOException {


        Scanner in = new Scanner(new File("D:\\Ideaprojects\\bigdata15_0612\\data\\oracle_sql\\"+str));
        while (in.hasNextLine()) {
            String s = in.nextLine();
            String s1 = s.replaceAll("\"", "");
            String s2 = s1.replaceAll("VARCHAR2\\(64 BYTE\\)", "string");
            String s3 = s2.replaceAll("NVARCHAR2\\(200\\)", "string");
            String s4 = s3.replaceAll("VARCHAR2\\(2000 BYTE\\)", "string");
            String s5 = s4.replaceAll("VARCHAR2\\(500 BYTE\\)", "string");
            String s6 = s5.replaceAll("VARCHAR2\\(200 BYTE\\)", "string");
            String s7 = s6.replaceAll("CLOB", "string");
            String s8 = s7.replaceAll("VARCHAR2\\(64 BYTE\\)", "string");
            String s9 = s8.replaceAll("TIMESTAMP\\(6\\)", "timestamp");
            String s10 = s9.replaceAll("NVARCHAR2\\(255\\)", "string");
            String s11 = s10.replaceAll("CHAR\\(1 BYTE\\)", "string");
            String s12 = s11.replaceAll("DEFAULT '0'   NOT NULL", "");
            String s13 = s12.replaceAll("NOT NULL", "");
            String s14 = s13.replaceAll("TEST\\.", "ODS_");
            String s15 = s14.replaceAll("VARCHAR2\\(255 BYTE\\)", "string");
            String s16 = s15.replaceAll("VARCHAR2\\(1000 BYTE\\)", "string");
            String s17 = s16.replaceAll("NUMBER", "double");
            String s18 = s17.replaceAll("VARCHAR2\\(400 BYTE\\)", "string");
            String s19 = s18.replaceAll("VARCHAR2\\(2550 BYTE\\)", "string");
            String s20 = s19.replaceAll("NVARCHAR2\\(64\\)", "string");
            String s21 = s20.replaceAll("NVARCHAR2\\(2000\\)", "string");
            String s22 = s21.replaceAll("DEFAULT \'\'", "");
            String s23 = s22.replaceAll("VARCHAR2\\(1 BYTE\\)", "string");
            String s24 = s23.replaceAll("VARCHAR2\\(100 BYTE\\)", "string");
            String s25 = s24.replaceAll("VARCHAR2\\(4000 BYTE\\)", "string");
            String s26 = s25.replaceAll("NVARCHAR2\\(500\\)", "string");
            String s27 = s26.replaceAll("NVARCHAR2\\(100\\)", "string");
            String s28 = s27.replaceAll("NUMBER\\(10,0\\)", "double");
            String s29 = s28.toLowerCase();
            String s30 = s29.replaceAll("\\)", ");");
            String s31 = s30.replaceAll("blob", "string");
            String s32 = s31.replaceAll("long", "string");


            System.out.println(s32);
        }
    }
}

测试oracle.sql

CREATE TABLE "TEST"."ORACLE" (
  "ID" VARCHAR2(64 BYTE) NOT NULL,
  "CATEGORY_ID" VARCHAR2(64 BYTE) NOT NULL,
  "CONTENT_ID" VARCHAR2(64 BYTE) NOT NULL,
  "TITLE" VARCHAR2(255 BYTE),
  "CONTENT" VARCHAR2(255 BYTE),
  "NAME" VARCHAR2(100 BYTE),
  "CREATE_DATE" TIMESTAMP(6) NOT NULL
)

生成hive创表语句

create table ods_oracle (
  id string ,
  category_id string ,
  content_id string ,
  title string,
  content string,
  name string,
  create_date timestamp
);