hive joinテスト

3927 ワード

hive> create table course(id int,c1 string,c2 string,c3 string,c4 string)row format delimited fields terminated by ' ';
OK
Time taken: 0.07 seconds
hive> create table student(id int,name string,age int)row format delimited fields terminated by ' ';
OK
Time taken: 0.039 seconds
hive> show tables;
OK
course
student
tianqi
Time taken: 0.016 seconds, Fetched: 3 row(s)
hive> describe student;
OK
id                      int                     None                
name                    string                  None                
age                     int                     None                
Time taken: 0.048 seconds, Fetched: 3 row(s)
hive> load data local inpath '/home/jifeng/hadoop/course.txt' into table course;
Copying data from file:/home/jifeng/hadoop/course.txt
Copying file: file:/home/jifeng/hadoop/course.txt
Loading data to table test.course
Table test.course stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 121, raw_data_size: 0]
OK
Time taken: 0.183 seconds
hive> select * from coures;
FAILED: SemanticException [Error 10001]: Line 1:14 Table not found 'coures'
hive> select * from course;
OK
1                       
2             
3                  
4                  
5                  
Time taken: 0.063 seconds, Fetched: 5 row(s)
hive> load data local inpath '/home/jifeng/hadoop/stu.txt' into table student;
Copying data from file:/home/jifeng/hadoop/stu.txt
Copying file: file:/home/jifeng/hadoop/stu.txt
Loading data to table test.student
Table test.student stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 57, raw_data_size: 0]
OK
Time taken: 0.135 seconds
hive> select * from student;
OK
1       nick    24
2       doping  25
3       caizhi  26
4       liaozhi 27
5       wind    30
Time taken: 0.043 seconds, Fetched: 5 row(s)
hive> select s.name,c.c1,c.c2,c.c3,c.c4 from student s join course c on s.id = c.id;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapred.reduce.tasks=<number>
Starting Job = job_201408190908_0002, Tracking URL = http://jifeng01:50030/jobdetails.jsp?jobid=job_201408190908_0002
Kill Command = /home/jifeng/hadoop/hadoop-1.2.1/libexec/../bin/hadoop job  -kill job_201408190908_0002
Hadoop job information for Stage-1: number of mappers: 2; number of reducers: 1
2014-08-19 16:25:08,848 Stage-1 map = 0%,  reduce = 0%
2014-08-19 16:25:10,857 Stage-1 map = 50%,  reduce = 0%, Cumulative CPU 0.46 sec
2014-08-19 16:25:11,864 Stage-1 map = 50%,  reduce = 0%, Cumulative CPU 0.46 sec
2014-08-19 16:25:12,870 Stage-1 map = 50%,  reduce = 0%, Cumulative CPU 0.46 sec
2014-08-19 16:25:13,876 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.25 sec
2014-08-19 16:25:14,880 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.25 sec
2014-08-19 16:25:15,887 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.25 sec
2014-08-19 16:25:16,892 Stage-1 map = 100%,  reduce = 33%, Cumulative CPU 1.25 sec
2014-08-19 16:25:17,900 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 1.8 sec
2014-08-19 16:25:18,907 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 1.8 sec
MapReduce Total cumulative CPU time: 1 seconds 800 msec
Ended Job = job_201408190908_0002
MapReduce Jobs Launched: 
Job 0: Map: 2  Reduce: 1   Cumulative CPU: 1.8 sec   HDFS Read: 616 HDFS Write: 142 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 800 msec
OK
nick                    
doping        
caizhi             
liaozhi            
wind               
Time taken: 13.549 seconds, Fetched: 5 row(s)
hive>