hive-sql语句-小小结

使用的CDH-5.8.3部署和管理整个hadoop生态集群，使用hive版本是hive-1.1.0

hive的官网上说不支持hive的一条条的插入（使用insert into命令），但是在执行过程中能完全运行insert into命令。

使用sqoop-1.4.6 对mysql与hive的数据的互相传输

1、使用sqoop由mysql导入到hive中

命令：

sqoop import --connect jdbc:mysql://mysql-ip:3306/kbyxdb --username mysql-name  --password mysql-password --table kbyx_weibo_wbuser_copy --hive-import --hive-database sinaweibo --hive-overwrite --create-hive-table --hive-table kbyx_weibo_wbuser_copy --null-string '\\N' --null-non-string '\\N' --fields-terminated-by '\t' --lines-terminated-by '\n';

使用sqoop由hive导入到mysql中

sqoop export --connect  "jdbc:mysql://mysql-ip:3306/mysql-database-name?useUnicode=true&characterEncoding=utf-8" --username root --password zysdadmin123 --table xiuxiu_user --export-dir /user/hive/warehouse/kbyx.db/xiuxiu_user --input-fields-terminated-by '\t' -input-null-string '\N'

其分割符'\t' 要与hive表的分隔符一致，hive表的默认分隔符为'\001'，而sqoop的默认分隔符为','

创建分区表：

create table if not exists user_bicinfo(id int,userid int,username string,sex string,imgurl string,vip string,level int,status string,location string, platformid string) partitioned by(date string) row format delimited fields terminated by '\t' lines terminated by '\n';

使用sqoop-1.4.6向分区表中导入数据

sqoop import --connect jdbc:mysql://mysql-ip:3306/mysql-database-name --username mysql_user_name --password mysql_user_password --table user_group_relation --hive-import --hive-overwrite --hive-database iqiyi --hive-table user_group_relation --hive-partition-key 'date' --hive-partition-value '2017-08-21' --null-string '\\N' --null-non-string '\\N' --fields-terminated-by '\t' --lines-terminated-by '\n';

查询1：

在同一张表中，分别有字段电影id（movie_id，其中一个电影的id号为342067）、每部电影所看的用户id（user_id）。

查询条件是：看过电影342067的所有用户所看过的电影的前十名，并把查询结果存入第三方表中

         set hive.exec.mode.local.auto=true;
         set hive.exec.parallel=true;
         set hive.exec.dynamic.partition=true;
         set hive.exec.dynamic.partition.mode=nonstrict;
         set hive.merge.mapfiles=true;
         set hive.merge.mapredfiles=true;
         set  hive.groupby.skewindata=true;
         set hive.vectorized.execution.enabled=true;
         set hive.vectorized.execution.reduce.enabled=true;
         set hive.merge.mapfiles=true;
         set hive.merge.mapredfiles=true;
         set mapred.job.reuse.jvm.num.tasks=10;
         set hive.merge.size.per.task=256000000;
         set mapred.max.split.size=256000000;
         set mapred.min.split.size.per.node=256000000;
         set mapred.min.split.size.per.rack=256000000;
         set hive.exec.reducer.bytes.per.reducer=512000000;
         set mapred.reduce.tasks = 5;

以上设置根据自己的数据和集群的配置

create table weibo_user comment '第一个字段为电影id号，第二个字段为统计个数，第三个为时间' row format delimited fields terminated by '\t' lines terminated by '\n' as select w.movie_id,count(w.movie_id) as num,from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') as time  from maoyan_user_watched w join maoyan_user_watched m on (w.user_id = m.user_id) where m.movie_id = 342067 group by w.movie_id sort by num desc limit 10;

查询2：

表userinfo和表maoyan_user_watched，看过某部电影用户的地区分布，并存入第三方表中

        set hive.exec.mode.local.auto=true;
         set hive.exec.parallel=true;
         set hive.exec.dynamic.partition=true;
         set hive.exec.dynamic.partition.mode=nonstrict;
         set hive.merge.mapfiles=true;
         set hive.merge.mapredfiles=true;
         set  hive.groupby.skewindata=true;
         set hive.vectorized.execution.enabled=true;
         set hive.vectorized.execution.reduce.enabled=true;
         set hive.merge.mapfiles=true;
         set hive.merge.mapredfiles=true;
         set mapred.job.reuse.jvm.num.tasks=10;
         set hive.merge.size.per.task=256000000;
         set mapred.max.split.size=256000000;
         set mapred.min.split.size.per.node=256000000;
         set mapred.min.split.size.per.rack=256000000;
         set hive.exec.reducer.bytes.per.reducer=512000000;
         set mapred.reduce.tasks = 5;

根据自己的数据和集群的配置

create table maoyan_area_distribution comment'每个地区的分布，任务时间'  row format delimited fields terminated by '\t' lines terminated by '\n' as select /*+streamtable(userinfo)*/ userinfo.area,count(userinfo.area) as num,from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') from userinfo full outer join maoyan_user_watched on (maoyan_user_watched.user_id = userinfo.userid) where maoyan_user_watched.movie_id = 344264 group by userinfo.area sort by num desc;