hive：建表：普通表 外部表 分区表 桶表_hive 不是分区表变为分区表

内部表：

默认数据格式为text（TEXTFILE）：

CREATE TABLE `ds.ds_ree_t_result_tmp` (
      `workno` int COMMENT '员工工号',
      `employeename` string COMMENT '姓名',
      `amount` string COMMENT '业绩',
      `effectivedate` string COMMENT '生效时间',
      `isleave` string COMMENT '是否离职',
      `leavedate` string COMMENT '离职日期',
      `tag` array<string> COMMENT '标签类型',   --这里指定是个数组类型
      `addtime` string COMMENT '订单生成时间'
);

数据格式为parquet：

CREATE TABLE `dws.test`(
  `source` string COMMENT '来源', 
  `client_id` bigint COMMENT '客户id', 
  `client_name` string COMMENT '客户名称') stored as parquet;

 

外部表

如果数据已经存在HDFS的'/user/hadoop/warehouse/page_view'上了，如果想创建表，指向这个路径，就需要创建外部表:

CREATE EXTERNAL TABLE page_view(
     viewTime INT, 
     userid BIGINT,
     page_url STRING, 
     referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User',
     country STRING COMMENT 'country of origination')
 COMMENT 'This is the staging page view table'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'
 STORED AS TEXTFILE
 LOCATION '/user/hadoop/warehouse/page_view';

创建表，有指定EXTERNAL就是外部表，没有指定就是内部表，内部表在drop的时候会从HDFS上删除数据，而外部表不会删除。

外部表和内部表一样，都可以有分区，如果指定了分区，那外部表建了之后，还要修改表添加分区。

外部表如果有分区，还可以加载数据，覆盖分区数据，但是外部表删除分区，对应分区的数据不会从HDFS上删除，而内部表会删除分区数据。

外部表删除时，不会删除数据，这是与内部表的最大区别，一般80%企业都是 外部表。

指定位置 ，必须在hdfs之上：

自己先创建目录 ：

文件如果 直接放到目录下面去，然后就可以直接查询出来，这样的方式是最快最省事的！

可以先创建表目录再放文件也可以。

分区表：

 

指定分区字段，以下示例是按月分区（可以多级分区也就是按多个字段分区）：

加载数据：

对分区的数据进行查询：

 实际业务场景如pvuv按日统计 按月统计。

多级分区：

 

 

继续进行测验：

通过将文件直接放到创建的表的hdfs目录中去。

 

再创建一张表：

第一种方式：

创建目录并将数据放到hdfs上去：

 

但是数据并不能被查出来：

查询mysql中的hive的元数据，并没有发现刚分区的信息。

解决方法：

能查出数据了。这是 分区表的特点，一定要进行这个修复。

 

第二种方式：

再增加一个 分区：

 

依然需要修复：

企业中一般也都是脚本里创建目录，将文件上传至目录，添加分区，这个过程。

 

查看表的分区：

查看分区结构：

 

 

like复制表结构创建表

CREATE TABLE empty_table_name LIKE table_name;
根据table_name创建一个空表empty_table_name,empty_table_name没有任何数据。

 

查询结果建表

CREATE TABLE new_key_value_store
  AS 
SELECT (key % 1024) new_key, concat(key, value) key_value_pair FROM key_value_store;

复杂的方式?

CREATE TABLE new_key_value_store
   ROW FORMAT SERDE "org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe"
   STORED AS RCFile AS
SELECT (key % 1024) new_key, concat(key, value) key_value_pair
FROM key_value_store
SORT BY new_key, key_value_pair;

创建有分区的表

create table table_name (
  id                int,
  dtDontQuery       string,
  name              string
)
partitioned by (date string)

一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下。

分区是以字段的形式在表结构中存在，通过describe table命令可以查看到字段存在，但是该字段不存放实际的数据内容，仅仅是分区的表示。

在Hive Select查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入了partition概念。表中的一个 Partition 对应于表下的一个目录,Partition 就是辅助查询，缩小查询范围，加快数据的检索速度和对数据按照一定的规格和条件进行管理。

 

其他：

1. 普通表
    普通表的创建，如上所说，不讲了。其中，一个表，就对应一个表名对应的文件。

2. 外部表
    EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。具体sql如下：

Sql代码  

1. CREATE EXTERNAL TABLE test_1(id INT, name STRING, city STRING) SORTED BY TEXTFILE ROW FORMAT DELIMITED‘\t’ LOCATION ‘hdfs://../../..’  

CREATE EXTERNAL TABLE test_1(id INT, name STRING, city STRING) SORTED BY TEXTFILE ROW FORMAT DELIMITED‘\t’ LOCATION ‘hdfs://../../..’

3. 分区表
    有分区的表可以在创建的时候使用 PARTITIONED BY 语句。一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下。而且，表和分区都可以对某个列进行 CLUSTERED BY 操作，将若干个列放入一个桶（bucket）中。也可以利用SORT BY 对数据进行排序。这样可以为特定应用提高性能。具体SQL如下：

Sql代码  

1. CREATE TABLE test_1(id INT, name STRING, city STRING) PARTITIONED BY (pt STRING) SORTED BY TEXTFILE ROW FORMAT DELIMITED‘\t’   

CREATE TABLE test_1(id INT, name STRING, city STRING) PARTITIONED BY (pt STRING) SORTED BY TEXTFILE ROW FORMAT DELIMITED‘\t’ 

 

Hive的排序，因为底层实现的关系，比较不同于普通排序，这里先不讲。

分区表实际是一个文件夹，表名即文件夹名。每个分区，实际是表名这个文件夹下面的不同文件。

分区可以根据时间、地点等等进行划分。

比如，每天一个分区，等于每天存每天的数据；或者每个城市，存放每个城市的数据。

每次查询数据的时候，只要写下类似 where pt=2010_08_23这样的条件即可查询指定时间得数据。

总体而言，普通表，类似mysql的表结构，外部表的意义更多是指数据的路径映射。分区表，是最难以理解，也是最hive最大的优势。之后会专门针对分区表进行讲解。

 

桶表：

桶的概念，主要是为性能考虑，可以理解为对分区内列，进行再次划分，提高性能。在底层，一个桶其实是一个文件。如果桶划分过多，会导致文件数量暴增，一旦达到系统文件数量的上限，就杯具了。哪种是最优数量，这个哥也不知道。

桶表是对数据进行哈希取值，然后放到不同文件中存储。

数据加载到桶表时，会对字段取hash值，然后与桶的数量取模。把数据放到对应的文件中。
物理上，每个桶就是表(或分区）目录里的一个文件，一个作业产生的桶(输出文件)和reduce任务个数相同。

作用

桶表专门用于抽样查询，是很专业性的，不是日常用来存储数据的表，需要抽样查询时，才创建和使用桶表。

实验

创建

[22:39:03]hive (zmgdb)> create table bucket_t1(id string)
[22:39:26]            > clustered by(id) into 6 buckets;
[22:39:27]OK
[22:39:27]Time taken: 0.546 seconds
clustered by：以哪个字段分桶。对id进行哈希取值，随机  地放到4个桶里。

-----------------------------

准备数据

[root@hello110 data]# vi bucket_test
1
2
3
4
5
6

.............

.........

导入数据

正确的导入方式：从日常保存数据的表insert

[21:27:45]hive (zmgdb)> create table t2(id string);
[21:27:45]OK
[21:27:45]Time taken: 0.073 seconds
[21:28:24]hive (zmgdb)> load data local inpath '/data/bucket_test' into table t2;
[21:28:24]Loading data to table zmgdb.t2
[21:28:25]OK

从日常表导入

[22:39:47]hive (zmgdb)> insert overwrite table bucket_t1 select id from t2;

hive会启动mapreduce
[22:39:48]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.
[22:39:48]Query ID = hadoop_20160922063946_34bf30c4-3f23-43e9-ad8f-edd5ee214948
[22:39:48]Total jobs = 1
[22:39:48]Launching Job 1 out of 1
[22:39:48]Number of reduce tasks determined at compile time: 6
[22:39:48]In order to change the average load for a reducer (in bytes):
[22:39:48]  set hive.exec.reducers.bytes.per.reducer=<number>
[22:39:48]In order to limit the maximum number of reducers:
[22:39:48]  set hive.exec.reducers.max=<number>
[22:39:48]In order to set a constant number of reducers:
[22:39:48]  set mapreduce.job.reduces=<number>
[22:39:51]Starting Job = job_1474497386931_0001, Tracking URL = http://hello110:8088/proxy/application_1474497386931_0001/
[22:39:51]Kill Command = /home/hadoop/app/hadoop-2.7.2/bin/hadoop job  -kill job_1474497386931_0001
[22:39:59]Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 6
[22:39:59]2016-09-22 06:39:59,419 Stage-1 map = 0%,  reduce = 0%
[22:40:06]2016-09-22 06:40:05,828 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.63 sec
[22:40:12]2016-09-22 06:40:12,347 Stage-1 map = 100%,  reduce = 17%, Cumulative CPU 3.48 sec
[22:40:16]2016-09-22 06:40:15,739 Stage-1 map = 100%,  reduce = 33%, Cumulative CPU 5.4 sec
[22:40:17]2016-09-22 06:40:16,807 Stage-1 map = 100%,  reduce = 50%, Cumulative CPU 7.52 sec
[22:40:19]2016-09-22 06:40:18,929 Stage-1 map = 100%,  reduce = 83%, Cumulative CPU 11.35 sec
[22:40:20]2016-09-22 06:40:19,991 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 13.19 sec
[22:40:21]MapReduce Total cumulative CPU time: 13 seconds 190 msec
[22:40:21]Ended Job = job_1474497386931_0001
[22:40:21]Loading data to table zmgdb.bucket_t1
[22:40:22]MapReduce Jobs Launched: 
[22:40:22]Stage-Stage-1: Map: 1  Reduce: 6   Cumulative CPU: 13.19 sec   HDFS Read: 25355 HDFS Write: 1434 SUCCESS
[22:40:22]Total MapReduce CPU Time Spent: 13 seconds 190 msec
[22:40:22]OK
[22:40:22]id
[22:40:22]Time taken: 34.91 seconds

错误的导入方式：从文件load data 

hive (zmgdb)> create table bucket_t2 like bucket_t1;
OK
Time taken: 0.707 seconds

hive (zmgdb)> load data local inpath '/data/bucket_test' into table bucket_t2;
Loading data to table zmgdb.bucket_t2
OK
Time taken: 1.485 seconds

没有启动mapreduce对数据进行哈希取值，只是简单的原样导入，没有起到抽样查询的目的。通过select * from 比较会发现bucket_t1的数据和bucket_t2的数据顺序是不同的，bucket_t2的表顺序与原数据文件顺序一致，没有做过哈希取值。

查询

select * from bucket_table tablesample(bucket x out of y on column);
tablesample是抽样语句
语法解析：TABLESAMPLE(BUCKET x OUT OF y on 字段)
y必须是table总bucket数的倍数或者因子。
hive根据y的大小，决定抽样的比例。
例如，table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽取(64/128=)1/2个bucket的数据。x表示从哪个bucket开始抽取。
例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据。如果是y=64，则抽取半个第3个桶的值。

[22:44:31]hive (zmgdb)> select * from bucket_t1 tablesample (bucket 1 out of 6 on id);
[22:44:31]OK
[22:44:31]bucket_t1.id
[22:44:31]6
[22:44:31]iu
[22:44:31]0
[22:44:31]6
[22:44:31]hj
[22:44:31]6
[22:44:31]6
[22:44:31]51
[22:44:31]
[22:44:31]
[22:44:31]r
[22:44:31]99
[22:44:31]0
[22:44:31]57
[22:44:31]loo
[22:44:31]r
[22:44:31]r
[22:44:31]r
[22:44:31]60
[22:44:31]66
[22:44:31]75
[22:44:31]6
[22:44:31]84
[22:44:31]x
[22:44:31]24
[22:44:31]93
[22:44:31]99
[22:44:31]105
[22:44:31]f
[22:44:31]r
[22:44:31]114
[22:44:31]0
[22:44:31]123
[22:44:31]129
[22:44:31]132
[22:44:31]x
[22:44:31]138
[22:44:31]141
[22:44:31]147
[22:44:31]33
[22:44:31]150
[22:44:31]156
[22:44:31]r
[22:44:31]f
[22:44:31]39
[22:44:31]15
[22:44:31]r
[22:44:31]ddd
[22:44:31]
[22:44:31]06
[22:44:31]hj
[22:44:31]f
[22:44:31]l
[22:44:31]f
[22:44:31]f
[22:44:31]f
[22:44:31]f
[22:44:31]42
[22:44:31]f
[22:44:31]r
[22:44:31]r
[22:44:31]f
[22:44:31]f
[22:44:31]r
[22:44:31]48
[22:44:31]6
[22:44:31]Time taken: 0.142 seconds, Fetched:66 row(s)

[22:44:43]hive (zmgdb)> select * from bucket_t1 tablesample (bucket 1 out of 60 on id);
[22:44:43]OK
[22:44:43]bucket_t1.id
[22:44:43]
[22:44:43]
[22:44:43]loo
[22:44:43]x
[22:44:43]114
[22:44:43]132
[22:44:43]x
[22:44:43]150
[22:44:43]ddd
[22:44:43]
[22:44:43]Time taken: 0.064 seconds, Fetched: 10 row(s)

原文参考：https://blog.csdn.net/zengmingen/article/details/52620913