`
samuschen
  • 浏览: 405498 次
  • 性别: Icon_minigender_2
  • 来自: 北京
社区版块
存档分类
最新评论

Hive QL

    博客分类:
  • hive
阅读更多

Hive 的官方文档中对查询语言有了很详细的描述,请参考:http://wiki.apache.org/hadoop/Hive/LanguageManual ,本文的内容大部分翻译自该页面,期间加入了一些在使用过程中需要注意到的事项。

Create Table

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),Hive 创建内部表时,会将数据移动到数据仓库指向的路径;若创建外部表,仅记录数据所在的路径,不对数据的位置做任何改变。在删除表的时候,内部表的元数据和数 据会被一起删除,而外部表只删除元数据,不删除数据。

LIKE 允许用户复制现有的表结构,但是不复制数据。

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

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

有分区的表可以在创建的时候使用 PARTITIONED BY 语句。一个表可以拥有一个或者多个分区,每一个分区单独存在一个目录下。而且,表和分区都可以对某个列进行 CLUSTERED BY 操作,将若干个列放入一个桶(bucket)中。也可以利用SORT BY 对数据进行排序。这样可以为特定应用提高性能。

表名和列名不区分大小写,SerDe 和属性名区分大小写。表和列的注释是字符串。

Drop Table

删除一个内部表的同时会同时删除表的元数据和数据。删除一个外部表,只删除元数据而保留数据。

Alter Table

Alter table 语句允许用户改变现有表的结构。用户可以增加列/分区,改变serde,增加表和 serde 熟悉,表本身重命名。

Add Partitions

ALTER TABLE table_name ADD
  partition_spec [ LOCATION 'location1' ]
  partition_spec [ LOCATION 'location2' ] ...

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

用户可以用 ALTER TABLE ADD PARTITION 来向一个表中增加分区。当分区名是字符串时加引号。

  ALTER TABLE page_view ADD
    PARTITION (dt='2008-08-08', country='us')
      location '/path/to/us/part080808'
    PARTITION (dt='2008-08-09', country='us')
      location '/path/to/us/part080809';

DROP PARTITION

ALTER TABLE table_name DROP
    partition_spec, partition_spec,...

用户可以用 ALTER TABLE DROP PARTITION 来删除分区。分区的元数据和数据将被一并删除。

ALTER TABLE page_view
    DROP PARTITION (dt='2008-08-08', country='us');

RENAME TABLE

ALTER TABLE table_name RENAME TO new_table_name

这个命令可以让用户为表更名。数据所在的位置和分区名并不改变。换而言之,老的表名并未“释放”,对老表的更改会改变新表的数据。

Change Column Name/Type/Position/Comment

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

这个命令可以允许用户修改一个列的名称、数据类型、注释或者位置。

比如:

CREATE TABLE test_change (a int, b int, c int);

ALTER TABLE test_change CHANGE a a1 INT; 将 a 列的名字改为 a1.

ALTER TABLE test_change CHANGE a a1 STRING AFTER b; 将 a 列的名字改为 a1,a 列的数据类型改为 string,并将它放置在列 b 之后。新的表结构为: b int, a1 string, c int.

ALTER TABLE test_change CHANGE b b1 INT FIRST; 会将 b 列的名字修改为 b1, 并将它放在第一列。新表的结构为: b1 int, a string, c int.

注意:对列的改变只会修改 Hive 的元数据,而不会改变实际数据。用户应该确定保证元数据定义和实际数据结构的一致性。

Add/Replace Columns

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

ADD COLUMNS 允许用户在当前列的末尾增加新的列,但是在分区列之前。

REPLACE COLUMNS 删除以后的列,加入新的列。只有在使用 native 的 SerDE(DynamicSerDe or MetadataTypeColumnsetSerDe)的时候才可以这么做。

Alter Table Properties

ALTER TABLE table_name SET TBLPROPERTIES table_properties
table_properties:
  : (property_name = property_value, property_name = property_value, ... )

用户可以用这个命令向表中增加 metadata,目前 last_modified_user,last_modified_time 属性都是由 Hive 自动管理的。用户可以向列表中增加自己的属性。可以使用 DESCRIBE EXTENDED TABLE 来获得这些信息。

Add Serde Properties

ALTER TABLE table_name
    SET SERDE serde_class_name
    [WITH SERDEPROPERTIES serde_properties]

ALTER TABLE table_name
    SET SERDEPROPERTIES serde_properties

serde_properties:
  : (property_name = property_value,
    property_name = property_value, ... )

这个命令允许用户向 SerDe 对象增加用户定义的元数据。Hive 为了序列化和反序列化数据,将会初始化 SerDe 属性,并将属性传给表的 SerDe。如此,用户可以为自定义的 SerDe 存储属性。

Alter Table File Format and Organization

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

这个命令修改了表的物理存储属性。

Loading files into table

当数据被加载至表中时,不会对数据进行任何转换。Load 操作只是将数据复制/移动至 Hive 表对应的位置。

Syntax:

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

Synopsis:

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

  • filepath 可以是:
    • 相对路径,例如:project/data1
    • 绝对路径,例如: /user/hive/project/data1
    • 包含模式的完整 URI,例如:hdfs://namenode:9000/user/hive/project/data1
  • 加载的目标可以是一个表或者分区。如果表包含分区,必须指定每一个分区的分区名。
  • filepath 可以引用一个文件(这种情况下,Hive 会将文件移动到表所对应的目录中)或者是一个目录(在这种情况下,Hive 会将目录中的所有文件移动至表所对应的目录中)。
  • 如果指定了 LOCAL,那么:
    • load 命令会去查找本地文件系统中的 filepath。如果发现是相对路径,则路径会被解释为相对于当前用户的当前路径。用户也可以为本地文件指定一个完整的 URI,比如:file:///user/hive/project/data1.
    • 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 关键字,则目标表(或者分区)中的内容(如果有)会被删除,然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。
  • 如果目标表(分区)已经有一个文件,并且文件名和 filepath 中的文件名冲突,那么现有的文件会被新文件所替代。

SELECT

Syntax

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 col_list]
  ]
[LIMIT number]
  • 一个SELECT语句可以是一个union查询或一个子查询的一部分。
  • table_reference是查询的输入,可以是一个普通表、一个视图、一个join或一个子查询
  • 简单查询。例如,下面这一语句从t1表中查询所有列的信息。
SELECT * FROM t1

WHERE Clause

where condition 是一个布尔表达式。例如,下面的查询语句只返回销售记录大于 10,且归属地属于美国的销售代表。Hive 不支持在WHERE 子句中的 IN,EXIST 或子查询。

SELECT * FROM sales WHERE amount > 10 AND region = "US"

ALL and DISTINCT Clauses

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

hive> SELECT col1, col2 FROM t1
    1 3
    1 3
    1 4
    2 5
hive> SELECT DISTINCT col1, col2 FROM t1
    1 3
    1 4
    2 5
hive> SELECT DISTINCT col1 FROM t1
    1
    2

基于Partition的查询

一般 SELECT 查询会扫描整个表(除非是为了抽样查询)。但是如果一个表使用 PARTITIONED BY 子句建表,查询就可以利用分区剪枝(input pruning)的特性,只扫描一个表中它关心的那一部分。Hive 当前的实现是,只有分区断言出现在离 FROM 子句最近的那个WHERE 子句中,才会启用分区剪枝。例如,如果 page_views 表使用 date 列分区,以下语句只会读取分区为‘2008-03-01’的数据。

 SELECT page_views.*
    FROM page_views
    WHERE page_views.date >= '2008-03-01'
      AND page_views.date <= '2008-03-31';

HAVING Clause

Hive 现在不支持 HAVING 子句。可以将 HAVING 子句转化为一个字查询,例如:

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

LIMIT Clause

Limit 可以限制查询的记录数。查询的结果是随机选择的。下面的查询语句从 t1 表中随机查询5条记录:

SELECT * FROM t1 LIMIT 5

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

SET mapred.reduce.tasks = 1
  SELECT * FROM sales SORT BY amount DESC LIMIT 5

REGEX Column Specification

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

SELECT `(ds|hr)?+.+` FROM sales

Join

Syntax

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:
    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 不支持所有非等值的连接,因为非等值连接非常难转化到 map/reduce 任务。另外,Hive 支持多于 2 个表的连接。

写 join 查询时,需要注意几个关键点:
1. 只支持等值join,例如:

  SELECT a.* FROM a JOIN b ON (a.id = b.id)
  SELECT a.* FROM a JOIN b
    ON (a.id = b.id AND a.department = b.department)

是正确的,然而:

  SELECT a.* FROM a JOIN b ON (a.id  b.id)

是错误的。

2. 可以 join 多于 2 个表,例如

  SELECT a.val, b.val, c.val FROM a JOIN b
    ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

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

  SELECT a.val, b.val, c.val FROM a JOIN b
    ON (a.key = b.key1) JOIN c
    ON (c.key = b.key1)

被转化为单个 map/reduce 任务,因为 join 中只使用了 b.key1 作为 join key。

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1)
  JOIN c ON (c.key = b.key2)

而这一 join 被转化为 2 个 map/reduce 任务。因为 b.key1 用于第一次 join 条件,而 b.key2 用于第二次 join。

join 时,每次 map/reduce 任务的逻辑是这样的:reducer 会缓存 join 序列中除了最后一个表的所有表的记录,再通过最后一个表将结果序列化到文件系统。这一实现有助于在 reduce 端减少内存的使用量。实践中,应该把最大的那个表写在最后(否则会因为缓存浪费大量内存) 。例如:

 SELECT a.val, b.val, c.val FROM a
    JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

所有表都使用同一个 join key(使用 1 次 map/reduce 任务计算)。Reduce 端会缓存 a 表和 b 表的记录,然后每次取得一个 c 表的记录就计算一次 join 结果 ,类似的还有:

  SELECT a.val, b.val, c.val FROM a
    JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

这里用了 2 次 map/reduce 任务。第一次缓存 a 表,用 b 表序列化;第二次缓存第一次 map/reduce 任务的结果,然后用 c 表序列化。

LEFT,RIGHT 和 FULL OUTER 关键字用于处理 join 中空记录的情况,例如:

  SELECT a.val, b.val FROM a LEFT OUTER
    JOIN b ON (a.key=b.key)

对应所有 a 表中的记录都有一条记录输出。输出的结果应该是 a.val, b.val,当 a.key=b.key 时,而当 b.key 中找不到等值的 a.key 记录时也会输出 a.val, NULL。“FROM a LEFT OUTER JOIN b”这句一定要写在同一行——意思是 a 表在 b 表的左边 ,所以 a 表中的所有记录都被保留了;“a RIGHT OUTER JOIN b”会保留所有 b 表的记录。OUTER JOIN 语义应该是遵循标准 SQL spec的。

Join 发生在 WHERE 子句之前 。如果你想限制 join 的输出,应该在 WHERE 子句中写过滤条件——或是在 join 子句中写。这里面一个容易混淆的问题是表分区的情况:

  SELECT a.val, b.val FROM a
  LEFT OUTER JOIN b ON (a.key=b.key)
  WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

会 join a 表到 b 表(OUTER JOIN),列出 a.val 和 b.val 的记录。WHERE 从句中可以使用其他列作为过滤条件。但是,如前所述,如果 b 表中找不到对应 a 表的记录,b 表的所有列都会列出 NULL,包括 ds 列 。也就是说,join 会过滤 b 表中不能找到匹配 a 表 join key 的所有记录。这样的话,LEFT OUTER 就使得查询结果与 WHERE 子句无关了。解决的办法是在 OUTER JOIN 时使用以下语法:

  SELECT a.val, b.val FROM a LEFT OUTER JOIN b
  ON (a.key=b.key AND
      b.ds='2009-07-07' AND
      a.ds='2009-07-07')

这一查询的结果是预先在 join 阶段过滤过的,所以不会存在上述问题。这一逻辑也可以应用于 RIGHT 和 FULL 类型的 join 中。

Join 是不能交换位置的。无论是 LEFT 还是 RIGHT join,都是左连接的。

  SELECT a.val1, a.val2, b.val, c.val
  FROM a
  JOIN b ON (a.key = b.key)
  LEFT OUTER JOIN c ON (a.key = c.key)

先 join a 表到 b 表,丢弃掉所有 join key 中不匹配的记录,然后用这一中间结果和 c 表做 join。这一表述有一个不太明显的问题,就是当一个 key 在 a 表和 c 表都存在,但是 b 表中不存在的时候:整个记录在第一次 join,即 a JOIN b 的时候都被丢掉了(包括a.val1,a.val2和a.key),然后我们再和 c 表 join 的时候,如果 c.key 与 a.key 或 b.key 相等,就会得到这样的结果:NULL, NULL, NULL, c.val。

LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现。Hive 当前没有实现 IN/EXISTS 子查询,所以你可以用 LEFT SEMI JOIN 重写你的子查询语句。LEFT SEMI JOIN 的限制是, JOIN 子句中右边的表只能在 ON 子句中设置过滤条件,在 WHERE 子句、SELECT 子句或其他地方过滤都不行。

  SELECT a.key, a.value
  FROM a
  WHERE a.key in
   (SELECT b.key
    FROM B);

可以被重写为:

   SELECT a.key, a.val
   FROM a LEFT SEMI JOIN b on (a.key = b.key)
分享到:
评论

相关推荐

    Hive_QL详解1

    【Hive QL详解1】 Hive 是一种基于 Hadoop 的数据仓库工具,它允许用户使用 SQL-like 查询语言(Hive QL)对大规模数据集进行分析和处理。本篇文章将详细探讨Hive的几个关键方面,包括Hadoop计算框架的特性、优化...

    hive实验报告.docx

    【Hive实验报告】 实验报告主要涵盖了Hive的安装、配置以及基本操作,包括DDL(Data Definition Language)和DML(Data Manipulation Language)的使用,Hive与MySQL的交互,UDF(User Defined Function)的开发,...

    hive语法和常用函数[归纳].pdf

    Facebook的分析人员中很多工程师比较擅长SQL而不善于开发MapReduce程序,为此开发出Hive,并对比较熟悉SQL的工程师提供了一套新的SQL-like语言——Hive QL。 Hive SQL语言特别和MySQL语言很像,并提供了Hive QL的...

    Hive高级编程经验分享.pdf

    在大数据处理领域,Apache Hive是一个广泛使用的数据仓库工具,它允许用户通过SQL(Hive QL)查询语言来处理存储在分布式存储系统如HDFS上的大规模数据集。本篇文章将深入探讨Hive的组件、MapReduce的工作原理以及...

    hive语法和常用函数.pdf

    Hive 提供了一种 SQL-like 的语言,称为 Hive QL,用于查询和分析大规模数据集。 Hive 的架构和执行流程: 1. 用户提交的 Hive QL 语句最终被编译为 MapReduce 程序作为 Job 提交给 Hadoop 执行。 2. Hive 的数据...

    Hive大数据平台介绍.pdf

    Hive的出现解决了在处理非结构化数据时缺乏高效查询和管理机制的问题,它将无序的数据视为有序,通过提供SQL类似的查询语言(Hive QL)使得数据分析人员能够方便地对海量数据进行分析。 Hive的核心设计理念在于,它...

    hive开发指南 -1.0.pdf

    Hive定义了一种类SQL查询语言(即Hive QL),让熟悉SQL的用户能够以简单的方式查询Hadoop中的数据。同时,Hive还允许熟悉MapReduce的开发者编写自定义的mapper和reducer函数,用于处理无法通过内建函数完成的复杂...

    hive语法和常用函数.doc

    Hive的架构和执行流程包括用户提交的Hive QL语句最终被编译为MapReduce程序作为Job提交给Hadoop执行。Hive的数据类型包括基本数据类型和集合类型。基本数据类型包括TINYINT、SAMLLINT、INT、BIGINT、BOOLEAN、FLOAT...

    解决Hive启动失败Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

    FAILED: SemanticException org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient 通过控制台的...

    【hive on spark Error】return code 30041 from org.apache.hadoop.hive.ql.exec.spark.SparkTask.

    ERROR : FAILED: Execution Error, return code 30041 from org.apache.hadoop.hive.ql.exec.spark.SparkTask. 前言报错信息异常分析配置改动后记 前言 在成功消除Cloudare管理界面上那些可恶的警告之后,我又对yarn...

    Cloudera_ImpalaJDBC41_2.5.36.zip

    9. **Hive QL**:ql.jar包含了Hive的查询解析器和编译器,尽管Impala有自己的查询引擎,但可能仍需要与Hive Metastore交互,因此可能需要这些组件。 10. **FB303**:libfb303-0.9.0.jar是Facebook开源的Thrift服务...

    cloudera-hive-cdh6.3.2源码包

    源码中的 `org.apache.hadoop.hive.ql.metadata.Partition` 类表示分区,`org.apache.hadoop.hive.ql.plan.CreateTableDesc` 中定义了如何创建分区的规则。 5. **MapReduce 与 Tez 执行引擎** Hive 默认使用 ...

    大数据方向学习课程体系

    - Hive QL数据查询语法:学习SQL-like查询语言的基础。 - Join:了解不同类型的连接操作及其应用。 - 排序:掌握数据排序的不同策略。 - Hive内置函数:学习常用的内置函数及其用法。 - Hive自定义函数:理解...

    Hive 对 Protobuf 序列化文件读取.zip

    STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.protobuf.ProtobufInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.protobuf.ProtobufOutputFormat'; ``` 其中,`protobuf.schema`属性指定了....

    hive UDF需要jar包

    用户需要编写Java类,继承Hive提供的基类,如`org.apache.hadoop.hive.ql.udf.generic.GenericUDF`,并实现其方法来定义函数逻辑。 3. **JAR包依赖**: 如描述中所述,至少需要两个关键的JAR包: - **hive-exec-...

    大数据技术原理及应用 (2).docx

    - Hive是由Facebook开发的数据仓库工具,它构建在Hadoop之上,支持SQL-like查询语言Hive QL,使得熟悉SQL的用户能轻松处理Hadoop中的大数据。Hive将查询语句编译为MapReduce任务,方便数据分析和处理。 7. **Pig**...

    Spark SQL- Relational Data Processing in Spark(Paper).rar

    Spark SQL支持标准的SQL语法,允许用户通过Hive QL或JDBC/ODBC接口与Spark交互。这使得传统SQL开发者可以无缝迁移到Spark平台,而无需学习新的API。 5. **Integration with Datasets**: Spark 2.0引入了Dataset ...

    spark-hive_2.11-2.1.4-SNAPSHOT.jar

    HiveHBaseTableOutputFormat cannot be cast to org.apache.hadoop.hive.ql.io.HiveOutputFormat 问题的补丁,替换jars文件夹下的对应jar包即可,版本spark2.1

    FAILED: SemanticException org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.RuntimeExcepti

    FAILED: SemanticException org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient 解决方法 ...

Global site tag (gtag.js) - Google Analytics