数据自定义查询API

在查询密钥生成后，您就可以通过调用自定义查询API进行项目数据的查询。调用方法请参见Open API文档中的调用方法描述。

1. SQL查询API

[POST /querySql]

参数字段说明：

字段 是否选填 描述
token 必填 查询密钥
sql 必填 查询的SQL语句
format 选填 行数据格式(json,csv,csv_header,tsv,tsv_header），默认json
  • curl示例
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'

1.1 返回结果

返回结果按行分隔，每行格式为执行查询语句时指定的格式。

1.1.1 json格式返回结果

当格式为json时，首行包含了状态值和数据元信息，格式如下：

{"data":{"headers":["#country","#province","#city"]},"return_code":0,"return_message":"success"}

结果字段说明：

字段 描述
return_code 状态值，0 表示查询成功
return_message 返回消息
data.headers 查询结果的列名信息

如果查询结果不为空，首行之后是数据行

["中国","甘肃省","兰州市"]
["中国","北京市","北京市"]
["中国","广东省","广州市"]
["中国","甘肃省","兰州市"]

1.1.1 其他格式返回结果

当格式为csv_header或tsv_header时，首行为列名信息（csv）：

"#country","#province","#city"
  • 之后是每行为一个列表，包含了返回的结果（csv）
    "中国","甘肃省","兰州市"
"中国","北京市","北京市"
"中国","广东省","广州市"
"中国","甘肃省","兰州市"

当格式为csv或tsv时，结果中没有列名信息，只有数据内容。

2. SQL分页查询API

SQL分页查询API包含两个相关方法。第一个方法用于执行查询语句，执行结束后返回结果的meta信息和分页信息；第二个方法用于下载某一页的结果数据。

2.1 执行查询语句

[POST /open/execute-sql]

参数字段说明：

字段 是否选填 描述
token 必填 查询密钥
sql 必填 查询的SQL语句
format 选填 行数据格式(json,csv,tsv），默认json
pageSize 选填 每页行数，最小1000，默认10000
  • curl示例
    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'
    返回结果
    {
"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","latest_login_time","g8","g9","g0","g3","sfgas","testyb1","testyb_002","chum_user","a333339","a3333346","a1211111","a1231","a11","a22","b1","b2","b3","b4","b5","wangli123","select","wangli4444","l655665","channel","test12377","default_3","test_group_con","asd1","fsdfssad","core_user","zhoujin_test","test_wl_limit","test_new","tss","t111","sss","testicon","aleeko1","tiaojianfenqun1","wltest111","sfat"],
  "pageCount": 2,
  "pageSize": 10000,
  "rowCount": 11000,
  "taskId": "119a3a37411f3000"
},
"return_code": 0,
"return_message": "success"
}
    结果里data所含字段说明：
字段 描述
headers 查询结果的列名信息
taskId 查询任务的ID，用于后续下载结果分页数据
pageCount 结果数据的总页数
pageSize 每页行数
rowCount 结果数据的总行数

2.2 下载结果分页数据

[GET /open/sql-result-page]

参数字段说明：

字段 是否选填 描述
token 必填 查询密钥
taskId 必填 来自执行查询语句返回
pageId 必填 取值范围：[0, pageCount-1]
  • curl示例
    curl -X GET 'http://ta2:8992/open/sql-result-page?token=bTOzKiTIozG4e19FgXphcA8dDV3DIY8RwdHTO7aSnBsRqSNaIk19BnBMecJDWibD&taskId=119a3a37411f3000&pageId=1'

返回结果按行分隔，每行数据的格式为执行查询语句时指定的格式

[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,"2019-12-15 16:22:13.000",false,false,false,false,null,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,"app store",false,false,true,false,false,false,true,false,false,false,false,false,false,false,false,false,false]
[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","2019-12-15 16:42:18.000",false,false,false,false,null,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,"app store",false,false,true,false,false,true,true,false,false,false,false,false,false,false,false,false,false]
[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,"2019-12-15 16:42:18.000",false,false,false,false,null,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,"华为应用市场",false,false,true,false,false,false,true,false,false,false,false,false,false,false,false,false,false]

results matching ""

    No results matching ""