hive基础（二）--HQL语句

1.执行shell命令

用户不需要退出hive CLI，即可执行简单的bash shell 命令，只要在命令前加上！，并以分号结尾即可：

hive> ! echo "hello world";
"hello world"
hive> !pwd;
/home/linux

注：hive CLI不能使用需要用户进行输入的交互式命令，而且不支持shell的管道功能和文件名的自动补全功能。

2.使用 Hadoop dfs命令

hive> dfs -ls /;
Found 2 items
drwx------   - linux supergroup          0 2017-07-27 10:23 /tmp
drwxr-xr-x   - linux supergroup          0 2017-07-27 10:33 /user
hive> dfs -cat /user/hive/warehouse/example.db/example/example.txt;
01 小红 1910000000000
02 晓彤 2120000000000
03 依诺 2130000000000
注：使用Hadoop的dfs命令，去掉Hadoop关键字仅适用dfs即可，结尾使用分号；

     这种使用Hadoop的命令比在bash shell中执行Hadoopdfs命令更加高效，因为shell中会启动一个新的JVM实例，而hive会在同一个进程中执行该命令。

3.hive的数据库

（1）hive数据库本质上仅仅是表的一个目录或者命名空间。

（2）hive会为数据库创建一个目录，数据库中的表将会以这个数据库目录的子目录形式存储

（3）数据库所在的目录位于属性hive.metastore.warehouse.dir所指定的顶层目录之后，Hive将会对应的创建一个目        录/user/hive/warehouse/….db。注意，数据库文件目录以.db结尾，例如/user/hive/warehouse/example.db。

4.数据库操作

（1）创建数据库

      create database sogou_2017;

      create database if not exists sogou;

（2）查看数据库

      hive> show databases;
     OK
     default
     example
     sogou
     sogou_2017
     sogou_test
     Time taken: 0.079 seconds, Fetched: 5 row(s)

     使用正则表达书查询所需要的数据库：

     hive> show databases like 's.*';
     OK
     sogou
     sogou_2017
     sogou_test
     Time taken: 0.054 seconds, Fetched: 3 row(s)
（3）查看数据库信息

     hive> describe database example;
     OK
     example hdfs://master:9000/user/hive/warehouse/example.dblinuxUSER
     Time taken: 0.066 seconds, Fetched: 1 row(s)

（4）删除数据库

      hive> drop database if exists sogou;

      hive不允许用户删除一个包含表的数据库，需要先删除表。在删除命令后面加上关键字CASCADE；

      hive>drop database if not exists sogou_test CASCADE;

5.表的操作

（1）表分为内部表和外部表：

      内部表又称管理表和临时表，默认存储在配置项hive.metastore.warehouse.dir（例如，/user/hive/warehouse）所定义的目录的子目录下，有如下特点：
          当删除一个管理表时，Hive也会删除这个表中数据
          管理表不方便和其他工作共享数据

       删除外部表时，由于hive没有所有权，所以删除的只是元数据信息，而不会删除数据，一般来说，当数据被多个工具需要时，可以创建外部表。

       不可以根据位置信息判断内部表和外部表。

（2）创建内部表

       hive> create table example
            > (id int,name string,age int,tel string)
            > row format delimited
            > fields terminated by '\t'
            > stored as textfile;
      OK
      Time taken: 0.242 seconds

（3）创建外部表

       hive> create external table if not exists dogou_1w(
    > ts string,
    > keyword string,
    > rank int,
    > order int,
    > url string)
    > row format delimited
    > fields terminated by '\t'
    > stored as textfile
    > location '/sogou_1w';
    OK
    Time taken: 0.141 seconds

    注：hive较高版本会区分关键字，定义字段是需注意

（4）创建分区表

       开启动态分区功能：set  hive.exec.dynamic.partition=true;
       所有分区都是动态的：set  hive.exec.dynamic.partition.mode=nonstrict;

       hive> create table test
    > (id int,name string,tel string)
    > partitioned by
    > (age int)
    > row format delimited
    > fields terminated by '\t'
    > stored as textfile;

（5）修改表

       修改表名:hive> alter table dogou_1w rename to sogou_1w;

       添加列：hive> alter table test add columns(app_name string,sessiin_id int);

（6）删除表：hive> drop table if exists test;

       注：对于管理表，表的元数据信息和表内的数据都会被删除
            对于外部表，表的元数据信息会被删除，但是表中的数据不会被删除

6.加载数据

（1）hadoop fs -put  ******

      hadoop fs -put sogou_1w /sogou_1w

（2）load data local inpath '路径' [overwrite] into table 表名    

      load data local inpath ‘/sogou_1w’ into table sogou_1w_2;

（3）通过查询语句向表中插入数据：insert overwrite table 表名1 select * from 表名2;

      insert overwrite table sogou_1w_3 select * from sogou_1w;

（4）单个查询语句中创建表并加载数据：create table 新表名 as select * from 原有表;

      create table sogou_1w_4  as select * from sogou_1w；

7.HQL查询

（1）select from 语句

    统计数据总条数：select count(*) from sogou_1w;

（2）where 语句

    查询密码不为空的用户个数：select conut(*) from sogou_1w where keyword is not null and keyword !='';

（3）group by 和having语句

   group by：通常和聚合函数一起使用，按照一个或者多个列对结果进行分组，然后对每个组执行聚合操作
   having：允许用户通过一个简单的语法完成原本需要通过子查询才能对group by语句产生分组进行条件过滤的任务

   查询次数大于两次的用户总数：

   select count(a.uid) from (select uid,count(*) as cnt from sogou_1w group by uid having cnt>2) a;

（4）JOIN语句

   a.内连接 inner join

   内连接中，只有进行连接的两个表中都存在于连接标准相匹配的数据才会被保留下来同时出现
   select * from sogou_1w m join sogou_limit3 n on m.uid=n.uid;

   b.左连接 left outer join

    在这种join中，join操作符左边表中符合where子句的所有记录将会被返回。join操作符右边表中如果没有符合on后面连接条件的记录时，那么从右边表指定选择的列的值将会是null
   select m.uid,m.keyword,n.uid,n.keyword from sogou_1w m left outer join sogou_limit3 n on m.uid=n.uid;

   c.右连接 right outer join

    右外连接会返回右边表所有符合where语句的记录，左表中匹配不上的字段值用null代替

    select m.uid,m.keyword,n.uid,n.keyword from sogou_1w m right outer join sogou_limit3 n on m.uid=n.uid;

（5）order by 和sort by

   order by 语句与其他sql语句中的定义相同，他会对查询结果做一个全排列，即所有的数据都通过一个reducer进行处理过程，对于大数据集群而言，效率较低

   sort by 只会在每个reducer中对数据进行排序，即执行一个局部排序过程，这样可以保证每个reducer输出的数据是有序的，但并非全局有序，这样可以提高全局排列的效率

  排序时默认为升序ASC，加上关键字DESC，可表示降序排列

附：查询示例(来源：大数据培训）

1、查询多个字段用逗号分隔
select uid,keyword from sogou_1w limit 10;
2、数值类型可以进行加减乘除
select rank+1 from sogou_1w limit 10;
3、给rank平方
select pow(rank,2) from sogou_1w limit 3;
4、模（取余）
select pmod(rank+1,2) from sogou_1w limit 10;
5、强制类型转换
select cast(rank as DOUBLE) from sogou_1w limit 10;

6、将String类型的值联接起来
select concat(uid,keyword) from sogou_1w limit 10;
7、查询json格式的数据
select get_json_object('{"name":"xiaoming","age":"15"}','$.age') from sogou;
8、字符串的长度
select length (uid) from sogou_1w limit 10;
9、查询字uid中第9个位置之后字符串ee第一次出现的位置
select locate ("ee",uid,9) from sogou_1w limit 10;
10、表的嵌套
select count(distinct e.uid) as cnt 
from (select * from sogou_1w where rank <= 3 and order = 1) e;

11、like的使用，模糊查询，%表示任意长度的字符
select * from sogou_1w where url like 'http%' limit 10;
12、group by 分组查询

(一个字段)   *表示所有的
select rank,count(*) from sogou_1w group by rank;
( 多个字段)
select rank,order,count(*) from sogou_1w group by rank,order;
13、having 分组筛选，必须用于group by分组查询之上
select rank,order,count(*) from sogou_1w group by rank,order having rank>3;
14、取出sogou500w中的前三行数据放到sougou2_limit表中
insert overwrite table sougou_limit3 select * from sogou_1w limit 3;

15.搜索过仙剑奇侠传的用户
select distinct(uid) from sogou_1w where keyword like '% 仙剑奇侠传%';
16.搜索过仙剑奇侠传的用户还搜索过什么内容
(1)select m.uid,m.keyword from sogou m join (
select distinct uid from sogou_1w where keyword like '%仙剑奇侠传%')

 n on m.uid=n.uid where m.keyword not like '%仙剑奇侠传%';
(2)select m.uid,m.keyword from sogou_1w m join 
 (select distinct uid from   sogou_1w where keyword like '%仙剑奇侠传%') n on m.uid = n.uid where m.keyword rlike '~(.*仙剑奇侠传.* )';