:Build a MaxCompute data lakehouse by using DataWorks and DLF

Limitations

• The data lakehouse solution is supported only in the following regions: China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Shenzhen), China (Hong Kong), Singapore, and Germany (Frankfurt).

• MaxCompute, OSS, ApsaraDB RDS, and DLF must be deployed in the same region.

Ingest MySQL data into a data lake

For details on data ingestion in DLF, see Quick start.

Step 1: Create a data lake metadatabase

Log on to the DLF console. In the top navigation bar, select a region. In the left-side navigation pane, choose MetaData > Metadata Management and create a metadatabase. For more information, see Databases, tables, and functions.

Step 2: Import data to OSS with DataWorks

1. Prepare the source data.

   1. Log on to the ApsaraDB RDS console, select a region, and then click Instances in the left-side navigation pane.

   2. Select the target ApsaraDB RDS instance and log on to the database.

   3. Create a table in the ApsaraDB RDS database and insert some test data. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance. For example, you can create a table named rds_mc with the following SQL statements:

      CREATE TABLE `rds_mc` (
        `id` varchar(32) ,
        `name` varchar(32) ,
          PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO `rds_mc`(`id` ,`name` ) VALUES(1,"Alice");
      INSERT INTO `rds_mc`(`id` ,`name` ) VALUES(2,"zhangsan");
      INSERT INTO `rds_mc`(`id` ,`name` ) VALUES(3,"zhaosi");
      INSERT INTO `rds_mc`(`id` ,`name` ) VALUES(4,"wangwu");
      INSERT INTO `rds_mc`(`id` ,`name` ) VALUES(5,"55555");
      INSERT INTO `rds_mc`(`id` ,`name` ) VALUES(8,"6666");
      SELECT * FROM `rds_mc`;

2. Prepare an ApsaraDB RDS for MySQL data source.

   Configure a MySQL data source in DataWorks. For more information, see Configure a MySQL data source.

3. Prepare an OSS data source.

   Configure an OSS data source in DataWorks. For more information, see Configure an OSS data source.

4. Create and run a data synchronization task.

   Create a batch synchronization task in the DataStudio module of DataWorks. For more information, see Configure a synchronization task in wizard mode. The following are key configurations:

   1. Network and resource settings

      Parameter		Description
      Source	Data source type	MySQL.
      	Data source name	The MySQL data source that you created.
      Resource group	My Resource Group	The exclusive resource group for Data Integration that you created.
      Destination	Data source type	OSS.
      	Data source name	The OSS data source that you created.

   2. Configure the task.

      On the Configure tasks tab, specify the table and filename.

      Parameter	Description
      Table	The name of the table that is created in the ApsaraDB RDS database.
      File name (with path)	Format: <Directory created in OSS>/<Data file to be exported to OSS>. Example: doc-test-01/datalake/anti.csv.

   3. Click the icon in the upper-left corner of the configuration page to save the task, and then click the icon to run the task.

   4. After the task runs successfully, verify that the data was imported to your configured OSS path.

Step 3: Discover metadata with DLF

In the DLF console, use metadata discovery to ingest the data. For more information, see Metadata discovery.

Step 4: View data lake metadata

In the DLF console, click MetaData > Metadata Management, go to the target database, and view the table information on the Table List tab.

Authorization

Building a data lakehouse with MaxCompute, DLF, and OSS requires authorization because the account used to create the MaxCompute project lacks default access to DLF and OSS. Grant permissions using one of the following methods:

• One-click authorization: Recommended if you use the same account for the MaxCompute project, DLF, and OSS. Click Authorize DLF and OSS to grant the required permissions.

• Custom authorization: This method can be used with either the same or different accounts for the MaxCompute project, DLF, or OSS. For more information, see Custom authorization.

Create an external project in MaxCompute

Create an external project in the DataWorks console.

1. Log on to the DataWorks console and select the China (Shanghai) region.

2. In the left-side navigation pane of the DataWorks console, choose More > Lake and Warehouse Integration (Data Lakehouse).

3. On the Lake and Warehouse Integration (Data Lakehouse) page, click Start.

4. On the Create Data Lakehouse page, follow the on-screen instructions. The following tables provide example parameter configurations.

   Table 1. Create a data warehouse

   Parameter	Example
   External Project Name	ext_dlf_delta
   MaxCompute Project	ms_proj1

   Table 2. Create an external data lake connection

   Parameter		Example
   Heterogeneous Data Platform Type	Select Alibaba Cloud DLF+OSS data lake connection	Not applicable
   Alibaba Cloud DLF+OSS data lake connection	External Project Description	Not applicable
   	DLF region	cn-shanghai
   	DLF endpoint	dlf-share.cn-shanghai.aliyuncs.com
   	DLF database name	datalake
   	DLF RoleARN	Not applicable

5. Click Create and then click Preview.

   If you can preview the tables from the DLF database, the creation was successful.

Query external project data with MaxCompute

In the DataWorks ad hoc query page, query the table data in the external project.

• Sample command:

  select * from ext_dlf_delta.rds_mc;

• Result: The command returns the data records from the id and name columns of the rds_mc table. This confirms that the data lake metadata was synchronized successfully.