The T+1 full data snapshot feature of Data Management (DMS) allows you to create snapshots for specified tables every hour or day on a T+1 basis. This way, you can view the statistics on data by hour, day, or month. This topic describes how to create snapshots of full data on a T+1 basis by submitting a ticket.

Prerequisites

  • A destination database where snapshots are stored is available.
    • Database type: AnalyticDB for MySQL.
    • The database instance to which the destination database belongs must be managed in Security Collaboration mode. For more information about the Security Collaboration mode, see View the control mode of an instance.
  • A source database for which the snapshots are created is available.
    • Database type: ApsaraDB RDS for MySQL, PolarDB for MySQL, or a logical database that consists of multiple PolarDB for MySQL database shards.
      Note To use a logical database, you must configure a logical database and the required logical tables first. For more information, see Logical database and Logical table.
    • The source database must be managed in Security Collaboration mode. For more information about the Security Collaboration mode, see View the control mode of an instance.
  • You log on to the DMS console by using an Alibaba Cloud account or as a RAM user to which the AliyunDTSFullAccess policy is attached.
    Note If you do not attach the AliyunDTSFullAccess policy to the RAM user, a dialog box appears to inform you that you do not have the required permissions. For more information about how to attach the AliyunDTSFullAccess policy to a RAM user by using an Alibaba Cloud account or as a RAM user with the AdministratorAccess policy, see Grant permissions to the RAM user.

Background information

The snapshot feature of traditional storage services backs up full data of the database or some tables at a specified point in time. If the amount of data is large, you cannot obtain the snapshots created at a specified point in time. In addition, your online database may be under pressure.

DMS parses database logs in real time to obtain incremental data of your database based on the real-time data synchronization feature of Data Transmission Service (DTS). This rarely affects data in the production environment. DMS stores the incremental data in history tables and schedules tasks to create snapshots of full data and store the snapshots to an AnalyticDB for MySQL database based on your configurations. Snapshots are partitioned by hour or day. Each partition contains historical full data.

Note History tables provided by DMS not only store incremental data, but also record data changes of your database in real time. This allows you to query snapshots that are created at an arbitrary point in time as needed.

Scenarios

The T+1 full data snapshot feature is commonly used to store your business data to data warehouses. This feature allows you to synchronize full data on an hourly or daily basis. This way, you can view the statistics on data by hour, day, or month. You can use this feature in the following scenarios:

  • Record the daily account balance for bill queries and account reconciliation in an accounting system.
  • Record the daily price of a product to check whether the product is sold at the lowest ever price or whether the product is suitable for promotion.
  • Collect statistics and calculate the total amount of orders on the previous day to obtain up-to-date information about business operations.

Limits

If the table schema of the source database changes, the data synchronization channel may become unavailable. Therefore, you can create only columns in the tables of the source database.

Important If you use DDL statements to create or delete a table, clear data from a table, rename a table, delete a column, rename a column, or change column data, the data synchronization channel is interrupted. In this case, you must resolve the issue in Operation Center.

Billing rule

If you use DTS for data synchronization, you are charged by DTS. In the example of this topic, a pay-as-you-go DTS instance whose specification is small is used. For more information about the billing rules, see Billing overview.
Important If you synchronize data to AnalyticDB for MySQL and ApsaraDB RDS for PostgreSQL databases, DTS allows you to use pay-as-you-go instances free of charge for two years. You can purchase up to 10 DTS instances and one DTS instance at a time.
  • You can use only the DTS instance whose specification is micro or small for data synchronization for free. If you change the specification of the instance during data synchronization, you are charged by DTS.
  • If you have purchased 10 DTS instances and released them later, you are charged by DTS when you purchase instances again.

Procedure

  1. Log on to the DMS console V5.0.
  2. In the top navigation bar, choose Solution > T+1 Full Data Snapshot.
  3. In the upper-right corner of the page, click T+1 Full Data Snapshot.
  4. On the T + 1 Full Snapshot Ticket page, set the parameters that are described in the following table.
    ParameterDescription
    Ticket NameThe name of the ticket.
    Snapshot EngineThe destination database used to store the snapshots.
    Snapshot Data SourceThe source database for which the snapshots are created.
    Snapshot Table SettingsThe tables for which you want to create snapshots. The following steps describe how to add one or more tables for which you want to create snapshots:
    1. In the left-side section, select one or more tables for which you want to create snapshots.
    2. Click the Add icon icon to add the selected tables to the right-side section.
    3. In the Snapshot Granularity column, specify the scheduling cycle for creating snapshots. Valid values:
      • Hours: A snapshot is created every hour.
      • Day: A snapshot is created every day.
    4. Select a field of the date and time type from the Time Field drop-down list. The data type of the field must be DATETIME, TIMESTAMP, or DATE.
      Note The system creates snapshots based on the time indicated by the specified time field rather than the system time.
    Name of Hourly Snapshot TableIf you specify that a snapshot is created every hour, you must set the Prefix and Suffix parameters. By default, the value of the Prefix parameter is ods_ and that of the Suffix parameter is _dltahh.
    Name of Daily Snapshot TableIf you specify that a snapshot is created every day, you must set the Prefix and Suffix parameters. By default, the value of the Prefix parameter is ods_ and that of the Suffix parameter is _dlta.
    Full Snapshot RetentionSpecifies whether to retain all snapshots. Valid values:
    • Yes: retains all snapshots. By default, each snapshot has 10,240 partitions and can be retained for up to 426 days.
    • No: does not retain all snapshots. You can specify the number of partitions for each snapshot based on the following formulas: Number of partitions for an hourly created snapshot = Number of retention days × 24. Number of partitions for a daily created snapshot = Number of retention days.
  5. Click Submit application.
  6. Purchase a DTS instance for data synchronization
    1. Confirm the order information. Read the Data Transmission Service (Pay-As-You-Go) Service Terms, select the checkbox,
    2. and then click Buy and Start.
    3. In the message that appears, click OK.
    After the ticket is approved, snapshot tasks are run based on your configurations. The following list describes the execution cycle of the snapshot tasks:
    • A daily snapshot task starts from 01:00 to 01:10 every day. For example, a snapshot task creates a partition in the destination database and synchronizes all data generated before 00:00 on December 13, 2021, to this partition.
    • An hourly snapshot task starts within the first 5 minutes of each hour. For example, a snapshot task creates a partition in the destination database and synchronizes all data generated before 16:00:00 on December 13, 2021, to this partition.
  7. Optional: View snapshots in the destination database.
    You can view the snapshots on the SQL Console page.

    In an hourly generated snapshot, if you want to query all data generated before 16:00:00 on December 13, 2021, execute the following SQL statement:

    SELECT * FROM 'Prefix_tablename_Suffix' WHERE ds='2021-12-10 16:00:00';
    SQL Console page
    Note
    • You can search for a snapshot by the prefix and suffix of its name.
    • The partition key of a snapshot is the ds column. You can use the partition key to filter data and query the full data generated before a specified point in time.
  8. Optional: On the T+1 Full Data Snapshot page, find the ticket whose information you want to view and click Operation in the Actions column.
    • View the execution information of a snapshot task:
      • If you select Day for the Snapshot Granularity parameter in the ticket, you can view daily snapshot tasks on the Daily Tasks tab. You can also stop or rerun a task.
      • If you select Hours for the Snapshot Granularity parameter in the ticket, you can view hourly snapshot tasks on the Hourly Tasks tab. You can also stop or rerun a task.
    • View the status of the data synchronization link: Click Synchronization Link of Intermediate Table to go to the DTS page. On the page that appears, choose Data integration > Data Synchronization. On the Data Synchronization page, you can view and manage the data synchronization channel.