Query ApsaraDB RDS for MySQL with Presto

Step 1: Configure the connector

1. Go to the Presto service page.
   1. Log on to the Alibaba Cloud EMR console.
   2. In the top navigation bar, select a region and a resource group.
      
   3. Click the Clusters tab.
      
   4. On the Clusters page, find your cluster and click Details in the Actions column.
      
   5. In the left-side navigation pane, choose Services > Presto.
2. Add connector information.
   1. On the Presto service page, click the Configure tab.
   2. In the Service Configuration section, click the connector1.properties tab.
      If you need to connect to multiple ApsaraDB RDS for MySQL databases, you can configure multiple connector.properties files.
   3. Change the value of connector.name to mysql.
   4. In the upper-right corner, click Custom Configurations.
   5. In the Add Configuration Item dialog box, configure the following parameters.

      Parameter	Description
      connection-user	The username to access the database. This example uses hiveuser.
      connection-password	The password to access the database.
      connection-url	The database connection string. For more information, see View and manage instance endpoints and ports. Example: jdbc:mysql://rm-2ze5ipacsu8265qxxxxxxxx.mysql.rds.aliyuncs.com:3306.

3. Save the configuration.
   1. In the upper-right corner, click Save.
   2. In the Confirm dialog box, enter a reason in the Execution Reason field and turn on Auto-update Configuration.
   3. Click OK.
4. Restart the service to apply the changes.
   1. In the upper-right corner, choose Actions > > Restart All Components.
   2. In the Execute Cluster Action dialog box, enter a reason in the Execution Reason field.
   3. Click OK.
   4. In the Confirm dialog box, click OK.

Step 2: View the RDS databases

1. Connect to the cluster over SSH.
   For more information, see Log on to a cluster.
2. Run the following command to connect to the Presto client.

   presto --server emr-header-1:9090 --catalog hive --schema default --user hadoop

   If the following prompt appears, you have successfully connected to the Presto client:

   presto:default>

3. Run the following command to view the schemas from the connector1 catalog.

   show schemas from connector1;

   Note The catalog name connector1 matches the name of the properties file that you configured in Step 1: Configure the connector.

   The following output is returned.

         Schema
   --------------------
    emruser
    information_schema
    performance_schema
    sys
   (4 rows)

Step 3: Query table data

• Query data from the emruser.tbl_employee table.

  select * from connector1.emruser.tbl_employee;

  The following output is returned.

   id |    name    | dept_id | salary
  ----+------------+---------+---------
    1 | Ming Li    |       1 | 10000.0
    2 | Eric Cai   |       1 | 11000.0
    3 | Bonnie Liu |       2 | 11000.0
  (3 rows)                       

• Query data from the tbl_department table.

  select * from hive.default.tbl_department;

  The following output is returned.

  dept_id | dept_name
  --------+----------
        1 | IT
        2 | Finance
  (2 rows)

• Join the tables to query data.
  Run the following command to query data by joining the default.tbl_department and emruser.tbl_employee tables.

  select * from hive.default.tbl_department a, connector1.emruser.tbl_employee b where a.dept_id = b.dept_id;

  The command returns the following result.

  dept_id |dept_name | id |    name    | dept_id | salary
  --------+----------+----+------------+---------+---------
        2 | Finance  |  3 | Bonnie Liu |       2 | 11000.0
        1 | IT       |  2 | Eric Cai   |       1 | 11000.0
        1 | IT       |  1 | Ming Li    |       1 | 10000.0
  (3 rows)