Learn to query Spark DataFrames with SQL or dot notation, two ways to do the same thing. The instructor shows how `select()`, `withColumnRenamed()`, `limit()`, and window functions work identically in both styles, then recommends picking one and sticking with it.
Chapters
Previously we learned how to use SQL tables. We now learn how to query DataFrames using either SQL queries or using dataframe dot notation. This allows us to compare and contrast these two notations. For example, suppose we have a dataframe containing three columns and we want to select only two columns. We could do this printed again. Here is how we obtained the result in the previous slide. See that the column trainid is a string given in quotes. We can also do the following using dot notation. This time the column is given as DF TrainID. We can also import this column function, which allows us to do the following. This time the column is given as an argument to this new operator. It may seem more verbose in this case. However, it is useful in other cases such as in the following to rename a column, we can use the withcolumnrenamed function, but we could also use the column operator like so Pro. Try not to use all three conventions at the same time without good reason. Most spark SQL queries can be done either dot notation or as SQL notation. Here's an example. Note that the limit operation is done at query time instead of at show time. We can get the same result using dot notation like so. Note how we use the column operator to select the train ID column and renamed it in place. Window functions can also be done in either SQL or dot notation. This query adds a number to each stop on a train line in a new column ID. Note how the ID column starts over for train ID324. Here's the same result using dot notation. There is typically a dot notation equivalent of every SQL clause including window functions. The row number SQL clause has an equivalent dot notation SQL function. The inside of an over clause is handled by a window object. The window object provides methods for it to be partitioned and ordered. Some people prefer the SQL version. Other people prefer the dot notation. A window spec is defined using the window class and then used subsequently as an argument to the over function in the window function query. Here is an example. Let's practice.
