HIVE Schema Design 模式设计

来源：互联网发布：淘宝的英树是正品吗编辑：程序博客网时间：2024/06/06 16:32

Table-by-Day

hive> CREATE TABLE supply_2011_01_02 (id int, part string, quantity int);hive> CREATE TABLE supply_2011_01_03 (id int, part string, quantity int);hive> CREATE TABLE supply_2011_01_04 (id int, part string, quantity int);hive> .... load data ...hive> SELECT part,quantity supply_2011_01_02    > UNION ALL    > SELECT part,quantity from supply_2011_01_03    > WHERE quantity < 4;

hive> CREATE TABLE supply (id int, part string, quantity int)    > PARTITIONED BY (day int);hive> ALTER TABLE supply add PARTITION (day=20110102);

hive> ALTER TABLE supply add PARTITION (day=20110103);hive> ALTER TABLE supply add PARTITION (day=20110102);hive> .... load data ...hive> SELECT part,quantity FROM supply    > WHERE day>=20110102 AND day<20110103 AND quantity < 4;

Adding Columns to a Table

hive> CREATE TABLE weblogs (version LONG, url STRING)    > PARTITIONED BY (hit_date int)    > ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';hive> ! cat log1.txt1  /mystuff1  /toyshive> LOAD DATA LOCAL INPATH 'log1.txt' int weblogs partition(20110101);hive> SELECT * FROM weblogs;1  /mystuff  201101011  /toys     20110101

hive> ! cat log2.txt2  /cars   bob2  /stuff  terryhive> ALTER TABLE weblogs ADD COLUMNS (user_id string);hive> LOAD DATA LOCAL INPATH 'log2.txt' int weblogs partition(20110102);hive> SELECT * from weblogs1  /mystuff  20110101  NULL1  /toys     20110101  NULL2  /cars     20110102  bob2  /stuff    20110102  terry