hive_SQL

一，表

1，hive内部表（删除表时，元数据与数据都会被删除）

CREATE  TABLE [IF NOT EXISTS] table_name

2，Hive 外部表（删除外部表只删除metastore的元数据，不删除hdfs中的表数据）

 CREATE EXTERNAL TABLE [IF NOT EXISTS] table_name LOCATION hdfs_path

3，hive建表

（1）普通建表方式

CREATE TABLE person(
id INT,
name STRING,
age INT,
likes ARRAY<STRING>,
address MAP<STRING,STRING>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY ' ';

（2）Create Table Like:

CREATE TABLE empty_key_value_store LIKE key_value_store;

（3）Create Table As Select (CTAS)

CREATE TABLE new_key_value_store
AS
SELECT columA, columB FROM key_value_store;

4，查看表描述

DESCRIBE [EXTENDED|FORMATTED] table_name

二，分区

1，分区表创建

必须在表定义时指定对应的partition字段
a、单分区建表语句：
create table day_table (id int, content string) partitioned by (dt string);
单分区表，按天分区，在表结构中存在id，content，dt三列。
以dt为文件夹区分
b、 双分区建表语句：
create table day_hour_table (id int, content string) partitioned by (dt string, hour string);
双分区表，按天和小时分区，在表结构中新增加了dt和hour两列。
先以dt为文件夹，再以hour子文件夹区分

2，添加分区

ALTER TABLE day_table ADD PARTITION (dt='2008-08-08', hour='08')；

3，删除分区

ALTER TABLE day_hour_table DROP PARTITION (dt='2008-08-08', hour='09');

4，向指定分区添加数据

LOAD DATA INPATH '/user/pv.txt' INTO TABLE day_hour_table PARTITION(dt='2008-08- 08', hour='08');
LOAD DATA local INPATH '/user/hua/*' INTO TABLE day_hour partition(dt='2010-07- 07');

当数据被加载至表中时，不会对数据进行任何转换。Load操作只是将数据复制至Hive表对应的位置。数据加载时在表下自动创建一个目录

5，查询分区数据

SELECT day_table.* FROM day_table WHERE day_table.dt>= '2008-08-08'; 

分区表的意义在于优化查询。查询时尽量利用分区字段。如果不使用分区字段，就会全部扫描。

三，函数

四，分桶

我们知道传统的DBMS系统一般都具有表分区的功能，通过表分区能够在特定的区域检索数据，减少扫描成本，在一定程度上提高查询效率，当然我们还可以通过进一步在分区上建立索引进一步提升查询效率。在此就不赘述了。

在Hive数仓中也有分区分桶的概念，在逻辑上分区表与未分区表没有区别，在物理上分区表会将数据按照分区键的列值存储在表目录的子目录中，目录名=“分区键=键值”。其中需要注意的是分区键的值不一定要基于表的某一列（字段），它可以指定任意值，只要查询的时候指定相应的分区键来查询即可。我们可以对分区进行添加、删除、重命名、清空等操作。因为分区在特定的区域（子目录）下检索数据，它作用同DNMS分区一样，都是为了减少扫描成本。

分桶则是指定分桶表的某一列，让该列数据按照哈希取模的方式随机、均匀地分发到各个桶文件中。因为分桶操作需要根据某一列具体数据来进行哈希取模操作，故指定的分桶列必须基于表中的某一列（字段）。因为分桶改变了数据的存储方式，它会把哈希取模相同或者在某一区间的数据行放在同一个桶文件中。如此一来便可提高查询效率，如：我们要对两张在同一列上进行了分桶操作的表进行JOIN操作的时候，只需要对保存相同列值的桶进行JOIN操作即可。同时分桶也能让取样（Sampling）更高效。