Updated on 2024-10-25 GMT+08:00

Running a Spark SQL Job

MRS allows you to submit and run your own programs, and get the results. This section will show you how to submit a Spark SQL job in an MRS cluster.

Spark SQL jobs are used to query and analyze data, including SQL statements and scripts. If SQL statements contain sensitive information, you can also use script files to submit them.

You can create a job online and submit it for running on the MRS console, or submit a job in CLI mode on the MRS cluster client.

Video Tutorial

This tutorial demonstrates how to submit and view a Spark SQL job on the cluster management page.

The UI may vary depending on the version. This tutorial is for reference only.

Prerequisites

  • You have uploaded the program packages and data files required by jobs to OBS or HDFS.
  • If the job program needs to read and analyze data in the OBS file system, you need to configure storage-compute decoupling for the MRS cluster. For details, see Configuring Storage-Compute Decoupling for an MRS Cluster.

Submitting a Job on the Console

  1. Log in to the MRS console.
  2. On the Active Clusters page, select a running cluster and click its name to switch to the cluster details page.
  3. In the Basic Information area of the Dashboard page, click Synchronize on the right side of IAM User Sync to synchronize IAM users.

    Perform this step only when Kerberos authentication is enabled for the cluster.

    • After the IAM user synchronization is complete, wait for 5 minutes before submitting a job. For details about IAM user synchronization, see Synchronizing IAM Users to MRS..
    • When the policy of the user group an IAM user belongs to changes from MRS ReadOnlyAccess to MRS CommonOperations, MRS FullAccess, or MRS Administrator, or vice versa, it takes time for the cluster node's System Security Services Daemon (SSSD) cache to refresh. To prevent job submission failure, wait for five minutes after user synchronization is complete before submitting the job with the new policy.
    • If the IAM username contains spaces (for example, admin 01), jobs cannot be added.

  4. Click Job Management. On the displayed job list page, click Create.
  5. Set Type to SparkSql and configure Spark SQL information be referring to Table 1.

    Figure 1 Adding a Spark SQL job
    Table 1 Job configuration information

    Parameter

    Description

    Example

    Name

    Job name. It contains 1 to 64 characters. Only letters, digits, hyphens (-), and underscores (_) are allowed.

    sparksql

    SQL Type

    Submission type of the SQL statement

    • SQL: Run the entered SQL statement.
    • Script: Load SQL scripts from HDFS or OBS to run SQL statements.

    SQL

    SQL Statement

    This parameter is valid only when SQL Type is set to SQL. Enter the SQL statement to be executed, and then click Check to check whether the SQL statement is correct.

    If you want to submit and execute multiple statements at the same time, use semicolons (;) to separate them.

    -

    SQL File

    This parameter is valid only when SQL Type is set to Script. The path of the SQL file to be executed must meet the following requirements:

    Path of the SQL script file to be executed. You can enter the path or click HDFS or OBS to select a file.

    • The value contains a maximum of 1,023 characters. It cannot contain special characters (;|&>,<'$) and cannot be left blank or all spaces.
    • The OBS program path should start with obs://, for example, obs://wordcount/program/XXX.jar. The HDFS program path should start with hdfs://, for example, hdfs://hacluster/user/XXX.jar.
    • The script file must end with .sql.

    obs://wordcount/program/test.sql

    Program Parameter

    (Optional) Used to configure optimization parameters such as threads, memory, and vCPUs for the job to optimize resource usage and improve job execution performance.

    Table 2 lists the common program parameters of SparkSql jobs. You can configure the parameters based on the execution program and cluster resources.

    -

    Service Parameter

    (Optional) Service parameters for the job.

    To modify the current job, change this parameter. For permanent changes to the entire cluster, refer to Modifying the Configuration Parameters of an MRS Cluster Component and modify the cluster component parameters accordingly.

    Click to the add icon on the right to add more parameters.

    For example, add the following service configuration parameters:

    • fs.obs.access.key: key ID used to access OBS in AK/SK mode.
    • fs.obs.secret.key: key used to access OBS in AK/SK mode.

    -

    Command Reference

    Command submitted to the background for execution when a job is submitted.

    mrs-spark-sql-wrapper -e

    Table 2 Program parameters

    Parameter

    Description

    Example

    --conf

    Configuration item for adding tasks.

    spark.executor.memory=2G

    --driver-memory

    Running memory of a driver.

    2G

    --num-executors

    Number of executors to be started.

    5

    --executor-cores

    Number of executor cores.

    2

    --jars

    Additional dependency packages of a task, which is used to add the external dependency packages to the task.

    -

    --executor-memory

    Executor memory.

    2G

  6. Confirm job configuration information and click OK.
  7. After the job is submitted, you can view the job running status and execution result in the job list. After the job status changes to Completed, you can view the analysis result of related programs.

Submitting a Job Using the Cluster Client

  1. Install the MRS cluster client. For details, see Installing an MRS Cluster Client.

    The MRS cluster comes with a client installed for job submission by default, which can also be used directly. For MRS 3.x and later versions, the default client installation path is /opt/Bigdata/client on the Master node. For versions earlier than MRS 3.x, the default client installation path is /opt/client on the Master node.

  2. If Kerberos authentication has been enabled for the current cluster, create a user for submitting jobs by referring to Creating an MRS Cluster User.

    Skip this step for normal clusters.

    In this example, a machine-machine user has been created, and user groups (hadoop and supergroup), the primary group (supergroup), and role permissions (System_administrator and default) have been correctly assigned to the user.

    After the user is created, download the authentication credential file.
    • For clusters of MRS 3.x or later, log in to FusionInsight Manager and choose System > Permission > User. In the Operation column of the newly created user, choose More > Download Authentication Credential.
    • For MRS 2.x or earlier, log in to MRS Manager and choose System > Manage User. In the Operation column of the newly created user, choose More > Download Authentication Credential.

    Upload the user authentication credential to the /opt directory on the cluster client node.

  3. Log in to the node where the client is located as the MRS cluster client installation user.
  4. Decompress the user authentication credential file to obtain the user.keytab and krb5.conf files.

    cd /opt

    tar -xvf XXX_keytab.tar

  5. Initialize environment variables.

    cd /opt/Bigdata/client

    source bigdata_env

    cd $SPARK_HOME

  6. Enter the spark-sql CLI and run the SQL statement.

    ./bin/spark-sql --conf spark.yarn.principal=MRSTest --conf spark.yarn.keytab=/opt/user.keytab

    To execute the SQL file, upload the SQL file to the node where the client is located (for example, the /opt/ directory) in advance and run the following command:

    ./bin/spark-sql --conf spark.yarn.principal=MRSTest --conf spark.yarn.keytab=/opt/user.keytab -f /opt/script.sql
    • spark.yarn.principal: name of the user who submits the job.
    • spark.yarn.keytab: keytab file for user authentication.