Lesson 4 of 5 in this module

4/21 in track

Reading and writing data

pyspark12 min

Overview

On a real cluster, Spark reads files such as Parquet and writes results back to storage. In this tab the prepared catalog is available through spark.table, so you can focus on the read and write ideas.

On this page7 sections

What you will do

A Spark job reads yesterday's orders from cloud storage, cleans them and writes a result. The first version reads every column as text, and it writes thousands of tiny files.

In this lesson you will see why the file format matters, how to ask for only the columns you need, and what happens to the output when Spark writes it.

Which file formats will you meet?

Data in storage is saved in a file format, which is a set of rules for how the values are laid out. Spark reads three formats all the time.

Parquet is the usual choice for pipelines. CSV and JSON arrive from outside.

FormatWhat it isRemembers column types?Where you see it
ParquetA compact file that stores each column togetherYesProduction tables and data lakes
CSVPlain text, one row per line, commas between valuesNoExports and files from other companies
JSONPlain text with labelled values, often one record per lineNoData from web services and event streams

On a real cluster the files sit in cloud storage such as Amazon S3. You point Spark at a folder and it reads every file inside.

PythonA path-based read and write (cluster only, not run here)
# On a real cluster this is a whole job:
df = spark.read.parquet("s3://bucket/orders/")
df.write.mode("overwrite").parquet("s3://bucket/silver/orders/")

Read it line by line. spark.read.parquet loads a folder of Parquet files into a DataFrame. df.write.parquet saves a DataFrame into another folder. mode("overwrite") says to replace anything already there.

What is a catalog, and why does spark.table use one?

A catalog is a list that links a table name to where its files live. When you ask for spark.table("orders"), Spark looks the name up and finds the folder, the format and the columns for you.

This editor has no cloud storage, so every example loads a table by name. On a real cluster you will use both ways.

Two ways to get a DataFrame
Files in storageCatalog orspark.readDataFrame planAction: show orwrite

table() uses the catalog. read.parquet uses a path. Both give you a DataFrame plan that runs when you ask for a result.

Words you will see in this lesson

Six words. The rest of the lesson uses only these.

WordWhat it means
SparkSessionThe front door to Spark. You start every job from it
ParquetA compact file format for big tables
CSVA text file with one row per line
SchemaThe column names and types of a table
Write modeWhat to do if the output already exists
DataFrameA table that Spark spreads across machines

Why this skill

Why does the file format change how fast a job runs?

Say a table has 200 columns and your report needs 2 of them. CSV stores whole rows, so Spark must read every line in full to reach those 2 columns.

Parquet stores each column in its own strip. Spark reads the 2 strips it needs and never touches the other 198.

Left: four CSV lines, each holding a whole row. Right: the same data as Parquet column strips, with order_id, order_total and order_status highlighted as read and customer and country marked skipped
CSV keeps each row together, so you read whole lines. Parquet keeps each column together, so you can read only the strips you asked for.

Select only the columns you need. With Parquet, the other columns are never read.

Choosing columns early is called column pruning, because you cut away what you do not need before it travels anywhere.

What goes wrong when Spark guesses the types in a CSV?

A CSV file does not say which column is a number. If you ask Spark to guess with inferSchema, it first reads the whole file once just to look at the values. Then it reads the file again to do your job.

On a multi-gigabyte file that can double the reading time. In real jobs you give Spark the schema yourself, so it can start straight away.

Why do write modes matter?

When Spark writes, it must know what to do if the output folder already holds files. That choice is the write mode.

The mode decides whether running a job twice is safe.

Write modeIf the folder already has filesWhat a retry does
appendAdds new files next to the old onesAdds the same rows a second time
overwriteReplaces the old filesGives the same result again
ignoreDoes nothing and says nothingSkips the write
errorIfExistsStops with an errorFails, so nothing is overwritten by accident

Suppose a nightly job fails halfway and the scheduler runs it again. With append, the rows from the first try are still there, so the table now holds duplicates. With overwrite, the second run replaces everything.

What is the small file problem?

Each partition becomes its own output file. If a DataFrame has 2,000 partitions but only 50,000 rows, Spark writes 2,000 files of about 25 rows each.

Thousands of tiny files slow every later query, because listing the files takes longer than reading them. The fix is to cut the number of partitions just before the write.

How the code works

How do you pick columns and cap the rows?

Pick only the columns the next step needs, as early as you can. Here a table of orders is trimmed to three columns and eight rows.

Columns we will select from orders
order_idorder_totalorder_status104284.50paid104319.00cancelled1044122.40paid

Only these 3 columns, and only the first few rows, are what the example returns.

PythonProject 3 columns, keep 8 rows
df = spark.table("orders")
result = df.select("order_id", "order_total", "order_status").limit(8)
result.show()
  • spark.table("orders") loads the table by name. No rows move yet.
  • select keeps three columns. limit(8) keeps eight rows.
  • show() is the action. Only now does Spark do the work and print the rows.

Each step returns a new DataFrame. The original orders table is never changed.

How do you write the output into folders?

The pattern below writes the result as Parquet, one folder per order status. It runs on a real cluster, not in this tab.

PythonWrite partitioned Parquet with controlled partition count (cluster only, not run here)
# Coalesce to avoid small files, then write partitioned Parquet
(
    result
    .coalesce(4)
    .write
    .mode("overwrite")
    .partitionBy("order_status")
    .parquet("s3://curated/orders/")
)
  • coalesce(4) joins neighbouring partitions until only 4 remain, so Spark writes a few healthy files and not hundreds.
  • partitionBy("order_status") makes one sub-folder for each status, such as order_status=paid.
  • A later query that filters on one status can skip the other folders completely.
  • s3://curated/orders/How the output folder looks on cloud storage
    • ├── order_status=paid/
    • │ ├── part-00000.snappy.parquet
    • │ └── part-00001.snappy.parquet
    • └── order_status=cancelled/
    • ├── part-00002.snappy.parquet
    • └── part-00003.snappy.parquet

What do CSV and JSON readers look like?

Files from other systems are usually CSV or JSON. They carry no types, so a real job names the schema.

Other readers you will meet (not run in this editor)
Watch outDo not invent file paths in this tab

spark.read.parquet("/some/path") will not open your laptop's disk here. Load spark.table("orders") and select columns. The path API is for a real cluster.

Why does Spark write a folder and not one file?

pandas runs in one process, so df.to_parquet("out.parquet") writes one file. Spark runs many tasks at once, and each task writes its own piece.

Same idea, different shape.

OperationpandasSpark
ReadLoads the whole table into one computer's memoryBuilds a plan that points at the files
SchemaGuesses types from the dataReads types from Parquet, or needs a schema
WriteOne output fileA folder of part files, one per partition
RememberIf you know pandas

pd.read_parquet and to_parquet become spark.read.parquet and df.write.parquet. pd.read_csv becomes spark.read.csv, with a schema. pandas writes one file. Spark writes many part files, one per partition, unless you reduce the partitions first.

Common beginner questions

Why is Parquet preferred?

It stores columns separately, compresses well and keeps the types. Spark can skip columns you did not select. CSV is for humans and awkward vendor files.

Why not keep everything as CSV so people can read it?

A person can open a small CSV in a spreadsheet. At gigabyte size, Spark must read every line in full, guess every type and skip nothing. Parquet is far smaller and faster, and tools can still show it as a table.

What does mode("overwrite") mean?

It replaces whatever is already in the output folder. append adds new files next to the old ones, and the other modes decide what to do if the output already exists. You will not write files in this tab, but you will see these on a real cluster.

Is spark.table the same as FROM orders in SQL?

Close. Both name a table the catalog knows. The next lesson shows you can write SELECT ... FROM orders as text and get the same result.

What is the difference between coalesce and repartition?

Both change the number of partitions. coalesce only merges neighbours, so it is cheap and good for reducing the count before a write. repartition reshuffles every row to balance the pieces, which costs more but can also increase the count.

What is the small file problem?

If a job produces thousands of tiny files, later queries spend more time listing files than reading data. Reduce partitions before writing, aiming for files of roughly 128 MB to 1 GB.

What comes next

You can now load a table, pick the columns you need and explain how a result is written. The next lesson shows that Spark also understands plain SQL, so you can ask the same question in either style.

  1. Use Parquet as the default storage format for pipelines.
  2. Give Spark a schema when you read CSV or JSON.
  3. Select only the columns you need, as early as possible.
  4. Reduce partitions before writing so you do not create thousands of tiny files.

Practice

Run the sample, then do the exercise: from spark.table("orders"), select order_id, order_total and order_status, limit to 8 rows and assign to result. You are practising a catalog load plus a column prune. Save the path-based readers for a real cluster.

Going further: what partitionBy does to your folders

Writing a table partitioned by a column makes one folder per value, so a later query that filters on that column can skip whole folders. The choice of column matters a great deal. The example uses DuckDB to show the same folder layout that Spark's partitionBy produces (the file names inside differ).

Predict how many folders and files are created when you partition by country (3 values) and by order_id (every row different).

PythonPartition by a low-cardinality and a high-cardinality column
import os, tempfile
import duckdb

con = duckdb.connect()
con.execute("""
    CREATE TABLE orders AS
    SELECT i AS order_id, ['IN', 'US', 'UK'][1 + i % 3] AS country, i * 10 AS amount
    FROM range(1, 301) t(i)
""")

def layout(column):
    root = tempfile.mkdtemp()
    con.execute(f"COPY orders TO '{root}' (FORMAT parquet, PARTITION_BY ({column}))")
    folders = sorted(d for d in os.listdir(root))
    files = sum(len(fs) for _, _, fs in os.walk(root))
    return folders, files

folders, files = layout("country")
print("by country :", folders, "files =", files)
folders, files = layout("order_id")
print("by order_id:", len(folders), "folders, files =", files)
by country : ['country=IN', 'country=UK', 'country=US'] files = 3
by order_id: 300 folders, files = 300

Partitioning by country gives 3 folders. Partitioning by order_id gives 300 folders holding 1 row each. That is the small-files problem: opening 300 files to read 300 rows is slower than reading one.

A good partition column has few distinct values, and appears in the filters people actually write, such as a date or a country.

Partition columnFoldersVerdict
order_date (about 365 a year)One per dayUsually good: queries filter by date
country (about 200)One per countryFine if queries filter by country, otherwise pointless
customer_id or order_idMillionsAlmost always wrong

Try a new scenario

Events arrive at 20 million a day. You partition by event_date and by event_hour, so each day has 24 folders. A query asks for 1 day. How many files might it open, and is the layout sensible?

Work it out, then open the answer

It opens at least the 24 hour folders for that day, and more if each write job produced several files. 20 million events a day is about 833,000 per hour, so each hourly folder holds a reasonable amount. The layout is sensible if queries often filter by hour. If nobody does, one folder per day is simpler and creates fewer, larger files.

Why prefer Parquet over CSV?

Parquet stores columns with their types, compresses well, and lets a reader skip columns and chunks it does not need. CSV is text, carries no types, and must be read in full. For repeated analysis Parquet is smaller and faster.

Close the lesson and explain it

Without scrolling up, explain what makes a good partition column and what goes wrong with a high-cardinality one.

Practicals · load into the editor

After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.

Finished this lesson?

Mark it as read when you could explain it to someone else.

Rate:
Was this useful?