Programming Hive ( Hive编程指南) 四

Chapter 6   HiveQL：查询 

--[root@master chapter5]# cat 4.create_employees.sql 
create table employees (
        name    string,
        salary  float,
        subordinates    Array<string>,
        dedcutions      map<string,float>,
        address struct<street:string,city:string,state:string,zip:int>
)
partitioned by (country string,state string);

--集合字符串元素是加“”的，而基本数据类型String的列值是不加的
select name,subordinates from employees;
Jason    ["Marry","Todd"]
Marry    ["Bill"]
Todd     []
Bill     []

--subordinates是Array，结果用【】表示；deductions是MAP,address是STRUCT，结果用{}表示

引用集合数据类型的元素(下标索引从0开始)，不存在元素返回null，且String类型没有"" 

--引用集合数据类型的元素(下标索引从0开始)
select name , subordinates[0] from employees;
Jason    Marry
Marry    Bill
Bill     null
Todd     null

引用MAP元素

--引用Map元素,需要使用键值，而不是下标索引
select name,deductions["State Taxes"] from employees;
Jason    0.05
Marry    0.05
Todd     0.03
Bill     0.03

引用Struct中的元素

--引用struct中的元素，使用.符号
select name , address.city from employees;
Jason    Chicago
Marry    Chicago
Todd     Oak Park
Bill     Obscuria

6.1.1使用正则表达式来指定列

--创建stocks表
create external table if not exists stocks (
    exchange    string,
    symbol      string,
    ymd         string,
    price_open  float,
    price_high  float,
    price_low   float,
    price_close float,
    volume      int,
    price_adj_close    float)
row format delimited fields terminated by ','
location '/data/stocks';

--选择symbol和所有列名以price为前缀的列(price_open、price_high、price_low、price_close、price_adj_close)
select symbol,'price.*' from stocks;
AAPL    195.69    197.88    194.0    194.12    194.12
AAPL    195.69    197.88    194.0    194.12    194.12
...

 6.1.2 使用列值进行计算

select upper(name),salary,deductions['Federal Taxes'],round(salary * (1-deductions['Federal Taxes'])) from employees;
Jason    10000.0    0.2    80000
Mary     8000.0     0.2    64000
Todd     7000.0     0.15   59500
Bill     6000.0    0.15    51000

6.1.3 算术运算符

运算符	类型	说明
A + B	所有数字类型	A和B相加。结果的与操作数值有共同类型。例如每一个整数是一个浮点数，浮点数包含整数。所以，一个浮点数和一个整数相加结果也是一个浮点数。
A – B	所有数字类型	A和B相减。结果的与操作数值有共同类型。
A * B	所有数字类型	A和B相乘，结果的与操作数值有共同类型。需要说明的是，如果乘法造成溢出，将选择更高的类型。
A / B	所有数字类型	A和B相除，结果是一个double（双精度）类型的结果。
A % B	所有数字类型	A除以B余数与操作数值有共同类型。
A & B	所有数字类型	运算符查看两个参数的二进制表示法的值，并执行按位”与”操作。两个表达式的一位均为1时，则结果的该位为 1。否则，结果的该位为 0。
A|B	所有数字类型	运算符查看两个参数的二进制表示法的值，并执行按位”或”操作。只要任一表达式的一位为 1，则结果的该位为 1。否则，结果的该位为 0。
A ^ B	所有数字类型	运算符查看两个参数的二进制表示法的值，并执行按位”异或”操作。当且仅当只有一个表达式的某位上为 1 时，结果的该位才为 1。否则结果的该位为 0。
~A	所有数字类型	对一个表达式执行按位”非”（取反）。

关系运算符

运算符	类型	说明
A = B	所有原始类型	如果A与B相等,返回TRUE,否则返回FALSE
A == B	无	失败，因为无效的语法。 SQL使用”=”，不使用”==”。
A <> B	所有原始类型	如果A不等于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”，结果返回”NULL”。
A < B	所有原始类型	如果A小于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”，结果返回”NULL”。
A <= B	所有原始类型	如果A小于等于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”，结果返回”NULL”。
A > B	所有原始类型	如果A大于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”，结果返回”NULL”。
A >= B	所有原始类型	如果A大于等于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”，结果返回”NULL”。
A IS NULL	所有类型	如果A值为”NULL”，返回TRUE,否则返回FALSE
A IS NOT NULL	所有类型	如果A值不为”NULL”，返回TRUE,否则返回FALSE
A LIKE B	字符串	如果A或B值为”NULL”，结果返回”NULL”。字符串A与B通过sql进行匹配，如果相符返回TRUE，不符返回FALSE。B字符串中 的”_”代表任一字符，”%”则代表多个任意字符。例如： (‘foobar’ like ‘foo’)返回FALSE，（ ‘foobar’ like ‘foo_ _ _’或者 ‘foobar’ like ‘foo%’)则返回TURE
A RLIKE B	字符串	如果A或B值为”NULL”，结果返回”NULL”。字符串A与B通过java进行匹配，如果相符返回TRUE，不符返回FALSE。例如：（ ‘foobar’ rlike ‘foo’）返回FALSE，（’foobar’ rlike ‘^f.*r$’ ）返回TRUE。
A REGEXP B	字符串	与RLIKE相同。

逻辑运算符

运算符	类型	说明
A AND B	布尔值	A和B同时正确时,返回TRUE,否则FALSE。如果A或B值为NULL，返回NULL。
A && B	布尔值	与”A AND B”相同
A OR B	布尔值	A或B正确,或两者同时正确返返回TRUE,否则FALSE。如果A和B值同时为NULL，返回NULL。
A | B	布尔值	与”A OR B”相同
NOT A	布尔值	如果A为NULL或错误的时候返回TURE，否则返回FALSE。
! A	布尔值	与”NOT A”相同

1.4复杂类型函数

函数	类型	说明
map	(key1, value1, key2, value2, …)	通过指定的键/值对，创建一个map。
struct	(val1, val2, val3, …)	通过指定的字段值，创建一个结构。结构字段名称将COL1，COL2，…
array	(val1, val2, …)	通过指定的元素，创建一个数组。

1.5对复杂类型函数操作

函数	类型	说明
A[n]	A是一个数组，n为int型	返回数组A的第n个元素，第一个元素的索引为0。如果A数组为['foo','bar']，则A[0]返回’foo’和A[1]返回”bar”。
M[key]	M是Map，关键K型	返回关键值对应的值，例如mapM为 \{‘f’ -> ‘foo’, ‘b’ -> ‘bar’, ‘all’ -> ‘foobar’\}，则M['all'] 返回’foobar’。
S.x	S为struct	返回结构x字符串在结构S中的存储位置。如 foobar \{int foo, int bar\} foobar.foo的领域中存储的整数。

2.内置函数
2.1数学函数

返回类型	函数	说明