ICP 5 - PavankumarManchala/BigDataProgrammingICPs GitHub Wiki

Submitted by:

Pavankumar Manchala class-Id: 16

Part-1:

1. Start the mysql service.

2. List the databases for your convenience and create a database for your own and use that database.

3. Next, create table and insert some records as shown below.

Describing the table.

4. Import the table to hadoop using sqoop as shown below.

5. Importing to a target directory using sqoop as shown below.

6. Verifying the import directory.

7. Table employee created in mysql for exporting from hadoop to sql.

8. After export in mysql table employee.

Part-2:

1. Download the hql script and modify it according to convenience. We can see the .hql file in my sourcecode. Then execute the hql script as shown below.

2. Check the hive warehouse using the command shown below. https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/17.PNG?raw=true

3. Create a new table employee in mysql. https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/18.PNG?raw=true

4. Exporting table to mysql employee through sqoop. https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/19.PNG?raw=true

https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/20.PNG?raw=true

5. After export mysql table looks like this. https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/21.PNG?raw=true

Part-3:

1. Took the stocks data and created a table and loaded data into the table. https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/22.PNG?raw=true

2. Also create a table stocks in mysql and export the hive table to mysql using sqoop. https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/23.PNG?raw=true

https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/24.PNG?raw=true

https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/25.PNG?raw=true

3. To compute statistics. https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/26.PNG?raw=true

4. To compute wordcount. https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/27.PNG?raw=true

https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/28.PNG?raw=true

https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/29.PNG?raw=true

5. For identifying patterns. a) To identify the stocks where difference b/w open and close prices is less than $2. https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/30.PNG?raw=true

https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/31.PNG?raw=true

b) Top 10 volumes for ibm stocks. https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/32.PNG?raw=true

c) To identify stock with the maximum volume(val5) and show it's adjusted closing price(val6), closing price(val4). https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/33.PNG?raw=true

d) Using regular expression to identify a given input. https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/34.PNG?raw=true

Bonus:

1. Saving queries results into a hive table as shown below. https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/35.PNG?raw=true

https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/36.PNG?raw=true

---> Checking the table wordcount as shown below.

https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/37.PNG?raw=true

https://github.com/PavankumarManchala/BigDataProgrammingICPs/blob/master/Hadoop/ICP5/38.PNG?raw=true

ICP5 video explanation: https://drive.google.com/open?id=1WZdvlV94I5uomjXRFqpq20Nq76uy1NG3

All ICPs videos link: https://drive.google.com/open?id=1racqWkfI10T-CpLYEDYCvJRSRhhLGsWL