Section 2, ELT with Apache Spark

Data stored in files like CSV, Parquet, or JSON.

Comma-separated values file. A common data format.

Columnar file format, optimized for Spark's processing.

JavaScript Object Notation file, data in key-value pairs.

Data organized in a table within Hive. Used by Spark.

Named logical schema (a stored query).

View only available during a session.

Spark SQL syntax to create a VIEW.

Changing column names in a DataFrame to more readable names (e.g., from a complicated nested field to "City" or "Zip")

Extracting data from nested columns in a DataFrame to create new, individual columns.

Array functions in Spark speed up and simplify ETL processes (extract, transform, load) involving array data structures

Array functions efficiently manage complex data arrangements like nested arrays, frequently used with JSON-based data.

Array functions make operations like filtering, transforming, aggregating or flattening data much easier without complex extra code.

Array functions leverage Spark's distributed processing for fast handling of large datasets.

Array functions enable transformations such as array concatenation, intersection, and element extraction on data within arrays without extra complex functions.

Array functions like explode, array_contains, improve clarity and maintainability of code by providing clear paths to handle array operations.

Returns only rows with matching values in both DataFrames.

Returns all rows from the left DataFrame, and matching rows from the right. Non-matching right side values are NULL.

Returns all rows from the right DataFrame, and matching rows from the left. Non-matching left side values are NULL.

Returns all rows from both DataFrames. Missing values are filled with NULL.

The result of joining two datasets (DataFrames) in Apache Spark based on the join type (inner, left, right, full outer).

A method to reshape a DataFrame from a long format to a wide format by aggregating data based on a specified column.

A data structure with a single row for each data point, where values for different categories are represented in separate columns.

A data structure with one column for each category, in this case each quarter, and rows grouped by common identifiers.

A user-defined function that can be used in Spark SQL queries for custom computation.

A process that extracts, transforms, and loads data from various sources to a data warehouse or a data lake.

A Spark DataFrame method designed to transform data from long to wide format. Similar to a SQL pivot clause.

A data structure to represent data in tabular format within Apache Spark.

A method that groups rows with the same values in specified columns for subsequent aggregation operations.

Spark functions are defined in three main locations: built-in functions provided by Spark, user-defined functions (UDFs) created by the user, and custom functions written directly within your application code.

A UDF is a custom function written by the user and registered within a Spark session. This allows the function to be used in Spark SQL queries or DataFrame operations.

UDFs provide a way to apply custom logic and transformations to data that are not natively supported by Spark SQL. This enhances the flexibility and power of Spark.

UDFs provide several benefits: they allow you to apply custom logic, they can be reused across multiple queries and DataFrames, and they offer flexibility to enhance Spark SQL capabilities.

Spark's built-in functions are accessed through the pyspark.sql.functions module in PySpark.

Use a UDF when you need to apply custom logic or transformations to data that are not natively supported by Spark SQL's built-in functions.

To use a UDF in Spark, you must first register it using the spark.udf.register method.

Custom functions are functions defined directly in your script or application. They are used for data manipulations within transformations or actions on DataFrames and RDDs.

The entry point to Spark functionality. It manages resources and allows interacting with Spark's core services.

A custom function written in Python or Scala that can be used within Spark SQL queries.

Making a UDF available for use in Spark SQL by associating it with a name.

Constructing a Spark DataFrame from data. This can be done from various sources like files or lists.

A temporary, named view of a DataFrame, allowing you to access data through SQL queries.

Calling the UDF within a Spark SQL query to apply its logic to the data.

Spark's SQL engine, allowing you to query and manipulate DataFrame data using SQL syntax.

A distributed data structure in Spark, similar to a table in SQL, holding organized data.

A CTE is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It's defined within a query, making SQL code more organized and readable.

You define a CTE within a SQL query using the WITH clause. It follows this pattern: WITH cte_name AS (SELECT ... FROM ...).

CTEs enhance the organization and readability of your ETL workflows by breaking down complex operations into smaller, more manageable units.

To find tables that are not Delta Lake tables, you can use a query to list all tables in a Spark database and then filter out those that contain '.delta' in their name.

SHOW TABLES is a Spark SQL command used to list all existing tables within a specified database.

A table format defines how data is structured and stored within a table. Examples include CSV, Parquet, JSON, and Delta Lake.

You can check the format of a table to determine if it's a Delta Lake table. Look for the '.delta' extension in the table's name.

Knowing a table's format is crucial for choosing the appropriate data processing techniques and tools. For example, Delta Lake tables offer features like ACID properties and time travel.

Count the number of rows in a Spark DataFrame that meet a specific condition.

Count the number of rows in a Spark DataFrame where a specific column has a NULL value.

The Python API for interacting with Apache Spark.

A PySpark SQL function that allows you to create conditional expressions within a DataFrame.

A PySpark SQL function used to count rows in a DataFrame, often combined with other functions to count specific conditions.

A PySpark function that checks if a column value is NULL.

Remove duplicate rows from a Spark DataFrame based on specified columns.

The Spark DataFrame method used to eliminate duplicate rows.

An open-source storage layer for data lakes that provides ACID properties and time travel.

Write the processed DataFrame to a new Delta Lake table or another format.

Ensuring that a primary key is unique across all rows in a Delta Lake table to maintain data integrity.

Set up a Spark session for interacting with Spark functionalities.

Creating a Spark DataFrame to store and manipulate data in a table-like structure. Data can come from various sources, like files or lists.

Changing the data type of a column in a DataFrame. This is crucial for ensuring data is in the correct format for analysis and calculations.

A data type representing a specific point in time. It allows for accurate time-based operations and analysis.

A Spark DataFrame function that lets you add or modify columns in a DataFrame. You specify the new column name and how to compute its values.

A PySpark function used to change the data type of a column in a DataFrame. This can convert strings to numbers, dates, or timestamps.

A new column created by casting a string date column to a timestamp data type.

The process of pulling data from fields within a complex data structure like a nested JSON object or a column containing nested data.

A way to access nested fields in Spark data using a period (.) to navigate layers within a structure.

A small, example DataFrame used to demonstrate a concept or technique. It doesn't contain real data.

A Spark DataFrame method used to change the name of a column.

A method used to display the contents of a Spark DataFrame.

Transform JSON strings in a DataFrame into structured data using the from_json function and a predefined schema.

Creating a StructType that defines the structure of the JSON data, specifying field names, data types, and nesting.

Transforming a nested struct into separate columns for easier access to individual fields.

Creating a SparkSession object, the entry point for interacting with Spark functionality.

A Spark function that parses JSON strings into structured data based on a predefined schema.

A Spark data type used to represent structured data like a JSON object, with fields and their corresponding data types.

A PySpark function used to access a column in a DataFrame by its name.

Data sources that store information in files, like CSV, Parquet, or JSON. Spark reads data directly from these files.

Data organized into tables like Hive tables or databases accessed through JDBC. Spark connects to these tables.

A named query that simplifies accessing data and reusing complex operations.

A view that exists only during the current Spark session. Useful for temporary analysis.

To make your Spark code more readable and maintainable, by breaking down complex operations into smaller, reusable parts.

Use dot syntax to access fields within nested data structures, like JSON objects, using a period (.) to navigate levels.

A Spark function to transform JSON strings in a DataFrame into structured data using a predefined schema.

Read data from a database using JDBC and create a temporary Spark table for further processing.

Read data from a CSV file and create a temporary Spark table for further processing.

A named table in Spark that only exists during the current session, making it easy to work with data.

A Spark function to create or update a temporary view in Spark.

Removing duplicate rows from a DataFrame based on specific columns.

1. Initialize Spark Session. 2. Create or load the DataFrame. 3. Deduplicate based on specific columns. 4. Save the deduplicated DataFrame.

Use the spark.createDataFrame function to create a DataFrame from a list of data.

Confirm that the new Delta Lake table contains only unique rows.

A table that uses the Delta Lake format for storage, offering features like ACID properties and time travel.

Converting a column containing timestamp strings to a proper timestamp data type.

Using functions to extract specific parts (year, month, hour, etc.) from a timestamp and adding them as new columns.

A Spark function that extracts specific patterns from a string column using regular expressions.

Identifying and extracting a specific pattern from a string column using the regexp_extract function.

Changing or extracting data from one format to another, often using Spark functions like regexp_extract or casting.

Extracting, Transforming, and Loading data between different systems using Spark functions.

A blueprint that defines the structure of data. It tells Spark how to interpret each piece of information.

A specific schema designed to interpret structured JSON data. It defines the field names and datatypes within a JSON object.

The process of converting a JSON string into a structured data format that Spark can easily understand.