Shulou(Shulou.com)06/01 Report--

This article introduces how to read Hbase data in Hive, the content is very detailed, interested friends can use for reference, I hope it can be helpful to you.

The first step is to start hadoop with the command:. / start-all.sh

The second step is to start hive with the command:

. / hive--auxpath / home/dream-victor/hive-0.6.0/lib/hive_hbase-handler.jar,/home/dream-victor/hive-0.6.0/lib/hbase-0.20.3.jar,/home/dream-victor/hive-0.6.0/lib/zookeeper-3.2.2.jar-hiveconf hbase.master=127.0.0.1:60000

Here,-hiveconf hbase.master= points to its own value of hbase.master in hbase-site.xml

The third step, start hbase, command:. / start-hbase.sh

The fourth step is to create the associated table, where the table we want to query already exists in hbase, so use CREATE EXTERNAL TABLE to create it, as follows:

CREATE EXTERNAL TABLE hbase_table_2 (key string, value string) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ("hbase.columns.mapping" = "data:1") TBLPROPERTIES ("hbase.table.name" = "test")

Hbase.columns.mapping points to the corresponding column family; when multi-column, data:1,data:2; multi-column family, data1:1,data2:1

Hbase.table.name points to the corresponding table

Hbase_table_2 (key string, value string), this is the associated table

Let's take a look at the structure of the table to be queried in HBase

Hbase (main): 001test'DESCRIPTION ENABLED 0 > describe 'test'DESCRIPTION ENABLED {NAME = >' test', FAMILIES = > [{NAME = > 'data', COMPRESSION = >' NONE', true VERSIONS = > '3mm, TTL = >' 2147483647', BLOCKSIZE = > '65536' IN_MEMORY = > 'false', BLOCKCACHE = >' true'}]} 1 row (s) in 0.0810 secondshbase (main): 002true' 0 >

Take a look at the data in the table.

Hbase (main): 002 scan 0 > test'ROW COLUMN+CELL row1 column=data:1, timestamp=1300847098583, value=value1 row12 column=data:1, timestamp=1300849056637 Value=value3 row2 column=data:2, timestamp=1300847106880, value=value2 3 row (s) in 0.0160 secondshbase (main): 003purl 0 >

Column families: data:1 and data:2

Key:row1 、 row12 、 row2

Value:value1 、 value3 、 value2

The row,value field in the corresponding test table in hbase_table_2 (key string, value string) corresponds to the value in the test table.

OK, now you can look at the query results.

Let's take a look at hbase_table_2 on the hive command line

Hive > select * from hbase_table_2;OKrow1 value1row12 value3Time taken: 0.197 secondshive >

Compare the data in the test table with the column family of data:1

Row1 column=data:1, timestamp=1300847098583, value=value1 row12 column=data:1, timestamp=1300849056637, value=value3

Consistent with the query results, no problem, and then we add a piece of data to the column family data:1 in hbase

Hbase (main): 003in 0 > put 'test','row13','data:1','value4'0 row (s) in 0.0050 secondshbase (main): 004test','row13','data:1','value4'0 row 0 >

Then look at the hbase_table_2 table

Hive > select * from hbase_table_2;OKrow1 value1row12 value3row13 value4Time taken: 0.165 secondshive >

The new data value4 appears, indicating that the test table of hbase can be queried through hbase_table_2.

Let's query the data in the test table with a value of value3.

Hive > select * From hbase_table_2 where value='value3' Total MapReduce jobs = 1Launching Job 1 out of 1Number of reduce tasks is set to 0 since there's no reduce operatorStarting Job = job_201103231022_0001, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201103231022_0001Kill Command = / home/dream-victor/hadoop-0.20.2/bin/hadoop job-Dmapred.job.tracker=localhost:9001-kill job_201103231022_00012011-03-23 11 Stage-1 map = 0%, reduce = 0% Reduce = 0% 2011-03-23 1111 reduce 233854 Stage-1 map = 100%, reduce = 100%Ended Job = job_201103231022_0001OKrow12 value3Time taken: 11.929 secondshive >

Compare it with hbase's test table.

Row12 column=data:1, timestamp=1300849056637, value=value3

OK, so we can use SQL to query hbase.

The above is just the left corresponding query on the command line. Our aim is to use JAVA code to query useful data. In fact, this is also very simple.

First, the command to start Hive has changed a bit, using the following command:

. / hive-- service hiveserver

Here we use the embedded Derby database by default, which can be found in the hive-site.xml file:

Javax.jdo.option.ConnectionURL jdbc:derby:;databaseName=metastore_db;create=true// specifies the default name and address of the database javax.jdo.option.ConnectionDriverName org.apache.derby.jdbc.EmbeddedDriver

Here, the URL of the database link can use the default: jdbc:hive://localhost:10000/default

With the above preparation, we can use the JAVA code to read the data, as follows:

Public class HiveTest extends TestCase {private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver"; private Connection con; private boolean standAloneServer = true; public void testSelect () throws SQLException {Statement stmt = con.createStatement (); ResultSet res = stmt.executeQuery ("select * from hbase_table_2"); boolean moreRow = res.next () While (moreRow) {System.out.println (res.getString (1) + "," + res.getString (2)); moreRow = res.next ();}} @ Override protected void setUp () throws Exception {super.setUp (); Class.forName (driverName) Con = DriverManager.getConnection ("jdbc:hive://localhost:10000/default", "", ");}}

Result,

Row1,value1row12,value3row13,value4row14,test

Check the results in hbase

ROW COLUMN+CELL row1 column=data:1, timestamp=1300847098583, value=value1 row12 column=data:1, timestamp=1300849056637, value=value3 row13 column=data:1 Timestamp=1300850443699, value=value4 row14 column=data:1, timestamp=1300867550502, value=test, so much for sharing about how to read Hbase data in Hive. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.