Hive基本语法使用

 

 

1 创建表以及表的关联查询

2 外部表的使用 

3 复杂数据类型的使用 

4 分区的使用

5 桶的使用与理解 

6 官方文档

https://cwiki.apache.org/confluence/display/Hive/Home

一、 创建表以及表的关联查询

1) 创建表

hive> create table student(name STRING,age INT)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ESCAPED BY '\\' STORED AS TEXTFILE;

hive> create table ticket(age INT,price FLOAT)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ESCAPED BY '\\' STORED AS TEXTFILE;

 

2）创建本地数据文件 student.txt 和ticket.txt，并加载到hive的数据表中

 内容：

 student.txt文件

huang26

lili25

dongdong27

wangxiao5

 

ticket.txt文件,加载到数据库中每一行多的数据会被忽略掉，少得数据会变成null

2619.0qwe

2510.0er

27

590.0

 

3）加载数据 

 LOAD DATA LOCAL INPATH '/root/student.txt' OVERWRITE INTO TABLE student;

 查看数据是否正确加载： select * from student ;

LOAD DATA LOCAL INPATH '/root/ticket.txt' OVERWRITE INTO TABLE ticket;   

 查看数据是否正确加载： select * from ticket ;

 

4)查看表以及表结构

hive> show tables;

hive> describe student;

 

5） 两个表连接查询（hive编译成MapReduce程序，在hadoop上执行）

 select * from student s  join ticket t on s.age=t.age;

 

 

二、外部表的使用 （对于外部表的删除数据文件不会被删除，只会删除元数据）

1）创建表的文件数据目录

-bash-3.2# hadoop fs -mkdir /tmp/ticket

-bash-3.2# hadoop fs -put /root/person.txt  /tmp/ticket 

-bash-3.2# hadoop fs -put /root/ticket.txt /tmp/ticket

-bash-3.2# hadoop fs -ls /tmp/ticket

2）创建外部表

 create external table student_ext(name STRING,age INT)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ESCAPED BY '\\' STORED AS TEXTFILE LOCATION '/tmp/ticket'

3) 数据确认

  select * from student_ext 查询到上面两个文件内容全都倒表student_ext中了

  结论：一个路径对应的应该是一个表关联的数据文件， 有其他的文件，则默认文件中数据也会导入到

  这个外部表中，没数据的列会为NULL，多出来的数据会被忽略 

4）

 

三、复杂数据类型的使用  

这里列之间以'\t'分割，数组元素之间以','分割 

数据文件内容：

huangfengbeijing,shanghai,tianjin,guangzhou

linanchangchu,chengdu,wuhan

 

hive> create table complex(name string,work_locations array<string>)

       ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

       COLLECTION ITEMS TERMINATED BY ',';

 

加载数据：

LOAD DATA LOCAL INPATH '/root/complex.txt' OVERWRITE INTO TABLE complex 

确认数据是否在：

select * from complex; 

 

查询数组第一个数据：

select name,work_locations[0] from complex; 

结果：

huangfengxiao   beijing

linan   changchu

 

四、分区使用 

 Mis li huangfengxiao 20

 Mis li lijie 21

 Mis li dongdong 21

 Mis li liqiang 21

 Mis li hemeng 21

 Mr xu dingding 19

 Mr xu wangqiang 19

 Mr xu lidong 19

 Mr xu hexing 19

1) 用处：上面数据  是老师 、学生、年龄 ，如果按教师分区的话，在查询的时候可以直接分区查询  

 

2) 分区文件：

 classmem_Misli.txt

dingding19

wangqiang19

lidong19

hexing19

 classmem_MrXu.txt

dingding19

wangqiang19

lidong19

hexing19

 

3) 创建表

create table classmem(student string,age int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ESCAPED BY '\\'  partitioned by(teacher string)

 

3) 数据导入-分区名称为查询表的最后一列，执行select * from classmem;

 LOAD DATA LOCAL INPATH '/root/classmem_Misli.txt' overwrite INTO TABLE classmem partition (teacher = 'Mis.li')  ;

 LOAD DATA LOCAL INPATH '/root/classmem_MrXu.txt' overwrite INTO TABLE classmem partition (teacher = 'Mis.Xu');  

 

4） 按分区进行数据查询 

  select * from classmem where teacher='Mis.Xu';   

 

五、桶的使用 

桶的原理是对一个表（或者分区）进行切片，选择被切片的字段，设定桶的个数，用字段与个数的hash值进行入桶。

比如bucket.txt数据文件内容如下：

id name age

1 huang 11

2 li 11

3 xu 12

4 zhong 14

5 hu 15

6 liqiang 17

7 zhonghua 19

如果我们想将这个数据表切成3个桶，切片字段为id

那么用id字段hash后，3个桶的内容如下：

桶id hash 3 =0

3 xu 12

6 liqiang 17

桶id hash 3 =1

1 huang 11

4 zhong 14

7 zhonghua 19

桶id hash 3 =2

2 li 11

5 hu 15

这个过程的创建表语句如下：

create table bucketmem (id int,name string,age int) CLUSTERED BY (id) sorted by (id asc) into 3 buckets

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

 

LOAD DATA LOCAL INPATH '/home/hadoop/hfxdoc/bucketmem.txt' INTO TABLE bucketmem;

select * from bucketmem tablesample(bucket 1 out of 4)

 

六、其他基本语句 

 1） 更改名称

alter table old_table_name RENAME TO new_table_name ;

 2）更改表名称

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type[COMMENT col_comment] 

 

 3）添加一个字段或者替换掉表中所有字段

     ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)   

     ADD是代表新增一字段，字段位置在所有列后面(partition列前);REPLACE则是表示替换表中所有字段

 

 

 七、在hive中查看hdfs文件

dfs -ls /user;

 

 

 八、时间处理函数 

  1） 字符串类型时间戳转换成日期

   select from_unixtime(cast('1326988805' as int),'yyyyMMddHH');  2012011908

  2）得到当前时间 

     select unix_timestamp()

  3)日期类型转换为时间戳  

select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss')

        select unix_timestamp(’2011-12-07 13:01:03′) 

   4)取日期的某一部分  hour,minute,second,weekofyear(当前是这个 年的第几周)

select to_date('2011-12-08 10:03:01′)  ;    --2011-12-08

select year('2011-12-08 10:03:01');--2011

select month('2011-12-08 10:03:01');

select day('2011-12-08 10:03:01'); 

select datediff(’2012-12-08′,’2012-05-09′)  查询时间差 

date_add(string startdate, int days)  日期添加天数 

date_sub (string startdate, int days)  日期减去天数