# データカスタムクエリ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.
- 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"]
- 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"
- 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'