How to split horizontally table on multiple zones?

Question

I want to split data on multiple tiKV because I have Swiss, Europeans and Americans and I need to store data in citizen country.

The user’s table has a country code and automatically data are stored in a good zone (tikv --label zone=ch/eu/us).

How can I do this ?

Answer 1

As this is a regulatory requirement, you can specify Placement Rules in SQL for the affected table, together with partitioning. See Placement Rules in SQL.

Example placement rules:

CREATE PLACEMENT POLICY p1 FOLLOWERS=5;
CREATE PLACEMENT POLICY europe PRIMARY_REGION="eu-central-1" REGIONS="eu-central-1,eu-west-1";
CREATE PLACEMENT POLICY northamerica PRIMARY_REGION="us-east-1" REGIONS="us-east-1";

Example table:

SET tidb_enable_list_partition = 1;
CREATE TABLE user (
  country VARCHAR(10) NOT NULL,
  userdata VARCHAR(100) NOT NULL
) PLACEMENT POLICY=p1 PARTITION BY LIST COLUMNS (country) (
  PARTITION pEurope VALUES IN ('DE', 'FR', 'GB') PLACEMENT POLICY=europe,
  PARTITION pNorthAmerica VALUES IN ('US', 'CA', 'MX') PLACEMENT POLICY=northamerica,
  PARTITION pAsia VALUES IN ('CN', 'KR', 'JP')
);

The pEurope partition will apply the europe policy.