[Feature] Support specifying partition time zone for data ingestion
- 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
```

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