menu
Is this helpful?

# データカスタムクエリAPI

After the access token has been generated, you can query the project data by calling the custom query api. Please refer to the call method description in Open API doc.

# 1. SQL Query

# SQL Query

Interface URL

/querySql?token=xxx&format=json&timeoutSecond=10&sql=select "#country","#province","#city" from v_event_102 where "$part_date"='2018-10-01' limit 200

Request method

POST

Content-Type

application/x-www-form-urlencoded

Request Query Parameter

Parameter name Sample value Parameter type Is required Parameter description
token xxx String Yes Query key
sql select "#country","#province","#city" from v_event_102 where "$part_date"='2018-10-01' limit 200 String Yes SQL statements for queries
format json String No Row data format the default is 'json' (json,csv,csv_header,tsv,tsv_header)
timeoutSeconds 10 Integer No Request timed out parameter, timeout cancels query task

Successful Response Example

The results are separated by rows, each in the format specified when the query statement was executed.

  1. Result in json format

When formatted as json, the first line contains status values and data metadata in the following format:

{
  "data": { 
      "headers": [
          "#country", 
          "#province", 
          "#city"
      ] 
  },
  "return_code": 0,
  "return_message": "success"
}
$$Parameter name Sample value Parameter type Parameter description
return_code 0 Integer Return code
return_message success String Return information
data - Object Return result
data.headers ["#country", "#province", "#city"] List First row

If the query result is not empty, the first row is followed by the data row

["China", "Gansu", "Lanzhou"]
["China", "Beijing", "Beijing"]
["China", "Guangdong", "Guangzhou"]
["China", "Gansu", "Lanzhou"]
  1. Other formats of return results

The first line is column information (csv) when formatted csv_header or tsv_header:

"#country","#province","#city"

After that is a list of rows, containing the returned result (csv)

"China", "Gansu", "Lanzhou"
"China", "Beijing", "Beijing"
"China", "Guangdong", "Guangzhou"
"China", "Gansu", "Lanzhou"
  1. The format is csv or tsv

There is no column information in the result, only data content.

Curl Example

curl -X POST 'http://ta2:8992/querySql?token=bTOzKiTIozG4e19FgXphcA8dDV3DIY8RwdHTO7aSnBsRqSNaIk19BnBMecJDWibD' --header 'Content-Type: application/x-www-form-urlencoded' -d 'sql=select+%22%23country%22%2c%22%23province%22%2c%22%23city%22+from+v_event_102+where+%22%24part_date%22%3d%272018-10-01%27+limit+200&format=json&timeoutSecond=10'

# 2. SQL Page-by-Page Query

The SQL paging query API contains two related methods. The first method is used to execute the query statement and return the meta information and paging information of the result after execution; the second method is used to download the paging data of the result.

# Execute Query Statement

Interface URL

/open/execute-sql?token=xxx&sql=select * from v_user_0 limit 11000&pageSize=10000&format=json&timeoutSeconds=10

Request method

POST

Content-Type

application/x-www-form-urlencoded

Request Query Parameter

Parameter name Sample value Parameter type Is required Parameter description
token xxx String Yes token
sql select * from v_user_0 limit 11000 String Yes SQL statements for queries
format json String No Row data format (json, csv, tsv), default json
pageSize 10000 Integer No Number of lines per page, minimum 1000, default 10000
timeoutSeconds 10 Integer No Request timed out parameter, timeout cancels query task

Successful Response Example

{
  "data": {
    "headers": [
      "#user_id",
      "#account_id",
      "#distinct_id",
      "#active_time",
      "#reg_time",
      "#user_operation",
      "#server_time",
      "#is_delete",
      "#update_time",
      "user_level",
      "coin_num",
      "register_time",
      "diamond_num",
      "first_recharge_time"
    ],
    "pageCount": 2,
    "pageSize": 10000,
    "rowCount": 11000,
    "taskId": "119a3a37411f3000"
  },
  "return_code": 0,
  "return_message": "success"
}
$$Parameter name Sample value Parameter type Parameter description
return_code 0 Integer Return code
return_message success String Return information
data - Object Return data
data.pageCount 2 Integer Total number of pages of result data
data.pageSize 10000 Integer Number of lines per page
data.rowCount 11000 Integer Total number of rows of result data
data.header ["#user_id"] List List of first-line fields
data.taskId 119a3a37411f3000 String Task ID

Error Response Example

{
    "return_code": -1008,
    "return_message": "The parameter (token) is empty"
}
Parameter name Sample value Parameter type Parameter description
return_code -1008 Integer Return code
return_message The parameter (token) is empty String Return information

Curl Example

curl -X POST 'http://ta2:8992/open/execute-sql?token=bTOzKiTIozG4e19FgXphcA8dDV3DIY8RwdHTO7aSnBsRqSNaIk19BnBMecJDWibD' --header 'Content-Type: application/x-www-form-urlencoded' -d 'sql=select%20*%20from%20v_user_0%20limit%2011000&pageSize=10000&format=json&timeoutSeconds=10'

# Download Results Paging Data

Interface URL

/open/sql-result-page?token=xxx&taskId=119a3a37411f3000&pageId=0

Request method

GET

Content-Type

application/json

Request Query Parameter

Parameter name Sample value Parameter type Is required Parameter description
token xxx String Yes token
taskId 119a3a37411f3000 String Yes The fallback field taskId from the Execute Query Statement interface to the
pageId 0 Integer No Value range: [0, pageCount-1], default is 0

The return results are separated by rows, and the format of each row of data is the format specified when executing the query statement.

[9324080,"c21756080","c40404080","2019-12-15 16:09:07.000","2019-12-15 16:09:07.000","user_set","2019-12-15 16:22:13.000",false,"2020-06-03 13:10:02.494",6,40000,"2019-12-15 16:09:07.000",0,null]
[9328294,"q21765894","q40422294","2019-12-15 16:19:49.000","2019-12-15 16:19:49.000","user_set","2019-12-15 16:42:18.000",false,"2020-06-03 13:10:02.494",17,642440,"2019-12-15 16:19:49.000",112,"2019-12-15 16:26:13.000"]
[9335719,"t21783319","t40454719","2019-12-15 16:29:45.000","2019-12-15 16:29:45.000","user_set","2019-12-15 16:42:18.000",false,"2020-06-03 13:10:02.494",6,70000,"2019-12-15 16:29:45.000",0,null]

Error Response Example

{
    "return_code": -1,
    "return_message": "The task is running"
}
Parameter name Sample value Parameter type Parameter description
return_code -1 Integer Return code
return_message The task is runningThe task is running String Return information

Curl Example

curl -X GET 'http://ta2:8992/open/sql-result-page?token=bTOzKiTIozG4e19FgXphcA8dDV3DIY8RwdHTO7aSnBsRqSNaIk19BnBMecJDWibD&taskId=119a3a37411f3000&pageId=1'

# 3. SQL Asynchronous Query API

The SQL Asynchronous Query API contains four related methods.

  • Submit the query statement and return the task ID of the query;
  • Query the execution status of the task.
  • The result data of the query task.
  • Cancel unfinished tasks.

# Execute query statement

Interface URL

/open/submit-sql?token=xxx&format=json&sql=select * from v_user_0 limit 11000

Request method

POST

Content-Type

application/json

Request Query parameter

Parameter name Sample value Parameter type Is required Parameter description
token xxx String Yes token
sql select * from v_user_0 limit 11000 String Yes SQL statements for queries
format json String No Row data format (json, csv, tsv), default json
pageSize 1000 Integer No Number of lines per page, minimum 1000, no paging by default

Successful Response Example

{
  "data": {
    "taskId": "119a3a37411f3000"
  },
  "return_code": 0,
  "return_message": "success"
}
$$Parameter name Sample value Parameter type Parameter description
data - Object Return result
data.taskId 119a3a37411f3000 String Task ID
return_code 0 Integer Return code
return_message success String Return information

Error Response Example

{
    "return_code": -1008,
    "return_message": "The parameter (token) is empty"
}
Parameter name Sample value Parameter type Parameter description
return_code -1008 Integer Return code
return_message The parameter (token) is empty String Return information

Curl Example

curl -X POST 'http://ta2:8992/open/submit-sql?token=bTOzKiTIozG4e19FgXphcA8dDV3DIY8RwdHTO7aSnBsRqSNaIk19BnBMecJDWibD' --header 'Content-Type: application/x-www-form-urlencoded' -d 'sql=select%20*%20from%20v_user_0%20limit%2011000&format=json'

# Query the Execution Status of the Task

Interface URL

/open/sql-task-info?token=xxx&taskId=119a3a37411f3000

Request method

GET

Content-Type

application/json

Request Query Parameter

Parameter name Sample value Parameter type Is required Parameter description
token xxx String Yes token
taskId 119a3a37411f3000 String Yes The interface executes the query statement and returns the taskId in the result

Successful Response Example

{
  "data": {
    "taskId": "119a3a37411f3000",
    "status": "FINISHED",
    "resultStat": {
        "rowCount": 11000,
        "pageCount": 1,
        "headers": [
          "#user_id",
          "#account_id",
          "#distinct_id",
          "#active_time",
          "#reg_time",
          "#user_operation",
          "#server_time",
          "#is_delete",
          "#update_time",
          "user_level",
          "coin_num",
          "register_time",
          "diamond_num",
          "first_recharge_time"
        ]
     }
  },
  "return_code": 0,
  "return_message": "success"
}
$$Parameter name Sample value Parameter type Parameter description
return_code 0 Integer Return code
return_message success String Return information
data - Object Return result
data.taskId 119a3a37411f3000 String The ID of the query task, which is used for paging data of subsequent download results
data.status FINISHED String Task Status (RUNNING, FINISHED, FAILED)
data.resultStat - Object Result information, returned when the status is FINISHED
data.resultStat.headers ["#user_id"] List List of names
data.resultStat.rowCount 11000 Integer Total number of rows
data.resultStat.pageCount 1 Integer Total Pages

Error Response Example

{
    "return_code": -1008,
    "return_message": "The parameter (token) is empty"
}
Parameter name Sample value Parameter type Parameter description
return_code -1008 Integer Return code
return_message The parameter (token) is empty String Return information

Curl Example

curl -X GET 'http://ta2:8992/open/sql-task-info?token=bTOzKiTIozG4e19FgXphcA8dDV3DIY8RwdHTO7aSnBsRqSNaIk19BnBMecJDWibD&taskId=119a3a37411f3000'

# Download Results Paging Data

Interface URL

/open/sql-result-page?token=xxx&taskId=119a3a37411f3000

Request method

GET

Content-Type

application/json

Request Query Parameter

Parameter name Sample value Parameter type Is required Parameter description
token xxx String Yes token
taskId 119a3a37411f3000 String Yes The interface executes the query statement and returns the taskId in the result
pageId 0 Integer No Value range: [0, pageCount-1], default is 0

The return results are separated by rows, and the format of each row of data is the format specified when executing the query statement.

[9324080,"c21756080","c40404080","2019-12-15 16:09:07.000","2019-12-15 16:09:07.000","user_set","2019-12-15 16:22:13.000",false,"2020-06-03 13:10:02.494",6,40000,"2019-12-15 16:09:07.000",0,null]
[9328294,"q21765894","q40422294","2019-12-15 16:19:49.000","2019-12-15 16:19:49.000","user_set","2019-12-15 16:42:18.000",false,"2020-06-03 13:10:02.494",17,642440,"2019-12-15 16:19:49.000",112,"2019-12-15 16:26:13.000"]
[9335719,"t21783319","t40454719","2019-12-15 16:29:45.000","2019-12-15 16:29:45.000","user_set","2019-12-15 16:42:18.000",false,"2020-06-03 13:10:02.494",6,70000,"2019-12-15 16:29:45.000",0,null]

Error Response Example

{
    "return_code": -1008,
    "return_message": "The parameter (token) is empty"
}
Parameter name Sample value Parameter type Parameter description
return_code -1008 Integer Return code
return_message The parameter (token) is empty String Return information

Curl Example

curl -X GET 'http://ta2:8992/open/sql-result-page?token=bTOzKiTIozG4e19FgXphcA8dDV3DIY8RwdHTO7aSnBsRqSNaIk19BnBMecJDWibD&taskId=119a3a37411f3000'

# Cancel Unfinished Tasks

Interface URL

/open/cancel-sql-task?token=xxx&taskId=119a3a37411f3000

Request method

POST

Content-Type

application/json

Request Query Parameter

Parameter name Sample value Parameter type Is required Parameter description
token xxx String Yes token
taskId 119a3a37411f3000 String Yes The interface executes the query statement and returns the taskId in the result

Successful Response Example

{
  "return_code": 0,
  "return_message": "success"
}

Error Response Example

{
    "return_code": -1008,
    "return_message": "The parameter (token) is empty"
}
Parameter name Sample value Parameter type Parameter description
return_code -1008 Integer Return code
return_message The parameter (token) is empty String Return information

Curl Example

curl -X POST 'http://ta2:8992/open/cancel-sql-task?token=bTOzKiTIozG4e19FgXphcA8dDV3DIY8RwdHTO7aSnBsRqSNaIk19BnBMecJDWibD&taskId=119a3a37411f3000'