提要
近期需要分析一批用户，按注册系统的时间，以及使用系统的次数，推算出这些用户日均使用的分布情况，如日均（1-1000）（1000-10000）（10000-100000）。这些数据是主要是csv格式，由于为了解决csv格式中的数字过大自动切换成科学技术法的问题，所有数据结尾处默认都是加入\t分隔符，数据格式如下。
操作
处理前的数据【我们提供若干样本数据】
> cd /opt/document/
> vi document.csv
10623429 ,74 ,2020-09-16 04:32:30
30428389 ,1899 ,2021-01-06 14:25:13
35315614 ,15000 ,2021-01-12 18:43:54
10472929 ,1406 ,2020-09-11 04:22:31
10502044 ,13000 ,2020-09-12 02:23:18
13829381 ,504 ,2020-10-22 00:50:35
17282257 ,488000 ,2020-10-26 23:46:29
7250404 ,7633 ,2020-06-15 03:08:20
7300069 ,464 ,2020-06-16 01:55:44
8192050 ,1424 ,2020-07-01 13:27:18
26185180 ,3434 ,2020-11-14 20:15:50
18928713 ,2998 ,2020-10-29 09:07:31
19155065 ,363 ,2020-10-29 19:22:26
23245377 ,14000 ,2020-11-05 08:20:58
17773641 ,2859 ,2020-10-27 07:04:42
18580457 ,351 ,2020-10-28 19:20:31
7105481 ,11000 ,2020-06-13 06:08:29
12665058 ,1181000 ,2020-10-12 23:03:33
10908114 ,28000 ,2020-09-22 21:02:29
替换文件中的特殊字符
我们参考下面的文章，使用python脚本【为啥？主要python无需编译且拥有强大的三方库支持】将csv文件中的一些特殊字符和多余的分割去掉。 替换文件中的特殊字符。
#centos默认安装python2.7，且python简单还有强大的库的支持
> vi filter.py
import csv
import sys
with open(sys.argv[1], 'r') as srcFile, open(sys.argv[2], 'w') as dstFile:
fileReader = csv.reader(srcFile)
fileWriter = csv.writer(dstFile)
for data in list(fileReader):
for i,d in enumerate(data):
if d.find('\r\n') != -1:
d = d.replace('\r\n', ' ')
if d.find('\n') != -1:
d = d.replace('\n', ' ')
if d.find('\r') != -1:
d = d.replace('\r', ' ')
if d.find('\\') != -1:
d = d.replace('\\', '')
if d.find('\t') != -1:
d = d.replace('\t', '')
data[i] = d
fileWriter.writerow(data)
dstFile.close()
srcFile.close()
执行
#设置权限并执行
> chmod 777 filter.py
> python filter.py document.csv documents.csv
显示处理后的数据
10623429,74,2020-09-16 04:32:30
30428389,1899,2021-01-06 14:25:13
35315614,15000,2021-01-12 18:43:54
10472929,1406,2020-09-11 04:22:31
10502044,13000,2020-09-12 02:23:18
13829381,504,2020-10-22 00:50:35
17282257,488000,2020-10-26 23:46:29
7250404,7633,2020-06-15 03:08:20
7300069,464,2020-06-16 01:55:44
8192050,1424,2020-07-01 13:27:18
26185180,3434,2020-11-14 20:15:50
18928713,2998,2020-10-29 09:07:31
19155065,363,2020-10-29 19:22:26
23245377,14000,2020-11-05 08:20:58
17773641,2859,2020-10-27 07:04:42
18580457,351,2020-10-28 19:20:31
7105481,11000,2020-06-13 06:08:29
12665058,1181000,2020-10-12 23:03:33
10908114,28000,2020-09-22 21:02:29
将【简化版的数据】的csv格式数据转换成tsv文件格式，并去除表头
由于csv默认格式是使用逗号进行分割，若分割的列中含有逗号，容易出现错列情况。 我们参考下面的文章，使用python脚本进行文件的格式转换。 如何将CSV格式文件转成TSV格式文件。
# 执行脚本
> /usr/bin/python3.6 conversion.py documents.csv documentss.csv
# 查看
> less documentss.csv
30428389 1899 2021-01-06 14:25:13
35315614 15000 2021-01-12 18:43:54
10472929 1406 2020-09-11 04:22:31
10502044 13000 2020-09-12 02:23:18
13829381 504 2020-10-22 00:50:35
17282257 488000 2020-10-26 23:46:29
7250404 7633 2020-06-15 03:08:20
7300069 464 2020-06-16 01:55:44
8192050 1424 2020-07-01 13:27:18
26185180 3434 2020-11-14 20:15:50
18928713 2998 2020-10-29 09:07:31
19155065 363 2020-10-29 19:22:26
23245377 14000 2020-11-05 08:20:58
17773641 2859 2020-10-27 07:04:42
18580457 351 2020-10-28 19:20:31
7105481 11000 2020-06-13 06:08:29
12665058 1181000 2020-10-12 23:03:33
10908114 28000 2020-09-22 21:02:29
分析最终表
创建临时表
hive> CREATE TABLE Document(id String,count String,time String) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE;
hive> LOAD DATA LOCAL INPATH '/opt/document/documentss.csv' INTO TABLE document;
数据导入最终表
hive> CREATE TABLE documents(id String,count String,time String,average DOUBLE) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE;
hive> insert overwrite table documents select id,count,time,count/datediff('2021-03-23',time) from document;
分析
> hive
> SELECT CASE WHEN average < 100 THEN 'a' WHEN average >= 1000 THEN 'b' WHEN average < 2000 THEN 'c' WHEN average < 3000 THEN 'd' ELSE average END AS `types`, count(*) FROM documents GROUP BY CASE WHEN average < 100 THEN 'a' WHEN average >= 1000 THEN 'b' WHEN average < 2000 THEN 'c' WHEN average < 3000 THEN 'd' ELSE average END
......
OK
b 2
c 3
a 13
Time taken: 1.265 seconds, Fetched: 3 row(s)
