? （1）建student & student1 表：（hive 托管）
create table student(id INT, age INT, name STRING)
partitioned by(stat_date STRING)
clustered by(id) sorted by(age) into 4 buckets
row format delimited fields terminated by ',';

create table studentrc(id INT, age INT, name STRING)
partitioned by(stat_date STRING)
clustered by(id) sorted by(age) into 4 buckets
row format delimited fields terminated by ',' stored as rcfile;

create table studentlzo(id INT, age INT, name STRING)
partitioned by(stat_date STRING)
clustered by(id) sorted by(age) into 4 buckets
row format delimited fields terminated by ',' stored as rcfile;

文件格式 textfile， sequencefile， rcfile
（2）設置環(huán)境變量：
set hive.enforce.bucketing = true;
（3）插入數(shù)據(jù)：
? LOAD DATA local INPATH '/home/hadoop/hivetest1.txt' OVERWRITE INTO TABLE student partition(stat_date="20120802");


(CPU使用率很高)
from student
insert overwrite table student1 partition(stat_date="20120802")
select id,age,name where stat_date="20120802" sort by age;

查看數(shù)據(jù)
select id, age, name from student? distribute by id ; // distribute相當于mapreduce中的key


抽選數(shù)據(jù)(一般測試的情況下使用)
select * from student tablesample(bucket 1 out of 2 on id);
TABLESAMPLE(BUCKET x OUT OF y)
其中, x必須比y小, y必須是在創(chuàng)建表的時候bucket on的數(shù)量的因子或者倍數(shù), hive會根據(jù)y的大小來決定抽樣多少, 比如原本分了32分, 當y=16時, 抽取32/16=2分, 這時TABLESAMPLE(BUCKET 3 OUT OF 16) 就意味著要抽取第3和第16+3=19分的樣品. 如果y=64， 這要抽取 32/64=1/2份數(shù)據(jù), 這時TABLESAMPLE(BUCKET 3 OUT OF 64) 意味著抽取第3份數(shù)據(jù)的一半來進行.

rcfile操作

// 導入(gzip壓縮)
set hive.enforce.bucketing=true;
set hive.exec.compress.output=true; ?
set mapred.output.compress=true; ?
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec; ?
set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec; ?
from student
insert overwrite table studentrc partition(stat_date="20120802") ?
select id,age,name where stat_date="20120802" sort by age;


// lzo壓縮
set hive.io.rcfile.record.buffer.size = 16777216; // 16 * 1024 * 1024
set io.file.buffer.size = 131072; // 緩沖區(qū)大小 128 * 1024

set hive.enforce.bucketing=true;
set hive.exec.compress.output=true; ?
set mapred.output.compress=true; ?
set mapred.output.compression.codec=com.hadoop.compression.lzo.LzoCodec; ?
set io.compression.codecs=com.hadoop.compression.lzo.LzoCodec; ?
from student
insert overwrite table studentlzo partition(stat_date="20120802") ?
select id,age,name where stat_date="20120802" sort by age;

// sequencefile導入
set hive.exec.compress.output=true; ?
set mapred.output.compress=true; ?
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec; ?
set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec; ?
insert overwrite table studentseq select * from student;

hive中使用rcfile