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'

← ユーザーコホートとタグAPI →