Hadoop day06(DML语句+函数使用 )

一、Hive SQL DML语句

      1、加载数据

官方推荐加载数据方式：清洗数据 成为结构化文件，在使用LOAD语法加载数据到表中。

注意：使用前先到目标数据库下，不知道自己在哪个数据库下，可以使用

          select current_database();       -- 查询当前数据库

            1）load加载（官方推荐）

                 将数据文件移动到与Hive表对应的位置，移动时是复制、移动操作。

                纯复制、移动指在数据load加载到表中时，HIve不会对表中的数据内容进行任何转换、任何操作。

                   语法规则：

                    LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;  

                     其中LOCAL是指：Hiveserver2服务所在机器的本地Linux文件系统，不是Hive客户端所在的本地文件系统。   不写则默认是HDFS中加载

                  示例：加载student.txt

                       本地加载数据

                         create table student_local(

                                      num int,

                                      name string,

                                      sex string,

                                      age int,

                                      dept string)

                                       row format delimated fields terminated by ",";

                       加载本地数据（日志中可以看到）

                         LOAD DATA LOCAL INPATH '/root/hivedata/student.txt' INTO TABLE student_local;

                         

                           HDFS加载数据                               

                                  create table student_HDFS(

                                                    num int,

                                                     name string,

                                                      sex string,

                                                      age int,

                                                      dept string)

                                                       row format delimated fields terminated by ",";

                            加载HDFS数据（日志中可以看到）

                            LOAD DATA INPATH '/students.txt' INTO TABLE  student_HDFS;

             2）insert插入数据（比较慢，会涉及MR）

                     常用：将查询返回结果插入到另一张表中（insert+select）

                              常用要求：保证查询结果列的数目和需要插入数据表格的列数目一致

                                                  查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为Null。

                               语法：INSERT INTO TABLE tablename select_statement1 FROM from_statement；

                                示例：创建一张目标表，只有俩个字段

                                      create table student-from_insert(sno int,sname strring);

                                      使用insert+select插入数据到新表中 

                                      insert into table student_from_insert select num,name from student;

                                      查看新表

                                       select * from student_from_insert; 

                                     

              3）直接在HDFS页面Web UI上上传

              4）使用hadoop fs -put |-mv等翁氏直接将数据移动到表文件夹下

       2、查询数据

               1)select 语法介绍

             （表名列名不区分大小写）

  示例：有一个感染情况的数据文件。

           创表

           加载

           简单查询

 

                2）select_expr、ALL、 DISTINCT结果返回与去重

                       select_expr:介于select和from之间，也就是需要查的对象

                              select */all  from 表名；    --查询所有字段或指定字段

                               select  字段1，字段n from 表名；  --查询字段。

                   ALL、 DISTINCT

                     

              去重结果：（上部分原数据，下部分为去重结果）

            外加一个 a      WWW

                3）where 过滤

                      where后面是一个布尔表达式，（结果要么为TRUE，要么为FALSE），用于查询过滤，当布尔表达式为true时，返回select后面expr表达式的结果，否则返回空。

                     在where表达式中，可以使用Hive支持的任何函数和运算符，当聚合函数除外。

 

                4）聚合操作

                      sql中拥有很多可用于计数和计算的内建函数，其中使用的语法是：

                      select function（列） from 表

                     聚合操作函数：如：count、sum、max、min、avg等函数。

                       聚合函数特点：不管原数据有多少，经过聚合后只返回一条数据，返回的数据就是聚合结果。

                  

 示例：

                 5）GROUP BY 分组

                        根据一个或多个列对结果集进行分组，没有group by 语法，则表中的所有行数据当成一组

                         group by 语法限制：

                          出现在group by 中的select_expr字段，要么是group by分组的字段，要么是被聚合函数应用的字段。

                       原因：避免出现一个字段多个值的歧义。 

                        select_expr中出现的：因为就是基于该字段分组的，同一组中必相同

                         被聚合函数应用的字段，也没歧义，因为聚合函数的本质就是多进溢出，最终返回一个结果。

               示例：

             错误示例：

               select  count（score）as  count_score, age from student where class=3 group by score;

原因：多了age，既不是group by分组字段，也不是聚合函数字段（没人在背后支持它，所以它不能被选中）

     正确示范：本处是做聚合函数，也可使用group by中添加。

              select  count（score）as  count_score, avg（age） from student where class=3 group by score;

                 6）HAVING 分组后过滤

                        HAVING 子句可以让我们筛选分组后的各组数据，并且可以在Having中使用聚合函数，因此此时where，group by已经执行结束，结果集已经确定。

                   在sql中增加having子句原因是，where关键字无法与聚合函数一起使用。

                 7）ORDER BY 排序

                        order by ：根据指定的列对结果集进行排序，默认asc（升序，也就是越来越大），desc（降序  越来越小）

                 8）LIMIT 返回条数限制

                      limit用于限制select语句返回的行数。接受1-2个数据参数，必须是非负整数常量。

                      第一个参数是返回第一行的偏移量，第二个参数指定要返回的最大行数。一个数字时，代表最大行数，并且偏移量默认为0。

执行优先级：

from>where>group(包含聚合)>having>order>select

聚合语句>having子句

where子句在查询过程中>聚合函数

注意事项：where  执行在聚合函数之前，having在聚合函数之后，group by 执行顺序是在select之前，所以group by中不能有select后面的字段名

       3、Join关联查询

                  背景：当不止一张表时，但表之间又有关联字段时。

               join语法出现：根据两个或多个表中的列之间的关系，从这些表中共同组合查询数据

            1）内连接：

inner join  （交集），inner可以不写，默认为inner join。也就是都匹配部分才能保留。

（两表中相同的字段为id，故on e.id =e_a.id)

 等价于：

     2）左连接：

left outer join 左外连接或者左连接，，以左边数据为准，关联右边，有则返回，无则显示null。

示例：

        二、函数使用

               1、函数概述和分类标准

                        为了提高sql编写率，有内置函数。分内置函数、用户自定义函数UDF

                        内置函数可分为：数值类型函数、日期类型函数、字符串类型函数、几何函数、条件函数等；

                         用户自定义函数根据输入输出的函数可分为3类：UDF、UDAF、UDTF。

                         show functions可以查看可用的所有函数，

                         describe function extended funcname 查看函数的使用方式

                2、常用内置函数

                  hive开发实现好，

                     字符串函数：

                   

>>>6

>>>tsacti

>>>anglela baby

>>>www.itcast.cn

>>>by   (倒着为-1开始，取右边的）

>>>ng  (正着来是1开始，取俩个）

>>>apache   

>>>

>>>

  日期函数

   时间戳：1970-01-01 00：00：00开始，每过一秒，加一。

 注意时间戳的格式

数学函数：

条件函数：

 

if条件判断：if（boolean testCondition， T valueTrue，T valueFalseOrNull） 

select if（1=2，100，200）     --如果1=2，返回100，否则返回200

  select if（sex = ’男‘，’M‘，’W'） from student limit 3；   --如果性别为男，则返回M，反之则W

  

空值转换函数：nvl（T value，T default_value)

select nvl（”allen“，”itcast“）；  --如果不为空，则返回自己的，也就是allen，否则返回指定值itcast。

select nvl（null，”itcast“）；

条件转换函数：CASE a when b THEN c [WHEN d THEN e] * [ELSE f] END

select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;   --100是50则返回为tom，是100则返回mary，都不是则返回tim

select case sex when '男‘ then ’male‘ else ’female‘ end from student limit 3；

                3、用户自定义函数UDF分类标准

               UDF：普通函数，一进一出  （扩大到hive所有函数中，包含内置函数和用户自定义函数）

                UDAF：聚合函数，多进一出（count）

                UDTF：表生成函数，一进多出