在Databricks中基于多个独立Parquet路径创建Spark SQL表

Got it, I've run into this exact scenario before when working with unconnected Parquet data paths in Databricks. Here are two solid approaches to create a single table from your two independent paths:

Approach 1: Directly Specify Multiple Paths in CREATE TABLE #

Spark allows you to pass multiple comma-separated paths in the path option when creating a Parquet table. This is the simplest method if your two paths have identical schemas (same column names, data types, and column order).

Code Example #

target_table_name = 'test_table_1'

# Drop existing table if it exists
spark.sql(f"""DROP TABLE IF EXISTS {target_table_name}""")

# Create table with both paths
spark.sql(f"""
CREATE TABLE IF NOT EXISTS {target_table_name}
USING org.apache.spark.sql.parquet
OPTIONS (
  path "/mnt/sparktables/ds=*/name=xyz/,/mnt/sparktables/new_path/name=123fo/"
)
""")

Key Notes #

• This creates an external table that directly references the original data locations (no data is copied).
• If the schemas of the two paths don't match, Spark will throw a schema mismatch error immediately. You'll need to fix the schema alignment first (see Approach 2 for how to handle this).

Approach 2: Read, Align, Union, and Write (Flexible for Schema Differences) #

If your two paths have different schemas, or you want more control over the data merging process, this method is better. You'll read each path into a DataFrame, align their schemas, union them, then write the result to a table.

Code Example #

target_table_name = 'test_table_1'

# Read data from both paths
df1 = spark.read.parquet("/mnt/sparktables/ds=*/name=xyz/")
df2 = spark.read.parquet("/mnt/sparktables/new_path/name=123fo/")

# --- Optional: Align schemas if they differ ---
# Example: Rename columns, cast data types, or add missing columns
# df2_aligned = df2.select(
#     "id",
#     "value",
#     df2["old_column"].alias("new_column"),
#     cast(df2["string_num"] as IntegerType()).alias("int_num"),
#     lit(None).alias("missing_column_from_df2")
# )

# Union the DataFrames (use unionByName to match columns by name instead of position)
combined_df = df1.unionByName(df2, allowMissingColumns=False)

# Write to a managed table (or add `option("path", "/your/external/path")` for external)
combined_df.write.mode("overwrite").saveAsTable(target_table_name)

# Alternatively, create a temporary view if you don't need a persistent table
# combined_df.createOrReplaceTempView(target_table_name)

Key Notes #

• unionByName is safer than union because it matches columns by name instead of position. Use allowMissingColumns=True if you want to fill missing columns with NULL.
• This method copies the combined data to the table's storage location (unless you specify an external path), which is useful if you want to consolidate the data into a single location.
• You can add transformations (filtering, cleaning, etc.) between reading the DataFrames and writing the table.

Final Checks #

After creating the table, verify that all data is loaded correctly with a quick count:

spark.sql(f"SELECT COUNT(*) FROM {target_table_name}").show()

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