PageRenderTime 8ms CodeModel.GetById 5ms app.highlight 1ms RepoModel.GetById 1ms app.codeStats 0ms

/tags/release-0.0.0-rc0/hive/external/hbase-handler/src/test/queries/hbase_queries.q

#
text | 160 lines | 131 code | 29 blank | 0 comment | 0 complexity | 58fe203981c55fe75b81f07d166de483 MD5 | raw file
  1DROP TABLE hbase_table_1;
  2CREATE TABLE hbase_table_1(key int, value string) 
  3STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
  4WITH SERDEPROPERTIES ("hbase.columns.mapping" = "cf:string")
  5TBLPROPERTIES ("hbase.table.name" = "hbase_table_0");
  6
  7DESCRIBE EXTENDED hbase_table_1;
  8
  9select * from hbase_table_1;
 10
 11EXPLAIN FROM src INSERT OVERWRITE TABLE hbase_table_1 SELECT * WHERE (key%2)=0;
 12FROM src INSERT OVERWRITE TABLE hbase_table_1 SELECT * WHERE (key%2)=0;
 13
 14DROP TABLE hbase_table_2;
 15CREATE EXTERNAL TABLE hbase_table_2(key int, value string) 
 16STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
 17WITH SERDEPROPERTIES ("hbase.columns.mapping" = "cf:string")
 18TBLPROPERTIES ("hbase.table.name" = "hbase_table_0");
 19
 20EXPLAIN 
 21SELECT Y.* 
 22FROM 
 23(SELECT hbase_table_1.* FROM hbase_table_1) x
 24JOIN 
 25(SELECT src.* FROM src) Y
 26ON (x.key = Y.key)
 27ORDER BY key, value LIMIT 20;
 28
 29SELECT Y.* 
 30FROM 
 31(SELECT hbase_table_1.* FROM hbase_table_1) x
 32JOIN 
 33(SELECT src.* FROM src) Y
 34ON (x.key = Y.key)
 35ORDER BY key, value LIMIT 20;
 36
 37EXPLAIN 
 38SELECT Y.*
 39FROM 
 40(SELECT hbase_table_1.* FROM hbase_table_1 WHERE hbase_table_1.key > 100) x
 41JOIN 
 42(SELECT hbase_table_2.* FROM hbase_table_2 WHERE hbase_table_2.key < 120) Y
 43ON (x.key = Y.key)
 44ORDER BY key, value;
 45
 46SELECT Y.*
 47FROM 
 48(SELECT hbase_table_1.* FROM hbase_table_1 WHERE hbase_table_1.key > 100) x
 49JOIN 
 50(SELECT hbase_table_2.* FROM hbase_table_2 WHERE hbase_table_2.key < 120) Y
 51ON (x.key = Y.key)
 52ORDER BY key,value;
 53
 54DROP TABLE empty_hbase_table;
 55CREATE TABLE empty_hbase_table(key int, value string) 
 56STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
 57WITH SERDEPROPERTIES ("hbase.columns.mapping" = "cf:string");
 58
 59DROP TABLE empty_normal_table;
 60CREATE TABLE empty_normal_table(key int, value string);
 61
 62select * from (select count(1) as c from empty_normal_table union all select count(1) as c from empty_hbase_table) x order by c;
 63select * from (select count(1) c from empty_normal_table union all select count(1) as c from hbase_table_1) x order by c;
 64select * from (select count(1) c from src union all select count(1) as c from empty_hbase_table) x order by c;
 65select * from (select count(1) c from src union all select count(1) as c from hbase_table_1) x order by c;
 66
 67CREATE TABLE hbase_table_3(key int, value string, count int) 
 68STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
 69WITH SERDEPROPERTIES (
 70"hbase.columns.mapping" = "cf:val,cf2:count"
 71);
 72
 73EXPLAIN 
 74INSERT OVERWRITE TABLE hbase_table_3
 75SELECT x.key, x.value, Y.count 
 76FROM 
 77(SELECT hbase_table_1.* FROM hbase_table_1) x
 78JOIN 
 79(SELECT src.key, count(src.key) as count FROM src GROUP BY src.key) Y
 80ON (x.key = Y.key);
 81
 82INSERT OVERWRITE TABLE hbase_table_3
 83SELECT x.key, x.value, Y.count 
 84FROM 
 85(SELECT hbase_table_1.* FROM hbase_table_1) x
 86JOIN 
 87(SELECT src.key, count(src.key) as count FROM src GROUP BY src.key) Y
 88ON (x.key = Y.key);
 89
 90select count(1) from hbase_table_3;
 91select * from hbase_table_3 order by key, value limit 5;
 92select key, count from hbase_table_3 order by key, count desc limit 5;
 93
 94DROP TABLE hbase_table_4;
 95CREATE TABLE hbase_table_4(key int, value1 string, value2 int, value3 int) 
 96STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
 97WITH SERDEPROPERTIES (
 98"hbase.columns.mapping" = "a:b,a:c,d:e"
 99);
100
101INSERT OVERWRITE TABLE hbase_table_4 SELECT key, value, key+1, key+2 
102FROM src WHERE key=98 OR key=100;
103
104SELECT * FROM hbase_table_4 ORDER BY key;
105
106DROP TABLE hbase_table_5;
107CREATE EXTERNAL TABLE hbase_table_5(key int, value map<string,string>) 
108STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
109WITH SERDEPROPERTIES ("hbase.columns.mapping" = "a:")
110TBLPROPERTIES ("hbase.table.name" = "hbase_table_4");
111
112SELECT * FROM hbase_table_5 ORDER BY key;
113
114DROP TABLE hbase_table_6;
115CREATE TABLE hbase_table_6(key int, value map<string,string>) 
116STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
117WITH SERDEPROPERTIES (
118"hbase.columns.mapping" = ":key,cf:"
119);
120INSERT OVERWRITE TABLE hbase_table_6 SELECT key, map(value, key) FROM src
121WHERE key=98 OR key=100;
122
123SELECT * FROM hbase_table_6 ORDER BY key;
124
125DROP TABLE hbase_table_7;
126CREATE TABLE hbase_table_7(value map<string,string>, key int) 
127STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
128WITH SERDEPROPERTIES (
129"hbase.columns.mapping" = "cf:,:key"
130);
131INSERT OVERWRITE TABLE hbase_table_7 
132SELECT map(value, key, upper(value), key+1), key FROM src
133WHERE key=98 OR key=100;
134
135SELECT * FROM hbase_table_7 ORDER BY key;
136
137set hive.hbase.wal.enabled=false;
138
139DROP TABLE hbase_table_8;
140CREATE TABLE hbase_table_8(key int, value1 string, value2 int, value3 int) 
141STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
142WITH SERDEPROPERTIES (
143"hbase.columns.mapping" = "a:b,a:c,d:e"
144);
145
146INSERT OVERWRITE TABLE hbase_table_8 SELECT key, value, key+1, key+2 
147FROM src WHERE key=98 OR key=100;
148
149SELECT * FROM hbase_table_8 ORDER BY key;
150
151DROP TABLE hbase_table_1;
152DROP TABLE hbase_table_2;
153DROP TABLE hbase_table_3;
154DROP TABLE hbase_table_4;
155DROP TABLE hbase_table_5;
156DROP TABLE hbase_table_6;
157DROP TABLE hbase_table_7;
158DROP TABLE hbase_table_8;
159DROP TABLE empty_hbase_table;
160DROP TABLE empty_normal_table;