If you want to query and analyze the data of an ApsaraDB RDS for MySQL instance that uses standard SSDs or enhanced SSDs (ESSDs), you must use the advanced download feature to download the data of the ApsaraDB RDS for MySQL instance to an Alibaba Cloud Object Storage Service (OSS) bucket, and then import the data in the OSS bucket to an AnalyticDB for MySQL instance.

Prerequisites

  • The RDS instance runs MySQL and uses standard SSDs or ESSDs.
  • The format of the file from which you want to import data to the AnalyticDB for MySQL instance is CSV and Parquet.
  • The following operations are performed to create a directory to store the AnalyticDB for MySQL data in an OSS bucket, and the advanced download feature is used to download the data to the OSS bucket.
    1. Activate OSS. For more information, see Activate OSS.
      Note The OSS bucket resides in the same region as AnalyticDB for MySQL.
    2. Create an OSS bucket. For more information, see Create buckets.
    3. Create a directory. For more information, see Create directories.
    4. Use the advanced download feature to download data to the OSS bucket.
      The mytable table exists in the mydb database. Example:mytable1
      In this example, the mytable file is downloaded to the /bucket/_export/mydb/mytable/data/0-1csv directory in the OSS bucket. Sample data in the downloaded CSV file:
      "12","hello_world_1"
      "27","hello_world_2"
      "28","hello_world_3"
      "33","hello_world_4"
      "37","hello_world_5"
      "40","hello_world_6"
  • The required AnalyticDB for MySQL cluster is created. For more information, see Create an AnalyticDB for MySQL cluster.
  • If the AnalyticDB for MySQL cluster is in elastic mode, you must turn on ENI in the Network Information section of the Cluster Information page. Enable ENI

Import the data in the OSS bucket to an AnalyticDB for MySQL instance.

The following example describes how to import the data in the /bucket/_export/mydb/mytable/data/0-1csv directory to the adb_demo database of an AnalyticDB for MySQL instance.

  1. Connect to the AnalyticDB for MySQL cluster. For more information, see Connect to an AnalyticDB for MySQL cluster.
  2. Create a destination database. For more information, see Create a database.

    In this example, the database named adb_demo is used as the destination database in the AnalyticDB for MySQL cluster.

  3. Create an external mapping table.

    AnalyticDB for MySQL supports only table analysis. Make sure that a mapping table is created on the AnalyticDB for MySQL instance before data analysis. You can execute the CREATE TABLE statement to create an OSS external mapping table in the CSV or Parquet format in the adb_demo database. For more information, see Syntax for creating an OSS external table. In the following example, an OSS external mapping table in the CSV format is created.

    CREATE TABLE IF NOT EXISTS mytable
    (
        id string,
        name string
    )
    ENGINE='OSS'
    TABLE_PROPERTIES='{
        "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
        "url":"oss://bucket/_export/mydb/mytable/data/0-1csv",
        "accessid":"LTAIF****5FsE",
        "accesskey":"Ccw****iWjv",
        "delimiter":",",
        "format":"csv"
    }';
    Note If the table in the downloaded file is stored in multiple CSV files, such as 0-1csv and 0-2csv, you need to only change the URL to "url":"oss://bucket/_export/mydb/mytable/data/". Then, AnalyticDB for MySQL merges the files in this directory into the mytable analysis table.

    This way, you can view the mytable table in the database of an AnalyticDB for MySQL instance. The content of this table is exactly the same as that of the original mytable table. You can query data in the mytable table.

  4. Query the data of an ApsaraDB RDS for MySQL instance that uses standard SSDs or ESSDs in the OSS bucket.
    Execute the following statement in the console:
    SELECT * FROM mytable; // mytable is the name of the table in the database of an AnalyticDB for MySQL instance.

    The data in the queried table is returned. The returned data is exactly the same as that of the original mytable table.

What to do next

After the data of your ApsaraDB RDS for MySQL instance that uses standard SSDs or ESSDs is imported to the AnalyticDB for MySQL instance, use AnalyticDB for MySQL to perform business analysis on the data. If multiple database tables are queried, follow the preceding steps to import multiple tables to the adb_demo database.