目录
此内容是否有帮助?

# Data Custom Query API

After the query key is generated, you can query the project data by calling the custom query API. See Call method description in Open API doc.

# I. 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 default json
(json,csv,csv_header,tsv,tsv_header）
timeoutSeconds
 10
 Integer
 No
 Request timed out parameter, timeout cancels query task

Successful Response Example

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

  1. Return 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
∟ 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 return results

First column name information (csv) when formatted csv_header or tsv_header:

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

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

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

There is no column name 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'

# II. 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
∟ pageCount 2
 Integer
 Total number of pages of result data
∟ pageSize 10000
 Integer
 Number of lines per page
∟ rowCount 11000
 Integer
 Total number of rows of result data
∟ header ["#user_id"]
 List
 List of first-line fields
∟ 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'

# III. 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
∟ 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
∟ taskId 119a3a37411f3000
 String
 The ID of the query task, which is used for paging data of subsequent download results
∟ status FINISHED
 String
 Task Status (RUNNING, FINISHED, FAILED)
∟ resultStat -
 Object
 Result information, returned when the status is FINISHED
∟ headers ["#user_id"]
 List
 List of names
∟ rowCount 11000
 Integer
 Total number of rows
∟ 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'

← User Groups and Tag APIs →