hive与hbase整合

2025-04-16 技术教程

--hbaseshellfilter--create'test1','lf','sf'--lf:columnfamilyofLONGvalues(binaryvalue)--sf:columnfamilyofSTRINGvalues--一个用户（userX），在什么时间（tsX），作为rowkey--对什么产品（value：skuXXX），做了什么操作作为列名，比如，c1:clickfromhomepage;c2:clickfromad;s1:searchfromhomepage;b1:buyput'test1','user1|ts1','sf:c1','sku1'put'test1','user1|ts2','sf:c1','sku188'put'test1','user1|ts3','sf:s1','sku123'put'test1','user2|ts4','sf:c1','sku2'put'test1','user2|ts5','sf:c2','sku288'put'test1','user2|ts6','sf:s1','sku222'scan'test1',FILTER=>"ValueFilter(=,'binary:sku188')"scan'test1',FILTER=>"ValueFilter(=,'substring:188')"scan'test1',FILTER=>"ValueFilter(=,'substring:88')"scan'test1',FILTER=>"ColumnPrefixFilter('c2')ANDValueFilter(=,'substring:88')"scan'test1',FILTER=>"ColumnPrefixFilter('s')AND(ValueFilter(=,'substring:123')ORValueFilter(=,'substring:222'))"scan'test1',FILTER=>"FirstKeyOnlyFilter()ANDValueFilter(=,'binary:sku188')ANDKeyOnlyFilter()"scan'test1',FILTER=>"PrefixFilter('user1')"scan'test1',{STARTROW=>'user1|ts2',FILTER=>"PrefixFilter('user1')"}scan'test1',{STARTROW=>'user1|ts2',STOPROW=>'user2'}importorg.apache.hadoop.hbase.filter.CompareFilterimportorg.apache.hadoop.hbase.filter.SubstringComparatorimportorg.apache.hadoop.hbase.filter.RowFilterscan'test1',{FILTER=>RowFilter.new(CompareFilter::CompareOp.valueOf('EQUAL'),SubstringComparator.new('ts3'))}importorg.apache.hadoop.hbase.filter.RegexStringComparatorput'test1','user2|err','sf:s1','sku999'scan'test1',{FILTER=>RowFilter.new(CompareFilter::CompareOp.valueOf('EQUAL'),RegexStringComparator.new('^user\d+\|ts\d+$'))}importorg.apache.hadoop.hbase.filter.CompareFilterimportorg.apache.hadoop.hbase.filter.SingleColumnValueFilterimportorg.apache.hadoop.hbase.filter.SubstringComparatorimportorg.apache.hadoop.hbase.util.Bytesscan't1',{COLUMNS=>'family:qualifier',FILTER=>SingleColumnValueFilter.new(Bytes.toBytes('family'),Bytes.toBytes('qualifier'),CompareFilter::CompareOp.valueOf('EQUAL'),SubstringComparator.new('somevalue'))}put'test1','user1|ts9','sf:b1','sku1'scan'test1',FILTER=>"ColumnPrefixFilter('b1')ANDValueFilter(=,'binary:sku1')"scan'test1',{COLUMNS=>'sf:b1',FILTER=>SingleColumnValueFilter.new(Bytes.toBytes('sf'),Bytes.toBytes('b1'),CompareFilter::CompareOp.valueOf('EQUAL'),Bytes.toBytes('sku1'))}--binaryvalue--org.apache.hadoop.hbase.util.Bytes.toString("HelloHBase".to_java_bytes)org.apache.hadoop.hbase.util.Bytes.toString("\x48\x65\x6c\x6c\x6f\x20\x48\x42\x61\x73\x65".to_java_bytes)--用户userX，作为rowkey，他的各种设备（brwoser,app,pc）作为列名，所对应的cookie_id作为value(长整型变量)put'test1','user1','lf:browser1',"\x00\x00\x00\x00\x00\x00\x00\x02"put'test1','user1','lf:app1',"\x00\x00\x00\x00\x00\x00\x00\x0F"put'test1','user1','lf:app2',"\x00\x00\x00\x00\x00\x00\x00\x10"put'test1','user2','lf:app1',"\x00\x00\x00\x00\x00\x00\x00\x11"put'test1','user2','lf:pc1',"\x00\x00\x00\x00\x00\x00\x00\x12"scan'test1',STOPROW=>'user2',FILTER=>"(ColumnPrefixFilter('app')ANDValueFilter(>,'binary:\x00\x00\x00\x00\x00\x00\x00\x0F'))"scan'test1',LIMIT=>10,FILTER=>"(ColumnPrefixFilter('app')ANDValueFilter(>,'binary:\x00\x00\x00\x00\x00\x00\x00\x0F'))"alter'test1',NAME=>'cf',METHOD=>'delete'alter'test1','delete'=>'cf'alter'test1',NAME=>'cf'--用户userX，作为rowkey，在什么时间（timestamp）作为列名，访问了什么页面的id作为value：page_id(整型变量)put'test1','user1','cf:1399999999',"\x00\x00\x00\x09"put'test1','user1','cf:1400000000',"\x00\x00\x00\x08"put'test1','user1','cf:1400000001',"\x00\x00\x00\x07"put'test1','user1','cf:1400000002',"\x00\x00\x20\xFB"put'test1','user2','cf:1500000000',"\x00\x00\x00\x11"put'test1','user2','cf:1500000001',"\x00\x00\x20\xFC"--hivehbasemapping--CREATEEXTERNALTABLEuser_app_cookie_list(usernameSTRING,app1_cookie_idBIGINT,app2_cookie_idBIGINT)STOREDBY'org.apache.hadoop.hive.hbase.HBaseStorageHandler'WITHSERDEPROPERTIES("hbase.columns.mapping"=":key,lf:app1#b,lf:app2#b")TBLPROPERTIES("hbase.table.name"="test1");select*fromuser_app_cookie_list;--hivehbasemappingcfwithbinary--http://www.abcn.net/2013/11/hive-hbase-mapping-column-family-with-binary-value.htmlCREATEEXTERNALTABLEts_string(usernameSTRING,visitsmap<string,int>)STOREDBY'org.apache.hadoop.hive.hbase.HBaseStorageHandler'WITHSERDEPROPERTIES("hbase.columns.mapping"=":key,cf:#s:b")TBLPROPERTIES("hbase.table.name"="test1");CREATEEXTERNALTABLEts_int(usernameSTRING,visitsmap<int,int>)STOREDBY'org.apache.hadoop.hive.hbase.HBaseStorageHandler'WITHSERDEPROPERTIES("hbase.columns.mapping"=":key,cf:#s:b")TBLPROPERTIES("hbase.table.name"="test1");CREATEEXTERNALTABLEts_int_long(usernameSTRING,visitsmap<int,bigint>)STOREDBY'org.apache.hadoop.hive.hbase.HBaseStorageHandler'WITHSERDEPROPERTIES("hbase.columns.mapping"=":key,cf:#s:b")TBLPROPERTIES("hbase.table.name"="test1");select*fromts_intlateralviewexplode(visits)tasts,page;selectusername,ts,page_idfromts_intlateralviewexplode(visits)tasts,page_id;selectusername,pos,ts,page_idfromts_intlateralviewposexplode(visits)taspos,ts,page_id;usernamepostspage_iduser1113999999999user1214000000008user1314000000017user1414000000028443user21150000000017user2215000000018444selectusername,from_unixtime(ts),page_idfromts_intlateralviewexplode(visits)tasts,page_id;