刷建表语句
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();
}
}
}
}
