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

HiveQL介绍

阅读更多

 

Hive支持原子和复杂数据类型。原子数据类型包括数值型、布尔型和字符串类型,其中数值型有int / bigint / smallint / tinyint /double / float,布尔型为boolean,字符串类型为string。复杂数据类型包括数组(Array)、集合(Map)和结构(Struct)。Hive 还提供了大量的内置函数,诸如数学和统计函数、字符串函数、日期函数、条件函数、聚集函数等。当无法用Hive提供的内置函数来表示时,用户可以使用 UDF(用户自定义函数)。Hive中有三种UDF:UDF(普通)、UDAF(用户定义聚集函数)和UDTF(用户定义表生成函数)。

第一部分:DDL

Hive提供的DDL包含如下操作:

•建表

1)  建立内部表

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可以为表与字段增加描述

STORED AS

SEQUENCEFILE

| TEXTFILE

| RCFILE

|INPUTFORMAT input_format_classname OUTPUTFORMAT             output_format_classname

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

2)建立外部表

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>’;

3)建立分区表

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;

3)建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;

4)复制一个空表

CREATE TABLE empty_key_value_store

LIKE key_value_store;

 

•删除表

DROP TABLE table_name

 

•修改表结构

修改表结构操作包括增加分区、删除分区、重命名表、修改列的名字、类型、位置、注释及增加/更新列、增加表的元数据信息。

增加分区 ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ]
删除分区 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]
增加/更新列 ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], …)
增加表的元数据信息 ALTER TABLE table_name SET TBLPROPERTIES table_properties table_properties:

•改变表文件格式与组织

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
DROP VIEW view_name
如果没有提供表名,视图列的名字将由定义的SELECT表达式自动生成。

如果修改基本表的属性,视图中不会体现,无效查询将会失败。

视图是只读的,不能用LOAD/INSERT/ALTER。

•创建数据库

CREATE DATABASE name

•显示命令

show tables;

show databases;

show partitions ;

show functions

describe extended table_name dot col_name

 

DML包含如下操作:

•向数据表内加载文件

•LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
Load 操作只是单纯的复制/移动操作,将数据文件移动到 Hive 表对应的位置。

filepath

1.相对路径,例如:project/data1

2.绝对路径,例如: /user/hive/project/data1

3.包含模式的完整 URI,例如:hdfs://namenode:9000/user/hive/project/data1

•将查询结果插入到Hive表中

基本模式 INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement
多插入模式 FROM from_statement

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1

[INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ...

自动分区模式 INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement
将查询结果写入HDFS文件系统

 

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...

FROM from_statement

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1

[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2]

INSERT INTO INSERT INTO  TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement

Hive的SQL操作

•基本的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]

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

2. Where 条件:类似我们传统SQL的where 条件

3. 0.9版本支持between,还不支持EXIST ,NOT EXIST

4. ORDER BY与SORT BY的不同:ORDER BY 全局排序,只有一个Reduce任务,SORT BY 只在本机做排序

Limit:Limit 可以限制查询的记录数,如:SELECT * FROM t1 LIMIT 5

 

•基于Partition的查询

如下面的查询语句:SELECT day_table.* FROM day_table WHERE day_table.dt>= ’2008-08-08′; dt是day_table表的分区字段。

Join语句

•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

•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)

•可以 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 任务

LEFT,RIGHT和FULL OUTER

•例子

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

•如果你想限制 join 的输出,应该在 WHERE 子句中写过滤条件——或是在 join 子句中写

•容易混淆的问题是表分区的情况

• SELECT c.val, d.val FROM c LEFT OUTER JOIN d ON (c.key=d.key)

WHERE a.ds=’2010-07-07′ AND b.ds=’2010-07-07‘

•如果 d 表中找不到对应 c 表的记录,d 表的所有列都会列出 NULL,包括 ds 列。也就是说,join 会过滤 d 表中不能找到匹配 c 表 join key 的所有记录。这样的话,LEFT OUTER 就使得查询结果与 WHERE 子句无关

•解决办法

•SELECT c.val, d.val FROM c LEFT OUTER JOIN d

ON (c.key=d.key AND d.ds=’2009-07-07′ AND c.ds=’2009-07-07′)

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)

UNION ALL

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

•select_statement UNION ALL select_statement UNION ALL select_statement …

 

 

第四部分:注意HiveQL区别于SQL的一些情况

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;

分号字符

•分号是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;

IS [NOT] NULL

•SQL中null代表空值, 值得警惕的是, 在HiveQL中String类型的字段若是空(empty)字符串, 即长度为0, 那么对它进行IS NULL的判断结果是False.

 

 

 

分享到:
评论

相关推荐

    Hive开发培训.pptx

    #### 五、HiveQL介绍 - **基本语法**: - 创建数据库: `CREATE DATABASE IF NOT EXISTS demo;` - 查看数据库详情: `DESCRIBE DATABASE [EXTENDED] demo;` - 设置当前数据库: `SET hive.cli.print.current.db=true;...

    大数据课程体系.docx

    - **HiveQL介绍与SQL的比较**:比较HiveQL与标准SQL的语法差异。 - **数据类型**:列出Hive支持的数据类型及其用途。 - **外部表和分区表**:说明如何创建和使用外部表与分区表。 - **DDL与CLI客户端演示**:通过...

    HiveQL_UDF_函数V0.5.pdf

    HiveQL的UDF手册是一份非常宝贵的资源,它详细介绍了每个函数的语法、操作类型、描述和应用举例,是大数据开发人员进行数据分析和处理的必备工具书。这些函数能够帮助用户在Hive环境中高效地处理数据,进行各种复杂...

    Data-Enginner

    二、HiveQL介绍 HiveQL,全称为Hive Query Language,是Apache Hive项目的一部分,它提供了一种SQL-like的语法来操作Hadoop上的大规模数据。HiveQL的主要功能包括: 1. 定义数据结构:通过创建表来定义数据结构,...

    Scriptis:Scriptis用于交互式数据分析,包括脚本开发(SQL,Pyspark,HiveQL),任务提交(Spark,Hive),UDF,功能,资源管理和智能诊断

    圣经 英文|介绍Scriptis用于交互式数据分析,包括脚本开发(SQL,Pyspark,HiveQL),任务提交(Spark,Hive),UDF,功能,资源管理和智能诊断。产品特点脚本编辑器:支持多语言,自动完成,语法突出显示和SQL语法...

    hive编程指南中文

    《Hive编程指南》是一本Apache Hive的编程指南 旨在介绍如何使用Hive的SQL方法 HiveQL来汇总 查询和分析存储在Hadoop分布式文件系统上的大数据集合 全书通过大量的实例 首先介绍如何在用户环境下安装和配置Hive 并对...

    datav.js

    例如,`HiveQL函数手册.doc`和`HiveQL中自带的数学函数.docx`中详细介绍了HiveQL提供的内置函数,包括统计函数、数学函数等,这些函数可以帮助用户进行复杂的数据计算和分析。 在Hadoop生态系统中,Hive主要负责...

    炼数成金 Hadoop 视频教程

    1.Hadoop的源起与体系介绍 2.Hadoop的源起与体系介绍 3.Hadoop的源起与体系介绍 4.实施Hadoop集群 5.实施Hadoop集群 6.实施Hadoop集群 7.分布式文件系统HDFS,大数据存储实战 8.分布式文件系统HDFS,大数据存储实战 ...

    hive的安装与配置的概要介绍与分析

    Apache Hive 是一个建立在 Hadoop 之上的数据仓库工具,它提供了一种类似 SQL 的查询语言——HiveQL,使得不熟悉 MapReduce 的用户也能方便地利用 Hadoop 的分布式处理能力来分析存储在 HDFS 中的大规模数据集。...

    4 大数据基础 - Hive

    下面将详细介绍 Hive 的基本概念和应用。 Hive 的基本概念 Hive 是一个数据仓库工具,基于 Hadoop 的分布式存储和计算平台,提供了类似 SQL 的查询语言 HiveQL,能够对大规模数据进行存储和查询。Hive 的主要特点...

    Programming Hive (hive编程).pdf

    标题《Programming Hive》即《Hive编程》,是一本专注于介绍如何使用Hive进行数据存取的书籍,针对的是那些希望学习Hive技术的初学者。Hive是一个建立在Hadoop上的数据仓库工具,它提供了类SQL查询语言——HiveQL,...

    Cloudera大数据分析培训内容介绍.pdf

    课程将介绍Hive的表结构、数据存储方式,对比Hive与传统数据库的差异,以及如何使用HiveQL进行关系数据分析。通过实验,学员将学习如何创建Hive数据库和表,以及编写复杂的查询语句。 **Cloudera Impala**:Impala...

    Hadoop数据仓库--hive介绍

    Hive 是一个基于 Hadoop 的数据仓库工具,它允许用户使用类似于 SQL 的查询语言(称为 HiveQL 或 HQL)对大规模数据集进行分析和处理。Hive 的设计初衷是为了简化大数据处理,使得非编程背景的用户也能方便地进行...

    大数据学习笔记

    ##### 第1章:Spark介绍 - **1.1 Spark简介与发展** - **背景**:随着大数据处理需求的增长,传统的Hadoop MapReduce框架虽然提供了强大的计算能力,但在处理速度、易用性和灵活性方面存在一定的局限性。 - **...

    Hive数据操作-教案.pdf

    在实际教学过程中,教师会先进行签到,然后介绍课程内容和目标,引导学生分组讨论如何编写HiveQL查询语句。教师会对各小组的讨论方案进行评价,指出优点和需要改进的地方,从而帮助学生理解和掌握Hive数据操作的关键...

    Michael Armbrust:Fast distributed query processing with Shark

    总之,Michael Armbrust在该演讲中介绍了Shark项目的关键特点和优势,以及其与Hive和Spark的关系。Shark通过在Spark上运行HiveQL,提升了处理速度,使得大数据分析更加高效。Shark的出现代表了大数据处理领域的进步...

    《Hive编程指南》英文文字版 非扫描版 带标签 超清晰

    本书详细介绍了如何使用Hive这个数据仓库工具来处理大数据问题。Hive作为Hadoop生态系统中的重要组件,允许用户用类似SQL的方式来查询存储在Hadoop上的大规模数据集。Hive提供了数据摘要、查询和分析的工具,使得...

    Hadoop数据仓库工具--hive介绍

    Hive 是一个基于 Hadoop 的数据仓库工具,它允许用户使用类似于 SQL 的查询语言(称为 HiveQL 或 HQL)对大数据集进行分析和处理。Hive 提供了一种简便的方式来组织、管理和查询存储在 Hadoop 分布式文件系统(HDFS...

Global site tag (gtag.js) - Google Analytics