MySQL connector - E-MapReduce - Alibaba Cloud Documentation Center

The MySQL connector lets you query and create tables in an external MySQL instance, and run JOIN operations across different systems (such as MySQL and Hive) or between two MySQL instances.

Background

The MySQL connector covers the following topics:

Configure the MySQL connector
Data type mapping
Example: Query MySQL data
Pushdown

Prerequisites

You have a DataLake cluster, a Hadoop cluster with the Trino service, or a standalone Trino cluster. For details, see Create a cluster.
You have an ApsaraDB RDS for MySQL instance. For details, see Quickly create an ApsaraDB RDS for MySQL instance.

Limitations

The MySQL connector is supported on Hadoop clusters and Trino clusters that run EMR V3.38.0 or later.
MySQL 5.7, 8.0, or later is required.
The Trino coordinator and all worker nodes require network access to the MySQL instance. The default port is 3306.
The following SQL statements are not supported:
- DELETE
- GRANT
- REVOKE
- SHOW GRANTS
- SHOW ROLES
- SHOW ROLE GRANTS

Configure the MySQL connector

To modify MySQL connector settings, see Configure a connector.

Default connector configuration

On the Trino service Configure page in the EMR console, in the Service Configuration section, click the mysql.properties tab and configure the following parameters.

Parameter	Description
connection-url	The JDBC connection string for the database. For details, see View and manage instance endpoints and ports. For example, jdbc:mysql://rm-2ze5ipacsu8265q****.mysql.rds.aliyuncs.com:3306.
connection-user	The database username. This user must have permission to access tables in the ApsaraDB RDS for MySQL database.
connection-password	The password for connection-user.

Configure multiple MySQL services

If you have multiple MySQL services, create a unique configuration file for each service in the etc/catalog directory. Each file must have a .properties extension. For example, a configuration file named sales.properties creates a catalog named sales in Trino.

Configuration example

To configure multiple MySQL services in an EMR cluster, add custom configuration items by using connector[x].properties, where x is 1, 2, 3, and so on. Perform the following steps:

On the configuration page of the Trino service in the EMR console, in the Service Configuration section, on the mysql.properties tab, modify the connection-user, connection-password, and connection-url parameters.

Click Add Configuration Item at the top to add the required configuration items. For details, see Add configuration items.
Save the configuration.
1. In the Service Configuration section, click Save.
2. In the dialog box that appears, enter an Execution Reason and click Save.
Restart the service.
1. In the upper-right corner, select More > Restart.
2. In the dialog box that appears, enter an Execution Reason and click OK.
3. In the Confirm dialog box, click OK.

Type mapping

DECIMAL types

You can map DECIMAL types with a precision greater than 38 to the Trino DECIMAL type by setting the decimal_mapping configuration item or the decimal_mapping session property to allow_overflow. The range of the result type is controlled by the decimal-default-scale or decimal-rounding-mode configuration item, and its precision is fixed at 38.

By default, values that require rounding or truncation fail at runtime. This behavior is controlled by the decimal-rounding-mode configuration item or the decimal-rounding-mode session property. Valid values: UNNECESSARY (default), UP, DOWN, CEILING, FLOOR, HALF_UP, HALF_DOWN, and HALF_EVEN.

General properties

The following properties control data type mapping and metadata caching in Trino.

Parameter	Description
unsupported-type-handling	Determines how unsupported column data types are handled. Valid values: IGNORE (default): The column is inaccessible. CONVERT_TO_VARCHAR: The column is converted to an unbounded VARCHAR.
jdbc-types-mapped-to-varchar	Maps a comma-separated list of data types to unbounded VARCHAR.
case-insensitive-name-matching	Specifies whether to match remote database and schema names in a case-insensitive manner. Valid values: true: Case-insensitive. false (default): Case-sensitive.
case-insensitive-name-matching.cache-ttl	The cache duration for case-insensitive remote name matching. The default is 1m.
metadata.cache-ttl	The cache duration for metadata, such as table and column statistics. The default value of 0 disables the cache.
metadata.cache-missing	Specifies whether to cache the results of failed metadata lookups, including table and column statistics. Valid values: true: Caches the absence. false (default): Does not cache the absence.

Example: Query MySQL

View the RDS database.
1. Connect to the cluster by using SSH. For more information, see Log on to a cluster.
2. Run the following command to connect to the Trino client.
  - DataLake cluster
```
trino --server master-1-1:9090 --catalog iceberg --schema default
```
  - Hadoop or standalone Trino cluster
```
trino --server emr-header-1:9090 --catalog iceberg --schema default
```
3. Run the following command to view the schema.
```
SHOW schemas FROM mysql;
```
  Note
  mysql is the name of the properties configuration file.
4. Run the following command to view the database.
```
SHOW tables FROM mysql.web;
```
  Note
  In the examples in this article, web is the database that you created in MySQL.
Query table data.
- Query data from the mysql.web.clicks table.
```
SELECT * FROM mysql.web.clicks;
```
- Query the column information for the mysql.web.clicks table.
```
SHOW columns FROM mysql.web.clicks;
```
  Alternatively, run the following command to describe the table.
```
DESCRIBE mysql.web.clicks;
```

Pushdown

For details about pushdown, see Pushdown in the Trino documentation.

The MySQL connector pushes down the following operators and functions:

Operators
- Join
- Limit
- Top-N
Functions
- avg()
- count()
- max()
- min()
- sum()
- stddev()
- stddev_pop()
- stddev_samp()
- variance()
- var_pop()
- var_samp()