> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

> Introduction to Apache Spark with ClickHouse

# Spark JDBC

export const ClickHouseSupportedBadge = () => {
  return <div className="ClickHouseSupportedBadge">
            <div className="ClickHouseSupportedIcon">
                <svg width="16" height="16" viewBox="0 0 16 16" fill="none" xmlns="http://www.w3.org/2000/svg">
                    <path d="M1.30762 1.39073C1.30762 1.3103 1.37465 1.22986 1.46849 1.22986H2.64824C2.72868 1.22986 2.80912 1.29689 2.80912 1.39073V14.4886C2.80912 14.5691 2.74209 14.6495 2.64824 14.6495H1.46849C1.38805 14.6495 1.30762 14.5825 1.30762 14.4886V1.39073Z" fill="currentColor" />
                    <path d="M4.2832 1.39073C4.2832 1.3103 4.35023 1.22986 4.44408 1.22986H5.62383C5.70427 1.22986 5.7847 1.29689 5.7847 1.39073V14.4886C5.7847 14.5691 5.71767 14.6495 5.62383 14.6495H4.44408C4.36364 14.6495 4.2832 14.5825 4.2832 14.4886V1.39073Z" fill="currentColor" />
                    <path d="M7.25977 1.39073C7.25977 1.3103 7.3268 1.22986 7.42064 1.22986H8.60039C8.68083 1.22986 8.76127 1.29689 8.76127 1.39073V14.4886C8.76127 14.5691 8.69423 14.6495 8.60039 14.6495H7.42064C7.3402 14.6495 7.25977 14.5825 7.25977 14.4886V1.39073Z" fill="currentColor" />
                    <path d="M10.2354 1.39073C10.2354 1.3103 10.3024 1.22986 10.3962 1.22986H11.576C11.6564 1.22986 11.7369 1.29689 11.7369 1.39073V14.4886C11.7369 14.5691 11.6698 14.6495 11.576 14.6495H10.3962C10.3158 14.6495 10.2354 14.5825 10.2354 14.4886V1.39073Z" fill="currentColor" />
                    <path d="M13.2256 6.6057C13.2256 6.52526 13.2926 6.44482 13.3865 6.44482H14.5662C14.6466 6.44482 14.7271 6.51186 14.7271 6.6057V9.27354C14.7271 9.35398 14.6601 9.43442 14.5662 9.43442H13.3865C13.306 9.43442 13.2256 9.36739 13.2256 9.27354V6.6057Z" fill="currentColor" />
                </svg>
            </div>
            ClickHouse Supported
        </div>;
};

<ClickHouseSupportedBadge />

JDBC is one of the most commonly used data sources in Spark.
In this section, we will provide details on how to
use the [ClickHouse official JDBC connector](/docs/integrations/language-clients/java/jdbc) with Spark.

<h2 id="read-data">
  Read data
</h2>

<Tabs>
  <Tab title="Java">
    ```java theme={null}
    public static void main(String[] args) {
            // Initialize Spark session
            SparkSession spark = SparkSession.builder().appName("example").master("local").getOrCreate();

            String jdbcURL = "jdbc:ch://localhost:8123/default";
            String query = "select * from example_table where id > 2";

            //---------------------------------------------------------------------------------------------------
            // Load the table from ClickHouse using jdbc method
            //---------------------------------------------------------------------------------------------------
            Properties jdbcProperties = new Properties();
            jdbcProperties.put("user", "default");
            jdbcProperties.put("password", "123456");

            Dataset<Row> df1 = spark.read().jdbc(jdbcURL, String.format("(%s)", query), jdbcProperties);

            df1.show();

            //---------------------------------------------------------------------------------------------------
            // Load the table from ClickHouse using load method
            //---------------------------------------------------------------------------------------------------
            Dataset<Row> df2 = spark.read()
                    .format("jdbc")
                    .option("url", jdbcURL)
                    .option("user", "default")
                    .option("password", "123456")
                    .option("query", query)
                    .load();

            df2.show();

            // Stop the Spark session
            spark.stop();
        }
    ```
  </Tab>

  <Tab title="Scala">
    ```java theme={null}
    object ReadData extends App {
      // Initialize Spark session
      val spark: SparkSession = SparkSession.builder.appName("example").master("local").getOrCreate

      val jdbcURL = "jdbc:ch://localhost:8123/default"
      val query: String = "select * from example_table where id > 2"

      //---------------------------------------------------------------------------------------------------
      // Load the table from ClickHouse using jdbc method
      //---------------------------------------------------------------------------------------------------
      val connectionProperties = new Properties()
      connectionProperties.put("user", "default")
      connectionProperties.put("password", "123456")

      val df1: Dataset[Row] = spark.read.
        jdbc(jdbcURL, s"($query)", connectionProperties)

      df1.show()
      //---------------------------------------------------------------------------------------------------
      // Load the table from ClickHouse using load method
      //---------------------------------------------------------------------------------------------------
      val df2: Dataset[Row] = spark.read
        .format("jdbc")
        .option("url", jdbcURL)
        .option("user", "default")
        .option("password", "123456")
        .option("query", query)
        .load()

      df2.show()

      // Stop the Spark session// Stop the Spark session
      spark.stop()

    }
    ```
  </Tab>

  <Tab title="Python">
    ```python theme={null}
    from pyspark.sql import SparkSession

    jar_files = [
        "jars/clickhouse-jdbc-X.X.X-SNAPSHOT-all.jar"
    ]

    # Initialize Spark session with JARs
    spark = SparkSession.builder \
        .appName("example") \
        .master("local") \
        .config("spark.jars", ",".join(jar_files)) \
        .getOrCreate()

    url = "jdbc:ch://localhost:8123/default"
    user = "your_user" 
    password = "your_password"  
    query = "select * from example_table where id > 2"
    driver = "com.clickhouse.jdbc.ClickHouseDriver"

    df = (spark.read
          .format('jdbc')
          .option('driver', driver)
          .option('url', url)
          .option('user', user)
          .option('password', password).option(
        'query', query).load())

    df.show()

    ```
  </Tab>

  <Tab title="Spark SQL">
    ```sql theme={null}
       CREATE TEMPORARY VIEW jdbcTable
               USING org.apache.spark.sql.jdbc
               OPTIONS (
                       url "jdbc:ch://localhost:8123/default", 
                       dbtable "schema.tablename",
                       user "username",
                       password "password",
                       driver "com.clickhouse.jdbc.ClickHouseDriver" 
               );
               
       SELECT * FROM jdbcTable;
    ```
  </Tab>
</Tabs>

<h2 id="write-data">
  Write data
</h2>

<Tabs>
  <Tab title="Java">
    ```java theme={null}
     public static void main(String[] args) {
            // Initialize Spark session
            SparkSession spark = SparkSession.builder().appName("example").master("local").getOrCreate();

            // JDBC connection details
            String jdbcUrl = "jdbc:ch://localhost:8123/default";
            Properties jdbcProperties = new Properties();
            jdbcProperties.put("user", "default");
            jdbcProperties.put("password", "123456");

            // Create a sample DataFrame
            StructType schema = new StructType(new StructField[]{
                    DataTypes.createStructField("id", DataTypes.IntegerType, false),
                    DataTypes.createStructField("name", DataTypes.StringType, false)
            });

            List<Row> rows = new ArrayList<Row>();
            rows.add(RowFactory.create(1, "John"));
            rows.add(RowFactory.create(2, "Doe"));

            Dataset<Row> df = spark.createDataFrame(rows, schema);

            //---------------------------------------------------------------------------------------------------
            // Write the df to ClickHouse using the jdbc method
            //---------------------------------------------------------------------------------------------------

            df.write()
                    .mode(SaveMode.Append)
                    .jdbc(jdbcUrl, "example_table", jdbcProperties);

            //---------------------------------------------------------------------------------------------------
            // Write the df to ClickHouse using the save method
            //---------------------------------------------------------------------------------------------------

            df.write()
                    .format("jdbc")
                    .mode("append")
                    .option("url", jdbcUrl)
                    .option("dbtable", "example_table")
                    .option("user", "default")
                    .option("password", "123456")
                    .save();

            // Stop the Spark session
            spark.stop();
        }
    ```
  </Tab>

  <Tab title="Scala">
    ```java theme={null}
    object WriteData extends App {

      val spark: SparkSession = SparkSession.builder.appName("example").master("local").getOrCreate

      // JDBC connection details
      val jdbcUrl: String = "jdbc:ch://localhost:8123/default"
      val jdbcProperties: Properties = new Properties
      jdbcProperties.put("user", "default")
      jdbcProperties.put("password", "123456")

      // Create a sample DataFrame

      val rows = Seq(Row(1, "John"), Row(2, "Doe"))

      val schema = List(
        StructField("id", DataTypes.IntegerType, nullable = false),
        StructField("name", StringType, nullable = true)
      )

      val df: DataFrame = spark.createDataFrame(
        spark.sparkContext.parallelize(rows),
        StructType(schema)
      )
      
      //---------------------------------------------------------------------------------------------------//---------------------------------------------------------------------------------------------------
      // Write the df to ClickHouse using the jdbc method
      //---------------------------------------------------------------------------------------------------//---------------------------------------------------------------------------------------------------

      df.write
        .mode(SaveMode.Append)
        .jdbc(jdbcUrl, "example_table", jdbcProperties)

      //---------------------------------------------------------------------------------------------------//---------------------------------------------------------------------------------------------------
      // Write the df to ClickHouse using the save method
      //---------------------------------------------------------------------------------------------------//---------------------------------------------------------------------------------------------------

      df.write
        .format("jdbc")
        .mode("append")
        .option("url", jdbcUrl)
        .option("dbtable", "example_table")
        .option("user", "default")
        .option("password", "123456")
        .save()

      // Stop the Spark session// Stop the Spark session
      spark.stop()

    }
    ```
  </Tab>

  <Tab title="Python">
    ```python theme={null}
    from pyspark.sql import SparkSession
    from pyspark.sql import Row

    jar_files = [
        "jars/clickhouse-jdbc-X.X.X-SNAPSHOT-all.jar"
    ]

    # Initialize Spark session with JARs
    spark = SparkSession.builder \
        .appName("example") \
        .master("local") \
        .config("spark.jars", ",".join(jar_files)) \
        .getOrCreate()

    # Create DataFrame
    data = [Row(id=11, name="John"), Row(id=12, name="Doe")]
    df = spark.createDataFrame(data)

    url = "jdbc:ch://localhost:8123/default"
    user = "your_user" 
    password = "your_password"  
    driver = "com.clickhouse.jdbc.ClickHouseDriver"

    # Write DataFrame to ClickHouse
    df.write \
        .format("jdbc") \
        .option("driver", driver) \
        .option("url", url) \
        .option("user", user) \
        .option("password", password) \
        .option("dbtable", "example_table") \
        .mode("append") \
        .save()

    ```
  </Tab>

  <Tab title="Spark SQL">
    ```sql theme={null}
       CREATE TEMPORARY VIEW jdbcTable
               USING org.apache.spark.sql.jdbc
               OPTIONS (
                       url "jdbc:ch://localhost:8123/default", 
                       dbtable "schema.tablename",
                       user "username",
                       password "password",
                       driver "com.clickhouse.jdbc.ClickHouseDriver" 
               );
       -- resultTable could be created with df.createTempView or with Spark SQL
       INSERT INTO TABLE jdbcTable
                    SELECT * FROM resultTable;
                    
    ```
  </Tab>
</Tabs>

<h2 id="parallelism">
  Parallelism
</h2>

When using Spark JDBC, Spark reads the data using a single partition. To achieve higher concurrency, you must specify
`partitionColumn`, `lowerBound`, `upperBound`, and `numPartitions`, which describe how to partition the table when
reading in parallel from multiple workers.
Please visit Apache Spark's official documentation for more information
on [JDBC configurations](https://spark.apache.org/docs/latest/sql-data-sources-jdbc.html#data-source-option).

<h2 id="jdbc-limitations">
  JDBC limitations
</h2>

* Spark JDBC lacks support for complex types (MAP, ARRAY, STRUCT) due to missing ClickHouse dialect - use the native Spark-ClickHouse connector for full complex type support.
* As of today, you can insert data using JDBC only into existing tables (currently, there is no way to auto-create the
  table on DF insertion, as Spark does with other connectors).