Hadoop学习笔记（19）Hive的数据查询、JOIN连接以及内置函数

一、SELECT语句

1、查询所有

hive> select * from sougou.table_partition;

2、查询10条数据

hive> select * from sougou.table_partition limit 10;

3、统计条数

hive> select count(distinct uid) from sougou.table_partition;

4、like的使用（模糊查询）

%表示任意长度的字符

hive> select * from sougou.table_sougou where url like 'http%' limit 10;

5、表的嵌套（子查询）

hive> select count(distinct a.uid) as num from (select * from sougou.table_sougou where rank<=5 and orders=1) a;

二、WHERE语句

hive> select * from sougou.table_partition where keyword='youku';

三、GROUP BY

hive> select ts,uid,sum(orders) as orders from sougou.table_partition group by ts,uid;

四、JOIN

hive> select * from sougou.table_partition as a left join sougou.table_sougou as b on a.uid=b.uid;

五、ORDER BY和SORT BY

hive> select * from sougou.table_partition order by rank

hive> select * from sougou.table_partition sort by rank

六、内置函数

1、将string类型的值拼接起来

hive> select concat(uid,keyword) from sougou.table_sougou limit 10;

2、查询json格式的数据

$ 指通过get_json_object函数将’{“name”:“xiaoming”,“age”:“15”}'字符串转换成JSON对象，然后$也代表引用这个JSON对象。

hive> hive> select get_json_object('{"name":"xiaoming","age":"15"}','$.age') from sougou.table_sougou limit 10;

3、字符串的长度

hive> select length(uid) from sougou.table_sougou limit 10;

4、查询字段uid中第9个位置之后字符串ee第一次出现的位置

hive> select locate("ee",uid,9) from sougou.table_sougou limit 10;

5、通过命令查询内置函数

hive> show functions;

所有函数如下：

hive> show functions;
OK
!
!=
%
&
*
+
-
/
<
<=
<=>
<>
=
==
>
>=
^
abs
acos
add_months
and
array
array_contains
ascii
asin
assert_true
atan
avg
base64
between
bin
case
cbrt
ceil
ceiling
coalesce
collect_list
collect_set
compute_stats
concat
concat_ws
context_ngrams
conv
corr
cos
count
covar_pop
covar_samp
create_union
cume_dist
current_database
current_date
current_timestamp
current_user
date_add
date_format
date_sub
datediff
day
dayofmonth
decode
degrees
dense_rank
div
e
elt
encode
ewah_bitmap
ewah_bitmap_and
ewah_bitmap_empty
ewah_bitmap_or
exp
explode
factorial
field
find_in_set
first_value
floor
format_number
from_unixtime
from_utc_timestamp
get_json_object
greatest
hash
hex
histogram_numeric
hour
if
in
in_file
index
initcap
inline
instr
isnotnull
isnull
java_method
json_tuple
lag
last_day
last_value
lcase
lead
least
length
levenshtein
like
ln
locate
log
log10
log2
lower
lpad
ltrim
map
map_keys
map_values
matchpath
max
min
minute
month
months_between
named_struct
negative
next_day
ngrams
noop
noopstreaming
noopwithmap
noopwithmapstreaming
not
ntile
nvl
or
parse_url
parse_url_tuple
percent_rank
percentile
percentile_approx
pi
pmod
posexplode
positive
pow
power
printf
radians
rand
rank
reflect
reflect2
regexp
regexp_extract
regexp_replace
repeat
reverse
rlike
round
row_number
rpad
rtrim
second
sentences
shiftleft
shiftright
shiftrightunsigned
sign
sin
size
sort_array
soundex
space
split
sqrt
stack
std
stddev
stddev_pop
stddev_samp
str_to_map
struct
substr
substring
sum
tan
to_date
to_unix_timestamp
to_utc_timestamp
translate
trim
trunc
ucase
unbase64
unhex
unix_timestamp
upper
var_pop
var_samp
variance
weekofyear
when
windowingtablefunction
xpath
xpath_boolean
xpath_double
xpath_float
xpath_int
xpath_long
xpath_number
xpath_short
xpath_string
year
|
~
Time taken: 0.046 seconds, Fetched: 216 row(s)
hive>