Transform Data

with Spark

Module 02

©2023 Databricks Inc. — All rights reserved 1

Module Objectives
Transform Data with Spark

1. Extract data from a variety of file formats and data sources using Spark
2. Apply a number of common transformations to clean data using Spark
3. Reshape and manipulate complex data using advanced built-in functions
in Spark
4. Leverage UDFs for reusable code and apply best practices for
performance in Spark

©2023 Databricks Inc. — All rights reserved 2

Module Agenda
Transform Data with Spark
Data Objects in the Lakehouse
DE 2.1 - Querying Files Directly
DE 2.2 - Options for External Sources
DE 2.3L - Extract Data Lab
DE 2.4 - Cleaning Data
DE 2.5 - Complex Transformations
DE 2.6L - Reshape Data Lab
DE 2.7A – SQL UDFs and Control Flow
DE 2.7B - Python UDFs
©2023 Databricks Inc. — All rights reserved 3
Data Objects in the
Lakehouse

©2023 Databricks Inc. — All rights reserved 4

Data objects in the Lakehouse

Metastore

Catalog

Schema
(Database)

Table View Function

5
©2023 Databricks Inc. — All rights reserved
Data objects in the Lakehouse

Metastore

Catalog

Schema
(Database)

Table View Function

6
©2023 Databricks Inc. — All rights reserved
Data objects in the Lakehouse

Metastore

Catalog

Schema
(Database)
(Database)

Table View Function

7
©2023 Databricks Inc. — All rights reserved
Data objects in the Lakehouse

Metastore

Catalog

Schema
(Database)

Managed table
Table View Function
External table

8
©2023 Databricks Inc. — All rights reserved
Managed Tables
Metastore

Catalog

Schema

Managed table

Metastore storage

9
©2023 Databricks Inc. — All rights reserved
External Tables
Metastore

Catalog Storage credential

Schema External location

Managed table External table

Metastore storage External storage

10
©2023 Databricks Inc. — All rights reserved
Data objects in the Lakehouse

Metastore

Catalog

Schema
(Database)

Table View Function

11
©2023 Databricks Inc. — All rights reserved
Data objects in the Lakehouse

Metastore

Catalog

Schema
(Database)

Table View Function

Global Temporary
Temporary View
View 12
©2023 Databricks Inc. — All rights reserved
Data objects in the Lakehouse

Metastore

Catalog

Schema
(Database)

Table View Function

Function

13
©2023 Databricks Inc. — All rights reserved
Extracting Data

©2023 Databricks Inc. — All rights reserved 14

Query files directly
SELECT * FROM file_format.`path/to/file`

Files can be queried directly using SQL

• SELECT * FROM json.`path/to/files/`
• SELECT * FROM text.`path/to/files/`

Process based on specified file format

• json pulls schema from underlying data
• binaryFile and text file formats have fixed data schemas
• text → string value column (row for each line)
• binaryFile → path, modificationTime, length, content columns (row for each file)

©2023 Databricks Inc. — All rights reserved 15

Configure external tables with read options
CREATE TABLE USING data_source OPTIONS (...)

Many data sources require schema declaration and other options to

correctly read data
• CSV options for delimiter, header, etc
• JDBC options for url, user, password, etc
• Note: using the JDBC driver pulls RDBMS tables dynamically for Spark processing

©2023 Databricks Inc. — All rights reserved 16

DE 2.1: Querying Files
Directly
Use Spark SQL to directly query JSON data files
Leverage text and binaryFile methods to review raw file contents

©2023 Databricks Inc. — All rights reserved 17

DE 2.2: Providing Options
for External Sources
Use Spark SQL to configure options for extracting data from external sources
Create tables against external data sources for various file formats
Describe behavior when querying tables defined against external RDBMS sources

©2023 Databricks Inc. — All rights reserved 18

DE 2.3L: Extract Data Lab

©2023 Databricks Inc. — All rights reserved 19

DE 2.4: Cleaning Data
Summarize datasets and describe NULL behaviors
Retrieve and removing Duplicates
Validate datasets for expected counts, missing values, and duplicate records
Apply date_format and regexp_extract to clean and transform data

©2023 Databricks Inc. — All rights reserved 20

Complex
Transformations

©2023 Databricks Inc. — All rights reserved 21

Interact with Nested Data
Use built-in syntax to traverse nested data with Spark SQL

Use “:” (colon) syntax in queries to access subfields in JSON strings

SELECT value:device, value:geo ...

Use “.” (dot) syntax in queries to access subfields in STRUCT types

SELECT value.device, value.geo ...

©2023 Databricks Inc. — All rights reserved 22

Complex Types
Nested data types storing multiple values
• Array: arbitrary number of elements of same data type
• Map: set of key-value pairs
• Struct: ordered (fixed) collection of column(s) and any data type

Example table with complex types

CREATE TABLE employees (name STRING, salary FLOAT,

subordinates ARRAY<STRING>,
deductions MAP<STRING, FLOAT>,
address STRUCT<street:STRING,city:STRING,state:STRING, zip:INT>)

©2023 Databricks Inc. — All rights reserved 23

DE 2.5: Complex
Transformations
Use : and . syntax to traverse nested data in strings and structs
Use .* syntax to flatten and query struct types
Parse JSON string fields
Flatten/unpack arrays and structs

©2023 Databricks Inc. — All rights reserved 24

explode lab explode outputs the elements of an array field into a separate row
for each element

SELECT
user_id, event_timestamp, event_name,
explode(items) AS item
FROM events

1
2
3

Each item in the items array above is exploded into its own row, resulting in the 3 rows below

1
2
3

©2023 Databricks Inc. — All rights reserved 25

flatten lab
collect_set returns an array of unique values from a field for each group of rows
flatten returns an array that flattens multiple arrays into one

SELECT user_id,
collect_set(event_name) AS event_history,
array_distinct(flatten(collect_set(items.item_id))) AS cart_history
FROM events
GROUP BY user_id

©2023 Databricks Inc. — All rights reserved 26

Collection example
collect_set returns an array with duplicate elements eliminated
collect_list returns an array with duplicate elements intact

df df.agg(collect_set(‘age’)) df.agg(collect_list(‘age’))

©2023 Databricks Inc. — All rights reserved 27

Parse JSON strings into structs
Create the schema to parse the JSON strings by providing an example JSON string from a row
that has no nulls

from_json uses JSON schema returned by schema_of_json to convert a column of JSON strings into structs

This highlighted JSON string is taken from the value field of a single row of data

Returns STRUCT column containing ARRAY of nested STRUCT

©2023 Databricks Inc. — All rights reserved 28

DE 2.5L: Reshape Data Lab
(Optional)

©2023 Databricks Inc. — All rights reserved 29

DE 2.7A: SQL UDFs and
Control Flow (Optional)

©2023 Databricks Inc. — All rights reserved 30

DE 2.7B: Python UDFs
(Optional)

©2023 Databricks Inc. — All rights reserved 31

©2023 Databricks Inc. — All rights reserved 32