2020红亚杯：商品数据分析

概述

爬取商城数据，并进行分析

具体步骤如下

爬取数据示例

1,MIUI/小米 小米手机4 小米4代 MI4智能4G手机包邮 黑色 D-LTE（4G）/TD-SCD,2100,684,0,125

2,苹果（Apple）iPhone 6 Plus (A1524)移动联通电信4G手机 金色 16G,4500.00-6800.00,714,0,1701

3,Samsung/三星 SM-G8508S GALAXY Alpha四核智能手机 新品 闪耀白,3888,546,0,235

4,Huawei/华为 H60-L01 荣耀6 移动4G版智能手机 安卓,1999,630,0,537

5,Meizu/魅族 MX4 Pro移动版 八核大屏智能手机 黑色 16G,2499,1037,,434

6,vivo X5MAX L 移动4G 八核超薄大屏5.5吋双卡手机vivoX5max,2998.9,608,0,319

7,纽芝兰包包女士2018新款潮百搭韩版时尚单肩斜挎包少女小挎包链条,168,482,0,320

8,MARNI Trunk 女士 中号拼色十字纹小牛皮 斜挎风琴包,356,473,0,35

数据导入

上传数据

hadoop fs -mkdir -p /college

hadoop fs -put /root/college/loan.csv /college

创建数据库

create database 库名;

create table 表名1(
id int,
name string,
price int,
views int,
sales int,
stock int
)row format delimited
fields terminated by ',';

导入数据-》hive

load data inpath '/college/loan.csv' into table 表名;

load data [local] inpath ‘/root/data’ into table psn; 表示本地而不是Hadoop

验证数据

统计验证

数据验证统计爬取的数量，写入到/root/college021/

INSERT OVERWRITE LOCAL DIRECTORY '/root/college021/'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

select count(*) from goods;

查询验证

数据验证查看id前三名的内容写入到/root/college022/

INSERT OVERWRITE LOCAL DIRECTORY '/root/college022/'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

select * from goods order by id limit 3 ;

以name列中第一个数据块为商品名字，统计各个品牌的数量，把数据写入root/college/01

INSERT OVERWRITE LOCAL DIRECTORY '/root/college/01'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

select split(name, '\ ')[0],count(*) from data

where group by split(name, '\ ')[0];

数据清洗

去空值，去掉带有女士和连衣裙的异常值，把数据写入goods1

create table goods1 as

select * from goods

where not id is null and not name is null and not price is null and not views is null and not sales is null and not stock is null

and name not like '%女士%' and name not like'%连衣裙%';