hiveql笔记（一）
1、创建表
create table if not exists mydb.employees{
name String COMMENT 'Employee name',
salary FLOAT COMMENT 'Empolyee salary',
subordinates APPAY<STRING> COMMENT 'Names of subordinates',
deductions MAP<STRING,FLOAT>
COMMENT 'Keys are deductions names,values are percentages'
address STRUCT<street:STRING，city:String,state:STRING,zip:INT>
COMMENT 'Home address')
COMMENT 'Desription of the table'
TBLPROPERITES ('creator '='me','created_at'='2012-01-02 10:00:00',...)
LOCATION '/usr/hive/warehouse/mydb.db/employees';
Hive 会自动添加两个表属性：一个是last_modified_by,其保存着最后修改这个表的用户的用户名。另一个是last_modified_time,其保存着最后修改的新纪元时间秒。
2、查看表的详细表结构信息
DESCRIBE EXTENDED mydb.employees;
//在表名后添加字段的名称，使用extended关键字也不会增加更多的输出信息。
DESCRIBE mydb.employees.salary;
3、外部表
CREATE EXTERNAL TABLE IF NOT EXISTS stocks (
exchange STRING,
symbol STRING,
ymd STRING,
price_open FLOAT,
price_high FLOAT,
price_low FLOAT,
price_close FLOAT,
volume INT,
price_adj_close FLOAT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘，‘
LOCATION '/data/stocks';
关键字EXTENAL告诉hive这个表是外部的，而后面的LOCATION..子句则用于告诉HIVE数据位于哪个路径下。
//还可以对一张存在的表进行结构复制(而不会复制数据）
CREATE EXTERNAL TABLE IF NOT EXISTS mydb.employees3
LIKE mydb.employees
LOCATION '/path/to/data';
4、分区表、管理表
CREATE TABLE employees （
name String,
salay FLOAT,
subordinates APPAY<STRING>,
deductions MAP<STRING,FLOAT>，
address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>
）
PARTITIONED BY (country STRING,state STRING);
分区表改变了Hive对数据存储的组织方式，这个表会有一个exployees目录与之对应，每个分区都有一个partXX
//可以使用show partitions命令查看表中存在的所有分区:
SHOW PARTITIONS employees;
//如果表中存在很多的分区，而只想查看是否存储某个特定分区键的分区的话，还可以在命令上添加指定了一个或多个特定分区字段值的PARTITION子句：
SHOW PARTITIONS employees PARTITION(country='US');
SHOW PARTITIONS employees PARTITION(country='US',state='AK');
5、外部分区表
CREATE EXTERNAL TABLE IF NOT EXISTS log_messages （
hms INT,
severity STRING,
server STRING,
porcess_id STRING,
message STRING)
PARTITIONED BY (year INT,month INT,day INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
）
//添加一个分区
ALTER TABLE log_messages ADD PARTITION(year = 2012,month = 1,day =2 )
LOCATITION 'hdfs://master_server/data/log_message/2012/01/02';
//将一个月前的分区数据拷贝到其他集群
hadoop distcp /data/log_message/2011/12/02 s3n://ourbucket/logs/2011/12/02
//修改表，将分区路径指向到S3路径：
ALTER TABLE log_messages PARTITION（year = 2011,month = 12,day = 2）
SET LOCATION 's3m://ourbucket/logs/2011/01/02';
//DESCRIBE EXTENDED log_message 语句会将分区键作为表的模式的一部分，和partitionKeys列表的内容同时进行显示：
DESCRIBE EXTENDED log_messages;
6、删除表
DROP TABLE IF EXISTS employees;
//这里有个知识点，如果开启了Hadoop回收站功能（默认是关闭），那么数据将会转移到用户在分布式文件系统中的用户跟目录下的.Trash目录下，也就是HDFS中的/usr/$USER/.Trash目录，如果要开启这个功能，需要配置fs.trash.internal的值。设置一个合理的整数，比如如果设置1440,那么就表示是24小时。
7、表重命名
ALTER TABLE log_messages RENAME TO logmsgs;
8、修改列信息
ALTER TABLE log_messages
CHANGE COLUMN hms hours_minutes_seconds INT
COMMENT 'The hours,minutes, and seconds part of the timestamp'
AFTER severity;
//这里说明，如果想将这个字段移动到第一个位置，需要使用FIRST关键字替代AFTER other_column子句即可。
9、增加列
ALTER TABLE log_messages ADD COLUMNS (
app_name STRING COMMENT 'Application name',
session_id LONG COMMENT 'The current session id');
10、删除或者替换列
//移除了之前所有的字段并重新指定了新的字段：
ALTER TABLE log_messages REPLACE COLUMNS (
hours_mins_secs INT COMMENT 'hour,minute,seconds from timestamp',
serverity STRING COMMENT 'The message severity'
message STRING COMMENT 'The rest of the message');
11、修改存储属性
ALTER TABLE log_messages
PARTITION(year = 2012,month = 1,day = 1)
SET FILEFORMAT SEQUENCEFILE;
12、通过查询语句向表中插入数据
INSERT OVERWRITE TABLE employees
PARTITION (country = 'US' , state = 'OR' )
SELECT * FROM staged_employees se
WHERE se.cnty = 'US' AND se.st = 'OR';
//如果是非分区表，之前表中的内容将会被覆盖掉
13、动态分区插入
//指定了country字段的值为静态的US，而分区字段state是动态值。
INSERT OVERWRITE TABLE employees
PARTITION (country='US',state)
SELECT ...,se.cnty,se.st
FROM staged_employees se
WHERE se.cnty = 'US';
(静态分区键必须出现在动态分区键之前)
//延伸动态分区属性:
hive.exec.dynamic.partition false 设置成true,表示开启动态分区功能
hive.exec.dynamic.partition.mode strict 设置成strict,表示允许所有分区都是动态的
hive.exec.max.dynamic.parition.pernode 100 每个mapper或reducer可以创建的最大动态分区个数。如果某个mapper或reducer尝试创建大于这个值得分区的话则会抛出一个致命错误信息。
hive.exec.max.dynamic.partitions +1000 一个动态分区创建语句可以创建的最大动态分区个数。如果超过这个值则会抛出一个致命错误信息。
hive.exec.max.created.files 100000 全局可以创建的最大文件个数。
eg:
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrct;
set.hive.exec.max.dynamic.partitions.pernode=1000;
INSERT OVERWRITE TABLE employees
PARTITION （country,state）
SELECT ...,se.cty,se.st
FROM staged_employees se;
14、单个查询语句中创建表并加载数据
CREATE TABLE ca_employees
as SELECT name,salary,address
FROM employees
WHERE se.state = 'CA';
15、导出数据
hadoop fs -cp sorce_path target_path
//也可以使用INSERT...DIRECTORY...
INSERT OVERWRITE LOCAL DIRECTOR 'tmp/ca_employees'
SELECT name,salary,address
From employees
WHERE se.state = 'CA';
参考文献《Hive编程指南》
