从SQL到HiveQL

从SQL到HiveQL应转变的习惯

HiveQL是一种类似SQL的语言, 它与大部分的SQL语法兼容, 但是并不完全支持SQL标准, 如HiveQL不支持更新操作（行级别）, 也不支持事务, 它的索引，子查询和join操作也很局限, 这是因其底层依赖于Hadoop云平台这一特性决定的, 但其有些特点是SQL所无法企及的。例如多表查询、和集成MapReduce脚本等,

也支持create table as select （像mysql 类似  create table newtable as select * from mytable;）

3.字段类型
Hive支持基本数据类型和复杂类型, 基本数据类型主要有数值类型(INT、FLOAT、DOUBLE)、布尔型和字符串, 复杂类型有三种:ARRAY、MAP 和 STRUCT。
a.基本数据类型
TINYINT: 1个字节
SMALLINT: 2个字节
INT: 4个字节   
BIGINT: 8个字节
BOOLEAN: TRUE/FALSE  
FLOAT: 4个字节，单精度浮点型
DOUBLE: 8个字节，双精度浮点型STRING       字符串

b.复杂数据类型
ARRAY: 有序字段
MAP: 无序字段
STRUCT: 一组命名的字段

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

4.2、分号字符

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

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

•但HiveQL在解析语句时提示：

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

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

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

 

4.3、IS [NOT] NULL

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

4.4、Hive不支持将数据插入现有的表或分区中，

仅支持覆盖重写整个表，示例如下：

INSERT OVERWRITE TABLE t1  

SELECT * FROM t2; INSERT OVERWRITE TABLE t1SELECT * FROM t2;

 

4.5、hive不支持INSERT INTO, UPDATE, DELETE操作

     这样的话，就不要很复杂的锁机制来读写数据。
     INSERT INTO syntax is only available starting in version 0.8。INSERT INTO就是在表或分区中追加数据。

4.6、hive支持嵌入mapreduce程序，来处理复杂的逻辑

如：

FROM (  

MAP doctext USING 'python wc_mapper.py' AS (word, cnt)  

FROM docs  

CLUSTER BY word  

) a  

REDUCE word, cnt USING 'python wc_reduce.py'; FROM (

MAP doctext USING 'python wc_mapper.py' AS (word, cnt)

FROM docs

CLUSTER BY word

) a

REDUCE word, cnt USING 'python wc_reduce.py';

--doctext: 是输入

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

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

并且map程序、reduce程序可以单独使用，如：

FROM (  

FROM session_table  

SELECT sessionid, tstamp, data  

DISTRIBUTE BY sessionid SORT BY tstamp  

) a  

REDUCE sessionid, tstamp, data USING 'session_reducer.sh';  

FROM (

FROM session_table

SELECT sessionid, tstamp, data

DISTRIBUTE BY sessionid SORT BY tstamp

) a

REDUCE sessionid, tstamp, data USING 'session_reducer.sh';

 

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

4.7、hive支持将转换后的数据直接写入不同的表，还能写入分区、hdfs和本地目录。

这样能免除多次扫描输入表的开销。

FROM t1  

INSERT OVERWRITE TABLE t2  

SELECT t3.c2, count(1)  

FROM t3  

WHERE t3.c1 <= 20  

GROUP BY t3.c2  

  

INSERT OVERWRITE DIRECTORY '/output_dir'  

SELECT t3.c2, avg(t3.c1)  

FROM t3  

WHERE t3.c1 > 20 AND t3.c1 <= 30  

GROUP BY t3.c2  

  

INSERT OVERWRITE LOCAL DIRECTORY '/home/dir'  

SELECT t3.c2, sum(t3.c1)  

FROM t3  

WHERE t3.c1 > 30  

GROUP BY t3.c2;  

 http://blog.sina.com.cn/s/blog_9f48885501017cs3.html

HIVE 中使用python 脚本的具体例子

http://www.coder4.com/archives/4052

http://m.oschina.net/blog/275750

我总结了一下这里python的写法为一句话：

sys.stdin作为输入，print出一个列表

然后在 hiveql中，我们使用了 AS ，指定输出的若干个列，分别对应到哪个列名。

比如一个

testTable 有两列，

create table testTable (col1 INT, col2 INT); 在Hive中，使用脚本，首先要将他们加入： 

add file /xxxx/test.py 然后，在调用时，使用TRANSFORM语法。 

SELECT TRANSFORM (col1, col2) USING './test.py' AS (new1, new2) FROM testTable;

http://www.cnblogs.com/HondaHsu/p/4346354.html