HBase + Hive 연동 (table, insert)

* 참고문서

http://itnp.kr/roller/home/entry/hive_hbase_integration

http://stackoverflow.com/questions/17425492/hive-insert-query-like-sql

 

1. HBase 데이터 생성

hbase(main):003:0> create 'short_urls', {NAME=>'u'},{NAME=>'s'}
0 row(s) in 2.1480 seconds
=> Hbase::Table - short_urls

hbase(main):004:0> put 'short_urls', 'bit.ly/aaaa', 's:hits', '100'
0 row(s) in 0.0870 seconds

hbase(main):005:0> put 'short_urls', 'bit.ly/aaaa', 'u:url', 'hbase.apache.org'
0 row(s) in 0.0150 seconds

hbase(main):006:0> put 'short_urls', 'bit.ly/abcd', 's:hits', '123'
0 row(s) in 0.0050 seconds

hbase(main):007:0> put 'short_urls', 'bit.ly/abcd', 'u:url', 'example.com/foo'
0 row(s) in 0.0060 seconds

hbase(main):008:0> scan 'short_urls'
ROW COLUMN+CELL 
 bit.ly/aaaa column=s:hits, timestamp=1425560516063, value=100 
 bit.ly/aaaa column=u:url, timestamp=1425560527857, value=hbase.apache.org 
 bit.ly/abcd column=s:hits, timestamp=1425560536934, value=123 
 bit.ly/abcd column=u:url, timestamp=1425560543867, value=example.com/foo 
2 row(s) in 0.0280 seconds

2. Hive 질의

hive> CREATE EXTERNAL TABLE short_urls(short_url string, url string, hit_count int) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES("hbase.columns.mapping" = ":key,u:url,s:hits") TBLPROPERTIES("hbase.table.name"="short_urls");
OK
Time taken: 1.177 seconds

hive> select * from short_urls;
OK
bit.ly/aaaa hbase.apache.org 100
bit.ly/abcd example.com/foo 123
Time taken: 0.765 seconds, Fetched: 2 row(s)

hive> desc short_urls;
OK
short_url string from deserializer 
url string from deserializer 
hit_count int from deserializer 
Time taken: 0.116 seconds, Fetched: 3 row(s)

3. Hive에서 insert into values 사용하기 위한 dummy 테이블 생성 후 데이터 추가

hive> create table one as select 1 as one from short_urls limit 1;
OK
Time taken: 50.066 seconds

hive> insert into table short_urls select 'bit.ly/bbbb', 'daum.net', 50 from one limit 1;
OK
Time taken: 19.255 seconds

hive> select * from short_urls;
OK
bit.ly/aaaa hbase.apache.org 100
bit.ly/abcd example.com/foo 123
bit.ly/bbbb daum.net 50
Time taken: 0.087 seconds, Fetched: 3 row(s)

4. Hive를 이용해 HBase 테이블에 데이터 입력 확인

hbase(main):009:0> scan 'short_urls'
ROW COLUMN+CELL 
 bit.ly/aaaa column=s:hits, timestamp=1425560516063, value=100 
 bit.ly/aaaa column=u:url, timestamp=1425560527857, value=hbase.apache.org 
 bit.ly/abcd column=s:hits, timestamp=1425560536934, value=123 
 bit.ly/abcd column=u:url, timestamp=1425560543867, value=example.com/foo 
 bit.ly/bbbb column=s:hits, timestamp=1425562060992, value=50 
 bit.ly/bbbb column=u:url, timestamp=1425562060992, value=daum.net 
3 row(s) in 0.0140 seconds

 

답글 남기기

아래 항목을 채우거나 오른쪽 아이콘 중 하나를 클릭하여 로그 인 하세요:

WordPress.com 로고

WordPress.com의 계정을 사용하여 댓글을 남깁니다. 로그아웃 / 변경 )

Twitter 사진

Twitter의 계정을 사용하여 댓글을 남깁니다. 로그아웃 / 변경 )

Facebook 사진

Facebook의 계정을 사용하여 댓글을 남깁니다. 로그아웃 / 변경 )

Google+ photo

Google+의 계정을 사용하여 댓글을 남깁니다. 로그아웃 / 변경 )

%s에 연결하는 중

%d 블로거가 이것을 좋아합니다: