Hadoop Hive sql语法详解5--HiveQL与SQL区别

标签: hadoop hive sql | 发表时间:2014-04-21 19:49 | 作者:myhadoop
出处:http://www.iteye.com

1.hive内联支持什么格式?
2.分号字符注意什么问题?

3.hive中empty是否为null?
4.hive是否支持插入现有表或则分区中?
5.hive是否支持INSERT INTO 表 values()?


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不支持将数据插入现有的表或分区中,
仅支持覆盖重写整个表,示例如下:

  1. INSERT OVERWRITE TABLE t1  
  2. SELECT * FROM t2;
复制代码



5、hive不支持INSERT INTO 表 Values(), UPDATE, DELETE操作
    这样的话,就不要很复杂的锁机制来读写数据。
    INSERT INTO syntax is only available starting in version 0.8。INSERT INTO就是在表或分区中追加数据。

6、hive支持嵌入mapreduce程序,来处理复杂的逻辑
如:

  1. FROM (  
  2. MAP doctext USING 'python wc_mapper.py' AS (word, cnt)  
  3. FROM docs  
  4. CLUSTER BY word  
  5. ) a  
  6. REDUCE word, cnt USING 'python wc_reduce.py';  
复制代码



--doctext: 是输入
--word, cnt: 是map程序的输出

--CLUSTER BY: 将wordhash后,又作为reduce程序的输入



并且map程序、reduce程序可以单独使用,如:

  1. FROM (  
  2. FROM session_table  
  3. SELECT sessionid, tstamp, data  
  4. DISTRIBUTE BY sessionid SORT BY tstamp  
  5. ) a  
  6. REDUCE sessionid, tstamp, data USING 'session_reducer.sh';  
复制代码



-DISTRIBUTE BY: 用于给reduce程序分配行数据

7、hive支持将转换后的数据直接写入不同的表,还能写入分区、hdfs和本地目录
这样能免除多次扫描输入表的开销。

  1. FROM t1  
  2.   
  3. INSERT OVERWRITE TABLE t2  
  4. SELECT t3.c2, count(1)  
  5. FROM t3  
  6. WHERE t3.c1 <= 20  
  7. GROUP BY t3.c2  
  8.   
  9. INSERT OVERWRITE DIRECTORY '/output_dir'  
  10. SELECT t3.c2, avg(t3.c1)  
  11. FROM t3  
  12. WHERE t3.c1 > 20 AND t3.c1 <= 30  
  13. GROUP BY t3.c2  
  14.   
  15. INSERT OVERWRITE LOCAL DIRECTORY '/home/dir'  
  16. SELECT t3.c2, sum(t3.c1)  
  17. FROM t3  
  18. WHERE t3.c1 > 30  
  19. GROUP BY t3.c2;  
复制代码


实际实例

创建一个表
CREATE TABLE u_data (
userid INT,
movieid INT,
rating INT,
unixtime STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '/t'
STORED AS TEXTFILE;

下载示例数据文件,并解压缩
wget  http://www.grouplens.org/system/files/ml-data.tar__0.gz
tar xvzf ml-data.tar__0.gz

加载数据到表中:
LOAD DATA LOCAL INPATH 'ml-data/u.data'
OVERWRITE INTO TABLE u_data;

统计数据总量:
SELECT COUNT(1) FROM u_data;

现在做一些复杂的数据分析:
创建一个 weekday_mapper.py: 文件,作为数据按周进行分割 
import sys
import datetime

for line in sys.stdin:
line = line.strip()
userid, movieid, rating, unixtime = line.split('/t')

生成数据的周信息
weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
print '/t'.join([userid, movieid, rating, str(weekday)])

使用映射脚本
//创建表,按分割符分割行中的字段值
CREATE TABLE u_data_new (
userid INT,
movieid INT,
rating INT,
weekday INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '/t';
//将python文件加载到系统
add FILE weekday_mapper.py;

将数据按周进行分割
INSERT OVERWRITE TABLE u_data_new
SELECT
TRANSFORM (userid, movieid, rating, unixtime)
USING 'python weekday_mapper.py'
AS (userid, movieid, rating, weekday)
FROM u_data;

SELECT weekday, COUNT(1)
FROM u_data_new
GROUP BY weekday;

处理Apache Weblog 数据
将WEB日志先用正则表达式进行组合,再按需要的条件进行组合输入到表中
add jar ../build/contrib/hive_contrib.jar;

CREATE TABLE apachelog (
host STRING,
identity STRING,
user STRING,
time STRING,
request STRING,
status STRING,
size STRING,
referer STRING,
agent STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
"input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (-|//[[^//]]*//]) ([^ /"]*|/"[^/"]*/") (-|[0-9]*) (-|[0-9]*)(?: ([^ /"]*|/"[^/"]*/") ([^ /"]*|/"[^/"]*/"))?",
"output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s"
)
STORED AS TEXTFILE;



已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [hadoop hive sql] 推荐:

Hadoop Hive sql语法详解5--HiveQL与SQL区别

- - SQL - 编程语言 - ITeye博客
1.hive内联支持什么格式. 3.hive中empty是否为null. 4.hive是否支持插入现有表或则分区中. 5.hive是否支持INSERT INTO 表 values(). 1、Hive不支持等值连接 . •SQL中对两表内联可以写成:. •分号是SQL语句结束标记,在HiveQL中也是,但是在HiveQL中,对分号的识别没有那么智慧,例如:.

SQL on Hadoop最新进展-转载

- - 人月神话的BLOG
原文:http://yanbohappy.sinaapp.com/?p=381. 为什么非要把SQL放到Hadoop上. 那为什么非得基于Hadoop呢. 目前SQL on Hadoop产品主要有以下几种:Hive, Tez/Stinger, Impala, Shark/Spark, Phoenix, Hawq/Greenplum, HadoopDB, Citusdata等.

SQL on Hadoop最新进展2-转载

- - 人月神话的BLOG
原文:http://yanbohappy.sinaapp.com/?p=407. 上篇主要讨论了Hive, Stinger/Tez, Impala, Shark这些SQL on Hadoop产品,这篇接着讨论Phoenix, Hadapt, Hawq. Salesforce开源的基于HBase的SQL查询系统,建立在HBase client API, coprocessors, custom filter的基础之上.

数据仓库中的SQL性能优化(Hive篇)

- - 奔跑的兔子
一个Hive查询生成多个map reduce job,一个map reduce job又有map,reduce,spill,shuffle,sort等多个阶段,所以针对hive查询的优化可以大致分为针对MR中单个步骤的优化(其中又会有细分),针对MR全局的优化,和针对整个查询(多MR job)的优化,下文会分别阐述.

Flume+Spark+Hive+Spark SQL离线分析系统

- - CSDN博客推荐文章
前段时间把Scala和Spark一起学习了,所以借此机会在这里做个总结,顺便和大家一起分享一下目前最火的分布式计算技术Spark. 当然Spark不光是可以做离线计算,还提供了许多功能强大的组件,比如说,Spark Streaming 组件做实时计算,和Kafka等消息系统也有很好的兼容性;Spark Sql,可以让用户通过标准SQL语句操作从不同的数据源中过来的结构化数据;还提供了种类丰富的MLlib库方便用户做机器学习等等.

Hadoop 归档 和HIVE 如何使用har 归档 文件

- - CSDN博客云计算推荐文章
但对于MapReduce 来说起不到任何作用,因为har文件就相当一个目录,仍然不能讲小文件合并到一个split中去,一个小文件一个split ,任然是低效的,这里要说一点<>对这个翻译有问题,上面说可以分配到一个split中去,但是低效的.      既然有优势自然也有劣势,这里不说它的不足之处,仅介绍如果使用har 并在hadoop中更好的使用har 文件.

基于Hadoop的数据仓库Hive 基础知识

- - IT瘾-bigdata
Hive是基于Hadoop的数据仓库工具,可对存储在HDFS上的文件中的数据集进行数据整理、特殊查询和分析处理,提供了类似于SQL语言的查询语言–HiveQL,可通过HQL语句实现简单的MR统计,Hive将HQL语句转换成MR任务进行执行. 数据仓库(Data Warehouse)是一个面向主题的(Subject Oriented)、集成的(Integrated)、相对稳定的(Non-Volatile)、反应历史变化(Time Variant)的数据集合,用于支持管理决策.

深入浅出数据仓库中SQL性能优化之Hive篇

- - 极客521 | 极客521
一个Hive查询生成多个Map Reduce Job,一个Map Reduce Job又有Map,Reduce,Spill,Shuffle,Sort等多个阶段,所以针对Hive查询的优化可以大致分为针对MR中单个步骤的优化(其中又会有细分),针对MR全局的优化,和针对整个查询(多MRJob)的优化,下文会分别阐述.

Greenplum Pivotal HD结合了SQL和Hadoop的优势

- - InfoQ cn
EMC Greenplum宣布了一个新的Hadoop发行版本—— Pivotal HD,其中包含一个完全运行于HDFS之上的MPP数据库,兼容SQL,而且速度“比Hive快数百倍”. Pivotal HD支持标准Hadoop发型版本的常用特性(包括HDFS、Pig、Hive、Mahout和Map-Reduce等),但又加入了一些其他的组件,具体如下面结构图所示: .

盘点SQL on Hadoop中用到的主要技术

- - 奔跑的兔子
自hive出现之后,经过几年的发展,SQL on Hadoop相关的系统已经百花齐放,速度越来越快,功能也越来越齐全. 本文并不是要去比较所谓“交互式查询哪家强”,而是试图梳理出一个统一的视角,来看看各家系统有哪些技术上相通之处. 考虑到系统使用的广泛程度与成熟度,在具体举例时一般会拿Hive和Impala为例,当然在调研的过程中也会涉及到一些其他系统,如Spark SQL,Presto,TAJO等.