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

Hive and HBase integration process of the basic command operation is how, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

First: the integration process of Hive and Hbase.

1. Create a table recognized by HBase, and then check to see if the table is created successfully.

Hive:

Hive > CREATE TABLE hivehbase (key int, value string)

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

> WITH SERDEPROPERTIES ("hbase.columns.mapping" = ": key,columnfamily1:val")

> TBLPROPERTIES ("hbase.table.name" = "hbasehive")

Hive > CREATE TABLE hivehbase (key int, value string) > STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' > WITH SERDEPROPERTIES ("hbase.columns.mapping" = ": key,columnfamily1:val") > TBLPROPERTIES ("hbase.table.name" = "hbasehive"); OKTime taken: 9.881 secondshive > show tables;OKhivehbasetesthiveTime taken: 0.055 seconds, Fetched: 2 row (s)

Hbase:

Hbase (main): 002purl 0 > listTABLE hbasehive

Note: hbase.table.name is defined in the table name of hbase

Column families defined by hbase.columns.mapping in hbase

two。 Use sql to import data into hivehbase, and import the following data from the testhive table into hivehbase, as follows:

Hive > select * from testhive;OK1 Berg1 Berg2 Cccc3 Xxxx4 Jjjj

Start importing data, that is, pay attention to overwrite, the same data record will overwrite points and copy.

Hive > insert overwrite table hivehbase select * from testhive

After successfully importing the data, query the data in the table, that is:

Hive > select * from hivehbase;OK1 Berg2 Cccc3 Xxxx4 JjjjTime taken: 0.458 seconds, Fetched: 4 row (s)

Then go to hbase to see if there is data in the hbasehive table in hbase:

Hbase (main): 004hbasehive'ROW COLUMN+CELL 0 > scan 'hbasehive'ROW COLUMN+CELL 1 column=columnfamily1:val, timestamp=1464514508295, value=Berg 2 column=columnfamily1:val, timestamp=1464514508295, value=Cccc 3 column=columnfamily1:val, timestamp=1464514508295 Value=Xxxx 4 column=columnfamily1:val, timestamp=1464514508295, value=Jjjj 4 row (s) in 0.0470 seconds

As you can see, the data added to hivehbase in hive already exists in the hbasehive table in hbase

In turn, can the data added to hbasehive in hbase be seen in hivehbase in hive?

Add a data record to the hbasehive in hbase as follows:

Hbase (main): 007 hbasehive', 0 > put 'hbasehive',' 5 records column family1l valium codes

Then look at the table data in hivehbase in hive, and you can see:

Hive > select * from hivehbase

OK

1 Berg

2 Cccc

3 Xxxx

4 Jjjj

5 Yyyy: it has been shown that manipulating data in hbase can also be queried in hive.

3.hive accesses an existing hbase, using CREATE EXTERNAL TABLE

Prepare a student table in hbase and insert three data records into its table, namely:

Hbase (main): 026 create 0 > student','info'

Hbase (main): 028 put 0 > student','1','info:name','Berg'

Hbase (main): 029 put 0 > student','2','info:name','Hbase'

Hbase (main): 030 put 0 > student','3','info:name','hive'

Use CREATE EXTERNAL TABLE:

CREATE EXTERNAL TABLE hbase_person (key string,value int) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'

WITH SERDEPROPERTIES ("hbase.columns.mapping" = "info:name,info:sex,info:age")

TBLPROPERTIES ("hbase.table.name" = "person")

As follows:

Hive > CREATE EXTERNAL TABLE hivehbase_student (key int, value string)

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

> WITH SERDEPROPERTIES ("hbase.columns.mapping" = "info:name")

> TBLPROPERTIES ("hbase.table.name" = "student")

OK

Check to see if the table generates:

Hive > show tables

OK

Hivehbase_student: indicates that the generation exists.

Then look at the data from this table in hive:

Hive > select * from hivehbase_student

OK

1 Berg

2 Hbase

3 hive

The name of the resulting table, which is consistent with the data in the student table in hbase.

*

After you have done this, move on to:

II: multi-column and multi-column families (Multiple Columns and Families)

1. Manipulate the database in hive as follows:

First prepare a metadata table, multiplehive, and store multiple data records in the table, as follows:

Hive > create table multiplehive > (id int, name string,sex string, age int) > ROW FORMAT DELIMITED FIELDS TERMINATED BY'\ t 'STORED AS TEXTFILE;OKTime taken: 0.179 secondshive > load data local inpath' / home/hadoop/mytestdata/multiplehive.txt' into table multiplehive;Loading data to table default.multiplehiveOKTime taken: 0.736 secondshive > select * from multiplehive;OK1 Berg f 212 BigData m 20Time taken: 0.186 seconds, Fetched: 2 row (s)

CREATE TABLE hivehbase_person (key int, name string, sex string, age int)

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

WITH SERDEPROPERTIES ("hbase.columns.mapping" = ": key,info:name,info:sex,info:age")

TBLPROPERTIES ("hbase.table.name" = "hbasehive_person")

Hive > CREATE TABLE hivehbase_person (key int, name string, sex string, age int) > STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' > WITH SERDEPROPERTIES ("hbase.columns.mapping" = ": key,info:name,info:sex,info:age") > TBLPROPERTIES ("hbase.table.name" = "hbasehive_person"); OK

After creating the associated table successfully, insert the prepared data into the table hivehbase _ person table in hive, namely:

Hive > insert overwrite table hivehbase_person select id,name,sex,age from multiplehive

Then look at the data in this table, as follows:

Hive > select * from hivehbase_person;OK1 Berg f 212 BigData m 20Time taken: 0.679 seconds, Fetched: 2 row (s)

Then go to Hbase and view the data in this table: hbase (main): 039 main 0 > scan 'hbasehive_person'

Hbase (main): 039 hbasehive_person'ROW COLUMN+CELL 0 > scan 'hbasehive_person'ROW COLUMN+CELL 1 column=info:age, timestamp=1464522188307, value=21 1 column=info:name, timestamp=1464522188307, value=Berg 1 column=info:sex Timestamp=1464522188307, value=f 2 column=info:age, timestamp=1464522188307, value=20 2 column=info:name, timestamp=1464522188307, value=BigData 2 column=info:sex, timestamp=1464522188307, value=m 2 row (s) in 8.1630 seconds, is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.