:Delta Lake or Apache Hudi storage mechanism based on DLF, ApsaraDB RDS or Realtime Compute for Apache Flink, and OSS

Procedure

1. Step 1: Grant MaxCompute the permissions to access DLF and OSS

   Grant the access permissions on DLF and OSS to the Alibaba Cloud account that manages the MaxCompute project.

2. Step 2: Create a bucket and a folder in OSS

   Create an OSS bucket as the unified storage path for data lakes.

3. Step 3: Prepare the data that you want to ingest into data lakes

   Create an ApsaraDB RDS database and prepare the data that you want to ingest into data lakes.

4. Step 4: Add a data source to DLF and create a metadatabase

   Add ApsaraDB RDS as a data source for DLF.

5. Step 5: Create and start a data ingestion task in the DLF console

   Create a data ingestion task in the DLF console to synchronize table data from the ApsaraDB RDS database and play back the data to a data lake in real time.

6. Step 6: Analyze data in a data lake based on MaxCompute

   Create an external project on the Lake and Warehouse Integration (Data Lakehouse) page in the DataWorks console to analyze data in a data lake.

Step 1: Grant MaxCompute the permissions to access DLF and OSS

Step 2: Create a bucket and a folder in OSS

Create an OSS bucket as the unified storage path for data lakes.

1. Log on to the OSS console.

2. In the left-side navigation pane, click Buckets. On the Buckets page, click Create Bucket.

3. In the Create Bucket panel, configure the Bucket Name parameter, select a region, and then click Create. For example, you can set the Bucket Name parameter to mc-dlf-oss and select the China (Shanghai) region from the Region drop-down list.

   

4. On the Buckets page, click the name of the created bucket to go to the Objects page.

5. On the right side of the page, click Create Directory. In the Create Directory panel, specify Directory Name, such as datalaketest, and click OK.

   

Step 3: Prepare the data that you want to ingest into data lakes

Construct an ApsaraDB RDS database, create a table, and prepare the data that you want to ingest into data lakes.

1. Go to the Instances page in the ApsaraDB RDS console. In the top navigation bar, select a region, such as China (Shanghai). Then, click the ID of the instance that you want to use in the instance list. In this example, an ApsaraDB RDS for MySQL instance is used.

2. Create an ApsaraDB RDS database account. In the left-side navigation pane of the instance details page, click Accounts. On the User Account tab, click Create Account. In the Create Account panel, configure the parameters and click OK. The following table describes the parameters.

   

   Parameter	Description	Example
   Database Account	The account that is used to access the ApsaraDB RDS database. This information is associated with the ApsaraDB RDS database when you create the database.	datalake_mcdbuser
   Account Type	The type of the account. Valid values: Standard Account: You must associate this account with the ApsaraDB RDS database. Privileged Account: This account has permissions on all databases in the ApsaraDB RDS instance. You do not need to grant permissions on specific databases to the privileged account.	Standard Account
   New Password	The password of the account.	None
   Confirm Password	Confirm the password of the account.	None

   For more information about the parameters, see Create databases and accounts for an ApsaraDB RDS for MySQL instance.

3. Create an ApsaraDB RDS database. In the left-side navigation pane of the instance details page, click Databases. On the right side of the page, click Create Database. In the Create Database panel, configure the parameters and click Create. The following table describes the parameters.

   Parameter	Description
   Database Name	The name must be 2 to 64 characters in length. It must start with a letter and end with a letter or digit. It can consist of lowercase letters, digits, underscores (_), or hyphens (-). The database name must be unique within the instance. Note If the database name contains a hyphen (-), the corresponding folder name for the created database will replace the hyphen (-) with @002d.
   Supported Character Set	Select a character set as needed.
   Authorized By	Select the account that needs to access this database. This parameter can be left empty, and accounts can be bound after the database is created. For more information, see Modify account permissions. Note Only regular accounts are displayed here because high-privilege accounts automatically have all permissions for all databases and do not require authorization.
   Description	Optional. This parameter is used to add remarks about the database for easier subsequent database management. Up to 256 characters are allowed.

4. Create a table in the ApsaraDB RDS database and insert a small amount of test data into the table. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance. In this example, a table named anti_fraud_result is created. Sample statements:

   CREATE TABLE `anti_fraud_result` (
     `transactionid` varchar(32) NOT NULL,
     `uid` varchar(32) DEFAULT NULL,
     `card` varchar(32) DEFAULT NULL,
     `longitude` double(12,8) DEFAULT '12.00000000',
     `latitude` double(12,8) DEFAULT '12.00000000',
     PRIMARY KEY (`transactionid`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8
   ;
   
   INSERT INTO `anti_fraud_result` values ("12489571","82005","123123",3.14592040,101.12315432);
   INSERT INTO `anti_fraud_result` values ("12489572","82005","123123",3.14592040,101.12315432);
   INSERT INTO `anti_fraud_result` values ("12489573","82005","123123",3.14592040,101.12315432);
   INSERT INTO `anti_fraud_result` values ("12489574","82005","123123",3.14592040,101.12315432);

(Optional) Step 4: Add a data source to DLF and create a metadatabase

Create a metadatabase to manage data of data lakes in DLF.

1. Log on to the DLF console. In the top navigation bar, select a region, such as China (Shanghai).

2. Create a metadatabase, such as the datalake metadatabase.

   1. In the left-side navigation pane of the DLF console, choose Metadata > Metadata. On the Database tab of the Metadata page, click Create Database.

   2. On the Create Database page, configure the parameters. The following table describes the parameters.

      Parameter	Description	Example
      Catalog	The name of the database catalog where the data is stored.	default
      Database Name	The name of the destination metadatabase.	dlf_db
      Database Description	The description of the metadatabase that you want to create.	Data lakehouse
      Select Path	The OSS directory in which the data file is saved. The directory must be in the oss://<Bucket name>/<OSS directory name> format.	oss://doc-test-01/datalake/

   3. Click OK.

Step 5: Create and start a data ingestion task in the DLF console

The data ingestion feature of DLF is no longer updated. You can use one of the following methods to ingest data into data lakes as Delta Lake files or Apache Hudi files.

Step 6: Analyze data in a data lake based on MaxCompute

1. Create an external project in the DataWorks console.

   1. Log on to the DataWorks console. In the top navigation bar, 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, configure the parameters. The following tables describe the parameters.

      Table 1. Parameters in the Create Data Warehouse step

      Parameter	Description
      External Project Name	ext_dlf_delta
      MaxCompute Project	ms_proj1

      Table 2. Parameters in the Create Data Lake Connection step

      Parameter		Description
      Heterogeneous Data Platform Type	Select Alibaba Cloud DLF + OSS from the drop-down list.	None
      Alibaba Cloud DLF + OSS	External Project Description	None
      	Region Where DLF Is Activated	cn-shanghai
      	DLF Endpoint	dlf-share.cn-shanghai.aliyuncs.com
      	DLF Database Name	datalake
      	DLF RoleARN	None

   5. Click Create. On the page that appears, click Preview.

      If you can preview the table information in the DLF database, the operation is successful.

   Note

   This step describes how to create an external project in the DataWorks console. For more information about how to create an external project by using SQL statements, see Use SQL statements to manage an external project.

2. On the Ad Hoc Query page of the DataWorks console, view the tables in the external project.

   Sample statement:

   show tables in ext_dlf_delta;

   The following result is returned:

   ALIYUN$***@aliyun.com:anti_rds

   Note

   For more information about the ad hoc query feature that is provided by DataStudio of DataWorks, see Use an ad hoc query node to execute SQL statements (Optional).

3. On the Ad Hoc Query page of the DataWorks console, query the table data of the external project.

   Note

   If the query result is garbled, resolve the issue by following instructions in the section "How do I handle a dirty data error that is caused by encoding format configuration issues or garbled characters?" in Batch synchronization.

   • Sample statement:

     select * from ext_dlf_delta.anti_rds;

   • The following figure shows the returned result.

Procedure

1. Step 1: Grant MaxCompute the permissions to access DLF and OSS

   Grant the DLF and OSS access permissions to the Alibaba Cloud account that manages the MaxCompute project.

2. Step 2: Create a bucket and a folder in OSS

   Create an OSS bucket as the unified storage path for data lakes.

3. Step 3: Prepare the data that you want to ingest into data lakes

   Create temporary tables and prepare the data that you want to ingest into data lakes in the Realtime Compute for Apache Flink console.

4. Step 4: Add a data source to DLF and create a metadatabase

   Add a data source to DLF and create a metadatabase.

5. Step 5: Create and start a metadata extraction task in the DLF console

   Create a metadata extraction task in the DLF console to extract table data in the OSS directory into the data lake.

6. Step 6: Analyze data in a data lake based on MaxCompute

   Create an external project on the Lake and Warehouse Integration (Data Lakehouse) page in the DataWorks console to analyze data in a data lake.

Step 1: Grant MaxCompute the permissions to access DLF and OSS

Step 2: Create a bucket and a folder in OSS

Create an OSS bucket as the unified storage path for data lakes.

1. Log on to the OSS console.

2. In the left-side navigation pane, click Buckets. On the Buckets page, click Create Bucket.

3. In the Create Bucket panel, configure the Bucket Name parameter, select a region, and then click Create. For example, you can set the Bucket Name parameter to mc-dlf-oss and select the China (Shanghai) region from the Region drop-down list.

   

4. On the Buckets page, click the name of the created bucket to go to the Objects page.

5. On the right side of the page, click Create Directory. In the Create Directory panel, specify Directory Name, such as datalaketest, and click OK.

   

Step 3: Prepare the data that you want to ingest into data lakes

Use the Hudi connector to create temporary tables and prepare the data that you want to ingest into data lakes in the Realtime Compute for Apache Flink console. For more information, see Get started with an SQL deployment.

1. Go to the instance list in the Realtime Compute for Apache Flink console. In the top navigation bar, select the region where the Realtime Compute for Apache Flink instance is located. Then, click the ID of instance.

2. In the left-side navigation pane, choose Development > ETL. Then, click New to create a blank stream draft, and click Next.

3. In the New Draft dialog box, specify the job information and click Create.

4. Enter the following statements in the SQL editor. For more information about the syntax, see Hudi connector (to be retired).

   -- Create a temporary table named datagen as the data source. 
   CREATE TEMPORARY TABLE datagen(
     id INT NOT NULL PRIMARY KEY NOT ENFORCED,
     data  STRING,
     ts TIMESTAMP(3)
   ) WITH (
     'connector' = 'datagen' ,
     'rows-per-second'='100' 
   );
   
   -- Create a temporary table named flink_hudi_tbl as the result table. The data store points to OSS and the data is stored in the Apache Hudi format.
   CREATE TEMPORARY TABLE flink_hudi_tbl (
     id INT NOT NULL PRIMARY KEY NOT ENFORCED,
     data STRING,
     ts TIMESTAMP(3)
   ) WITH (
     'connector' = 'hudi', 
     'oss.endpoint' = 'oss-cn-beijing-internal.aliyuncs.com', 
     'accessKeyId' = '${secret_values.ak_id}', 
     'accessKeySecret' = '${secret_values.ak_secret}', 
     'path' = 'oss://<yourOSSBucket>/<Custom storage location>',
     'table.type' = 'MERGE_ON_READ',
     'hive_sync.enable' = 'true',
     'hive_sync.mode' = 'hms',
     'hive_sync.db' = 'flink_hudi',
     'hive_sync.table' = 'flink_hudi_tbl',
     'dlf.catalog.region' = 'cn-beijing',
     'dlf.catalog.endpoint' = 'dlf-vpc.cn-beijing.aliyuncs.com'
   );
   
   -- Write data from the source table to the result table.
   INSERT INTO flink_hudi_tbl SELECT * from datagen;

   Parameters

   Parameter	Description
   oss.endpoint	The internal endpoint of the region where the Flink instance resides. For more information about the endpoint of each region, see Regions and endpoints.
   accessKeyId	The AccessKey ID of your Alibaba Cloud account.
   accessKeySecret	The AccessKey secret of your Alibaba Cloud account.
   path	The path of the OSS bucket.
   dlf.catalog.region	The region of Alibaba Cloud DLF. For more information, see Supported regions and endpoints.
   dlf.catalog.endpoint	The endpoint of DLF. For more information, see Supported regions and endpoints. Note We recommend that you set this parameter to the virtual private cloud (VPC) endpoint of DLF. If you select the China (Hangzhou) region, set this parameter to dlf-vpc.cn-hangzhou.aliyuncs.com.

5. Perform a syntax check and deploy the draft. For more information, see Steps 4 and 6 in Get started with an SQL deployment.

   1. In the upper-right corner of the SQL Editor page, click Validate to perform a syntax check. If the syntax check is passed, a success message appears.

   2. In the upper-right corner of the SQL Editor page, click Deploy. In the Deploy draft dialog box, configure the related parameters and click Confirm.

6. Start the deployment for the draft and view the startup result. For more information, see Step 7 in Get started with an SQL deployment.

   1. In the left-side navigation pane, click Deployments.

   2. Find the desired deployment and click Start in the Actions column. In the Start Job dialog box, select Initial Mode and click Start. When the deployment status changes to RUNNING, the deployment is running as expected.

   3. On the Deployments page, view the computing result.

7. After the deployment starts and runs for a period of time, log on to the OSS console and view the data files that are written to the directory.

Step 4: Add a data source to DLF and create a metadatabase

Create a metadatabase to manage data of data lakes in DLF.

1. Log on to the DLF console. In the top navigation bar, select a region.

2. Create a metadatabase, such as the datalake metadatabase.

   1. In the left-side navigation pane of the DLF console, choose Metadata > Metadata. On the Database tab of the Metadata page, click Create Database.

   2. In the Create Database dialog box, configure the parameters. The following table describes the parameters.

      Parameter	Description	Example
      Catalog	The name of the database catalog where the data is stored.	default
      Database Name	The name of the destination metadatabase.	dlf_db
      Database Description	The description of the metadatabase that you want to create.	Data lakehouse
      Select Path	The OSS directory in which the data file is saved. The directory must be in the oss://<Bucket name>/<OSS directory name> format.	oss://doc-test-01/datalake/

   3. Click OK.

Step 5: Create and start a metadata extraction task in the DLF console

1. In the left-side navigation pane of the DLF console, choose Metadata > Metadata Discovery.

2. On the Metadata Discovery page, click Create Extraction Task on the Extraction Task tab.

3. On the Create Extraction Task page, configure relevant parameters. For more information, see Metadata discovery.

4. Click Save and Execute.

5. View the metadata table. After the execution progress of the extraction task reaches 100%, click Metadata in the left-side navigation pane. On the Table List tab, view the table details.

6. Query the table data. In the left-side navigation pane, click Data Exploration. On the page that appears, execute SQL statements to query the table data.

Step 6: Analyze data in a data lake based on MaxCompute

For more information, see Step 6: Analyze data in a data lake based on MaxCompute in this topic.