Describes the use cases, submission methods, and configurations for developing XIHE BSP SQL jobs. - AnalyticDB

Prerequisites

A job resource group is created for the AnalyticDB for MySQL Enterprise Edition, Basic Edition, or Data Lakehouse Edition cluster.
A database account is created for the AnalyticDB for MySQL Enterprise Edition, Basic Edition, or Data Lakehouse Edition cluster.

Use cases

The XIHE BSP engine executes XIHE BSP SQL jobs, which are suitable for ETL scenarios, large queries, and bursty low-priority queries. For more information about the XIHE BSP engine, see compute engine.

ETL scenarios

The following figure shows a typical ETL process.

Data cleansing and transformation operations on large datasets between a data source and the ADS layer often take a long time to complete. For these jobs, response time is not a major concern. Instead, high reliability is required to ensure that the entire ELT process completes by a specific deadline. The system must also support features such as automatic retries. You can run these queries using the XIHE BSP engine to take advantage of its high throughput, high reliability, and low cost.

Queries on the ADS layer are typically more sensitive to response times, often requiring responses in seconds or even milliseconds. You can run these queries using the XIHE MPP engine to take advantage of its higher speed.

Large queries for the BSP engine

Due to the limitations of XIHE MPP, some large queries may cause out-of-memory (OOM) errors or other query exceptions. To run these queries on the XIHE MPP engine, you must scale up your cluster to add more resources, which is not cost-effective. In this case, you can use the XIHE BSP engine to run the queries. The queries run in a specified job resource group and spill data to disks, which is more suitable for large queries. In addition, resources for a job resource group are requested and billed on demand, which lowers costs.

Bursty low-priority queries

Low-priority queries are usually not sensitive to response times. However, if a large number of these queries are submitted at once, they can consume system resources and affect the execution of other queries. To address this issue, you can run these low-priority queries in a job resource group by using the XIHE BSP engine. This approach isolates resources, mitigates resource pressure on the system, and prevents queries from affecting each other.

Limitations

The XIHE BSP engine does not support writing to Hudi tables.
The XIHE BSP engine does not support reading from or writing to Delta tables.

Developing XIHE BSP jobs

You can develop a XIHE BSP job using one of the following methods.

SQL development editor

On the SQL Development page, select a job resource group and the XIHE engine to submit a XIHE BSP job.

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 Job Development > SQL Development.
On the SQLConsole tab, select a job resource group and the XIHE engine.
Enter the SQL statement and click Execute.
After the SQL statement executes, the results appear on the Execution Results tab.

On the Execution Records tab, click Result in the Actions column to download the execution result.

Synchronous submission

You can synchronously submit a XIHE BSP job by specifying a job resource group in a hint.

Syntax

/*+ resource_group=<resource_group_name>*/ <SQL Statement>;

resource_group_name: the name of the job resource group.
SQL Statement: the SQL statement. You must add a hint before each statement.

Example

/*+ resource_group=bsptest*/SELECT count(*) from test_db.ods_hudi;

Asynchronous submission

You can asynchronously submit a XIHE BSP job by specifying a job resource group and an asynchronous submission type in a hint.

Syntax

/*+ resource_group=<resource_group_name>, query_submission_type=async*/ <SQL Statement>;

resource_group_name: the name of the job resource group.
query_submission_type=async: specifies that the job is submitted asynchronously.
SQL Statement: the SQL statement. You must add a hint before each statement.

Example

/*+ resource_group=bsptest, query_submission_type=async*/SELECT count(*) from test_db.ods_hudi;

After you submit an asynchronous job, a job_id is immediately returned. After the job completes, you can run theSHOW job result WHERE job='job_id'; statement to query the execution result. For information about how to query the status of an asynchronous job, see Query the status of an asynchronous job.

XIHE BSP job configuration

You can configure the resources, default timeout, and priority for a XIHE BSP job.

Configuration methods

You can apply configuration settings to a single BSP job, all jobs in a specific job resource group, or all jobs in a cluster.

For a single job

To make a configuration effective only for a single job, add the/*+ resource_group=<resource_group_name>,<config_name>*/ hint to the SQL statement.

In the hint, resource_group_name is the name of the resource group and config_name is a parameter from the Configuration parameters list.

Example: To limit a job in the job resource group named bsptest to a maximum of 20 ACUs:

/*+ resource_group=bsptest,elastic_job_max_acu=20*/SELECT count(*) from test_db.ods_hudi;

For a resource group

To make a configuration effective for all jobs that run in a job resource group, run theSET adb_config <resource_group_name>.<config_name> statement.

In the hint, resource_group_name is the name of the resource group and config_name is a parameter from the Configuration parameters list.

Example: This command limits each job that runs in the job resource group named bsptest to a maximum of 20 ACUs.

SET adb_config bsptest.elastic_job_max_acu=20;

Verify the configuration

To verify that the configuration is applied to the resource group, run theSHOW ADB_CONFIG KEY=<resource_group_name>.<config_name> statement.

For a cluster

To make a configuration effective for all jobs that run in a cluster, run theSET adb_config <config_name> statement. config_name is a parameter from the Configuration parameters list.

Example: This command limits each job that runs in the cluster to a maximum of 20 ACUs.

SET adb_config elastic_job_max_acu=20;

Verify the configuration

To verify that the configuration is applied to the cluster, run theSHOW ADB_CONFIG KEY=<config_name> statement.

Parameters

The following table describes the parameters that you can configure for XIHE BSP jobs.

Category	Parameter	Description	Default
Resource	`elastic_job_max_acu`	The maximum number of ACUs that a single XIHE BSP job can use. This includes both the AppMaster and compute nodes. The value cannot exceed the maximum number of ACUs allocated to the resource group. Note The AppMaster is a node that parses queries, and schedules and executes jobs.	9
Timeout	`batch_query_timeout`	The timeout for a BSP job, in milliseconds (ms). If a job runs longer than this value, the system automatically cancels it.	7200000
Priority	`query_priority`	The priority of the BSP job. Valid values: HIGH, NORMAL, LOW, and LOWEST. For more information about the priority queue, see Priority queue of a job resource group.	NORMAL

FAQ

View BSP job status

If you submitted the BSP job from the SQL development editor, go to the Job Editor > SQL Development page and view the job status on the Execution Records tab.
If you submitted the BSP job using other methods, you can query its status from theinformation_schema.kepler_meta_elastic_job_list table by running the following statement:
```
SELECT status FROM information_schema.kepler_meta_elastic_job_list WHERE process_id='<job_id>';
```
Note
Theinformation_schema.kepler_meta_elastic_job_list table stores up to 1,000 BSP jobs submitted within the last 30 days. You can perform further statistical analysis, such as aggregation, on this table. The following example shows how to count the number of BSP jobs by state:
```
SELECT status,count(*) FROM information_schema.kepler_meta_elastic_job_list GROUP BY status;
```

Synchronous vs. asynchronous submission

Synchronous submission and asynchronous submission provide the same features. The only difference is whether the client must wait for the query to complete.

Asynchronous submission has the following limitations:

A result set can contain a maximum of 10,000 rows.
A maximum of 1,000 result sets, including their CSV file download links, are retained for up to 30 days.

Asynchronous submission is recommended for long-running, compute-intensive queries that return small result sets, such asINSERT INTO SELECT,INSERT OVERWRITE SELECT, andCREATE TABLE AS SELECT.