java连接phoenix导出csv格式数据

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>

数据格式