一、创建数据库
hive>create database mydb ; //hql和mysql语法极其相似
hive>show databases;//查看所有数据库
二、创建表
格式:
create [external] table <表名>(列的定义,....) [row format delimited fields terminated by ' ' lines terminated by ' ']
举例:
hive>use mydb;//使用数据库
hive>create table player(id int,name string,club string);//默认创建的是内部表
hive>show tables;//查看该数据库下的所有表
hive>desc player;//查看表结构
hive>desc formatted player;//看到格式化的详细信息,比如:表的类型,是内部表(管理表)还是外部表
说明:
整个数据库的数据分两块,数据库的元信息(表结构等)存储在RDBMS上的一个数据库中,如mysql,数据直接存储在hdfs上。
1、在mysql上查看hive数据库的元信息
mysql>use hive;//hive是在mysql中事先创建好的数据库,而数据库名hive则是在配置hive外模式数据库mysql时指定的,在hive-site.xml配置文件中。
mysql>select * from dbs;//查看hive中创建的所有的数据库
mysql>select * from tbls;//查看hive中创建的所有表
mysql>select * from columns_v2;//查看hive中创建的所有字段
2、在hdfs上查看数据
$>hdfs dfs -ls -R /user/hive/warehouse/mydb.db/player/
-rwxr-xr-x 2 root supergroup 18 2018-09-10 16:37 /user/hive/warehouse/mydb.db/player/000000_0
//mydb.db和player是数据库和表在hdfs上对应的文件夹,000000_0是数据文件,可以使用cat查看内容
三、查询数据
格式:select <字段> from <表名>
说明:和SQL类似
举例:hive>select * from player
四、向表中添加数据
格式:
load data [local] inpath '文件路径' [overwrite] into table <表名>
说明:
[local]表示local是个可选项,如果要导入的数据文件在本地,需加local;如果数据文件在hdfs上,则无需加local
[overwrite]表示覆盖,即将原表中的数据全部删除。
举例:
hive>load data local inpath '/root/mytemp/player_data' into table player; //player_data的数据是结构化的数据,和player表中字段一一对应。
五、修改/删除
hive不支持
但可以对其对应的hdfs的数据进行修改或删除操作
六、创建外部表
举例:
hive>create external table student(id int,name string) row format delimited fields terminated by ' ' location '/mydata/external'
说明:
(1)和创建内部表相比多了红字部分。
(2)location指定的位置是hdfs上的路径
(3)创建外部表的不会移动数据,仅在元数据中记录数据的位置。外部表和内部表最大的区别,外部表删除表(drop table)时,只是删除表的元数据,并不删除hdfs上的数据,而内部表删除时,元数据和数据都会删除。
七、分区表
举例:
hive>create table person(id int,name string) partitioned by (city string) row format delimited fields terminated by ' '
结果:
id int
name string
city string
# Partition Information
# col_name data_type comment
city string
说明:
(1)注意partitioned的位置,要在row format之前。
(2)注意原表中的字段只有id和name
1、查看分区:
hive>show partitions person;
2、为分区表加载数据
(1)使用load data加载数据
hive>load data local inpath '/root/mytemp/wuhu.txt' into table person partition(city='wuhu');
hive>select * from person;
201801 zhangsan wuhu //wuhu是分区的名字,表示该记录在wuhu这个分区里,并不是表中的字段
201802 lisi wuhu
在hdfs上查看
$>hdfs dfs -ls -R /user/hive/warehouse/mydb.db/person
drwxr-xr-x - root supergroup 0 2020-02-29 16:07 /user/hive/warehouse/mydb.db/person/city=wuhu
-rwxr-xr-x 2 root supergroup 28 2020-02-29 16:07 /user/hive/warehouse/mydb.db/person/city=wuhu/wuhu.txt
$>hdfs dfs -cat /user/hive/warehouse/mydb.db/person/city=wuhu/wuhu.txt
201801 zhangsan
201802 lisi
hive>select * from person where city='wuhu'
说明:
分区以后,表中的数据结构不变,只是表中的数据分在不同的hdfs目录文件中。'分区字段=<值>'就是相应的目录。或者说通过文件夹将数据分开。
表中数据分散在不同的文件的好处,就可以避免全表扫描。比如,我要找'wuhu'市的数据,只要在wuhu.txt文件中查找就行了,我要找'hefei'市的数据,只要到hefei.txt查找就行了,这样缩小了搜索的范围。只是在前期存储的时候要分区存储,看起来比较麻烦,但带来的好处是查询的速度提升了。
(2)从一个查询结果中加载数据到分区
格式:insert [overwrite] table <表名> partition(分区名='分区值') select <查询>
举例:insert overwrite table person partition(city='bengbu') select id,name from student;//会转成MR程序执行
结果:
$>hdfs dfs -ls -R /user/hive/warehouse/mydb.db/person
drwxr-xr-x - root supergroup 0 2020-02-29 16:42 /user/hive/warehouse/mydb.db/person/city=bengbu
-rwxr-xr-x 2 root supergroup 20 2020-02-29 16:42 /user/hive/warehouse/mydb.db/person/city=bengbu/000000_0 //000000_0是数据文件
drwxr-xr-x - root supergroup 0 2020-02-29 16:07 /user/hive/warehouse/mydb.db/person/city=wuhu
-rwxr-xr-x 2 root supergroup 28 2020-02-29 16:07 /user/hive/warehouse/mydb.db/person/city=wuhu/wuhu.txt
作业:
1、在linux本地节点上创建文本文件anhui.txt,保存安徽人的数据,并输入数据如下:
1801 刘备
1802 关羽
2、在linux本地节点上创建文本文件jiangsu.txt,保存江苏人的数据,并输入数据如下:
1901 孙权
1902 周瑜
3、在hive中创建分区表person(id,name),分区的列为province,string类型。
4、将anhui.txt的数据导入到person表的province=anhui分区
5、将jiangsu.txt的数据导入到person表的province=jiangsu分区
6、分别查看person表的所有数据、安徽省的数据、江苏省的数据,并作一张截图。
7、使用hadoop的webUI查看hdfs中person目录,并作一张截图。
八、分桶表
1、创建分桶表
格式:create table <表名>(列的定义,...) clustered by (分桶字段) into <n> buckets
举例:create table bucketed_student(id int,name string) clustered by (id) into 4 buckets stored as orc;
//ORC格式属于hive的RCFILE的升级版,一种行列存储相结合的存储方式。首先,其将数据按行分块,保证同一个record在一个块上,避免读一个记录需要读取多个block。其次,块数据列式存储,有利于数据压缩和快速的列存取。
2、导入数据
insert into table bucketed_student select id,name from student;
//说明只有TextFile格式的表才能使用load data导入数据文件。
//查看hdfs
$>hdfs dfs -ls -R /user/hive/warehouse/mydb.db/bucketed_student;
-rwxr-xr-x 2 root supergroup 19 2020-02-29 19:16 /user/hive/warehouse/mydb.db/bucketed_student/000000_0 //取余4为0
-rwxr-xr-x 2 root supergroup 31 2020-02-29 19:16 /user/hive/warehouse/mydb.db/bucketed_student/000001_0 //取余4为1
-rwxr-xr-x 2 root supergroup 29 2020-02-29 19:16 /user/hive/warehouse/mydb.db/bucketed_student/000002_0 //取余4为2
-rwxr-xr-x 2 root supergroup 20 2020-02-29 19:16 /user/hive/warehouse/mydb.db/bucketed_student/000003_0 //取余4为3
3、查询数据
hive>select * from bucketed_user tablesample(bucket 1 out of 16 on id) ; //1表示从1开始分配,抽取第1桶数据的比例为4/16,即抽取第1桶1/4的数据。
1008 bob
hive> select * from bucketed_student tablesample(bucket 1 out of 4 on id) ;
1008 bob
1004 rose
hive>select * from bucketed_student;
//总数据
1001 mike #第2桶,即余数为1,对应的文件为000001_0
1002 john #第3桶,即余数为2,对应的文件为000002_0
1003 mike #第4桶,即余数为3,对应的文件为000003_0
1004 rose #第1桶,即余数为0,对应的文件为000000_0
1005 joan #第2桶,即余数为1,对应的文件为000001_0
1006 davi #第3桶,即余数为2,对应的文件为000002_0
1007 alex #第4桶,即余数为3,对应的文件为000003_0
1008 bob #第1桶,即余数为0,对应的文件为000000_0
1009 kitty #第2桶,即余数为1,对应的文件为000001_0
1010 tom #第3桶,即余数为2,对应的文件为000002_0
小结:
桶是比表或分区更为细粒度的数据范围划分。针对某一列进行桶的组织,对列值哈希,然后除以桶的个数求余,决定将该条记录存放到哪个桶中。
把表(或者分区)组织成桶(Bucket)有两个理由:
(1)获得更高的查询处理效率。桶为表加上了额外的结构,Hive 在处理有些查询时能利用这个结构。具体而言,连接两个在相同列(包含连接列的)上划分了桶的表,可以使用 Map 端连接 (Map-side join)高效的实现。比如JOIN操作:对于JOIN操作两个表有一个相同的列,如果对这两个表都进行了桶操作,那么将保存相同列值的桶进行JOIN操作就可以,可以大大减少JOIN的数据量
(2)使取样(sampling)更高效。在处理大规模数据集时,在开发和修改查询的阶段,如果能在数据集的一小部分数据上试运行查询,会带来很多方便。
分桶:如果我们根据某列进行分桶,意思就是对这列的值进行hash,然后除以桶的个数再决定把这个值放到哪个桶中,当我们查询数据的时候,where 分桶列=“” 首先也会对这个条件的值进行hash,找到他所在的桶,这样的话 其他桶就不会再找避免暴力扫描,速度上也会提升
和分区区别:分区是增加实际目录,每增加一个分区就会多个目录,分桶时把一个大的文件分成多个小文件。
分桶表的作用:最大的作用是用来提高join操作的效率;但是两者的分桶数要相同或者成倍数。