Azure Databricks – Spark SQL

Spark SQL is a powerful tool within Azure Databricks for querying and analyzing large-scale datasets using SQL-like syntax. It integrates seamlessly with the Spark ecosystem, enabling efficient data processing and analysis for various scenarios.

Table of Contents

Spark SQL in Azure Databricks

1. Accessing Azure Databricks:

Open the Azure Databricks workspace in your web browser.
Create a new cluster with appropriate configurations for your workload. This includes specifying the number of nodes, the amount of RAM and CPU per node, and the Spark version.
Once the cluster is created, you can access a notebook or create a new one to start working with Spark SQL.

2. Connecting to Data:

Spark SQL allows reading data from various sources, including:
- Azure Blob Storage: Use the spark.read.format("csv").option("header", True).load("abfss://container@storageaccount.blob.core.windows.net/data.csv") command to read a CSV file.
- Azure Data Lake Store: Similarly, use spark.read.parquet("adl://datalake.azuredatabricks.net/data/data.parquet") to read a Parquet file.
- Local Files: You can also read data from local files using paths accessible within the cluster.
Remember to specify the data format and any relevant options like header presence when reading data.

Example:

# Read a CSV file from Azure Blob Storage
df = spark.read.format("csv").option("header", True).load("abfss://container@storageaccount.blob.core.windows.net/data.csv")

# Read a Parquet file from Azure Data Lake Store
df2 = spark.read.parquet("adl://datalake.azuredatabricks.net/data/data.parquet")

3. Exploring and Manipulating Data:

Use SQL-like queries to filter, sort, aggregate, and join data within DataFrames.
Spark SQL provides various built-in functions for data manipulation and analysis, such as avg, max, count, and filter.

Examples:

# Filter rows where age is greater than 25 and sort by city
df.filter("age > 25").orderBy("city").show()

# Calculate average and maximum salary per department
df.groupBy("department").agg(avg("salary"), max("salary")).show()

4. Joining DataFrames:

Combine data from multiple DataFrames based on specific join conditions.
Spark SQL supports various join types like inner, outer, left, and right.

Example:

# Inner join two DataFrames based on the id column
df1.join(df2, df1.id == df2.id, "inner").show()

5. Working with Views:

Create temporary or permanent views to store frequently used queries for later reuse.
This simplifies data access and avoids repeatedly writing complex queries.

Example:

# Create a temporary view of filtered data
spark.sql("CREATE TEMP VIEW customers_filtered AS SELECT * FROM customers WHERE active = true")

# Use the view in subsequent queries
spark.sql("SELECT * FROM customers_filtered ORDER BY name").show()

6. Saving Data:

After analysis, save the DataFrame data back to various storage locations in desired formats.
Spark SQL supports various data sources like CSV, Parquet, and JSON for writing data.

Example:

# Save DataFrame to a Parquet file in Azure Data Lake Store
df.write.parquet("adl://datalake.azuredatabricks.net/data/output.parquet")

7. Advanced Techniques:

Explore advanced Spark SQL features like:
- Window functions: Perform calculations based on rows within a specific window.
- Subqueries: Nest queries within other queries for complex data analysis.
- User-defined functions: Extend Spark SQL capabilities with custom functions.
Integrate Spark SQL with other Spark libraries like:
- MLlib: For machine learning and data science tasks.
- GraphX: For graph processing and analysis.
Refer to the official Spark SQL documentation for comprehensive information and tutorials on advanced features and integrations.

Creating SQL Databases and Tables in Spark Azure Databricks

Azure Databricks provides a powerful platform for working with data using SQL and Spark. This includes creating and managing databases and tables, both managed and external.

1. Managed Tables:

Managed tables store data in a managed location controlled by Databricks.

Create Database:

CREATE DATABASE IF NOT EXISTS my_database;

Definition: Managed tables store their data and metadata within the Databricks workspace.
Benefits: Automatic data lifecycle management, optimized performance, ACID transactions (Delta tables).
Creating:

CREATE TABLE my_table (
  id INT,
  name STRING,
  age INT
);

2. External Tables:

Definition: External tables reference data stored outside the Databricks workspace, typically in cloud storage like Azure Blob Storage or ADLS.
Benefits: Flexibility to use existing data, data remains accessible even if the Databricks cluster is terminated.
Creating:

CREATE EXTERNAL TABLE my_external_table (
  id INT,
  name STRING,
  age INT
) LOCATION 'abfss://container@storageaccount.blob.core.windows.net/data.csv';

3. Viewing Metadata:

Databricks provides several ways to view table metadata:
- SQL DESCRIBE TABLE:

DESCRIBE TABLE my_table;

Examples:

Create a managed table:

CREATE TABLE customer_data (
  id INT,
  name STRING,
  city STRING
)
USING DELTA;

INSERT INTO customer_data VALUES (1, 'John Doe', 'Seattle');
INSERT INTO customer_data VALUES (2, 'Jane Doe', 'New York');

Create an external table:

CREATE EXTERNAL TABLE sales_data (
  order_id INT,
  product_id STRING,
  amount DOUBLE
)
LOCATION 'abfss://container@storageaccount.blob.core.windows.net/sales.csv'
FORMAT CSV
HEADER TRUE;

View the metadata of a table: