在impala中建立hbase的外表

在impala中建立hbase的外表

Impala是Cloudera 开发的所以在CDH大数据环境下 安装简单，CDH自己集成了Impala

Impala可以通过Hive外部表方式和HBase进行整合，步骤如下：

步骤1：创建hbase 表，向表中添加数据

create 'test_info', 'info'
put 'test_info','1','info:name','zhangsan’
put 'test_info','2','info:name','lisi'

步骤2：创建hive表

CREATE EXTERNAL TABLE test_info(key string,name string )
ROW FORMAT SERDE 'org.apache.hadoop.hive.hbase.HBaseSerDe'
STORED by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping"=":key,info:name")
TBLPROPERTIES
("hbase.table.name" = "test_info");

步骤3：刷新Impala表

impala-shell命令进入操作界面

invalidate metadata;

整合完毕，验证一下：

用JDBC java代码调用：

pow引用

<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>1.1.1</version>
        </dependency>

代码：

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

public class ImpalaJDBC {

    private static final String SQL_STATEMENT = "SELECT * FROM test_impala_hbase";
    private static final String IMPALAD_HOST = "172.24.112.13";
    private static final String IMPALAD_JDBC_PORT = "21050";
    private static final String CONNECTION_URL = "jdbc:hive2://" + IMPALAD_HOST + ':' + IMPALAD_JDBC_PORT + "/;auth=noSasl";

    private static final String JDBC_DRIVER_NAME = "org.apache.hive.jdbc.HiveDriver";

    public static void main(String[] args) {
        System.out.println("Cloudera Impala JDBC Example");
        System.out.println("Using Connection URL: " + CONNECTION_URL);
        System.out.println("Running Query: " + SQL_STATEMENT);

        Connection con = null;

        try {

            Class.forName(JDBC_DRIVER_NAME);
            con = DriverManager.getConnection(CONNECTION_URL);
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(SQL_STATEMENT);
            System.out.println("Begin Query Results");
            while (rs.next()) {
                System.out.println(rs.getString(1)+"\t"+rs.getString(2));
            }

            System.out.println("End Query Results");

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
            }
        }
    }
}

结果：