刷建表语句

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 ''01'' stored as textfile location ''ZZZZZZZ/' ||                c.table_name ||                ''';'             else ''        end ,       c.column_id,       c.COLUMN_ID_MIN,       c.COLUMN_ID_MAXfrom (        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.ResultSet Data;import java.sql.SQLException;import java.sql.Statement;//第一步，先把oracle数据抽取到hdfs目录，通过sqoop工具//第二步，修改下面对应的链接，oracle链接即可//第三步，执行程序，等程序执行完毕，数据都到了hyper 表中，可验证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,
" + " case when c.column_id=c.COLUMN_ID_MIN 
"                        + " then 'create external table if not exists " + ORACLEUSERNAME1                        + "_EX."+tableName1+" ( ' 
" + " 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 ''/tmp/imp/' ||u.USERNAME||'/'|| 
"                        + " 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 'NVARCHAR2' then ' string '
"                        + " when 'DATE' then ' string '
" + " when 'INTEGER' then ' string '
"                        + " when 'CHAR' then ' string '
" + " when 'CLOB' then ' string '
"                        + " when 'NCLOB' then ' string '
" + " when 'BLOB' then ' string '
"                        + " when 'LONG RAW' then ' string '
" + " when 'UNDEFINED' then ' string '
"                        + " when 'LONG' then ' string '
" + " when 'Bit' then ' string '
"                        + " when 'TIMESTAMP(6)' then ' string '
" + " when 'Boolean' 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 = '" + tableName                        + "' --order by COLUMN_ID asc 
" + " )c 
" + "left join user_users u on 1=1 
"                        + "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 data  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,
" + " case when c.column_id=c.COLUMN_ID_MIN 
"                            + " then 'create table if not exists " + ORACLEUSERNAME1 + "."+ tableName1 +"( ' 
"                            + " else ', ' 
" + " end 
" + " ||c.sqltxt||
"                            + " case when c.column_id=c.COLUMN_ID_MAX 
" + " then ') clustered by (" + columnname                            + ") into " + buckets + " buckets STORED AS ORC ;' 
" + " 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 'NVARCHAR2' then ' string '
"                            + " when 'DATE' then ' date '
" + " when 'INTEGER' then ' decimal(38,0)'
"                            + " when 'CHAR' then ' string '
" + " when 'CLOB' then ' clob '
"                            + " when 'TIMESTAMP(6)' then ' timestamp '
" + " when 'NCLOB' then ' clob '
"                            + " when 'LONG RAW' then ' string '
" + " when 'LONG' then ' string '
"                            + " when 'UNDEFINED' then ' string '
" + " when 'BLOB' then ' blob '
"                            + " when 'Bit' then ' boolean '
" + " when 'Boolean' then ' boolean '
"                            + " 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 = '" + tableName                            + "' --order by COLUMN_ID asc 
" + " )c 
" + "left join user_users u on 1=1 
"                            + "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 data  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();            }        }    }}