02分布式数据仓库 HIVE -- 表的相关操作

show tables;

show create table user;

建表(内部表)

create table user(name string,password string);   简单建表

复杂建表语句（外部表）

CREATE EXTERNAL TABLE SOGOUQ1(DT STRING,WEBSESSION STRING,WORD STRING,S_SEQ INT,C_SEQ INT,WEBSITE STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' STORED AS TEXTFILE LOCATION '/dataguru/data/SogouQ1';     复杂建表，行分隔‘\n’ 回车,数据分隔符‘\t’ Tab键

复杂建表2，

drop table if exists user;

create external table user(

name string,

age int,

a array<String>,

b map<string,float>,

c struct<a:string,b:string,c:string,d:int>

)

row format delimited fields terminated by '\t'

collection items terminated by ','

map keys terminated by '\n'

stored as textfile

location '/data/';

liguozhong         25        a,b,c         a:2,b:6,c:3          s1,r,g,2

加载数据 

load data local inpath '/home/data/user.txt' overwrite into table user;     //overwrite覆盖原始文件。非overwrite x_copy.txt

简单查询

select * from user ;

复杂查询

select a[0] from user;

select b["c"] from user;

select c.c from user;

---------------------------------------------------------------------------------------------------------------------------------------

create table user like student;     //数据不带过去。

create table user as select a,b,c from student;//连带数据带过来。

不同存储格式（stored as  textfile）的查看方式。

1：textfile：hadoop fs -text

2：sequencefile：hadoop fs -text

3：rcfile：hive -service rcfilecat '/home/user'

4: 自定义输入流:自定义输出流

分区(一般按天作分区，一天的数据，作为一个分区)

create table user(

name string

)

partitioned by (dt string,b string);

alter table user add if not exists partition(dt='20140405',b='boy');

alter table user drop if extists partition(dt='20140405',b='girl');

分桶

create table user(

name string,

sex int,

age int

)

clustered by (set) sorted by(age) into 10 buckets

row format delimited fields terminated by '\t' as textfile;

insert overwrite table user select name,sex,age from student;

set hive.enforce.bucketing = true;

select name from user where sex = 1;