<?xml version="1.0"?><?xml-stylesheet type="text/xsl" href="configuration.xsl"?><!-- Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.--><configuration> <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://192.168.32.100:3306/hive_db?createDatabaseIfNotExist=true</value> </property> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>root</value> </property> <property> <name>javax.jdo.option.ConnectionPassword</name> <value>bill</value> </property> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.jdbc.Driver</value> </property> <!-- 下面两个配置用来连接hbase --> <property> <name>hive.aux.jars.path</name> <value>file:///home/hadoop/hive/lib/hive-hbase-handler-0.9.0.jar,file:///home/hadoop/hive/lib/hbase-0.92.0.jar,file:///home/hadoop/hive/lib/zookeeper-3.4.3.jar</value> </property> <property> <name>hbase.zookeeper.quorum</name> <value>v5,v6,v7</value> </property></configuration>
1. 建立关联表
1.1. 创建表语句
这里我们要查询的表在hbase中已经存在，所以，使用CREATE EXTERNAL TABLE来建立，如下：
单列：
|
CREATE EXTERNAL TABLE hbase_table_2(key string,value int) STORED BY ‘org.apache.hadoop.hive.hbase.HBaseStorageHandler’ WITH SERDEPROPERTIES (“hbase.columns.mapping“ = “content:info“) TBLPROPERTIES (“hbase.table.name“ = “stat“);
|
多列：
|
CREATE EXTERNAL TABLE hbase_table_3(key string,notename string,createtime string,status string,content string) STORED BY ‘org.apache.hadoop.hive.hbase.HBaseStorageHandler’ WITH SERDEPROPERTIES (“hbase.columns.mapping” = “ni:nn,ni:ct,ni:st,ci:c“) TBLPROPERTIES (“hbase.table.name” = “n“);
|
参数说明：
hbase.columns.mapping指向对应的列族；
多列时，content:info，content:info2；
多列族时，content1:info, content2:info；
hbase.table.name指向对应的表；
hbase_table(key string, value string)，这个是关联表
1.2. 对应的hbase中的结构
HBase中要查询的表的结构，
hbase(main):001:0> describe ‘test’
{NAME => ‘stat’, FAMILIES => [{NAME => ‘content’, DATA_BLOCK_ENCODING => ‘NON true
E’, BLOOMFILTER => ‘NONE’, REPLICATION_SCOPE => ‘0’, VERSIONS => ‘3’, COMPRES
SION => ‘NONE’, MIN_VERSIONS => ‘0’, TTL => ‘2147483647’, KEEP_DELETED_CELLS
=> ‘false’, BLOCKSIZE => ‘65536’, IN_MEMORY => ‘false’, ENCODE_ON_DISK => ‘tr
ue’, BLOCKCACHE => ‘true’}]}
1.3. 表中的数据
hbase_table(key string, value string)中的key对应的stat表中的row，value字段对应的是stat表中的value
|
hbase(main):008:0> scan ‘stat’
ROW COLUMN+CELL
Apache column=content:info, timestamp=1433791930042, value=1
Hadoop column=content:info, timestamp=1433791930042, value=5
The column=content:info, timestamp=1433791930042, value=2
a column=content:info, timestamp=1433791930042, value=1
|
2. 查询
hive> select * from hbase_table_2;
3. 在hbase中新增
在hbase中新增数据，hive中会实时的查询到。
4. java代码操作
4.1. 启动hive的server端
./hive –service hiveserver
4.2. 导包
将hive下的包导入
4.3. 代码
|
package com.itcast.hive.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class HiveJDBC {
/**
* @param args
*/
public static void main(String[] args) {
try {
Class.forName(“org.apache.hadoop.hive.jdbc.HiveDriver”);
Connection con = DriverManager.getConnection(“jdbc:hive://master:10000/default”,””,””);
Statement stmt = con.createStatement();
String querySQL=”select * from hbase_table_2″;
ResultSet res = stmt.executeQuery(querySQL);
while (res.next()) {
System.out.println(res.getString(1)+”\t”);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
|
测试：
hive:
hive> create external table hbase_person(key string,value string) stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' with serdeproperties ("hbase.columns.mapping"="info:name") tblproperties ("hbase.table.name"="person");
hbase:
hbase(main):020:0> describedescribe describe_namespacehbase(main):020:0> describe 'person'DESCRIPTION ENABLED 'person', {NAME => 'hobby', DATA_BLOCK_ENCODING => 'NONE', BLOOMFILTER => 'ROW', REPLICATION_SCOPE => '0', VE true RSIONS => '1', COMPRESSION => 'NONE', MIN_VERSIONS => '0', TTL => '2147483647', KEEP_DELETED_CELLS => 'false' , BLOCKSIZE => '65536', IN_MEMORY => 'false', BLOCKCACHE => 'true'}, {NAME => 'info', DATA_BLOCK_ENCODING => 'NONE', BLOOMFILTER => 'ROW', REPLICATION_SCOPE => '0', VERSIONS => '1', COMPRESSION => 'NONE', MIN_VERSIONS => '0', TTL => '2147483647', KEEP_DELETED_CELLS => 'false', BLOCKSIZE => '65536', IN_MEMORY => 'false', BLOCK CACHE => 'true'} 1 row(s) in 0.2460 secondshbase(main):021:0> scan 'person'ROW COLUMN+CELL r1 column=hobby:music, timestamp=1435362428030, value=bu jian bu san r1 column=info:age, timestamp=1435362339452, value=20 r1 column=info:name, timestamp=1435362267657, value=bill r2 column=info:name, timestamp=1435362845834, value=lulu r3 column=info:name, timestamp=1435362855969, value=fly 3 row(s) in 0.1200 secondshbase(main):022:0>
hive查询：
hive> select * from hbase_person;OKr1 billr2 lulur3 flyTime taken: 0.458 seconds, Fetched: 3 row(s)hive>