Hadoop Hive sql 语法详解

352262

　　Hive 是基于Hadoop 构建的一套数据仓库分析系统，它提供了丰富的SQL查询方式来分析存储在Hadoop 分布式文件系统中的数据，可以将结构化的数据文件映射为一张数据库表，并提供完整的SQL查询功能，可以将SQL语句转换为MapReduce任务进行运行，通过自己的SQL 去查询分析需要的内容，这套SQL 简称Hive SQL，使不熟悉mapreduce 的用户很方便的利用SQL 语言查询，汇总，分析数据。而mapreduce开发人员可以把己写的mapper 和reducer 作为插件来支持Hive 做更复杂的数据分析。
　　它与关系型数据库的SQL 略有不同，但支持了绝大多数的语句如DDL、DML 以及常见的聚合函数、连接查询、条件查询。HIVE不适合用于联机，也不提供实时查询功能。它最适合应用在基于大量不可变数据的批处理作业。
　　HIVE的特点：可伸缩（在Hadoop的集群上动态的添加设备），可扩展，容错，输入格式的松散耦合。
　　Hive 的官方文档中对查询语言有了很详细的描述，请参考：http://wiki.apache.org/hadoop/Hive/LanguageManual ，本文的内容大部分翻译自该页面，期间加入了一些在使用过程中需要注意到的事项。
1. DDL 操作
建表：

• 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

• DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
  
• [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
  
• | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
  

　　用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。
　　STORED AS

• SEQUENCEFILE
  
• | TEXTFILE
  
• | RCFILE
  
• | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
  

　　如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。

• 创建简单表：
  

• hive> CREATE TABLE pokes (foo INT, bar STRING);
  

• 创建外部表：
  

• CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,
  
• page_url STRING, referrer_url STRING,
  
• ip STRING COMMENT 'IP Address of the User',
  
• country STRING COMMENT 'country of origination')
  
• COMMENT 'This is the staging page view table'
  
• ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'
  
• STORED AS TEXTFILE
  
• LOCATION '<hdfs_location>';
  

• 建分区表
  

• CREATE TABLE par_table(viewTime INT, userid BIGINT,
  
• page_url STRING, referrer_url STRING,
  
• ip STRING COMMENT 'IP Address of the User')
  
• COMMENT 'This is the page view table'
  
• PARTITIONED BY(date STRING, pos STRING)
  
• ROW FORMAT DELIMITED ‘\t’
  
• FIELDS TERMINATED BY '\n'
  
• STORED AS SEQUENCEFILE;
  

• 建Bucket表
  

• CREATE TABLE par_table(viewTime INT, userid BIGINT,
  
• page_url STRING, referrer_url STRING,
  
• ip STRING COMMENT 'IP Address of the User')
  
• COMMENT 'This is the page view table'
  
• PARTITIONED BY(date STRING, pos STRING)
  
• CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
  
• ROW FORMAT DELIMITED ‘\t’
  
• FIELDS TERMINATED BY '\n'
  
• STORED AS SEQUENCEFILE;
  

• 创建表并创建索引字段ds
  

• hive> CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);
  

• 复制一个空表
  

• CREATE TABLE empty_key_value_store
  
• LIKE key_value_store;
  

　　例子

• create table user_info (user_id int, cid string, ckid string, username string)
  
• row format delimited
  
• 
  
• fields terminated by '\t'
  
• 
  
• lines terminated by '\n';
  

　　导入数据表的数据格式是：字段之间是tab键分割，行之间是断行。
　　及要我们的文件内容格式：

• 100636 100890 c5c86f4cddc15eb7 yyyvybtvt
  
• 100612 100865 97cc70d411c18b6f gyvcycy
  
• 100078 100087 ecd6026a15ffddf5 qa000100
  

• 显示所有表：
  

• hive> SHOW TABLES;
  

　　按正条件（正则表达式）显示表，

• hive> SHOW TABLES '.*s';
  

修改表结构

• 表添加一列 ：
  

• hive>>
  

• 添加一列并增加列字段注释
  

• hive>>
  

• 更改表名：
  

• hive>>
  

• 删除列：
  

• hive> DROP TABLE pokes;
  

　　增加、删除分区

• 增加
  

• 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,...
  

• 重命名表
  

• ALTER TABLE table_name RENAME TO new_table_name
  

• 修改列的名字、类型、位置、注释：
  

• ALTER TABLE table_name CHANGE [COLUMN] col_old_name
  
• col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
  

　　这个命令可以允许改变列名、数据类型、注释、列位置或者它们的任意组合

• 表添加一列 ：
  

• hive>>
  

• 添加一列并增加列字段注释
  

• hive>>
  

• 增加/更新列
  

• ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
  

　　ADD是代表新增一字段，字段位置在所有列后面(partition列前)
　　REPLACE则是表示替换表中所有字段。

• 增加表的元数据信息
  

• ALTER TABLE table_name SET TBLPROPERTIES table_properties table_properties:
  
• :[property_name = property_value…..]
  

　　用户可以用这个命令向表中增加metadata

• 改变表文件格式与组织
  

• ALTER TABLE table_name SET FILEFORMAT file_format
  
• ?ALTER TABLE table_name CLUSTERED BY(userid) SORTED BY(viewTime) 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
  

• 增加视图
  

　　如果没有提供表名，视图列的名字将由定义的SELECT表达式自动生成
　　如果修改基本表的属性，视图中不会体现，无效查询将会失败
　　视图是只读的，不能用LOAD/INSERT/ALTER

• 删除视图
  

• DROP VIEW view_name
  

• 创建数据库
  

• CREATE DATABASE name
  

• 显示命令
  

• show tables;
  
• ?show databases;
  
• ?show partitions ;
  
• ?show functions
  
• ?describe extended table_name dot col_name
  

2. DML 操作:元数据存储
　　hive不支持用insert语句一条一条的进行插入操作，也不支持update操作。数据是以load的方式加载到建立好的表中。数据一旦导入就不可以修改。
　　DML包括：INSERT插入、UPDATE更新、DELETE删除

• 向数据表内加载文件
  

• LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename
  
• [PARTITION (partcol1=val1, partcol2=val2 ...)]
  

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

• 　　filepath
  
  
  
  • 相对路径，例如：project/data1
    
  • 绝对路径，例如： /user/hive/project/data1
    
  
  

　　包含模式的完整 URI，例如：hdfs://namenode:9000/user/hive/project/data1
　　例如：

• hive> LOAD DATA LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE pokes;
  

• 　　加载本地数据，同时给定分区信息
  　　加载的目标可以是一个表或者分区。如果表包含分区，必须指定每一个分区的分区名
  　　filepath 可以引用一个文件（这种情况下，Hive 会将文件移动到表所对应的目录中）或者是一个目录（在这种情况下，Hive 会将目录中的所有文件移动至表所对应的目录中）
  
• 　　LOCAL关键字
  　　指定了LOCAL，即本地
  　　load 命令会去查找本地文件系统中的 filepath。如果发现是相对路径，则路径会被解释为相对于当前用户的当前路径。用户也可以为本地文件指定一个完整的 URI，比如：file:///user/hive/project/data1.
  

　　load 命令会将 filepath 中的文件复制到目标文件系统中。目标文件系统由表的位置属性决定。被复制的数据文件移动到表的数据对应的位置
3. DQL 操作:数据查询SQL
3.1 基本的Select 操作

• SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  
• FROM table_reference
  
• [WHERE where_condition]
  
• [GROUP BY col_list [HAVING condition]]
  
• [ CLUSTER BY col_list
  
• | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
  
• ]
  
• [LIMIT number]
  

　　使用ALL和DISTINCT选项区分对重复记录的处理。默认是ALL，表示查询所有记录。DISTINCT表示去掉重复的记录

• Where 条件
  

　　类似我们传统SQL的where 条件
　　目前支持 AND,OR ,0.9版本支持between

• 　　IN, NOT IN
  
• 　　不支持EXIST ,NOT EXIST
  
• 　　ORDER BY与SORT BY的不同
  
• 　　ORDER BY 全局排序，只有一个Reduce任务
  
• 　　SORT BY 只在本机做排序
  
• 　　Limit
  

　　Limit 可以限制查询的记录数

• SELECT * FROM t1 LIMIT 5
  

• 实现Top k 查询
  

　　下面的查询语句查询销售记录最大的 5 个销售代表。

• SET mapred.reduce.tasks = 1
  
• SELECT * FROM test SORT BY amount DESC LIMIT 5
  
• REGEX Column Specification
  

　　SELECT 语句可以使用正则表达式做列选择，下面的语句查询除了 ds 和 hr 之外的所有列：

• SELECT `(ds|hr)?+.+` FROM test
  

4. 从SQL到HiveQL应转变的习惯
1、Hive不支持等值连接
　　SQL中对两表内联可以写成：

• select * from dual a,dual b where a.key = b.key;
  

　　Hive中应为

• select * from dual a join dual b on a.key = b.key;
  

　　而不是传统的格式：

• SELECT t1.a1 as c1, t2.b1 as c2FROM t1, t2
  
• WHERE t1.a2 = t2.b2
  

2、分号字符
　　分号是SQL语句结束标记，在HiveQL中也是，但是在HiveQL中，对分号的识别没有那么智慧，例如：

• select concat(key,concat(';',key)) from dual;
  

　　但HiveQL在解析语句时提示：

• FAILED: Parse Error: line 0:-1 mismatched input '<EOF>' expecting ) in function specification
  

　　解决的办法是，使用分号的八进制的ASCII码进行转义，那么上述语句应写成：

• select concat(key,concat('\073',key)) from dual;
  

3、IS [NOT] NULL
　　SQL中null代表空值, 值得警惕的是, 在HiveQL中String类型的字段若是空(empty)字符串, 即长度为0, 那么对它进行IS NULL的判断结果是False.
4、Hive不支持将数据插入现有的表或分区中，仅支持覆盖重写整个表，示例如下：

• INSERT OVERWRITE TABLE t1
  
• SELECT * FROM t2;
  

　　from: http://blog.leanote.com/post/mouto/Untitled-553318f738f41124b200001b-38