luozihen commented on issue #11878:
URL: https://github.com/apache/seatunnel/issues/11878#issuecomment-5366553524
Thanks! Glad we've reached a consensus on this issue. Below are two concrete
examples to illustrate the scenarios where this configuration takes effect, as
well as the fallback principle.
**Example 1**
Original table info:
| Table name | Primary key | Unique key |
|---|---|---|
| t_nova_fo_serial_000/048 | serial_id | - |
| t_nova_order_000/048 | id | - |
| t_tyuen_txn_cp_000/048 | id | id_txn_ctrl |
| t_tyuen_txn_qr_000/048 | id | id_txn_ctrl |
Expected behavior:
1. For the `t_nova_*` tables, use the original primary key plus
`DATA_SOURCE` as the composite primary key to distinguish the data sources.
2. For the `t_tyuen_txn_*` tables, use the unique key `id_txn_ctrl` plus
`DATA_SOURCE` as the composite primary key.
source:
```hcl
table-pattern =
"^tyuen\\.t_(?:(?:nova_fo_serial|nova_order|tyuen_txn_cp|tyuen_txn_qr))_.*$"
```
transform:
```hcl
Sql {
plugin_input = "tr1"
query = "SELECT *, 'idc' AS DATA_SOURCE FROM source_table"
plugin_output = "sharding00_sink"
}
```
sink:
```hcl
# In this example, all tables need an extra DATA_SOURCE field as part of the
composite primary key,
# so the original primary_keys option does not take effect here; leave it
unconfigured.
multi-table_config {
primary_keys {
"^t_nova_.*$" = ["${primary_key}", "DATA_SOURCE"]
"^t_tyuen_txn_.*$" = ["ID_TXN_CTRL", "DATA_SOURCE"]
}
}
```
Notes:
1. Since the tables are sharded, the new configuration needs to support
regular expressions to avoid a bloated config.
2. Here the `t_nova_*` tables directly use the original primary key plus
`DATA_SOURCE` as the composite primary key, while the `t_tyuen_txn_*` tables
use another unique key plus `DATA_SOURCE`.
3. In this example, the primary keys of all tables are re-specified.
**Example 2**
Original table info:
| Table name | Primary key | Unique key |
|---|---|---|
| t_nova_merchant_info | id | merchant_id |
| t_tyuen_txn_ext | id | id_txn_ctrl |
| t_nova_merge_settle_serial | id | - |
Expected behavior:
1. `t_tyuen_txn_ext` and `t_nova_merge_settle_serial` add `DATA_SOURCE` to
distinguish the data sources; `t_nova_merchant_info` stays single-source, so no
`DATA_SOURCE` is added.
2. For `t_nova_merchant_info` and `t_tyuen_txn_ext`, use another unique key
as the primary key; `t_tyuen_txn_ext` adds `DATA_SOURCE` as part of the
composite primary key.
3. `t_nova_merge_settle_serial` uses the original primary key plus
`DATA_SOURCE` as the composite primary key.
source:
```hcl
plugin_output = "gote_source"
table-pattern = "xxx"
```
transform:
```hcl
plugin_input = "gote_source"
query = "SELECT *, idc FROM source_table"
table_transform = [
{
table_path = "gote.t_nova_merchant_info"
query = "SELECT * FROM source_table"
}
]
plugin_output = "tr"
```
sink:
```hcl
plugin_input = "tr"
primary_keys = ["merchant_id"]
# t_nova_merchant_info is not matched by multi-table_config,
# so it falls back to the original primary_keys option.
multi-table_config {
primary_keys {
"t_tyuen_txn_ext*" = ["id_txn_ctrl", "DATA_SOURCE"]
"t_nova_merge_settle_serial" = ["${primary_key}", "DATA_SOURCE"]
}
}
```
I hope the two examples above demonstrate the necessity and feasibility of
the table-level primary key specification parameter. Please feel free to point
out anything unreasonable — all feedback is welcome.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
