Alex的Hadoop菜鸟教程:第10课Hive入门教程 - 数据库编程

s/workers.csv
原来就是原封不动的把文件拷贝进去啊！就是这么土！我们可以试验再放一个文件 workers2.txt （我故意把扩展名换一个，其实hive是不看扩展名的）

# cat workers2.txt 
4,peter
5,kate
6,ted

导入

hive> LOAD DATA LOCAL INPATH '/home/alex/workers2.txt' INTO TABLE workers;
Copying data from file:/home/alex/workers2.txt
Copying file: file:/home/alex/workers2.txt
Loading data to table default.workers
Table default.workers stats: [num_partitions: 0, num_files: 2, num_rows: 0, total_size: 46, raw_data_size: 0]
OK
Time taken: 0.79 seconds

去看下文件的存储结构

# hdfs dfs -ls /user/hive/warehouse/workers/
Found 2 items
-rwxrwxrwt   2 root supergroup         25 2014-12-08 15:23 /user/hive/warehouse/workers/workers.csv
-rwxrwxrwt   2 root supergroup         21 2014-12-08 15:29 /user/hive/warehouse/workers/workers2.txt

多出来一个workers2.txt 再用sql查询下

hive> select * from workers;
OK
1	jack
2	terry
3	michael
4	peter
5	kate
6	ted
Time taken: 0.144 seconds, Fetched: 6 row(s)

分区表

分区表是用来加速查询的，比如你的数据非常多，但是你的应用场景是基于这些数据做日报表，那你就可以根据日进行分区，当你要做2014-05-05的报表的时候只需要加载2014-05-05这一天的数据就行了。我们来创建一个分区表来看下

create table partition_employee(id int, name string) 
partitioned by(daytime string) 
row format delimited fields TERMINATED BY '\054';

可以看到分区的属性，并不是任何一个列我们先建立2个测试数据文件，分别对应两天的数据

# cat 2014-05-05
22,kitty
33,lily
# cat 2014-05-06
14,sami
45,micky

导入到分区表里面

hive> LOAD DATA LOCAL INPATH '/home/alex/2014-05-05' INTO TABLE partition_employee partition(daytime='2014-05-05');
Copying data from file:/home/alex/2014-05-05
Copying file: file:/home/alex/2014-05-05
Loading data to table default.partition_employee partition (daytime=2014-05-05)
Partition default.partition_employee{daytime=2014-05-05} stats: [num_files: 1, num_rows: 0, total_size: 21, raw_data_size: 0]
Table default.partition_employee stats: [num_partitions: 1, num_files: 1, num_rows: 0, total_size: 21, raw_data_size: 0]
OK
Time taken: 1.154 seconds
hive> LOAD DATA LOCAL INPATH '/home/alex/2014-05-06' INTO TABLE partition_employee partition(daytime='2014-05-06');
Copying data from file:/home/alex/2014-05-06
Copying file: file:/home/alex/2014-05-06
Loading data to table default.partition_employee partition (daytime=2014-05-06)
Partition default.partition_employee{daytime=2014-05-06} stats: [num_files: 1, num_rows: 0, total_size: 21, raw_data_size: 0]
Table default.partition_employee stats: [num_partitions: 2, num_files: 2, num_rows: 0, total_size: 42, raw_data_size: 0]
OK
Time taken: 0.763 seconds

导入的时候通过 partition 来指定分区。
查询的时候通过指定分区来查询

hive> select * from partition_employee where daytime='2014-05-05';
OK
22	kitty	2014-05-05
33	lily	2014-05-05
Time taken: 0.173 seconds, Fetched: 2 row(s)

我的查询语句并没有什么特别的语法，hive 会自动判断你的where语句中是否包含分区的字段。而且可以使用大于小于等运算符

hive> select * from partition_employee where daytime>='2014-05-05';
OK
22	kitty	2014-05-05
33	lily	2014-05-05
14	sami	2014-05-06
45	mick'	2014-05-06
Time taken: 0.273 seconds, Fetched: 4 row(s)

我们去看看存储的结构

# hdfs dfs -ls /user/hive/warehouse/partition_employee
Found 2 items
drwxrwxrwt   - root supergroup          0 2014-12-08 15:57 /user/hive/warehouse/partition_employee/daytime=2014-05-05
drwxrwxrwt   - root supergroup          0 2014-12-08 15:57 /user/hive/warehouse/partition_employee/daytime=2014-05-06

我们试试二维的分区表

create table p_student(id int, name string) 
partitioned by(daytime string,country string) 
row format delimited fi

Alex的Hadoop菜鸟教程:第10课Hive入门教程(二)

分区表