HIVE学习笔记

左手的ㄟ右手 2022-03-18 07:26 320阅读 0赞

**hive> desc mytest;**  
OK  
num    int      
name    string      
Time taken: 0.087 seconds  
**hive> select \* from mytest;**  
OK  
**NULL    NULL**  
22    world  
33    hive

**hive> select \* from mytest a join mytest b on a.num=b.num;**

OK  
22    world    22    world  
33    hive    33    hive

**1.hive测试数据位置本地：／home/hadoop/testdata/hive**

**命名规则：表名.txt**

create table tb1(id double,name string) ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\\t'

storedas textfile;

**本地直接写绝对路径就好，loaddata local inpath**

loaddata local inpath '/home/hadoop/testdata/hive/tb1.txt' overwrite intotable tb1;

hive>select \*from tb1;

100.0 hadoop

101.0 hive

102.0 hbase

103.0 memcachedb

Timetaken: 0.236 seconds

因为字段类型为double，所以将id显示为double型

**创建表tb2,并将tb1的数据插入tb2**

create table tb2 (id double,name string) ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\\t'

stored as textfile;

insert overwrite table tb2 select \* from tb1;

select\*from tb2;

100.0 hadoop

101.0 hive

102.0 hbase

103.0 memcachedb

**修改表tb101的id字段信息，由double修改为int**

alter table tbt1 change id id int;

100 hadoop

101 hive

102 hbase

103 memcachedb

**修改表tb2的id字段信息，由double修改为int**

alter table tb2 change id id int;

hive>select \*from tb2;

NULL hadoop

NULL hive

NULL hbase

NULL memcachedb

此时由于tb2表的底层数据发生了变化

hadoop@ubuntu:~/testdata/hive$hadoop fs -cat /user/hive/warehouse/tb1/tb1.txt

100 hadoop

101 hive

102 hbase

103 memcachedb

hadoop@ubuntu:~/testdata/hive$hadoop fs -cat /user/hive/warehouse/tb2/000000\_0

100.0 hadoop

101.0 hive

102.0 hbase

103.0 memcachedb

**虚拟列**

INPUT\_\_FILE\_\_NAME表示输入文件的路径

BLOCK\_\_OFFSET\_\_INSIDE\_\_FILE表示的记录在文件中的偏移量

select id,INPUT\_\_FILE\_\_NAME,BLOCK\_\_OFFSET\_\_INSIDE\_\_FILE from tb1;

100 hdfs://localhost:9000/user/hive/warehouse/tb1/tb1.txt 0

101 hdfs://localhost:9000/user/hive/warehouse/tb1/tb1.txt 11

102 hdfs://localhost:9000/user/hive/warehouse/tb1/tb1.txt 20

103 hdfs://localhost:9000/user/hive/warehouse/tb1/tb1.txt 30

**一些函数的用法保证tb1存在**

select concat('a','\\001','b') from tb1;

**Array数组构建：**

**语法:array(val1, val2, …)说明：根据输入的参数构建数组array类型**

create table udftest as select array("tom","mary","tim")as t from tb1;

hive>desc udftest;

t array<string>

hive>select \*from udftest;

\["tom","mary","tim"\]

### array类型访问:A\[n\] ###

select t\[0\],t\[1\],t\[2\] from udftest;

tom mary tim

s**elect t\[0\],t\[4\] from udftest;****没有直接返回null**

**Map类型构建**

Create table maptest as select map('100','tom','200','mary') as t fromtb1;(注意此处maptest不能写成map)

hive>desc maptest;

t map<string,string>

Timetaken: 0.106 seconds

hive>select \*from maptest;

\{"100":"tom","200":"mary"\}

### map类型访问:M\[key\] ###

select t\['200'\],t\['100'\] from maptest;

mary tom

select t\['200'\],t\['300'\] from maptest;key—300不存在,返回null

mary NULL

**Struct类型构建**

create table structtest as select struct('tom','mary','tim') as t from tb1;

hive>desc structtest;

t struct<col1:string,col2:string,col3:st

hive>select \*from structtest;

\{"col1":"tom","col2":"mary","col3":"tim"\}

### struct类型访问:S.x ###

说明：返回结构体S中的x字段。比如，对于结构体structfoobar \{int foo, int bar\}，foobar.foo返回结构体中的foo字段，没有是直接报错eg:t.col4

select t.col1,t.col3 from structtest test;

tom tim

select t.col1,t.col2 from structtest test;

tom mary

select t.col1,t.col2,t.col3 from structtest test;

tom mary tim

### 分割字符串函数: split ###

select split('abtcdtef','t') from tb1;

\["ab","cd","ef"\]

### json解析函数：get\_json\_object ###

select get\_json\_object('\{"store":\{"fruit":\\\[\{"weight":8,"type":"apple"\},\{"weight":9,"type":"pear"\}\],"bicycle":\{"price":19.95,"color":"red"\}\},"email":"amy@only\_for\_json\_udf\_test.net","owner":"amy"\}','$.owner')from tb1;

amy

amy@only\_for\_json\_udf\_test.net

[amy@only][amy_only]\_for\_json\_udf\_test.net

### URL解析函数：parse\_url ###

明：返回URL中指定的部分。partToExtract的有效值为：HOST,PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.

select parse\_url('http://facebook.com/path1/p.php?k1=v1&k2=v2\#Ref1','HOST') from tb1;

facebook.com

select parse\_url('http://facebook.com/path1/p.php?k1=v1&k2=v2\#Ref1','USERINFO') from tb1;

分隔符处理‘\\001’

‘\\**001′****是hive中默认的分隔符,hive对该字符有些特殊的处理.**

select concat('a','\\001','b') fromtb1;

load data local inpath '/home/hadoop/testdata/hive/ml-100k/u.data' overwrite into table u\_data;

python脚本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)rowformat delimited fields terminated by '\\t' stored as textfile;

add file /home/hadoop/testdata/hive/weekday\_mapper.py;

hive>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;

insert overwrite table u\_data\_new selecttransform(userid.movieid,rating,unixtime) using 'pythonweekday\_mapper.py' as (userid,movieid,rating,weekday) from u\_data;

select weekday,count(\*) from u\_data\_new group by weekday;

[amy_only]: mailto:amy@only