hive (obd_message)> CREATE TABLE locus_travel_cent4 as
> select tb3.obd_id,tb3.start_time,tb3.end_time, tb3.mc[2] as dis,tb3.mc[3]*tb4.pl*tb4.xs/1.8 as disFuel
> from
> ( select tb1.*,tb2.* from
> (select start_time,end_time,obd_id from locus_travel_basic ) tb1 inner join
> (select split(message_content,',') as mc,create_time from device_status_log where message_id = '38' and message_content like '%acc=>00%') tb2
> ) tb3 left outer join locus_vin_parse tb4 on tb3.obd_id=tb4.obd_id where abs(unix_timestamp(create_time)-end_time)<60 ;
Warning: Map Join MAPJOIN[20][bigTable=?] in task 'Stage-6:MAPRED' is a cross product
Query ID = hdfs_20160923175656_8ca7ef5d-d552-4f7c-9a93-2a3ef3ef6528
Total jobs = 1
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
Execution log at: /tmp/hdfs/hdfs_20160923175656_8ca7ef5d-d552-4f7c-9a93-2a3ef3ef6528.log
2016-09-23 06:34:11 Starting to launch local task to process map join; maximum memory = 1908932608
2016-09-23 06:34:14 Dump the side-table for tag: 1 with group count: 11075 into file: file:/tmp/hdfs/6f2804b8-5705-426a-9ed3-25ac3424585c/hive_2016-09-23_18-34-03_781_339526347037153125-1/-local-10004/HashTable-Stage-6/MapJoin-mapfile01--.hashtable
2016-09-23 06:34:14 Uploaded 1 File to: file:/tmp/hdfs/6f2804b8-5705-426a-9ed3-25ac3424585c/hive_2016-09-23_18-34-03_781_339526347037153125-1/-local-10004/HashTable-Stage-6/MapJoin-mapfile01--.hashtable (567843 bytes)
2016-09-23 06:34:14 Dump the side-table for tag: 0 with group count: 1 into file: file:/tmp/hdfs/6f2804b8-5705-426a-9ed3-25ac3424585c/hive_2016-09-23_18-34-03_781_339526347037153125-1/-local-10004/HashTable-Stage-6/MapJoin-mapfile10--.hashtable
2016-09-23 06:34:14 Uploaded 1 File to: file:/tmp/hdfs/6f2804b8-5705-426a-9ed3-25ac3424585c/hive_2016-09-23_18-34-03_781_339526347037153125-1/-local-10004/HashTable-Stage-6/MapJoin-mapfile10--.hashtable (498468 bytes)
2016-09-23 06:34:14 End of local task; Time Taken: 3.392 sec.
Execution completed successfully
MapredLocal task succeeded
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Cannot run job locally: Input Size (= 486705900) is larger than hive.exec.mode.local.auto.inputbytes.max (= 50000000)
Starting Job = job_1474531499351_0075, Tracking URL = http://shjq-np-test-hadoop-slave:8088/proxy/application_1474531499351_0075/
Kill Command = /data/cloudera/parcels/CDH-5.7.0-1.cdh5.7.0.p0.45/lib/hadoop/bin/hadoop job -kill job_1474531499351_0075
Hadoop job information for Stage-6: number of mappers: 5; number of reducers: 0
2016-09-23 18:34:27,763 Stage-6 map = 0%, reduce = 0%
2016-09-23 18:34:38,895 Stage-6 map = 23%, reduce = 0%, Cumulative CPU 8.53 sec
2016-09-23 18:34:42,168 Stage-6 map = 28%, reduce = 0%, Cumulative CPU 13.45 sec
2016-09-23 18:34:51,722 Stage-6 map = 38%, reduce = 0%, Cumulative CPU 62.26 sec
2016-09-23 18:34:54,874 Stage-6 map = 40%, reduce = 0%, Cumulative CPU 74.37 sec
2016-09-23 18:35:55,696 Stage-6 map = 40%, reduce = 0%, Cumulative CPU 251.22 sec
2016-09-23 18:36:55,908 Stage-6 map = 40%, reduce = 0%, Cumulative CPU 427.95 sec
2016-09-23 18:37:56,184 Stage-6 map = 40%, reduce = 0%, Cumulative CPU 596.97 sec
2016-09-23 18:38:56,782 Stage-6 map = 40%, reduce = 0%, Cumulative CPU 773.36 sec
2016-09-23 18:39:57,435 Stage-6 map = 40%, reduce = 0%, Cumulative CPU 930.23 sec
2016-09-23 18:40:57,586 Stage-6 map = 40%, reduce = 0%, Cumulative CPU 1100.4 sec
2016-09-23 18:41:57,721 Stage-6 map = 40%, reduce = 0%, Cumulative CPU 1273.58 sec
2016-09-23 18:42:58,635 Stage-6 map = 40%, reduce = 0%, Cumulative CPU 1452.74 sec
2016-09-23 18:43:10,022 Stage-6 map = 60%, reduce = 0%, Cumulative CPU 1485.18 sec
2016-09-23 18:44:10,074 Stage-6 map = 60%, reduce = 0%, Cumulative CPU 1615.26 sec
2016-09-23 18:45:10,950 Stage-6 map = 60%, reduce = 0%, Cumulative CPU 1740.12 sec
2016-09-23 18:45:59,410 Stage-6 map = 70%, reduce = 0%, Cumulative CPU 1841.38 sec
2016-09-23 18:47:00,238 Stage-6 map = 70%, reduce = 0%, Cumulative CPU 1970.3 sec
2016-09-23 18:48:01,147 Stage-6 map = 70%, reduce = 0%, Cumulative CPU 2092.2 sec
2016-09-23 18:49:01,887 Stage-6 map = 70%, reduce = 0%, Cumulative CPU 2213.93 sec
2016-09-23 18:50:02,608 Stage-6 map = 70%, reduce = 0%, Cumulative CPU 2336.08 sec
2016-09-23 18:51:03,343 Stage-6 map = 70%, reduce = 0%, Cumulative CPU 2457.86 sec
2016-09-23 18:52:04,199 Stage-6 map = 70%, reduce = 0%, Cumulative CPU 2579.74 sec
2016-09-23 18:52:15,511 Stage-6 map = 90%, reduce = 0%, Cumulative CPU 2604.11 sec
2016-09-23 18:53:16,379 Stage-6 map = 90%, reduce = 0%, Cumulative CPU 2671.83 sec
2016-09-23 18:54:17,134 Stage-6 map = 90%, reduce = 0%, Cumulative CPU 2732.69 sec
2016-09-23 18:55:17,814 Stage-6 map = 90%, reduce = 0%, Cumulative CPU 2796.53 sec
2016-09-23 18:56:18,543 Stage-6 map = 90%, reduce = 0%, Cumulative CPU 2857.3 sec
2016-09-23 18:57:18,671 Stage-6 map = 90%, reduce = 0%, Cumulative CPU 2918.12 sec
2016-09-23 18:57:50,576 Stage-6 map = 100%, reduce = 0%, Cumulative CPU 2949.4 sec
MapReduce Total cumulative CPU time: 49 minutes 9 seconds 400 msec
Ended Job = job_1474531499351_0075
Moving data to: hdfs://cluster1/user/hive/warehouse/obd_message.db/locus_travel_cent4
Table obd_message.locus_travel_cent4 stats: [numFiles=5, numRows=6768, totalSize=389645, rawDataSize=382877]
MapReduce Jobs Launched:
Stage-Stage-6: Map: 5 Cumulative CPU: 2949.4 sec HDFS Read: 486892540 HDFS Write: 390061 SUCCESS
Total MapReduce CPU Time Spent: 49 minutes 9 seconds 400 msec
OK
tb3.obd_id tb3.start_time tb3.end_time dis disfuel
Time taken: 1428.363 seconds
hive (obd_message)>
>
> show tables;
> select tb3.obd_id,tb3.start_time,tb3.end_time, tb3.mc[2] as dis,tb3.mc[3]*tb4.pl*tb4.xs/1.8 as disFuel
> from
> ( select tb1.*,tb2.* from
> (select start_time,end_time,obd_id from locus_travel_basic ) tb1 inner join
> (select split(message_content,',') as mc,create_time from device_status_log where message_id = '38' and message_content like '%acc=>00%') tb2
> ) tb3 left outer join locus_vin_parse tb4 on tb3.obd_id=tb4.obd_id where abs(unix_timestamp(create_time)-end_time)<60 ;
Warning: Map Join MAPJOIN[20][bigTable=?] in task 'Stage-6:MAPRED' is a cross product
Query ID = hdfs_20160923175656_8ca7ef5d-d552-4f7c-9a93-2a3ef3ef6528
Total jobs = 1
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
Execution log at: /tmp/hdfs/hdfs_20160923175656_8ca7ef5d-d552-4f7c-9a93-2a3ef3ef6528.log
2016-09-23 06:34:11 Starting to launch local task to process map join; maximum memory = 1908932608
2016-09-23 06:34:14 Dump the side-table for tag: 1 with group count: 11075 into file: file:/tmp/hdfs/6f2804b8-5705-426a-9ed3-25ac3424585c/hive_2016-09-23_18-34-03_781_339526347037153125-1/-local-10004/HashTable-Stage-6/MapJoin-mapfile01--.hashtable
2016-09-23 06:34:14 Uploaded 1 File to: file:/tmp/hdfs/6f2804b8-5705-426a-9ed3-25ac3424585c/hive_2016-09-23_18-34-03_781_339526347037153125-1/-local-10004/HashTable-Stage-6/MapJoin-mapfile01--.hashtable (567843 bytes)
2016-09-23 06:34:14 Dump the side-table for tag: 0 with group count: 1 into file: file:/tmp/hdfs/6f2804b8-5705-426a-9ed3-25ac3424585c/hive_2016-09-23_18-34-03_781_339526347037153125-1/-local-10004/HashTable-Stage-6/MapJoin-mapfile10--.hashtable
2016-09-23 06:34:14 Uploaded 1 File to: file:/tmp/hdfs/6f2804b8-5705-426a-9ed3-25ac3424585c/hive_2016-09-23_18-34-03_781_339526347037153125-1/-local-10004/HashTable-Stage-6/MapJoin-mapfile10--.hashtable (498468 bytes)
2016-09-23 06:34:14 End of local task; Time Taken: 3.392 sec.
Execution completed successfully
MapredLocal task succeeded
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Cannot run job locally: Input Size (= 486705900) is larger than hive.exec.mode.local.auto.inputbytes.max (= 50000000)
Starting Job = job_1474531499351_0075, Tracking URL = http://shjq-np-test-hadoop-slave:8088/proxy/application_1474531499351_0075/
Kill Command = /data/cloudera/parcels/CDH-5.7.0-1.cdh5.7.0.p0.45/lib/hadoop/bin/hadoop job -kill job_1474531499351_0075
Hadoop job information for Stage-6: number of mappers: 5; number of reducers: 0
2016-09-23 18:34:27,763 Stage-6 map = 0%, reduce = 0%
2016-09-23 18:34:38,895 Stage-6 map = 23%, reduce = 0%, Cumulative CPU 8.53 sec
2016-09-23 18:34:42,168 Stage-6 map = 28%, reduce = 0%, Cumulative CPU 13.45 sec
2016-09-23 18:34:51,722 Stage-6 map = 38%, reduce = 0%, Cumulative CPU 62.26 sec
2016-09-23 18:34:54,874 Stage-6 map = 40%, reduce = 0%, Cumulative CPU 74.37 sec
2016-09-23 18:35:55,696 Stage-6 map = 40%, reduce = 0%, Cumulative CPU 251.22 sec
2016-09-23 18:36:55,908 Stage-6 map = 40%, reduce = 0%, Cumulative CPU 427.95 sec
2016-09-23 18:37:56,184 Stage-6 map = 40%, reduce = 0%, Cumulative CPU 596.97 sec
2016-09-23 18:38:56,782 Stage-6 map = 40%, reduce = 0%, Cumulative CPU 773.36 sec
2016-09-23 18:39:57,435 Stage-6 map = 40%, reduce = 0%, Cumulative CPU 930.23 sec
2016-09-23 18:40:57,586 Stage-6 map = 40%, reduce = 0%, Cumulative CPU 1100.4 sec
2016-09-23 18:41:57,721 Stage-6 map = 40%, reduce = 0%, Cumulative CPU 1273.58 sec
2016-09-23 18:42:58,635 Stage-6 map = 40%, reduce = 0%, Cumulative CPU 1452.74 sec
2016-09-23 18:43:10,022 Stage-6 map = 60%, reduce = 0%, Cumulative CPU 1485.18 sec
2016-09-23 18:44:10,074 Stage-6 map = 60%, reduce = 0%, Cumulative CPU 1615.26 sec
2016-09-23 18:45:10,950 Stage-6 map = 60%, reduce = 0%, Cumulative CPU 1740.12 sec
2016-09-23 18:45:59,410 Stage-6 map = 70%, reduce = 0%, Cumulative CPU 1841.38 sec
2016-09-23 18:47:00,238 Stage-6 map = 70%, reduce = 0%, Cumulative CPU 1970.3 sec
2016-09-23 18:48:01,147 Stage-6 map = 70%, reduce = 0%, Cumulative CPU 2092.2 sec
2016-09-23 18:49:01,887 Stage-6 map = 70%, reduce = 0%, Cumulative CPU 2213.93 sec
2016-09-23 18:50:02,608 Stage-6 map = 70%, reduce = 0%, Cumulative CPU 2336.08 sec
2016-09-23 18:51:03,343 Stage-6 map = 70%, reduce = 0%, Cumulative CPU 2457.86 sec
2016-09-23 18:52:04,199 Stage-6 map = 70%, reduce = 0%, Cumulative CPU 2579.74 sec
2016-09-23 18:52:15,511 Stage-6 map = 90%, reduce = 0%, Cumulative CPU 2604.11 sec
2016-09-23 18:53:16,379 Stage-6 map = 90%, reduce = 0%, Cumulative CPU 2671.83 sec
2016-09-23 18:54:17,134 Stage-6 map = 90%, reduce = 0%, Cumulative CPU 2732.69 sec
2016-09-23 18:55:17,814 Stage-6 map = 90%, reduce = 0%, Cumulative CPU 2796.53 sec
2016-09-23 18:56:18,543 Stage-6 map = 90%, reduce = 0%, Cumulative CPU 2857.3 sec
2016-09-23 18:57:18,671 Stage-6 map = 90%, reduce = 0%, Cumulative CPU 2918.12 sec
2016-09-23 18:57:50,576 Stage-6 map = 100%, reduce = 0%, Cumulative CPU 2949.4 sec
MapReduce Total cumulative CPU time: 49 minutes 9 seconds 400 msec
Ended Job = job_1474531499351_0075
Moving data to: hdfs://cluster1/user/hive/warehouse/obd_message.db/locus_travel_cent4
Table obd_message.locus_travel_cent4 stats: [numFiles=5, numRows=6768, totalSize=389645, rawDataSize=382877]
MapReduce Jobs Launched:
Stage-Stage-6: Map: 5 Cumulative CPU: 2949.4 sec HDFS Read: 486892540 HDFS Write: 390061 SUCCESS
Total MapReduce CPU Time Spent: 49 minutes 9 seconds 400 msec
OK
tb3.obd_id tb3.start_time tb3.end_time dis disfuel
Time taken: 1428.363 seconds
hive (obd_message)>
>
> show tables;