Help Center> Data Lake Insight> API Reference> APIs Related to SQL Jobs> Job-related APIs> Submitting a SQL Job (Recommended)
View PDF

Submitting a SQL Job (Recommended)

Function

This API is used to submit jobs to a queue using SQL statements.

The job types support DDL, DCL, IMPORT, QUERY, and INSERT. Functions of IMPORT and EXPORT are the same as those described in Importing Data. The difference lies in the implementation method.

Additionally, you can use other APIs to query and manage jobs. For details, see the following sections:

This API is synchronous if job_type in the response message is DCL.

Debugging

You can debug this API in API Explorer.

URI

  • URI format

    POST /v1.0/{project_id}/jobs/submit-job

  • Parameter description
    Table 1 URI parameter

    Parameter

    Mandatory

    Type

    Description

    project_id

    Yes

    String

    Project ID, which is used for resource isolation. For details about how to obtain its value, see Obtaining a Project ID.

Request

Table 2 Request parameters

Parameter

Mandatory

Type

Description

sql

Yes

String

SQL statement that you want to execute.

currentdb

No

String

Database where the SQL statement is executed. This parameter does not need to be configured during database creation.

queue_name

No

String

Name of the queue to which a job to be submitted belongs. The name can contain only digits, letters, and underscores (_), but cannot contain only digits or start with an underscore (_).

conf

No

Array of Strings

You can set the configuration parameters for the SQL job in the form of Key/Value. For details about the supported configuration items, see Table 3.
Table 3 Configuration parameters description

Parameter

Default Value

Description

spark.sql.files.maxRecordsPerFile

0

Maximum number of records to be written into a single file. If the value is zero or negative, there is no limit.

spark.sql.autoBroadcastJoinThreshold

209715200

Maximum size of the table that displays all working nodes when a connection is executed. You can set this parameter to -1 to disable the display.

NOTE:

Currently, only the configuration unit metastore table that runs the ANALYZE TABLE COMPUTE statistics noscan command and the file-based data source table that directly calculates statistics based on data files are supported.

spark.sql.shuffle.partitions

4096

Default number of partitions used to filter data for join or aggregation.

spark.sql.dynamicPartitionOverwrite.enabled

false

In dynamic mode, Spark does not delete the previous partitions and only overwrites the partitions without data during execution.

spark.sql.files.maxPartitionBytes

134217728

Maximum number of bytes to be packed into a single partition when a file is read.

spark.sql.badRecordsPath

-

Path of bad records.

dli.sql.sqlasync.enabled

false

Indicates whether DDL and DCL statements are executed asynchronously. The value true indicates that asynchronous execution is enabled.

dli.sql.job.timeout

-

Sets the job running timeout interval. If the timeout interval expires, the job is canceled. Unit: ms.

Response

Table 4 Response parameters

Parameter

Mandatory

Type

Description

is_success

Yes

Boolean

Indicates whether the request is successfully sent. Value true indicates that the request is successfully sent.

message

Yes

String

System prompt. If execution succeeds, the parameter setting may be left blank.

job_id

Yes

String

ID of a job returned after a job is generated and submitted by using SQL statements. The job ID can be used to query the job status and results.

job_type

Yes

String

Type of a job. Job types include the following:

  • DDL
  • DCL
  • IMPORT
  • EXPORT
  • QUERY
  • INSERT

schema

No

Array of objects

If the statement type is DDL, the column name and type of DDL are displayed.

rows

No

Array of objects

When the statement type is DDL, results of the DDL are displayed.

job_mode

No

String

Job execution mode. The options are as follows:

  • async: asynchronous
  • sync: synchronous

Example

  • Example request 
    {
    "currentdb": "db1",
    "sql": "desc table1",
    "queue_name": "default",
    "conf": [
        "dli.sql.shuffle.partitions = 200"
    ]
}
  • Example response (successful request) 
    {
  "is_success": true,
  "message": "{""}",
  "job_id": "8ecb0777-9c70-4529-9935-29ea0946039c",
  "job_type": "DDL",
  "job_mode":"sync",
  "schema": [
    {
      "col_name": "string"
    },
    {
      "data_type": "string"
    },
    {
      "comment": "string"
    }
  ],
  "rows": [
    [
      "c1",
      "int",
      null
    ],
    [
      "c2",
      "string",
      null
    ]
  ]
}

Status Codes

Table 5 describes the status code.

Table 5 Status codes

Status Code

Description

200

Submitted successfully.

400

Request error.

500

Internal service error.

Error Codes

If an error occurs when this API is invoked, the system does not return the result similar to the preceding example, but returns the error code and error information. For details, see Error Code.

Parent topic: Job-related APIs