Hive基本操作

Hive基本操作
实例：

建立外部表：

CREATE EXTERNAL TABLE `trojan_controller`(
`key` string COMMENT 'key',
`src` string COMMENT '',
`src_type` string COMMENT '',
`e_detail_type_code` string COMMENT '',
`e_base_type` string COMMENT '',
`e_subtype` string COMMENT '',
`e_detail_type` string COMMENT '',
`e_extend_type` string COMMENT '',
`full_time` string COMMENT '',
`e_date` string COMMENT '',
`e_hour` string COMMENT '',
`src_ip_point` string COMMENT 'IP_',
`src_ip_value` string COMMENT 'IP_',
`src_port` string COMMENT '',
`dst_ip_point` string COMMENT 'IP_',
`dst_ip_value` string COMMENT 'IP_',
`dst_port` string COMMENT '',
`ctrl_flag` string COMMENT '',
`r_value` string COMMENT '',
`src_ip_vest` string COMMENT 'IP',
`src_ip_area` string COMMENT 'IP',
`src_ip_operator` string COMMENT 'IP',
`dst_ip_vest` string COMMENT 'IP',
`dst_ip_area` string COMMENT 'IP',
`dst_ip_operator` string COMMENT 'IP',
`src_ip_longitude` string COMMENT 'IP',
`src_ip_latitude` string COMMENT 'IP',
`dst_ip_longitude` string COMMENT 'IP',
`dst_ip_latitude` string COMMENT 'IP',
`monitor_point_type` string COMMENT '',
`monitor_point_area` string COMMENT '',
`monitor_point_operator` string COMMENT '',
`monitor_physical_point` string COMMENT '',
`monitor_point_net` string COMMENT '',
`monitor_point_gatway` string COMMENT '',
`city` string COMMENT 'sip',
`obj_name` string COMMENT '',
`organization_name` string COMMENT '')
PARTITIONED BY (`ts` string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
LOCATION 'hdfs://10.0.1.43/user/hive/warehouse/trojan_controller';

加载数据：

load data local inpath '/data/cncert/@Bot_or_Trojan@#917MT@_C_IP_@20161230000000.txt' overwrite into table trojan_controller PARTITION(ts='20161230');

删除表：

drop table trojan_controller;

把没添加进partition的数据，都增加对应的partition。同步源数据信息metadata：

msck repair table trojan_controller;

数据定义 - DDL

（1）建表（CREATE）的语法如下：
```
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
  [(col_name data_type [COMMENT col_comment], ...)] 
  [COMMENT table_comment] 
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
  [CLUSTERED BY (col_name, col_name, ...) 
  [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
  [ROW FORMAT row_format] 
  [STORED AS file_format] 
  [LOCATION hdfs_path]
```
上面的一些关键字解释：
- CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常
- EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）
- LIKE 允许用户复制现有的表结构，但是不复制数据
- COMMENT 可以为表与字段增加描述
- ROW FORMAT 用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。
- STORED AS 如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。
（2）建表（CREATE）
- 创建普通表
- 创建外部表
- 创建分区表
- 创建 Bucket 表
- 创建简单表：
```
hive> CREATE TABLE shiyanlou1(
        id        INT,
        email     STRING,
        name      STRING);
```
- 创建外部表：
```
hive> CREATE EXTERNAL TABLE shiyanlou2(
        id             INT,
        email          STRING,
        name           STRING
        )
        LOCATION '/home/hive/external';
```
和简单表相比较，可以发现外部表多了external的关键字说明以及LOCATION指定外部表存放的路径（如果没有LOCATION，Hive将在HDFS上的/user/hive/warehouse文件夹下以外部表的表名创建一个文件夹，并将属于这个表的数据存放在这里）。
- 创建分区表：
为了避免Hive在查询时扫描全表，增加没有必要的消耗，因此在建表时加入partition。
```
hive> CREATE TABLE shiyanlou3(
        id             INT,
        email          STRING,
        name           STRING
        )
        PARTITIONED BY(sign_date STRING,age INT);
```
可以看到，我们使用了sign_date 和age 两个字段作为分区列。但是，我们必须先创建这两个分区，才能够使用。
```
hive> ALTER TABLE shiyanlou3 add partition(sign_date='20160720',age=20);
```
- 创建 Bucket 表：
Hive中的table可以拆分成partiton，table和partition又可以进一步通过CLUSTERED BY分成更小的文件bucket，这样使得多个文件可以在map上同时启动。

首先需要设置环境变量

hive>set hive.enforce.bucketing = true
```
hive> CREATE TABLE shiyanlou4(
        id             INT,
        email          STRING,
        name           STRING,
        age            INT
        )
        PARTITIONED BY(sign_date STRING)
        CLUSTERED BY(id)SORTED BY(age) INTO 5 BUCKETS
        ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
```
（3）修改表结构
- 重命名表
- 增加、删除分区
- 增加、更新列
- 修改列的名字、类型、位置、注释
- 增加表的元数据信息
- ...
- 复制一个空表
```
CREATE TABLE empty
LIKE shiyanlou3;
```
- 删除表
```
DROP TABLE [IF EXISTS] table_name [RESTRICT|CASCAD];
```
- 重命名表
```
ALTER TABLE table_name RENAME TO new_table_name
```
- 增加、删除分区
```
# 增加
ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...
partition_spec:
  : PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)

# 删除
ALTER TABLE table_name DROP
    partition_spec, partition_spec,...
```
- 增加、更新列
```
# ADD 是代表新增一字段，字段位置在所有列后面(partition列前)
# REPLACE 则是表示替换表中所有字段。
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
```
- 修改列的名字、类型、位置、注释
```
# 这个命令可以允许改变列名、数据类型、注释、列位置或者它们的任意组合
ALTER TABLE table_name CHANGE [COLUMN]
col_old_name col_new_name column_type
[COMMENT col_comment]
[FIRST|AFTER column_name]
```
- 增加表的元数据信息
```
# 用户可以用这个命令向表中增加元数据信息 metadata
ALTER TABLE table_name SET TBLPROPERTIES table_properties table_properties:
    : (property_name = property_value, ...)
```
- 改变文件格式和组织
```
ALTER TABLE table_name SET FILEFORMAT file_format
ALTER TABLE table_name CLUSTERED BY(col_name, col_name, ...) 
    [SORTED BY(col_name, ...)] INTO num_buckets BUCKETS
```
- 创建、删除视图
```
# 创建视图
CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], ...) ][COMMENT view_comment][TBLPROPERTIES (property_name = property_value, ...)]
AS SELECT ...

# 删除视图
DROP VIEW view_name
```
- 创建、删除函数
```
# 创建函数
CREATE TEMPORARY FUNCTION function_name AS class_name

# 删除函数
DROP TEMPORARY FUNCTION function_name
```
- 展示、描述语句
```
# 显示 表
show tables;

# 显示 数据库
show databases;

# 显示 函数
show functions;

# 描述 表/列
describe [EXTENDED] table_name[DOT col_name]
```
数据管理操作 - DML

Hive不支持insert语句进行逐条插入，也不支持update修改数据。首先需要在Hive中建好表，再使用load语句将数据导入，数据一旦导入就不可以修改。

（1）加载数据到Hive表

Hive加载数据到表中时，不做任何转换。加载操作目前只是单纯地复制/移动数据文件到Hive表格的对应位置。
```
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE]
    INTO TABLE tablename
    [PARTITION (partcol1=val1, partcol2=val2 ...)]
```
- filepath
  - 相对路径，例如：project/data1
  - 绝对路径，例如： /user/hive/project/data1
  - 包含模式的完整 URI，例如：hdfs://namenode:9000/user/hive/project/data1
- LOCAL 关键字
  - 指定了LOCAL 即本地 load 命令会去查找本地文件系统中的 filepath. 如果发现是相对路径，则路径会被解释为相对于当前用户的当前路径。用户也可以为本地文件指定一个完整的 URI，比如：file:///user/hive/project/data. 此时 load 命令会将 filepath 中的文件复制到目标文件系统中。目标文件系统由表的位置属性决定。被复制的数据文件移动到表的数据对应的位置。
  - 没有指定LOCAL 如果 filepath 指向的是一个完整的 URI，hive 会直接使用这个 URI. 否则如果没有指定 schema 或者 authority，Hive 会使用在 hadoop 配置文件中定义的 schema 和 authority，fs.default.name 指定了 Namenode 的 URI. 如果路径不是绝对的，Hive 相对于 /user/ 进行解释。 Hive 会将 filepath 中指定的文件内容移动到 table （或者 partition）所指定的路径中。
- OVERWRITE
  - 使用 OVERWRITE 关键字，目标表（或者分区）中的内容（如果有）会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。
- 实例：
```
hive> LOAD DATA LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE pokes;
```
（2）将查询结果插入Hive表
```
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement
```
- 多插入模式
```
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1

INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2
```
- 自动分区模式
```
INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement
```
（3）将查询结果写入HDFS文件系统
```
INSERT OVERWRITE [LOCAL] DIRECTORY directory1
  [ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)
  SELECT ... FROM ...
```
- 多插入模式
```
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
```
数据写入文件系统时会进行文本序列化，且每列用 ^A 来区分，换行。如果任何一列不是原始类型，那么这些将会被序列化为 JSON 格式。

（4）从SQL获取数据插入Hive表
```
INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]
```
value_row 应用为（value [, value ...]），其中value为null或是任意有效的SQL 语句。

数据查询操作 - DQL

SQL操作：
- 基本的 Select 操作
- 基于 Partition 的查询
- HAVING 查询
- Join 查询
（1）基本的 Select 操作
```
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
    FROM table_reference
    [WHERE where_condition]
    [GROUP BY col_list]
    [   CLUSTER BY col_list
      | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
    ]
    [LIMIT number]
```
- 一个SELECT语句可以是UNION查询的一部分，也可以是另一个查询的子查询。
- table_reference表示的是所查询的表。
- 表名和列名大小写敏感。
- 使用 ALL 和 DISTINCT 选项区分对重复记录的处理。默认是 ALL，表示查询所有记录。DISTINCT 表示去掉重复的记录；
- LIMIT number可以限制查询的记录数
- 简单的查询例子： SELECT * FROM shiyanlou
（2）基于 Partition 的查询

一般 SELECT 查询会扫描整个表，使用 PARTITIONED BY 子句建表，查询就可以利用分区剪枝（input pruning）的特性。

Hive 当前的分区剪枝，只有分区断言出现在离 FROM 子句最近的那个 WHERE 子句中，才会启用分区剪枝。

下面是两个例子,page_view根据date分区：
```
SELECT page_views.*
FROM page_views
WHERE page_views.date >= '2008-03-01' AND page_views.date <= '2008-03-31'
```
```
SELECT page_views.* 
FROM page_views JOIN dim_users
ON (page_views.user_id = dim_users.id AND page_views.date >= '2008-03-01' AND page_views.date <= '2008-03-31')
```
（3）HAVING 查询

Hive在0.7.0版本中添加了对HAVING语句的支持，在旧版本的Hive中，使用一个子查询也可以实现相同的效果。
```
SELECT col1 FROM t1 GROUP BY col1 HAVING SUM(col2) > 10
```
等价于
```
SELECT col1 FROM (SELECT col1, SUM(col2) AS col2sum FROM t1 GROUP BY col1) t2 WHERE t2.col2sum > 10
```
（4）Join 查询

Join 的语法如下：
```
join_table:
    table_reference JOIN table_factor [join_condition]
  | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
  | table_reference LEFT SEMI JOIN table_reference join_condition
  | table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)

table_reference:
    table_factor
  | join_table

table_factor:
    tbl_name [alias]
  | table_subquery alias
  | ( table_references )

join_condition:
    ON equality_expression ( AND equality_expression )*

equality_expression:
    expression = expression
```
- hive 只支持等连接（equality joins）、外连接（outer joins）、左半连接（left semi joins）。hive 不支持非相等的 join 条件，因为它很难在 map/reduce job 中实现这样的条件。而且，hive 可以 join 两个以上的表。
参考：

https://www.shiyanlou.com/courses/38/labs/772/document

https://cwiki.apache.org/confluence/display/Hive/LanguageManual
相关阅读:
css3的::selection属性
 css3的apprearance属性（转）
CSS3的background-size
DNS与获取
 AMD规范
 网站的个性图标
 webapp之路--之ios上图标
 webapp之路--之必备知识
 用Js的eval解析JSON中的注意点
 CSS 盒模型
原文地址：https://www.cnblogs.com/libin2015/p/7382828.html

实例：

数据定义 - DDL

数据管理操作 - DML

数据查询操作 - DQL