Spark Dataframe Select

In this post, we will see how to fetch data from HIVE table into SPARK DataFrame and perform few SQL like “SELECT” operations on it. I have a table in HIVE database which has details of all the US Presidents (src: https://en.wikipedia.org/) .
If you don’t know how to create table in hive or load data into hive table kindly read this post:
Create Table in Hive
The data looks like :

0: jdbc:hive2://localhost:10000> select * from pres_orc as tbl;

Now we will create a DataFrame in spark and will load this data into it. For future examples, we will use this DataFrame when required. Now let’s connect to spark. I am using HDP 2.5. So I will open “spark-shell” and few points to note here:
spark version 1.6
Spark context sc
SQL context sqlContext
Now let’s run the statement to create dataframe:

 scala> val df_pres = sqlContext.sql(" SELECT * from testdb.pres_orc")
17/09/10 08:32:58 INFO ParseDriver: Parsing command: SELECT * from testdb.pres_orc
17/09/10 08:32:58 INFO ParseDriver: Parse Completed
df_pres: org.apache.spark.sql.DataFrame = [pres_id: tinyint, pres_name: string, pres_dob: date, pres_bp: string, pres_bs: string, pres_in: date, pres_out: date]

Now I have a DataFrame with name df_pres which has the HIVE Table data in it. I can perform almost all the SQL operations on it in SPARK-SQL. SELECT primarily has two options:

1. You can either SELECT all columns by specifying “*” in the SQL query
2. You can mention specific columns in the SQL query to pick only required columns

Now how do we do it in Spark ?

1) Show all columns from DataFrame

scala> df_pres.show()
+-------+--------------------+----------+-------------------+--------------------+----------+----------+
|pres_id|           pres_name|  pres_dob|            pres_bp|             pres_bs|   pres_in|  pres_out|
+-------+--------------------+----------+-------------------+--------------------+----------+----------+
|      1|   George Washington|1732-02-22|Westmoreland County|            Virginia|1789-04-30|1797-03-04|
|      2|          John Adams|1735-10-30|          Braintree|       Massachusetts|1797-03-04|1801-03-04|
|      3|    Thomas Jefferson|1743-04-13|           Shadwell|            Virginia|1801-03-04|1809-03-04|
|      4|       James Madison|1751-03-16|        Port Conway|            Virginia|1809-03-04|1817-03-04|
|      5|        James Monroe|1758-04-28|        Monroe Hall|            Virginia|1817-03-04|1825-03-04|
|      6|   John Quincy Adams|1767-07-11|          Braintree|       Massachusetts|1825-03-04|1829-03-04|
|      7|      Andrew Jackson|1767-03-15|     Waxhaws Region|South/North Carolina|1829-03-04|1837-03-04|
|      8|    Martin Van Buren|1782-12-05|         Kinderhook|            New York|1837-03-04|1841-03-04|
|      9|William Henry Har...|1773-02-09|Charles City County|            Virginia|1841-03-04|1841-04-04|
|     10|          John Tyler|1790-03-29|Charles City County|            Virginia|1841-04-04|1845-03-04|
|     11|       James K. Polk|1795-11-02|          Pineville|      North Carolina|1845-03-04|1849-03-04|
|     12|      Zachary Taylor|1784-11-24|      Barboursville|            Virginia|1849-03-04|1850-07-09|
|     13|    Millard Fillmore|1800-01-07|         Summerhill|            New York|1850-07-09|1853-03-04|
|     14|     Franklin Pierce|1804-11-23|       Hillsborough|       New Hampshire|1853-03-04|1857-03-04|
|     15|      James Buchanan|1791-04-23|           Cove Gap|        Pennsylvania|1857-03-04|1861-03-04|
|     16|     Abraham Lincoln|1809-02-12|     Sinking spring|            Kentucky|1861-03-04|1865-04-15|
|     17|      Andrew Johnson|1808-12-29|            Raleigh|      North Carolina|1865-04-15|1869-03-04|
|     18|    Ulysses S. Grant|1822-04-27|     Point Pleasant|                Ohio|1869-03-04|1877-03-04|
|     19| Rutherford B. Hayes|1822-10-04|           Delaware|                Ohio|1877-03-04|1881-03-04|
|     20|   James A. Garfield|1831-11-19|     Moreland Hills|                Ohio|1881-03-04|1881-09-19|
+-------+--------------------+----------+-------------------+--------------------+----------+----------+
only showing top 20 rows

If you wish to show more number of rows, just enter the number in the show function.

scala> df_pres.show(45)

The above command will show all 45 rows from the data frame.

2) Show specific columns from DataFrame

scala> df_pres.select($"pres_id",$"pres_dob",$"pres_bs").show()
+-------+----------+--------------------+
|pres_id|  pres_dob|             pres_bs|
+-------+----------+--------------------+
|      1|1732-02-22|            Virginia|
|      2|1735-10-30|       Massachusetts|
|      3|1743-04-13|            Virginia|
|      4|1751-03-16|            Virginia|
|      5|1758-04-28|            Virginia|
|      6|1767-07-11|       Massachusetts|
|      7|1767-03-15|South/North Carolina|
|      8|1782-12-05|            New York|
|      9|1773-02-09|            Virginia|
|     10|1790-03-29|            Virginia|
|     11|1795-11-02|      North Carolina|
|     12|1784-11-24|            Virginia|
|     13|1800-01-07|            New York|
|     14|1804-11-23|       New Hampshire|
|     15|1791-04-23|        Pennsylvania|
|     16|1809-02-12|            Kentucky|
|     17|1808-12-29|      North Carolina|
|     18|1822-04-27|                Ohio|
|     19|1822-10-04|                Ohio|
|     20|1831-11-19|                Ohio|
+-------+----------+--------------------+
only showing top 20 rows

In the next post, we will see how to mention ALIAS names for COLUMNS and TABLES while using DATAFRAMES in SPARK.