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

> Introducción a Apache Spark con 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 con ClickHouse
        </div>;
};

<ClickHouseSupportedBadge />

JDBC es una de las fuentes de datos más utilizadas en Spark.
En esta sección, explicaremos cómo
usar el [conector JDBC oficial de ClickHouse](/docs/es/integrations/language-clients/java/jdbc) con Spark.

<div id="read-data">
  ## Leer datos
</div>

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

            //---------------------------------------------------------------------------------------------------
            // Carga la tabla desde ClickHouse mediante el método 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();

            //---------------------------------------------------------------------------------------------------
            // Carga la tabla desde ClickHouse mediante el método load
            //---------------------------------------------------------------------------------------------------
            Dataset<Row> df2 = spark.read()
                    .format("jdbc")
                    .option("url", jdbcURL)
                    .option("user", "default")
                    .option("password", "123456")
                    .option("query", query)
                    .load();

            df2.show();

            // Detiene la sesión de Spark
            spark.stop();
        }
    ```
  </Tab>

  <Tab title="Scala">
    ```java theme={null}
    object ReadData extends App {
      // Inicializa la sesión de 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"

      //---------------------------------------------------------------------------------------------------
      // Carga la tabla desde ClickHouse mediante el método 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()
      //---------------------------------------------------------------------------------------------------
      // Carga la tabla desde ClickHouse mediante el método load
      //---------------------------------------------------------------------------------------------------
      val df2: Dataset[Row] = spark.read
        .format("jdbc")
        .option("url", jdbcURL)
        .option("user", "default")
        .option("password", "123456")
        .option("query", query)
        .load()

      df2.show()

      // Detiene la sesión de Spark// Detiene la sesión de 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"
    ]

    # Inicializa la sesión de Spark con 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">
  ## Escritura de datos
</div>

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

            // Detalles de conexión JDBC
            String jdbcUrl = "jdbc:ch://localhost:8123/default";
            Properties jdbcProperties = new Properties();
            jdbcProperties.put("user", "default");
            jdbcProperties.put("password", "123456");

            // Crear un DataFrame de ejemplo
            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);

            //---------------------------------------------------------------------------------------------------
            // Escribir el df en ClickHouse usando el método jdbc
            //---------------------------------------------------------------------------------------------------

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

            //---------------------------------------------------------------------------------------------------
            // Escribir el df en ClickHouse usando el método save
            //---------------------------------------------------------------------------------------------------

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

            // Detener la sesión de Spark
            spark.stop();
        }
    ```
  </Tab>

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

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

      // Detalles de conexión JDBC
      val jdbcUrl: String = "jdbc:ch://localhost:8123/default"
      val jdbcProperties: Properties = new Properties
      jdbcProperties.put("user", "default")
      jdbcProperties.put("password", "123456")

      // Crear un DataFrame de ejemplo

      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)
      )
      
      //---------------------------------------------------------------------------------------------------//---------------------------------------------------------------------------------------------------
      // Escribir el df en ClickHouse usando el método jdbc
      //---------------------------------------------------------------------------------------------------//---------------------------------------------------------------------------------------------------

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

      //---------------------------------------------------------------------------------------------------//---------------------------------------------------------------------------------------------------
      // Escribir el df en ClickHouse usando el método save
      //---------------------------------------------------------------------------------------------------//---------------------------------------------------------------------------------------------------

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

      // Detener la sesión de Spark// Detener la sesión de Spark
      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"
    ]

    # Inicializar sesión de Spark con JARs
    spark = SparkSession.builder \
        .appName("example") \
        .master("local") \
        .config("spark.jars", ",".join(jar_files)) \
        .getOrCreate()

    # Crear 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"

    # Escribir DataFrame en 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 puede crearse con df.createTempView o con Spark SQL
       INSERT INTO TABLE jdbcTable
                    SELECT * FROM resultTable;
                    
    ```
  </Tab>
</Tabs>

<div id="parallelism">
  ## Paralelismo
</div>

Al usar Spark JDBC, Spark lee los datos con una sola partición. Para lograr una mayor concurrencia, debe especificar
`partitionColumn`, `lowerBound`, `upperBound` y `numPartitions`, que describen cómo particionar la tabla al
leer en paralelo con varios workers.
Consulte la documentación oficial de Apache Spark para obtener más información
sobre la [configuración de JDBC](https://spark.apache.org/docs/latest/sql-data-sources-jdbc.html#data-source-option).

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

* Spark JDBC no admite tipos complejos (MAP, ARRAY, STRUCT) debido a la ausencia del dialecto de ClickHouse; use el conector nativo Spark-ClickHouse para obtener compatibilidad total con tipos complejos.
* A día de hoy, solo puede insertar datos mediante JDBC en tablas existentes (actualmente, no hay forma de crear automáticamente la
  tabla al insertar el DF, como hace Spark con otros conectores).