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;