//format 定义返回的数据类型 适合php的推荐使用json ,除此之外还可以选择 txt,csv,yaml,cbor,smile等
使用sql语句查询有一个弊端 就是开始定义某列数据的类型 后期存储时 存入了错误的数据类型 存储时成功的 但是使用sql语句查询会报错,所有最有效的ES查询方法还是使用原生的
当我们不确定有哪些SQL函数可以直接使用时 ,可以在 /_xpack/sqlformat=json 接口使用query=‘show functions’,会列举出支持的SQL函数;
且SQL api查询的数据 不做限制 最多只能查询到满足条件的数据1000条。
/**
* elasticsearh-sql 查询方法,传入sql语法查询
*/
function elasticsearh_sql($sql=’’){
c
u
r
l
=
c
u
r
l
_
i
n
i
t
(
)
;
c
u
r
l
_
s
e
t
o
p
t
(
curl = curl\_init(); curl\_setopt(
curl=curl_init();curl_setopt(curl, CURLOPT_URL,你自己的ES路径和端口.’/_xpack/sqlformat=json’);
curl_setopt(KaTeX parse error: Undefined control sequence: \[ at position 28: …T\_HTTPHEADER, \̲[̲"Content-type:a…curl, CURLOPT_SSL_VERIFYPEER, 0); //让CURL支持HTTPS访问
curl_setopt(
c
u
r
l
,
C
U
R
L
O
P
T
_
R
E
T
U
R
N
T
R
A
N
S
F
E
R
,
1
)
;
/
/
结
果
转
化
为
字
串
,
而
不
是
直
接
输
出
c
u
r
l
_
s
e
t
o
p
t
(
curl, CURLOPT\_RETURNTRANSFER, 1); //结果转化为字串,而不是直接输出 curl\_setopt(
curl,CURLOPT_RETURNTRANSFER,1);//结果转化为字串,而不是直接输出curl_setopt(curl, CURLOPT_CONNECTTIMEOUT, 300);
curl_setopt(
c
u
r
l
,
C
U
R
L
O
P
T
_
T
I
M
E
O
U
T
,
300
)
;
c
u
r
l
_
s
e
t
o
p
t
(
curl, CURLOPT\_TIMEOUT, 300); curl\_setopt(
curl,CURLOPT_TIMEOUT,300);curl_setopt(curl, CURLOPT_POST, 1);
curl_setopt(KaTeX parse error: Undefined control sequence: \[ at position 41: …, json\_encode(\̲[̲'query'=>sql]));
r
e
s
u
l
t
=
c
u
r
l
_
e
x
e
c
(
result = curl\_exec(
result=curl_exec(curl);
if($result){
r
e
s
u
l
t
=
j
s
o
n
_
d
e
c
o
d
e
(
result=json\_decode(
result=json_decode(result,true);
if(isset(KaTeX parse error: Undefined control sequence: \[ at position 7: result\̲[̲'columns'\]) &&…result[‘rows’]) && is_array(KaTeX parse error: Undefined control sequence: \[ at position 7: result\̲[̲'columns'\]) &&…result[‘rows’])){
KaTeX parse error: Undefined control sequence: \[ at position 7: field=\̲[̲\]; foreach (result[‘columns’] as $key => $value) {
KaTeX parse error: Undefined control sequence: \[ at position 6: field\̲[̲key]=$value[‘name’];
}
KaTeX parse error: Undefined control sequence: \[ at position 6: data=\̲[̲\]; foreach (result[‘rows’] as $key => KaTeX parse error: Expected '}', got 'EOF' at end of input: …) { foreach (value as $keys => $val) {
KaTeX parse error: Undefined control sequence: \[ at position 5: data\̲[̲key][KaTeX parse error: Undefined control sequence: \[ at position 6: field\̲[̲keys]]=KaTeX parse error: Undefined control sequence: \[ at position 6: 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执行。