`

Hadoop Hive sql语法详解4--DQL 操作:数据查询SQL

    博客分类:
  • Hive
阅读更多

1.基本的Select 操作如何实现?
2.基于Partition的查询如何实现?
3.如何实现join,是否支持左连接,右连接?
4.hive数据如何去重?

5.ORDER BY 是否全局排序,只有一个Reduce任务?
6.SORT BY 是否全局排序?
7.hive是否支持exists?
8.Hive不支持所有非等值的连接,为什么?

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


例如
按先件查询
hive> SELECT a.foo FROM invites a WHERE a.ds='<DATE>';


将查询数据输出至目录:
hive> INSERT OVERWRITE DIRECTORY '/tmp/hdfs_out' SELECT a.* FROM invites a WHERE a.ds='<DATE>';


将查询结果输出至本地目录:
hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/local_out' SELECT a.* FROM pokes a;


选择所有列到本地目录 :
hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a;
hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a WHERE a.key < 100;
hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/reg_3' SELECT a.* FROM events a;
hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_4' select a.invites, a.pokes FROM profiles a;
hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT COUNT(1) FROM invites a WHERE a.ds='<DATE>';
hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT a.foo, a.bar FROM invites a;
hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/sum' SELECT SUM(a.pc) FROM pc1 a;


将一个表的统计结果插入另一个表中:
hive> FROM invites a INSERT OVERWRITE TABLE events SELECT a.bar, count(1) WHERE a.foo > 0 GROUP BY a.bar;
hive> INSERT OVERWRITE TABLE events SELECT a.bar, count(1) FROM invites a WHERE a.foo > 0 GROUP BY a.bar;
JOIN
hive> FROM pokes t1 JOIN invites t2 ON (t1.bar = t2.bar) INSERT OVERWRITE TABLE events SELECT t1.bar, t1.foo, t2.foo;


将多表数据插入到同一表中:
FROM src
INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100
INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200
INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE src.key >= 300;


将文件流直接插入文件:
hive> FROM invites a INSERT OVERWRITE TABLE events SELECT TRANSFORM(a.foo, a.bar) AS (oof, rab) USING '/bin/cat' WHERE a.ds > '2008-08-09';
This streams the data in the map phase through the script /bin/cat (like hadoop streaming). Similarly - streaming can be used on the reduce side (please see the Hive Tutorial or examples)




2. 基于Partition的查询

•一般 SELECT 查询会扫描整个表,使用 PARTITIONED BY 子句建表,查询就可以利用分区剪枝(input pruning)的特性
•Hive 当前的实现是,只有分区断言出现在离 FROM 子句最近的那个WHERE 子句中,才会启用分区剪枝

3.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 任务

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

 

分享到:
评论

相关推荐

    Hive-2.1.1-CDH-3.6.1 相关JDBC连接驱动 Jar 包集合

    01、hadoop-common-3.0.0-cdh6.3.1.jar 02、hive-exec-2.1.1-cdh6.3.1.jar 03、hive-jdbc-2.1.1-cdh6.3.1.jar 04、hive-jdbc-2.1.1-cdh6.3.1-standalone.jar 05、hive-metastore-2.1.1-cdh6.3.1.jar 06、hive-...

    flink-sql-connector-hive-3.1.2_2.11-1.11.6.jar 已经解决guava冲突亲测可以

    flink-sql-connector-hive-3.1.2_2.11-1.11.6.jar 已经解决guava冲突亲测可以

    DBeaver链接hive驱动包下载: hive-jdbc-uber-2.6.5.0-292.jar

    在大数据处理领域,Hive作为一个基于Hadoop的数据仓库工具,广泛用于数据查询和分析。而DBeaver,作为一款跨平台的数据库管理工具,以其用户友好的界面和强大的功能,深受开发者喜爱。当需要在DBeaver中操作Hive...

    spark-hive-2.11和spark-sql-以及spark-hadoop包另付下载地址

    Spark是大数据处理领域的一款热门框架,它提供了高效、弹性、分布式的计算能力。...通过正确配置和使用这些jar包,用户可以在Spark上实现高效、灵活的Hive数据查询和分析,同时享受Spark带来的高性能计算体验。

    SQL-on-Hadoop: Full Circle Back to Shared-Nothing Database Architectures

    为了更好地利用Hadoop的强大功能并保持对SQL的支持,出现了一系列的SQL-on-Hadoop解决方案,旨在将传统的SQL查询能力与Hadoop的大规模分布式计算能力相结合,满足企业级数据分析的需求。 #### Hive与Impala对比 - ...

    hive驱动包hive-jdbc-uber-2.6.5.0-292.jar(用户客户端连接使用)

    Hive是Apache Hadoop生态系统中的一个数据仓库工具,它允许我们对存储在HDFS上的大数据进行结构化查询和分析。Hive JDBC驱动是Hive与各种数据库管理工具、应用程序之间建立连接的关键组件,使得用户可以通过标准的...

    hive案例之---------微博数据分析及答案.zip

    Hive是Apache Hadoop生态系统中的一个数据仓库工具,它允许用户使用SQL-like查询语言(HQL)来处理存储在Hadoop分布式文件系统(HDFS)上的大数据集。在这个“hive案例之---------微博数据分析及答案”的项目中,...

    hive-jdbc-1.1.0-cdh5.12.1 连接库 jar包

    在大数据处理领域,Hive通常被用作数据仓库工具,支持SQL查询,使得非Hadoop开发者也能轻松地处理和分析存储在Hadoop集群中的大量数据。 Hive JDBC是Hive提供的一种标准接口,它遵循JDBC API规范,使得任何支持JDBC...

    基于Hadoop Hive健身馆可视化分析平台项目源码+数据库文件.zip

    环境启动 hadoop hive2元数据库 sql导入 导入hivesql脚本,修改application.yml 启动主程序 HadoopApplication 基于Hadoop Hive健身馆可视化分析平台项目源码+数据库文件.zip启动方式 环境启动 hadoop hive2元数据库...

    hive-jdbc-1.2.1-standalone

    Hive JDBC允许开发者使用标准的JDBC API来执行SQL查询,获取数据,或者将数据写入Hive表。在给定的标题“hive-jdbc-1.2.1-standalone”中,"standalone"表示这是一个独立的版本,包含了运行Hive JDBC所需的所有依赖...

    Hadoop-2.8.0-HA-Hive安装部署与HQL12.hive的基本语法--数据导入--从本地--从hdfs.mp4

    Hadoop-2.8.0-HA-Hive安装部署与HQL12.hive的基本语法--数据导入--从本地--从hdfs.mp4

    hive-jdbc-jar-多版本.zip

    Hive JDBC驱动是Java开发人员用来与Hadoop上的Hive数据仓库进行交互的重要工具。它提供了标准的JDBC接口,使得任何支持JDBC的应用程序,如Java应用或BI工具,都可以方便地连接到Hive执行SQL查询。"hive-jdbc-jar-多...

    hive-jdbc-uber-2.6.5.0-292.zip

    Hive本身是一个基于Hadoop的数据仓库工具,用于查询和管理大规模结构化数据。它提供了SQL-like语言(称为HQL,Hive Query Language)来处理数据,非常适合批处理和分析作业。Hive JDBC驱动使得Hive的数据可被任何...

    Hadoop-2.8.0-Day08-Hive函数与HQL详解-课件与资料.zip

    Hadoop是一个开源框架,主要用于分布式存储和计算大规模数据集,而Hive则是建立在Hadoop之上的一种数据仓库工具,它提供了一种SQL-like的语言(称为HiveQL或HQL)来方便数据查询和分析。在这个"Day08-Hive函数与HQL...

    hive-jdbc-uber-2.6.5.0-292.jar

    它提供了标准的JDBC接口,使得开发者可以使用熟悉的SQL语法与Hive进行交互,极大地简化了Hadoop环境下的数据操作。Hive JDBC的Uber版本,如2.6.5.0-292,将所有依赖库都包含在一个JAR文件中,避免了因为依赖问题带来...

    Oracle PL-SQL 对应 hadoop hive 、 phoenix中常用的对应函

    Oracle PL-SQL 与 Hadoop Hive 和 Phoenix 是两种不同的数据处理和存储系统,它们各自拥有独特的函数库,但在数据操作中存在一些相似的功能。本文将详细介绍 Oracle PL-SQL 中的一些常见函数,以及它们在 Hadoop ...

    hive-jdbc-uber-2.6.5.0-292.jar驱动

    1. **数据查询**:允许用户执行SQL查询并获取结果,从而在Hive上进行数据分析。 2. **连接管理**:提供连接到Hive服务器的接口,包括建立、配置、管理和关闭连接。 3. **元数据获取**:允许用户获取数据库、表、列等...

    hive-jdbc-uber-3.1.2+yanagishima-18.0

    这个版本的特性包括对Hive SQL的全面支持,以及与多种数据库管理系统兼容的能力,使得开发人员可以轻松地在Hive与应用程序之间建立连接,进行数据查询和操作。 然而,在大型企业或组织中,数据安全性是至关重要的。...

    Hadoop-2.8.0-HA-Hive安装部署与HQL09.hive的基本语法--建表语法.mp4

    Hadoop-2.8.0-HA-Hive安装部署与HQL09.hive的基本语法--建表语法.mp4

    Hive_SQL语法大全

    Hive_Sql语法详述,平时工作中遇到的问题,用于Hadoop平台的数据挖掘。

Global site tag (gtag.js) - Google Analytics