LEI'S DESIGN SERVICES

EXPERIENCED IN CHIP DESIGN VERIFICATION

Chapter 7:  Sqoop Import from MYSQL to HIVE

 

 Figure 6:  Sqoop import from MYSQL to HIVE diagram

 

7.1 Dataset

Source MYSQL database:  megamillionAnalysis

Source MYSQL table:  megamillionDataExtraction

 

7.2 Problem Statement

Using Sqoop, import a MYSQL table to a HIVE table.

 

7.3 Approach

a)      Create a HIVE database, megamillionAnalysisHive.  Create a HIVE table, megamillionDataExtractionHive.

b)      Import a MYSQL table into the HIVE table using Sqoop.

a)      Do HIVE table data post-processing.

Figure 6 above shows the Sqoop import from MYSQL into HIVE diagram.

 

7.4 Code

Linux script to run Sqoop import from MYSQL into HIVE:  runsqoopimportmegamillion2hive

 

Command:  ./ runsqoopimportmegamillion2hive

File name:  runsqoopimportmegamillion2hive

File content:

hdfs dfs -rmr /user/hive/warehouse/megamillionanalysishive.db/megamilliondataextractionhive

hive < hiveMegamillion

#import customer table in big database in mysql back to hdfs /output/sqoop

#directory

sqoop import --connect jdbc:mysql://localhost/megamillionAnalysis --table megamillionDataExtraction --fields-terminated-by , --username root -P --hive-import --hive-table megamillionAnalysisHive.megamillionDataExtractionHive --hive-home /user/hive/warehouse

  #check the import data files in

  #megamillionAnalysisHive.megamillionDataExtractionHive directory

  hdfs dfs -ls  

  /user/hive/warehouse/megamillionanalysishive.db/megamilliondataextractionhive

 

hive < hivemegamillionPostProcessing

 

 

HIVE database and table creation file name: hiveMegamillion

Command:   ./hiveMegamillion

File content:

use megamillionAnalysisHive;

drop table megamillionDataExtractionHive;

drop database megamillionAnalysisHive;

create database megamillionAnalysisHive;

use megamillionAnalysisHive;

create Table megamillionDataExtractionHive(

ID STRING,

Period STRING,

AnalysisType STRING,

Win6nums STRING,

Win5nums STRING,

Win4numsNmega STRING,

Win4nums STRING,

Win3numsNmega STRING,

Win3nums STRING,

Win2numsNmega STRING,

Win1numNmega STRING,

Winmega STRING,

Profit STRING,

WinPercent STRING)

row format delimited

fields terminated by ",";

 

HIVE post-processing file name:  hivemegamillionPostProcessing

Command :  hive < hivemegamillionPostProcessing

 

File content:

use megamillionAnalysisHive;

select Period,AnalysisType,Profit,WinPercent,Win4nums from megamilliondataextractionhive sort by AnalysisType,Period;

 

HIVE post-processing 2 file name: hivemegamillionPostProcessingSortProfit

Command:  hive < hivemegamillionPostProcessingSortProfit

 

File content:

use megamillionAnalysisHive;

#sort by profit

select Period,AnalysisType,Profit,WinPercent,Win4nums from megamilliondataextractionhive sort by Profit;

 

HIVE post-processing 3 file name:  hivemegamillionPostProcessingSortWinPercent

Command:  hive < hivemegamillionPostProcessingSortWinPercent

 

File content:

use megamillionAnalysisHive;

#sort by winning percentage

select Period,AnalysisType,Profit,WinPercent,Win4nums from megamilliondataextractionhive sort by WinPercent;

 

HIVE post-processing 4 file name:

hivemegamillionPostProcessingSortWin4nums

 

Command:  hive < hivemegamillionPostProcessingSortWin4nums

File content:

use megamillionAnalysisHive;

select Period,AnalysisType,Profit,WinPercent,Win4nums from megamilliondataextractionhive sort by Win4nums;

7.5 Execution

 Command to create HIVE database and table, import MYSQL table to HIVE using Sqoop, and do post-processing:  ./ runsqoopimportmegamillion2hive

Output content:

rmr: DEPRECATED: Please use 'rm -r' instead.

OpenJDK Server VM warning: You have loaded library /home/notroot/lab/software/hadoop-2.6.0/lib/native/libhadoop.so.1.0.0 which might have disabled stack guard. The VM will try to fix the stack guard now.

It's highly recommended that you fix the library with 'execstack -c <libfile>', or link it with '-z noexecstack'.

15/09/14 16:28:27 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable

15/09/14 16:28:30 INFO fs.TrashPolicyDefault: Namenode trash configuration: Deletion interval = 0 minutes, Emptier interval = 0 minutes.

Deleted /user/hive/warehouse/megamillionanalysishive.db/megamilliondataextractionhive

WARNING: org.apache.hadoop.metrics.jvm.EventCounter is deprecated. Please use org.apache.hadoop.log.metrics.EventCounter in all the log4j.properties files.

Logging initialized using configuration in jar:file:/home/notroot/lab/software/hive-0.9.0-bin/lib/hive-common-0.9.0.jar!/hive-log4j.properties

Hive history file=/tmp/root/hive_job_log_root_201509141628_1150656158.txt

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hadoop-2.6.0/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hive-0.9.0-bin/lib/slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

OpenJDK Server VM warning: You have loaded library /home/notroot/lab/software/hadoop-2.6.0/lib/native/libhadoop.so.1.0.0 which might have disabled stack guard. The VM will try to fix the stack guard now.

It's highly recommended that you fix the library with 'execstack -c <libfile>', or link it with '-z noexecstack'.

hive> use megamillionAnalysisHive;

OK

Time taken: 13.479 seconds

hive> drop table megamillionDataExtractionHive;

OK

Time taken: 9.642 seconds

hive> drop database megamillionAnalysisHive;

OK

Time taken: 0.708 seconds

hive> create database megamillionAnalysisHive;

OK

Time taken: 0.761 seconds

hive> use megamillionAnalysisHive;

OK

Time taken: 0.111 seconds

hive> create Table megamillionDataExtractionHive(

    > ID STRING,

    > Period STRING,

    > AnalysisType STRING,

    > Win6nums STRING,

    > Win5nums STRING,

    > Win4numsNmega STRING,

    > Win4nums STRING,

    > Win3numsNmega STRING,

    > Win3nums STRING,

    > Win2numsNmega STRING,

    > Win1numNmega STRING,

    > Winmega STRING,

    > Profit STRING,

    > WinPercent STRING)

    > row format delimited

    > fields terminated by ",";

OK

Time taken: 1.308 seconds

hive> Enter password:

15/09/14 16:29:23 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.

15/09/14 16:29:23 INFO tool.CodeGenTool: Beginning code generation

15/09/14 16:29:24 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `megamillionDataExtraction` AS t LIMIT 1

15/09/14 16:29:25 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `megamillionDataExtraction` AS t LIMIT 1

15/09/14 16:29:25 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /home/notroot/lab/software/hadoop-2.6.0

Note: /tmp/sqoop-root/compile/cf0bc267eefc662a5736eeeea87472de/megamillionDataExtraction.java uses or overrides a deprecated API.

Note: Recompile with -Xlint:deprecation for details.

15/09/14 16:29:32 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/cf0bc267eefc662a5736eeeea87472de/megamillionDataExtraction.jar

15/09/14 16:29:32 WARN manager.MySQLManager: It looks like you are importing from mysql.

15/09/14 16:29:32 WARN manager.MySQLManager: This transfer can be faster! Use the --direct

15/09/14 16:29:32 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.

15/09/14 16:29:32 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)

15/09/14 16:29:32 INFO mapreduce.ImportJobBase: Beginning import of megamillionDataExtraction

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hadoop-2.6.0/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hbase-1.1.0/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

OpenJDK Server VM warning: You have loaded library /home/notroot/lab/software/hadoop-2.6.0/lib/native/libhadoop.so.1.0.0 which might have disabled stack guard. The VM will try to fix the stack guard now.

It's highly recommended that you fix the library with 'execstack -c <libfile>', or link it with '-z noexecstack'.

15/09/14 16:29:34 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable

15/09/14 16:29:34 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar

15/09/14 16:29:37 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps

15/09/14 16:29:38 INFO client.RMProxy: Connecting to ResourceManager at /0.0.0.0:8032

15/09/14 16:29:44 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN(`ID`), MAX(`ID`) FROM `megamillionDataExtraction`

15/09/14 16:29:44 WARN db.TextSplitter: Generating splits for a textual index column.

15/09/14 16:29:44 WARN db.TextSplitter: If your database sorts in a case-insensitive order, this may result in a partial import or duplicate records.

15/09/14 16:29:44 WARN db.TextSplitter: You are strongly encouraged to choose an integral split column.

15/09/14 16:29:44 INFO mapreduce.JobSubmitter: number of splits:6

15/09/14 16:29:46 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1442255635725_0004

15/09/14 16:29:48 INFO impl.YarnClientImpl: Submitted application application_1442255635725_0004

15/09/14 16:29:48 INFO mapreduce.Job: The url to track the job: http://localhost:8088/proxy/application_1442255635725_0004/

15/09/14 16:29:48 INFO mapreduce.Job: Running job: job_1442255635725_0004

15/09/14 16:30:17 INFO mapreduce.Job: Job job_1442255635725_0004 running in uber mode : false

15/09/14 16:30:17 INFO mapreduce.Job:  map 0% reduce 0%

15/09/14 16:30:52 INFO mapreduce.Job:  map 17% reduce 0%

15/09/14 16:30:53 INFO mapreduce.Job:  map 33% reduce 0%

15/09/14 16:31:26 INFO mapreduce.Job:  map 50% reduce 0%

15/09/14 16:31:27 INFO mapreduce.Job:  map 67% reduce 0%

15/09/14 16:31:58 INFO mapreduce.Job:  map 83% reduce 0%

15/09/14 16:32:01 INFO mapreduce.Job:  map 100% reduce 0%

15/09/14 16:32:03 INFO mapreduce.Job: Job job_1442255635725_0004 completed successfully

15/09/14 16:32:04 INFO mapreduce.Job: Counters: 31

   File System Counters

               FILE: Number of bytes read=0

               FILE: Number of bytes written=688242

               FILE: Number of read operations=0

               FILE: Number of large read operations=0

               FILE: Number of write operations=0

               HDFS: Number of bytes read=757

               HDFS: Number of bytes written=26925

               HDFS: Number of read operations=24

               HDFS: Number of large read operations=0

               HDFS: Number of write operations=12

   Job Counters

               Killed map tasks=1

               Launched map tasks=7

               Other local map tasks=7

               Total time spent by all maps in occupied slots (ms)=193059

               Total time spent by all reduces in occupied slots (ms)=0

               Total time spent by all map tasks (ms)=193059

               Total vcore-seconds taken by all map tasks=193059

               Total megabyte-seconds taken by all map tasks=197692416

   Map-Reduce Framework

               Map input records=360

               Map output records=360

               Input split bytes=757

               Spilled Records=0

               Failed Shuffles=0

               Merged Map outputs=0

               GC time elapsed (ms)=2967

               CPU time spent (ms)=13540

               Physical memory (bytes) snapshot=459321344

               Virtual memory (bytes) snapshot=3296096256

               Total committed heap usage (bytes)=195428352

   File Input Format Counters

               Bytes Read=0

   File Output Format Counters

               Bytes Written=26925

15/09/14 16:32:04 INFO mapreduce.ImportJobBase: Transferred 26.2939 KB in 147.2205 seconds (182.8889 bytes/sec)

15/09/14 16:32:04 INFO mapreduce.ImportJobBase: Retrieved 360 records.

15/09/14 16:32:04 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `megamillionDataExtraction` AS t LIMIT 1

15/09/14 16:32:05 INFO hive.HiveImport: Loading uploaded data into Hive

15/09/14 16:32:13 INFO hive.HiveImport: WARNING: org.apache.hadoop.metrics.jvm.EventCounter is deprecated. Please use org.apache.hadoop.log.metrics.EventCounter in all the log4j.properties files.

15/09/14 16:32:18 INFO hive.HiveImport: Logging initialized using configuration in jar:file:/home/notroot/lab/software/hive-0.9.0-bin/lib/hive-common-0.9.0.jar!/hive-log4j.properties

15/09/14 16:32:18 INFO hive.HiveImport: Hive history file=/tmp/root/hive_job_log_root_201509141632_917315458.txt

15/09/14 16:32:19 INFO hive.HiveImport: SLF4J: Class path contains multiple SLF4J bindings.

15/09/14 16:32:19 INFO hive.HiveImport: SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hadoop-2.6.0/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]

15/09/14 16:32:19 INFO hive.HiveImport: SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hbase-1.1.0/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]

15/09/14 16:32:19 INFO hive.HiveImport: SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hive-0.9.0-bin/lib/slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]

15/09/14 16:32:19 INFO hive.HiveImport: SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

15/09/14 16:32:19 INFO hive.HiveImport: SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

15/09/14 16:32:20 INFO hive.HiveImport: OpenJDK Server VM warning: You have loaded library /home/notroot/lab/software/hadoop-2.6.0/lib/native/libhadoop.so.1.0.0 which might have disabled stack guard. The VM will try to fix the stack guard now.

15/09/14 16:32:20 INFO hive.HiveImport: It's highly recommended that you fix the library with 'execstack -c <libfile>', or link it with '-z noexecstack'.

15/09/14 16:32:41 INFO hive.HiveImport: OK

15/09/14 16:32:41 INFO hive.HiveImport: Time taken: 20.792 seconds

15/09/14 16:32:45 INFO hive.HiveImport: Loading data to table megamillionanalysishive.megamilliondataextractionhive

15/09/14 16:32:47 INFO hive.HiveImport: OK

15/09/14 16:32:47 INFO hive.HiveImport: Time taken: 5.428 seconds

15/09/14 16:32:47 INFO hive.HiveImport: Hive import complete.

15/09/14 16:32:47 INFO hive.HiveImport: Export directory is empty, removing it.

OpenJDK Server VM warning: You have loaded library /home/notroot/lab/software/hadoop-2.6.0/lib/native/libhadoop.so.1.0.0 which might have disabled stack guard. The VM will try to fix the stack guard now.

It's highly recommended that you fix the library with 'execstack -c <libfile>', or link it with '-z noexecstack'.

15/09/14 16:32:54 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable

Found 7 items

-rw-r--r--   1 root supergroup          0 2015-09-14 16:32 /user/hive/warehouse/megamillionanalysishive.db/megamilliondataextractionhive/_SUCCESS

-rw-r--r--   1 root supergroup          0 2015-09-14 16:30 /user/hive/warehouse/megamillionanalysishive.db/megamilliondataextractionhive/part-m-00000

-rw-r--r--   1 root supergroup      13517 2015-09-14 16:30 /user/hive/warehouse/megamillionanalysishive.db/megamilliondataextractionhive/part-m-00001

-rw-r--r--   1 root supergroup          0 2015-09-14 16:31 /user/hive/warehouse/megamillionanalysishive.db/megamilliondataextractionhive/part-m-00002

-rw-r--r--   1 root supergroup          0 2015-09-14 16:31 /user/hive/warehouse/megamillionanalysishive.db/megamilliondataextractionhive/part-m-00003

-rw-r--r--   1 root supergroup      10754 2015-09-14 16:31 /user/hive/warehouse/megamillionanalysishive.db/megamilliondataextractionhive/part-m-00004

-rw-r--r--   1 root supergroup       2654 2015-09-14 16:31 /user/hive/warehouse/megamillionanalysishive.db/megamilliondataextractionhive/part-m-00005

.

.

.

 

 

Output content:

HIVE output table sorted by profit:

WARNING: org.apache.hadoop.metrics.jvm.EventCounter is deprecated. Please use or

g.apache.hadoop.log.metrics.EventCounter in all the log4j.properties files.

Logging initialized using configuration in jar:file:/home/notroot/lab/software/h

ive-0.9.0-bin/lib/hive-common-0.9.0.jar!/hive-log4j.properties

Hive history file=/tmp/root/hive_job_log_root_201509031802_2056931738.txt

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hadoop-2.6.0/share/

hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.cla

ss]

SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hive-0.9.0-bin/lib/

slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

OpenJDK Server VM warning: You have loaded library /home/notroot/lab/software/ha

doop-2.6.0/lib/native/libhadoop.so.1.0.0 which might have disabled stack guard.

The VM will try to fix the stack guard now.

It's highly recommended that you fix the library with 'execstack -c <libfile>',

or link it with '-z noexecstack'.

hive> use megamillionAnalysisHive;

FAILED: Error in metadata: javax.jdo.JDOFatalDataStoreException: Failed to start

 database 'metastore_db', see the next exception for details.

NestedThrowables:

java.sql.SQLException: Failed to start database 'metastore_db', see the next exc

eption for details.

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTa

sk

hive> #select Period,AnalysisType,Profit,WinPercent,Win4nums from megamilliondat

aextractionhive sort by AnalysisType,Period;

FAILED: Parse Error: line 1:0 character '#' not supported here

 

hive> select Period,AnalysisType,Profit,WinPercent,Win4nums from megamilliondata

extractionhive sort by Profit;

FAILED: Error in semantic analysis: Unable to fetch table megamilliondataextract

ionhive

hive> root@lubuntu:/home/notroot/lab/workspacehadoopHW# hive < hivemegamillionPo

essingSortProfit

WARNING: org.apache.hadoop.metrics.jvm.EventCounter is deprecated. Please use or

g.apache.hadoop.log.metrics.EventCounter in all the log4j.properties files.

Logging initialized using configuration in jar:file:/home/notroot/lab/software/h

ive-0.9.0-bin/lib/hive-common-0.9.0.jar!/hive-log4j.properties

Hive history file=/tmp/root/hive_job_log_root_201509031816_934721189.txt

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hadoop-2.6.0/share/

hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.cla

ss]

SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hive-0.9.0-bin/lib/

slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

OpenJDK Server VM warning: You have loaded library /home/notroot/lab/software/ha

doop-2.6.0/lib/native/libhadoop.so.1.0.0 which might have disabled stack guard.

The VM will try to fix the stack guard now.

It's highly recommended that you fix the library with 'execstack -c <libfile>',

or link it with '-z noexecstack'.

hive> use megamillionAnalysisHive;

OK

Time taken: 14.864 seconds

hive> #select Period,AnalysisType,Profit,WinPercent,Win4nums from megamilliondat

aextractionhive sort by AnalysisType,Period;

FAILED: Parse Error: line 1:0 character '#' not supported here

 

hive> select Period,AnalysisType,Profit,WinPercent,Win4nums from megamilliondata

extractionhive sort by Profit;

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>

15/09/03 18:16:50 INFO Configuration.deprecation: mapred.job.name is deprecated.

 Instead, use mapreduce.job.name

15/09/03 18:16:50 INFO Configuration.deprecation: mapred.system.dir is deprecate

d. Instead, use mapreduce.jobtracker.system.dir

15/09/03 18:16:50 INFO Configuration.deprecation: mapred.local.dir is deprecated

. Instead, use mapreduce.cluster.local.dir

15/09/03 18:16:50 WARN conf.HiveConf: hive-site.xml not found on CLASSPATH

WARNING: org.apache.hadoop.metrics.jvm.EventCounter is deprecated. Please use or

g.apache.hadoop.log.metrics.EventCounter in all the log4j.properties files.

Execution log at: /tmp/root/root_20150903181616_2314e676-e812-4b0a-9743-a1443deb

5491.log

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hadoop-2.6.0/share/

hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.cla

ss]

SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hive-0.9.0-bin/lib/

slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

OpenJDK Server VM warning: You have loaded library /home/notroot/lab/software/ha

doop-2.6.0/lib/native/libhadoop.so.1.0.0 which might have disabled stack guard.

The VM will try to fix the stack guard now.

It's highly recommended that you fix the library with 'execstack -c <libfile>',

or link it with '-z noexecstack'.

Job running in-process (local Hadoop)

Hadoop job information for null: number of mappers: 2; number of reducers: 1

2015-09-03 18:17:36,709 null map = 0%,  reduce = 0%

2015-09-03 18:18:28,742 null map = 50%,  reduce = 0%, Cumulative CPU 8.28 sec

2015-09-03 18:18:30,523 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:31,691 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:33,159 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:35,230 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:36,828 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:38,202 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:40,708 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:41,900 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:43,313 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:44,851 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:46,572 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:47,753 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:49,000 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:50,108 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:51,404 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:53,075 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:54,268 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:55,573 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:57,079 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:18:58,948 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:19:00,398 null map = 100%,  reduce = 0%, Cumulative CPU

10.06 sec

2015-09-03 18:19:01,889 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:19:03,332 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:19:05,021 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:19:06,265 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:19:07,511 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:19:09,053 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:19:10,342 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:19:11,816 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:19:14,068 null map = 100%,  reduce = 0%, Cumulative CPU 10.06 sec

2015-09-03 18:19:15,460 null map = 100%,  reduce = 67%, Cumulative CPU 13.45 sec

2015-09-03 18:19:17,381 null map = 100%,  reduce = 100%, Cumulative CPU 14.46 se

c

2015-09-03 18:19:18,790 null map = 100%,  reduce = 100%, Cumulative CPU 14.46 se

c

2015-09-03 18:19:20,423 null map = 100%,  reduce = 100%, Cumulative CPU 14.46 sec

MapReduce Total cumulative CPU time: 14 seconds 460 msec

Ended Job = job_1441040443320_0211

Execution completed successfully

Mapred Local Task Succeeded . Convert the Join into MapJoin

OK

2007to2005      top5numsNmegaR      -11      2      1

2013to2005      top5Nmega3R      -135      9      0

2013to2005    top62R      -137      6      0

2013to2005      top5Nmega      -139      1      0

2013to2005      individualtop5Nmega2 -140      9      0

2013to2005      individualtop5Nmega   -141      7      0

2013to2005      individualtop5NmegaSL1      -141      7      0

2013to2005      individualtop5Nmega3 -143      7      0

2013to2005    top63      -144      5      0

2013to2005            top5numsNmega2            -146            7            0

2013to2005            top6R            -147            5            0

2013to2005            top62            -148            5            0

2013to2005            top6            -148            1            0

2013to2005            top5NmegaR            -148            1            0

2013to2005            top5numsNmega3            -148            4            0

2013to2005            individualtop5NmegaSL4            -157            0            0

2013to2005            all6avg            -157            0            0

2013to2005            all6avgR            -157            0            0

2013to2005            all6med            -157            0            0

2013to2005            all6medR            -157            0            0

2013to2005            bottomall6            -157            0            0

2013to2005            bottomall6R            -157            0            0

2013to2005            individualbottomall6            -157            0            0

2013to2005            individualbottomall6R            -157            0            0

2013to2005            individualtop5Nmega2R            -157            0            0

2013to2005            individualtop5Nmega3R            -157            0            0

2013to2005            individualtop5NmegaR            -157            0            0

2013to2005            individualtop5NmegaSL2            -157            0            0

2013to2005            individualtop5NmegaSL3            -157            0            0

2013to2005            top5Nmega2            -157            0            0

2013to2005            top5Nmega2R            -157            0            0

2013to2005            top5Nmega3            -157            0            0

2013to2005            top5numsNmega            -157            0            0

2013to2005            top5numsNmega2R            -157            0            0

2013to2005            top5numsNmega3R            -157            0            0

2013to2005            top5numsNmegaR            -157            0            0

2013to2005            top63R            -157            0            0

2005to2005            all6med            -164            4            1

2012to2005            top63            -181            4            0

2012to2005            top5Nmega3R            -185            4            0

2012to2005            individualbottomall6R            -198            3            0

2012to2005            individualtop5Nmega3            -222            7            0

2012to2005            top5numsNmega2R            -224            8            0

2012to2005            individualtop5NmegaSL1            -229            5            0

2012to2005            individualtop5Nmega3R            -230            7            0

2012to2005            top5numsNmega3R            -230            7            0

2012to2005            individualtop5Nmega2            -238            7            0

2012to2005            all6med            -242            2            0

2012to2005            all6avg            -242            2            0

2012to2005            top5Nmega            -243            1            0

2012to2005            individualtop5NmegaSL3            -243            2            0

2012to2005            individualtop5NmegaR            -243            1            0

2012to2005            top6            -244            1            0

2012to2005            top5numsNmega            -244            1            0

2012to2005            top5numsNmega3            -246            5            0

2005to2005            top5Nmega3  -247            0            1

2012to2005      individualtop5NmegaSL2      -247      0      0

2012to2005      individualtop5Nmega2R      -248      4      0

2012to2005      top5NmegaR      -248      1      0

2012to2005    top62R      -249      4      0

2012to2005      top5Nmega3      -249      1      0

2012to2005      top5Nmega2R      -252      0      0

2012to2005      bottomall6 -253      0      0

2012to2005    top6R      -253      0      0

2012to2005      top5numsNmega2      -253      0      0

2012to2005      top5Nmega2      -259      1      0

2012to2005      top5numsNmegaR      -261      0      0

2012to2005      bottomall6R      -262      0      0

2012to2005      all6medR    -262      0      0

2012to2005      all6avgR    -262      0      0

2012to2005    top63R      -262      0      0

2012to2005    top62      -262      0      0

2011to2005      individualtop5Nmega3R      -286      6      0

2011to2005      top5numsNmega3R    -297      7      0

2011to2005    all6avg      -315      2      0

2011to2005      top5Nmega3R      -316      2      0

2011to2005      individualtop5Nmega2 -321      6      0

2011to2005      individualtop5Nmega   -329      4      0

2011to2005    top63      -329      4      0

2011to2005      top5Nmega2R      -332      4      0

2011to2005      top5numsNmega2R    -333      5      0

2011to2005      all6medR    -334      2      0

2011to2005      individualtop5Nmega3 -335      4      0

2011to2005            top5Nmega    -337            2            0

2011to2005      individualtop5Nmega2R      -338      4      0

2011to2005      top5numsNmega3      -338      5      0

2011to2005    top62      -339      3      0

2011to2005      top5numsNmega      -339      1      0

2011to2005      top5numsNmegaR      -340      3      0

2011to2005      individualtop5NmegaSL1      -340      3      0

2011to2005      all6avgR    -341      2      0

2011to2005    all6med      -348      1      0

2011to2005      individualtop5NmegaSL4      -348      1      0

2011to2005    top6      -348      1      0

2011to2005    top62R      -350      2      0

2011to2005      individualtop5NmegaSL2      -352      1      0

2011to2005    top63R      -352      1      0

2011to2005      bottomall6R      -353      2      0

2011to2005      top5numsNmega2      -354      2      0

2011to2005      individualtop5NmegaR      -355      1      0

2011to2005      bottomall6 -357      0      0

2011to2005            individualtop5NmegaSL3        -357            0            0

2011to2005         individualbottomall6R         -357         2         0

2011to2005         top6R         -357         2         0

2011to2005         top5NmegaR         -358         1         0

2011to2005         individualbottomall6         -358         2         0

2014to2005         individualtop5Nmega2       -36         15         0

2010to2005         top63         -361         5         0

2011to2005         top5Nmega2         -364         1         0

2010to2005         top5numsNmega2         -374         2         0

2014to2005         top5Nmega -41         10         0

2010to2005         individualtop5Nmega2       -414         4         0

2010to2005         top5numsNmega2R         -415         4         0

2010to2005         bottomall6R -415         5         0

2010to2005         top62R         -418         3         0

2010to2005         individualtop5NmegaSL1   -431         3         0

2010to2005         individualtop5Nmega         -433         3         0

2010to2005         top62         -434         3         0

2010to2005         top6         -437         2         0

2010to2005         all6med         -437         1         0

2010to2005         all6medR       -437         1         0

2010to2005         top5Nmega3         -439         1         0

2010to2005         all6avg         -439         1         0

2010to2005         individualbottomall6         -439         1         0

2014to2005         top5NmegaR         -44         13         0

2010to2005         top5numsNmega3         -440         3         0

2010to2005         individualtop5Nmega2R     -440         3         0

2010to2005         bottomall6    -443         1         0

2010to2005         top5numsNmegaR         -443         2         0

2010to2005         individualtop5NmegaSL4   -443         1         0

2010to2005         individualtop5Nmega3       -448         3         0

2010to2005         all6avgR       -449         2         0

2014to2005         top5numsNmegaR         -45         12         0

2010to2005         top5Nmega2         -451         2         0

2010to2005         individualtop5NmegaSL3   -452         0         0

2010to2005         individualtop5NmegaSL2   -453         1         0

2010to2005         individualtop5NmegaR       -453         2         0

2010to2005         top5NmegaR         -453         3         0

2010to2005         top6R         -453         1         0

2010to2005         top5Nmega -454         1         0

2010to2005         individualbottomall6R         -454         1         0

2014to2005         individualtop5Nmega         -46         10         0

2014to2005         individualtop5NmegaR       -46         11         0

2010to2005         top5Nmega2R         -463         1         0

2009to2005         top5numsNmegaR         -463         4         0

2014to2005         top5Nmega2         -47         8         0

2014to2005         individualtop5NmegaSL1   -47         8         0

2014to2005            top5numsNmega2R            -48            6            0

2014to2005   individualtop5Nmega2R   -48   6   0

2014to2005   top5Nmega2R   -49   6   0

2009to2005   individualbottomall6   -496   2   0

2014to2005   top5Nmega3R   -50   6   0

2014to2005   top5Nmega3   -50   6   0

2014to2005   top63   -50   6   0

2014to2005   top5numsNmega3R   -51   4   0

2014to2005   individualtop5Nmega3R   -51   4   0

2009to2005   individualtop5Nmega   -518   4   0

2009to2005   individualtop5NmegaSL1   -518   4   0

2009to2005   bottomall6R   -519   3   0

2014to2005   individualtop5Nmega3   -52   2   0

2009to2005   top63   -523   4   0

2009to2005   all6avg   -525   4   0

2009to2005   individualtop5Nmega3R   -525   4   0

2009to2005   individualtop5Nmega3   -526   5   0

2009to2005   top5Nmega3   -529   3   0

2014to2005   top63R   -53   0   0

2014to2005   all6avg   -53   0   0

2014to2005   all6avgR -53   0   0

2014to2005   all6med   -53   0   0

2014to2005   all6medR -53   0   0

2014to2005   bottomall6   -53   0   0

2014to2005   bottomall6R   -53   0   0

2014to2005   individualbottomall6   -53   0   0

2014to2005   individualbottomall6R   -53   0   0

2014to2005   individualtop5NmegaSL2   -53   0   0

2014to2005            individualtop5NmegaSL3        -53            0            0

2014to2005   individualtop5NmegaSL4   -53   0   0

2014to2005   top5numsNmega -53   0   0

2014to2005   top5numsNmega2   -53   0   0

2014to2005   top5numsNmega3   -53   0   0

2014to2005   top6   -53   0   0

2014to2005   top62   -53   0   0

2014to2005   top6R   -53   0   0

2014to2005   top62R   -53   0   0

2009to2005   individualtop5NmegaSL2   -532   2   0

2010to2005   top5Nmega3R   -533   4   0

2009to2005   top62R   -534   2   0

2009to2005   all6medR -534   2   0

2009to2005   individualtop5NmegaR   -534   2   0

2009to2005   top5numsNmega3R   -536   2   0

2009to2005   individualtop5Nmega2   -537   3   0

2010to2005   top5numsNmega3R   -537   2   0

2009to2005   individualtop5NmegaSL4   -539   1   0

2009to2005            bottomall6       -539            1            0

2009to2005      top5numsNmega      -539      1      0

2009to2005      all6avgR    -540      2      0

2009to2005      top5Nmega3R      -540      2      0

2009to2005      individualtop5NmegaSL3      -541      1      0

2009to2005      top5NmegaR      -541      3      0

2009to2005      top5numsNmega2R    -548      1      0

2010to2005    top63R      -551      2      0

2010to2005      individualtop5Nmega3R      -553      3      0

2009to2005      top5Nmega2R      -554      2      0

2009to2005    top63R      -554      2      0

2009to2005      individualbottomall6R  -555      1      0

2009to2005      top5Nmega2      -556      3      0

2008to2005      individualtop5Nmega3 -556      4      0

2009to2005      top5Nmega      -556      3      0

2009to2005    top6R      -557      0      0

2009to2005    top6      -557      0      0

2009to2005      top5numsNmega3      -561      2      0

2009to2005    top62      -566      0      0

2009to2005      individualtop5Nmega2R      -570      1      0

2008to2005      individualtop5NmegaSL1      -609      5      0

2008to2005      top5numsNmega2R    -616      2      0

2008to2005      individualtop5NmegaSL2      -619      2      0

2008to2005      individualtop5NmegaR      -620      3      0

2008to2005      individualtop5NmegaSL3      -624      2      0

2008to2005      individualbottomall6    -625      3      0

2008to2005    top62R      -625      3      0

2008to2005    top62      -626      3      0

2008to2005            individualtop5Nmega3R          -630            1            0

2008to2005      individualtop5Nmega2 -630      4      0

2008to2005      top5NmegaR      -636      1      0

2008to2005      all6avgR    -636      2      0

2008to2005      bottomall6R      -639      4      0

2008to2005      top5numsNmega      -640      3      0

2008to2005    top6      -640      3      0

2008to2005      top5numsNmega3R    -644      1      0

2008to2005    all6avg      -646      2      0

2008to2005      top5Nmega      -646      2      0

2007to2005      all6avgR    -646      3      0

2008to2005      individualtop5Nmega2R      -647      2      0

2008to2005      top5Nmega3      -647      1      0

2008to2005      individualbottomall6R  -648      2      0

2008to2005      top5Nmega2R      -649      1      0

2007to2005      top5Nmega3      -650      4      0

2008to2005    all6med      -651      1      0

2008to2005    top6R      -652      2      0

2008to2005            top5Nmega3R            -652            2            0

2008to2005      top5numsNmegaR      -652      2      0

2008to2005    top63      -652      2      0

2008to2005      top5numsNmega3      -656      1      0

2008to2005      all6medR    -656      2      0

2008to2005      top5Nmega2      -658      1      0

2008to2005    top63R      -661      0      0

2008to2005      top5numsNmega2      -661      0      0

2008to2005      bottomall6 -670      0      0

2007to2005    top62R      -676      1      0

2007to2005      individualtop5Nmega3R      -690      3      0

2007to2005      individualtop5Nmega3 -702      3      0

2007to2005      top5numsNmega3R    -705      2      0

2007to2005      top5Nmega2R      -707      4      0

2007to2005      individualtop5NmegaSL2      -710      2      0

2007to2005      all6medR    -712      2      0

2007to2005      top5Nmega2      -715      3      0

2007to2005    top62      -717      3      0

2007to2005    top6R      -720      3      0

2007to2005      top5Nmega3R      -720      4      0

2007to2005      top5Nmega      -722      4      0

2007to2005    top63R      -726      2      0

2007to2005    all6med      -731      2      0

2007to2005      individualbottomall6    -731      2      0

2007to2005      individualbottomall6R  -732      2      0

2007to2005      individualtop5NmegaSL1      -735      3      0

2007to2005      top5numsNmega2R    -736      2      0

2007to2005      individualtop5Nmega   -736      2      0

2007to2005      top5NmegaR      -739      1      0

2007to2005      top5numsNmega      -739      1      0

2007to2005    all6avg      -741      1      0

2007to2005      individualtop5NmegaR      -744      2      0

2007to2005      individualtop5NmegaSL3      -744      2      0

2007to2005      top5numsNmega2      -746      3      0

2007to2005      individualtop5Nmega2 -746      3      0

2007to2005      top5numsNmega3      -752      1      0

2007to2005      bottomall6R      -753      2      0

2007to2005    top63      -757      0      0

2007to2005    top6      -757      0      0

2007to2005      individualtop5NmegaSL4      -766      0      0

2007to2005      individualtop5Nmega2R      -767      2      0

2007to2005      bottomall6 -784      0      0

2006to2005      top5Nmega      -788      3      0

2006to2005      individualbottomall6    -791      3      0

2006to2005      top5numsNmega2R    -799      2      0

2006to2005    all6avg      -814      4      0

2006to2005            top6            -816            2            0

2006to2005      individualtop5Nmega2R      -822      3      0

2006to2005      top5Nmega3      -825      1      0

2006to2005      individualtop5Nmega3R      -826      2      0

2006to2005      individualtop5Nmega2 -829      3      0

2006to2005    top62R      -831      2      0

2006to2005      top5numsNmegaR      -833      2      0

2006to2005      top5numsNmega      -833      2      0

2006to2005      individualbottomall6R  -834      3      0

2006to2005      top5NmegaR      -835      2      0

2005to2005    top6R      -835      4      0

2006to2005      bottomall6R      -838      2      0

2006to2005      individualtop5NmegaSL2      -839      2      0

2006to2005      individualtop5NmegaR      -839      2      0

2006to2005      bottomall6 -840      3      0

2006to2005      top5Nmega2      -842      1      0

2006to2005      all6medR    -843      2      0

2006to2005      all6avgR    -846      1      0

2006to2005      individualtop5NmegaSL3      -846      2      0

2006to2005    top63      -847      1      0

2006to2005      individualtop5NmegaSL1      -847      3      0

2006to2005      top5numsNmega3R    -848      2      0

2006to2005      individualtop5Nmega3 -848      3      0

2006to2005      top5Nmega3R      -850      1      0

2006to2005      top5numsNmega2      -852      0      0

2006to2005      top5numsNmega3      -853      1      0

2006to2005    all6med      -854      2      0

2006to2005      individualtop5Nmega   -857      2      0

2005to2005      individualbottomall6R  -858      3      0

2006to2005    top62      -861      0      0

2006to2005    top6R      -861      0      0

2006to2005    top63R      -870      1      0

2006to2005      top5Nmega2R      -870      0      0

2006to2005      individualtop5NmegaSL4      -879      0      0

2005to2005      top5numsNmega3R    -880      2      0

2005to2005      all6medR    -889      3      0

2005to2005    all6avg      -904      4      0

2005to2005      individualtop5Nmega2R      -910      4      0

2005to2005      individualbottomall6    -911      1      0

2005to2005      individualtop5Nmega2 -912      3      0

2005to2005      top5Nmega2R      -913      3      0

2005to2005      individualtop5NmegaSL1      -919      2      0

2005to2005      top5Nmega3R      -921      2      0

2005to2005      top5NmegaR      -924      2      0

2005to2005      top5Nmega      -925      3      0

2005to2005    top62R      -930      3      0

2005to2005            top5numsNmegaR            -930            3            0

2005to2005         individualtop5Nmega3R     -932         2         0

2005to2005         individualtop5Nmega3       -936         4         0

2005to2005         top5Nmega2         -936         3         0

2005to2005         top5numsNmega2R         -937         3         0

2005to2005         individualtop5Nmega         -937         2         0

2005to2005         top6         -938         1         0

2005to2005         bottomall6    -939         3         0

2005to2005         individualtop5NmegaR       -942         3         0

2005to2005         top63R         -946         2         0

2005to2005         top63         -947         1         0

2005to2005         all6avgR       -949         2         0

2005to2005         top62         -949         3         0

2005to2005         individualtop5NmegaSL4   -956         0         0

2005to2005         individualtop5NmegaSL3   -956         0         0

2005to2005         bottomall6R -958         2         0

2005to2005         individualtop5NmegaSL2   -960         2         0

2005to2005         top5numsNmega3         -965         0         0

2005to2005         top5numsNmega2         -974         0         0

2005to2005         top5numsNmega         -974         0         0

2009to2005         all6med       173         2         1

2009to2005         top5numsNmega2         182         3         1

2010to2005         top5numsNmega         280         3         1

2011to2005         top5Nmega3         382         3         1

2012to2005         individualbottomall6       459         0         1

2012to2005         individualtop5NmegaSL4         465         0         1

2012to2005         individualtop5Nmega     480         5         1

2008to2005         individualtop5NmegaSL4         84         1         1

2008to2005         individualtop5Nmega     89         3         1

 

 HIVE output table sorted by WinPercent:

WARNING: org.apache.hadoop.metrics.jvm.EventCounter is deprecated. Please use org.apache.hadoop.log.metrics.EventCounter in all the log4j.properties files.

Logging initialized using configuration in jar:file:/home/notroot/lab/software/h

ive-0.9.0-bin/lib/hive-common-0.9.0.jar!/hive-log4j.properties

Hive history file=/tmp/root/hive_job_log_root_201509031828_1087483748.txt

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hadoop-2.6.0/share/

hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.cla

ss]

SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hive-0.9.0-bin/lib/

slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

OpenJDK Server VM warning: You have loaded library

/home/notroot/lab/software/hadoop-2.6.0/lib/native/libhadoop.so.1.0.0 which might have disabled stack guard.

The VM will try to fix the stack guard now.

It's highly recommended that you fix the library with 'execstack -c <libfile>',

or link it with '-z noexecstack'.

hive> use megamillionAnalysisHive;

OK

Time taken: 20.102 seconds

hive> #select Period,AnalysisType,Profit,WinPercent,Win4nums from megamilliondat

aextractionhive sort by AnalysisType,Period;

FAILED: Parse Error: line 1:0 character '#' not supported here

 

hive> select Period,AnalysisType,Profit,WinPercent,Win4nums from megamilliondata

extractionhive sort by WinPercent;

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>

15/09/03 18:29:12 INFO Configuration.deprecation: mapred.job.name is deprecated.

 Instead, use mapreduce.job.name

15/09/03 18:29:12 INFO Configuration.deprecation: mapred.system.dir is deprecate

d. Instead, use mapreduce.jobtracker.system.dir

15/09/03 18:29:12 INFO Configuration.deprecation: mapred.local.dir is deprecated

. Instead, use mapreduce.cluster.local.dir

15/09/03 18:29:12 WARN conf.HiveConf: hive-site.xml not found on CLASSPATH

WARNING: org.apache.hadoop.metrics.jvm.EventCounter is deprecated. Please use or

g.apache.hadoop.log.metrics.EventCounter in all the log4j.properties files.

Execution log at: /tmp/root/root_20150903182929_91bdd0ac-e29d-4727-b5ff-5ae860d7

7371.log

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hadoop-2.6.0/share/

hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.cla

ss]

SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hive-0.9.0-bin/lib/

slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

OpenJDK Server VM warning: You have loaded library /home/notroot/lab/software/ha

doop-2.6.0/lib/native/libhadoop.so.1.0.0 which might have disabled stack guard.

The VM will try to fix the stack guard now.

It's highly recommended that you fix the library with 'execstack -c <libfile>',

or link it with '-z noexecstack'.

Job running in-process (local Hadoop)

Hadoop job information for null: number of mappers: 2; number of reducers: 1

2015-09-03 18:31:01,402 null map = 0%,  reduce = 0%

2015-09-03 18:32:02,496 null map = 0%,  reduce = 0%

2015-09-03 18:32:05,406 null map = 50%,  reduce = 0%, Cumulative CPU 2.48 sec

2015-09-03 18:32:06,700 null map = 50%,  reduce = 0%, Cumulative CPU 2.48 sec

2015-09-03 18:32:07,864 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:09,106 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:10,320 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:11,752 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:12,988 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:14,158 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:15,299 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:16,425 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:17,521 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:18,680 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:19,824 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:20,904 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:21,982 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:23,069 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:24,280 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:25,398 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:26,624 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:27,732 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:28,869 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:30,005 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:31,114 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:32,179 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:33,280 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:34,423 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:35,598 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:36,680 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:37,789 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:38,905 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:40,004 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:41,090 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:42,235 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:43,364 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:44,466 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:45,682 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:47,003 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:48,352 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:50,140 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:51,434 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:52,650 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:54,165 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:55,322 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:56,600 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:57,817 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:32:59,043 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:33:00,294 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:33:01,658 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:33:02,935 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:33:04,505 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:33:05,775 null map = 100%,  reduce = 0%, Cumulative CPU 5.32 sec

2015-09-03 18:33:07,213 null map = 100%,  reduce = 100%, Cumulative CPU 9.34 sec

2015-09-03 18:33:09,146 null map = 100%,  reduce = 100%, Cumulative CPU 9.34 sec

2015-09-03 18:33:10,239 null map = 100%,  reduce = 100%, Cumulative CPU 9.34 sec

2015-09-03 18:33:11,396 null map = 100%,  reduce = 100%, Cumulative CPU 9.34 sec

MapReduce Total cumulative CPU time: 9 seconds 340 msec

Ended Job = job_1441040443320_0212

Execution completed successfully

Mapred Local Task Succeeded . Convert the Join into MapJoin

OK

2014to2005         top6R         -53         0         0

2014to2005         top63R         -53         0         0

2014to2005         top62R         -53         0         0

2014to2005         top62         -53         0         0

2014to2005         top6         -53         0         0

2014to2005         top5numsNmega3         -53         0         0

2014to2005            top5numsNmega2            -53            0            0

2014to2005            top5numsNmega            -53            0            0

2014to2005            individualtop5NmegaSL4        -53            0            0

2014to2005            individualtop5NmegaSL3        -53            0            0

2014to2005            individualtop5NmegaSL2        -53            0            0

2014to2005            individualbottomall6R            -53            0            0

2014to2005            individualbottomall6            -53            0            0

2014to2005            bottomall6R    -53            0            0

2014to2005            bottomall6       -53            0            0

2014to2005            all6medR          -53            0            0

2014to2005            all6med            -53            0            0

2014to2005            all6avgR          -53            0            0

2014to2005            all6avg            -53            0            0

2013to2005            top63R            -157            0            0

2013to2005            top5numsNmegaR            -157            0            0

2013to2005            top5numsNmega3R            -157            0            0

2013to2005            top5numsNmega2R            -157            0            0

2013to2005            top5numsNmega            -157            0            0

2013to2005            top5Nmega3  -157            0            0

2013to2005            top5Nmega2R            -157            0            0

2013to2005            top5Nmega2  -157            0            0

2013to2005            individualtop5NmegaSL4        -157            0            0

2013to2005            individualtop5NmegaSL3        -157            0            0

2013to2005            individualtop5NmegaSL2        -157            0            0

2013to2005            individualtop5NmegaR            -157            0            0

2013to2005            individualtop5Nmega3R          -157            0            0

2013to2005            individualtop5Nmega2R          -157            0            0

2013to2005            individualbottomall6R            -157            0            0

2013to2005            individualbottomall6            -157            0            0

2013to2005            bottomall6R    -157            0            0

2013to2005            bottomall6       -157            0            0

2013to2005            all6medR          -157            0            0

2013to2005            all6med            -157            0            0

2013to2005            all6avgR          -157            0            0

2013to2005            all6avg            -157            0            0

2012to2005            top6R            -253            0            0

2012to2005            top63R            -262            0            0

2012to2005            top62            -262            0            0

2012to2005            top5numsNmegaR            -261            0            0

2012to2005            top5numsNmega2            -253            0            0

2012to2005            top5Nmega2R            -252            0            0

2012to2005            individualtop5NmegaSL4        465            0            1

2012to2005            individualtop5NmegaSL2        -247            0            0

2012to2005            individualbottomall6            459            0            1

2012to2005            bottomall6R    -262            0            0

2012to2005            bottomall6       -253            0            0

2012to2005            all6medR          -262            0            0

2012to2005            all6avgR          -262            0            0

2011to2005            individualtop5NmegaSL3        -357            0            0

2011to2005            bottomall6       -357            0            0

2010to2005            individualtop5NmegaSL3        -452            0            0

2009to2005            top6R            -557            0            0

2009to2005            top62            -566            0            0

2009to2005            top6            -557            0            0

2008to2005            top63R            -661            0            0

2008to2005            top5numsNmega2            -661            0            0

2008to2005            bottomall6       -670            0            0

2007to2005            top63            -757            0            0

2007to2005            top6            -757            0            0

2007to2005            individualtop5NmegaSL4        -766            0            0

2007to2005            bottomall6       -784            0            0

2006to2005            top6R            -861            0            0

2006to2005            top62            -861            0            0

2006to2005            top5numsNmega2            -852            0            0

2006to2005            top5Nmega2R            -870            0            0

2006to2005            individualtop5NmegaSL4        -879            0            0

2005to2005            top5numsNmega3            -965            0            0

2005to2005            top5numsNmega2            -974            0            0

2005to2005            top5numsNmega            -974            0            0

2005to2005            top5Nmega3  -247            0            1

2005to2005            individualtop5NmegaSL4        -956            0            0

2005to2005            individualtop5NmegaSL3        -956            0            0

2012to2005            top6            -244            1            0

2012to2005            top5numsNmega            -244            1            0

2012to2005            top5NmegaR  -248            1            0

2012to2005            top5Nmega3  -249            1            0

2012to2005            top5Nmega2  -259            1            0

2012to2005            top5Nmega    -243            1            0

2005to2005            top6            -938            1            0

2012to2005            individualtop5NmegaR            -243            1            0

2011to2005            top63R            -352            1            0

2011to2005            top6            -348            1            0

2011to2005            top5numsNmega            -339            1            0

2011to2005            top5NmegaR  -358            1            0

2011to2005            top5Nmega2  -364            1            0

2011to2005            individualtop5NmegaSL4        -348            1            0

2011to2005            individualtop5NmegaSL2        -352            1            0

2011to2005            individualtop5NmegaR            -355            1            0

2011to2005            all6med            -348            1            0

2010to2005            top6R            -453            1            0

2010to2005            top5Nmega3  -439            1            0

2010to2005            top5Nmega2R            -463            1            0

2010to2005            top5Nmega    -454            1            0

2010to2005         individualtop5NmegaSL4   -443         1         0

2010to2005         individualtop5NmegaSL2   -453         1         0

2010to2005         individualbottomall6R         -454         1         0

2010to2005         individualbottomall6         -439         1         0

2010to2005         bottomall6    -443         1         0

2010to2005         all6medR       -437         1         0

2010to2005         all6med         -437         1         0

2010to2005         all6avg         -439         1         0

2009to2005         top5numsNmega2R         -548         1         0

2009to2005         top5numsNmega         -539         1         0

2009to2005         individualtop5NmegaSL4   -539         1         0

2009to2005         individualtop5NmegaSL3   -541         1         0

2009to2005         individualtop5Nmega2R     -570         1         0

2009to2005         individualbottomall6R         -555         1         0

2009to2005         bottomall6    -539         1         0

2005to2005         top63         -947         1         0

2008to2005         top5numsNmega3R         -644         1         0

2008to2005         top5numsNmega3         -656         1         0

2008to2005         top5NmegaR         -636         1         0

2008to2005         top5Nmega3         -647         1         0

2008to2005         top5Nmega2R         -649         1         0

2008to2005         top5Nmega2         -658         1         0

2008to2005         individualtop5NmegaSL4   84         1         1

2008to2005         individualtop5Nmega3R     -630         1         0

2008to2005         all6med         -651         1         0

2007to2005         top62R         -676         1         0

2007to2005         top5numsNmega3         -752         1         0

2007to2005         top5numsNmega         -739         1         0

2007to2005         top5NmegaR         -739         1         0

2007to2005         all6avg         -741         1         0

2006to2005         top63R         -870         1         0

2006to2005         top63         -847         1         0

2006to2005         top5numsNmega3         -853         1         0

2006to2005         top5Nmega3R         -850         1         0

2013to2005         top6         -148         1         0

2006to2005         top5Nmega3         -825         1         0

2006to2005         top5Nmega2         -842         1         0

2013to2005         top5NmegaR         -148         1         0

2005to2005         individualbottomall6         -911         1         0

2013to2005         top5Nmega -139         1         0

2006to2005         all6avgR       -846         1         0

2014to2005         top5Nmega         -41         10         0

2014to2005         individualtop5Nmega     -46         10         0

2014to2005         individualtop5NmegaR  -46         11         0

2014to2005         top5numsNmegaR         -45         12         0

2014to2005            top5NmegaR            -44            13            0

2014to2005     individualtop5Nmega2           -36       15        0

2011to2005     all6medR          -334     2          0

2006to2005     top5numsNmega2R      -799     2          0

2011to2005     all6avgR           -341     2          0

2011to2005     all6avg  -315     2          0

2005to2005     individualtop5Nmega    -937     2          0

2010to2005     top63R -551     2          0

2010to2005     top6     -437     2          0

2010to2005     top5numsNmegaR        -443     2          0

2010to2005     top5numsNmega3R      -537     2          0

2010to2005     top5numsNmega2        -374     2          0

2006to2005     top5numsNmega          -833     2          0

2006to2005     top5NmegaR    -835     2          0

2010to2005     top5Nmega2    -451     2          0

2006to2005     all6med            -854     2          0

2010to2005     individualtop5NmegaR  -453     2          0

2010to2005     all6avgR           -449     2          0

2009to2005     top63R -554     2          0

2009to2005     top62R -534     2          0

2009to2005     top5numsNmega3R      -536     2          0

2009to2005     top5numsNmega3        -561     2          0

2009to2005     top5Nmega3R  -540     2          0

2009to2005     top5Nmega2R  -554     2          0

2009to2005     individualtop5NmegaSL2          -532     2          0

2009to2005     individualtop5NmegaR  -534     2          0

2009to2005     individualbottomall6      -496     2          0

2006to2005     individualtop5NmegaSL3          -846     2          0

2009to2005     all6medR          -534     2          0

2009to2005     all6med            173      2          1

2009to2005     all6avgR           -540     2          0

2008to2005     top6R   -652     2          0

2005to2005     all6avgR           -949     2          0

2008to2005     top63   -652     2          0

2006to2005     individualtop5NmegaSL2          -839     2          0

2008to2005     top5numsNmegaR        -652     2          0

2005to2005     top63R -946     2          0

2008to2005     top5numsNmega2R      -616     2          0

2006to2005     individualtop5NmegaR  -839     2          0

2008to2005     top5Nmega3R  -652     2          0

2006to2005     individualtop5Nmega3R            -826     2          0

2008to2005     top5Nmega      -646     2          0

2008to2005     individualtop5NmegaSL3          -624     2          0

2008to2005     individualtop5NmegaSL2          -619     2          0

2008to2005     individualtop5Nmega2R            -647     2          0

2008to2005     individualbottomall6R    -648     2          0

2005to2005     individualtop5NmegaSL2          -960     2          0

2008to2005         all6medR       -656         2         0

2008to2005         all6avgR       -636         2         0

2008to2005         all6avg         -646         2         0

2007to2005         top63R         -726         2         0

2005to2005         individualtop5NmegaSL1   -919         2         0

2007to2005         top5numsNmegaR         -11         2         1

2007to2005         top5numsNmega3R         -705         2         0

2007to2005         top5numsNmega2R         -736         2         0

2006to2005         individualtop5Nmega         -857         2         0

2005to2005         individualtop5Nmega3R     -932         2         0

2007to2005         individualtop5NmegaSL3   -744         2         0

2007to2005         individualtop5NmegaSL2   -710         2         0

2007to2005         individualtop5NmegaR       -744         2         0

2007to2005         individualtop5Nmega2R     -767         2         0

2007to2005         individualtop5Nmega         -736         2         0

2007to2005         individualbottomall6R         -732         2         0

2007to2005         individualbottomall6         -731         2         0

2007to2005         bottomall6R -753         2         0

2007to2005         all6medR       -712         2         0

2007to2005         all6med         -731         2         0

2006to2005         bottomall6R -838         2         0

2006to2005         top62R         -831         2         0

2012to2005         individualtop5NmegaSL3   -243         2         0

2005to2005         top5numsNmega3R         -880         2         0

2005to2005         bottomall6R -958         2         0

2012to2005         all6med         -242         2         0

2012to2005         all6avg         -242         2         0

2011to2005         top6R         -357         2         0

2014to2005         individualtop5Nmega3       -52         2         0

2011to2005         top62R         -350         2         0

2011to2005         top5numsNmega2         -354         2         0

2011to2005         top5Nmega3R         -316         2         0

2006to2005         top6         -816         2         0

2011to2005         top5Nmega -337         2         0

2006to2005         top5numsNmegaR         -833         2         0

2005to2005         top5NmegaR         -924         2         0

2006to2005         top5numsNmega3R         -848         2         0

2006to2005         all6medR       -843         2         0

2011to2005         individualbottomall6R         -357         2         0

2011to2005         individualbottomall6         -358         2         0

2011to2005         bottomall6R -353         2         0

2005to2005         top5Nmega3R         -921         2         0

2005to2005         top5numsNmegaR         -930         3         0

2006to2005         individualtop5Nmega2R     -822         3         0

2008to2005         individualtop5NmegaR       -620         3         0

2008to2005            individualtop5Nmega            89            3            1

2008to2005         individualbottomall6         -625         3         0

2006to2005         individualtop5Nmega2       -829         3         0

2012to2005         individualbottomall6R         -198         3         0

2007to2005         top6R         -720         3         0

2011to2005         individualtop5NmegaSL1   -340         3         0

2011to2005         top5Nmega3         382         3         1

2007to2005         top62         -717         3         0

2005to2005         individualtop5NmegaR       -942         3         0

2005to2005         top5numsNmega2R         -937         3         0

2005to2005         bottomall6    -939         3         0

2007to2005         top5numsNmega2         -746         3         0

2005to2005         all6medR       -889         3         0

2006to2005         individualbottomall6R         -834         3         0

2007to2005         top5Nmega2         -715         3         0

2007to2005         individualtop5NmegaSL1   -735         3         0

2007to2005         individualtop5Nmega3R     -690         3         0

2007to2005         individualtop5Nmega3       -702         3         0

2007to2005         individualtop5Nmega2       -746         3         0

2011to2005         top62         -339         3         0

2011to2005         top5numsNmegaR         -340         3         0

2010to2005            top5NmegaR  -453            3            0

2010to2005         individualtop5NmegaSL1   -431         3         0

2007to2005         all6avgR       -646         3         0

2010to2005         individualtop5Nmega3R     -553         3         0

2010to2005         individualtop5Nmega3       -448         3         0

2010to2005         individualtop5Nmega2R     -440         3         0

2010to2005         individualtop5Nmega         -433         3         0

2008to2005         top62R         -625         3         0

2005to2005         individualbottomall6R         -858         3         0

2010to2005         top5numsNmega         280         3         1

2005to2005         top5Nmega2R         -913         3         0

2006to2005         individualbottomall6         -791         3         0

2005to2005         top5Nmega2         -936         3         0

2005to2005         top5Nmega -925         3         0

2009to2005         top5numsNmega2         182         3         1

2009to2005         top5NmegaR         -541         3         0

2009to2005         top5Nmega3         -529         3         0

2009to2005         top5Nmega2         -556         3         0

2009to2005         top5Nmega -556         3         0

2006to2005         top5Nmega -788         3         0

2009to2005         individualtop5Nmega2       -537         3         0

2009to2005         bottomall6R -519         3         0

2005to2005         top62R         -930         3         0

2010to2005         top5numsNmega3         -440         3         0

2006to2005         bottomall6    -840         3         0

2008to2005            top62            -626            3            0

2008to2005            top6            -640            3            0

2010to2005            top62R            -418            3            0

2005to2005            top62            -949            3            0

2006to2005            individualtop5NmegaSL1        -847            3            0

2005to2005            individualtop5Nmega2            -912            3            0

2008to2005            top5numsNmega            -640            3            0

2010to2005            top62            -434            3            0

2006to2005            individualtop5Nmega3            -848            3            0

2005to2005            all6med            -164            4            1

2009to2005            top5numsNmegaR            -463            4            0

2007to2005            top5Nmega3  -650            4            0

2005to2005            individualtop5Nmega2R          -910            4            0

2007to2005            top5Nmega3R            -720            4            0

2011to2005            top5Nmega2R            -332            4            0

2005to2005            top6R            -835            4            0

2012to2005            top63            -181            4            0

2005to2005            all6avg            -904            4            0

2009to2005            individualtop5NmegaSL1        -518            4            0

2010to2005            top5Nmega3R            -533            4            0

2012to2005            top5Nmega3R            -185            4            0

2006to2005            all6avg            -814            4            0

2009to2005            individualtop5Nmega3R          -525            4            0

2014to2005            individualtop5Nmega3R          -51            4            0

2005to2005            individualtop5Nmega3            -936            4            0

2009to2005            individualtop5Nmega            -518            4            0

2011to2005            individualtop5Nmega2R          -338            4            0

2008to2005            individualtop5Nmega3            -556            4            0

2010to2005            top5numsNmega2R            -415            4            0

2008to2005            individualtop5Nmega2            -630            4            0

2012to2005            top62R            -249            4            0

2009to2005            all6avg            -525            4            0

2013to2005            top5numsNmega3            -148            4            0

2008to2005            bottomall6R    -639            4            0

2012to2005            individualtop5Nmega2R          -248            4            0

2011to2005            top63            -329            4            0

2011to2005            individualtop5Nmega3            -335            4            0

2010to2005            individualtop5Nmega2            -414            4            0

2014to2005            top5numsNmega3R            -51            4            0

2009to2005            top63            -523            4            0

2011to2005            individualtop5Nmega            -329            4            0

2007to2005            top5Nmega    -722            4            0

2007to2005            top5Nmega2R            -707            4            0

2013to2005            top63            -144            5            0

2009to2005            individualtop5Nmega3            -526            5            0

2013to2005            top62            -148            5            0

2010to2005            bottomall6R    -415            5            0

2012to2005            individualtop5NmegaSL1        -229            5            0

2008to2005            individualtop5NmegaSL1        -609            5            0

2012to2005            individualtop5Nmega            480            5            1

2012to2005            top5numsNmega3            -246            5            0

2010to2005            top63            -361            5            0

2011to2005            top5numsNmega3            -338            5            0

2013to2005            top6R            -147            5            0

2011to2005            top5numsNmega2R            -333            5            0

2014to2005            top5Nmega3R            -50            6            0

2011to2005            individualtop5Nmega3R     -286            6            0

2014to2005            top5Nmega2R            -49            6            0

2011to2005            individualtop5Nmega2        -321            6            0

2014to2005            top63            -50            6            0

2013to2005            top62R         -137            6            0

2014to2005            individualtop5Nmega2R     -48            6            0

2014to2005            top5numsNmega2R            -48            6            0

2014to2005            top5Nmega3 -50            6            0

2012to2005            top5numsNmega3R            -230            7            0

2013to2005            top5numsNmega2            -146            7            0

2013to2005            individualtop5Nmega          -141            7            0

2013to2005            individualtop5NmegaSL1   -141            7            0

2011to2005            top5numsNmega3R            -297            7            0

2013to2005            individualtop5Nmega3        -143            7            0

2012to2005            individualtop5Nmega2        -238            7            0

2012to2005            individualtop5Nmega3        -222            7            0

2012to2005            individualtop5Nmega3R     -230            7            0

2012to2005            top5numsNmega2R            -224            8            0

2014to2005            top5Nmega2 -47            8            0

2014to2005            individualtop5NmegaSL1   -47            8            0

2013to2005            top5Nmega3R            -135            9            0

2013to2005            individualtop5Nmega2        -140            9            0

Time taken: 258.281 seconds

 

 

HIVE output table sorted by Winning 4 numbers column(Win4nums):

WARNING: org.apache.hadoop.metrics.jvm.EventCounter is deprecated. Please use or

g.apache.hadoop.log.metrics.EventCounter in all the log4j.properties files.

Logging initialized using configuration in jar:file:/home/notroot/lab/software/h

ive-0.9.0-bin/lib/hive-common-0.9.0.jar!/hive-log4j.properties

Hive history file=/tmp/root/hive_job_log_root_201509031800_858663424.txt

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hadoop-2.6.0/share/

hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.cla

ss]

SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hive-0.9.0-bin/lib/

slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

OpenJDK Server VM warning: You have loaded library /home/notroot/lab/software/ha

doop-2.6.0/lib/native/libhadoop.so.1.0.0 which might have disabled stack guard.

The VM will try to fix the stack guard now.

It's highly recommended that you fix the library with 'execstack -c <libfile>',

or link it with '-z noexecstack'.

hive> use megamillionAnalysisHive;

OK

Time taken: 12.679 seconds

hive> #select Period,AnalysisType,Profit,WinPercent,Win4nums from megamilliondat

aextractionhive sort by AnalysisType,Period;

FAILED: Parse Error: line 1:0 character '#' not supported here

 

hive> select Period,AnalysisType,Profit,WinPercent,Win4nums from megamilliondata

extractionhive sort by Win4nums;

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>

15/09/03 18:00:31 INFO Configuration.deprecation: mapred.job.name is deprecated.

 Instead, use mapreduce.job.name

15/09/03 18:00:31 INFO Configuration.deprecation: mapred.system.dir is deprecate

d. Instead, use mapreduce.jobtracker.system.dir

15/09/03 18:00:31 INFO Configuration.deprecation: mapred.local.dir is deprecated

. Instead, use mapreduce.cluster.local.dir

15/09/03 18:00:31 WARN conf.HiveConf: hive-site.xml not found on CLASSPATH

WARNING: org.apache.hadoop.metrics.jvm.EventCounter is deprecated. Please use or

g.apache.hadoop.log.metrics.EventCounter in all the log4j.properties files.

Execution log at: /tmp/root/root_20150903180000_75324922-dce0-4e9f-826a-ed7de03d

0dd2.log

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hadoop-2.6.0/share/

hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.cla

ss]

SLF4J: Found binding in [jar:file:/home/notroot/lab/software/hive-0.9.0-bin/lib/

slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

OpenJDK Server VM warning: You have loaded library /home/notroot/lab/software/ha

doop-2.6.0/lib/native/libhadoop.so.1.0.0 which might have disabled stack guard.

The VM will try to fix the stack guard now.

It's highly recommended that you fix the library with 'execstack -c <libfile>',

or link it with '-z noexecstack'.

Job running in-process (local Hadoop)

Hadoop job information for null: number of mappers: 2; number of reducers: 1

2015-09-03 18:01:09,494 null map = 0%,  reduce = 0%

2015-09-03 18:01:57,548 null map = 50%,  reduce = 0%, Cumulative CPU 8.7 sec

2015-09-03 18:01:59,766 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:01,149 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:02,246 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:03,326 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:04,522 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:05,782 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:07,007 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:08,170 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:09,380 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:10,708 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:11,934 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:13,115 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:14,407 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:15,503 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:16,595 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:17,822 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:19,080 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:20,348 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:21,560 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:22,758 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:24,033 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:25,264 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:26,597 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:27,831 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:29,172 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:30,404 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:31,608 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:32,835 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:34,057 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:35,340 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:36,639 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:37,903 null map = 100%,  reduce = 0%, Cumulative CPU 9.6 sec

2015-09-03 18:02:39,108 null map = 100%,  reduce = 100%, Cumulative CPU 11.46 se

c

2015-09-03 18:02:40,294 null map = 100%,  reduce = 100%, Cumulative CPU 11.46 se

c

2015-09-03 18:02:41,360 null map = 100%,  reduce = 100%, Cumulative CPU 11.46 se

c

MapReduce Total cumulative CPU time: 11 seconds 460 msec

Ended Job = job_1441040443320_0210

Execution completed successfully

Mapred Local Task Succeeded . Convert the Join into MapJoin

OK

2014to2005         top6R         -53         0         0

2014to2005         top63R         -53         0         0

2014to2005         top63         -50         6         0

2014to2005         top62R         -53         0         0

2014to2005         top62         -53         0         0

2014to2005         top6         -53         0         0

2014to2005         top5numsNmegaR         -45         12         0

2014to2005         top5numsNmega3R         -51         4         0

2014to2005         top5numsNmega3         -53         0         0

2014to2005         top5numsNmega2R         -48         6         0

2014to2005         top5numsNmega2         -53         0         0

2014to2005         top5numsNmega         -53         0         0

2014to2005         top5NmegaR         -44         13         0

2014to2005         top5Nmega3R         -50         6         0

2014to2005         top5Nmega3         -50         6         0

2014to2005         top5Nmega2R         -49         6         0

2014to2005         top5Nmega2         -47         8         0

2014to2005         top5Nmega -41         10         0

2014to2005         individualtop5NmegaSL4   -53         0         0

2014to2005         individualtop5NmegaSL3   -53         0         0

2014to2005         individualtop5NmegaSL2   -53         0         0

2014to2005         individualtop5NmegaSL1   -47         8         0

2014to2005         individualtop5NmegaR       -46         11         0

2014to2005         individualtop5Nmega3R     -51         4         0

2014to2005         individualtop5Nmega3       -52         2         0

2014to2005         individualtop5Nmega2R     -48         6         0

2014to2005         individualtop5Nmega2       -36         15         0

2014to2005         individualtop5Nmega         -46         10         0

2014to2005         individualbottomall6R         -53         0         0

2014to2005         individualbottomall6         -53         0         0

2014to2005         bottomall6R -53         0         0

2014to2005         bottomall6    -53         0         0

2014to2005         all6medR       -53         0         0

2014to2005         all6med         -53         0         0

2014to2005         all6avgR       -53         0         0

2014to2005         all6avg         -53         0         0

2013to2005         top6R         -147         5         0

2013to2005         top63R         -157         0         0

2013to2005         top63         -144         5         0

2013to2005            top62R            -137            6            0

.

.

.

 

2005to2005      individualtop5NmegaR      -942      3      0

2005to2005      individualtop5Nmega3R      -932      2      0

2005to2005      individualtop5Nmega3      -936      4      0

2005to2005      individualtop5Nmega2R      -910      4      0

2005to2005      individualtop5Nmega2      -912      3      0

2005to2005      individualtop5Nmega      -937      2      0

2005to2005      individualbottomall6R      -858      3      0

2005to2005      individualbottomall6      -911      1      0

2005to2005      bottomall6R      -958      2      0

2005to2005      bottomall6      -939      3      0

2005to2005      all6medR      -889      3      0

2005to2005      all6avgR      -949      2      0

2005to2005      all6avg      -904      4      0

2005to2005      all6med      -164      4      1

2005to2005      top5Nmega3      -247      0      1

2007to2005      top5numsNmegaR      -11      2      1

2012to2005      individualtop5Nmega      480      5      1

2009to2005      all6med      173      2      1

2012to2005      individualbottomall6      459      0      1

2012to2005      individualtop5NmegaSL4      465      0      1

2008to2005      individualtop5NmegaSL4      84      1      1

2009to2005      top5numsNmega2      182      3      1

2011to2005      top5Nmega3      382      3      1

2010to2005      top5numsNmega      280      3      1

2008to2005            individualtop5Nmega          89            3            1

 Click here to go to Chapter 8:   www.leisdesignservices.com/sqoopimportmysql2hbase.htm

 Or click here to go to the Table of Content:  www.leisdesignservices.com/hadoopproofofconcept.htm