java导出HBASE数据csv格式
主main方法导出,自定义sql导出数据
#jdbc链接工具类
package com.chinawinddata.hbase;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class DBUtils {
public static Connection getConnection(String ip) {
Properties props = new Properties();//1200000
props.setProperty("phoenix.query.timeoutMs", "1200000");
props.setProperty("hbase.rpc.timeout", "1200000");
props.setProperty("hbase.client.scanner.timeout.period", "1200000");
Connection con=null;
try {
// load driver
Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
// get connection
// jdbc 的 url 类似为 jdbc:phoenix [ :<zookeeper quorum> [ :<port number> ] [ :<root node> ] ], \
con=DriverManager.getConnection("jdbc:phoenix:"+ip+":2181",props);
return con;
} catch (Exception e) {
e.printStackTrace();
try {
con.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
return null;
}
}
}
导出类
主方法main
这里我的打包方式为jar包,是因为业务需要。主main方法传入参数即可
public static void main( String[] args ) {
String ip =args[0];//ip
System.out.println("ip:---------"+ip);
String zd =args[1]; //字段
System.out.println("查询参数名"+zd);
String tableName=args[2]; //表名
System.out.println("表名"+tableName);
String tiaojian=args[3]; //查询条件
System.out.println("查询条件"+tiaojian);
Connection conn = DBUtils.getConnection(args[0]);
System.out.println(conn);
if(conn!=null){
query(conn,zd,tableName,tiaojian);
}
}
查询方法
查询数据,设置表头数据和主数据
public static void query(Connection conn,String zd,String tableName,String tiaojian ) {
long startTime = System.currentTimeMillis();
try {
if (conn == null) {
System.out.println("conn is null... 连接失败");
return;
}
if(zd.equals("'*'")){
zd="*";
}
//"select "+zd+" from \""+tableName+"\" where pk like'w012|2019-04-04%' ";
String sql = "select "+zd+" from \""+tableName+"\" "+tiaojian+" ";
System.out.println("sql :-----"+sql);
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
List<Map<String,String>> list = new ArrayList<Map<String,String>>();
//表头
Map<String,String> couMap =new HashMap<String, String>();
boolean isAll=false;
String coumln []=null;
if(zd.equals("*")){
isAll=true;
}else{
coumln=zd.split(",");
for (int i = 0; i < coumln.length; i++) {
couMap.put(coumln[i],coumln[i]);
}
}
ResultSetMetaData rsmd=null;
if (rs != null) {
while (rs.next()) {
Map<String, String> map =new HashMap<String, String>();
if(isAll){
rsmd = rs.getMetaData();
int count=rsmd.getColumnCount();
String[] name=new String[count];
for(int i=0;i<count;i++){
name[i]=rsmd.getColumnName(i+1);
couMap.put(name[i],name[i] );
}
for (int i = 0; i < name.length; i++) {
//map.put(name[i], rs.getString(name[i])==null?" ":rs.getString(name[i]));
map.put(name[i], rs.getString(i+1)==null?" ":rs.getString(i+1));
}
}else{
for (int i = 0; i < coumln.length; i++) {
map.put(coumln[i], rs.getString(i+1)==null?" ":rs.getString(i+1));
}
}
list.add(map);
}
long endTime = System.currentTimeMillis();
System.out.println("查询设置表头用时: "+(endTime-startTime)/1000+"秒");
//导出数据
exportData(list,tableName,couMap);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
生成为CVS格式文件
数据写入数据并生成csv文件
/**
* 生成为CVS文件
*
* @param alist 源数据alist
* @param map csv文件的列表头map
* @param outPutPath 文件路径
* @param fileName 文件名称
* @return
*/
public static void exportData(List<Map<String,String>> alist,String fileName,Map<String,String> map){
File csvFile = null;
BufferedWriter csvFileOutputStream = null;
long startTime = System.currentTimeMillis();
try {
File file = new File(outPutPath);
if (!file.exists()) {
file.mkdir();
}
// 定义文件名格式并创建
csvFile = File.createTempFile(fileName, ".csv", new File(outPutPath));
//csvFile = File.createTempFile(fileName, ".csv");
System.out.println("csvFile保存路径:" + csvFile);
// UTF-8使正确读取分隔符","
csvFileOutputStream = new BufferedWriter(
new OutputStreamWriter( new FileOutputStream(csvFile), "UTF-8"),1024);
System.out.println("csvFileOutputStream:" + csvFileOutputStream);
// 写入文件头部
for (Iterator propertyIterator = map.entrySet().iterator();
propertyIterator.hasNext();) {
java.util.Map.Entry propertyEntry =
(java.util.Map.Entry) propertyIterator.next();
csvFileOutputStream.write(
"" + (String) propertyEntry.getValue() != null ?
(String) propertyEntry.getValue() : "" + "");
if (propertyIterator.hasNext()) {
csvFileOutputStream.write(",");
}
}
csvFileOutputStream.newLine();
// 写入文件内容
for (Iterator iterator = alist.iterator(); iterator.hasNext();) {
Object row = (Object) iterator.next();
for (Iterator propertyIterator = map.entrySet().iterator();
propertyIterator.hasNext();) {
java.util.Map.Entry propertyEntry =
(java.util.Map.Entry) propertyIterator.next();
csvFileOutputStream.write((String) BeanUtils.getProperty(
row, (String) propertyEntry.getKey()));
if (propertyIterator.hasNext()) {
csvFileOutputStream.write(",");
}
}
if (iterator.hasNext()) {
csvFileOutputStream.newLine();
}
}
csvFileOutputStream.flush();
long endTime = System.currentTimeMillis();
System.out.println("导出用时: "+(endTime-startTime)/1000+"秒");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
csvFileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
pom.xml 依赖
设置main 方法为程序入口
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.chinawinddata</groupId>
<artifactId>exportHbaseData</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>exportDataFromHbase</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.5</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.4</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>2.6.0</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.apache.phoenix</groupId>
<artifactId>phoenix-client</artifactId>
<version>1.2.0</version>
</dependency>
<dependency>
<groupId>jdk.tools</groupId>
<artifactId>jdk.tools</artifactId>
<version>1.6</version>
<scope>system</scope>
<systemPath>${JAVA_HOME}/lib/tools.jar</systemPath>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>2.3.2</version>
<configuration>
<source>1.8</source> <!--编译设置 1.8,1.7 -->
<target>1.8</target>
</configuration>
</plugin>
<plugin>
<artifactId> maven-assembly-plugin </artifactId>
<configuration>
<descriptorRefs>
<descriptorRef>jar-with-dependencies</descriptorRef>
</descriptorRefs>
<archive>
<manifest>
<mainClass>com.chinawinddata.hbase.HbaseExportMain</mainClass>
</manifest>
</archive>
</configuration>
<executions>
<execution>
<id>make-assembly</id>
<phase>package</phase>
<goals>
<goal>single</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
这个是过程中遇到的问题,转载地址忘记,导入tool.jar
<dependency>
<groupId>jdk.tools</groupId>
<artifactId>jdk.tools</artifactId>
<version>1.6</version>
<scope>system</scope>
<systemPath>${JAVA_HOME}/lib/tools.jar</systemPath>
</dependency>