自定义查询

方舟5.0版本中新增API

1.自定义SQL查询

适合查询结果为小数据量的场景(千级数据量),例如分析结果统计,查询数据状态;如需导出更大规模数据,见【章节2.自定义SQL导出

1.1 接口地址

【POST】 /uba/api/sql/query

1.2 请求参数

1.2.1 Body参数说明:

body参数示例:

{
"sql": "SELECT xwhat,xwhen,xwho FROM event_vd as event where event.ds=20200214 and event.\"$startup_time\" is not null LIMIT 10",
"format": "json"
}

body参数说明:

  • sql: 查询的 SQL,例如 SELECT xwhat,xwhen,xwho FROM event_vd LIMIT 10

  • format: 文本输出可选项包括

    • csv:默认格式,sql查询结果输出为csv格式文件。默认文件编码为UTF-8,若导出文件要用excel打开,增加url参数?csvUtfEncoding=0

    • json:sql查询结果输出为一个完整JSON 的json文件

1.2.2 特殊说明:

  1. 可供查询的表有:事件表event_vd,属性表profile_vd

  2. 事件字段、事件属性字段、用户属性字段信息可以参见 API章节中的 元数据管理

  3. 事件或属性字段中如果包含$符号的则需要对该字段加上双引号,双引号也需要转义,例如\"$startup_time\"

  4. 认证参数:接口必传token和appKey两个参数,详情见 项目接口认证

  5. 表名可以不附带数据库名,API程序会根据认证参数,自动为表名加上数据库名,如果自带不符合认证校验的数据库名,接口将返回参数异常,提示包含非当前项目的数据库表

  6. 不支持数据操纵语言DML、数据定义语言DDL以及数据控制语言DCL,只支持数据查询语言DQL(基本结构是由SELECT子句,FROM子句,WHERE 子句组成的查询块)

  7. API程序默认开启 limit 检测,查询记录数不能超过系统配置的参数项arkweb.open_api.sqlquery.limit,该参数可以通过ambari管理平台进行变更设置,设置为-1表示不限制查询记录条数

1.3 返回结果示例

format为 csv类型

xwhen,xwhat,xwho,
1581648715861,$pageview,JS4a8bf500c4f4aadfdf3a43586d9008fd4a8b,
1581648718542,$pageview,JS4a8bf500c4f4aadfdf3a43586d9008fd4a8b,
1581648726163,$pageview,JS4a8bf500c4f4aadfdf3a43586d9008fd4a8b,

format为 ‌json类型

[
{
"xwhen": 1581648715861,
"xwhat": "$pageview",
"xwho": "JS4a8bf500c4f4aadfdf3a43586d9008fd4a8b"
},
{
"xwhen": 1581648718542,
"xwhat": "$pageview",
"xwho": "JS4a8bf500c4f4aadfdf3a43586d9008fd4a8b"
},
//....
]

1.4 接口调用示例

curl -H "Content-Type:application/json" -H "token:4113c9cad1c301113783f433e254888c" -H "appKey:31abd9593e9983ec" -X POST --data '{
"sql": "SELECT xwho,xwhat,xwhen,\"$browser\" FROM event_vd as event where event.ds=20200214 and event.\"$startup_time\" is not null LIMIT 2",
"format": "csv"
}' http://127.0.0.1:4005/uba/api/sql/query

2. 自定义SQL导出

适合sql查询结果为较大数据量的场景(百万级数据量),例如导出事件数据,导出用户数据

2.1 接口地址

【POST】 /uba/api/sql/export

2.2 请求参数

2.2.1 Body参数说明:

body参数示例:

{
"sql": "SELECT xwhat,xwhen,xwho FROM event_vd as event where event.ds=20200214 and event.\"$startup_time\" is not null LIMIT 10",
"format": "json"
}

body参数说明:

  • sql: 查询的 SQL,例如 SELECT xwhat,xwhen,xwho FROM event_vd LIMIT 10

  • format: 文本输出可选项包括

    • csv:默认格式,sql查询结果输出为csv格式文件。默认文件编码为UTF-8,若导出文件要用excel打开,增加url参数?csvUtfEncoding=0

    • json:sql查询结果输出为每行一个JSON 的json文件

2.2.2 特殊说明:

  1. 可供查询的表有:事件表event_vd,属性表profile_vd

  2. 事件字段、事件属性字段、用户属性字段信息可以参见 API章节中的 元数据管理

  3. 事件或属性字段中如果包含$符号的则需要对该字段加上双引号,双引号也需要转义,例如\"$startup_time\"

  4. 认证参数:接口必传token和appKey两个参数,详情见 项目接口认证

  5. 表名可以不附带数据库名,API程序会根据认证参数,自动为表名加上数据库名,如果自带不符合认证校验的数据库名,接口将返回参数异常,提示包含非当前项目的数据库表

  6. 不支持数据操纵语言DML、数据定义语言DDL以及数据控制语言DCL,只支持数据查询语言DQL(基本结构是由SELECT子句,FROM子句,WHERE 子句组成的查询块)

  7. 该接口不对sql 进行limit 限制,但受限于查询时长,默认在10分钟超时时间内,经过测试该API能够导出200万左右条记录(仅供参考,不同机器配置和查询列数差异会导致可导出记录数存在一定的偏差)

2.3 返回结果示例

format为 csv类型的输出举例:

xwhen,xwhat,xwho,
1581648715861,$pageview,JS4a8bf500c4f4aadfdf3a43586d9008fd4a8b,
1581648718542,$pageview,JS4a8bf500c4f4aadfdf3a43586d9008fd4a8b,
1581648726163,$pageview,JS4a8bf500c4f4aadfdf3a43586d9008fd4a8b,

format为 json类型输出举例:

{"xwhen":1581648726045,"$browser":"Chrome","xwho":"JS4a8bf500c4f4aadfdf3a43586d9008fd4a8b","xwhat":"login"}
{"xwhen":1581650573221,"$browser":"Chrome","xwho":"a5qvKY6K1000047yuanyw@analysys.com.cn","xwhat":"login"}
{"xwhen":1581678914730,"$browser":"Firefox","xwho":"JS2db3b546aac1fd04f54d29a574a186042db3","xwhat":"login"}
{"xwhen":1581678914730,"$browser":"Firefox","xwho":"JS2db3b546aac1fd04f54d29a574a186042db3","xwhat":"login"}
{"xwhen":1581651522435,"$browser":"Chrome","xwho":"JS8ae9e9bf73b5b0dc3e42c8d31dd8078a8ae9","xwhat":"login"}
{"xwhen":1581669615438,"$browser":"Firefox","xwho":"JS4d372a84aa14b9279c0c35db28c3cb544d37","xwhat":"login"}

输出结果备注:

  1. /sql/export 接口输出,因为可以支撑大数据量,为了方便客户端可以进行批次处理,json类型的输出并非是一个完整的json数组,而是一行一条json(无[],按\n\分割)

  2. /sql/export接口输出,因为可以支撑大数据量,接口response为 流式输出。如果是通过程序来调用,那么建议:

    • 避免一次性加载response到内存中,改为流式接收 response body

    • Http Connection 需要增加SocketTimeout时长,如果下载过程出现504 网关超时,则需要修改nginx 超时配置

2.4 接口调用示例

导出日期为20200214的所有事件数据,输出写入到output.json 文件中

curl -o output.json -H "Content-Type:application/json" -H "token:3edbaf427ecdda80beef322ad3c333a4" -H "appKey:31abd9593e9983ec" -X POST --data '{
"sql": "SELECT xwho,xwhat,xwhen,\"$browser\" FROM event_vd as event where event.ds=20200214",
"format": "json"
}' http://127.0.0.1:4005/uba/api/sql/export

2.5 Java HttpClient 接口调用示例

public void testSqlExport(){
// 依赖 org.apache.httpcomponents 的 httpclient 包
static String UTF = "UTF-8";
CloseableHttpClient client = HttpClients.createDefault();
HttpPost httpPost = new HttpPost("http://127.0.0.1:4005/uba/api/sql/export");
//Header 参数
httpPost.setHeader("Content-Type", "application/json");
httpPost.setHeader("appKey", YOUR_APP_KEY);
httpPost.setHeader("token", YOUR_APP_TOKEN);
// post body params
Map<String,Object> params = new HashMap<>();
params.put("format", "json");
params.put("sql", "select * from profile_vd where arkfq_5=1");
ObjectMapper objectMapper = new ObjectMapper();
StringEntity stringEntity = new StringEntity(objectMapper.writeValueAsString(params), UTF);
stringEntity.setContentEncoding(UTF);
httpPost.setEntity(stringEntity);
//set timeout
RequestConfig reqConfig = RequestConfig.custom()
.setSocketTimeout(10*60*1000).setConnectTimeout(60*1000).build();
httpPost.setConfig(reqConfig);
//假设存到 output.txt(也可以是其他处理方式)
File dir = new File("/tmp");
File file = new File(dir, "output.txt");
try (CloseableHttpResponse response1 = client.execute(httpPost);
FileOutputStream fos = new FileOutputStream(file)) {
final HttpEntity entity = response1.getEntity();
if (entity != null) {
try (InputStream inputStream = entity.getContent()) {
// TODO: do something useful with the stream
try (BufferedReader br = new BufferedReader(new InputStreamReader(inputStream))) {
String line;
while ((line = br.readLine()) != null) {
// TODO: process the line.
// 以文件存储为例,按行读取,按行写入
fos.write((line+"\n").getBytes(UTF));\
}
}
}
EntityUtils.consume(entity);
}
}
}