智警杯&大数据技能竞赛sql分析脚本汇总

####特殊：
#设置hive本地模式：
set hive.exec.mode.local.auto=true;

#加速hive
set hive.execution.engine=tez;

#扩大内存(最好不用)
set mapreduce.map.memory.mb=10150; 
set mapreduce.map.java.opts=-Xmx6144m;
set mapreduce.reduce.memory.mb=10150; 
set mapreduce.reduce.java.opts=-Xmx8120m;

#查询hadoop文件
hadoop fs -ls '/college2020/'
hadoop fs -cat '/college2020/'

####student

#hadoop fs -mkdir '/college2020/'

#cmd
cd /root
vi student.csv

#复制进student.csv
巴拉巴拉。。。

#hadoop
hadoop fs -put '/root/student.csv' '/college2020/'

#hive
create database student;
use student;
create table student(num int,name string) row format delimited fields terminated by ',';
load data local inpath '/root/student.csv' overwrite into table student;

#select * from student;

####爬取弹幕

#把comment.csv拖进/root/

#hadoop
hadoop fs -put '/root/comment.csv' '/college2020/'

#hive
create database qingjiao;
use qingjiao;
create table comment(text string,color string,size string,position string,time string) row format delimited fields terminated by ',';
load data local inpath '/root/comment.csv' overwrite into table comment;

#51 查询弹幕数量
insert overwrite directory "/college2020/51/" row format delimited fields terminated by 't' select count(*) from comment;

#52 查询弹幕对应颜色
insert overwrite directory "/college2020/52" row format delimited fields terminated by '\t ' select color from comment where text='haoran';

#53 查询弹幕颜色top6
#最大
insert overwrite directory "/college2020/53" row format delimited fields terminated by '\t'