Siddhi CEP带WHERE子句的查询Payload格式问题

问题描述 #

已通过以下Siddhi代码完成RDBMS数据插入：

@App:name("CustomerInfoCreator")
@App:description("Consume events from HTTP and write to TEST_DB")

@source(type = 'http', receiver.url = "http://0.0.0.0:8006/production_cust_information",
    @map(type = 'json'))
define stream CustomerStream (id string, customerName string, cibil float, outsandingLoanAmt float, salary float, phoneNumber string, location string, status string, loanType string, loanAmt float, approvalDecision string);

@store(type='rdbms', jdbc.url="jdbc:sqlserver://localhost:1433;databaseName=samplesiddhi;sendStringParametersAsUnicode=false;encrypt=false", username="sa", password="pwd", jdbc.driver.name="com.microsoft.sqlserver.jdbc.SQLServerDriver")
define table CustomerLoanApplication  (id string, customerName string, cibil float, outsandingLoanAmt float, salary float, phoneNumber string, location string, status string, loanType string, loanAmt float, approvalDecision string);

-- Store all events to the table
@info(name = 'query1')
from CustomerStream
insert into CustomerLoanApplication

@App:description("Consume events from HTTP and write to TEST_DB")

@source(type = 'http', receiver.url = "http://0.0.0.0:8006/production_cust_information",
    @map(type = 'json'))
define stream CustomerStream (id string, customerName string, cibil float, outsandingLoanAmt float, salary float, phoneNumber string, location string, status string, loanType string, loanAmt float, approvalDecision string);

@store(type='rdbms', jdbc.url="jdbc:sqlserver://localhost:1433;databaseName=samplesiddhi;sendStringParametersAsUnicode=false;encrypt=false", username="sa", password="$9Lserver", jdbc.driver.name="com.microsoft.sqlserver.jdbc.SQLServerDriver")
define table CustomerLoanApplication  (id string, customerName string, cibil float, outsandingLoanAmt float, salary float, phoneNumber string, location string, status string, loanType string, loanAmt float, approvalDecision string);

-- Store all events to the table
@info(name = 'query1')
from CustomerStream
insert into CustomerLoanApplication

数据插入成功后，可通过以下REST API请求获取全部记录：

POST: https://localhost:9743/query

请求Payload：

{
   "appName":"CustomerInfoCreator",
   "query":"from  CustomerLoanApplication select * limit 10 "
}

但使用WHERE子句筛选数据时，如下请求Payload触发语法错误：

POST : https://localhost:9743/query

错误Payload：

{
   "appName":"CustomerInfoCreator",
   "query":"from  CustomerLoanApplication(phoneNumber = '587488848484') select * limit 10 "
}

返回错误信息：

{
    "code": 1,
    "type": "error",
    "message": "Cannot query: Error between @ Line: 1. Position: 0 and @ Line: 1. Position: 29. Syntax error in SiddhiQL, mismatched input '(' expecting <EOF>."
}

正确的查询格式 #

SiddhiQL不支持在表名后用括号添加过滤条件，需使用WHERE关键字来指定筛选条件，正确的请求Payload如下：

{
   "appName":"CustomerInfoCreator",
   "query":"from CustomerLoanApplication where phoneNumber = '587488848484' select * limit 10"
}

语法说明 #

在SiddhiQL中，查询表数据并添加过滤条件的标准语法结构为：

from <表名> where <过滤条件> select <字段列表> [limit <数量>]

需注意：

• 必须使用WHERE关键字来定义过滤条件，而非将条件放在表名后的括号内
• 过滤条件的写法与标准SQL类似，支持常见的比较运算符（=, !=, >, <等）和逻辑运算符（AND, OR等）

内容的提问来源于stack exchange，提问作者ksv