educoder平台Hive基本查询操作二.docx

第 1 关：Hive排序 禁止修改 create database if not exists mydb; use mydb; create table if not exists total( tradedate string, tradetime string, securityid string, bidpx1 string, bidsize1 int, offerpx1 string, bidsize2 int) row format delimited fields terminated by , stored as textfile; truncate table total; load data local inpath /root/files into table total; 禁止修改 begin selectsecurityid,sum(bidsizsef1r)om totalwhere tradedateroup by securityid order by s desc limit 3; end ———————————————————————————————— 第 2 关：Hive数据类型和类型转换禁止修改 create database if not exists mydb; use mydb; create table if not exists total( tradedate string, tradetime string, securityid string, bidpx1 string, bidsize1 int, offerpx1 string, bidsize2 int) row format delimited fields terminated by , stored as textfile; truncate table total; load data local inpath /root/files into table total; 禁止修改 begin selectsecurityid,sum(bidsize1*cast(biadspfxl1oat)f)rom totalwhere tradedategroup by securityid; end —————————————————————————————— 第 3 关：Hive抽样查询 禁止修改 create database if not exists mydb; use mydb; create table if not exists total( tradedate string, tradetime string, securityid string, bidpx1 string, bidsize1 int, offerpx1 string, bidsize2 int) row format delimited fields terminated by , stored as textfile; truncate table total; load data local inpath /root/files into table total; drop table if exists total_bucket; 禁止修改 begin create table if not exists total_bucket( tradedate string, securityid string, bidsize1 int, bidsize2 int )clustered by(securityid) into 6 buckets row format delimited fields terminated by , stored as textfile; set hive.enforce.bucketing = true; insert overwrite table total_bucket select tradedate,securityid,bidsize1,bidsize2 from total; selecttradedate,securityid,sum(bidsize1+bidfsriozmet2o)tal_bucketablesample(bucke2t out of 2 on securityid) group by tradedate,securityid; end