<1>hive的一些基本操作

1.创建库
create database if not exists mydb;
添加注释
create database if not exists mydb2
comment 'this is test database';
查看注释
describe database mydb2;
删除库
drop database if exists mydb2;(库中没有表的情况)
drop database if exists mydb2 cascade;(库中有表的情况, cascade关键字)
其它
show tables in mydb;
show tables 'm*';

拷贝表模式但是不拷贝数据
create table if not exists mydb2.t1 like mydb2.tt;
查看表详细结构
describe extended mydb2.tt;
查看表所有分区
show partitions tt;
查看特定分区
show partitions tt partition(id='1');
重命名
alter table tt rename to tts;
1.创建内部表
create table t1
(tid int,tname string,age int);

create table t2
(tid int,tname string,age int)
location '/mytable2/t2';

create table t3(name string,age int)
row format delimited fields terminated by ',';

load data inpath '/student/' into table t3;
单个查询语句中创建表并加载数据(这个功能不能用于外部表)hive2.x可以？？？
create table t4
as
select * from t3;

create table t5
row format delimited fields terminated by ','
as
select * from t3;
创建复查数据类型表
create external table employees(
name string,
salary float,
subordinates array<string>,
deductions map<string,float>,
address struct<street:string,city:string,state:string,zip:int>
)
row format delimited fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
stored as textfile
location '/data';
数据
wang|123|a1,a2,a3|k1:1,k2:2,k3:3|s1,s2,s3,4
liu|345|a4,a5,a6|k1:4,k5:5,k6:6|s4,s5,s6,6
zhang|789|a7,a8,a9|k1:7,k8:8,k9:9|s7,s8,s9,9

create table if not exists a3(
id int,
name string,
age int,
familyArr array<string>,
familyMap map<string,string>,
familyStruct struct<girl1:string,girl2:string,girl3:string,money:float>
)
row format delimited fields terminated by ','
collection items terminated by '|'
map keys terminated by ':'
stored as textfile
location '/data/a3';
数据
1,ycy,18,a1|a2|a3|a4,a1:1500|a2:1600|a3:1800|a4:2000,y|c|y|3
2,cjk,28,b1|b2|b3|b4,b1:1500|b2:1600|b3:1800|b4:2000,c|j|k|3
3,xzmly,28,c1|c2|c3|c4,c1:1500|c2:1600|c3:1800|c4:2000,m|l|y|3

show create table a3;
array字段类型的查询(array查询是0下标开始的)
select subordinates[1] from employees;
map字段类型查询
select deductions["k1"] from employees;
struct字段类型查询
select address.city from employees;

select deductions.k4 from employees;
修改列信息(change 关键字)
alter table tt change name names string after x;(可以对某个字段重命名，并修改其位置，类型或者注释)
添加列
alter table t1 add cloumns(english int);
删除或者替换列
修改表属性,但是无法删除属性
修改存储属性
向管理表中装载数据(load data local拷贝贝蒂数据到hdfs上的目标位置/load data 转移数据到目标位置)
load data inpath '/student/sampleDataM.txt' overwrite into table tt partition(gender='M');
通过查询语句向表中插入数据(insert overwrit覆盖,insert into追加)
insert overwrite table tt partition(gender='M') select id,name from sampleData where gender='M';
insert into table tt partition(gender='M') select id,name from sampleData where gender='M';
动态分区插入(要开启严格模式，至少有一列分区是静态的)？？？？
导出数据???
insert overwrite local directory '/home/lz/sampleDataLocal' select id,name,salary from sampleData where gender='M';