Custom data query API
After you generate a query token, you can query project data by calling the custom query API. For how to make calls, see the Open API documentation.
1. SQL query
1. SQL query
Endpoint URL
/querySql?token=xxx&format=json&timeoutSeconds=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 parameters
| Parameter name | Example value | Parameter type | Required | 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 statement to run |
| format | json | String | No | Row data format, json by default (json,csv,csv_header,tsv,tsv_header,json_object) |
| timeoutSeconds | 10 | Integer | No | Request timeout. The query task is canceled when it times out |
Success response example
Results are separated by line, and each line uses the format specified when the query statement was run.
1. Results in json format
When the format is json, the first line contains the status value and data metadata in the following format:
{
"data": {
"headers": [
"#country",
"#province",
"#city"
]
},
"return_code": 0,
"return_message": "success"
}
| $$Parameter name | Example value | Parameter type | Description | |
|---|---|---|---|---|
| return_code | 0 | Integer | Return code | |
| return_message | success | String | Return message | |
| data | - | Object | Returned result | |
| data.headers | ["#country", "#province", "#city"] | List | First line | |
If the query result isn't empty, data lines follow the first line
["China","Gansu","Lanzhou"]
["China","Beijing","Beijing"]
["China","Guangdong","Guangzhou"]
["China","Gansu","Lanzhou"]
2. Results in other formats
When the format is csv_header or tsv_header, the first line contains the column names (csv):
"#country","#province","#city"
Each following line is a list containing the returned results (csv)
"China","Gansu","Lanzhou"
"China","Beijing","Beijing"
"China","Guangdong","Guangzhou"
"China","Gansu","Lanzhou"
3. When the format is csv or tsv
The results contain no column names, only data.
curl example
curl -X POST 'http://ta2:8992/querySql?token=YOUR_TOKEN' --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&timeoutSeconds=10'
2. SQL paginated query
The SQL paginated query API has two related methods. The first runs the query statement and, when it finishes, returns the result's meta information and pagination information. The second downloads paginated result data.
2. Run a query statement
Endpoint 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 parameters
| Parameter name | Example value | Parameter type | Required | Description |
|---|---|---|---|---|
| token | xxx | String | Yes | Query key |
| sql | select * from v_user_0 limit 11000 | String | Yes | SQL statement to run |
| format | json | String | No | Row data format (json,csv,tsv,json_object), json by default |
| pageSize | 10000 | Integer | No | Rows per page, minimum 1000, 10000 by default |
| timeoutSeconds | 10 | Integer | No | Request timeout. The query task is canceled when it times out |
Success 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 | Example value | Parameter type | Description |
|---|---|---|---|
| return_code | 0 | Integer | Return code |
| return_message | success | String | Return message |
| data | - | Object | Returned data |
| data.pageCount | 2 | Integer | Total pages of result data |
| data.pageSize | 10000 | Integer | Rows per page |
| data.rowCount | 11000 | Integer | Total rows of result data |
| data.header | ["#user_id"] | List | Field list of the first line |
| data.taskId | 119a3a37411f3000 | String | Task ID |
Error response example
{
"return_code": -1008,
"return_message": "Parameter (token) is empty"
}
| Parameter name | Example value | Parameter type | Description |
|---|---|---|---|
| return_code | -1008 | Integer | Return code |
| return_message | Parameter (token) is empty | String | Return message |
curl example
curl -X POST 'http://ta2:8992/open/execute-sql?token=YOUR_TOKEN' --header 'Content-Type: application/x-www-form-urlencoded' -d 'sql=select%20*%20from%20v_user_0%20limit%2011000&pageSize=10000&format=json&timeoutSeconds=10'
3. Download paginated result data
Endpoint URL
/open/sql-result-page?token=xxx&taskId=119a3a37411f3000&pageId=0
Request method
GET
Content-Type
application/json
Request query parameters
| Parameter name | Example value | Parameter type | Required | Description |
|---|---|---|---|---|
| token | xxx | String | Yes | Query key |
taskId | 119a3a37411f3000 | String | Yes | The taskId field returned by the query execution API |
| pageId | 0 | Integer | No | Value range: [0, pageCount-1], 0 by default |
2.1 Results are separated by line, and each line uses the format specified when the query statement was run
[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]
2.2 When format is set to json_object in /open/execute-sql, results are returned in the following format:
{"app_version": "1.1", "channel": "Baidu", "server_time": "2020-06-03 13:10:02.494", "distinct_id": "c40404080"}
{"app_version": "1.1", "channel": "Baidu", "server_time": "2020-06-03 13:10:02.494", "distinct_id": "q21765894"}
{"app_version": "1.1", "channel": "Baidu", "server_time": "2020-06-03 13:10:02.494", "distinct_id": "t21783319"}
Error response example
{
"return_code": -1,
"return_message": "The task is running"
}
| Parameter name | Example value | Parameter type | Description |
|---|---|---|---|
| return_code | -1 | Integer | Return code |
| return_message | The task is runningThe task is running | String | Return message |
curl example
curl -X GET 'http://ta2:8992/open/sql-result-page?token=YOUR_TOKEN&taskId=119a3a37411f3000&pageId=1'
3. SQL asynchronous query API
The SQL asynchronous query API has four related methods.
- Submit a query statement and return the query task ID;
- Check the execution status of a query task.
- Get the result data of a query task.
- Cancel an unfinished task.
4. Run a query statement
Endpoint 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 parameters
| Parameter name | Example value | Parameter type | Required | Description |
|---|---|---|---|---|
| token | xxx | String | Yes | Query key |
sql | select * from v_user_0 limit 11000 | String | Yes | SQL statement to run |
| format | json | String | No | Row data format (json,csv,tsv,json_object), json by default |
| pageSize | 1000 | Integer | No | Rows per page, minimum 1000, no pagination by default |
Success response example
{
"data": {
"taskId": "119a3a37411f3000"
},
"return_code": 0,
"return_message": "success"
}
| $$Parameter name | Example value | Parameter type | Description |
|---|---|---|---|
| data | - | Object | Returned result |
| data.taskId | 119a3a37411f3000 | String | Task ID |
| return_code | 0 | Integer | Return code |
| return_message | success | String | Return message |
Error response example
{
"return_code": -1008,
"return_message": "Parameter (token) is empty"
}
| Parameter name | Example value | Parameter type | Description |
|---|---|---|---|
| return_code | -1008 | Integer | Return code |
| return_message | Parameter (token) is empty | String | Return message |
curl example
curl -X POST 'http://ta2:8992/open/submit-sql?token=YOUR_TOKEN' --header 'Content-Type: application/x-www-form-urlencoded' -d 'sql=select%20*%20from%20v_user_0%20limit%2011000&format=json'
5. Check the execution status of a query task
Endpoint URL
/open/sql-task-info?token=xxx&taskId=119a3a37411f3000
Request method
GET
Content-Type
application/json
Request query parameters
| Parameter name | Example value | Parameter type | Required | Description |
|---|---|---|---|---|
| token | xxx | String | Yes | Query key |
| taskId | 119a3a37411f3000 | String | Yes | The taskId in the result returned by the query execution API |
Success response example
{
"data": {
"taskId": "119a3a37411f3000",
"status": "FINISHED",
"progress": 100,
"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 | Example value | Parameter type | Description |
|---|---|---|---|
| return_code | 0 | Integer | Return code |
| return_message | success | String | Return message |
| data | - | Object | Returned result |
| data.taskId | 119a3a37411f3000 | String | ID of the query task, used to download paginated result data later |
| data.status | FINISHED | String | Task status (RUNNING, FINISHED, FAILED) |
| data.progress | 100 | Integer | Query progress (a value between 0 and 100 while RUNNING) |
| data.resultStat | - | Object | Result information, returned when the status is FINISHED |
| data.resultStat.headers | ["#user_id"] | List | List of column names |
| data.resultStat.rowCount | 11000 | Integer | Total rows |
| data.resultStat.pageCount | 1 | Integer | Total pages |
Error response example
{
"return_code": -1008,
"return_message": "Parameter (token) is empty"
}
| Parameter name | Example value | Parameter type | Description |
|---|---|---|---|
| return_code | -1008 | Integer | Return code |
| return_message | Parameter (token) is empty | String | Return message |
curl example
curl -X GET 'http://ta2:8992/open/sql-task-info?token=YOUR_TOKEN&taskId=119a3a37411f3000'
6. Download paginated result data
Endpoint URL
/open/sql-result-page?token=xxx&taskId=119a3a37411f3000
Request method
GET
Content-Type
application/json
Request query parameters
| Parameter name | Example value | Parameter type | Required | Description |
|---|---|---|---|---|
| token | xxx | String | Yes | Query key |
| taskId | 119a3a37411f3000 | String | Yes | The taskId in the result returned by the query execution API |
| pageId | 0 | Integer | No | Value range: [0, pageCount-1], 0 by default |
Results are separated by line, and each line uses the format specified when the query statement was run
[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": "Parameter (token) is empty"
}
| Parameter name | Example value | Parameter type | Description |
|---|---|---|---|
| return_code | -1008 | Integer | Return code |
| return_message | Parameter (token) is empty | String | Return message |
curl example
curl -X GET 'http://ta2:8992/open/sql-result-page?token=YOUR_TOKEN&taskId=119a3a37411f3000'
7. Cancel an unfinished task
Endpoint URL
/open/cancel-sql-task?token=xxx&taskId=119a3a37411f3000
Request method
POST
Content-Type
application/json
Request query parameters
| Parameter name | Example value | Parameter type | Required | Description |
|---|---|---|---|---|
| token | xxx | String | Yes | Query key |
| taskId | 119a3a37411f3000 | String | Yes | The taskId in the result returned by the query execution API |
Success response example
{
"return_code": 0,
"return_message": "success"
}
Error response example
{
"return_code": -1008,
"return_message": "Parameter (token) is empty"
}
| Parameter name | Example value | Parameter type | Description |
|---|---|---|---|
| return_code | -1008 | Integer | Return code |
| return_message | Parameter (token) is empty | String | Return message |
curl example
curl -X POST 'http://ta2:8992/open/cancel-sql-task?token=YOUR_TOKEN&taskId=119a3a37411f3000'