Skip to main content

Apache Spark

Apache Spark is a distributed processing engine. The DuckLake Spark connector writes Spark DataFrames into a MotherDuck-hosted DuckLake database, and Spark can read MotherDuck over the Postgres endpoint.

Apache Spark is a distributed processing engine for large-scale data. MotherDuck's DuckLake Spark connector lets a Spark job write DataFrames straight into a MotherDuck-hosted DuckLake database, so Spark handles the heavy transformation and MotherDuck serves the queries.

Write to MotherDuck with the DuckLake Spark connector​

The connector is a Spark DataSource V2 catalog implementation published to Maven Central. Spark writes the Parquet files to your bucket, and the connector uses the DuckLake extension to commit them to the MotherDuck catalog.

This requires a bring-your-own-bucket DuckLake database: Spark writes directly to the storage, so the storage has to be yours.

Add the connector when you submit the job:

spark-submit --packages com.motherduck:ducklake-spark_2.12:0.2.0 my_job.py

The artifact suffix is the Scala binary version, which must match your Spark runtime. Use ducklake-spark_2.12 for default Spark 3.x builds, including pyspark from PyPI, and ducklake-spark_2.13 for Scala 2.13 builds. A mismatch fails at class loading rather than with a clear error.

Configure the catalog with your MotherDuck DuckLake database and the bucket credentials Spark needs:

from pyspark.sql import SparkSession

spark = (
    SparkSession.builder
    .appName("orders-load")
    .config("spark.sql.catalog.ducklake", "com.motherduck.spark.catalog.DuckLakeCatalog")
    .config("spark.sql.catalog.ducklake.path", "md:my_ducklake")
    .config("spark.sql.catalog.ducklake.motherduck-token", "<motherduck_token>")
    .config("spark.jars.packages",
            "org.apache.hadoop:hadoop-aws:3.3.4,com.amazonaws:aws-java-sdk-bundle:1.12.262")
    .config("spark.hadoop.fs.s3a.access.key", "<aws_access_key_id>")
    .config("spark.hadoop.fs.s3a.secret.key", "<aws_secret_access_key>")
    .config("spark.hadoop.fs.s3a.region", "<aws_region>")
    .getOrCreate()
)

df = spark.createDataFrame([(1, "Alice"), (2, "Bob")], ["id", "name"])
df.writeTo("ducklake.main.users").append()

spark.stop()

The connector reads a motherduck_token environment variable too, which is the better option in production so the token stays out of job configuration.

Create the target table in MotherDuck first, with the types you want:

CREATE DATABASE my_ducklake (TYPE DUCKLAKE, DATA_PATH 's3://my-bucket/ducklake/');

CREATE TABLE my_ducklake.main.users (id INTEGER, name VARCHAR);

Connector limitations​

  • Write-only. Reading through the connector isn't implemented. Read with a DuckDB client or the Postgres endpoint instead.
  • Append only. Overwrite and other save modes aren't supported.
  • No schema evolution. The DataFrame schema must match the table exactly. Spark LongType fails against an INTEGER column, so set explicit schemas rather than relying on inference.
  • No complex types. Lists, structs, and JSON columns aren't supported.
  • No UUID or BLOB columns. Spark has no native UUID type and writes strings, and BinaryType maps to a Parquet byte array that doesn't match DuckLake BLOB. Use VARCHAR columns instead, base64-encoding binary values.
  • Spark 4 isn't supported yet. It needs Scala 2.13 and a newer DataSource V2 API than the connector targets.

If S3 writes fail with an access error, check the region as well as the credentials. If the Hadoop S3 jars can't be found, pass them through spark.jars.packages as shown above or place them in $SPARK_HOME/jars.

Exchange Parquet files​

Without the connector, the plain route works fine and has no version constraints: Spark writes Parquet to object storage, and MotherDuck reads it.

df.write.mode("overwrite").parquet("s3a://my-bucket/spark-output/orders/")
CREATE SECRET my_s3_secret IN MOTHERDUCK (
    TYPE S3,
    KEY_ID '<aws_access_key_id>',
    SECRET '<aws_secret_access_key>',
    REGION '<aws_region>'
);

CREATE OR REPLACE TABLE orders AS
SELECT * FROM read_parquet('s3://my-bucket/spark-output/orders/*.parquet');

This is also the highest-throughput way to load a large result into MotherDuck, whichever route you use for the rest of the pipeline.

Read MotherDuck from Spark​

To pull MotherDuck data into a DataFrame, use Spark's JDBC source against the Postgres endpoint with the standard PostgreSQL driver:

df = (
    spark.read.format("jdbc")
    .option("url", "jdbc:postgresql://pg.us-east-1-aws.motherduck.com:5432/md:?sslmode=require")
    .option("driver", "org.postgresql.Driver")
    .option("dbtable", "main.orders")
    .option("user", "postgres")
    .option("password", "<motherduck_token>")
    .load()
)

Push filters and aggregations into the dbtable subquery so MotherDuck does the work rather than shipping every row to Spark. The endpoint's limitations apply: no local files, no extension loading, no Dual Execution. Avoid JDBC writes into MotherDuck, which go row by row.

Related content​