Hive下 HQL使用（库、表、区）

准备：

Mysql&Hive:Mysql&Hive:基于Hive的伪分布式Hadoop集群环境的配置的博客

hive使用(HQL)

不用进入hive的客户端，可以使用如下命令来查看

$ hive -e 'show databases'

数据库操作

创建数据库：create database db;

如果数据库不存在就创建： create database if not exists db;

在指定的路径下创建数据库：create database db location '/my/directory';

创建数据库，并为数据库添加描述信息：

create database db comment 'it is my database'

with dbproperties('creator'='zhangsan','date'='2018-01-10');

查看数据库：show databases;  

查看创建数据库sql语句： show create database school;

查询以hive开头的数据库：show databases like 'hive*';  

查看数据库hive结构：desc database hive;  

查看数据库hive的详细描述信息：desc database extended hive;  

切换数据库：use default;   

删除数据库：drop database if exists school cascade;

表操作

创建表： create table student(表字段) row format delimited fields terminated by ',';

重命名表：alter table 原表名 rename to 新表名;

删除表：drop table if exists 表名;

查看当前数据库的所有表：show tables;  

查看当前数据库中以emp开头的表：show tables 'emp*';  

查看表emp的结构：desc emp;  

查看表emp的详细描述信息：desc extended emp;  

查看表emp的详细描述信息（详细信息以特定的格式展现）：desc formatted emp;  

导入数据：load data local inpath '导入数据的文件路径' into table 表名;

内部表和外部表的区别

内部表：managed_table

在删除时，HDFS数据被删除，MySQL元数据也被删除

外部表：external

在删除时，HDFS数据不被删除，MySQL元数据被删除

将emp中的数据导出到本地系统的指定目录下

insert overwrite local directory '/home/hadoop/dataFile/tmp.txt'

row format delimited fields terminated by '\t' select * from emp;

用hdfs命令将数据库表emp中的数据导出到本地文件系统的当前目录下

hadoop fs -get /user/hive/warehouse/emp

================================================================

Hive分区表

静态分区：单级分区、多级分区

 

创建单级分区表 T_USER：

要求按性别分区

按年龄分桶到3个桶中进行排序

create table T_USER(

name string,

age int

)

partitioned by(sex string)

clustered by (age) sorted by (age asc) into 3 buckets;

增加数据

insert into table T_USER partition(sex='man') values

('张三',18),

('李四',17),

('王五',16),

('赵六',15);

加载本地数据到到分区表

load data local inpath '/home/hadoop/text.txt' overwrite into table T_USER partition(event_month='2014-05');

在hdfs上创建分区目录

hadoop fs -mkdir -p /user/hive/warehouse/T_USER/event_month=2014-06

将本地数据上传到hdfs上创建好的分区目录

hadoop fs -put order.txt /user/hive/warehouse/T_USER/event_month=2014-06

 

刷新表 T_USER

Msck repair table T_USER;

创建多级分区表

order_multi_partition

create table order_multi_partition(

order_number string,

event_time string

)

PARTITIONED BY(event_month string, step string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

 

加载本地数据到多级分区表中

load data local inpath '/home/hadoop/data/order.txt'

overwrite into table order_multi_partition partition(event_month='2014-05',step='1');

 

load data local inpath '/home/hadoop/data/order.txt'

overwrite into table order_multi_partition partition(event_month='2014-05',step='2');

 

 

创建数组表 hive_array

create table hive_array(

name string,

work_locations array<string>

)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

COLLECTION ITEMS TERMINATED BY ',';

 

加载本地数据到数组表

load data local inpath '/home/hadoop/data/hive_array.txt'

overwrite into table hive_array ;

 

查询数组的大小

select name,size(work_locations) from hive_array;

 

创建map表 hive_map

create table hive_map(

name string,

scores map<string,int>

)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

COLLECTION ITEMS TERMINATED BY ','

MAP KEYS TERMINATED BY ':';

 

加载本地数据到 hive_map

load data local inpath '/home/hadoop/data/hive_map.txt'

overwrite into table hive_map ;

查询学生的英语成绩

select name,scores[english] from hive_map;

 

创建结构体表hive_struct

create table hive_struct(

ip string,

userinfo struct<name:string,age:int>

)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '#'

COLLECTION ITEMS TERMINATED BY ':';

 

加载本地数据到表hive_struct

load data local inpath '/home/hadoop/data/hive_struct.txt'

overwrite into table hive_struct ;