数据自定义查询API

在查询密钥生成后，您就可以通过调用自定义查询API进行项目数据的查询

1. SQL查询API

[POST /querySql]

参数字段说明：

• 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"}

结果字段说明：

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

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

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]

参数字段说明：

• 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所含字段说明：

2.1 下载结果分页数据

[GET /open/sql-result-page]

参数字段说明：

• 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]