Spark connector

# Create DataFrame with JSON data
json_data = [
    (1, '{"name": "Alice", "age": 30}'),
    (2, '{"name": "Bob", "age": 25}'),
    (3, '{"name": "Charlie", "city": "NYC"}')
]
df = spark.createDataFrame(json_data, ["id", "json_string"])

# Parse JSON strings to VariantType
variant_df = df.select(
    "id",
    parse_json("json_string").alias("data")
)

# Write to ClickHouse with JSON type
variant_df.writeTo("clickhouse.default.user_data").create()

# Or specify Variant with multiple types
spark.sql("""
  CREATE TABLE clickhouse.default.mixed_data (
    id INT,
    data VARIANT
  ) USING clickhouse
  TBLPROPERTIES (
    'clickhouse.column.data.variant_types' = 'String, Int64, Bool, JSON',
    'engine' = 'MergeTree()',
    'order_by' = 'id'
  )
""")
```

```
import static org.apache.spark.sql.functions.*;

// Create DataFrame with JSON data
List<Row> jsonData = Arrays.asList(
    RowFactory.create(1, "{\"name\": \"Alice\", \"age\": 30}"),
    RowFactory.create(2, "{\"name\": \"Bob\", \"age\": 25}"),
    RowFactory.create(3, "{\"name\": \"Charlie\", \"city\": \"NYC\"}")
);
StructType schema = new StructType(new StructField[]{
    DataTypes.createStructField("id", DataTypes.IntegerType, false),
    DataTypes.createStructField("json_string", DataTypes.StringType, false)
});
Dataset<Row> jsonDF = spark.createDataFrame(jsonData, schema);

// Parse JSON strings to VariantType
Dataset<Row> variantDF = jsonDF.select(
    col("id"),
    parse_json(col("json_string")).as("data")
);

// Write to ClickHouse with JSON type (JSON objects only)
variantDF.writeTo("clickhouse.default.user_data").create();

// Or specify Variant with multiple types
spark.sql("CREATE TABLE clickhouse.default.mixed_data (" +
    "id INT, " +
    "data VARIANT" +
    ") USING clickhouse " +
    "TBLPROPERTIES (" +
    "'clickhouse.column.data.variant_types' = 'String, Int64, Bool, JSON', " +
    "'engine' = 'MergeTree()', " +
    "'order_by' = 'id'" +
    ")");
```

### Creating VariantType Tables with Spark SQL[​](#creating-varianttype-tables-spark-sql "Direct link to Creating VariantType Tables with Spark SQL")

You can create VariantType tables using Spark SQL DDL:

```
-- Create table with JSON type (default)
CREATE TABLE clickhouse.default.json_table (
  id INT,
  data VARIANT
) USING clickhouse
TBLPROPERTIES (
  'engine' = 'MergeTree()',
  'order_by' = 'id'
)
```

```
-- Create table with Variant type supporting multiple types
CREATE TABLE clickhouse.default.flexible_data (
  id INT,
  data VARIANT
) USING clickhouse
TBLPROPERTIES (
  'clickhouse.column.data.variant_types' = 'String, Int64, Float64, Bool, Array(String), JSON',
  'engine' = 'MergeTree()',
  'order_by' = 'id'
)
```

### Configuring Variant Types[​](#configuring-variant-types "Direct link to Configuring Variant Types")

When creating tables with VariantType columns, you can specify which ClickHouse types to use:

#### JSON Type (Default)[​](#json-type-default "Direct link to JSON Type (Default)")

If no `variant_types` property is specified, the column defaults to ClickHouse's `JSON` type, which only accepts JSON objects:

```
CREATE TABLE clickhouse.default.json_table (
  id INT,
  data VARIANT
) USING clickhouse
TBLPROPERTIES (
  'engine' = 'MergeTree()',
  'order_by' = 'id'
)
```

This creates the following ClickHouse query:

```
CREATE TABLE json_table (id Int32, data JSON) ENGINE = MergeTree() ORDER BY id
```

#### Variant Type with Multiple Types[​](#variant-type-multiple-types "Direct link to Variant Type with Multiple Types")

To support primitives, arrays, and JSON objects, specify the types in the `variant_types` property:

```
CREATE TABLE clickhouse.default.flexible_data (
  id INT,
  data VARIANT
) USING clickhouse
TBLPROPERTIES (
  'clickhouse.column.data.variant_types' = 'String, Int64, Float64, Bool, Array(String), JSON',
  'engine' = 'MergeTree()',
  'order_by' = 'id'
)
```

This creates the following ClickHouse query:

```
CREATE TABLE flexible_data (
  id Int32, 
  data Variant(String, Int64, Float64, Bool, Array(String), JSON)
) ENGINE = MergeTree() ORDER BY id
```

### Supported Variant Types[​](#supported-variant-types "Direct link to Supported Variant Types")

The following ClickHouse types can be used in `Variant()`:

- **Primitives**: `String`, `Int8`, `Int16`, `Int32`, `Int64`, `UInt8`, `UInt16`, `UInt32`, `UInt64`, `Float32`, `Float64`, `Bool`
- **Arrays**: `Array(T)` where T is any supported type, including nested arrays
- **JSON**: `JSON` for storing JSON objects

### Read Format Configuration[​](#read-format-configuration "Direct link to Read Format Configuration")

By default, JSON and Variant columns are read as `VariantType`. You can override this behavior to read them as strings:

- Scala- Python- Java

```
// Read JSON/Variant as strings instead of VariantType
spark.conf.set("spark.clickhouse.read.jsonAs", "string")

val df = spark.sql("SELECT id, data FROM clickhouse.default.json_table")
// data column will be StringType containing JSON strings
```

```
# Read JSON/Variant as strings instead of VariantType
spark.conf.set("spark.clickhouse.read.jsonAs", "string")

df = spark.sql("SELECT id, data FROM clickhouse.default.json_table")
# data column will be StringType containing JSON strings
```

```
// Read JSON/Variant as strings instead of VariantType
spark.conf().set("spark.clickhouse.read.jsonAs", "string");

Dataset<Row> df = spark.sql("SELECT id, data FROM clickhouse.default.json_table");
// data column will be StringType containing JSON strings
```

### Write Format Support[​](#write-format-support "Direct link to Write Format Support")

VariantType write support varies by format:

|  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Format Support Notes|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | JSON ✅ Full Supports both `JSON` and `Variant` types. Recommended for VariantType data| Arrow ⚠️ Partial Supports writing to ClickHouse `JSON` type. Doesn't support ClickHouse `Variant` type. Full support is pending resolution of <https://github.com/ClickHouse/ClickHouse/issues/92752> | | | | | | | | |

Configure the write format:

```
spark.conf.set("spark.clickhouse.write.format", "json")  // Recommended for Variant types
```

Tip

If you need to write to a ClickHouse `Variant` type, use JSON format. Arrow format only supports writing to `JSON` type.

### Best Practices[​](#varianttype-best-practices "Direct link to Best Practices")

1. **Use JSON type for JSON-only data**: If you only store JSON objects, use the default JSON type (no `variant_types` property)
2. **Specify types explicitly**: When using `Variant()`, explicitly list all types you plan to store
3. **Enable experimental features**: Ensure ClickHouse has `allow_experimental_json_type = 1` enabled
4. **Use JSON format for writes**: JSON format is recommended for VariantType data for better compatibility
5. **Consider query patterns**: JSON/Variant types support ClickHouse's JSON path queries for efficient filtering
6. **Column hints for performance**: When using JSON fields in ClickHouse, adding column hints improves query performance. Currently, adding column hints via Spark isn't supported. See [GitHub issue #497](https://github.com/ClickHouse/spark-clickhouse-connector/issues/497) for tracking this feature.

### Example: Complete Workflow[​](#varianttype-example-workflow "Direct link to Example: Complete Workflow")

- Scala- Python- Java

```
import org.apache.spark.sql.functions._

// Enable experimental JSON type in ClickHouse
spark.sql("SET allow_experimental_json_type = 1")

// Create table with Variant column
spark.sql("""
  CREATE TABLE clickhouse.default.events (
    event_id BIGINT,
    event_time TIMESTAMP,
    event_data VARIANT
  ) USING clickhouse
  TBLPROPERTIES (
    'clickhouse.column.event_data.variant_types' = 'String, Int64, Bool, JSON',
    'engine' = 'MergeTree()',
    'order_by' = 'event_time'
  )
""")