hive 常用命令

1.set hive.cli.print.header=true; //打印表格头部

2.set hive.mapred.mode=strict;  //设置hive为“严格”模式，这样如果对分区表进行查询而where子句没有加分区过滤的话，将禁止提交这个任务

3.select * from new_cnt；//显示表格

4.limit 20;

5.crate database  name； // 创建数据库

6.show databases；

7.show database like “h.*"  //显示“h”开头的database

8.crate external table name  //建立外部表

9.crate table name（  ）partition by（ county string，state string）  //建立表并分区

10.drop table tablename；//删除表格

11.alter table log_messages rename to logmsgs；//将表log_massages重命名为logmsgs

12.alter table log_massages add    //增加表的新分区

      partition（year = 2011，month = 1，day = 1） location"/logs/2011/01/01"

      partition（year = 2011，month = 1，day = 2） location"/logs/2011/01/02"

      partition（year = 2011，month = 1，day = 3） location"/logs/2011/01/03"

  .....;

13..alter table log_massages  partition（year = 2011，month = 1，day = 2）

set location"s3n://ourbucket/logs/2011/01/02" ; //移动分区

14.alter table  log_massages drop partition（year = 2011，month = 1，day = 2）

//删除分区

15.insert overwrite(into) table employees  partition (county = "US",state="OR")

    select * from staged_employees se where se.cnty = "US" and se.st = "OR";

      //把表 “se” 中的数据插入到表“s”中    ----静态分区插入

16.insert overwrite(into) table employees partition (county,state)

    select ...,se.cnty,se.st from staged_employees se；

    //把表“se”中的数据插入到表“s" -------动态分区插入

17.insert overwrite(into) table employees  partition (county = "US",state)

    select ...,se.cnty,se.st from staged_employees se where se.cnty = "US" ；

    //把表“se”中的数据动态和静态结合插入到表“s”中

属性名称缺省值描述

hive.exec.dynamic.partitionfalse设置成true，表示开启动态分区功能

hive.exec.dynamic.partition.modestrict设置成nonstrict，表示允许所有分区都是动态的

hive.exec.max.dynamic.partitions.pernode100每个mapper或reducer可以创建的最大动态分区个数。如果某个mapper或reducer尝试创建大于这个值得分区的话则会抛出一个致命错误信息

hive.exec.max.dynamic.partitions+1000一个动态分区创建语句可以创建的最大动态分区个数。如果超过这个值则会抛出一个致命错误信息

hive.exec.max.created.files10000全局可以创建的最大文件个数。有一个Hadoop计数器会跟踪记录创新了多少个文件，如果超过这个值则会抛出一个致命错误信息

sethive.exec.dynamic.partition=true；

set hive.exec.dynamic.partition.mode=nonstrict；

set hive.exec.max.dynamic.partitions.pernode=1000

18.Hadoop fs -cp source_path target_path,    //导出数据

    insert overwrite local directory"/tmp/ca_employees" 

    select name,salary,address from employees where se.state = "CA";

19.Hive distribute by sorl by order by 的区别

distribute by：控制着在map端如何分区，按照什么字段进行分区，要注意均衡

sort by：每个reduce按照sort by 字段进行排序，reduce的数量按照默认的数量来进行，当然可以指定。

               最终可以进行归并排序得出结果。

                适用于数据量比较大的排序场景。

order by：reduce只有一个，在一个reduce中完成排序，使用于数据量小的场景。

20.都是kill子任务：

-- kill job：

hadoop job  -kill job_1537547243219_0058

-- kill application：

yarn application -kill application_1537547243219_0056

HiveQL之Sort by、Distribute by、Cluster by、Order By详解

在这里解释一下select语法中的order by、sort by、distribute by、cluster by、order by语法。

一、order by语法

在hiveQL中Order by语法类似于sql语言中的order by语法。

colOrder: ( ASC | DESC )

colNullOrder: (NULLS FIRST | NULLS LAST)           -- (Note: Available in Hive 2.1.0 and later)

orderBy: ORDER BY colName colOrder? colNullOrder? (',' colName colOrder? colNullOrder?)*

query: SELECT expression (',' expression)* FROM src orderBy

有一些限制在order by子句中，在严格模式下（strict），order by子句必须带有以limit子句。Limit子句不是必须的，如果你设置hive.mapred.mode是非严格模式（nostrict）。原因是为了增强全局排序的结果，这里只有一个reduce去进行最终的排序输出，如果输出结果集行集太大，这单独的reduce可能需要花费非常长的时间去处理。

注意，这列必须使用的名字，而不能指定位置号，然而在hive 0.11.0以后，在做了下面配置之后列可以指定序列号，如下：

1）从hive0.11.0到2.1.x，设置hive.groupby.orderby.position.alias 是true（默认值是false）。

2）从hive2.2.0以后，hive.orderby.position.alias默认是ture。

Order by默认排序是asc。

在hive 2.1.0以后，被选择排序的每个列是null在order by子句是支持的。默认null排序规则是asc（升序）是nulls first，当默认的排序规则是desc（时）是nulls last

二、sort by语法

在hiveQL中sort by语法类似于sql语言中的order by语法。

colOrder: ( ASC | DESC )

sortBy: SORT BY colName colOrder? (',' colName colOrder?)*

query: SELECT expression (',' expression)* FROM src sortBy

Hive中被用来sort by排序的行的排序操作发生在发送这些行到reduce之前。排序的次序依赖于排序列的类型，如果列是数值类型，那么排序次序按照数值排序，如果列式字符串类型，那么排序次序将按照字典排序。

1、sort by和order by的不同点

Hive sort by的排序发生在每个reduce里，order by和sort by之间的不同点是前者保证在全局进行排序，而后者仅保证在每个reduce内排序，如果有超过1个reduce，sort by可能有部分结果有序。

注意：它也许是混乱的作为单独列排序对于sort by和cluster by。不同点在于cluster by的分区列和sort by有多重reduce，reduce内的分区数据时一致的。

通常，数据在每个reduce排序通过用户指定的规则，如下示例：

SELECT key, value FROM src SORT BY key ASC, value DESC

查询有两个reduce，查询结果如下：

0   5

0   3

3   6

9   1

0   4

0   3

1   1

2   5

2、设置排序类型

转换后，参数类型一般认为是string类型，意味着数值类型将按照字典排序方式进行排序，克服这一点，第二个select语句可以在sort by之前被使用。

FROM (FROM (FROM src

            SELECT TRANSFORM(value)

            USING 'mapper'

            AS value, count) mapped

      SELECT cast(value as double) AS value, cast(count as int) AS count

      SORT BY value, count) sorted

SELECT TRANSFORM(value, count)

USING 'reducer'

AS whatever

三、Cluster By 和Distribute By语法

Cluster by和distribute by主要用在进行Transform/Map-Reduce脚本。但是，他有时可以应用在select语句，如果有一个子查询需要分区和排序对于输出结果集。

Cluster by是一个捷径对于包含distribute by和sort by的语句。

Hive使用distribute by中的列去分发行到每个reduce中，所有同样的distribute by列的行将发送到同一个reduce。然而，分发并不保证clustering和sorting的属性在分发关键字。

例如，我们按照x进行distribute by在5行数据到2个reduce中：

x1

x2

x4

x3

x1

Reduce 1 得到的数据：

x1

x2

x1

Reduce 2 得到的数据：

x4

x3

注意：这所有制是x1的行确保被分发到通一个reduce中，但是，他们不能保证在集合中会在相邻的位置。

相比之下，如果我们使用cluster by x，这两个reduce将进一步在x上进行排序。

Reduce 1得到数据：

x1

x1

x2

Reduce 2得到数据：

x3

x4

不能替代cluster by，用户可以指定distribute by和sort by，同样，分区列和排序列可以是不同的，通常情况，分区类是排序类的前缀，但是他并不是必须的。

SELECT col1, col2 FROM t1 CLUSTER BY col1

SELECT col1, col2 FROM t1 DISTRIBUTE BY col1

SELECT col1, col2 FROM t1 DISTRIBUTE BY col1 SORT BY col1 ASC, col2 DESC

FROM (

FROM pv_users

MAP ( pv_users.userid, pv_users.date )

USING 'map_script'

AS c1, c2, c3

DISTRIBUTE BY c2

SORT BY c2, c1) map_output

INSERT OVERWRITE TABLE pv_users_reduced

REDUCE ( map_output.c1, map_output.c2, map_output.c3 )

USING 'reduce_script'

AS date, count;

20.count（1）和count（*）的区别

count(*)表示检索表中所有列，并统计记录数，包括重复列和空值。

count(1)中1表示表中的第一列，即检索表中第一列，并统计第一列不为空的记录数

21.concat、concat_ws、group_concat函数用法

一、concat()函数可以连接一个或者多个字符串

　　CONCAT(str1,str2,…) 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

　　select concat('11','22','33');     112233

二、CONCAT_WS(separator,str1,str2,...) 

　　是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。

　   select concat_ws(',','11','22','33');   　11,22,33

三、group_concat()分组拼接函数

　　group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

　　对下面的一组数据使用 group_concat()

　　| id |name

　　|1 | 10|

　　|1 | 20|

　　|1 | 20|

　　|2 | 20|

　　|3 | 200   |

　　|3 | 500   |

　　1、select id,group_concat(name) from aa group by id;

　　|1 | 10,20,20|

　　|2 | 20 |

　　|3 | 200,500|

　　2、select id,group_concat(name separator ';') from aa group by id;

　　|1 | 10;20;20 |

　　|2 | 20|

　　|3 | 200;500   |

　　3、select id,group_concat(name order by name desc) from aa group by id;

　　|1 | 20,20,10   |

　　|2 | 20|

　　|3 | 500,200|

　　4、select id,group_concat(distinct name) from aa group by id;

　　|1 | 10,20|

　　|2 | 20   |

　　|3 | 200,500 |