【Hive】SQL使用

2024-02-13 21:29:10

强制删除一个database
DROP DATABASE IF EXISTS dbname CASCADE;
删除一个table
drop table if exists xxxx
分区查询
show partitions xxx
Hive复杂查询
- select * from pic_collection where pic_collection.uid in (select uid from pic_collection group by uid having count(*) > 1)
- insert overwrite local directory ‘/var/lib/hadoop-hdfs/chenguolin/pic_gateway/url’ select ori_pic_url from pic_collection where pic_collection.uid in (select uid from pic_collection group by uid having count(*) > 1);
hive表添加字段
ALTER TABLE sdk_report_upstream ADD COLUMNS (push_ping_packet_loss_cnt int, push_ping_succ_cnt int, push_ping_total_rtt double);
Hive Map查询
- select version, response_code from chaos_log where date=2017121817 and version=6850 and size(response_code) > 0 limit 10;
- select version, response_code from chaos_log where date=2017121817 and version=6850 and size(response_code) > 0 and response_code[‘0’] = ‘403’
Hive查询指定特殊的分隔符
- insert overwrite local directory ‘/var/lib/hadoop-hdfs/chenguolin/pic_gateway/url’ row format delimited fields terminated by ‘\t’ COLLECTION ITEMS TERMINATED BY ‘,’ MAP KEYS TERMINATED BY ‘:’ select ori_pic_url from pic_collection where pic_collection.uid in (select uid from pic_collection group by uid having count(*) > 1);
Hive distinct使用
- select day, stream_type, count(distinct id) from media_quality.sdk_report_upstream where date >= 2017122700 and date <= 2017123123 group by stream_type, day;