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 $*

推荐阅读

• 

  椰子油的生活小妙用 椰子油有什么用途和功效
• 

  庆东锅炉故障代码是什么如何解决
• 

  教学教务管理人员是干什么的
• 

  联想yoga2
• 

  如何选择抗体稀释液？ 抗体稀释液
• 

  下载导航地图，导航地图下载
• 

  流媒体后视镜可以当行车记录仪吗 行车记录仪有必要买流媒体吗
• 

  炒苔菜用不用焯水，苔菜用不用焯水再炒
• 

  昆明工业职业技术学院怎么样
• 

  （152）感赏女儿|（152）感赏女儿 2020-12-4
• 

  桃子核发霉外面是好的还能吃吗
• 

  离散傅立叶分析,origin怎么进行傅立叶分析
• 

  次密接触者的一般接触者需要隔离吗
• 

  海带炖土豆的做法 海带炖土豆的做法窍门
• 

  大概的近义词 大概的近义词是什么 标准答案
• 

  合金筷子是塑料还是金属 合金筷子是什么材质
• 

  真我与假我
• 

  钢材包括哪些 钢材怎么分类
• 

  heos,黑色素瘤
• 

  【家事】节如流水

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