> ## 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.

> Présentation d’Apache Spark avec 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>
            Compatible avec ClickHouse
        </div>;
};

<ClickHouseSupportedBadge />

JDBC est l’une des sources de données les plus utilisées dans Spark.
Dans cette section, nous expliquons comment utiliser le [connecteur JDBC officiel de ClickHouse](/docs/fr/integrations/language-clients/java/jdbc) avec Spark.

<div id="read-data">
  ## Lire les données
</div>

<Tabs>
  <Tab title="Java">
    ```java theme={null}
    public static void main(String[] args) {
            // Initialiser la session Spark
            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";

            //---------------------------------------------------------------------------------------------------
            // Charger la table depuis ClickHouse avec la méthode jdbc
            //---------------------------------------------------------------------------------------------------
            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();

            //---------------------------------------------------------------------------------------------------
            // Charger la table depuis ClickHouse avec la méthode load
            //---------------------------------------------------------------------------------------------------
            Dataset<Row> df2 = spark.read()
                    .format("jdbc")
                    .option("url", jdbcURL)
                    .option("user", "default")
                    .option("password", "123456")
                    .option("query", query)
                    .load();

            df2.show();

            // Arrêter la session Spark
            spark.stop();
        }
    ```
  </Tab>

  <Tab title="Scala">
    ```java theme={null}
    object ReadData extends App {
      // Initialiser la session Spark
      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"

      //---------------------------------------------------------------------------------------------------
      // Charger la table depuis ClickHouse avec la méthode jdbc
      //---------------------------------------------------------------------------------------------------
      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()
      //---------------------------------------------------------------------------------------------------
      // Charger la table depuis ClickHouse avec la méthode load
      //---------------------------------------------------------------------------------------------------
      val df2: Dataset[Row] = spark.read
        .format("jdbc")
        .option("url", jdbcURL)
        .option("user", "default")
        .option("password", "123456")
        .option("query", query)
        .load()

      df2.show()

      // Arrêter la session Spark// Arrêter la session Spark
      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"
    ]

    # Initialiser la session Spark avec les 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>

<div id="write-data">
  ## Écriture de données
</div>

<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>

<div id="parallelism">
  ## Parallélisme
</div>

Lors de l'utilisation de Spark JDBC, Spark lit les données à l'aide d'une seule partition. Pour obtenir davantage de parallélisme, vous devez spécifier
`partitionColumn`, `lowerBound`, `upperBound` et `numPartitions`, qui définissent comment partitionner la table lors de la
lecture en parallèle depuis plusieurs workers.
Veuillez consulter la documentation officielle d'Apache Spark pour plus d'informations
sur les [configurations JDBC](https://spark.apache.org/docs/latest/sql-data-sources-jdbc.html#data-source-option).

<div id="jdbc-limitations">
  ## Limitations de JDBC
</div>

* Spark JDBC ne prend pas en charge les types complexes (MAP, ARRAY, STRUCT) en raison de l'absence de dialecte ClickHouse - utilisez le connecteur natif Spark-ClickHouse pour bénéficier d'une prise en charge complète des types complexes.
* À ce jour, vous ne pouvez insérer des données via JDBC que dans des tables existantes (il n'existe actuellement aucun moyen de créer automatiquement la
  table lors de l'insertion du DF, comme Spark le fait avec d'autres connecteurs).