Presto (also known as PrestoDB) is a flexible, scalable, and distributed SQL query engine that supports standard SQL for interactive analysis and querying of big data. DataWorks provides EMR Presto nodes that you can use to develop and periodically schedule Presto tasks. This topic describes the process of developing tasks using EMR Presto nodes and includes important notes.
Prerequisites
An Alibaba Cloud EMR cluster is created and registered to DataWorks. For more information, see DataStudio (old version): Associate an EMR computing resource.
(Required if you use a RAM user to develop tasks) The RAM user is added to the DataWorks workspace as a member and is assigned the Develop or Workspace Administrator role. The Workspace Administrator role has more permissions than necessary. Exercise caution when you assign the Workspace Administrator role. For more information about how to add a member, see Add workspace members and assign roles to them.
A serverless resource group is purchased and configured. The configurations include association with a workspace and network configuration. For more information, see Create and use a serverless resource group.
A workflow is created in DataStudio.
Development operations in different types of compute engines are performed based on workflows in DataStudio. Therefore, before you create a node, you must create a workflow. For more information, see Create a workflow.
Limitations
Only legacy Hadoop data lake clusters are supported. DataLake and Custom clusters are not supported.
This type of node can be run only on a serverless resource group or an exclusive resource group for scheduling. We recommend that you use a serverless resource group.
The size of an SQL statement in a Presto task cannot exceed 130 KB.
When you use an EMR Presto node to query data, a maximum of 10,000 records can be returned, and the total size of the returned data cannot exceed 10 MB.
Data lineage is not supported for tasks that are developed using EMR Presto nodes.
Step 1: Create an EMR Presto node
Go to the DataStudio page.
Log on to the DataWorks console. In the top navigation bar, select the desired region. In the left-side navigation pane, choose . On the page that appears, select the desired workspace from the drop-down list and click Go to Data Development.
Create an EMR Presto node.
Right-click the target workflow and choose .
NoteAlternatively, you can hover over Create and select .
In the Create Node dialog box, enter a Name and select an Engine Instance, Node Type, and Path. Click Confirm. The configuration tab for the EMR Presto node is displayed.
NoteThe node name can contain uppercase letters, lowercase letters, Chinese characters, digits, underscores (_), and periods (.).
Step 2: Develop an EMR Presto task
On the configuration tab of the EMR Presto node, double-click the node that you created. You are redirected to the task development page where you can perform the following operations.
Develop SQL code
You can develop node code in the SQL editor and define variables using the ${variable_name} format. You can then assign a value to each variable on the node editing page in the Scheduling Configuration > Scheduling Parameters section of the right-side navigation bar. This lets you dynamically pass parameters to your code in scheduling scenarios. For more information about scheduling parameters, see Supported formats of scheduling parameters. The following is an example.
select '${var}'; -- You can use this with scheduling parameters.
select * from userinfo ;The size of an SQL statement cannot exceed 130 KB.
If multiple EMR computing resources are attached to your workspace in DataStudio, select a computing resource as required. If only one EMR computing resource is attached, you do not need to select one.
To modify parameter assignments in your code, click Run With Parameters on the top toolbar. For more information about the parameter assignment logic, see Differences in parameter assignment logic for Run, Run with Parameters, and development environment smoke testing.
(Optional) Configure advanced parameters
You can configure specific properties for a node in the Advanced Settings section. For more information about how to configure the parameters, see Spark Configuration. The following table describes the advanced parameters that you can configure for different types of EMR clusters.
Hadoop cluster: EMR on ECS
Advanced parameters | Description |
FLOW_SKIP_SQL_ANALYZE | The execution method of SQL statements. Valid values:
Note This parameter is supported only for test runs of flows in the development environment. |
USE_GATEWAY | Specifies whether to submit jobs from this node through a gateway cluster. Valid values:
Note If the node's cluster is not associated with a gateway cluster, setting this parameter to |
Run the SQL task
In the toolbar, click the
icon. In the Parameters dialog box, select the scheduling resource group that you created and click Run.NoteTo access computing resources over the public internet or in a VPC, you must use a schedule resource group that has passed a connectivity test with those resources. For more information, see Network Connectivity Solutions.
To modify the resource group for subsequent jobs, you can click the Run With Parameters
icon and select a different scheduling resource group.
Click the
icon to save the SQL statement.(Optional) Perform smoke testing.
If you want to perform smoke testing in the development environment, you can do so when you submit a node or after the node is submitted. For more information, see Perform smoke testing.
Step 3: Configure scheduling properties
If you want the system to periodically run a task on the node, you can click Properties in the right-side navigation pane on the configuration tab of the node to configure task scheduling properties based on your business requirements. For more information, see Overview.
You must configure the Rerun and Parent Nodes parameters on the Properties tab before you commit the task.
Step 4: Deploy the task
After a task on a node is configured, you must commit and deploy the task. After you commit and deploy the task, the system runs the task on a regular basis based on scheduling configurations.
Click the
icon in the top toolbar to save the task. Click the
icon in the top toolbar to commit the task. In the Submit dialog box, configure the Change description parameter. Then, determine whether to review task code after you commit the task based on your business requirements.
NoteYou must configure the Rerun and Parent Nodes parameters on the Properties tab before you commit the task.
You can use the code review feature to ensure the code quality of tasks and prevent task execution errors caused by invalid task code. If you enable the code review feature, the task code that is committed can be deployed only after the task code passes the code review. For more information, see Code review.
If you use a workspace in standard mode, you must deploy the task in the production environment after you commit the task. To deploy a task on a node, click Deploy in the upper-right corner of the configuration tab of the node. For more information, see Deploy nodes.
What to do next
After you commit and deploy the task, the task is periodically run based on the scheduling configurations. You can click Operation Center in the upper-right corner of the configuration tab of the corresponding node to go to Operation Center and view the scheduling status of the task. For more information, see View and manage auto triggered tasks.
FAQ
Q: Why does the "Error executing query" message appear?
A: Ensure that the cluster is an earlier version of a Hadoop-based data lake cluster.
Q: Why does a connection timeout occur when the node runs?
A: Ensure network connectivity between the resource group and the cluster. Go to the computing resource list page to initialize the resource. In the dialog box that appears, click Re-initialize and verify that the initialization is successful.
