HIVE创建目录和表

创建表目录

create database companys
create database companys location table
create database companys location table with dbproperties('name'='kedde','data'='2012-01-02')
查看database信息，无法查看当前表目录
describe database companys
describe database extended companys
改变默认的表目录
use companys
删除表目录
drop database financials
修改表目录属性,无法删除表属性
alter database financials setdbproperties('owner'='dirk')
2.表篇
新建表 mydb必须是存在的，location可以不存在，hive会新建
CREATE TABLE IF NOT EXISTS mydb.employees (
name STRING COMMENT 'Employee name',
salary FLOAT COMMENT 'Employee salary',
subordinates ARRAY<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 'Description of the table'
TBLPROPERTIES ('creator'='me', 'created_at'='2012-01-02 10:00:00', ...)
LOCATION '/user/hive/warehouse/mydb.db/employees';
新建表，copy已有表结构schema，不包含数据
create table if not exists mydb.empoyees link mydb.employees;
查看表
show tables;
show tables in companys;
show tables 'emp.*'; #注意是.*
查看表信息
describe extended companys.tables;
规定分隔符为/t
row format delimited fields terminated by '/t'；
3.表篇分区
不用于关系数据库partition中的字段可以不再table中，但是partition中的字段可以如同table中column一样使用这样可以加快查询速度，因为只用查找一个目下文件就可以了这里分区分为单分区partition一个column，多分区partition多个column单分区就一个目录，多分区也是一个目录，并嵌套多个目录
实例：按照 country 和 state 给employee多分区
CREATE TABLE employees (
name STRING,
salary FLOAT,
subordinates ARRAY<STRING>,
deductions MAP<STRING, FLOAT>,
address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
)
PARTITIONED BY (country STRING, state STRING);
查看partition
show partitions employees;
SHOW PARTITIONS employees PARTITION(country='US');
添加partition(不区分大小写)
alter table employees add partition(country='US',state='dallas')
alter table employees add partition(country='US',state='dallas') location '/home/hadoop/us-dallas'
alter table employees add partition(country='US',state='dallas') location '/home/hadoop/us-dallas' partition(country='US',state='ca') location '/home/hadoop/us-dallas'
删除partition,分区数据和元数据都被删除
alter table employees drop partition(country='us',state='dallas');
向分区中添加数据
load data inpath '/home/hadoop/resource/dallas' into table employees partition(country='us',state='dallas');
分区的属性
set hive.mapred.mode=strict;属性禁止没有where的语句执行在partition的table上（防止数据量巨大得table，执行这样没有限制的语句）

set hive.mapred.mode=nonstrict;

resource:http://www.superwu.cn/2016/06/08/2978/