Data Management:Query archived data in OSS or dedicated storage

Billing

This feature is free during the public preview.

Limitations

• Only the creator or stakeholders of a data archiving ticket can query the archived data.

• To query data archived to Object Storage Service (OSS) or a logical data warehouse, you must use a logical data warehouse.

Query data archived in OSS

1. Log in to DMS 5.0.

2. In the top navigation bar, choose Solution > Data Archiving.

   Note

   If you use the DMS console in simple mode, move the pointer over the icon in the upper-left corner of the DMS console and choose All Features > Solution > Data Archiving.

3. On the Data Archiving Ticket List page, set View to Archive View.

4. Find the target data archiving ticket by entering its number, database name, or table name in the search box and clicking Search.

5. In the Operation column for the target ticket, click Query.

   Important

   If this is your first time querying data from an Object Storage Service (OSS) instance, you must log on using the AccessKey ID and AccessKey secret for that instance. For more information, see Create an AccessKey pair.

6. Optional: Enter the database account and database password, and then click OK.

   Important

   Ensure that the entered database account and database password correspond to the AccessKey ID and AccessKey secret of the OSS instance. Otherwise, the query fails.

7. The system automatically redirects you to the logical data warehouse page and populates the command execution area with the SQL query.

   Note

   If you are redirected to a blank page, you may not have the required permissions for the logical data warehouse. To request permissions, use DingTalk to search for and join the group with ID 24005004020.

8. Click Execute. The query results appear in the results pane at the bottom of the page.

   Important

   By default, the system returns a maximum of 20 rows. You can modify the LIMIT clause in the SQL statement to return more data based on your business needs.

Query data archived in dedicated storage

1. Log in to DMS 5.0.

2. In the top navigation bar, choose Solution > Data archiving.

3. On the Data Archiving Ticket List page, set View to Archive View.

4. Find the target data archiving ticket by entering its number, database name, or table name in the search box and clicking Search.

5. In the Operation column for the target ticket, click Query.

6. The system automatically redirects you to the logical data warehouse page and populates the command execution area with the SQL query.

   Note

   If you are redirected to a blank page, you may not have the required permissions for the logical data warehouse. To request permissions, use DingTalk to search for and join the group with ID 24005004020.

7. Click Execute. The query results appear in the results pane at the bottom of the page.

   Note

   By default, the system returns a maximum of 20 rows. You can modify the LIMIT clause in the SQL statement to return more data based on your business needs.

FAQ

Q: When I query archived data from a logical database, are all tables from the logical database merged into a single physical table?

A: Yes. When data from a logical database is archived, all its tables are merged into a single table in a physical database. As a result, on the Archive View page, you can query any of the logical tables to retrieve all data from the logical database.

Q: Can I perform a join query on data archived under different tickets?

A: Yes. The logical data warehouse in Data Management Service (DMS) supports join queries on archived data, regardless of whether the data resides in the same or different Object Storage Service (OSS) instances. To perform a join query, go to the logical data warehouse page, log on to the relevant OSS instances, and use JOIN statements such as LEFT JOIN, INNER JOIN, or RIGHT JOIN.

Q: If a single ticket is used for multiple data archiving runs (periodic archiving), can I query data from a specific time period?

A: Yes. For a ticket with periodic archiving, each run creates a distinct, time-stamped data archive. You can then query a specific archive based on its creation time.