hive表操作（转）

转载于：http://blog.csdn.net/lovelovelovelovelo/article/details/52234971

数据类型 
基本数据类型 
集合类型，array、map、struct 
文件格式，textfile、sequencefile、rcfile

创建表（内部表）

create table employee(

    name string comment 'name',

    salary float,

    subordinates array<string>,

    deductions map<string,float>,

    address struct<street:string,city:string,state:string,zip:int>

)

row format delimited fields termited by '\t' lines terminated by '\n' stored as textfile;

从文件加载数据，覆盖源表

load data local infile 'path' overwrite into table 'table'

创建外部表

create external table employee(

    name string comment 'name',

    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 '\t'

collection items terminated by ','

map keys terminated by ':'

lines terminated by '\n'

stored as textfile

location '/data/';

表中数据

lucy 11000 tom,jack,dave,kate  tom:1200,jack:1560 beijing,changanjie,xichengqu,10000

lily 13000 dave,kate  dave:1300,kate:1260 beijing,changanjie,xichengqu,10000

和我们熟悉的关系型数据库不一样，Hive现在还不支持在insert语句里面直接给出一组记录的文字形式，也就是说，hive并不支持INSERT INTO …. VALUES形式的语句。

新建employee.txt，将数据存入文件中，注意字段间用tab，行间换行enter 
通过hive命令加载数据

hive> load data local inpath '/root/employee.txt' into table employee;

hive> select * from employee;

OK

lucy    11000.0 ["tom","jack","dave","kate"]    {"tom":1200.0,"jack":1560.0}    {"street":"beijing","city":"changanjie","state":"xichengqu","zip":10000}

lily    13000.0 ["dave","kate"] {"dave":1300.0,"kate":1260.0}   {"street":"beijing","city":"changanjie","state":"xichengqu","zip":10000}

Time taken: 0.054 seconds, Fetched: 2 row(s)

select * from table不走mapreduce 

由一个表创建另一个表

create table table2 like table1;

从其他表查询创建表

create table table2 as select name,age,add from table1;

hive不同文件读取

stored as textfile:

    hadoop fs -text

stored as sequencefile:

    hadoop fs -text

stored as rcfile:

    hive -service rcfilecat path

stored as input format 'class':

    outformat 'class'

分区表操作

alter table employee add if not exists partition(country='')

alter table employee drop if exists partition(country='')

hive分桶

create table bucket_table(

    id int,

    name string

)

clustered by(id) sorted by(name) into 4 buckets

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

set hive.enforce.bucketing=true;

创建分区表

create table partitionTable(

    name string,

    age int

)

partitioned by(dt string)

row format delimited fields terminated by '\t'

lines terminated by '\n'

stored as textfile;