Create an EMR Spark SQL node - - Alibaba Cloud Documentation Center

This topic describes how to create an E-MapReduce (EMR) Spark SQL node. EMR Spark SQL nodes allow you to use the distributed SQL query engine to process structured data. This helps improve the efficiency of jobs.

Prerequisites

An EMR cluster is registered to DataWorks. For more information, see Register an EMR cluster to DataWorks.
(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 Development or Workspace Manager role. The Workspace Manager role has more permissions than necessary. Exercise caution when you assign the Workspace Manager role. For more information about how to add a member, see Add workspace members and assign roles to them.
A 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. 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.
If you want to use a specific development environment to develop a task, you can create a custom image in the DataWorks console. For more information, see Manage images.

Limits

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.
If you want to manage metadata for a DataLake or custom cluster in DataWorks, you must configure EMR-HOOK in the cluster first. If you do not configure EMR-HOOK in the cluster, metadata cannot be displayed in real time, audit logs cannot be generated, and data lineages cannot be displayed in DataWorks. In addition, the related EMR governance tasks cannot be run. For more information about how to configure EMR-HOOK, see Use the Spark SQL extension feature to record data lineage and historical access information.
You cannot view data lineages of a Spark cluster that is created on the EMR on ACK page or data lineages of an EMR Serverless Spark cluster.
For DataLake and custom clusters, you can register functions in a visualized manner. For Spark clusters that are created on the EMR on ACK page and EMR Serverless Spark clusters, you cannot register functions in a visualized manner.

Step 1: Create an EMR Spark SQL 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 Data Development and Governance > Data Development. On the page that appears, select the desired workspace from the drop-down list and click Go to Data Development.
Create an EMR Spark SQL node.
1. Find the desired workflow, right-click the workflow name, and then choose Create Node > EMR > EMR Spark SQL.
  Note
  Alternatively, you can move the pointer over the Create icon and choose Create Node > EMR > EMR Spark SQL.
2. In the Create Node dialog box, configure the Name, Engine Instance, Node Type, and Path parameters. Click Confirm. The configuration tab of the EMR Spark SQL node appears.
  Note
  The node name can contain letters, digits, underscores (_), and periods (.).

Step 2: Develop an EMR Spark SQL task

You can perform the following operations to develop an EMR Spark SQL task on the configuration tab of the EMR Spark SQL node:

Develop SQL code

In the SQL editor, develop node code. You can define variables in the ${Variable} format in the node code and configure the scheduling parameters that are assigned to the variables as values in the Scheduling Parameter section of the Properties tab. This way, the values of the scheduling parameters are dynamically replaced in the node code when the node is scheduled to run. For more information about how to use scheduling parameters, see Supported formats of scheduling parameters. Sample code:

SHOW TABLES; 
-- Define a variable named var in the ${var} format. If you assign the ${yyyymmdd} parameter to the variable as a value, you can create a table whose name is suffixed with the data timestamp. 
CREATE TABLE IF NOT EXISTS userinfo_new_${var} (
ip STRING COMMENT'IP address',
uid STRING COMMENT'User ID'
)PARTITIONED BY(
dt STRING
); -- You can assign a specific scheduling parameter to the var variable.

Note

The size of an SQL statement for the node cannot exceed 130 KB.
If multiple EMR data sources are associated with DataStudio in your workspace, you must select one from the data sources based on your business requirements. If only one EMR data source is associated with DataStudio in your workspace, you do not need to select a data source.

(Optional) Configure advanced parameters

You can configure Spark-specific parameters on the Advanced Settings tab of the configuration tab of the current node. For more information about how to configure the parameters, see Spark Configuration. The following table describes the advanced parameters that can be configured for different types of EMR clusters.

DataLake cluster or custom cluster: created on the EMR on ECS page

Advanced parameter	Description
queue	The scheduling queue to which jobs are committed. Default value: default. For information about EMR YARN, see YARN schedulers.
priority	The priority. Default value: 1.
FLOW_SKIP_SQL_ANALYZE	The manner in which SQL statements are executed. Valid values: `true`: Multiple SQL statements are executed at a time. `false` (default): Only one SQL statement is executed at a time. Note This parameter is available only for testing in the development environment of a DataWorks workspace.
ENABLE_SPARKSQL_JDBC	Specifies whether SQL statements are committed by using Java Database Connectivity (JDBC). Valid values: `true`: The SQL statements are committed by using JDBC. If the EMR cluster does not have the Kyuubi service, the SQL statements are committed to Spark Thrift Server. If the EMR cluster has the Kyuubi service, the SQL statements are committed to Kyuubi by using JDBC. You can configure custom Spark parameters. If the parameter is set to true, the metadata lineage feature is supported. However, if the SQL statements are committed to Spark Thrift Server, you cannot view the output information of the metadata of a node. `false` (default): The SQL statements are committed by using the spark-submit command. In this case, both Spark 2 and Spark 3 support the metadata lineage feature and allow you to view the output information of a node. You can also configure custom Spark parameters. Note If you run the spark-submit command to commit the SQL statements, temporary files and directories are generated in the `/tmp` directory of the Hadoop Distributed File System (HDFS) service of the EMR cluster by default. Make sure that you have the read and write permissions on the directory. If you run the spark-submit command to commit the SQL statements, you can add a SparkConf parameter on the Advanced Settings tab. After you commit the code, DataWorks adds the custom parameter to the command. Example: `"spark.driver.memory" : "2g"`.
USE_GATEWAY	Specifies whether to use a gateway cluster to commit jobs on the current node. Valid values: `true`: Use a gateway cluster to commit jobs. `false` (default): Use no gateway cluster to commit jobs. Jobs are automatically committed to the master node. Note If the EMR cluster to which the node belongs is not associated with a gateway cluster but the USE_GATEWAY parameter is set to `true`, jobs may fail to be committed.
DATAWORKS_SESSION_DISABLE	Specifies whether to establish a JDBC connection every time an SQL statement is executed. This parameter is available for testing in the development environment of a DataWorks workspace. Valid values: `true`: A JDBC connection is established every time an SQL statement is executed. `false` (default): The same JDBC connection is used when different SQL statements are executed for the same node. Note If the DATAWORKS_SESSION_DISABLE parameter is set to `false`, the value of `yarn applicationId` for Hive is not displayed. If you want the value of `yarn applicationId` to be displayed, you can set the DATAWORKS_SESSION_DISABLE parameter to `true`.
Others	You can add Spark-specific parameters. For more information about Spark-specific parameters, see Spark Configuration. Example: `spark.eventLog.enabled : false` . When you commit the code of the EMR Spark SQL node, DataWorks adds the custom parameter to the code in the `--conf key=value` format. Note You can configure global Spark parameters at the workspace level for DataWorks services. You can specify whether the global Spark parameters configured at the workspace level have a higher priority than the Spark parameters configured to run a single task in a specific DataWorks service. For more information about how to configure global Spark parameters, see Configure global Spark parameters.

EMR Serverless Spark cluster

For more information about parameter settings, see the Step 3: Submit a Spark job section of the "Use the spark-submit CLI to submit a Spark job" topic.

Advanced parameter	Description
queue	The scheduling queue to which jobs are committed. Default value: dev_queue.
priority	The priority. Default value: 1.
FLOW_SKIP_SQL_ANALYZE	The manner in which SQL statements are executed. Valid values: `true`: Multiple SQL statements are executed at a time. `false` (default): Only one SQL statement is executed at a time. Note This parameter is available only for testing in the development environment of a DataWorks workspace.
ENABLE_SPARKSQL_JDBC	Specifies whether SQL statements are committed by using JDBC. Valid values: `true`: The SQL statements are committed by using JDBC. If the EMR cluster does not have the Kyuubi service, the SQL statements are committed to Spark Thrift Server. If the EMR cluster has the Kyuubi service, the SQL statements are committed to Kyuubi by using JDBC. You can configure custom Spark parameters. If the parameter is set to true, the metadata lineage feature is supported. However, if the SQL statements are committed to Spark Thrift Server, you cannot view the output information of the metadata of a node. `false` (default): The SQL statements are committed by using the spark-submit command. In this case, both Spark 2 and Spark 3 support the metadata lineage feature and allow you to view the output information of a node. You can also configure custom Spark parameters. Note If you run the spark-submit command to commit the SQL statements, temporary files and directories are generated in the `/tmp` directory of the HDFS service of the EMR cluster by default. Make sure that you have the read and write permissions on the directory. If you run the spark-submit command to commit the SQL statements, you can add a SparkConf parameter on the Advanced Settings tab. After you commit the code, DataWorks adds the custom parameter to the command. Example: `"spark.driver.memory" : "2g"`.
USE_GATEWAY	Specifies whether to use a gateway cluster to commit jobs on the current node. Valid values: `true`: Use a gateway cluster to commit jobs. `false` (default): Use no gateway cluster to commit jobs. Jobs are automatically committed to the master node. Note If the EMR cluster to which the node belongs is not associated with a gateway cluster but the USE_GATEWAY parameter is set to `true`, jobs may fail to be committed.
DATAWORKS_SESSION_DISABLE	Specifies whether to establish a JDBC connection every time an SQL statement is executed. This parameter is available for testing in the development environment of a DataWorks workspace. Valid values: `true`: A JDBC connection is established every time an SQL statement is executed. `false` (default): The same JDBC connection is used when different SQL statements are executed for the same node. Note If the DATAWORKS_SESSION_DISABLE parameter is set to `false`, the value of `yarn applicationId` for Hive is not displayed. If you want the value of `yarn applicationId` to be displayed, you can set the DATAWORKS_SESSION_DISABLE parameter to `true`.
SERVERLESS_RELEASE_VERSION	The version of the Spark engine. By default, the value specified by the Default Engine Version parameter on the Register EMR Cluster page is used. To go to the Register EMR Cluster page, you can perform the following operations: Go to the SettingCenter page. In the left-side navigation pane, click Cluster Management. On the Cluster Management page, click Register Cluster and select E-MapReduce in the Select Cluster Type dialog box. You can configure this parameter to specify different engine versions for different types of tasks. Note The `SERVERLESS_RELEASE_VERSION` parameter takes effect only when the SQL session specified during cluster registration is not started on the EMR Serverless Spark page in the EMR console.
SERVERLESS_QUEUE_NAME	The resource queue. By default, the value specified by the Default Resource Queue parameter on the Register EMR Cluster page is used. You can add queues to meet resource isolation and management requirements. For more information, see Manage resource queues. Note The `SERVERLESS_QUEUE_NAME` parameter takes effect only when the SQL session specified during cluster registration is not started on the EMR Serverless Spark page in the EMR console.
SERVERLESS_SQL_COMPUTE	The SQL session. By default, the value specified by the Default SQL Compute parameter on the Register EMR Cluster page is used. You can configure this parameter to specify different SQL sessions for different types of tasks. For information about how to create an SQL session, see Manage SQL sessions.
Others	You can add Spark-specific parameters. For more information about Spark-specific parameters, see Spark Configuration. Example: `spark.eventLog.enabled : false` . When you commit the code of the EMR Spark SQL node, DataWorks adds the custom parameter to the code in the `--conf key=value` format. Note You can configure global Spark parameters at the workspace level for DataWorks services. You can specify whether the global Spark parameters configured at the workspace level have a higher priority than the Spark parameters configured to run a single task in a specific DataWorks service. For more information about how to configure global Spark parameters, see Configure global Spark parameters.

Save and execute SQL statements

In the top toolbar, click the icon to save the SQL statements. Then, click the icon to execute the SQL statements.

In the Parameters dialog box, select a resource group whose network connectivity test is successful to ensure that DataWorks can access your Spark service. If variables are used in node code, assign constants to the variables during the debugging and running process. For information about how to configure scheduling parameters and a resource group that are used for node scheduling, see the Step 3: Configure scheduling properties section in this topic. For more information about debugging, see Debugging procedure.

Note

If you want to change the scheduling parameter that is assigned to a variable in the code, click Run with Parameters in the top toolbar. For more information about the value assignment logic of scheduling parameters, see What are the differences in the value assignment logic of scheduling parameters among the Run, Run with Parameters, and Perform Smoke Testing in Development Environment modes?

Spark cluster: created on the EMR on ACK page

Advanced parameter	Description
FLOW_SKIP_SQL_ANALYZE	The manner in which SQL statements are executed. Valid values: `true`: Multiple SQL statements are executed at a time. `false` (default): Only one SQL statement is executed at a time. Note This parameter is available only for testing in the development environment of a DataWorks workspace.
USE_GATEWAY	Specifies whether to use a gateway cluster to commit jobs on the current node. Valid values: `true`: Use a gateway cluster to commit jobs. `false` (default): Use no gateway cluster to commit jobs. Jobs are automatically committed to the master node. Note If the EMR cluster to which the node belongs is not associated with a gateway cluster but the USE_GATEWAY parameter is set to `true`, jobs may fail to be committed.
Others	You can add Spark-specific parameters. For more information about Spark-specific parameters, see Spark Configuration. Example: `spark.eventLog.enabled : false` . When you commit the code of the EMR Spark SQL node, DataWorks adds the custom parameter to the code in the `--conf key=value` format. Note You can configure global Spark parameters at the workspace level for DataWorks services. You can specify whether the global Spark parameters configured at the workspace level have a higher priority than the Spark parameters configured to run a single task in a specific DataWorks service. For more information about how to configure global Spark parameters, see Configure global Spark parameters.

Hadoop cluster: created on the EMR on ECS page

Advanced parameter	Description
queue	The scheduling queue to which jobs are committed. Default value: default. For information about EMR YARN, see YARN schedulers.
priority	The priority. Default value: 1.
FLOW_SKIP_SQL_ANALYZE	The manner in which SQL statements are executed. Valid values: `true`: Multiple SQL statements are executed at a time. `false` (default): Only one SQL statement is executed at a time. Note This parameter is available only for testing in the development environment of a DataWorks workspace.
USE_GATEWAY	Specifies whether to use a gateway cluster to commit jobs on the current node. Valid values: `true`: Use a gateway cluster to commit jobs. `false` (default): Use no gateway cluster to commit jobs. Jobs are automatically committed to the master node. Note If the EMR cluster to which the node belongs is not associated with a gateway cluster but the USE_GATEWAY parameter is set to `true`, jobs may fail to be committed.
Others	You can add Spark-specific parameters. For more information about Spark-specific parameters, see Spark Configuration. Example: `spark.eventLog.enabled : false` . When you commit the code of the EMR Spark SQL node, DataWorks adds the custom parameter to the code in the `--conf key=value` format. Note You can configure global Spark parameters at the workspace level for DataWorks services. You can specify whether the global Spark parameters configured at the workspace level have a higher priority than the Spark parameters configured to run a single task in a specific DataWorks service. For more information about how to configure global Spark parameters, see Configure global Spark parameters.

Execute SQL statements

Click the icon in the top toolbar. In the Parameters dialog box, select a created resource group for scheduling and click Run.
Note
- If you want to access a data source over the Internet or a virtual private cloud (VPC), you must use the resource group for scheduling that is connected to the data source. For more information, see Network connectivity solutions.
- If you want to change the resource group in subsequent operations, you can click the (Run with Parameters) icon to change the resource group in the Parameters dialog box.
- If you use an EMR Spark SQL node to query data, a maximum of 10,000 data records can be returned, and the total size of the returned data records cannot exceed 10 MB.
Click the icon in the top toolbar to save SQL statements.
Optional. Perform smoke testing.
You can perform smoke testing on the node in the development environment when you commit the node or after you commit the node. 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.

Note

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.
Note
- You 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.