Specify the column data types when importing to the catalog

[aadant]

currently there is no way to override the default behavior

--schema-from-parquet

we would like to be able to specify the columns and data types.
Maybe it is possible that Parquet exported from CH has this information (or it can be added) but to import Parquet in general, we need to be able to specify columns (including calculated ones, or default values).

We can just start with the data types (or data type mapping).

A CH Date would translate into UInt16 and would become an Integer in Iceberg ... by default.
A CH DateTime would also translate to Integer.

[shyiko]

sidenote: We should probably stick to the same format as https://docs.aws.amazon.com/cli/latest/reference/s3tables/create-table.html

[subkanthi]

Suggestion from @aadant was to support column name overrides.

--column-type-mapping-override '[{"column_name":"db_time", "type": "DateTime64(\'America/Chicago\',6)"}]'

[hodgesrm]

Before we implement overrides it's helpful to investigate the current state of mapping and understand the goals they satisfy. Let's suppose we're dumping data from MergeTree into Iceberg. There are two obvious goals:

1. Column values in Iceberg should be valid (i.e., not coerced into incompatible types, such as UInt64 to int64).
2. Values in the different stores should be commensurate when selected in ClickHouse, even if the native column types are different.

I put together a Gist that illustrates this for datetime values. It defines values in MergeTree, writes the row(s) to Parquet, an imports to Iceberg using ice. See the Gist for full details.

Here's the original table definition.

CREATE TABLE IF NOT EXISTS datetime_examples
(
    `description` String,
    `date16` Date DEFAULT now(),
    `date32` Date32 DEFAULT now(),
    `datetime` DateTime DEFAULT now(),
    `datetime_tz_pt` DateTime('America/Los_Angeles') DEFAULT now(),
    `datetime_tz_utc` DateTime('UTC') DEFAULT now(),
    `datetime64` DateTime(0) DEFAULT now(),
    `datetime64_ms` DateTime(3) DEFAULT now(),
    `datetime64_mc` DateTime(6) DEFAULT now(),
    `datetime64_mc_pt` DateTime('America/Los_Angeles') DEFAULT now()
)
ENGINE = MergeTree
PARTITION BY date16
ORDER BY datetime

Sample values in MergeTree look like the following.

SELECT * FROM default.datetime_examples  ┌─description─┬─────date16─┬─────date32─┬────────────datetime─┬──────datetime_tz_pt─┬─────datetime_tz_utc─┬──────────datetime64─┬───────────datetime64_ms─┬──────────────datetime64_mc─┬────datetime64_mc_pt─┐
1. │ now()       │ 2025-08-27 │ 2025-08-27 │ 2025-08-27 04:47:40 │ 2025-08-26 21:47:40 │ 2025-08-27 04:47:40 │ 2025-08-27 04:47:40 │ 2025-08-27 04:47:40.000 │ 2025-08-27 04:47:40.000000 │ 2025-08-26 21:47:40 │
   └─────────────┴────────────┴────────────┴─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────────┴────────────────────────────┴─────────────────────┘

The same values loaded to Iceberg look like the following.

SELECT * FROM ice.`default.datetime_examples`
   ┌─description─┬─────date16─┬─────date32─┬───────────────────datetime─┬─────────────datetime_tz_pt─┬────────────datetime_tz_utc─┬─────────────────datetime64─┬──────────────datetime64_ms─┬──────────────datetime64_mc─┬───────────datetime64_mc_pt─┐
1. │ now()       │ 2025-08-27 │ 2025-08-27 │ 2025-08-27 04:47:40.000000 │ 2025-08-27 04:47:40.000000 │ 2025-08-27 04:47:40.000000 │ 2025-08-27 04:47:40.000000 │ 2025-08-27 04:47:40.000000 │ 2025-08-27 04:47:40.000000 │ 2025-08-27 04:47:40.000000 │
   └─────────────┴────────────┴────────────┴────────────────────────────┴────────────────────────────┴────────────────────────────┴────────────────────────────┴────────────────────────────┴────────────────────────────┴────────────────────────────┘

We can solve some of the differences using appropriate casts. Here's an example that adds back a missing time zone to Iceberg data.

SELECT
    'iceberg datetime_tz_pt',
    datetime_tz_pt AS raw,
    toTypeName(datetime_tz_pt) AS raw_type,
    CAST(datetime_tz_pt, 'DateTime(\'America/Los_Angeles\')') AS final
FROM ice.`default.datetime_examples`
UNION ALL
SELECT
    'mergetree datetime_tz_pt',
    datetime_tz_pt AS raw,
    toTypeName(datetime_tz_pt) AS raw_type,
    datetime_tz_pt AS final
FROM default.datetime_examples

Query id: 315a4f18-32e3-4536-8769-6f3f5b73aec6

   ┌─'iceberg datetime_tz_pt'─┬────────────────────────raw─┬─raw_type────────────────────────┬───────────────final─┐
1. │ mergetree datetime_tz_pt │ 2025-08-27 04:47:40.000000 │ DateTime('America/Los_Angeles') │ 2025-08-26 21:47:40 │
   └──────────────────────────┴────────────────────────────┴─────────────────────────────────┴─────────────────────┘
   ┌─'iceberg datetime_tz_pt'─┬────────────────────────raw─┬─raw_type─────────────┬───────────────final─┐
2. │ iceberg datetime_tz_pt   │ 2025-08-27 04:47:40.000000 │ DateTime64(6, 'UTC') │ 2025-08-26 21:47:40 │
   └──────────────────────────┴────────────────────────────┴──────────────────────┴─────────────────────┘

The results are a little bit surprising. ClickHouse correctly shows the data types from Iceberg and MergeTree columns but it coerces the MergeTree value to DateTime('UTC').

[hodgesrm]

Following up on the admittedly limited result above, it looks to me that there are a couple of options for handling schema overrides.

1. Add schema overrides that "remember" the native MergeTree type, as proposed in this issue.
2. Use the Infinity table engine to handle casts/mapping automatically. We could take the MergeTree table as defining the schema, then generate appropriate casts/transformations on values in the Iceberg query. that would prevent the result morphing shown above.

The second option seems simpler. It addresses another issue--if there's a schema change in MergeTree, the override values in Iceberg may now be obsolete or flat out wrong.

[aadant]

@hodgesrm this issue is unrelated to MergeTree. It has to exist to import Parquet files in general (they may exist outside CH completely), also so that the ice catalog can serve other engines than CH. Please note that it is only relevant for table creation. Schema changes are another topic.

[aadant]

still useful to override data types especially when smaller than the Iceberg standard

--column-type-mapping-override '[{"column_name":"id", "type": "decimal(20,0)"}]'

btw the below snippet does not make sense as Iceberg has no notion of timezone in its data types, also I think that the type should be in the Iceberg supported data types :

--column-type-mapping-override '[{"column_name":"db_time", "type": "DateTime64(\'America/Chicago\',6)"}]' 

[hodgesrm]

Ice definitely needs to map to proper MergeTree datatypes wherever possible. I think it would be good experiment to see how AWS Glue maps datatypes from Parquet. It has a crawler that will determine the schema and publish it in Iceberg format.