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
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
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'

# 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
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'