1)mysql源个表
sale_order -> so
》desc so;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| date | datetime | YES | | NULL | |
| order_id | bigint(20) | YES | | NULL | |
| user_id | bigint(20) | YES | | NULL | |
| order_amt | double | YES | | NULL | |
| rk | varchar(100) | YES | | NULL | |
注: rk为user_id +date+orderid构成,为后面插入hbase表做准备
sale_detail -> so_detail
desc so_detail;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| date | datetime | YES | | NULL | |
| id | bigint(20) | YES | | NULL | |
| order_id | bigint(20) | YES | | NULL | |
| product_id | bigint(20) | YES | | NULL | |
| user_id | bigint(20) | YES | | NULL | |
| price | double | YES | | NULL | |
| product_num | int(11) | YES | | NULL | |
| rk | varchar(100) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
注: rk为orderid+id构成,为后面插入hbase表做准备
2)用scoop导入表数据到hbase
》 sqoop --options-file so_hbase.opt
so_hbase.opt内容如下
import
--connect
"jdbc:mysql://192.168.1.100:3306/h_order"
--username
root
--password
Nokia123
--query
"select date,order_id,user_id,order_amt,rk from so where $CONDITIONS "
-m
5
--split-by
order_id
--hbase-table
so
--hbase-row-key
rk
--column-family
cf
》 sqoop --options-file so_dtl_hbase.opt
so_dtl_hbase.opt内容如下:
import
--connect
"jdbc:mysql://192.168.1.100:3306/h_order"
--username
root
--password
Nokia123
--query
"select date,id,order_id,product_id,user_id,price,product_num,rk from so_detail where $CONDITIONS "
-m
5
--split-by
id
--hbase-table
so_detail
--hbase-row-key
rk
--column-family
cf
》 sqoop --options-file order_his_idx.opt
order_his_idx.opt内容如下:
import
--connect
"jdbc:mysql://192.168.1.100:3306/h_order"
--username
root
--password
Nokia123
--query
"select rk,order_id from so where $CONDITIONS "
-m
1
--hbase-table
order_his_idx
--hbase-row-key
rk
--column-family
cf
3)java代码通过userid找到orderid然后通过中间(索引)表order_his_idx找到oder_detail表内容:
package com.cloudy.lesson.order;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.apache.hadoop.hbase.KeyValue;
import org.apache.hadoop.hbase.client.Result;
import org.jboss.netty.util.internal.StringUtil;
import com.dao.impl.HBaseDAOImp;
public class Test {
public static void main(String[] args) {
// TODO Auto-generated method stub
HBaseDAO dao = new HBaseDAOImp();
// List<Put> list = new ArrayList<Put>();
// System.out.println(new Date());
// for (int i = 0; i < 20000; i++) {
// Put put = new Put(("r234"+i).getBytes());
// put.add("cf".getBytes(), "name".getBytes(), ("zhangsna"+i).getBytes()) ;
// put.setWriteToWAL(false);
// list.add(put) ;
dao.save(put, "test") ;
//
// }
// dao.save(list, "test");
// System.out.println(new Date());
// put.add("cf".getBytes(), "addr".getBytes(), "shanghai1".getBytes()) ;
// list.add(put) ;
// put.add("cf".getBytes(), "age".getBytes(), "30".getBytes()) ;
// list.add(put) ;
// put.add("cf".getBytes(), "tel".getBytes(), "13567882341".getBytes()) ;
// list.add(put) ;
//
// dao.save(list, "test");
// dao.save(put, "test") ;
// dao.insert("test", "testrow", "cf", "age", "35") ;
// dao.insert("test", "testrow", "cf", "cardid", "12312312335") ;
// dao.insert("test", "testrow", "cf", "tel", "13512312345") ;
// List<Result> list = dao.getRows("test", "r23419",new String[]{"name"}) ;
// List<Result> list = dao.getRows("test", "r234198","r2341996") ;
/**
* 查询1:查询个人历史订单;
*/
List<Result> list = dao.getRows("order_his_idx", "17291404");
System.out.println("list.size="+list.size());
for(Result rs : list)
{
for(KeyValue keyValue : rs.raw())
{
//17291404_2015-04-21 00:00:21_314295453511152
String rk = new String(keyValue.getRow());
String order_id = rk.split("_")[2];
List<Result> orderDtls = dao.getRows("so_detail", order_id);
for(Result rs2 : orderDtls)
{
for(KeyValue keyValue2 : rs2.raw())
{
System.out.println("Qualifier:"+ new String(keyValue2.getQualifier()));
System.out.println("Value:"+ new String(keyValue2.getValue()));
// StringUtils.reverse("");
}
}
// System.out.println("rowkey:"+ new String(keyValue.getRow()));
// System.out.println("Qualifier:"+ new String(keyValue.getQualifier()));
// System.out.println("Value:"+ new String(keyValue.getValue()));
// System.out.println("----------------");
}
}
// Result rs = dao.getOneRow("test", "testrow");
/**
* 查询2:查询某个历史订单的明细信息;
*/
List<Result> orderDtls = dao.getRows("so_detail", "314295453511152");
for(Result rs2 : orderDtls)
{
for(KeyValue keyValue2 : rs2.raw())
{
System.out.println("Qualifier:"+ new String(keyValue2.getQualifier()));
System.out.println("Value:"+ new String(keyValue2.getValue()));
// StringUtils.reverse("");
}
}
}
}