1. 1. 1. Hbase数据导出实例

需求：
根据时间范围、区域等条件查询，将hbase中终端采集数据最大时间、最小时间的日志数据导出
参考文档：
http://blog.csdn.net/qq_27078095/article/details/56482010
https://www.cnblogs.com/szw-blog/p/5512349.html
http://blog.csdn.net/javajxz008/article/details/61173213
http://www.cnblogs.com/husky/p/6422001.html
https://my.oschina.net/wangjiankui/blog/497658
https://www.2cto.com/net/201708/673854.html
http://www.cnblogs.com/husky/p/6764317.html

解决办法：

1. 通过hbase自带导入导出将查询到的终端mac数据导出到指定目录

hbase org.apache.hadoop.hbase.mapreduce.Driver export 表名称目录
这样导出是整个表数据，没法过滤，姑且暂时不能使用。

1. 通过查询条件过滤导出

scan 'LOG20180108',{COLUMNS => 'INFO',LIMIT=>1,FILTER=>"(PrefixFilter('T')) AND (SingleColumnValueFilter('INFO','AreaCode',=,'binary:610103'))"}
导出到文件：
echo "scan 'LOG20180108',{COLUMNS => ['INFO'],LIMIT=>1,FILTER=>\"(PrefixFilter('T')) AND (SingleColumnValueFilter('INFO','AreaCode',=,'binary:610103'))\"}" | hbase shell > myText.csv

1. 通过hive导出

1. 将hbase表与hive临时表同步。
2. 将hive临时表数据导入到真实表
3. 将真实表数据导入数据库

脚本如下：
#!/bin/bash

#获取输入时间，默认为当前时间
get_time(){
if [ 3 -eq $# ]
then
date=`date -d "+0 day $1" +%Y%m%d`
enddate=`date -d "+1 day $2" +%Y%m%d`
elif [ 0 -eq $# ]
then
echo "无输入参数，则默认构建昨天."
echo "若是批量构建，请输入时间段，格式为【$0 yyyy-mm-dd yyyy-mm-dd】."
#read -p "若不输入参数则默认构建昨天数据，输入【y】继续构建昨日数据，输入【n】退出：" isBuild
#case $isBuild in
#y | Y)
date=`date -d "+0 day yesterday" +%Y%m%d`
enddate=`date +%Y%m%d`
#; ;
#n | N)
#exit
#; ;
#*)
#echo "输入错误，退出"
#exit
#; ;
#esac
else
【hbase|Hbase数据导出实例】echo "输入有误."
echo "若是批量构建，请输入时间段，格式为【$0 yyyy-mm-dd yyyy-mm-dd】."
echo "若默认构建昨天数据，则不需要输入参数，直接执行【$0】."
fi
}



#创建存储数据表结构
hive_table(){
echo "create hive table start......."
hive -S -e "DROP TABLE IF EXISTS LogTerminal;
CREATE TABLE LogTerminal(rowkey string,TerminalID string,TerminalMac string,DeviceType string, Power string,Channel string,MaxPower string,TimeNear string,LonNear string,LatNear string,RouteMac string,SSID string, SSIDs string,SecurityType string,RealType string, RealCode string,TerminalType int,PcBrand string, Phone string,IMSI string,IMEI string,OS string,CustomerStartTime string,OffLineTime string, Model string,CoordinateX string,CoordinateY string, OffLineLon string,OffLineLat string,PcIP string, RouteType string,SessionID string,ProtoType string,CyberCode string,IsMove string,IsElectric string, SafeState string,GPIOState string,Serial string, GuildID string,Time string,ManufacturerCode string,AreaCode string,UnitCode string,MachineCode string, SystemType string,DATASOURCEID string,Lon string, Lat string,LatLon string,RESOURCETYPE string, AccessSystemID string,InterfaceID string,InterfaceGroupID string,WriterTime string )COMMENT 'LogTerminal Table' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS PARQUET; "
hive -S -e "DROP TABLE IF EXISTS LogTerminal_min;
CREATE TABLE LogTerminal_min(rowkey string,TerminalID string,TerminalMac string,DeviceType string, Power string,Channel string,MaxPower string,TimeNear string,LonNear string,LatNear string,RouteMac string,SSID string, SSIDs string,SecurityType string,RealType string, RealCode string,TerminalType int,PcBrand string, Phone string,IMSI string,IMEI string,OS string,CustomerStartTime string,OffLineTime string, Model string,CoordinateX string,CoordinateY string, OffLineLon string,OffLineLat string,PcIP string, RouteType string,SessionID string,ProtoType string,CyberCode string,IsMove string,IsElectric string, SafeState string,GPIOState string,Serial string, GuildID string,Time string,ManufacturerCode string,AreaCode string,UnitCode string,MachineCode string, SystemType string,DATASOURCEID string,Lon string, Lat string,LatLon string,RESOURCETYPE string, AccessSystemID string,InterfaceID string,InterfaceGroupID string,WriterTime string )COMMENT 'LogTerminal Table' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS PARQUET; "
hive -S -e "DROP TABLE IF EXISTS LogTerminal_max;
CREATE TABLE LogTerminal_max(rowkey string,TerminalID string,TerminalMac string,DeviceType string, Power string,Channel string,MaxPower string,TimeNear string,LonNear string,LatNear string,RouteMac string,SSID string, SSIDs string,SecurityType string,RealType string, RealCode string,TerminalType int,PcBrand string, Phone string,IMSI string,IMEI string,OS string,CustomerStartTime string,OffLineTime string, Model string,CoordinateX string,CoordinateY string, OffLineLon string,OffLineLat string,PcIP string, RouteType string,SessionID string,ProtoType string,CyberCode string,IsMove string,IsElectric string, SafeState string,GPIOState string,Serial string, GuildID string,Time string,ManufacturerCode string,AreaCode string,UnitCode string,MachineCode string, SystemType string,DATASOURCEID string,Lon string, Lat string,LatLon string,RESOURCETYPE string, AccessSystemID string,InterfaceID string,InterfaceGroupID string,WriterTime string )COMMENT 'LogTerminal Table' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS PARQUET; "
echo "create hive table end......."
}

#创建临时终端日志表结构，并将hbase表和hive临时表关联，然后根据条件查询临时表数据插入到真实表中
hive_task(){
echo "hive task $1 $2 $3..."
DATA_FORMAT=`date -d "$1" +%Y-%m-%d`
TABLE_NAME=LOG$1
AREA_CODE=$3

echo $DATA_FORMAT
echo $TABLE_NAME
echo $AREA_CODE

hive -S -e "DROP TABLEIF EXISTS TempLogTerminal;
CREATE EXTERNAL TABLE TempLogTerminal(key string,TerminalID string,TerminalMac string,DeviceType string, Power string,Channel string,MaxPower string,TimeNear string,LonNear string,LatNear string,RouteMac string,SSID string, SSIDs string,SecurityType string,RealType string, RealCode string,TerminalType int,PcBrand string, Phone string,IMSI string,IMEI string,OS string,CustomerStartTime string,OffLineTime string, Model string,CoordinateX string,CoordinateY string, OffLineLon string,OffLineLat string,PcIP string, RouteType string,SessionID string,ProtoType string,CyberCode string,IsMove string,IsElectric string, SafeState string,GPIOState string,Serial string, GuildID string,Time string,ManufacturerCode string,AreaCode string,UnitCode string,MachineCode string,SystemType string,DATASOURCEID string,Lon string, Lat string,LatLon string,RESOURCETYPE string, AccessSystemID string,InterfaceID string,InterfaceGroupID string,WriterTime string)ROW FORMAT SERDE 'org.apache.hadoop.hive.hbase.HBaseSerDe' STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES('hbase.columns.mapping'=':key,INFO:TerminalID,INFO:TerminalMac,INFO:DeviceType,INFO:Power,INFO:Channel,INFO:MaxPower,INFO:TimeNear,INFO:LonNear,INFO:LatNear,INFO:RouteMac,INFO:SSID,INFO:SSIDs,INFO:SecurityType,INFO:RealType,INFO:RealCode,INFO:TerminalType,INFO:PcBrand,INFO:Phone,INFO:IMSI,INFO:IMEI,INFO:OS,INFO:CustomerStartTime,INFO:OffLineTime,INFO:Model,INFO:CoordinateX,INFO:CoordinateY,INFO:OffLineLon,INFO:OffLineLat,INFO:PcIP,INFO:RouteType,INFO:SessionID,INFO:ProtoType,INFO:CyberCode,INFO:IsMove,INFO:IsElectric,INFO:SafeState,INFO:GPIOState,INFO:Serial,INFO:GuildID,INFO:Time,INFO:ManufacturerCode,INFO:AreaCode,INFO:UnitCode,INFO:MachineCode,INFO:SystemType,INFO:DATASOURCEID,INFO:Lon,INFO:Lat,INFO:LatLon,INFO:RESOURCETYPE,INFO:AccessSystemID,INFO:InterfaceID,INFO:InterfaceGroupID,INFO:WriterTime') TBLPROPERTIES('hbase.table.name'='$TABLE_NAME') ;
INSERT $2 TABLE logterminal SELECT key as rowkey,TerminalID,TerminalMac,DeviceType,Power,Channel,MaxPower,TimeNear,LonNear,LatNear,RouteMac,SSID,SSIDs,SecurityType,RealType,RealCode,TerminalType,PcBrand, Phone,IMSI,IMEI,OS,CustomerStartTime,OffLineTime,Model,CoordinateX,CoordinateY,OffLineLon,OffLineLat,PcIP,RouteType,SessionID,ProtoType,CyberCode,IsMove,IsElectric, SafeState,GPIOState,Serial,GuildID,Time,ManufacturerCode,AreaCode,UnitCode,MachineCode,SystemType,DATASOURCEID,Lon,Lat,LatLon,RESOURCETYPE,AccessSystemID,InterfaceID,InterfaceGroupID,WriterTime
FROM TempLogTerminal WHERE RESOURCETYPE=32 ANDAreaCode='$AREA_CODE'; "

echo "hive task end......."
}

#创建sql表
sqlserver_table(){
"
if exists (select * from sysobjects where name='LogTerminal_min')
drop table LogTerminal_min

CREATETABLE LogTerminal_min(
rowkey nvarchar(200),TerminalID bigint,TerminalMac nvarchar(200),DeviceType bigint,
Power bigint,Channel bigint,MaxPower bigint,
TimeNear nvarchar(200),LonNear float,LatNear float,
RouteMac nvarchar(200),SSID nvarchar(200),
SSIDs nvarchar(200),SecurityType nvarchar(200),RealType bigint,
RealCode nvarchar(200),TerminalType bigint,PcBrand nvarchar(200),
Phone nvarchar(200),IMSI nvarchar(200),IMEI nvarchar(200),
OS nvarchar(200),CustomerStartTime nvarchar(200),OffLineTime nvarchar(200),
Model nvarchar(200),CoordinateX nvarchar(200),CoordinateY nvarchar(200),
OffLineLon float,OffLineLat float,PcIP nvarchar(200),
RouteType bigint,SessionID nvarchar(200),ProtoType bigint,
CyberCode nvarchar(200),IsMove bigint,IsElectric bigint,
SafeState bigint,GPIOState bigint,Serial nvarchar(200),
GuildID nvarchar(200),Time nvarchar(200),ManufacturerCode nvarchar(200),
AreaCode nvarchar(200),UnitCode nvarchar(200),MachineCode nvarchar(200),
SystemType nvarchar(200),DATASOURCEID bigint,Lon float,
Lat float,LatLon nvarchar(200),RESOURCETYPE bigint,
AccessSystemID bigint,InterfaceID bigint,InterfaceGroupID bigint,
WriterTime nvarchar(200)
);

if exists (select * from sysobjects where name='LogTerminal_max')
drop table LogTerminal_max

CREATETABLE LogTerminal_max(
rowkey nvarchar(200),TerminalID bigint,TerminalMac nvarchar(200),DeviceType bigint,
Power bigint,Channel bigint,MaxPower bigint,
TimeNear nvarchar(200),LonNear float,LatNear float,
RouteMac nvarchar(200),SSID nvarchar(200),
SSIDs nvarchar(200),SecurityType nvarchar(200),RealType bigint,
RealCode nvarchar(200),TerminalType bigint,PcBrand nvarchar(200),
Phone nvarchar(200),IMSI nvarchar(200),IMEI nvarchar(200),
OS nvarchar(200),CustomerStartTime nvarchar(200),OffLineTime nvarchar(200),
Model nvarchar(200),CoordinateX nvarchar(200),CoordinateY nvarchar(200),
OffLineLon float,OffLineLat float,PcIP nvarchar(200),
RouteType bigint,SessionID nvarchar(200),ProtoType bigint,
CyberCode nvarchar(200),IsMove bigint,IsElectric bigint,
SafeState bigint,GPIOState bigint,Serial nvarchar(200),
GuildID nvarchar(200),Time nvarchar(200),ManufacturerCode nvarchar(200),
AreaCode nvarchar(200),UnitCode nvarchar(200),MachineCode nvarchar(200),
SystemType nvarchar(200),DATASOURCEID bigint,Lon float,
Lat float,LatLon nvarchar(200),RESOURCETYPE bigint,
AccessSystemID bigint,InterfaceID bigint,InterfaceGroupID bigint,
WriterTime nvarchar(200)
);
"
}

#将数据导入到sqlserver中
import_sqlserver(){
echo " import sqlserver start......."
sqoop export -connect 'jdbc:sqlserver://192.168.2.219; username=nsmc53; password=123456; database=WFBDCMain'-table LogTerminal_min --hcatalog-database default --hcatalog-table LogTerminal_min --num-mappers 5;
sqoop export -connect 'jdbc:sqlserver://192.168.2.219; username=nsmc53; password=123456; database=WFBDCMain'-table LogTerminal_max --hcatalog-database default --hcatalog-table LogTerminal_max --num-mappers 5;

echo " import sqlserver end......."
}

#将hive数据导出到本地目录
export_hive_local(){
echo " export hive to local start......."
mkdir -p /home/hive/min
mkdir -p /home/hive/max
hive -S -e "\
insert overwrite local directory '/home/hive/min'ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' select a.* from LogTerminal a inner join(select TerminalMac,min(time) time from LogTerminal group by TerminalMac) b on a.TerminalMac = b.TerminalMac and a.time = b.time order by a.TerminalMac ; \
insert overwrite local directory '/home/hive/max'ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' select a.* from LogTerminal a inner join(select TerminalMac,max(time) time from LogTerminal group by TerminalMac) b on a.TerminalMac = b.TerminalMac and a.time = b.time order by a.TerminalMac ; "

#将hive数据导出到csv文件
hive -e " set hive.cli.print.header=true; select a.* from LogTerminal a inner join(select TerminalMac,min(time) time from LogTerminal group by TerminalMac) b on a.TerminalMac = b.TerminalMac and a.time = b.time order by a.TerminalMac ; " >> /home/hive/logterminal-min.csv

echo "export hive to local end......."
}

#将hive数据根据查询条件过滤导入到其他表
export_hive(){
echo " export hive to sqlserver start......."
#查询时间最小mac
hive -S -e "INSERT OVERWRITE TABLE LogTerminal_min select a.* from LogTerminal a inner join(select TerminalMac,min(time) time from LogTerminal group by TerminalMac) b on a.TerminalMac = b.TerminalMac and a.time = b.time order by a.TerminalMac ; ";
#查询时间最大mac
hive -S -e "INSERT OVERWRITE TABLE LogTerminal_max select a.* from LogTerminal a inner join(select TerminalMac,max(time) time from LogTerminal group by TerminalMac) b on a.TerminalMac = b.TerminalMac and a.time = b.time order by a.TerminalMac ; ";

echo "export hive to sqlserver end......."
}

main(){
#创建hive表LogTerminal
echo " create hive table LogTerminal......."
hive_table

#生成表结构时间范围
get_time $*
Style="OVERWRITE"
date1=$date
while [[ $date1 < $enddate ]]
do
echo "$date"
##创建临时终端日志表TempLogTerminal关联hbase表，重新运行时删除以前创建表结构，并将查询数据导入LogTerminal
echo " exechive_task....... $date1 $Style $3 "
hive_task $date1 $Style $3
date1=`date -d "+1 day $date1" +%Y%m%d`
Style="INTO"
done
#从hive表LogTerminal日志表导出到本地目录/home/hive
echo " export hive to sqlserver ......."
export_hive
echo " import sqlserver ......."
import_sqlserver
echo " query logterminal end......."
}

main $*

推荐阅读

• 

  凉拌鸡爪——促进骨骼预防高血压
• 

  写在静心手触上海课程前
• 

  2345智能浏览器怎么更改主页 2345智能浏览器主页如何更改
• 

  原来是这些原因 为什么有时候手机收不到验证码
• 

  吃荔枝上火吗？
• 

  西门子新款冰箱系列：科技与品质的好融合，让您的厨房更智能
• 

  再见2018，活出精彩2019
• 

  清明何止雨纷纷
• 

  JS|深拷贝浅拷贝的区别（如何实现一个深拷贝？）
• 

  excel2019怎么提供声音反馈
• 

  上夜班不可以吃芒果和火龙果 芒果不能和什么一起吃
• 

  如何设置新买的服务器的映射？ 新买的服务器怎么映射
• 

  图文 虞姬是自刎还是被人杀死的，揭秘虞姬死亡的真相
• 

  三国志战略版卧薪尝胆战法攻略 S级战法卧薪尝胆简评
• 

  机动车怎么审车 机动车怎么审
• 

  边 刑法关于放行偷越国境人员法定量刑档次是怎样
• 

  mongodb的坑 mongodb凉了吗
• 

  怎么蒸豆包蒸豆包的方法 如何蒸酥皮豆包，酥皮包子的做法窍门
• 

  2023年10月份重庆多雨吗
• 

  违章代码46131是什么意思

• HBase的 rowkey 设计原则
• HBase 数据存储结构详细解读
• hadoop|Import/Export实现hbase集群间迁移
• HBase数据Export & Import
• 实战大数据，HBase 性能调优指南
• HBase tableExists() 作用就是判断某个表是否存在
• java hbase ddl alter 添加新的列族
• 大数据|HBase导出CSV格式数据的方法
• Hbase数据的导入导出以及优化