Hive学习笔记（3）表

作者: 井地儿 | 来源:发表于2019-04-02 18:04 被阅读0次

Hive的表在逻辑上由存储的数据和描述表中数据形式的相关元数据组成。数据通常存储在HDFS中，元数据通常保存在关系型数据库中。

数据库

Hive中也由database的概念，本质是在HDFS中的一个目录。

创建数据库

create database dbname;

切换数据库

use dbname;

删除数据库

drop database dbname;

全限定指定表

dbname.tablename

默认库

在不指定数据库时，hive默认使用default数据库。

托管表和外部表

默认情况下，表数据由Hive负责管理，hive会把数据文件移动到仓库目录下，我们把这种表称为托管表，也叫内部表。
我们也可以指定Hive从仓库目录以外的位置访问数据，这种表我们称为外部表。

托管表的创建和数据加载

//创建
create table managed_table (name string);
//加载数据
load data inpath '/user/stefan/data.txt' into table managed_table;
//删除表
drop table managed_table;

这里创建的表为托管表（内部表），加载数据的过程实际上是hive将数据文件移动到仓库目录下的对应表目录下，而删除表，hive不光会删除元数据信息，还会删除对应的数据文件。所以，托管表（内部表）是完全将数据交给了Hive来管理。如果误删除了表，相应的数据也会丢失。

外部表的创建和数据加载

//创建
create external table external_table (name string) location '/user/stefan/external_table';
//加载数据
load data inpath '/user/stefan/data.txt' into table external_table;
//删除表
drop table managed_table;

通过external关键字来指定当前表是外部表，注意外部表在drop表时，只会删除元数据信息，并不会删除数据文件。

托管表和外部表如何选择？

可以看到托管表和外部表最大的区别在于drop表时，是否会删除数据。我们知道drop表是一个高危的操作，通常情况下应该严格控制drop操作。如果我们采用外部表，在一定程度上也提高了数据的可恢复性，挽回损失。另外，外部表的数据通常可以方便其他工具来处理相同的数据。不过外部表同时也增加了数据维护的成本和复杂性。
在ETL过程中，我们可以将初始来源数据设置为外部表，然后中间的数据加工整体依赖于托管表，最后输出的时候，可以再变换为外部表供其他工具等使用。
案例：将mysql数据同步到hive，进行加工后，再同步到mysql供线上系统使用。方案：将mysql数据导入至hdfs，然后加载到hive外部表中进行一系列的加工处理之后，写入外部表作为输出。其中，mysql2hdfs（hive）和hdfs（hive）2mysql都采用datax开源工具完成。

分区和桶

Hive表分区（pattition）是根据分区列的值对表进行粗略划分的，具体表现为对应的分区对应一个具体的目录。
Hive表或分区还可以进一步划分为桶（bucket），桶是在数据的基础上提供额外的结构以获得更高效的查询处理。

分区（partition）

我们最常见的分区方式有根据时间和省份或者国家来划分分区。
分区的定义：

//创建分区表
create table partition_table (id int, name string) partitioned by (dt string, country string);
//加载数据
load data local inpath '/user/home/stefan/partition_table_data.txt' into table partition_table partition ( dt = '20190402', country = 'china');

hive> create table partition_table (id int, name string) partitioned by (dt string, country string);
OK
Time taken: 1.195 seconds
hive> show tables;
OK
first_table
partition_table
Time taken: 1.302 seconds, Fetched: 2 row(s)
hive> desc partition_table;
OK
id      int
name                    string
dt                      string
country                 string

# Partition Information
# col_name              data_type               comment

dt                      string
country                 string
Time taken: 2.638 seconds, Fetched: 9 row(s)
hive> load data local inpath '/home/hadoop/stefan/test/partition_table_data.txt' into table partition_table partition (dt = '20190402', country = 'china');
Loading data to table default.partition_table partition (dt=20190402, country=china)
OK
Time taken: 1.944 seconds
hive> select * from partition_table;
OK
1  杨康   20190402    china
2  郭靖   20190402    china
3  黄蓉   20190402    china
Time taken: 1.612 seconds, Fetched: 3 row(s)

查看分区数据目录结构

查看当前表分区

show pattitions tablename;

 hive> show partitions partition_table;
OK
dt=20190402/country=china
Time taken: 0.449 seconds, Fetched: 1 row(s)

查看数据存储位置

show create table table_name;

其中location指定了当前表数据在hdfs中的存储位置。

hive> show create table partition_table;
OK
CREATE TABLE `partition_table`(
  `id` int, `name` string)
PARTITIONED BY (
  `dt` string,
  `country` string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://jms-master-01:9000/user/hive/warehouse/partition_table'
TBLPROPERTIES (
  'transient_lastDdlTime'='1554190131')
Time taken: 0.218 seconds, Fetched: 15 row(s)

查看分区表目录结构

hadoop fs -ls -R locationpath;

通过 hadoop fs ls -R 命令我们可以看出分区表的数据目录结构。

[hadoop@jms-master-01 ~]$ hadoop fs -ls -R /user/hive/warehouse/partition_table
19/04/02 15:49:26 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
drwxr-xr-x   - hadoop supergroup          0 2019-04-02 15:34 /user/hive/warehouse/partition_table/dt=20190402
drwxr-xr-x   - hadoop supergroup          0 2019-04-02 15:34 /user/hive/warehouse/partition_table/dt=20190402/country=china
-rwxr-xr-x   3 hadoop supergroup         21 2019-04-02 15:34 /user/hive/warehouse/partition_table/dt=20190402/country=china/partition_table_data.txt

关于分区列

partitioned by指定的列在hive表中是正式的列，称为分区列（partition column）。数据文件中是不包含这些列的值的，分区列源自于目录名。

桶

桶（bucket）的功能：一是提高查询处理效率；二是数据取样更高效。

桶的定义

//创建
create table bucketed_table (id int, name string) clustered by (id) into 4 buckets;
//向桶表中插入数据
insert overwriter table bucketed_table select id, name from partition_table;

向桶表中添加数据时，启动了一个MR任务来散列数据。

hive> insert overwrite table bucketed_table select id, name from partition_table;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = hadoop_20190402172813_54c4fc16-0b17-4e5e-af95-2308156acf88
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 4
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1552651623473_0004, Tracking URL = http://jms-master-01:8088/proxy/application_1552651623473_0004/
Kill Command = /home/hadoop/tools/hadoop-2.7.7/bin/hadoop job  -kill job_1552651623473_0004
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 4
2019-04-02 17:28:23,843 Stage-1 map = 0%,  reduce = 0%
2019-04-02 17:28:29,339 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.61 sec
2019-04-02 17:28:36,812 Stage-1 map = 100%,  reduce = 50%, Cumulative CPU 7.4 sec
2019-04-02 17:28:37,860 Stage-1 map = 100%,  reduce = 75%, Cumulative CPU 10.36 sec
2019-04-02 17:28:38,987 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 12.99 sec
MapReduce Total cumulative CPU time: 12 seconds 990 msec
Ended Job = job_1552651623473_0004
Loading data to table default.bucketed_table
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1  Reduce: 4   Cumulative CPU: 12.99 sec   HDFS Read: 19374 HDFS Write: 393 SUCCESS
Total MapReduce CPU Time Spent: 12 seconds 990 msec
OK
Time taken: 27.706 seconds

桶表的每个桶就是表数据目录下的一个文件。其实一个桶对应一个MapReduce的输出文件分区：一个作业产生的桶和reduce任务个数相同。

[hadoop@jms-master-01 test]$ hadoop fs -ls hdfs://jms-master-01:9000/user/hive/warehouse/bucketed_table
19/04/02 17:30:12 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Found 4 items
-rwxr-xr-x   3 hadoop supergroup         24 2019-04-02 17:28 hdfs://jms-master-01:9000/user/hive/warehouse/bucketed_table/000000_0
-rwxr-xr-x   3 hadoop supergroup         18 2019-04-02 17:28 hdfs://jms-master-01:9000/user/hive/warehouse/bucketed_table/000001_0
-rwxr-xr-x   3 hadoop supergroup         18 2019-04-02 17:28 hdfs://jms-master-01:9000/user/hive/warehouse/bucketed_table/000002_0
-rwxr-xr-x   3 hadoop supergroup         21 2019-04-02 17:28 hdfs://jms-master-01:9000/user/hive/warehouse/bucketed_table/000003_0
[hadoop@jms-master-01 test]$ hadoop fs -cat hdfs://jms-master-01:9000/user/hive/warehouse/bucketed_table/000000_0
19/04/02 17:30:29 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
8杨铁心
4穆念慈

分桶规则：分桶字段除以桶数取余。

桶表的取样

通过tablesample子句对表取样。

TABLESAMPLE (BUCKET x OUT OF y [ON colname])

说明：colname表明抽取样本的列，可以是非分区列中的任意一列，或者使用rand()表明咋整个行中抽取样本而不是单个列。在colname上分桶的行随机进入1到y个桶中，返回属于桶x的行。举个例子：

//将数据随机进入20个桶中，返回第三个桶的数据
TABLESAMPLE (BUCKET 3 OUT OF 20 rand())

hive> select * from bucketed_table tablesample(bucket 1 out of 4 on id);
OK
8   杨铁心
4   穆念慈
Time taken: 0.17 seconds, Fetched: 2 row(s)
hive> select * from bucketed_table tablesample(bucket 1 out of 2 on id);
OK
8   杨铁心
4   穆念慈
6   西毒
2   黄蓉
Time taken: 0.093 seconds, Fetched: 4 row(s)