All Products
Search

This Product

    Data Management:Create data warehouses

    Document Center

    Data Management:Create data warehouses

    Last Updated:Mar 14, 2024

    The one-click data warehousing feature of Data Management (DMS) allows you to create a data warehouse for real-time data synchronization with ease. You can synchronize data to an AnalyticDB for MySQL database within seconds. This helps you obtain accurate and up-to-date information about your business and facilitates business analysis and decision-making. This way, the performance of the business is improved. This topic describes how to use the one-click data warehousing feature in DMS.

    Background information

    A conventional practice of data warehouse creation is to use offline SQL scripts to periodically collect data from a database to a data warehouse. Then, the extract, transform, and load (ETL) process is implemented. This method is time-consuming and requires heavy investment in technology. You must wait until the next day (T+1) to obtain a data analysis report.

    The one-click data warehousing feature of DMS combines the synchronization channels of Data Transmission Service (DTS) and the database analysis engine of AnalyticDB for MySQL. You can use this feature to create a data warehouse for real-time data synchronization with ease.

    Note

    • Data Transmission Service (DTS) provides high channel stability and data reliability. For more information, see What is DTS?.

    • AnalyticDB for MySQL is a cloud-based data warehouse for massively parallel processing (MPP). You can use AnalyticDB for MySQL to process petabytes of data in an easy and flexible way, and perform highly concurrent write operations. For more information, see What is AnalyticDB for MySQL?

    Billing

    • AnalyticDB for MySQL: If you purchase an AnalyticDB for MySQL instance, you are charged for the instance. For more information, see Billing methods and prices of AnalyticDB for MySQL.

      Note

      By default, an AnalyticDB for MySQL V3.0 instance that uses the subscription billing method is used, and the subscription duration is three months.

    • DTS: You are charged for data synchronization. For more information, see Pricing for DTS.

    Prerequisites

    • An ApsaraDB RDS for MySQL instance is purchased by using your Alibaba Cloud account. An ApsaraDB RDS for MySQL database is created in the instance as the source database.

    • If you need to synchronize data to an existing AnalyticDB for MySQL V3.0 instance, register the instance in DMS and log on to the instance. For more information, see Register an Alibaba Cloud database instance.

    • You are a DMS administrator, database administrator (DBA), or regular user.

      Note

      If you are a regular user, you must have the export permissions on the ApsaraDB RDS for MySQL database and the change permissions on an existing AnalyticDB for MySQL V3.0 database. You do not need to obtain the change permissions on a database in a new AnalyticDB for MySQL V3.0 instance. For more information about permissions, see Permission management.

    Procedure

    1. Log on to the DMS console V5.0.

    2. In the top navigation bar, choose Solution > One-Click Data Warehouse Building.

    3. In the upper-right corner of the page, click One-Click Data Warehouse Building.

    4. On the One-Click Data Warehouse Building Tickets page, specify the Data Source and Analytics Engine parameters.

      Parameter

      Description

      Data Source

      The database that you want to analyze.

      Analytics Engine

      The AnalyticDB for MySQL instance that you want to use for data analysis. Valid values:

      • Purchase AnalyticDB Instance (8 Cores and 32 GB of Memory): Select this value if you want to purchase a new AnalyticDB for MySQL instance. By default, an AnalyticDB for MySQL V3.0 instance that uses the subscription billing method is used, and the subscription duration is three months. If you select this value, you must specify the following parameters:

        • Region: the region where the AnalyticDB for MySQL instance resides. The region cannot be changed after the instance is created. To maximize the access speed, we recommend that you select a region in close proximity to the geographic location of your business.

        • Zone: the zone where the AnalyticDB for MySQL instance resides. Each zone is an independent physical location within a region. Zones in the same region are similar.

        • VPC: the virtual private cloud (VPC) to which the AnalyticDB for MySQL instance belongs. A VPC is an isolated network that provides higher security than classic networks. For more information, see Default VPC and default vSwitch.

        • vSwitch: the vSwitch of the VPC.

        Note

        After you purchase an AnalyticDB for MySQL instance, the database instance is automatically registered in DMS. In addition, a default account and password are created for you to log on to the database instance in DMS. To view the information about the database instance or reset the password, go to the AnalyticDB for MySQL console.

      • Select Existing AnalyticDB Instance: Select this value if you want to use an existing AnalyticDB for MySQL V3.0 instance. If you select this value, you must select the instance that you want to use from the drop-down list.

    5. Optional:Set the parameters in the Advanced Settings section.

      Parameter

      Description

      Destination Database Name

      The name of the destination database. Valid values:

      • Automatically Create Database with the Same Name: creates a database that has the same name as the source database in the destination database instance. This is the default value.

      • Rename Database: creates a database that has the specified name. If you select this value, you must specify the name of the destination database.

      Synchronization Mode

      The method to be used to synchronize data. You can use multiple methods at the same time. Valid values:

      • Initial Schema Synchronization: synchronizes table schemas in the source database to the destination database. If the name of a table in the destination database is the same as that in the source database, an error message is returned. This is selected by default.

      • Full Data: synchronizes all of the data in the source database to the destination database. This is also selected by default.

      • Incremental Data: synchronizes the incremental data in the source database to the destination database. This option is selected and cannot be cleared.

      Synchronization Range

      Specifies the tables to be synchronized from the source database to the destination database. Valid values:

      • Full Database: synchronizes all of the tables in the source database to the destination database. This is the default value.

      • Specific Tables: synchronizes one or more specified tables to the destination database.

    6. Click Submit.

      DMS checks whether you have required permissions on the databases.

    7. Click Submit for Approval.

      After the ticket is approved, DMS runs the synchronization task based on the configurations. You can view the progress of the synchronization task in the Data Synchronization step.

    What to do next

    You can perform the following operations in the Data Analysis step on the details tab of the one-click data warehouse building ticket:

    • Click SQL Console to analyze data in the data warehouse in real time. For more information, see Overview.