Hive(三) -- dml

Hive(三) -- dml

一、插入

1. load

        load data [local] inpath '/../..' [overwrite] into table tname [partition(partcol1=val1,...)];

        1）从本地加载

                使用local关键字加载本地的数据，实质是将本地数据上传到HDFS中Hive表的目录下。

        2）从HDFS加载

                加载HDFS上的数据，此时会将数据移动到Hive表的目录下。实际只需要修改元数据中

                的抽象目录树即可。

2. insert

        1）插入一条数据（不推荐）

                insert into table tname values(xx,yy,zz);

                这种插入方式会先创建一个临时表values_tmp_table_1，将要插入的数据插入到临时表

                中，后面再从临时表中复制过来。（建表 + 单重插入）

        2）单重插入                 

                insert into table tname select ... from ...;

                使用查询语句进行插入，每次插入数据都是一次查询的结果

        3）多重插入

                from tname 

                        insert into table tname_1 select ...

                        insert into table tname_2 select ...;

                对表扫描一次，插入多个查询结果。

3. 分区表的数据插入

        分区表数据插入支持load方式，但不适用，一般都使用insert方式。

        分区插入有两种，静态分区和动态分区。如果混合使用，则静态分区必须在动态分区之前。

        静态分区：必须手动创建，然后在指定分区中插入数据。其常用步骤为：

                                1）创建一个分区表        2）添加分区        3）向指定分区添加数据

        动态分区：分区会根据数据动态生成。

                1）创建分区表（和静态分区相同）

                2）向分区表中插入数据。不需要指定分区名，只需要指定分区字段，且查询语句中必须

                                                          要查询出分区字段。查询时默认将末尾字段作为分区字段。

                        insert into table tname partition(partcol) select ..., ... , partcol from ...;

        多个分区字段：每个字段代表一级目录。多字段分区，必须至少指定一个静态分区。插入时

                                 字段保持一致即可。

4. 分桶表数据插入

        分桶表数据插入不支持load方式。分桶就是MR中的分区，而load方式不会经过MR过程。

        建表：

                create table stu(id int, name string, age int, sex string) clustered by (age) sorted by (id

                        desc) into 3 buckets;

        插入：

                insert into stu select id,name,age,sex from student;

        bucket1  --  age % 3 == 0

        bucket2  --  age % 3 == 1

        bucket3  --  age % 3 == 2

5. insert数据导出

        将Hive表中的数据导出到本地

        insert overwrite local directory '本地空目录' select ...;

二、查询

        Hive 中的 SELECT 基础语法和标准 SQL 语法基本一致，支持 WHERe、DISTINCT、GROUP BY、ORDER BY、HAVINg、LIMIT、子查询等。

 1. Hive中的join

        Hive中的join只支持等值连接和and连接

        1）内连接：join

                取两个表的交集

        2）外连接：left join

                以join左侧表为依据，右侧的表若关联不上则补null

        3）全外连接：full join

                取两个表的并集。关联不上补null

        4）半连接：left semi join

                结果只有左半边表的数据，且是能与右表关联上的数据。

                Hive中支持 in 但性能不高，左半连接就是为了取代 in *** 作

2. Hive中的by

        1）order by：全局排序

        2）sort by：局部排序。针对于每一个reducetask的结果进行局部排序，默认只有1个

                                              reducetask时与order by效果相同。

        3）distribute by：分组（类似于分桶）。将数据按照给定的字段进行分组（与分桶规则相

                                                                         同），分组的个数取决于reducetask的个数。若想

                                                                         对分组后的数据排序，可结合sort by。

        4）cluster by：先分组再排序。分组和排序字段相同。

        当分组字段与排序字段相同时：cluster by = distribute by + sort by

        当分组字段与排序字段不同时：只能使用 distribute by + sort by

3. Hive中的 Union 和 Union All

        join  -- 横向拼接

        union  -- 纵向拼接。将两个或多个查询结果拼接在一起

        union  -- 去重

        union all -- 不去重

4. Hive SQL语句执行顺序

        select * from ... where ... group by ... having ... order by ... limit ...

        执行顺序：1）from  2）where  3）group by   4）having

                          5）select  6）order by