Elasticsearch使用sql语句进行查询

/**
* elasticsearh-sql 查询方法，传入sql语法查询
*/
function elasticsearh_sql($sql=”){
$curl = curl_init();
curl_setopt($curl, CURLOPT_URL,你自己的ES路径和端口.’/_xpack/sql?format=json’);
curl_setopt($curl, CURLOPT_HTTPHEADER, [“Content-type:application/json”]);
curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, 0); //让CURL支持HTTPS访问
curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1); //结果转化为字串，而不是直接输出
curl_setopt($curl, CURLOPT_CONNECTTIMEOUT, 300);
curl_setopt($curl, CURLOPT_TIMEOUT, 300);
curl_setopt($curl, CURLOPT_POST, 1);
curl_setopt($curl, CURLOPT_POSTFIELDS, json_encode([‘query’=>$sql]));
$result = curl_exec($curl);
if($result){
$result=json_decode($result,true);
if(isset($result[‘columns’]) && isset($result[‘rows’]) && is_array($result[‘columns’]) && is_array($result[‘rows’])){
$field=[];
foreach ($result[‘columns’] as $key => $value) {
$field[$key]=$value[‘name’];
}
$data=[];
foreach ($result[‘rows’] as $key => $value) {
foreach ($value as $keys => $val) {
$data[$key][$field[$keys]]=$value[$keys];
}
}
return $data;
}
}
return false;
}
/**
* elasticsearh-sql sql语句转ES 当不熟悉原生ES的写法时 可以使用sql转es然后根据该方法返回的数据 进行原生es的查询
*/
function sql2es($sql=”){
$curl = curl_init();
curl_setopt($curl, CURLOPT_URL,你自己的ES路径和端口.’/_xpack/sql/translate’);
curl_setopt($curl, CURLOPT_HTTPHEADER, [“Content-type:application/json”]);
curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, 0);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($curl, CURLOPT_CONNECTTIMEOUT, 300);
curl_setopt($curl, CURLOPT_TIMEOUT, 300);
curl_setopt($curl, CURLOPT_POST, 1);
curl_setopt($curl, CURLOPT_POSTFIELDS, json_encode([‘query’=>$sql]));
$result = curl_exec($curl);
// curl_close($curl);
$result = json_decode($result,true);
dd($result);
}

SQL API的功能其实是将SQL语句翻译成DSL语句给ES执行。