Hive是一个数据仓库基础的应用工具，在Hadoop中用来处理结构化数据，它架构在Hadoop之上，通过SQL来对数据进行操作。

Hive 查询操作过程严格遵守Hadoop MapReduce 的作业执行模型，Hive 将用户的Hive SQL 语句通过解释器转换为MapReduce 作业提交到Hadoop 集群上，Hadoop 监控作业执行过程，然后返回作业执行结果给用户。Hive 并非为联机事务处理而设计，Hive 并不提供实时的查询和基于行级的数据更新操作。Hive 的最佳使用场合是大数据集的批处理作业。

下面总结一下Hive操作常用的一些SQL语法：

"[ ]"括起来的代表我们可以写也可以不写的语句。

创建数据库

CREATEname;

  • 显示查看操作命令

show tables; --显示表

show--显示数据库

showtable_name; --显示表名为table_name的表的所有分区

show functions--显示所有函数

describetable_name--查看表中字段

DDL(Data Defination Language)

数据库定义语言

  • 创建表结构

CREATEEXTERNAL] TABLEIF NOT EXISTS] table_name

[(col_name data_type [COMMENT col_comment], ...)]

[COMMENT table_comment]

[PARTITIONED BYCOMMENT col_comment], ...)]

[CLUSTERED BY (col_name, col_name, ...)

[SORTED BYASC|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 设置行数据分割格式

DELIMITED [FIELDS TERMINATED BYchar] [COLLECTION ITEMS TERMINATED BYchar]

MAPBYchar] [LINES TERMINATED BYchar]

WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

   

  • STORED AS

SEQUENCEFILE

| TEXTFILE

| RCFILE

| INPUTFORMAT input_format_classname OUTPUTFORMAT

output_format_classname

如果文件数据是纯文本，可以使用AS TEXTFILE。

如果数据需要压缩，使用AS SEQUENCE 。

   

创建简单表：

CREATE TABLEnameINT);

   

创建外部表:

CREATE EXTERNAL TABLEINT, userid BIGINT,

page_url STRING, referrer_url STRING,

COMMENT'IP Address of the User',

COMMENT'country of origination')

COMMENT'这里写表的描述信息'

ROWBY'\054'

AS TEXTFILE

LOCATION'<hdfs_location>';

   

创建分区表：

CREATE TABLEINT, userid BIGINT,

page_url STRING, referrer_url STRING,

COMMENT'IP Address of the User')

COMMENT'This is the page view table'

BY(date STRING, pos STRING)

ROW\t'

BY'\n'

STORED AS SEQUENCEFILE;

创建分桶表：

CREATE TABLEINT, userid BIGINT,

page_url STRING, referrer_url STRING,

COMMENT'IP Address of the User')

COMMENT'This is the page view table'

BY(date STRING, pos STRING)

BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS

ROW\t'

BY'\n'

STORED AS SEQUENCEFILE;

创建带索引字段的表：

CREATE TABLEINT, bar STRING) PARTITIONED BY (dindex STRING);

复制一个空表：

CREATE TABLE empty_key_value_store

LIKE key_value_store;

显示所有表：

SHOW TABLES;

按正则表达式显示表：

SHOW TABLES'.*s';

表中添加一个字段：

ALTER TABLEADD COLUMNSINT);

添加一个字段并为其添加注释：

ALTER TABLEADD COLUMNSINTCOMMENT'a comment');

删除列：

ALTER TABLEREPLACE COLUMNS(idBIGINT, name STRING);

更改表名：

ALTER TABLERENAME TO new_events;

增加、删除分区

--增加：

ALTER TABLE table_name ADDIF NOT EXISTS] partition_spec [ LOCATION'location1'LOCATION'location2' ] ...

partition_spec:

PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)

  

--删除：

ALTER TABLE table_name DROP partition_spec, partition_spec,...

   

改变表的文件格式与组织：

ALTER TABLE table_name SET FILEFORMAT file_format

ALTER TABLE table_nameBY(userid) SORTED BY(viewTime) INTO--这个命令修改了表的物理存储属性

   

创建和删除视图：

--创建视图：

CREATE VIEWIF NOT EXISTS] view_name [ (column_nameCOMMENTCOMMENTAS SELECT；

  

--删除视图：

DROP VIEW view_name；

   

DML（Data manipulation language）

数据操作语言，主要是数据库增删改三种操作，DML包括：INSERT插入、UPDATE新、DELETE删除。

   

向数据表内加载文件：

LOAD DATALOCAL] INPATH 'filepath'INTO TABLEPARTITION (partcol1=val1, partcol2=val2 ...)]

--load操作只是单纯的复制/移动操作，将数据文件移动到Hive表对应的位置。

--加载本地

LOAD DATA LOCAL'./examples/files/kv1.txt'INTO TABLE pokes;

  

--加载HDFS数据，同时给定分区信息

LOAD DATA'/user/myname/kv2.txt'INTO TABLEPARTITION'2008-08-15');

   

将查询结果插入到Hive表：

INSERTTABLEPARTITIONFROM from_statement;

  

--多插入模式：

FROM from_statement

INSERTTABLEPARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1

[INSERTTABLEPARTITION ...] select_statement2] ...

  

--自动分区模式

INSERTTABLEPARTITIONFROM from_statement;

   

将查询结果插入到HDFS文件系统中：

INSERTLOCAL] DIRECTORY directory1 SELECTFROM ...

FROM from_statement

INSERTLOCAL] DIRECTORY directory1 select_statement1

INSERTLOCAL] DIRECTORY directory2 select_statement2]

   

INSERT INTO

INSERT INTO TABLEPARTITIONFROM from_statement;

insert overwrite和insert into的区别：

  • insert overwrite 会覆盖已经存在的数据，假如原始表使用overwrite 上述的数据，先现将原始表的数据remove，再插入新数据。
  • insert into 只是简单的插入，不考虑原始表的数据，直接追加到表中。最后表的数据是原始数据和新插入数据。

   

DQL（data query language）数据查询语言 select操作

   

SELECT查询结构：

SELECTALLDISTINCT] select_expr, select_expr, ...

FROM table_reference

[WHERE where_condition]

[GROUP BYHAVING condition]]

[ CLUSTER BY col_list

BYBY| ORDER BY col_list]

]

[LIMITnumber]

   

  • 使用ALL和DISTINCT选项区分对重复记录的处理。默认是ALL，表示查询所有记录DISTINCT表示去掉重复的记录
  • Where 条件 类似我们传统SQL的where 条件
  • ORDER BY 全局排序，只有一个Reduce任务
  • SORT BY 只在本机做排序
  • LIMIT限制输出的个数和输出起始位置

   

将查询数据输出至目录：

INSERT'/tmp/hdfs_out'SELECTFROMWHERE'<DATE>';

将查询结果输出至本地目录：

INSERTLOCAL'/tmp/local_out'SELECTFROM pokes a;

   

将一个表的结果插入到另一个表：

FROMINSERTTABLESELECTcount(1) WHEREGROUP BY a.bar;

INSERTTABLESELECTcount(1) FROMWHEREGROUP BY a.bar;

JOIN

FROMJOINONINSERTTABLESELECT t1.bar, t1.foo, t2.foo;

   

将多表数据插入到同一表中

FROM src

INSERTTABLESELECTWHEREkey < 100

INSERTTABLESELECTkey, src.value WHEREkeyandkey < 200

INSERTTABLEPARTITION(ds='2008-04-08', hr='12') SELECTkey WHEREkeyandkey < 300

INSERTLOCAL'/tmp/dest4.out'SELECTvalue WHEREkey >= 300;

   

Hive 只支持等值连接（equality joins）、外连接（outer joins）和（left semi joins）。Hive 不支持所有非等值的连接，因为非等值连接非常难转化到 map/reduce 任务。

  • LEFT，RIGHT和FULL OUTER关键字用于处理join中空记录的情况
  • LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现
  • join 时，每次 map/reduce 任务的逻辑是这样的：reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统
  • 实际应用过程中应尽量使用小表join大表 join查询时应注意的点：

--只支持等值连接

SELECTFROMJOINONidid)

  

SELECTFROMJOIN b

ONidid AND a.department = b.department)

  

--可以 join 多个表

SELECTFROMJOIN b

ONkeyJOINONkey = b.key2)

  

--如果join中多个表的 join key 是同一个，则 join 会被转化为单个 map/reduce 任务

   

LEFT，RIGHT和FULL OUTER关键字

--左外连接

SELECTFROMLEFT OUTER JOINONkey=b.key)

--右外链接

SELECTFROMRIGHT OUTER JOINONkey=b.key)

--满外连接

SELECTFROMFULL OUTER JOINONkey=b.key)

   

LEFT SEMI JOIN关键字

--LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行

SELECTkey, a.value

FROM a

WHEREkey in

SELECTkey

FROM B);

--可以被写为：

SELECTkey, a.val

FROMLEFTJOINonkeykey)

   

UNION 与 UNION ALL

--用来合并多个select的查询结果，需要保证select中字段须一致

select_statement UNION ALLUNION ALL select_statement ...

--UNION 和 UNION ALL的区别

--UNION只会查询到两个表中不同的数据，相同的部分不会被查出

--UNION ALL会把两个表的所有数据都查询出