Hive-HQL数据定义DML

小技巧说明：

    在Hive Cli中，可以在！后加一些简单的linux命令执行，如clear、cat、ls等；

向管理表中load数据：

    load data [local] inpath '${env:HOME}/test_file_dir' overwirte into table test_table partition (...);

    路径可以为一个文件，或者一个目录，如果是目录，Hive会将该目录下所有文件都加载到hdfs中，但是该目录下不可以再包含任何子目录；

    注意：

    如果没有local关键字，Hive会将hdfs中的数据转移到目标位置，注意是转移，可能考虑到分布式文件系统不需要保存多份数据；同时，必须保证源文件和目标地址应该       在同一个hdfs集群中。

    Hive并不会验证用户load的数据和表的schema是否匹配，但是Hive会验证文件格式是否和表定义的一致。

通过查询语句向表中插入数据：

    insert overwirte table test_table partition (...) select * from other_table a where a...

    另一种高效的load方式，只扫描一遍表：

    from other_table a

    insert overwrite table test_table1 partition (...) select * where a...

    insert overwrite table test_table2 partition (...) select * where a...

    insert overwrite table test_table3 partition (...) select * where a...

动态分区插入：

    insert overwrite table test_table

    partition (pt_key1, pt_key2)

    select ..., a.key1, a.key2

    from other_table a;

    Hive会根据select语句的最后两列来确定分区字段的值，在实际应用中静态分区和动态分区可以混合使用，但是静态分区键必须在动态分区键之前。

    默认情况下动态分区功能未开启，开启后采用strict模式执行，下面列出经常使用的动态分区属性：

    hive.exec.dynamic.partition=true; 开启动态分区

    hive.exec.dynamic.partition.mode=strict; strict模式，至少有一列分区字段是静态的

    hive.exec.max.dynamic.partitions.pernode=100; 每个map或reduce可以创建的最大分区个数

    hive.exec.max.dynamic.partitions=1000; 一个动态分区创建语句可以创建的最大动态分区数

    hive.exec.max.created.fields=100000; 全局可以创建的最大文件个数

查询语句创建表并加载数据：

    create table test_table as select * from other_table where ...;

导出数据：

    如果数据文件恰好是用户需要的，直接拷贝hdfs文件即可：hadoop fs -cp src_path target_path

    也可以查询之后重定向到本地文件中；

    或者：

    insert overwrite [local] directory 'path' select * from test_table where ...;

    from test_table a

    insert overwrite directory 'path_one' select * where ...

    insert overwrite directory 'path_another' select * where ...

    注意：

    在Hive0.10.0之后上述方式可以定制输出文件的格式：

    insert overwrite directory 'path' row format delimited fields terminated by '\t' lines terminated by '\n' select * from test_table where ...;

    对于0.10.0之前的版本，可以自己写一个定制的OutputFormat控制数据格式（负责），另一种方式可以采用临时表的方式，不过还得自己删除，略麻烦，最简单办法是使用Hive提供的内置函数，比如concat等实现自己希望的格式。

简单查询：

    select * from test_table;

    select a.field1, a.field2 ... from test_table a;

    当选择的列是集合数据类型时，Hive会使用JSON语法输出（字符串用引号括起来，但是正常的STRING类型时查询出来是不带引号的）；

    select field1, field2[0] from test_table;    field2是ARRAY类型，索引同样从0开始；

    select field1, field2["key"] from test_table;    field2是MAP类型；

    select field1, field2.subfield from test_table;    field2是STRUCT类型；

    同样，可以使用正则表达式选择查询列；

    与SQL一样，Hive不但可以选择表中的列，还可以使用函数和算术表达式来操作列值；

    算术运算符：+（加）、-（减）、*（乘）、/（除）、%（取余）、&（与）、|（或）、^（异或）、~（非）

    数学函数：round(d)、round(d, n)、floor(d)、ceil(d)、rand()、exp(d)、ln(d)、log10(d)、log2(d)、log(base, d)、pow(d, p)、sqrt(d)、bin(i)、hex(i)、unhex(b)、conv(num, from_base, to_base)、abs(d)、pmod(i1, i2)、sin(d)、asin(d), cos(d)、acos(d)、tan(d)、atan(d)、degrees(d)、radians(d)、positive(i)、negative(i)、sign(d)、e()、pi()

    注意，遇到将浮点数转换为整形时，floor、ceil、round这些函数是首选，而不是使用cast强制类型转换；

    聚合函数：count()、sum()、avg()、min()、max()、collect_set()

    小技巧：通常，可以通过设置属性hive.map.aggr=true来提高聚合的性能，这个设置会触发在map阶段进行“顶级”聚合过程（非顶级聚合过程将会在执行一个GROUP BY后进行），不过，这个设置将需要更多的内存；

    表生成函数：与聚合函数相反，将单列扩展为多列或者多行，注意，在使用表生成函数时，Hive要求使用列别名，explode(array)、explode(map)、inline()、json_tuple()、parse_url_tuple()、stack()

    其他内置函数：ascii(s)、base64(bin)、binary(s)、cast(expr as type)、concat()、concat_ws()、decode()、encode()、find_in_set()、format_number()、get_json_object()、in()、in_file()、in_str()、length()、locate()、lower()、lpad()、ltrim()、parse_url()、printf()、regexp_extract()、regexp_replace()、repeat()、reverse()、rpad()、rtrim()、size()、space()、split()、str_to_map()、substr()、translate()、trim()、unbase64()、upper()、from_unixtime()、unix_timestamp()、to_date()、year()、month()、day()、hour()、minute()、second()、weekofyear()、datadiff()、data_add()、date_sub()

limit语句：

    select upper(field1), field2["key"] as new_field2, round(1 - field3*0.2) as new_filed3 from test_table limit 2;

嵌套select语句：

    select e.field1, e.field2 from (

        select a.field1, a.field2 from test_table a) e where e.field1 > 10;

    from (

         select a.field1, a.field2 from test_table a

    ) e

    select e.field1, e.field2 where e.field1 > 10;

case when ... then ... when ... then ... else ... end

避免进行MR：

    大多数查询都会触发一个MR任务（job），Hive中对某些情况可以不必使用MR，也就是所谓的本地模式，比如select * from test_table，同样，对于where语句中只有分区字段时也不会出现MR，此外，可以设置属性hive.exec.mode.local.auto=true，Hive会尝试使用本地模式进行操作，所以最好将其设置到$HOME/.hiverc中；

where注意事项：

    select field1, 2*(1 - field2) as new_field2 from test_table where round(new_field2) > 80;    该语句在HIVE中会报错，Hive不允许在where语句中使用别名，不过可以使用一个嵌套查询进行改写：

    select e.* from (

        select field1, 2*(1 - field2) as new_field2 from test_table

    ) e where round(e.new_field2) > 80; 

谓词操作符：

    A=B、A<>B、A!=B、A<B、A>B、A [NOT] BETWEEN B AND C、A IS [NOT] NULL、A [NOT] LIKE B、A RLIKE B、A REGEXP B

浮点数比较注意事项：

    如果表中定义为float类型field，谓词表达式为field > 0.2，那么查询结果会包含0.2，因为其实float类型为0.2000001表示，转换为double型进行比较，所以，正确方式应该显示将0.2转换为float型cast(0.2 as float)，在进行比较；

Hive中group by和having与SQL相同；

连接Join：

    Hive支持Join操作，但是只支持等值连接，因为通过MR很难实现这种类型的连接，同时Hive中目前还不支持在ON子句中的谓词间使用OR；

Inner Join：

    select a.field, b.field from test_table1 a join test_table2 b on a.field=b.field where ...

    大多数情况下，Hive会对每对Join连接对象启动一个MR任务；但是，如果每个ON子句中都使用同一个连接字段作为其中一个连接键，HIVE通过一个优化可以在同一个MR job中连接多张表；

    Hive同时假定查询中最后一个表是最大的那个表，在对每行记录进行连接操作时，它会尝试将其他表缓存起来，然后扫描最后那个表进行计算，因此，用户需要保证连续查询中的表的大小从左到右依次增加；用户也可以显示的告诉查询优化器哪张表是大表: /* +STREAMTABLE(s) */

Left Outer Join：

    注意：在使用外连接时，Hive会忽略ON后面的分区条件，在内连接中有效；where语句在连接操作执行后才会执行？？？（有待验证）

Right Outer Join：

Full Outer Join：

Left Semi Join：

    Hive中不支持in、exists等谓词，所以可以使用左半外连接实现该功能，注意Hive不支持Right Semi Join；通常来说，如果只需要左边表的记录，那么Semi Join比Join要高效一些，对于左边表中的一条指定记录，如果在右表中找到匹配，Hive就会立即停止扫描；

笛卡尔积Join：

    select * from a join b;其实没有理由去执行笛卡尔积操作，指定hive.mapred.mode=true，Hive会阻止用户只需笛卡尔积操作；

Join优化-Map-side Join：

    如果所有表中只有一张表是小表，那么可以在最大的表通过mapper的时候，将小表完全放到内存中，Hive可以在map端执行连接过程，从而省略常规连接操作所需要的reduce操作；即使是很小的表，这个优化也明显要快于常规连接操作；

    /* +MAPJOIN(d) */该标记可以手动触发map-side join，或者可以通过设置属性hive.auto.convert.join=true让Hive在必要的时候启动这个优化，该属性依据另一个属性hive.mapjoin.smalltable.filesize=25000000判断是否使用map join，默认是25M；

    该开关可以设置在$HOME/.hiverc中；

ORDER BY：

    Hive会对查询结果集执行一个全局排序，也就是说所有数据都会通过一个reduce进行处理，对于大数据集，这个过程会执行很长时间；如果是strict模式，Hive要求ORDER BY必须使用limit；

SORT BY：

    只会对每一个Reduce中的数据进行排序，局部排序过程，这样可以提高后面进行全局排序的效率；

含有SORT BY的DISTRIBUTE BY：

    默认情况下，MR计算框架会依据Map输入的键计算相应的hash值，然后按照得到的hash键值对均匀的分发到多个reduce中；如果想要指定某个字段都分配到同一个reduce中进行处理，这是可以使用distribute by，然后再使用sort by；

    select a.field1, a.field2 from test_table a distribute by a.field1 sort by a.field1;    注意distribute by必须写在sort by前面

CLUSTER BY：

    如果distribute by字段和sort by字段相同，而且是asc，那么等价于直接使用cluster by； 这种方式怎么可以实现全局有序呢？？？

隐式类型转换和显示类型转换：

    Hive在比较时会将小类型隐式转换为大类型，也可以通过cast(value as TYPE)显示转换类型；

    注意，Hive在类型检查是很宽松，如果类型不符合，会设置为NULL，而不会报错；

抽样查询：

    扫描表中所有的数据，每N行中抽取第几行数据；

    对于非常大的数据集，用户需要使用一个具有代表性的查询结果而不是全部结果，Hive可以通过对表进行分桶抽样：

    select * from test_table TABLESAMPLE (BUCKET 3 OUT OF 10 ON field) s;

    数据块抽样：

    select * from test_table TABLESAMPLE (0.1 PERCENT) s;

    分桶表，如果抽样列与分桶列相同，那么TABLESAMPLE只会扫描涉及到的hash分区数据；

UNION ALL：

    除非建立了索引，否则对同一份数据的union all查询会导致同一份数据进行多次拷贝分发；

以上就是Hive DML相关的部分，内容有点儿杂。。。不过大部分都挺有用的，还可以继续深挖！