a1.sources=r1
a1.channels=c1
a1.sinks=s1

a1.sources.r1.type=spooldir
a1.sources.r1.spoolDir=/home/zebra
# 拦截器
a1.sources.r1.interceptors=i1
# 时间戳
a1.sources.r1.interceptors.i1.type=timestamp

a1.sinks.s1.type=hdfs
a1.sinks.s1.hdfs.path=hdfs://hadoop02:9000/zebra/reportTime=%Y-%m-%d
a1.sinks.s1.hdfs.fileType=DataStream
a1.sinks.s1.hdfs.rollInterval=100
a1.sinks.s1.hdfs.rollSize=0
a1.sinks.s1.hdfs.rollCount=0

a1.channels.c1.type=memory
a1.channels.c1.capacity=1000
a1.channels.c1.transactionCapacity=100

a1.sources.r1.channels=c1
a1.sinks.s1.channel=c1

../bin/flume-ng agent -n a1 -c ../conf/ -f zebra.conf -Dflume.root.logger=INFO,console

create external table zebra(a1 string,a2 string,a3 string,a4 string,a5 string,a6 string,a7 string,a8 string,a9 string,a10 string,a11 string,a12 string,a13 string,a14 string,a15 string,a16 string,a17 string,a18 string,a19 string,a20 string,a21 string,a22 string,a23 string,a24 string,a25 string,a26 string,a27 string,a28 string,a29 string,a30 string,a31 string,a32 string,a33 string,a34 string,a35 string,a36 string,a37 string,a38 string,a39 string,a40 string,a41 string,a42 string,a43 string,a44 string,a45 string,a46 string,a47 string,a48 string,a49 string,a50 string,a51 string,a52 string,a53 string,a54 string,a55 string,a56 string,a57 string,a58 string,a59 string,a60 string,a61 string,a62 string,a63 string,a64 string,a65 string,a66 string,a67 string,a68 string,a69 string,a70 string,a71 string,a72 string,a73 string,a74 string,a75 string,a76 string,a77 string) partitioned by(reportTime string) row format delimited fields terminated by '|' stored as textfile location '/zebra';

alter table zebra add partition (reportTime = '2025-03-16') location '/zebra/reportTime=2025-03-16';

select * from zebra tablesample (1 rows);

create table dataclear(reportTime string,appType bigint,appSubType bigint,userIp string,userPort bigint,appServerIp string,appServerPort bigint,host string,cellid string,appTypeCode string,interruptType string,transStatus bigint,trafficUL bigint,trafficDL bigint,retranUL bigint,retranDL bigint,procdureStartTime bigint,procdureEndTime bigint) row format delimited fields terminated by '|';

insert overwrite table dataclear select concat(reportTime,' ','00:00:00'),a23,a24,a27,a29,a31,a33,a59,a17,a19,a68,a55,a34,a35,a40,a41,a20,a21 from zebra;

create table dataproc(reportTime string,appType bigint,appSubType bigint,userIp string,userPort bigint,appServerIp string,appServerPort bigint,host string,cellid string,attempts bigint,accepts bigint,trafficUL bigint,trafficDL bigint,retranUL bigint,retranDL bigint,failCount bigint,transDelay bigint) row format delimited fields terminated by '|';

insert overwrite table dataproc select reportTime,appType,appSubType,userIp,userPort,appServerIp,appServerPort,host,if(cellid == '','00000000',cellid),if(appTypeCode == 103,1,0),if(appTypeCode == 103 and find_in_set(transStatus,"10,11,12,13,14,15,32,33,34,35,36,37,38,48,49,50,51,52,53,54,55,199,200,201,202,203,204,205,206,302,304,306") != 0 and interruptType == 0,1,0),if(appTypeCode == 103,trafficUL,0),if(appTypeCode == 103,trafficDL,0),if(appTypeCode == 103,retranUL,0),if(appTypeCode == 103,retranDL,0),if(appTypeCode == 103 and transStatus == 1 and interruptType == 0,1,0),if(appTypeCode == 103,procdureEndTime - procdureStartTime,0) from dataclear;

create table D_H_HTTP_APPTYPE(hourid string,appType int,appSubType int,attempts bigint,accepts bigint,succRatio double,trafficUL bigint,trafficDL bigint,totalTraffic bigint,retranUL bigint,retranDL bigint,retranTraffic bigint,failCount bigint,transDelay bigint) row format delimited fields terminated by '|';

insert overwrite table D_H_HTTP_APPTYPE select reportTime,appType,appSubType,sum(attempts),sum(accepts),round(sum(accepts) / sum(attempts),2),sum(trafficUL),sum(trafficDL),sum(trafficUL) + sum(trafficDL),sum(retranUL),sum(retranDL),sum(retranUL) + sum(retranDL),sum(failCount),sum(transDelay) from dataproc group by reportTime,appType,appSubType;

select hourid,appType,sum(totalTraffic) as tt from D_H_HTTP_APPTYPE group by hourid,appType sort by tt desc limit 5;

①create database zebra;
②use zebra;
③create table D_H_HTTP_APPTYEP(hourid datetime,appType int,appSubType int,attempts bigint,accepts bigint,succRatio double,traffiUL bigint,trafficDL bigint,totalTraffic bigint,retranUL bigint,retranDL bigint,retranTraffic bigint,failCount bigint,transDelay bigint);

sh sqoop export --connect jdbc:mysql://hadoop01:3306/zebra?useSSL=false --username root --password Root123456~! --export-dir '/home/software/hive-2.3.7/warehouse/zebra.db/d_h_http_apptype/000000_0' --table D_H_HTTP_APPTYEP -m 1 --fields-terminated-by '|';