Spark SQL

Spark Multi-Dimension aggregation methods like cube and rollup allow the creation of aggregates across a various combination of columns. So in a sense, they are an extension of groupBy with features of union operators rolled in. They can help you create pretty great datasets very quickly and easily and save you from writing an awful lot of complicated code to achieve the same result. Let’s unpick both these methods one by one and look at examples. Rollup Let’s assume that you have a dataset which has product type, year, quarter and quantity columns. You have been asked to create a single dataset in which you have the following combinations of aggregations – total quantity by product type, year and quarter by product type and year by product type Finally the total quantity for all product types, all years & quarters. One way would be to create three datasets using groupBy … Read more

Before we dive headfirst into Window functions it would be a good idea to review the groupby aggregate functions. An important thing to note, when groupBy functions are applied they reduce the number of rows returned. However, when Window functions are applied they do not reduce the rows in the result set. Window describes the set of rows on which the window function operates. It returns a value which is shown in all the rows of the window. One more thing, Window Functions also help us compare the current rows with other rows. Simple Aggregations Below is an example showing two operations to give a contrast in the results. avg function when a groupBy is applied avg function when a window is applied Let’s look at the Step – 4 & 5 which are the most important in this example. Step 1,2,3 – Creating a spark context, Reading a data file … Read more

Pivoting data is the process of re-arranging the data from rows into columns based on one or more columns. It is also sometimes called re-shaping data. Pivot in Spark is quite simple and can be accomplished via the pivot method. More importantly, before you pivot data make sure it has the right columns which are required. If there are columns you donot need in the pivot make sure they are not there in the dataset. To enable pivoting in spark requires the following. groupBy – Is passed columns which are used for aggregating and are not pivoted. pivot – Column(s) whose values are converted into columns agg – Aggregation method which is applied during pivot Pivoting a Single Column Let’s take a look at an example. See Below Let’s analyse the above example Step 1 – Creates a spark session Step 2 – Creates a custom schema Step 3 – … Read more

In this entry, we look at how we can aggregate data in Spark datasets to create new datasets. If you have seen the previous post on selecting and filtering data you would have realised that these API are quite similar to SQL operations. That similarity continues in this entry as well. Let’s start with the count API. There are a couple of ways to apply this API. See Below. Step 5 – Returns the count of rows in a dataset.The relevant log is below Step 6 – Use the groupBy & agg method to count the orders by order status.The relevant log is below Multiple aggregations can be performed via a single groupBy & agg method call. In the next example we see sum, avg, min and max being performed on the order price. The relevant portion of the log is show below Hope you have found this post interesting. … Read more

Spark DataSet- Data & Time APIs allow extensive data transformations. The API can be roughly divided into the following categories. Data Extraction from Dates – Extract years, quarters, months, weeks,  days, the day of the week, week of the year Epoch & Unix Time – Conversion to & from epoch Date Calculations – Date difference, add days, next day Current date and timestamps Different date formats All of these categories are discussed in this post. Data Extraction from Dates There are various API available to extract data from a given date. They are all listed below and can be applied directly on a date column as we will see. year – Extracts the calendar year from a given date quarter – Extracts the calendar quarter from a given date month – Extracts the calendar month from a given date weekOfYear – Extract the week number from a given date dayOfMonth – … Read more

Introduction DataSet operations are used for transforming data and are quite similar to SQL.  Selecting Data Filtering Data – Applying Conditions Ordering data Aggregating data – count, sum, max, min, avg Joins – Right Outer, Left Outer, Full Outer Set operations – union, minus, intersect In all the following examples, I have used the TPC-H datasets which are an industry standard for various benchmarks. The data generators are open source and can be download from GitHub. The structure of the data is available on tpc-h website or you can directly click here and look at page 13. I have provided samples of the data files at the end of the posts. DataSet – Read options Before we get into the details of actual dataframe API let’s understand some of the configurations to read the data from a simple CSV file. There are various options available to read files. Some of them … Read more