hive系列-hive整合hbase

hive整合hbase

1、在hive控制台建库，建表（注意，int，long，boolean这些数据类型，映射时要加上#b，不然查询到的数据会是乱码或者null）

create database credit_mate_data;

use credit_mate_data;

create external table shop_order_info(

key string,

ticket_head string,

token string,

trans_status string,

actual_fee bigint,

create_time string,

flow_no string,

goods_num int,

order_address string,

order_create_time string,

order_finish_time string,

order_full_name string,

order_mobile string,

order_pay_time string,

order_post string,

order_send_time string,

other_order_num string,

post_fee int,

shop_name string

)

STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'

WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,

SHOP_INFO:ticket_head,

SHOP_INFO:token,

SHOP_INFO:trans_status,

SHOP_INFO:actual_fee#b,

SHOP_INFO:create_time,

SHOP_INFO:flow_no,

SHOP_INFO:goods_num#b,

SHOP_INFO:order_address,

SHOP_INFO:order_create_time,

SHOP_INFO:order_finish_time,

SHOP_INFO:order_full_name,

SHOP_INFO:order_mobile,

SHOP_INFO:order_pay_time,

SHOP_INFO:order_post,

SHOP_INFO:order_send_time,

SHOP_INFO:other_order_num,

SHOP_INFO:post_fee#b,

SHOP_INFO:shop_name

")

TBLPROPERTIES ("hbase.table.name" = "SHOP");

2、建完表后就可以直接在hive控制台(注意：hive控制台必须切换到hdfs用户启动，不然没有启动mapreduce的权限)查询数据了，下面是执行的sql

select * from shop_order_info where key like 'SO%';

select token,trans_status,actual_fee,goods_num,post_fee from shop_order_info where key like 'SO%';

select * from shop_order_info limit 5;

3、java用jdbc连接hive

maven的依赖

<dependency>

<groupId>org.apache.hadoop</groupId>

<artifactId>hadoop-common</artifactId>

<version>2.6.0</version>

</dependency>

<dependency>

<groupId>org.apache.hive</groupId>

<artifactId>hive-jdbc</artifactId>

<version>1.1.0</version>

</dependency>

<dependency>

<groupId>org.apache.hive</groupId>

<artifactId>hive-service</artifactId>

<version>1.1.0</version>

</dependency>

<dependency>

<groupId>commons-logging</groupId>

<artifactId>commons-logging</artifactId>

<version>1.1</version>

</dependency>

<!-- Logging with SLF4J & LogBack -->

<dependency>

<groupId>org.slf4j</groupId>

<artifactId>slf4j-api</artifactId>

<version>1.7.13</version>

<scope>compile</scope>

</dependency>

<dependency>

<groupId>ch.qos.logback</groupId>

<artifactId>logback-classic</artifactId>

<version>1.1.3</version>

<scope>runtime</scope>

</dependency>

java代码

package com.boat.hive;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import org.junit.Test;

/**

* @Description: TODO(用一句话描述该文件做什么)

* @author boat

* @date 2017年4月26日 上午8:52:16

* @version V1.0

*/

public class TestHiveConnect {

@Test

public void testHiveConnect() {

String driverName = "org.apache.hive.jdbc.HiveDriver";

String sql = "select * from shop_order_info where key like 'SO%' limit 5";

try {

Class.forName(driverName);

} catch (ClassNotFoundException e) {

e.printStackTrace();

System.exit(1);

}

Connection connection = null;

try {

connection = DriverManager.getConnection("jdbc:hive2://master1:10000/credit_mate_data", "hdfs", "");

Statement stmt = connection.createStatement();

ResultSet rs = stmt.executeQuery(sql);

ResultSetMetaData m = rs.getMetaData();

int columns = m.getColumnCount();

// 显示列,表格的表头

for (int i = 1; i <= columns; i++) {

System.out.print(m.getColumnName(i));

System.out.print("\t\t");

}

System.out.println();

// 显示表格内容

while (rs.next()) {

for (int i = 1; i <= columns; i++) {

System.out.print(rs.getString(i));

System.out.print("\t\t");

}

System.out.println();

}

connection.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}