fork download
copy 
  1. //** HBase Hive integration using External table simple example ***/
  2.  
  3. 1. Create the underlying Hbase table using below commands
  4.         create 'HBASE_FCM:emp_table','id','name','salary'	
  5.         describe 'HBASE_FCM:emp_table'
  6.  
  7. 2. Create temporary table to load data from a text file as we cannot load data directly in hive –hbase 
  8.     integration table.
  9.         create table temp_load_emp ( id int, value1 string,value2 int, value3 int)
  10. row format delimited fields terminated by '|' stored as textfile;
  11.  
  12. 3. Load data in temporary table
  13.      load data local inpath '/development/Projects/fcm/data/srcfiles/ted04/retail/raw/emp.txt' into table temp_emp;
  14.  
  15. 4. Create Hive table with storage as HBase to perform the HIVE HBase integration
  16. CREATE EXTERNAL TABLE hbase_table_1(id int, name string, salary int) 
  17.  STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
  18.  WITH SERDEPROPERTIES ( "hbase.columns.mapping" = ":key, name :c, salary:e")
  19.  TBLProperties("hbase.table.name" ="emp_table");
  20.  
  21. serdeproperties: define the schema definition where we define the mapping between hive and hbase column.
  22. tblproperties : define the hbase table corresponding the hive table,
  23. STORED BY is HBase so we will have the table directory in warehouse but there will be no text file.
  24. To view hive warehouse location trigger below command
  25.        desc formatted table_name;
  26.  
  27. 5. Insert data from temp table to hive hbase integration table
  28.   insert overwrite table hbase_table_2  select * from temp_emp;
  29.  
  30.  
  31.  
  32.  
  33.  
  34.  
  35.  
  36.  
  37.  
  38.  
  39.  
  40.  
  41.  
  42.  
  43.  
  44. //** HBase Hive integration using External table with composite key ***/
  45.  
  46. Same as above steps will be followed. Only changes will be there in step 4 and 5
  47.  
  48. 4 Create external table with as composite key. 
  49. CREATE EXTERNAL TABLE hbase_emp(key struct<id:int,name:string>,
  50. salary int)
  51. ROW FORMAT DELIMITED
  52. COLLECTION ITEMS TERMINATED BY '_'
  53. stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
  54. WITH SERDEPROPERTIES ("hbase.columns.mapping"=":key,salary:salary")
  55. TBLPROPERTIES("hbase.table.name"="HBASE_FCM:emp_table");
  56.  
  57. 5. Insert data into hive hbase table 
  58.  insert overwrite table hbase_emp select named_struct('id',id,'name',name),salary from temp_emp;
  59.  
  60. Note:
  61. Basically what you are doing here is that you are visualizing the composite
  62. key as a struct and specifying that your keys in the composite key are
  63. separated by a "_".  When you view hbase table key column value will be like id_name
  64. If we have to perform GROUP BY on any one part of key in your composite
  65. key, you simply run a query like:
  66. select * from hbase_table_2 GROUP BY key.id;
  67.  
  68.  
  69.  
  70.  
  71.  
  72.  
  73.  
  74.  
  75.  
  76.  
  77.  
  78.  
  79.  
  80.  
  81.  
  82.  
  83.  
  84.  
  85.  
  86.  
  87. //*******    HBASE HIVE integration with internal tables    ******************//
  88.  
  89. CREATE TABLE hbase_table_2(id int, name string, salary int) 
  90.  STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
  91.  WITH SERDEPROPERTIES ( "hbase.columns.mapping" = ":key, name :c, salary:e")
  92.  TBLProperties("hbase.table.name" ="HBASE_FCM:emp_table2");
  93.  
  94.  
  95. /**** HIVE HBase integration table with composite key **************/
  96.  
  97. CREATE TABLE hbase_emp2(key struct<id:int,name:string>,salary int)
  98. ROW FORMAT DELIMITED
  99. COLLECTION ITEMS TERMINATED BY '_'
  100. STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
  101. WITH SERDEPROPERTIES ("hbase.columns.mapping"=":key,salary:salary")
  102. TBLPROPERTIES("hbase.table.name"="HBASE_FCM:emp_table3");
  103.  
  104.  
  105.  
  106.  
Success #stdin #stdout #stderr 0.3s 40428KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
Standard output is empty
stderr
copy 
Error: unexpected '/' in "/"
Execution halted
https://ideone.com/1fX82d
language:
R (R 3.5.2)
created:
10 days ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!