Hive总结（五）表的基本操作

0.准备工作,在本地建立测试数据

cat /data/hive_data/ data.txt
09  Nermaer
31  JiaJia
10  Messi
16  Santi
06  Tian
21  Pirlo 

1.创建分区表表语句

hive> CREATE TABLE logs(id BIGINT,name STRING)
    > PARTITIONED BY(dt STRING,country STRING)
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY '\t'     行限定文件的结束符
    > STORED AS TEXTFILE;

如果想要获得相同表模式的新表
CREATE TABLE aaa LIKE bbb;

2.描述表的结构

hive>DESCRIBE logs;
id                      bigint                                      
name                    string                                      
dt                      string                                      
country                 string                                      
# Partition Information      
# col_name       data_type              comment             
dt                      string                                      
country                 string

3.把数据加载到分区表，要显示指定分区值

hive> LOAD DATA LOCAL INPATH '/home/santiago/data/hive_data/data.txt'
    > INTO TABLE log
> PARTITION(dt='2017-3-22',country='china');
**当数据需要覆盖时在INTO 前加OVERWRITE**
hive> LOAD DATA LOCAL INPATH'/home/santiago/data/hive_data/data.txt'
    > OVERWRITE INTO TABLE log
    > PARTITION(dt="2017-3-22",country="china"); 

4显示分区信息

hive> SHOW PARTITIONs logs;
dt=2017-3-22/country=china    两个分区

hadoop fs -ls /hdfs/hive/warehouse/test.db/logs
drwxrwxr-x   - santiago supergroup          0 2017-03-20 11:02
 /hdfs/hive/warehouse/test.db/logs/dt=2017-3-20
hadoop fs -ls /hdfs/hive/warehouse/test.db/logs/dt=2017-3-20/country=china
-rwxrwxr-x   2 santiago supergroup         45 2017-03-20 11:02
 /hdfs/hive/warehouse/test.db/logs/dt=2017-3-20/country=china/data.txt

表的一个分区其实就是一个目录，总体来说,PARTITION是辅助查询，缩小查询范围，加快数据搜索和对数据按照一定规律和条件进行管理。

5.删除表

hive> DROP TABLE logs;
保留表的结构，但要删除数据则可以在HDFS，hive配置处上手动删除数据。

6.表的修改

更新表名字
hive> ALTER TABLE logs RENAME TO players;
对于外部表：只更新元数据，而不移动目录。
对于内部表：更新元数据，还将表目录移动到新的目录下。
新添加一列
hive>ALTER TABLE dataload2 ADD COLUMNS (col3 STRING);
查询

hive> SELECT * FROM dataload2;
9   NULL
31  NULL
10  NULL
16  NULL
6   NULL
21  NULL

发现都是NULL，因为数据文件并没有被更新，所以查询会为col3的所有值返回NULL,因为HIVE并不支持更新已有的记录，所以常用的方法是创建一个定义了新列的心表，然后使用select语句把数据填充进去。

7.建桶表

hive> CREATE TABLE bucket_use(id BIGINT,name STRING)
    > CLUSTERED BY(id) INTO 4 BUCKETS;
hive> DESCRIBE bucket_use;
OK
id                      bigint                                      
name                    string

给桶表加添加数据，注意只能load数据到普通表，因为直接load到分桶的表是不会分桶，需要insert进行才会根据根据分桶要求进行分桶。

hive> SELECT * FROM test;
9   Nermaer
31  JiaJia
10  Messi
16  Santi
6   Tian
21  Pirlo

set hive.enforce.bucketing = true;
hive> INSERT OVERWRITE TABLE bucket_use
    > SELECT * FROM test;
HDFS   bucket_use 分为4个桶
hadoop fs -ls /hdfs/hive/warehouse/test.db/bucket_use
Found 4 items
-rwxrwxr-x   2 santiago supergroup          9 2017-03-22 22:22
 /hdfs/hive/warehouse/test.db/bucket_use/000000_0
-rwxrwxr-x   2 santiago supergroup         19 2017-03-22 22:22
 /hdfs/hive/warehouse/test.db/bucket_use/000001_0
-rwxrwxr-x   2 santiago supergroup         16 2017-03-22 22:22
 /hdfs/hive/warehouse/test.db/bucket_use/000002_0
-rwxrwxr-x   2 santiago supergroup         10 2017-03-22 22:22
 /hdfs/hive/warehouse/test.db/bucket_use/000003_0

Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

hadoop fs -cat /hdfs/hive/warehouse/test.db/bucket_use/000000_0
16Santi
hadoop fs -cat /hdfs/hive/warehouse/test.db/bucket_use/000001_0
21Pirlo
9Nermaer
hadoop fs -cat /hdfs/hive/warehouse/test.db/bucket_use/000002_0
6Tian
10Messi
hadoop fs -cat /hdfs/hive/warehouse/test.db/bucket_use/000003_0
31JiaJia

桶采样TABLESAMPLE
tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y)
y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。
例如table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据。

hive> SELECT * FROM bucket_use
    > TABLESAMPLE(BUCKET 1 OUT OF 2 ON id);
16  Santi
6   Tian
10  Messi

因为分成4个桶，则4/2=2 取两个桶，则取第一个和第三个桶数据。

8.INSERT导入数据

从一个hive表填充到另一个hive表,目标表需要提前建好。

hive> INSERT OVERWRITE TABLE dataload
    > SELECT id
    > FROM test;
hive> SELECT * FROM dataload;
9
31
10
16
6
21

CTAS操作,直接创建新表
CREATE TABLE …..AS SELECT

hive> CREATE TABLE dataload2
    > AS SELECT
    > id
    > FROM test;
hive> SELECT * FROM dataload2;
9
31
10
16
6
21

这种操作就不需要新建表了。
另外CTAS是原子操作，如果SELECT查询由于某种元婴失败，则新表就不需要建立了。

9.排序和聚集

Hive中用ORDER BY子句对数据进行排列，但是ORDER BY只使用一个reducer 来完成排序，对大型的数据集来说效率比较低。所以使用Hive非标准扩展SORT BY,它为每个reducer产生一个排序文件，所以就需要特定某行到某个reducer中，为了进行后续的聚集操作，所以需要配合DISTRIBUTE BY操作。
例如：
数据源1

hive> SELECT * FROM test;
9   Nermaer
31  JiaJia
10  Messi
16  Santi
6   Tian
21  Pirlo
hive> FROM test
    > SELECT id,name 
    > DISTRIBUTE BY id
    > SORT BY id;
6   Tian
9   Nermaer
10  Messi
16  Santi
21  Pirlo
31  JiaJia

number of mappers: 1; number of reducers: 1

数据源2

hive> SELECT * FROM dtsort;
OK
9   23  Nermaer
31  26  JiaJia
10  29  Messi
16  25  Santi
6   26  Tian
21  34  Pirlo

hive> FROM dtsort
    > SELECT id,age,name
    > DISTRIBUTE BY age
    > SORT BY age,id;
结果
9   23  Nermaer
16  25  Santi
6   26  Tian
31  26  JiaJia
10  29  Messi
21  34  Pirlo
Stage-1 map = 0%,  reduce = 0%
Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.06 sec
Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 2.19 sec
MapReduce Total cumulative CPU time: 2 seconds 190 msec
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 2.19 sec   HDFS Read: 7338 HDFS Write: 231 SUCCESS
Total MapReduce CPU Time Spent: 2 seconds 190 msec
可以通过以下设定进行设置：
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>

10.显示所有函数：

hive> show functions;