apache / apache/paimon

[Feature] Support specifying partition time zone for data ingestion

Open
#1,143 1 comment 0 reactions 0 assignees View on GitHub
enhancement
Dominant language
Java
Stars
3.4k
Forks
1.4k
Avg merge
1d 11h
Merged PRs (30d)
396

Description

### Search before asking

- [X] I searched in the [issues](https://github.com/apache/incubator-paimon/issues) and found nothing similar.

### Motivation

In Paimon, the display value of timestamp is affected by timezone, we store 2023-03-23T15:00:00 in UTC+8 timezone and query this timestamp in UTC+5, and the display value should increase by 3 hour, which is 2023-03-23T18:00:00. This may cause problem when we specify a partition for the table and enable parititon pruning (for a feature similar to [HUDI-5880](https://issues.apache.org/jira/browse/HUDI-5880)).

For example. Suppose we have the following MySQL table definition:

```sql
CREATE TABLE partitioned_table (
a INT primary key,
dt DATETIME,
ts TIMESTAMP
)
```

and run the ingestion as follows:

```sql

// create mysql table and paimon catalog

CREATE TABLE ts_table (...) PARTITIONED BY (ts) WITH (
'partition.timestamp-formatter' = 'yyyyMMddHH'
);

INSERT INTO ts_table select * from partitioned_table;
```

Suppose the server timezone of MySQL is UTC+8, and the system timezone of query is UTC+5, then
a record like +I[1, "2023-05-12T23:00:00", "2023-05-12T23:00:00"] corresponds to partition `2023051223`. The result of query should be +I[1, "2023-05-13T02:00:00", "2023-05-13T02:00:00"] due to the difference in time zone.

However, +I[1, "2023-05-12T23:00:00", "2023-05-12T23:00:00"] should fall into the partition `2023051302` so that it will show up when querying `SELECT * FROM ts_table WHERE partition = '2023051302'`.

### Solution

I propose we should add time zone information for partitions, **and** if a computed column is of temporal type we also need to include the target partition time zone information during `eval` as in the implementation of #1109

### Anything else?

Here's an example of why partition time zone need to be taken into account during eval of computed column:

```bash
/bin/flink run \
-c org.apache.paimon.flink.action.FlinkActions \
/path/to/paimon-flink-**-{{< version >}}.jar \
mysql-sync-table \
--warehouse hdfs:///path/to/warehouse \
--database test_db \
--table test_table \
--partition-keys pt \
--primary-keys pt,uid \
--computed-columns '_year=year(age)' \
--mysql-conf hostname=127.0.0.1 \
--mysql-conf username=root \
--mysql-conf password=123456 \
--mysql-conf database-name=source_db \
--mysql-conf table-name='source_table_.*' \
--catalog-conf metastore=hive \
--catalog-conf uri=thrift://hive-metastore:9083 \
--table-conf bucket=4 \
--table-conf changelog-producer=input \
--table-conf sink.parallelism=4
```
![image](https://github.com/apache/incubator-paimon/assets/20527912/936e4d04-a1c3-4e3b-8254-f51255113aa7)

If local timezone is UTC+8 but the query time zone is UTC+5, then for a record with age=2022-12-31 22:00:00, year(age)=2022 which falls into partition `2022`, but the query result gives `2023-01-01 01:00:00` which falls into partition `2023`.

### Are you willing to submit a PR?

- [X] I'm willing to submit a PR!

Contributor guide

No contributing guide indexed for this repository

Research direction

Start with the FlinkActions mysql-sync-table entry point and trace how partition keys and computed columns are evaluated during ingestion. Compare the partition timezone behavior described in the MySQL examples with the query results, and consider the existing implementation referenced by issue #1109. Done means temporal partitions and temporal computed columns use the configured target timezone consistently.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, mysql, sql
Domain
data-engineering, databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
28/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.