Oracle-SQL转换为Hive-SQL

王林-wlin

已于 2023-03-08 16:36:29 修改

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏：大数据文章标签： oracle sql hive

于 2023-02-24 15:14:56 首次发布

本文为CSDN博主「王林」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。

本文链接：https://blog.csdn.net/qq_45912025/article/details/129201403

大数据专栏收录该内容

1 篇文章

订阅专栏

OracleToHiveSqlUtil是一个Java工具类，用于将OracleSQL语句转换为HiveSQL。该类读取指定的OracleSQL文件，应用一系列替换规则以适应Hive的数据类型和语法，并将结果写入新的HiveSQL文件。转换过程涉及到数据类型如VARCHAR2、NUMBER和DATE的映射，以及添加Hive特定的分区和行格式信息。

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

Oracle-SQL转换为Hive-SQL

Oracle-SQL转换为Hive-SQL工具类

工具类名：OracleToHiveSqlUtil

package com.example.demotest.util;

import lombok.extern.slf4j.Slf4j;

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

/**
 * @author: wanglin
 * @date: 2023-02-22 周三
 * @Version: 1.0
 * @Description: oracle sql语句转换为hive SQL语句
 */
@Slf4j
public class OracleToHiveSqlUtil {
    public static void main(String[] args) throws IOException {
        getFileContentThree("oracle.sql");
    }

    public static void getFileContentThree(String str) throws IOException {

        Scanner in = new Scanner(new File("D:\\sql\\oracle_sql\\" + str));
        String data = "";
        String end = "\t\tcomment '入本地库时间')\n" +
                "partitioned by (dc_etl_time string)\n" +
                "row format delimited fields terminated by '\\001' stored as parquet tblproperties('parquet.compress' = 'SNAPPY');";

        String fileSuffix = ".sql";
        String name = "ods_yzzx.shx_xship222";
        String fileName = name + fileSuffix;

        while (in.hasNextLine()) {
            String s = in.nextLine();
            String s0 = s.toUpperCase();
            String s1 = s0.replaceAll("\"", "");
            String s2 = s1.replaceAll("YZZX", "ods_yzzx");
            String s3 = s2.replaceAll(" NOT NULL", " ");
            String s4 = s3.replaceAll(" NULL", " ");
            String s5 = s4.replaceAll("VARCHAR2", "varchar");
            String s6 = s5.replaceAll(" NUMBER", " decimal");
            String s7 = s6.replaceAll("DATE NULL", "\tstring");
            String s8 = s7.replaceAll("TIMESTAMP NULL", "\tstring");
            String s9 = s8.replaceAll("TIMESTAMP", "\tstring");
            String s10 = s9.replaceAll("DEFAULT '0'   NOT NULL", "");
            String s11 = s10.replaceAll(" DATE ", "\tstring");
            String s12 = s11.replaceAll(" CLOB ", "\tstring");
            String s13 = s12.replaceAll("[)] ,", ")\t\tcomment '备注',");
            String s14 = s13.replaceAll("string ,", "\tstring\tcomment '备注',");
            String s15 = s14.replaceAll("string,", "\tstring\tcomment '备注',");
            String s16 = s15.replaceAll("decimal ,", "\tdecimal\tcomment '备注',");
            String s100 = s16.toLowerCase();
            data = data + "\n" + s100;
        }
        data = data.replaceAll("\n[)];", end);
//        System.out.println(data);
        log.info("-------------------hive sql 解析完成----------------");
        writeToFile(data, fileName);
        log.info("-------------------hive sql 文件生成完毕----------------");
    }

    public static void writeToFile(String data, String fileName) {

        byte[] sourceByte = data.getBytes();
        String path = "D:\\sql\\hive_sql\\";
//        String fileName = "test.txt";
        if (null != sourceByte) {
            try {
                File file = new File(path + fileName);//文件路径（路径+文件名）
                if (!file.exists()) {   //文件不存在则创建文件，先创建目录
                    File dir = new File(file.getParent());
                    dir.mkdirs();
                    file.createNewFile();
                }
                FileOutputStream outStream = new FileOutputStream(file); //文件输出流将数据写入文件
                outStream.write(sourceByte);
                outStream.close();
            } catch (Exception e) {
                e.printStackTrace();
                // do something
            } finally {
                // do something
            }
        }
    }
}