Databricks: Common Table Expressions

Posted on by
Reply

Common Table Expressions (CTE) are supported in Spark SQL. A CTE provides a temporary result set which you can then use in a SELECT statement. These are different from temporary views in that they cannot be used beyond the scope of a single query. In this case, we will use the CTE to get a closer look at the nested data without writing a new table or view. CTEs use the WITH clause to start defining the expression.

Notice that after we explode the source column, we can access individual properties in the value field by using dot notation with the property name.

Databricks: Create temporary view using ROUND and AVG and GROUP BY month

Posted on by
Reply

We will create a temporary view that we can easily refer to the data we want to include in our visualization. For this data, we can investigate whether there are any patterns in the ratings when grouped by month. To do that, we use the ROUND() and AVG() functions to calculate the average rating and limit it to 3 decimal places. Then, extract the month from the timeRecorded column after casting it as a timestamp. The AVG() is calculated over the course of a month, as specified in the GROUP BY clause.

CREATE

OR REPLACE TEMPORARY VIEW ratingsByMonth AS

SELECT

ROUND(AVG(rating), 3) AS avgRating,

month(CAST(timeRecorded as timestamp)) AS month

FROM

movieRatings

GROUP BY

month;

Databricks: Basic SQL queries using Apache Spark

Posted on by
Reply

park SQL is a component of Apache Spark that enables querying structured data using SQL syntax, either through SQL queries or DataFrame APIs. Here’s a brief overview of some basic queries you can perform using Spark SQL:

  1. Selecting Data:To select specific columns from a DataFrame:SELECT col1, col2 FROM table_name;
  2. Filtering Data:To filter rows based on certain conditions:SELECT * FROM table_name WHERE condition;
  3. Aggregating Data:To perform aggregation operations like sum, count, average, etc.:SELECT COUNT(*), AVG(salary) FROM employee_table;
  4. Grouping Data:To group data based on certain columns:SELECT department, AVG(salary) FROM employee_table GROUP BY department;
  5. Joining Data:To join two or more tables based on a common key:SELECT * FROM table1 JOIN table2 ON table1.key = table2.key;
  6. Sorting Data:To sort data based on one or more columns:SELECT * FROM table_name ORDER BY column_name ASC/DESC;
  7. Subqueries:To use a query within another query:SELECT * FROM table1 WHERE col1 IN (SELECT col2 FROM table2);
  8. Window Functions:To perform calculations across a set of rows:ELECT department, employee_id, salary, AVG(salary) OVER (PARTITION BY department) AS avg_salary_department FROM employee_table;
  9. Common Table Expressions (CTEs):To define temporary named result sets for use in a query:WITH cte AS ( SELECT department, AVG(salary) AS avg_salary FROM employee_table GROUP BY department ) SELECT * FROM cte WHERE avg_salary > 50000;
  10. Union:To combine the results of two or more SELECT statements:SELECT col1 FROM table1 UNION SELECT col2 FROM table2;

These are some of the basic SQL queries you can perform using Spark SQL. Keep in mind that Spark SQL supports a wide range of SQL functionalities, and you can use it to handle complex data manipulation and analysis tasks.