You have marks of all the students of class and you want to find ranks of students using python.
A pipe separated file which contains roll number and marks of students : below are the sample values :-
Please download sample file marks
Step 1 : Loading the Raw data into hive table
As you can see we have our raw data into file which is pipe separated. I am keeping the raw file into class8 directory which is just created . Use below commands:-
- cd bdp/projects/
- mkdir class8
- cd class8
- cat marks.txt
Now I am putting this file into Hdfs location after creating stu_marks directory into Hdfs location.
Use below commands:-
- hadoop fs -mkdir bdps/stu_marks
- hadoop fs -put marks.txt bdps/stu_marks/
- hadoop fs -ls bdps/stu_marks/
Now we will load this file into hive table . Please refer this post if you have trouble loading file into hive table .You need to give pipe (|) as delimiter .
I have created one hive table named as “Studnt_mrks” on top of this data .
Which have two columns and both of them are of Int type.
Please use below commands one by one to create a hive table on top of it.
- CREATE SCHEMA IF NOT EXISTS bdp;
- CREATE EXTERNAL TABLE IF NOT EXISTS bdp.class8_marks
- (roll_no INT,
- ttl_marks INT)
- ROW FORMAT DELIMITED
- FIELDS TERMINATED BY '|'
- STORED AS TEXTFILE
- LOCATION 'hdfs://sandbox-hdp.hortonworks.com:8020/user/root/bdps/stu_marks';
As you can see above the Location which I gave here is the path where my hdfs file is present.
Step 2: – Loading hive table into Spark using python
First open pyspark shell by using below command:-
Once the CLI is opened .Use below commands to load the hive table:-
here you can see stu_marks is the data frame which contains the data of hive table: – You can see the data using show command :-
Step 3 : Assign Rank to the students
Now let’s come to the actual logic to find the rank of the students :-
Please use below command to import the required functions
- from pyspark.sql.functions import rank
- from pyspark.sql.window import Window
- from pyspark.sql.functions import col
In above command I am using rank function over marks . As we want to rank higher if one has score higher marks, So we are using desc .
Below command will give you the expected results ,In which rank of student is assigned against roll no.
You can save the result as per your requirements .
Here we have understood how the rank functions works and what is the simple use case of this function. We can assign rank partition wise as well ,For that you have to use partition by in over clause .
Rank can be used if you want to find the result of n’th rank holder .You can filter based on the required rank. If you are looking for the same code in scala instead of python .Please read this blog post.
Don’t forget to subscribe our blog.