Use rcfile in hive

(1) create student & student1 table: (hive managed)
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 ',';

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;

File Format: textfile, sequencefile, rcfile
(2) Set environment variables:
Set hive. Enforce. bucketing = true;
(3) Insert data:
Load data local inpath '/home/hadoop/hivetest1.txt' overwrite into Table student partition (stat_date = "20120802 ");


(High CPU usage)
From student
Insert overwrite table student1 partition (stat_date = "20120802 ")
Select ID, age, name where stat_date = "20120802" sort by age;

View data
Select ID, age, name from student distribute by ID; // distribute is equivalent to the key in mapreduce


Data extraction (usually used for testing)
Select * from student tablesample (bucket 1 out of 2 on ID );
Tablesample (bucket X out of Y)
X must be smaller than Y, and y must be a factor or multiple of the number of bucket on during table creation. Hive determines the sample size based on Y, for example, if the original score is 32 and Y = 16, 32/16 = 2 is extracted. tablesample (bucket 3 out of 16) this means that 3rd and 16th + 3 = 19 points are to be extracted. if y = 64, 32/64 = 1/2 pieces of data will be extracted. tablesample (bucket 3 out of 64) means that half of the 3rd pieces of data will be extracted.

Rcfile operation

// Import (gzip compression)
Set hive. Enforce. bucketing = true;
Set hive.exe C. 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 Compression
Set hive. Io. rcfile. Record. Buffer. size = 16777216; // 16*1024*1024
Set Io. file. Buffer. size = 131072; // buffer size: 128*1024

Set hive. Enforce. bucketing = true;
Set hive.exe C. 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 Import
Set hive.exe C. 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;

Use rcfile in hive