PySpark新手求助：按ID关联MySQL表获取HOST值无结果问题

Hey there! Let's break down why your current code isn't working and get you the right results the Spark way.

What's Wrong With Your Current Code? #

Your approach has two key issues that are blocking you from getting the correct HOST values for each account_id:

1. You're overwriting your DataFrame in every loop
   Each time you run datasource_df = datasource_df.select(...), you replace the original full datasource_df with a filtered version that only contains results from the first account_id. By the second loop, you're searching for the next ID in a tiny subset of data—so no matches show up.
2. Using collect() defeats Spark's distributed purpose
   Calling collect() pulls all data from criteria_df onto your Driver node. For large datasets, this can cause memory crashes, and it completely ignores Spark's ability to process data in parallel across a cluster.

The Correct Spark-Friendly Solution: Use join #

Spark is built for distributed data operations, so instead of looping through rows, you should use a join to match your two tables directly. This is efficient, scalable, and aligns with how Spark is meant to work.

Here's the revised code:

import pyspark.sql.functions as F

# Load your tables as before
criteria_df = read_data_from_table(criteria_tbl)
datasource_df = read_data_from_table(data_source_tbl)

# Join the two tables on account_id (from criteria) and id (from datasource)
# Rename datasource's "id" to "account_id" to make the join column name consistent
result_df = criteria_df.join(
    datasource_df.select(F.col("id").alias("account_id"), F.col("host")),
    on="account_id",
    how="inner"  # Use "left" if you want to keep all account_ids even if no HOST exists
).select("account_id", "criteria", "host")  # Select the columns you need

# View the final results
result_df.show()

How This Works: #

• The join operation runs across your Spark cluster, matching every account_id from criteria_df with the corresponding id in datasource_df in parallel.
• You avoid overwriting your original DataFrame, so you get matches for all account_ids in one go.
• No collect() means you keep data distributed and avoid memory issues with large datasets.

Quick Adjustments to Fit Your Needs: #

• If account_id (from criteria) and id (from datasource) have different data types (e.g., one is string, one is integer), use F.cast() to align them before joining (e.g., F.col("id").cast("string").alias("account_id")).
• Tweak the how parameter in the join:
  • inner: Only keep account_ids that have a matching HOST (default)
  • left: Keep all account_ids from criteria_df, even if no HOST exists (will show null for missing HOSTs)

内容的提问来源于stack exchange，提问作者Vishnu Chaturvedi