github.com/pingcap/tiflow@v0.0.0-20240520035814-5bf52d54e205/docs/design/2023-03-15-ticdc-storage-sink.md (about) 1 # TiCDC Design Documents 2 3 - Author(s): [CharlesCheung](https://github.com/CharlesCheung96), [zhaoxinyu](https://github.com/zhaoxinyu) 4 - Tracking Issue: https://github.com/pingcap/tiflow/issues/6797 5 6 ## Table of Contents 7 8 - [Introduction](#introduction) 9 - [Motivation or Background](#motivation-or-background) 10 - [Detailed Design](#detailed-design) 11 - [Storage path structure](#storage-path-structure) 12 - [Data change records](#data-change-records) 13 - [Index files](#index-files) 14 - [Metadata](#metadata) 15 - [DDL events](#ddl-events) 16 - [Data type in schema](#data-type-in-schema) 17 - [Integer types](#integer-types) 18 - [Decimal types](#decimal-types) 19 - [Date and time types](#date-and-time-types) 20 - [String types](#string-types) 21 - [Enum and Set types](#enum-and-set-types) 22 - [Protocols](#protocols) 23 - [CSV](#csv) 24 - [Canal json](#canal-json) 25 - [Test Design](#test-design) 26 - [Functional Tests](#functional-tests) 27 - [Scenario Tests](#scenario-tests) 28 - [Compatibility Tests](#compatibility-tests) 29 - [Benchmark Tests](#benchmark-tests) 30 - [Impacts & Risks](#impacts--risks) 31 - [Investigation & Alternatives](#investigation--alternatives) 32 - [Unresolved Questions](#unresolved-questions) 33 34 ## Introduction 35 36 This document provides a complete design on implementing storage sink, which provides 37 the ability to output changelogs to NFS, Amazon S3, GCP and Azure Blob Storage. 38 39 ## Motivation or Background 40 41 External storage services, such as Amazon S3, GCP and Azure Blob Storage, are designed 42 to handle large volumes of data and provide high availability and durability. By 43 leveraging such services, TiCDC can provide a scalable and cost-effective way to 44 store and manage TiDB's incremental changelogs, and enable users to build flexible 45 end-to-end data integration pipelines that can support a wide range of use cases 46 and scenarios. 47 48 ## Detailed Design 49 50 ### Storage path structure 51 52 This section describes the storage path structure of data change records, metadata, and DDL events. 53 Using the csv protocol as an example, files containing row change events should be organized as follows: 54 55 ``` 56 s3://bucket/prefix1/prefix2 <prefix> 57 ├── metadata 58 └── schema1 <schema> 59 ├── meta 60 │ └── schema_441349361156227000_3233644819.json [database schema file] 61 └── table1 <table> 62 │── meta 63 │ └── schema_441349361156227074_3131721815.json [table schema file] 64 └── 441349361156227074 <table-version-separator> 65 └── 13 <partition-separator, optional> 66 ├── 2023-05-09 <date-separator, optional> 67 │ ├── CDC00000000000000000001.csv 68 │ └── meta 69 │ └── CDC.index 70 └── 2023-05-10 <date-separator, optional> 71 ├── CDC00000000000000000001.csv 72 └── meta 73 └── CDC.index 74 ``` 75 76 #### Data change records 77 78 Data change records are saved to the following path: 79 80 ```shell 81 {scheme}://{prefix}/{schema}/{table}/{table-version-separator}/{partition-separator}/{date-separator}/CDC{num}.{extension} 82 ``` 83 84 - `scheme`: specifies the data transmission protocol, or the storage type, for example, <code>**s3**://xxxxx</code>. 85 - `prefix`: specifies the user-defined parent directory, for example, <code>s3://**bucket/prefix1/prefix2**</code>. 86 - `schema`: specifies the schema name, for example, <code>s3://bucket/prefix1/prefix2/**schema1**</code>. 87 - `table`: specifies the table name, for example, <code>s3://bucket/prefix1/prefix2/schema1/**table1**</code>. 88 - `table-version-separator`: specifies the separator that separates the path by the table version, for example, <code>s3://bucket/prefix1/prefix2/schema1/table1/**441349361156227074**</code>. 89 - `partition-separator`: specifies the separator that separates the path by the table partition, for example, <code>s3://bucket/prefix1/prefix2/schema1/table1/441349361156227074/**13**</code>. 90 - `date-separator`: classifies the files by the transaction commit date. Value options are: 91 - `none`: no `date-separator`. For example, all files with `test.table1` version being `441349361156227074` are saved to `s3://bucket/prefix1/prefix2/schema1/table1/441349361156227074`. 92 - `year`: the separator is the year of the transaction commit date, for example, <code>s3://bucket/prefix1/prefix2/schema1/table1/441349361156227074/**2022**</code>. 93 - `month`: the separator is the year and month of the transaction commit date, for example, <code>s3://bucket/prefix1/prefix2/schema1/table1/441349361156227074/**2022-05**</code>. 94 - `day`: the separator is the year, month, and day of the transaction commit date, for example, <code>s3://bucket/prefix1/prefix2/schema1/table1/441349361156227074/**2022-05-09**</code>. 95 - `num`: saves the serial number of the file that records the data change, for example, <code>s3://bucket/prefix1/prefix2/schema1/table1/441349361156227074/2022-01-02/CDC**000001**.csv</code>. 96 - `extension`: specifies the extension of the file. TiDB v6.5.0 supports the CSV and Canal-JSON formats. 97 98 > **Note:** 99 > 100 > The table version changes only after a DDL operation is performed, the table version is the TSO when the DDL is executed in the upstream TiDB. However, the change of the table version does not mean the change of the table schema. For example, adding a comment to a column does not cause the schema file content to change. 101 102 #### Index files 103 104 An index file is used to prevent written data from being overwritten by mistake. It is stored in the same path as the data change record. 105 106 ```shell 107 {scheme}://{prefix}/{schema}/{table}/{table-version-separator}/{partition-separator}/{date-separator}/meta/CDC.index 108 ``` 109 110 An index file records the largest file name used in the current directory. For example: 111 112 ``` 113 CDC000005.csv 114 ``` 115 116 In this example, the files CDC000001.csv through CDC000004.csv in this directory are occupied. When a table scheduling or node restart occurs in the TiCDC cluster, the new node reads the index file and determines if CDC000005.csv is occupied. If it is not occupied, the new node writes the file starting from CDC000005.csv. If it is occupied, it starts writing from CDC000006.csv, which prevents overwriting data written by other nodes. 117 118 #### Metadata 119 120 Metadata is saved in the following path: 121 122 ```shell 123 {protocol}://{prefix}/metadata 124 ``` 125 126 Metadata is a JSON-formatted file, for example: 127 128 ```json 129 { 130 "checkpoint-ts": 433305438660591626 131 } 132 ``` 133 134 - `checkpoint-ts`: Transactions with `commit-ts` smaller than `checkpoint-ts` are written to the target storage in the downstream. 135 136 #### DDL events 137 138 ##### Table DDL events 139 140 When DDL events cause the table version to change, TiCDC switches to a new path to write data change records. For example, when the version of `test.table1` changes to `441349361156227074`, data will be written to the path `s3://bucket/prefix1/prefix2/schema1/table1/441349361156227074/2022-01-02/CDC000001.csv`. In addition, when DDL events occur, TiCDC generates a file to save the table schema information. 141 142 Table schema information is saved in the following path: 143 144 ```shell 145 {scheme}://{prefix}/{schema}/{table}/meta/schema_{table-version}_{hash}.json 146 ``` 147 148 The following is a table schema file named `schema_441349361156227074_3131721815.json`: 149 150 ```json 151 { 152 "Table": "table1", 153 "Schema": "schema1", 154 "Version": 1, 155 "TableVersion": 441349361156227074, 156 "Query": "ALTER TABLE schema1.table1 ADD OfficeLocation blob(20)", 157 "Type": 5, 158 "TableColumns": [ 159 { 160 "ColumnName": "Id", 161 "ColumnType": "INT", 162 "ColumnNullable": "false", 163 "ColumnIsPk": "true" 164 }, 165 { 166 "ColumnName": "LastName", 167 "ColumnType": "CHAR", 168 "ColumnLength": "20" 169 }, 170 { 171 "ColumnName": "FirstName", 172 "ColumnType": "VARCHAR", 173 "ColumnLength": "30" 174 }, 175 { 176 "ColumnName": "HireDate", 177 "ColumnType": "DATETIME" 178 }, 179 { 180 "ColumnName": "OfficeLocation", 181 "ColumnType": "BLOB", 182 "ColumnLength": "20" 183 } 184 ], 185 "TableColumnsTotal": "5" 186 } 187 ``` 188 189 - `Table`: Table name. 190 - `Schema`: Schema name. 191 - `Version`: Protocol version of the storage sink. 192 - `TableVersion`: Table version. 193 - `Query`：DDL statement. 194 - `Type`: Type of DDL event. 195 - `TableColumns`: An array of one or more maps, each of which describes a column in the source table. 196 - `ColumnName`: Column name. 197 - `ColumnType`: Column type. For details, see [Data type](#data-type). 198 - `ColumnLength`: Column length. For details, see [Data type](#data-type). 199 - `ColumnPrecision`: Column precision. For details, see [Data type](#data-type). 200 - `ColumnScale`: The number of digits following the decimal point (the scale). For details, see [Data type](#data-type). 201 - `ColumnNullable`: The column can be NULL when the value of this option is `true`. 202 - `ColumnIsPk`: The column is part of the primary key when the value of this option is `true`. 203 - `TableColumnsTotal`: The size of the `TableColumns` array. 204 205 ##### Schema DDL events 206 207 Database schema information is saved in the following path: 208 209 ```shell 210 {scheme}://{prefix}/{schema}/meta/schema_{table-version}_{hash}.json 211 ``` 212 213 The following is a database schema file named `schema_441349361156227000_3131721815.json`: 214 215 ```json 216 { 217 "Table": "", 218 "Schema": "schema1", 219 "Version": 1, 220 "TableVersion": 441349361156227000, 221 "Query": "CREATE DATABASE `schema1`", 222 "Type": 1, 223 "TableColumns": null, 224 "TableColumnsTotal": 0 225 } 226 ``` 227 228 ### Data type in schema 229 230 This section describes the data types used in the schema file. The data types are defined as `T(M[, D])`. 231 232 #### Integer types 233 234 Integer types in TiDB are defined as `IT[(M)] [UNSIGNED]`, where 235 236 - `IT` is the integer type, which can be `TINYINT`, `SMALLINT`, `MEDIUMINT`, `INT`, `BIGINT`, or `BIT`. 237 - `M` is the display width of the type. 238 239 Integer types are defined as follows in schema: 240 241 ```json 242 { 243 "ColumnName": "COL1", 244 "ColumnType": "{IT} [UNSIGNED]", 245 "ColumnPrecision": "{M}" 246 } 247 ``` 248 249 #### Decimal types 250 251 Decimal types in TiDB are defined as `DT[(M,D)][UNSIGNED]`, where 252 253 - `DT` is the floating-point type, which can be `FLOAT`, `DOUBLE`, `DECIMAL`, or `NUMERIC`. 254 - `M` is the precision of the data type, or the total number of digits. 255 - `D` is the number of digits following the decimal point. 256 257 Decimal types are defined as follows in schema file: 258 259 ```json 260 { 261 "ColumnName": "COL1", 262 "ColumnType": "{DT} [UNSIGNED]", 263 "ColumnPrecision": "{M}", 264 "ColumnScale": "{D}" 265 } 266 ``` 267 268 #### Date and time types 269 270 Date types in TiDB are defined as `DT`, where 271 272 - `DT` is the date type, which can be `DATE` or `YEAR`. 273 274 The date types are defined as follows in schema file: 275 276 ```json 277 { 278 "ColumnName": "COL1", 279 "ColumnType": "{DT}" 280 } 281 ``` 282 283 The time types in TiDB are defined as `TT[(M)]`, where 284 285 - `TT` is the time type, which can be `TIME`, `DATETIME`, or `TIMESTAMP`. 286 - `M` is the precision of seconds in the range from 0 to 6. 287 288 The time types are defined as follows in schema file: 289 290 ```json 291 { 292 "ColumnName": "COL1", 293 "ColumnType": "{TT}", 294 "ColumnScale": "{M}" 295 } 296 ``` 297 298 #### String types 299 300 The string types in TiDB are defined as `ST[(M)]`, where 301 302 - `ST` is the string type, which can be `CHAR`, `VARCHAR`, `TEXT`, `BINARY`, `BLOB`, or `JSON`. 303 - `M` is the maximum length of the string. 304 305 The string types are defined as follows in schema file: 306 307 ```json 308 { 309 "ColumnName": "COL1", 310 "ColumnType": "{ST}", 311 "ColumnLength": "{M}" 312 } 313 ``` 314 315 #### Enum and Set types 316 317 The Enum and Set types are defined as follows in schema file: 318 319 ```json 320 { 321 "ColumnName": "COL1", 322 "ColumnType": "{ENUM/SET}" 323 } 324 ``` 325 326 ### Protocols 327 328 #### CSV 329 330 ##### Transactional constraints 331 332 - In a single CSV file, the commit-ts of a row is equal to or smaller than that of the subsequent row. 333 - The same transactions of a single table are stored in the same CSV file when `transaction-atomicity` is set to table level. 334 - Multiple tables of the same transaction can be stored in different CSV files. 335 336 ##### Definition of the data format 337 338 In the CSV file, each column is defined as follows: 339 340 - Column 1: The operation-type indicator, including `I`, `U`, and `D`. `I` means `INSERT`, `U` means `UPDATE`, and `D` means `DELETE`. 341 - Column 2: Table name. 342 - Column 3: Schema name. 343 - Column 4: The `commit-ts` of the source transaction. This column is optional. 344 - Column 5 to the last column: One or more columns that represent data to be changed. 345 346 Assume that table `hr.employee` is defined as follows: 347 348 ```sql 349 CREATE TABLE `employee` ( 350 `Id` int NOT NULL, 351 `LastName` varchar(20) DEFAULT NULL, 352 `FirstName` varchar(30) DEFAULT NULL, 353 `HireDate` date DEFAULT NULL, 354 `OfficeLocation` varchar(20) DEFAULT NULL 355 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 356 ``` 357 358 The DML events of this table are stored in the CSV format as follows: 359 360 ```shell 361 "I","employee","hr",433305438660591626,101,"Smith","Bob","2014-06-04","New York" 362 "U","employee","hr",433305438660591627,101,"Smith","Bob","2015-10-08","Los Angeles" 363 "D","employee","hr",433305438660591629,101,"Smith","Bob","2017-03-13","Dallas" 364 "I","employee","hr",433305438660591630,102,"Alex","Alice","2017-03-14","Shanghai" 365 "U","employee","hr",433305438660591630,102,"Alex","Alice","2018-06-15","Beijing" 366 ``` 367 368 ##### Data type mapping 369 370 | MySQL type | CSV type | Example | Description | 371 | ----------------------------------------------------------------- | -------- | ------------------------------ | ---------------------------------- | 372 | `BOOLEAN`/`TINYINT`/`SMALLINT`/`INT`/`MEDIUMINT`/`BIGINT` | Integer | `123` | - | 373 | `FLOAT`/`DOUBLE` | Float | `153.123` | - | 374 | `NULL` | Null | `\N` | - | 375 | `TIMESTAMP`/`DATETIME` | String | `"1973-12-30 15:30:00.123456"` | Format: `yyyy-MM-dd HH:mm:ss.%06d` | 376 | `DATE` | String | `"2000-01-01"` | Format: `yyyy-MM-dd` | 377 | `TIME` | String | `"23:59:59"` | Format: `yyyy-MM-dd` | 378 | `YEAR` | Integer | `1970` | - | 379 | `VARCHAR`/`JSON`/`TINYTEXT`/`MEDIUMTEXT`/`LONGTEXT`/`TEXT`/`CHAR` | String | `"test"` | UTF-8 encoded | 380 | `VARBINARY`/`TINYBLOB`/`MEDIUMBLOB`/`LONGBLOB`/`BLOB`/`BINARY` | String | `"6Zi/5pav"` | base64 encoded | 381 | `BIT` | Integer | `81` | - | 382 | `DECIMAL` | String | `"129012.1230000"` | - | 383 | `ENUM` | String | `"a"` | - | 384 | `SET` | String | `"a,b"` | - | 385 386 #### Canal json 387 388 Storage sink uses the same canal-json protocol as the mq sink. The [official documentation](https://docs.pingcap.com/tidb/dev/ticdc-canal-json/) shows how the Canal-JSON data format is implemented in TiCDC, including the TiDB extended fields, the definition of the Canal-JSON data format, and the comparison with the official Canal. 389 390 ## Test Design 391 392 Storage sink is a new feature, For tests, we focus on the functional tests, scenario tests and benchmark. 393 394 ### Functional Tests 395 396 - Regular unit testing and integration testing cover the correctness of data replication using csv and canal-json protocol. 397 - Manually test the availability and correctness of data synchronization using different external storage. 398 399 ### Scenario Tests 400 401 Run stability and chaos tests under different workloads. 402 403 - The upstream and downstream data are consistent. 404 - Throughput and latency are stable for most scenarios. 405 406 ### Compatibility Tests 407 408 #### Compatibility with other features/components 409 410 Should be compatible with other features. 411 412 #### Upgrade Downgrade Compatibility 413 414 Storage sink is a new feature, so there should be no upgrade 415 or downgrade compatibility issues. 416 417 ### Benchmark Tests 418 419 Perform benchmark tests under common scenarios, big data scenarios, multi-table scenarios, and wide table scenarios with different parameters. 420 421 ## Impacts & Risks 422 423 N/A 424 425 ## Investigation & Alternatives 426 427 N/A 428 429 ## Unresolved Questions 430 431 N/A