Import data from OSS to AnalyticDB for MySQL using metadata discovery - AnalyticDB

Business services often generate data files, such as standard forms and logs, that are periodically uploaded to OSS. However, this data frequently lacks metadata management, which makes the data difficult to process and analyze. The AnalyticDB for MySQL metadata discovery feature supports automatic OSS path mapping, intelligent file grouping, dynamic field discovery, and change awareness to automatically create and update data lake metadata. You can create an OSS metadata discovery job to automatically discover and import data from OSS files and buckets in the same region as your AnalyticDB for MySQL cluster.

Prerequisites

An AnalyticDB for MySQL Enterprise Edition, Basic Edition, or Data Lakehouse Edition cluster is created.
A database account is created for the AnalyticDB for MySQL cluster.
- If you use an Alibaba Cloud account, you need to only create a privileged account.
- If you use a Resource Access Management (RAM) user, you must create a privileged account and a standard account and associate the standard account with the RAM user.
An Object Storage Service (OSS) bucket is created in the same region as the AnalyticDB for MySQL cluster.
You have uploaded files to an OSS bucket, and the OSS path meets the following requirements:
- The OSS path must be in the format of <BucketName>/directory/.../directory/table/file or <BucketName>/directory/.../directory/table/partition/.../partition/file.
- The bucket must contain at least one level of subdirectories.
- Files in the same table or partition must have the same file type.
- Files in the same table or partition must have the same field types and number of fields.

Limitations

You can create only one metadata discovery job for the same OSS path in a cluster.

Procedure

Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. Find the cluster that you want to manage and click the cluster ID.
In the left-side navigation pane, choose Data Ingestion > Metadata Discovery.

On the Metadata Discovery page, in the OSS data source section, click Start Wizard.

Note

If the Start Wizard button is dimmed, you must create a privileged account first.

On the OSS Data Source tab, configure the parameters as described in the following table.

Parameter	Parameter	Description
Data Source Configuration	Data Warehouse Mode	Provides automated metadata discovery for building a standard data warehouse on OSS. This mode provides high detection accuracy. Note Free mode is not supported.
	OSS Directory	Common files: The storage path of the files in OSS. The path must end with a forward slash (`/`). Selecting a different OSS Directory results in a different mapping. Important When you specify an OSS Directory, make sure that at least one subdirectory exists after the OSS Directory. You cannot specify the parent directory of a file. Data lake table: The parent directory of the table's storage path in OSS. The path must end with a `/`. For example, if the directory of an Iceberg table is `oss://adb/testdb/iceberg_table/`, the OSS directory location is `oss://adb/testdb/`.
	Path Filter Rules (Optional)	Specify the storage path of files that you want to map to AnalyticDB for MySQL. Include: All files in the specified storage path will be mapped to AnalyticDB for MySQL. Important If a storage path contains multiple files, they must have the same file type, column count, and column types to be mapped. Exclude: All files in the specified storage path will not be mapped to AnalyticDB for MySQL.
	Format Parser	Select a format resolver based on the file format. For common file formats, the format resolver also supports automatic parsing by calling the resolvers for common file formats in sequence to parse files. Important If the selected format resolver type does not match the file format, the mapping fails. Supported resolver types: Common Files: `csv`, `json`, `parquet`, `avro`, `orc`, and `automatic`. Data Lake Tables: `iceberg`. Note The data lake table feature is in public preview. To use this feature, Submit a ticket.
	Configuration Options (Optional)	Advanced custom settings. When the format resolver is set to CSV, you can configure the following parameters: Field separator: Specifies the column separator for the CSV data file. Reference Identifier: Specifies the reference identifier for the AnalyticDB for MySQL table after parsing. Header Mode: Identifies the CSV header to determine the column names of the table. Allow single column: Specifies whether to allow a row in a CSV data file to contain only one column. false: Not allowed. true: Allowed. For other configurations, such as using the first row of a CSV file for column names, contact technical support.
Properties	Scheduling Frequency	To periodically capture changes in data files, set a scheduling frequency to run the job on a recurring basis. When data in OSS files changes, the data in the tables mapped to AnalyticDB for MySQL is updated based on the rules in Destination Metadata Settings.
Destination Metadata Settings	Schema Name	Set a schema name, which maps to a database name in AnalyticDB for MySQL. By default, a new schema is created for each discovery job. When you specify a database name, note the following: The schema name cannot be the name of an existing database in AnalyticDB for MySQL. It cannot be the same as the database name used by another metadata discovery job.
Destination Metadata Settings	Configuration Options (Optional)	Specify how to update a table mapped to AnalyticDB for MySQL when an OSS file's fields change or the file is deleted. Object Field Change Rules: Add Only Columns: When a column is added to an OSS file, the next metadata discovery job run adds the column to the table mapped to AnalyticDB for MySQL. Ignore Table Updates: When a column or partition of an OSS file changes, the next metadata discovery job run only synchronizes partitions to the table mapped to AnalyticDB for MySQL. Columns are not added or deleted. Object Delete Change Rules: Only Ignore Delete Updates is supported. If an OSS file is deleted, the table mapped to AnalyticDB for MySQL still exists after the next metadata discovery job is run. Important You can perform DDL and DML operations on the table mapped to AnalyticDB for MySQL. If you add a column to the AnalyticDB for MySQL table, the column is not overwritten and still exists after the next metadata discovery job is run. If you delete a column from the table mapped to AnalyticDB for MySQL, the deleted column is re-mapped to AnalyticDB for MySQL after the next metadata discovery job is run.

After you configure the parameters, click Create to create the metadata discovery job.

Note
After the metadata discovery job is created, it runs automatically based on the specified schedule. If you want to synchronize data immediately, find the job in the job list and click Execute in the Actions column.
After the job starts, you can manage it on the Tasks page, where you can view the job status, modify the configuration, and more.

Note
After the job runs successfully, go to the Job Development > SQL Development page to view the databases, tables, and partitions mapped to AnalyticDB for MySQL.

Mapping OSS paths to AnalyticDB for MySQL

The mapping result for an OSS path to AnalyticDB for MySQL depends on two factors:

The OSS file and its path.
The OSS Directory selected in the metadata discovery job.

The system automatically maps directory names to a table name and partitions based on the OSS Directory that you select. The name of the first-level directory under the OSS Directory is mapped to the table name, and the names of the directories from the second to the last level under the OSS Directory are mapped to partitions. An example is as follows.

OSS path	OSS directory location	Mapping to AnalyticDB for MySQL tables
oss://adb/Table1/file1.csv oss://adb/Table1/file2.json	oss://adb/	A table cannot be mapped. Reason: The files have inconsistent types (CSV and JSON) and cannot be mapped to a single table. Important Mapping also fails if files of the same type have inconsistent field data types.
oss://adb/Table2/year/month/day/file3.json oss://adb/Table2/year/month/day/file4.json	oss://adb/	The mapped table in AnalyticDB for MySQL is a partitioned table named `Table2`, with partition fields `partition_0`, `partition_1`, and `partition_2`. Note Because no partition key is specified, numbered placeholders like partition_0 are used as substitutes.
	oss://adb/Table2/	The mapped table in AnalyticDB for MySQL is a partitioned table named `year`, with partition fields `partition_0` and `partition_1`.
	oss://adb/Table2/year/month/	The mapped table in AnalyticDB for MySQL is a non-partitioned table named `day`.
	oss://adb/Table2/year/month/day/	A table cannot be mapped. Reason: No subdirectories exist after the selected OSS Directory.
oss://adb/Table3/year=2022/month=03/day=01/file5.csv oss://adb/Table3/year=2022/month=03/day=01/file6.csv	oss://adb/	The mapped table in AnalyticDB for MySQL is a partitioned table named `Table3`, with partition fields `year`, `month`, and `day`.
	oss://adb/Table3/ oss://adb/Table3/year=2022/	A table cannot be mapped. Reason: The directory names `year=2022` or `month=03` do not comply with the table naming conventions of AnalyticDB for MySQL.
oss://adb/Table4/2020/03/30/file7.csv oss://adb/Table3/2020/03/30/file8.csv	oss://adb/	The mapped table in AnalyticDB for MySQL is a partitioned table named `Table4`, with partition fields `partition_0`, `partition_1`, and `partition_2`. Note Because no partition key is specified, numbered placeholders like partition_0 are used as substitutes.
	oss://adb/Table3/ oss://adb/Table3/2020/	A table cannot be mapped. Reason: The directory names `2020` or `03` do not comply with the table naming conventions of AnalyticDB for MySQL.