Updated on 2022-02-22 GMT+08:00

Creating and Submitting a SQL Job

Scenario Description

This section describes how to create and query SQL jobs using APIs. For details on how to call APIs, see Calling APIs.

Constraints

  • It takes 6 to 10 minutes to start a job using a new queue for the first time.

Involved APIs

Procedure

  1. Create a SQL queue. For details, see Creating a Queue.
  2. Create a database.
    • API

      URI format: POST /v1.0/{project_id}/databases

    • Request example
      • Description: Creates a database named db1 in the project whose ID is 48cc2c48765f481480c7db940d6409d1.
      • Example URL: POST https://{endpoint}/v1.0/48cc2c48765f481480c7db940d6409d1/databases
      • Body:
        {
             "database_name": "db1",
             "description": "this is for test"
        }
    • Example response
      {
        "is_success": true,
        "message": ""
      }
  3. Create a table.
    • API

      URI format: POST /v1.0/{project_id}/databases/{database_name}/tables

    • Request example
      • Description: In the project whose ID is 48cc2c48765f481480c7db940d6409d1, create a table named tb1 in the db1 database.
      • Example URL: POST https://{endpoint}/v1.0/48cc2c48765f481480c7db940d6409d1/databases/db1/tables
      • Body:
        {
          "table_name": "tb1",
          "data_location": "OBS",
          "description": "",
          "data_type": "csv",
          "data_path": "obs://obs/path1/test.csv",
          "columns": [
          {
             "column_name": "column1",
             "type": "string",
             "description": "",
             "is_partition_column": true
          },
          {
             "column_name": "column2",
             "type": "string",
             "description": "",
             "is_partition_column": false
          }
          ],
          "with_column_header": true,
          "delimiter": ",",
          "quote_char": "\"",
          "escape_char": "\\",
          "date_format": "yyyy-MM-dd",
          "timestamp_format": "yyyy-MM-dd HH:mm:ss"
        }
    • Example response
      {
        "is_success": true,
        "message": ""
      }
  4. (Optional) If the table to be created does not contain data, use the Importing Data API to import data to the table.
  5. (Optional) After data is imported, you can use the Querying Job Details API to check whether the imported data is correct.
  6. Submit a query job.
    • API

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

    • Request example
      • Description: Submit a SQL job in the project whose ID is 48cc2c48765f481480c7db940d6409d1 and query data in the tb1 table in the database db1.
      • Example URL: POST https://{endpoint}/v1.0/48cc2c48765f481480c7db940d6409d1/jobs/submit-job
      • Body:
        {
            "currentdb": "db1",
            "sql": "select * from tb1 limit 10",
            "queue_name": "queue1"
        }
    • Example response
      {
        "is_success": true,
        "message": "",
        "job_id":""95fcc908-9f1b-446c-8643-5653891d9fd9",
        "job_type": "QUERY",
        "job_mode": "async"
      }