Oracle刷出整库建表语句

最新推荐文章于 2022-04-27 15:50:27 发布
最新推荐文章于 2022-04-27 15:50:27 发布
阅读量169 收藏
点赞数
文章标签： 数据库 java 大数据
原文链接：https://yq.aliyun.com/articles/625517
版权

刷建表语句

select c.table_name,
       case when c.column_id=c.COLUMN_ID_MIN 
            then 'create external table '||u.USERNAME||'_'||c.table_name||' ( ' 
            else ',' 
       end 
       ||c.sqltxt||
       case when c.column_id=c.COLUMN_ID_MAX 
            then ') row format DELIMITED FIELDS terminated by ''\001'' stored as textfile location ''ZZZZZZZ/' || 
               c.table_name || 
               ''';' 
            else '' 
       end ,
       c.column_id,
       c.COLUMN_ID_MIN,
       c.COLUMN_ID_MAX
from (        
select                 table_name,
                       column_name || ' ' ||
                       case data_type
                       when 'NUMBER' then '    decimal(' || data_length || ',' || nvl(data_scale,0) || ') '
                       when 'FLOAT' then '    decimal(' || data_length || ',' || nvl(data_scale,0) || ') '
                       when 'VARCHAR2' then '    string '
                       when 'DATE' then '    string '
                       when 'INTEGER' then '    decimal(38,0)'
                       when 'CHAR' then '    string '
                       end sqlTxt
                       ,COLUMN_ID
                       ,min(COLUMN_ID)over(partition by table_name) as COLUMN_ID_MIN
                       ,max(COLUMN_ID)over(partition by table_name) as COLUMN_ID_MAX
                from   user_tab_columns 
                --where  table_name='EMP'
                --order  by COLUMN_ID asc     
                )c 
left join user_users u on 1=1               
order by c.table_name,c.COLUMN_ID  asc

代码程序

package oa.epoint.com;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

//第一步，先把oracle数据抽取到hdfs目录，通过sqoop工具
//第二步，修改下面对应的链接，oracle链接即可
//第三步，执行程序，等程序执行完毕，数据都到了hyperbase表中，可验证
public class AutoImportDataToORC {
    private static String ORACLEUSERNAME = "esv12001";
    private static String ORACLEPASSWORD = "tjzysj";
    private static String ORACLEDRIVER = "oracle.jdbc.OracleDriver";
    private static String ORACLEURL = "jdbc:oracle:thin:@10.12.8.234:1521:orcl";

    private static String HIVEDRIVER = "org.apache.hive.jdbc.HiveDriver";
    private static String HIVEURL = "jdbc:hive2://10.12.8.152:10000/default";
    private static String HIVEUSERNAME = "hive";
    private static String HIVEPASSWORD = "hive123";

    Connection oracleconn = null;
    Statement oraclepstm = null;
    ResultSet oraclers = null;

    Connection hiveconn = null;
    Statement hivepstm = null;
    ResultSet hivers = null;

    String sql1 = " ";
    String sql2 = " ";
    String sql3 = " ";
    String sql4 = " ";
    String sql5 = " ";
    String sql6 = " ";
    String ORACLEUSERNAME1 = ORACLEUSERNAME.replaceAll("001", "");

    public static void main(String[] args) throws Exception {
        AutoImportDataToORC aidth = new AutoImportDataToORC();
        aidth.CreateExternalTable();
        aidth.CreateOrcTable();
        aidth.ImportDataToORC();
        System.out.println("程序已经执行完毕！请去waterdrop验证结果吧！！");
    }

    public void CreateExternalTable() {
        oracleconn = getOracleConnection();
        hiveconn = getHiveConnection();
        String sql0 = "dfs -du /tmp/imp/" + ORACLEUSERNAME.toUpperCase();
        try {
            hivepstm = hiveconn.createStatement();
            oraclepstm = oracleconn.createStatement();
            hivers = hivepstm.executeQuery(sql0);
            int i = 0;
            while (hivers.next()) {
                String hdfsspace = hivers.getString(1);
                String a[] = hdfsspace.split("/", 2);
                String size = a[0];
                String tableNameAndFloder = a[1];
                String ss[] = tableNameAndFloder.split("/");
                String tableName = ss[3];
                if(Long.parseLong(size.trim()) !=  0L){
                i = i + 1;
                String tableName1 = tableName.replaceAll("\\$", "");
                String sql = "select c.table_name,\r\n" + " case when c.column_id=c.COLUMN_ID_MIN \r\n"
                        + " then 'create external table if not exists " + ORACLEUSERNAME1
                        + "_EX."+tableName1+" ( ' \r\n" + " else ', ' \r\n" + " end \r\n" + " ||c.sqltxt||\r\n"
                        + " case when c.column_id=c.COLUMN_ID_MAX \r\n"
                        + " then ') row format DELIMITED FIELDS terminated by ''\\001'' stored as textfile location ''/tmp/imp/' ||u.USERNAME||'/'|| \r\n"
                        + " c.table_name || \r\n" + " ''';' \r\n" + " else '' \r\n" + " end ,\r\n" + " c.column_id,\r\n"
                        + " c.COLUMN_ID_MIN,\r\n" + " c.COLUMN_ID_MAX\r\n" + "from ( \r\n" + "select table_name,\r\n"
                        + "'`' || column_name || '` ' || \r\n" + " case data_type\r\n"
                        + " when 'NUMBER' then ' decimal(' || data_length || ',' || nvl(data_scale,0) || ') '\r\n"
                        + " when 'FLOAT' then ' decimal(' || data_length || ',' || nvl(data_scale,0) || ') '\r\n"
                        + " when 'VARCHAR2' then ' string '\r\n" + " when 'NVARCHAR2' then ' string '\r\n"
                        + " when 'DATE' then ' string '\r\n" + " when 'INTEGER' then ' string '\r\n"
                        + " when 'CHAR' then ' string '\r\n" + " when 'CLOB' then ' string '\r\n"
                        + " when 'NCLOB' then ' string '\r\n" + " when 'BLOB' then ' string '\r\n"
                        + " when 'LONG RAW' then ' string '\r\n" + " when 'UNDEFINED' then ' string '\r\n"
                        + " when 'LONG' then ' string '\r\n" + " when 'Bit' then ' string '\r\n"
                        + " when 'TIMESTAMP(6)' then ' string '\r\n" + " when 'Boolean' then ' string '\r\n"
                        + " end sqlTxt\r\n" + " ,COLUMN_ID\r\n"
                        + " ,min(COLUMN_ID)over(partition by table_name) as COLUMN_ID_MIN\r\n"
                        + " ,max(COLUMN_ID)over(partition by table_name) as COLUMN_ID_MAX\r\n"
                        + " from user_tab_columns \r\n" + " where table_name = '" + tableName
                        + "' --order by COLUMN_ID asc \r\n" + " )c \r\n" + "left join user_users u on 1=1 \r\n"
                        + "order by c.table_name,c.COLUMN_ID asc";
                oraclers = oraclepstm.executeQuery(sql);
                while (oraclers.next()) {
                    sql1 = oraclers.getString(2);
                    sql2 = sql2 + sql1;
                }
            }
            }
            sql3 = "create database IF NOT EXISTS " + ORACLEUSERNAME1 + "_ex";
            hivepstm.execute(sql3);
            System.out.println("-------------------建外表,一共" + i + "个表表结构的语句为：" + sql2);
            hivepstm.execute(sql2);
            System.out.println("----------------------------------------建外表已结束！！！！！！！！！！！！！！！！！！！！！！！！！！！！！");
            hivepstm.close();
            oraclepstm.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            OracleReleaseResource();
            HiveReleaseResource();
        }
    }

    public void CreateOrcTable() {
        oracleconn = getOracleConnection();
        hiveconn = getHiveConnection();
        String sql0 = "dfs -du /tmp/imp/" + ORACLEUSERNAME.toUpperCase();
        int i = 0;
        try {
            hivepstm = hiveconn.createStatement();
            oraclepstm = oracleconn.createStatement();
            hivers = hivepstm.executeQuery(sql0);
            while (hivers.next()) {
                String hdfsspace = hivers.getString(1);
                String a[] = hdfsspace.split("/", 2);
                String size = a[0];
                String tableNameAndFloder = a[1];
                String ss[] = tableNameAndFloder.split("/");
                String tableName = ss[3];
                if(Long.parseLong(size.trim()) !=  0L){

                Long buckets = 0L;
                buckets = Long.parseLong(size.trim()) / (60 * 1024 * 1024) + 1;
                String sql_0 = "select column_name from user_tab_columns where table_name = '" + tableName.toUpperCase()
                        + "' and column_id = 1";
                oraclers = oraclepstm.executeQuery(sql_0);
                String tableName1 = tableName.replaceAll("\\$", "");
                i = i + 1;
                while (oraclers.next()) {
                    String columnname = oraclers.getString(1);
                    String sql = "select c.table_name,\r\n" + " case when c.column_id=c.COLUMN_ID_MIN \r\n"
                            + " then 'create table if not exists " + ORACLEUSERNAME1 + "."+ tableName1 +"( ' \r\n"
                            + " else ', ' \r\n" + " end \r\n" + " ||c.sqltxt||\r\n"
                            + " case when c.column_id=c.COLUMN_ID_MAX \r\n" + " then ') clustered by (" + columnname
                            + ") into " + buckets + " buckets STORED AS ORC ;' \r\n" + " else '' \r\n" + " end ,\r\n"
                            + " c.column_id,\r\n" + " c.COLUMN_ID_MIN,\r\n" + " c.COLUMN_ID_MAX\r\n" + "from ( \r\n"
                            + "select table_name,\r\n" + " '`' || column_name || '` ' ||\r\n" + " case data_type\r\n"
                            + " when 'NUMBER' then ' decimal(' || data_length || ',' || nvl(data_scale,0) || ') '\r\n"
                            + " when 'FLOAT' then ' decimal(' || data_length || ',' || nvl(data_scale,0) || ') '\r\n"
                            + " when 'VARCHAR2' then ' string '\r\n" + " when 'NVARCHAR2' then ' string '\r\n"
                            + " when 'DATE' then ' date '\r\n" + " when 'INTEGER' then ' decimal(38,0)'\r\n"
                            + " when 'CHAR' then ' string '\r\n" + " when 'CLOB' then ' clob '\r\n"
                            + " when 'TIMESTAMP(6)' then ' timestamp '\r\n" + " when 'NCLOB' then ' clob '\r\n"
                            + " when 'LONG RAW' then ' string '\r\n" + " when 'LONG' then ' string '\r\n"
                            + " when 'UNDEFINED' then ' string '\r\n" + " when 'BLOB' then ' blob '\r\n"
                            + " when 'Bit' then ' boolean '\r\n" + " when 'Boolean' then ' boolean '\r\n"
                            + " end sqlTxt\r\n" + " ,COLUMN_ID\r\n"
                            + " ,min(COLUMN_ID)over(partition by table_name) as COLUMN_ID_MIN\r\n"
                            + " ,max(COLUMN_ID)over(partition by table_name) as COLUMN_ID_MAX\r\n"
                            + " from user_tab_columns \r\n" + " where table_name = '" + tableName
                            + "' --order by COLUMN_ID asc \r\n" + " )c \r\n" + "left join user_users u on 1=1 \r\n"
                            + "order by c.table_name,c.COLUMN_ID asc";

                    oraclers = oraclepstm.executeQuery(sql);
                    while (oraclers.next()) {
                        sql4 = oraclers.getString(2);
                        sql5 = sql5 + sql4;
                    }
                }
            }
        }
            sql6 = "create database IF NOT EXISTS " + ORACLEUSERNAME1;
            System.out.println("-------------------建orc表库名的语句为：" + sql6);
            hivepstm.execute(sql6);
            System.out.println("-------------------建ORC表,一共" + i + "个表的表结构的语句为：" + sql5);
            hivepstm.execute(sql5);
            System.out.println("----------------------------------------建ORC表已结束！！！！！！！！！！！！！！！！！！！！！！！！！！！！！");
//          hivepstm.close();
//          oraclepstm.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            OracleReleaseResource();
            HiveReleaseResource();
        }
    }

    public void ImportDataToORC() {
        oracleconn = getOracleConnection();
        hiveconn = getHiveConnection();
        String sql = "select table_name from user_tables where num_rows > 0 order by table_name asc";
        int i = 0;
        try {
            oraclepstm = oracleconn.createStatement();
            oraclers = oraclepstm.executeQuery(sql);
            hivepstm = hiveconn.createStatement();
            while (oraclers.next()) {
                i = i + 1;
                String table_name = oraclers.getString("table_name").replaceAll("\\$", "");
                String sql7 = "insert into " + ORACLEUSERNAME1 + "." + table_name + " select * from " + ORACLEUSERNAME1
                        + "_ex." + table_name;
                System.out.println("现在插入第"+i+"个表："+sql7);
                hivepstm.execute(sql7);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            OracleReleaseResource();
            HiveReleaseResource();
        }
    }

    public Connection getOracleConnection() {
        try {
            Class.forName(ORACLEDRIVER);
            oracleconn = DriverManager.getConnection(ORACLEURL, ORACLEUSERNAME, ORACLEPASSWORD);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("class not find !", e);
        } catch (SQLException e) {
            throw new RuntimeException("get connection error!", e);
        }

        return oracleconn;
    }

    public void OracleReleaseResource() {
        if (oraclers != null) {
            try {
                oraclers.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (oraclepstm != null) {
            try {
                oraclepstm.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (oracleconn != null) {
            try {
                oracleconn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public Connection getHiveConnection() {
        try {
            Class.forName(HIVEDRIVER);
            hiveconn = DriverManager.getConnection(HIVEURL, HIVEUSERNAME, HIVEPASSWORD);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("class not find !", e);
        } catch (SQLException e) {
            throw new RuntimeException("get connection error!", e);
        }
        return hiveconn;
    }

    public void HiveReleaseResource() {
        if (hivers != null) {
            try {
                hivers.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (hivepstm != null) {
            try {
                hivepstm.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (hiveconn != null) {
            try {
                hiveconn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
weixin_34186128
关注
Oracle刷表
bingoabin
03-14 4664
代码实例select c.table_name, case when c.column_id=c.COLUMN_ID_MIN then 'create external table '||u.USERNAME||'_'||c.table_name||' ( ' else ',' end ||c.sqlt
oracle刷数据库表
03-03
创建唯一索引: CREATE unique INDEX 索引名 ON 表名 列名 TABLESPACE 表空间名; 例如：create unique index IND ZL JGSPJWGXB on ZL JGSPJWGXB JGID SPID ;
Oracle刷建表语句
weixin_33835103的博客
03-14 132
代码实例 select c.table_name, case when c.column_id=c.COLUMN_ID_MIN then 'create external table '||u.USERNAME||'_'||c.table_...
Oracle数据库刷库步骤
漂亮的代码
11-06 1340
以dba登录 sqlplus / as sysdba; 删除原有的同名用户 drop user XXX cascade; 创建新用户 create user XXX identified by pwd default tablespace XXX_DATA temporary tablespace XXX_DATA; 给用户授权 GRANT CONNECT,RESOURCE TO XXX; ...
oracle 批量刷数据库,Oracle批量操作数据库
weixin_28956753的博客
04-07 191
1：批量插入insert into RECIPEDETAIL (RDID, ROID, TYPE,NAME, MEDIWEIGHT, MEDINUM,MONEY, OPERATETIME, CHARGENUMBER,REMARK, INSTRUCTION, AMOUNTEVERY,MEDIUNIT, STATUS)select SEQ_recipeDetail.NEXTVAL,A.* from(s...
Oracle建表语句demo（建表语句、索引、序列）
最新发布
04-22
提供了一个Oracle数据库建表语句的完整示例代码，用于演示如何在Oracle环境中创建一个结构化的数据表。 资源优点： 完整性展示：DEMO提供了从基础建表到复杂约束（如外键、自增主键）等实际生产场景所需的完整SQL...
c# oracle转starRocks(doris)建表语句
07-31
- SQL脚本：这些脚本可能包含了从Oracle到StarRocks的建表语句模板，需要根据实际的Oracle表结构进行调整。 - 工具代码：可能是用C#编写的工具类，用于自动化执行上述步骤，包括连接、读取、转换和写入数据。 总...
Oracle触发器销售库存建表语句
01-25
Oracle触发器文章配套建表语句。库存明细表：e_stock_info。销售明细表：e_sales_info（这里创建的是按日自动分区表）。
通过xlrd读取excel生成oracle的建表语句
04-06
本话题主要关注如何利用Python的xlrd库来读取Excel文件，并根据其中的数据生成适用于Oracle数据库的建表语句。 首先，xlrd是一个Python库，专门用于读取Excel文件。它支持多种Excel文件格式，包括.xls和.xlsx，使得...
oracle到mysql建表语句迁移
03-19
oracle向mysql建表语句的迁移。 直接表结构的生成sql脚本
批量创建表
05-30
用存储过程通过两张字典表批量创建表（先删除后创建），内含，存储过程代码和两张表的结构和信息
Oracle获取干净的建表DDL语句，不含其它存储、表空间、段属性
weixin_30879833的博客
08-19 851
早上一个同事资讯怎么获取到建表语句而且是不带存储那种SQL。Oracle自己提供了一个函数DBMS_METADATA.GET_DDL，但是获取到的建表语句含有存储、表空间、以及一些其他段的属性。如图： 看到这个获取到的ddl语句，想通过利用Oracle函数来截取的方式获取建表语句。 思路为:1.通过get_ddl获取建表语句 abc2.将abc中的pctfree'替换成';'3.计算';'...
oracle中mod和mysql_MySQL和Oracle的添加字段的处理差别
weixin_39639600的博客
01-18 124
昨天在微信群中有个朋友也是无意中问了一下，说数据库中的表字段想保持一种相对规范的顺序，怎么办？要知道Oracle中这个操作就比较纠结了，因为是按照追加的方式来处理的。没法在已有的字段1，字段2中间添加一个字段3。但是MySQL却可以，这个方面MySQL看起来要灵活的多，这个是什么原因呢，他们在设计上有什么差别呢。MySQL中对每个表存在一个定义文件，即frm文件，我们来取出一个表，看看能不能简单解...
oracle数据库表字段的更名、属性更改及id自动增长
chenzhjlf的专栏
10-05 878
oracle建表： create table INDEX_POLICY_TBL ( ID NUMBER(10) NOT NULL PRIMARY KEY, POLICY_ID VARCHAR2(64) NOT NULL, ALARM_COUNT NUMBER(10) NOT NULL ) 执行即可。 增加字段： ALTER TABLE TABLE_NAME ADD(COLUMN_NAME COLU...
Oracle_查询表结构_每列属性
icecoola_的博客
07-23 2877
1 select t.COLUMN_NAME, t.DATA_TYPE, t.DATA_LENGTH, t.DATA_PRECISION, t.NULLABLE, t.COLUMN_ID, c.COMMENTS from user_tab_columns t, user_col_comments c wher...
oracle 表 备注字段,Oracle 查询表信息（字段+备注）
weixin_35980523的博客
04-06 195
获取表字段：select*fromuser_tab_columnswhereTable_Name="用户表"orderbycolumn_name获取表注释：select*fromuser_tab_commentswhereTable_Name="用户表"orderbyTable_Name获取字段注释：select*fromuser_col_commentswhereTable_Name="用户表"...
oracle的column命令详解,Oracle的column命令详解
weixin_36216860的博客
04-11 2099
Oracle column命令可以对列的现实效果，包括对列标题和列数据，进行格式化，该命令的语法如下：COL[UMN] [{column | expr} [option ...] ]column指定列名options指定用于格式化列或别名的一个或者多个选项。其中的option选项如下：ALI[AS] alias 指定要格式化的列的别名CLE[AR] 清楚列的格式化ENTMA...
如何从Oracle获取当前库中所有表空间创建的语句
violetlength的专栏
04-27 2311
方便Oracle表空间语句获取与创建；
Oracle如何批量导出数据库表结构（建表语句）
热门推荐
kngines
11-14 3万+
1. 用system用户创建路径 utl_dir用于存放文件-- C:/dba必需是已经存在的目录create or replace directory UTL_DIR as 'C:\dba'; --用sys用户登录给要访问的用户指定访问目录的权限 grant write on directory UTL_DIR to public; grant read on directory UTL_DIR
oracle建表语句转MySQL建表语句
12-07
以下是将Oracle建表语句转换为MySQL建表语句的方法： ```python import re # 定义函数，将Oracle数据类型转换为MySQL数据类型 def oracle_to_mysql(data_type): if 'NUMBER' in data_type: return 'decimal' ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值