Hive基础 (一)

Hive 概念

Hive是一个SQL解析引擎，也就是说Hive本身是没有存储数据以及对数据进行计算处理的能力的，它是一种MapReduce和Hadoop的封装，由Hadoop提供数据存储能力，由MapReduce提供计算能力。Hive的出现，让很多只懂SQL的工程师也可以操作大量需要具备Java能力才可以进行的MapReduce作业。

因此MapReduce和Hadoop的特点也就一并带给了Hive

• RDBMS的SQL提供增删改查(insert, delete, update, select)的能力，而HQL对改(update)支持得非常差，虽然从Hive 2.0提供了一定的改能力，但是依然不推荐进行改操作
• MapReduce的计算需要将中间结果存盘，然后进行shuffle阶段，所以Hive也继承了这种慢速批处理计算的特性

Hive数据模型:

1. 数据表
   • Table 内部表，表结构和数据都由Hive管理，当Hive进行删除(drop)操作的时候，表结构和数据都会被删除掉，如果要恢复已被删除的内部表，就必须重新建表，然后导入数据。
   • External Table 外部表，表结构由Hive管理，而数据则不由Hive进行管理，当Hive进行删除(drop)操作的时候，只有表结构被删除，而数据则依然保存在本地磁盘或者HDFS上，如果要恢复已被删除的外部表，只需要重新建表即可，查询的时候会自动定位到数据所在的位置
2. 分区表 Partition: 依然是数据表，只是一种不同于普通的建表方式
   • 通常在where语句中经常出现的字段比较适合做分区，分区字段的值不会太多，工作中常用的是时间、物品类型之类的字段来做分区。
   • Hive表的数据在HDFS上的体现是所有的数据都放在一个以表名命名的文件夹中，和普通的RDBMS不一样，Hive其实没有严格的索引概念，也只提供有限的索引功能，所以大部分情况下查询的时候会需要遍历文件夹中的所有文件。如果以日期字段做了分区，那么想要获取分析某天的数据，就只要对相应日期对应的分区文件夹的数据进行遍历(where条件)，大大提高了查询的效率。
3. 分桶表 Bucket: 也是数据表，只是一种不同于普通的建表方式
   • Bucket的用途经常是用于抽样
   • Hive会针对某一列进行桶的组织，通常会对列做哈希
   • 进行分桶之后，做join的时候会自动激活map端的map-site join，可以优化查询，方便采样

Hive建表

数据表

在实际操作过程中，经常会出现得到的数据源csv文件内容中是带有表头信息的，如果建表导入数据的话，就会导致有一行数据实际是表头信息。有些同学在操作的时候会选择将源数据(csv文件)的表头数据删除，然后再进行建表操作。这种操作比较危险，一般而言为了保证数据的完整、安全和有效，是不建议对源数据直接进行修改的。那么可以在建表时加入表属性"skip.header.line.count"="1"来保证在导入数据时可以忽略文件第一行数据。

hive> create table orders
hive> (
hive> order_id                string,
hive> user_id                 string,
hive> eval_set                string,
hive> order_number            string,
hive> order_dow               string,
hive> order_hour_of_day       string,
hive> days_since_prior_order  string)
hive> row format delimited fields terminated by ','
hive> tblproperties("skip.header.line.count"="1");

hive> load data local inpath '/usr/bigdata/download/orders.csv' 
hive> into table orders;

字段和字段类型之间不要用tab来分割，HQL和SQL一样，喜欢空格来对命令进行分割

这里创建的是一个普通表，如果查看表的属性，可以看到Hive默认建新表保存为Text格式

hive> show create table orders;
OK
...
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
...

Text格式的表创建简单，但是当和其他组件一起使用的时候，可能会再次出现表头的问题，比如Spark SQL，在Spark SQL中对Text格式的Hive表进行查询，会再次出现表头的问题。这种情况可以采取对表进行转储来解决，也就是建一个新表，指定存储格式为ORC或者Parquet，将Text格式的表的数据插入新表

hive> create table orders_orc
hive> (
hive> order_id                string,
hive> user_id                 string,
hive> eval_set                string,
hive> order_number            string,
hive> order_dow               string,
hive> order_hour_of_day       string,
hive> days_since_prior_order  string)
hive> row format delimited fields terminated by ','
hive> stored as orc;

hive> insert into table order_orc
hive> select * from orders;

Hive从一个表将数据载入另一张表需要用插入的方式

这时可以看到新表的存储方式为ORC格式

hive> show create table orders_orc;
OK
...
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
...

这里所建都是内部表，建外部表也很简单，create external table就可以了

hive> create external table orders_ext
hive> (
hive> order_id                string,
hive> user_id                 string,
hive> eval_set                string,
hive> order_number            string,
hive> order_dow               string,
hive> order_hour_of_day       string,
hive> days_since_prior_order  string)
hive> row format delimited fields terminated by ','
hive> tblproperties("skip.header.line.count"="1");

分区表

建分区表的时候，和建内\外部表的命令差不多，只是把需要作为分区的字段单独拿出来进行声明。

hive> create table orders_part
hive> (
hive> order_id                string,
hive> user_id                 string,
hive> eval_set                string,
hive> order_number            string,
hive> order_hour_of_day       string,
hive> days_since_prior_order  string)
hive> partitioned by (order_dow string)
hive> row format delimited fields terminated by ','
hive> tblproperties("skip.header.line.count"="1");

分区表导入数据有两种方式

1. load命令导入原始数据
   • 导入数据的时候必须要声明分区字段以及字段值，所以需要进行多次导入操作
2. insert命令从其他表插入数据
   • 静态插入:和load命令一样，必须要声明分区字段以及字段值，童谣需要进行多次插入操作
   • 动态插入:可以让Hive动态的识别分区字段值，进行分区操作，需要打开动态分区功能set hive.exec.dynamic.partition=true;和set hive.exec.dynamic.partition.mode=nonstrict;

   hive> set hive.exec.dynamic.partition=true;
   hive> set hive.exec.dynamic.partition.mode=nonstrict;
   hive> insert into table orders_part partition (order_dow) 
   hive> select order_id,user_id,eval_set,order_number,
   hive> order_hour_of_day,days_since_prior_order,order_dow from orders;

   从其他表插入数据到分区表，在select的时候分区字段要放在最后

分桶表

分桶表是无法直接使用load的方式载入数据的，所以使用分桶表，首先需要有辅助表的存在

hive> create table bucket_aux(num int);
hive> load data inpath '/usr/bigdata/download/bucket_test.txt' into table bucket_aux;

然后创建分桶表，并载入数据。创建分桶表的时候需要注意:

• 分桶参数要设置set hive.enforce.bucketing=true
• 建表的时候要说明分多少个桶

hive> set hive.enforce.bucketing=true;
hive> create table bucket_test(num int) clustered by (num) into 32 buckets;
hive> insert overwrite table bucket_test select num from bucket_aux;

然后可以在HDFS上看到分桶情况

[bigdata@master download]$ hadoop fs -ls /usr/hive/warehouse/badou.db/bucket_test
Found 32 items
-rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:45 /usr/hive/warehouse/badou.db/bucket_test/000000_0
-rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:45 /usr/hive/warehouse/badou.db/bucket_test/000001_0
-rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000002_0
-rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:45 /usr/hive/warehouse/badou.db/bucket_test/000003_0
-rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000004_0
-rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000005_0
-rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000006_0
-rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000007_0
-rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000008_0
-rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000009_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000010_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000011_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000012_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000013_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000014_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000015_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000016_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000017_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000018_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000019_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000020_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000021_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000022_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000023_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000024_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000025_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000026_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000027_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000028_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000029_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000030_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000031_0

而如果分桶为30个，情况则是这个样子的

hive> create table bucket_test_30(num int) clustered by (num) into 30 buckets;
hive> insert overwrite table bucket_test_30 select num from bucket_aux;
[bigdata@master download]$ hadoop fs -ls /usr/hive/warehouse/badou.db/bucket_test_30
Found 30 items
-rwx-wx-wx 2 bigdata supergroup 5 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000000_0
-rwx-wx-wx 2 bigdata supergroup 5 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000001_0
-rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000002_0
-rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000003_0
-rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000004_0
-rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000005_0
-rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000006_0
-rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000007_0
-rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000008_0
-rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000009_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000010_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000011_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000012_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000013_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000014_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000015_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000016_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000017_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000018_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000019_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000020_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000021_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000022_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000023_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000024_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000025_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000026_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000027_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000028_0
-rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000029_0

查看30个分桶的第0个和第1个分桶，可以看到这样的结果

[bigdata@master download]$ hadoop fs -cat /usr/hive/warehouse/badou.db/bucket_test_30/000000_0
0
30
[bigdata@master download]$ hadoop fs -cat /usr/hive/warehouse/badou.db/bucket_test_30/000001_0
31
1

也就是说，进行分桶的时候，是以数据所在行号对分桶个数进行取模运算来进行存放的。即桶号=(行号)%(分桶个数)
对分桶表进行查询，一般需要使用采样语句tablesample

hive> select * from bucket_test tablesample (bucket 1 out of 32 on num);
OK
0

这个查询语句从32个桶的第一个桶里取数据，所以取到的是0。

hive> select * from bucket_test tablesample (bucket 1 out of 9 on num);
OK
0
9
18
27

这个查询语句首先将32个桶分成4份，也就是ceiling(32/9)

• 1-9号桶为第一份，对应值为0-8
• 10-18号桶为第二份，对应值为9-17
• 19-27号桶为第三份，对应值为18-26
• 28-32号桶为第四份，对应值为27-31
  然后在每份里取第一个值

hive> select * from bucket_test tablesample (bucket 7 out of 9 on num);
OK
6
15
24

这个查询将32个同样分成4份，但是由于最后一份只有5个桶，查询不到第7个，所以只返回3个值

hive> select * from bucket_test tablesample (bucket 3 out of 35 on num);
OK
2

这个查询要将32个桶分成1份ceiling(32/35)，然后取第3个桶

转载于:https://www.cnblogs.com/LuYin/p/11070869.html