第6章音乐魔盒案例 Ver1.6-20221128

采集到mysql

采集到Mongodb

转移到hive

转换到hbase

生成hive结果

生成hbase结果

转移到mysql

转移到mysql

CREATE TABLE `music_data` (
`mid` int(11) NOT NULL AUTO_INCREMENT,
`collector` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`coll_time` datetime DEFAULT NULL,
`song_url` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`song_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`artist_url` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`artist_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`album_url` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`album_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`mid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `test`.`sum_artist_name` (
`mid` int(11) NOT NULL AUTO_INCREMENT,
`collector` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`coll_time` datetime DEFAULT NULL,
`artist_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`acount` int(11) DEFAULT 0,
PRIMARY KEY (`mid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `test`.`wc_song_name` (
`mid` int(11) NOT NULL AUTO_INCREMENT,
`collector` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`coll_time` datetime DEFAULT NULL,
`word` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`acount` int(11) DEFAULT 0,
PRIMARY KEY (`mid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

安装Pycharm

chrome&chromdriver安装

pip安装依赖库

备注:以下使用gitee.com,需要先注册并登陆

下载源码目录

进入pycharm后将该目录打开，将music_scrapy下的musics设置其为源码目录

修改采集者：music_scarpy->musics->musics-pipelines.py

运行脚本：music_scrapy->musics->musics->_init_.py

执行脚本:music_scrapy->muiscs->musics->checkdata.py

以下命令中'张三'需更换为本人的中文姓名，需与采集数据时使用的名字一致;myname需换成本人姓名全拼或简拼

sqoop import --connect "jdbc:mysql://home.hddly.cn:53306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8" --username test --password test --table music_data --where "collector='张三'" --delete-target-dir --target-dir /user/myname/sqoop_hive_music --hive-database myname --hive-import --hive-overwrite --hive-table music_data --hive-drop-import-delims --m 1

通过hive查验

在hive中执行下载脚本

use myname;
drop table wc_song_name;
create table wc_song_name as (select 0 as mid,'张三' as collecter,current_timestamp() as coll_time,word, count(1) as acount from (select explode(split(song_name, ' ')) as word from music_data) w group by word order by word);
drop table sum_artist_name;
create table sum_artist_name as (select 0 as mid,'张三' as collecter,current_timestamp() as coll_time,artist_name,count(1) as acount from music_data group by artist_name order by count(1) desc);

通过hive执行下面脚本核验

use myname;
show tables;
select count(1) from wc_song_name;
select count(1) from sum_artist_name;

如果两个count的结果>0说明成功统计

sqoop export --connect "jdbc:mysql://home.hddly.cn:53306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8" --username test --password test --table sum_artist_name --fields-terminated-by '\001' --lines-terminated-by '\n' --export-dir /user/hive/warehouse/myname.db/sum_artist_name;
sqoop export --connect "jdbc:mysql://home.hddly.cn:53306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8" --username test --password test --table wc_song_name --fields-terminated-by '\001' --lines-terminated-by '\n' --export-dir /user/hive/warehouse/myname.db/wc_song_name;

执行脚本:music_scrapy->muiscs->musics->checkdata.py

打开jupyter

导入bigdata库

创建脚本

编写脚本

查看该目录下是否含有本人学号的图片文件

连接配置如图:
connectname: remotetest
hostname: home.hddly.cn
port: 53306
username: test
点:连接测试(test connection )
输入密码: test

新建查询：