Use sqoop to import mysql Data to hadoop

Use sqoop to import mysql Data to hadoop

The installation and configuration of hadoop will not be discussed here.

Sqoop installation is also very simple. After sqoop is installed, you can test whether it can be connected to mysql (Note: The jar package of mysql should be placed under SQOOP_HOME/lib): sqoop list-databases -- connect jdbc: mysql: // 192.168.1.109: 3306/-- username root -- password 19891231 the result is as follows: sqoop can be used normally. Next, import data from mysql to hadoop. I have prepared an ID card for 3 million data entries. Data Table: Start hive (use command line: hive to start it) and then use sqoop to import data to hive: sqoop import -- connect jdbc: mysql: // 192.168.1.109: 3306/hadoop -- username root -- password 19891231 -- table test_sfz -- hive-import sqoop starts the job to complete the import. The import was completed at 2 minutes 20 seconds, which is good. In hive, we can see the imported data table: Let's test the SQL statement: select * from test_sfz where id <10; we can see that, it took almost 25 seconds for hive to complete this task, which is indeed quite slow (almost no time-consuming in mysql), but it should be considered that hive created a job to run in hadoop, of course there are many times. Next, we will test the complex query of the data: the sub-configuration of our machine is as follows: hadoop is a pseudo-distributed running on the virtual machine, and the virtual machine OS is ubuntu12.04 64-bit. The configuration is as follows: TEST 1 Average age calculation TEST data: 300.8 W 1. calculate the average age of Guangdong mysql: select (sum (year (NOW ()-SUBSTRING (borth,)/count (*)) as ageAvge from test_sfz where address like 'guangdong % '; Time: 0.877 s hive: select (sum (year ('2017-10-01')-SUBSTRING (borth, 2014 )) /count (*) as ageAvge from test_sfz where address like 'guangdong % '; Time: 25.012 s 2. rank the average age of each city from high to low Mysql in sequence: select address, (sum (year (NOW ()-SUBSTRING (borth,)/count (*)) as ageAvge from test_sfz group by address order by ageAvge desc; Time: 2.949 s hive: select address, (sum (year ('2017-10-01 ')-SUBSTRING (borth, 2014 )) /count (*) as ageAvge from test_sfz group by address order by ageAvge desc; Time: 51.29 s, we can see that hive is slower than mysql in terms of time consumption. TEST 2 TEST data: 1200 W mysql engine: MyISAM (to speed up the query) imported to hive: 1. calculate the average age of Guangdong mysql: select (sum (year (NOW ()-SUBSTRING (borth,)/count (*)) as ageAvge from test_sfz2 where address like 'guangdong % '; Time: 5.642 s hive: select (sum (year ('2017-10-01')-SUBSTRING (borth, 2014 )) /count (*) as ageAvge from test_sfz2 where address like 'guangdong % '; duration: 168.259 s 2. sort the average age of each city in descending order. mysql: select address, (sum (year (NOW ()-SUBSTRING (Borth, 11.964)/count (*) as ageAvge from test_sfz2 group by address order by ageAvge desc; Time: s hive: select address, (sum (year ('1970-10-01 ')-SUBSTRING (borth, 2014)/count (*) as ageAvge from test_sfz2 group by address order by ageAvge desc; Time: 311.714 sTEST 3 test data: 2000 W mysql engine: MyISAM (to speed up the query) imported to hive: (this time is very short! It may be because my host is doing other resource-consuming work during import in TEST2 ..) 1. calculate the average age of Guangdong mysql: select (sum (year (NOW ()-SUBSTRING (borth,)/count (*)) as ageAvge from test_sfz2 where address like 'guangdong % '; Time: 6.605 s hive: select (sum (year ('2017-10-01')-SUBSTRING (borth, 2014 )) /count (*) as ageAvge from test_sfz2 where address like 'guangdong % '; duration: 188.206 s 2. sort the average age of each city in descending order. mysql: select address, (sum (year (NOW ()-SUBSTRING (borth )) /count (*) as ageAvge from test_sfz2 group by address order by ageAvge desc; Time: 19.926 s hive: select address, (sum (year ('2017-10-01 ') -SUBSTRING (borth, 411.816)/count (*) as ageAvge from test_sfz2 group by address order by ageAvge desc; Time: s