You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by "Ray Liu (rayliu)" <ra...@cisco.com> on 2017/09/13 06:56:44 UTC
Issue when executing sql query using REST API
Hi all,
I’m trying to execute a sql query using REST API with the following url.
http://127.0.0.1:8080/ignite?cacheName=person&pageSize=1&qry=limit+2&cmd=qryexe&type=Person&
But I got the following error response
{
"successStatus": 1,
"error": "IgniteTest$Person",
"response": null,
"sessionToken": null
}
Here’s the code I used to ingest data to ignite.
public class IgniteTest {
public static void main(String[] args) throws Exception {
try (Ignite ignite = Ignition.start("example-ignite.xml")) {
CacheConfiguration<Integer, Person> personCacheCfg = new CacheConfiguration<>("person");
personCacheCfg.setIndexedTypes(Integer.class, Person.class);
IgniteCache<Integer, Person> personCache = ignite.getOrCreateCache(personCacheCfg);
Person p1 = new Person(1, "John", "Doe", 2000);
Person p2 = new Person(1, "Jane", "Doe", 1000);
Person p3 = new Person(2, "John", "Smith", 1000);
Person p4 = new Person(2, "Jane", "Smith", 2000);
personCache.put(p1.getId(), p1);
personCache.put(p2.getId(), p2);
personCache.put(p3.getId(), p3);
personCache.put(p4.getId(), p4);
}
}
/**
* Person class.
*/
public static class Person implements Serializable {
/** Person id. */
private static int PERSON_ID = 0;
/** Person ID (indexed). */
@QuerySqlField(index = true)
private Integer id;
/** Organization id. */
@QuerySqlField(index = true)
private Integer orgId;
/** First name (not-indexed). */
@QuerySqlField
private String firstName;
/** Last name (not indexed). */
@QuerySqlField
private String lastName;
/** Salary (indexed). */
@QuerySqlField(index = true)
private double salary;
/**
* @param firstName First name.
* @param lastName Last name.
* @param salary Salary.
*/
Person(Integer orgId, String firstName, String lastName, double salary) {
id = PERSON_ID++;
this.orgId = orgId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
}
/**
* @return Organization ID.
*/
public Integer getOrganizationId() {
return orgId;
}
/**
* @return First name.
*/
public String getFirstName() {
return firstName;
}
/**
* @return Last name.
*/
public String getLastName() {
return lastName;
}
/**
* @return Salary.
*/
public double getSalary() {
return salary;
}
/**
* @return Id.
*/
public Integer getId() {
return id;
}
}
}
And debug log generated by ignite
[2017-09-13T14:42:33,059][DEBUG][qtp379430898-73][GridJettyRestProtocol] Handling request [target=/ignite, req=(GET /ignite?cacheName=person&pageSize=1&qry=limit+2&cmd=qryexe&type=Person)@1162648735 org.eclipse.jetty.server.Request@454c9c9f, srvReq=(GET /ignite?cacheName=person&pageSize=1&qry=limit+2&cmd=qryexe&type=Person)@1162648735 org.eclipse.jetty.server.Request@454c9c9f]
[2017-09-13T14:42:33,059][DEBUG][qtp379430898-73][GridJettyRestProtocol] Initialized command request: GridRestRequest [destId=null, clientId=null, addr=/127.0.0.1:50853, cmd=EXECUTE_SQL_QUERY]
[2017-09-13T14:42:33,060][DEBUG][rest-#42%null%][GridRestProcessor] Grid runnable started: rest-proc-worker
[2017-09-13T14:42:33,060][DEBUG][rest-#42%null%][GridRestProcessor] Received request from client: GridRestRequest [destId=null, clientId=null, addr=/127.0.0.1:50853, cmd=EXECUTE_SQL_QUERY]
[2017-09-13T14:42:33,060][DEBUG][rest-#42%null%][GridResourceProcessor] Injecting resources [target=org.apache.ignite.internal.processors.rest.handlers.query.QueryCommandHandler$ExecuteQueryCallable@12f55fbb]
[2017-09-13T14:42:33,060][DEBUG][rest-#42%null%][GridRestProcessor] Grid runnable finished normally: rest-proc-worker
[2017-09-13T14:42:33,060][DEBUG][pub-#310%null%][GridClosureProcessor] Grid runnable started: closure-proc-worker
[2017-09-13T14:42:33,061][DEBUG][pub-#310%null%][GridCacheProcessor] Getting public cache for name: person
[2017-09-13T14:42:33,063][DEBUG][pub-#310%null%][IgniteH2Indexing] Set schema: person
[2017-09-13T14:42:33,064][DEBUG][grid-timeout-worker-#15%null%][GridTimeoutProcessor] Timeout has occurred: CancelableTask [id=a5584d97e51-9027a323-d2b1-49c5-962d-e0dd53b5eba0, endTime=1505284953057, period=3000, cancel=false, task=org.apache.ignite.internal.processors.query.GridQueryProcessor$2@df38437]
[2017-09-13T14:42:33,070][DEBUG][pub-#310%null%][IgniteH2Indexing] Parsed query: `SELECT "person"."PERSON"._KEY, "person"."PERSON"._VAL FROM "person"."PERSON" limit 2` into two step query: GridCacheTwoStepQuery [mapQrys=[GridCacheSqlQuery [qry=SELECT
"person".__Z0._KEY __C0_0,
"person".__Z0._VAL __C0_1
FROM "person".PERSON __Z0 LIMIT 2, paramIdxs=[], cols={__C0_0=GridSqlType [type=4, scale=0, precision=10, displaySize=11, sql=INTEGER], __C0_1=GridSqlType [type=19, scale=0, precision=2147483647, displaySize=2147483647, sql=OTHER]}, alias=null, sort=[], partitioned=true, node=null, derivedPartitions=null]], rdc=GridCacheSqlQuery [qry=SELECT
__C0_0 _KEY,
__C0_1 _VAL
FROM PUBLIC.__T0 LIMIT 2, paramIdxs=[], cols=null, alias=null, sort=null, partitioned=false, node=null, derivedPartitions=null], pageSize=1000, explain=false, originalSql=SELECT
"person".PERSON._KEY,
"person".PERSON._VAL
FROM "person".PERSON LIMIT 2, distributedJoins=false, skipMergeTbl=false, local=false]
[2017-09-13T14:42:33,071][DEBUG][pub-#310%null%][GridReduceQueryExecutor] Sending: [msg=GridH2QueryRequest [reqId=12, caches=[-991716523], topVer=AffinityTopologyVersion [topVer=9, minorTopVer=0], parts=null, qryParts=null, pageSize=1024, qrys=[GridCacheSqlQuery [qry=SELECT
"person".__Z0._KEY __C0_0,
"person".__Z0._VAL __C0_1
FROM "person".PERSON __Z0 LIMIT 2, paramIdxs=[], cols={__C0_0=GridSqlType [type=4, scale=0, precision=10, displaySize=11, sql=INTEGER], __C0_1=GridSqlType [type=19, scale=0, precision=2147483647, displaySize=2147483647, sql=OTHER]}, alias=null, sort=[], partitioned=true, node=null, derivedPartitions=null]], flags=2, tbls=null, timeout=0, params=[], schemaName=person], nodes=[TcpDiscoveryNode [id=ad9ca7a8-4ed2-47ac-b5fe-074f3b4a7685, addrs=[0:0:0:0:0:0:0:1, 10.140.48.140, 127.0.0.1, 2001:420:589a:1250:516a:c5d4:fe7d:863c], sockAddrs=[/10.140.48.140:47500, /0:0:0:0:0:0:0:1:47500, /127.0.0.1:47500, /2001:420:589a:1250:516a:c5d4:fe7d:863c:47500], discPort=47500, order=1, intOrder=1, lastExchangeTime=1505284895513, loc=true, ver=2.1.0#20170721-sha1:a6ca5c8a, isClient=false]], specialize=null]
[2017-09-13T14:42:33,078][DEBUG][pub-#310%null%][GridCacheProcessor] Getting internal cache adapter: person
[2017-09-13T14:42:33,084][DEBUG][pub-#310%null%][GridReduceQueryExecutor] Processed response: ad9ca7a8-4ed2-47ac-b5fe-074f3b4a7685->ad9ca7a8-4ed2-47ac-b5fe-074f3b4a7685 GridQueryNextPageResponse [qryReqId=12, segmentId=0, qry=0, page=0, allRows=2, cols=2, retry=null, valsSize=0, rowsSize=2]
[2017-09-13T14:42:33,085][DEBUG][pub-#310%null%][GridMapQueryExecutor] Processed request: ad9ca7a8-4ed2-47ac-b5fe-074f3b4a7685->ad9ca7a8-4ed2-47ac-b5fe-074f3b4a7685 GridH2QueryRequest [reqId=12, caches=[-991716523], topVer=AffinityTopologyVersion [topVer=9, minorTopVer=0], parts=null, qryParts=null, pageSize=1024, qrys=[GridCacheSqlQuery [qry=SELECT
"person".__Z0._KEY __C0_0,
"person".__Z0._VAL __C0_1
FROM "person".PERSON __Z0 LIMIT 2, paramIdxs=[], cols={__C0_0=GridSqlType [type=4, scale=0, precision=10, displaySize=11, sql=INTEGER], __C0_1=GridSqlType [type=19, scale=0, precision=2147483647, displaySize=2147483647, sql=OTHER]}, alias=null, sort=[], partitioned=true, node=null, derivedPartitions=null]], flags=2, tbls=null, timeout=0, params=[], schemaName=person]
[2017-09-13T14:42:33,086][DEBUG][pub-#310%null%][GridReduceQueryExecutor] Sending: [msg=GridQueryCancelRequest [qryReqId=12], nodes=[TcpDiscoveryNode [id=ad9ca7a8-4ed2-47ac-b5fe-074f3b4a7685, addrs=[0:0:0:0:0:0:0:1, 10.140.48.140, 127.0.0.1, 2001:420:589a:1250:516a:c5d4:fe7d:863c], sockAddrs=[/10.140.48.140:47500, /0:0:0:0:0:0:0:1:47500, /127.0.0.1:47500, /2001:420:589a:1250:516a:c5d4:fe7d:863c:47500], discPort=47500, order=1, intOrder=1, lastExchangeTime=1505284895513, loc=true, ver=2.1.0#20170721-sha1:a6ca5c8a, isClient=false]], specialize=null]
[2017-09-13T14:42:33,087][DEBUG][pub-#310%null%][GridMapQueryExecutor] Processed request: ad9ca7a8-4ed2-47ac-b5fe-074f3b4a7685->ad9ca7a8-4ed2-47ac-b5fe-074f3b4a7685 GridQueryCancelRequest [qryReqId=12]
[2017-09-13T14:42:33,088][DEBUG][pub-#310%null%][GridClosureProcessor] Grid runnable finished normally: closure-proc-worker
[2017-09-13T14:42:33,088][DEBUG][qtp379430898-73][GridJettyRestProtocol] Parsed command response into JSON object: {"successStatus":1,"error":"IgniteTest$Person","response":null,"sessionToken":null}
[2017-09-13T14:42:33,089][DEBUG][qtp379430898-73][GridJettyRestProtocol] Processed HTTP request [action=/ignite, jsonRes=GridRestResponse [successStatus=1, sesTokStr=null, err=IgniteTest$Person, obj=null], req=(GET /ignite?cacheName=person&pageSize=1&qry=limit+2&cmd=qryexe&type=Person)@1162648735 org.eclipse.jetty.server.Request@454c9c9f<ma...@454c9c9f>]
Please help me.
Thanks
Re: Issue when executing sql query using REST API
Posted by Ray <ra...@cisco.com>.
This problem is resolved after I add proper getter/setter in the POJO class
and deploy the jar file under the IgniteHome/lib folder.
Also, I'd like to mention the POJO class written in scala case class is not
working for the query exe api.
Had to use a java POJO class if you want to get this API working.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Issue when executing sql query using REST API
Posted by Alexey Kukushkin <ku...@gmail.com>.
Hi Ray,
The REST response is in plan text so you do not need those dependencies if
you do nothing with the received plain text. But looking at your exception
my guess was that your application deserialises received text into POJO,
cannot find a deserialiser and displays that error. Can you please confirm.
On Fri, Sep 15, 2017 at 2:37 PM, Ray <ra...@cisco.com> wrote:
> From Ignite document
> https://apacheignite.readme.io/docs/rest-api#section-sql-query-execute.
> the response should be a plain text JSONObject, why do I have to have these
> dependencies on client side?
>
> Could you please advice how to get this API to working if I'm using Postman
> or curl as a client?
>
> Thanks
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
--
Best regards,
Alexey
Re: Issue when executing sql query using REST API
Posted by Ray <ra...@cisco.com>.
From Ignite document
https://apacheignite.readme.io/docs/rest-api#section-sql-query-execute.
the response should be a plain text JSONObject, why do I have to have these
dependencies on client side?
Could you please advice how to get this API to working if I'm using Postman
or curl as a client?
Thanks
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Issue when executing sql query using REST API
Posted by Ray <ra...@cisco.com>.
Yes, this exception is from client side.
I'm using Postman as a REST client to retrieve the data from ignite.
I don't think postman will be able to add these dependencies.
From my understanding, the results returned from Ignite sql query REST call
is plain text, is it right?
But from your explanation I sense that the results returned from Ignite sql
query REST call is a binary object and will have to deserialized by client.
If the Ignite REST API works the latter way, what's point of having a REST
API?
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Issue when executing sql query using REST API
Posted by Alexey Kukushkin <ku...@gmail.com>.
Hi Ray,
The exception seems not related to Ignite to me. I believe you see it on
the client side. Are you sure you have all Jackson runtime dependencies?
For example, I have jackson-annotations, jackson-core and jackson-databind.
Id you do not have it then you forgot to reference ignite-rest-http that
brings those dependencies in your client POM:
runtime group: 'org.apache.ignite', name: 'ignite-rest-http', version:
igniteVersion
Re: Issue when executing sql query using REST API
Posted by "Ray Liu (rayliu)" <ra...@cisco.com>.
Hi Alexey,
Thanks for the information, the SqlFieldsQuery works fine for me.
But I still want to try out the sql query.
After uploading the jar file in the $IgniteHome/lib folder, a new exception shows up.
The rest response says
No serializer found for class Ignite$Test and no properties discovered to create BeanSerializer
Any ideas?
From: Alexey Kukushkin <ku...@gmail.com>
Reply-To: "user@ignite.apache.org" <us...@ignite.apache.org>
Date: Wednesday, 13 September 2017 at 17:46
To: "user@ignite.apache.org" <us...@ignite.apache.org>
Subject: Re: Issue when executing sql query using REST API
Hi Ray,
You can avoid deploying classes to the server nodes by using Ignite API that does not require building POJOs (IgniteTest$Person in your case).
* For SQL API use SqlFieldsQuery<https://apacheignite.readme.io/docs/sql-queries#section-sqlfieldsqueries> that, unlike SqlQuery, does not build a full object
* For REST API use command "qryfldexe" <https://apacheignite.readme.io/docs/rest-api#sql-fields-query-execute> (that will result in SqlFieldsQuery)
* For Java API use IgniteBinary API<https://apacheignite.readme.io/docs/binary-marshaller#binaryobject-cache-api>.
Also, Ignite has Zero deployment feature<https://apacheignite.readme.io/docs/zero-deployment> applicable only to the computational code (compute closures and tasks) that enables automated deployment. But as I said it is not applicable to the metadata (cache) classes.
On Wed, Sep 13, 2017 at 12:23 PM, Ray Liu (rayliu) <ra...@cisco.com>> wrote:
Do I have to deploy my classes on all of the server nodes manually?
Every time I added some new classes, I have to do this again?
From: Alexey Kukushkin <ku...@gmail.com>>
Reply-To: "user@ignite.apache.org<ma...@ignite.apache.org>" <us...@ignite.apache.org>>
Date: Wednesday, 13 September 2017 at 17:20
To: "user@ignite.apache.org<ma...@ignite.apache.org>" <us...@ignite.apache.org>>
Subject: Re: Issue when executing sql query using REST API
Ray, I am sorry - just read your second email and I realised I originally misunderstood your question. You guessed right, your classes need to be deployed on the server nodes and on the server nodes you need to set the USER_LIBS environment variable like:
export USER_LIBS=<path to your IgniteTest JAR>/<your IgniteTest>.jar
On Wed, Sep 13, 2017 at 12:15 PM, Ray Liu (rayliu) <ra...@cisco.com>> wrote:
Can you share your configuration xml?
I think it’s a binary masharller configuration issue.
From: Alexey Kukushkin <ku...@gmail.com>>
Reply-To: "user@ignite.apache.org<ma...@ignite.apache.org>" <us...@ignite.apache.org>>
Date: Wednesday, 13 September 2017 at 17:12
To: "user@ignite.apache.org<ma...@ignite.apache.org>" <us...@ignite.apache.org>>
Subject: Re: Issue when executing sql query using REST API
Hi Ray,
Just copied your code into a simple single file sample project, ran it and your query worked fine for me:
1. Request URL:
http://127.0.0.1:8080/ignite?cacheName=person&pageSize=1&qry=limit+2&cmd=qryexe&type=Person&
2. Request Method:
GET
3. Status Code:
200 OK
4. ---------------------
5. {"successStatus":0,"error":null,"response":{"items":[{"key":0,"value":{"id":0,"firstName":"John","lastName":"Doe","salary":2000.0,"organizationId":1}}],"last":false,"fieldsMetadata":[],"queryId":2},"sessionToken":null}
--
Best regards,
Alexey
--
Best regards,
Alexey
Re: Issue when executing sql query using REST API
Posted by Alexey Kukushkin <ku...@gmail.com>.
Hi Ray,
You can avoid deploying classes to the server nodes by using Ignite API
that does not require building POJOs (IgniteTest$Person in your case).
- For SQL API use SqlFieldsQuery
<https://apacheignite.readme.io/docs/sql-queries#section-sqlfieldsqueries>
that, unlike SqlQuery, does not build a full object
- For REST API use command "qryfldexe"
<https://apacheignite.readme.io/docs/rest-api#sql-fields-query-execute>(that
will result in SqlFieldsQuery)
- For Java API use IgniteBinary API
<https://apacheignite.readme.io/docs/binary-marshaller#binaryobject-cache-api>
.
Also, Ignite has Zero deployment feature
<https://apacheignite.readme.io/docs/zero-deployment> applicable only to
the computational code (compute closures and tasks) that enables automated
deployment. But as I said it is not applicable to the metadata (cache)
classes.
On Wed, Sep 13, 2017 at 12:23 PM, Ray Liu (rayliu) <ra...@cisco.com> wrote:
> Do I have to deploy my classes on all of the server nodes manually?
>
> Every time I added some new classes, I have to do this again?
>
>
>
>
>
> *From: *Alexey Kukushkin <ku...@gmail.com>
> *Reply-To: *"user@ignite.apache.org" <us...@ignite.apache.org>
> *Date: *Wednesday, 13 September 2017 at 17:20
>
> *To: *"user@ignite.apache.org" <us...@ignite.apache.org>
> *Subject: *Re: Issue when executing sql query using REST API
>
>
>
> Ray, I am sorry - just read your second email and I realised I originally
> misunderstood your question. You guessed right, your classes need to be
> deployed on the server nodes and on the server nodes you need to set the
> USER_LIBS environment variable like:
>
> export USER_LIBS=<path to your IgniteTest JAR>/<your IgniteTest>.jar
>
>
>
> On Wed, Sep 13, 2017 at 12:15 PM, Ray Liu (rayliu) <ra...@cisco.com>
> wrote:
>
> Can you share your configuration xml?
>
> I think it’s a binary masharller configuration issue.
>
>
>
> *From: *Alexey Kukushkin <ku...@gmail.com>
> *Reply-To: *"user@ignite.apache.org" <us...@ignite.apache.org>
> *Date: *Wednesday, 13 September 2017 at 17:12
> *To: *"user@ignite.apache.org" <us...@ignite.apache.org>
> *Subject: *Re: Issue when executing sql query using REST API
>
>
>
> Hi Ray,
>
>
>
> Just copied your code into a simple single file sample project, ran it and
> your query worked fine for me:
>
>
>
> *1.* *Request URL:*
>
> http://127.0.0.1:8080/ignite?cacheName=person&pageSize=1&
> qry=limit+2&cmd=qryexe&type=Person&
>
> *2.* *Request Method:*
>
> GET
>
> *3.* *Status Code:*
>
> 200 OK
>
> 4. ---------------------
>
> 5. {"successStatus":0,"error":null,"response":{"items":[{"
> key":0,"value":{"id":0,"firstName":"John","lastName":"
> Doe","salary":2000.0,"organizationId":1}}],"last":
> false,"fieldsMetadata":[],"queryId":2},"sessionToken":null}
>
>
>
>
>
> --
>
> Best regards,
>
> Alexey
>
--
Best regards,
Alexey
Re: Issue when executing sql query using REST API
Posted by "Ray Liu (rayliu)" <ra...@cisco.com>.
Do I have to deploy my classes on all of the server nodes manually?
Every time I added some new classes, I have to do this again?
From: Alexey Kukushkin <ku...@gmail.com>
Reply-To: "user@ignite.apache.org" <us...@ignite.apache.org>
Date: Wednesday, 13 September 2017 at 17:20
To: "user@ignite.apache.org" <us...@ignite.apache.org>
Subject: Re: Issue when executing sql query using REST API
Ray, I am sorry - just read your second email and I realised I originally misunderstood your question. You guessed right, your classes need to be deployed on the server nodes and on the server nodes you need to set the USER_LIBS environment variable like:
export USER_LIBS=<path to your IgniteTest JAR>/<your IgniteTest>.jar
On Wed, Sep 13, 2017 at 12:15 PM, Ray Liu (rayliu) <ra...@cisco.com>> wrote:
Can you share your configuration xml?
I think it’s a binary masharller configuration issue.
From: Alexey Kukushkin <ku...@gmail.com>>
Reply-To: "user@ignite.apache.org<ma...@ignite.apache.org>" <us...@ignite.apache.org>>
Date: Wednesday, 13 September 2017 at 17:12
To: "user@ignite.apache.org<ma...@ignite.apache.org>" <us...@ignite.apache.org>>
Subject: Re: Issue when executing sql query using REST API
Hi Ray,
Just copied your code into a simple single file sample project, ran it and your query worked fine for me:
1. Request URL:
http://127.0.0.1:8080/ignite?cacheName=person&pageSize=1&qry=limit+2&cmd=qryexe&type=Person&
2. Request Method:
GET
3. Status Code:
200 OK
4. ---------------------
5. {"successStatus":0,"error":null,"response":{"items":[{"key":0,"value":{"id":0,"firstName":"John","lastName":"Doe","salary":2000.0,"organizationId":1}}],"last":false,"fieldsMetadata":[],"queryId":2},"sessionToken":null}
--
Best regards,
Alexey
Re: Issue when executing sql query using REST API
Posted by Alexey Kukushkin <ku...@gmail.com>.
Ray, I am sorry - just read your second email and I realised I originally
misunderstood your question. You guessed right, your classes need to be
deployed on the server nodes and on the server nodes you need to set the
USER_LIBS environment variable like:
export USER_LIBS=<path to your IgniteTest JAR>/<your IgniteTest>.jar
On Wed, Sep 13, 2017 at 12:15 PM, Ray Liu (rayliu) <ra...@cisco.com> wrote:
> Can you share your configuration xml?
>
> I think it’s a binary masharller configuration issue.
>
>
>
> *From: *Alexey Kukushkin <ku...@gmail.com>
> *Reply-To: *"user@ignite.apache.org" <us...@ignite.apache.org>
> *Date: *Wednesday, 13 September 2017 at 17:12
> *To: *"user@ignite.apache.org" <us...@ignite.apache.org>
> *Subject: *Re: Issue when executing sql query using REST API
>
>
>
> Hi Ray,
>
>
>
> Just copied your code into a simple single file sample project, ran it and
> your query worked fine for me:
>
>
>
> *1. **Request URL:*
>
> http://127.0.0.1:8080/ignite?cacheName=person&pageSize=1&
> qry=limit+2&cmd=qryexe&type=Person&
>
> *2. **Request Method:*
>
> GET
>
> *3. **Status Code:*
>
> 200 OK
>
> 4. ---------------------
>
> 5. {"successStatus":0,"error":null,"response":{"items":[{"
> key":0,"value":{"id":0,"firstName":"John","lastName":"
> Doe","salary":2000.0,"organizationId":1}}],"last":
> false,"fieldsMetadata":[],"queryId":2},"sessionToken":null}
>
--
Best regards,
Alexey
Re: Issue when executing sql query using REST API
Posted by "Ray Liu (rayliu)" <ra...@cisco.com>.
Can you share your configuration xml?
I think it’s a binary masharller configuration issue.
From: Alexey Kukushkin <ku...@gmail.com>
Reply-To: "user@ignite.apache.org" <us...@ignite.apache.org>
Date: Wednesday, 13 September 2017 at 17:12
To: "user@ignite.apache.org" <us...@ignite.apache.org>
Subject: Re: Issue when executing sql query using REST API
Hi Ray,
Just copied your code into a simple single file sample project, ran it and your query worked fine for me:
1. Request URL:
http://127.0.0.1:8080/ignite?cacheName=person&pageSize=1&qry=limit+2&cmd=qryexe&type=Person&
2. Request Method:
GET
3. Status Code:
200 OK
4. ---------------------
5. {"successStatus":0,"error":null,"response":{"items":[{"key":0,"value":{"id":0,"firstName":"John","lastName":"Doe","salary":2000.0,"organizationId":1}}],"last":false,"fieldsMetadata":[],"queryId":2},"sessionToken":null}
Re: Issue when executing sql query using REST API
Posted by Alexey Kukushkin <ku...@gmail.com>.
Hi Ray,
Just copied your code into a simple single file sample project, ran it and
your query worked fine for me:
1. Request URL:
http://127.0.0.1:8080/ignite?cacheName=person&pageSize=1&qry=limit+2&cmd=qryexe&type=Person&
2. Request Method:
GET
3. Status Code:
200 OK
4. ---------------------
5.
{"successStatus":0,"error":null,"response":{"items":[{"key":0,"value":{"id":0,"firstName":"John","lastName":"Doe","salary":2000.0,"organizationId":1}}],"last":false,"fieldsMetadata":[],"queryId":2},"sessionToken":null}
Re: Issue when executing sql query using REST API
Posted by "Ray Liu (rayliu)" <ra...@cisco.com>.
Also sql scan query is not working with the following rest call.
http://127.0.0.1:8080/ignite?cmd=qryscanexe&pageSize=10&cacheName=person&className=IgniteTest$Person
And the error response is
{
"successStatus": 1,
"error": "Failed to find target class: IgniteTest$Person",
"response": null,
"sessionToken": null
}
But sql fields query is working with the following rest call.
http://127.0.0.1:8080/ignite?cmd=qryfldexe&pageSize=10&cacheName=person&qry=select+firstName%2C+lastName+from+Person
So I’m guessing the Person class should be somehow uploaded to the lib folder under Ignitehome folder?
From: "Ray Liu (rayliu)" <ra...@cisco.com>
Reply-To: "user@ignite.apache.org" <us...@ignite.apache.org>
Date: Wednesday, 13 September 2017 at 14:56
To: "user@ignite.apache.org" <us...@ignite.apache.org>
Subject: Issue when executing sql query using REST API
Hi all,
I’m trying to execute a sql query using REST API with the following url.
http://127.0.0.1:8080/ignite?cacheName=person&pageSize=1&qry=limit+2&cmd=qryexe&type=Person&
But I got the following error response
{
"successStatus": 1,
"error": "IgniteTest$Person",
"response": null,
"sessionToken": null
}
Here’s the code I used to ingest data to ignite.
public class IgniteTest {
public static void main(String[] args) throws Exception {
try (Ignite ignite = Ignition.start("example-ignite.xml")) {
CacheConfiguration<Integer, Person> personCacheCfg = new CacheConfiguration<>("person");
personCacheCfg.setIndexedTypes(Integer.class, Person.class);
IgniteCache<Integer, Person> personCache = ignite.getOrCreateCache(personCacheCfg);
Person p1 = new Person(1, "John", "Doe", 2000);
Person p2 = new Person(1, "Jane", "Doe", 1000);
Person p3 = new Person(2, "John", "Smith", 1000);
Person p4 = new Person(2, "Jane", "Smith", 2000);
personCache.put(p1.getId(), p1);
personCache.put(p2.getId(), p2);
personCache.put(p3.getId(), p3);
personCache.put(p4.getId(), p4);
}
}
/**
* Person class.
*/
public static class Person implements Serializable {
/** Person id. */
private static int PERSON_ID = 0;
/** Person ID (indexed). */
@QuerySqlField(index = true)
private Integer id;
/** Organization id. */
@QuerySqlField(index = true)
private Integer orgId;
/** First name (not-indexed). */
@QuerySqlField
private String firstName;
/** Last name (not indexed). */
@QuerySqlField
private String lastName;
/** Salary (indexed). */
@QuerySqlField(index = true)
private double salary;
/**
* @param firstName First name.
* @param lastName Last name.
* @param salary Salary.
*/
Person(Integer orgId, String firstName, String lastName, double salary) {
id = PERSON_ID++;
this.orgId = orgId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
}
/**
* @return Organization ID.
*/
public Integer getOrganizationId() {
return orgId;
}
/**
* @return First name.
*/
public String getFirstName() {
return firstName;
}
/**
* @return Last name.
*/
public String getLastName() {
return lastName;
}
/**
* @return Salary.
*/
public double getSalary() {
return salary;
}
/**
* @return Id.
*/
public Integer getId() {
return id;
}
}
}
And debug log generated by ignite
[2017-09-13T14:42:33,059][DEBUG][qtp379430898-73][GridJettyRestProtocol] Handling request [target=/ignite, req=(GET /ignite?cacheName=person&pageSize=1&qry=limit+2&cmd=qryexe&type=Person)@1162648735 org.eclipse.jetty.server.Request@454c9c9f, srvReq=(GET /ignite?cacheName=person&pageSize=1&qry=limit+2&cmd=qryexe&type=Person)@1162648735 org.eclipse.jetty.server.Request@454c9c9f]
[2017-09-13T14:42:33,059][DEBUG][qtp379430898-73][GridJettyRestProtocol] Initialized command request: GridRestRequest [destId=null, clientId=null, addr=/127.0.0.1:50853, cmd=EXECUTE_SQL_QUERY]
[2017-09-13T14:42:33,060][DEBUG][rest-#42%null%][GridRestProcessor] Grid runnable started: rest-proc-worker
[2017-09-13T14:42:33,060][DEBUG][rest-#42%null%][GridRestProcessor] Received request from client: GridRestRequest [destId=null, clientId=null, addr=/127.0.0.1:50853, cmd=EXECUTE_SQL_QUERY]
[2017-09-13T14:42:33,060][DEBUG][rest-#42%null%][GridResourceProcessor] Injecting resources [target=org.apache.ignite.internal.processors.rest.handlers.query.QueryCommandHandler$ExecuteQueryCallable@12f55fbb]
[2017-09-13T14:42:33,060][DEBUG][rest-#42%null%][GridRestProcessor] Grid runnable finished normally: rest-proc-worker
[2017-09-13T14:42:33,060][DEBUG][pub-#310%null%][GridClosureProcessor] Grid runnable started: closure-proc-worker
[2017-09-13T14:42:33,061][DEBUG][pub-#310%null%][GridCacheProcessor] Getting public cache for name: person
[2017-09-13T14:42:33,063][DEBUG][pub-#310%null%][IgniteH2Indexing] Set schema: person
[2017-09-13T14:42:33,064][DEBUG][grid-timeout-worker-#15%null%][GridTimeoutProcessor] Timeout has occurred: CancelableTask [id=a5584d97e51-9027a323-d2b1-49c5-962d-e0dd53b5eba0, endTime=1505284953057, period=3000, cancel=false, task=org.apache.ignite.internal.processors.query.GridQueryProcessor$2@df38437]
[2017-09-13T14:42:33,070][DEBUG][pub-#310%null%][IgniteH2Indexing] Parsed query: `SELECT "person"."PERSON"._KEY, "person"."PERSON"._VAL FROM "person"."PERSON" limit 2` into two step query: GridCacheTwoStepQuery [mapQrys=[GridCacheSqlQuery [qry=SELECT
"person".__Z0._KEY __C0_0,
"person".__Z0._VAL __C0_1
FROM "person".PERSON __Z0 LIMIT 2, paramIdxs=[], cols={__C0_0=GridSqlType [type=4, scale=0, precision=10, displaySize=11, sql=INTEGER], __C0_1=GridSqlType [type=19, scale=0, precision=2147483647, displaySize=2147483647, sql=OTHER]}, alias=null, sort=[], partitioned=true, node=null, derivedPartitions=null]], rdc=GridCacheSqlQuery [qry=SELECT
__C0_0 _KEY,
__C0_1 _VAL
FROM PUBLIC.__T0 LIMIT 2, paramIdxs=[], cols=null, alias=null, sort=null, partitioned=false, node=null, derivedPartitions=null], pageSize=1000, explain=false, originalSql=SELECT
"person".PERSON._KEY,
"person".PERSON._VAL
FROM "person".PERSON LIMIT 2, distributedJoins=false, skipMergeTbl=false, local=false]
[2017-09-13T14:42:33,071][DEBUG][pub-#310%null%][GridReduceQueryExecutor] Sending: [msg=GridH2QueryRequest [reqId=12, caches=[-991716523], topVer=AffinityTopologyVersion [topVer=9, minorTopVer=0], parts=null, qryParts=null, pageSize=1024, qrys=[GridCacheSqlQuery [qry=SELECT
"person".__Z0._KEY __C0_0,
"person".__Z0._VAL __C0_1
FROM "person".PERSON __Z0 LIMIT 2, paramIdxs=[], cols={__C0_0=GridSqlType [type=4, scale=0, precision=10, displaySize=11, sql=INTEGER], __C0_1=GridSqlType [type=19, scale=0, precision=2147483647, displaySize=2147483647, sql=OTHER]}, alias=null, sort=[], partitioned=true, node=null, derivedPartitions=null]], flags=2, tbls=null, timeout=0, params=[], schemaName=person], nodes=[TcpDiscoveryNode [id=ad9ca7a8-4ed2-47ac-b5fe-074f3b4a7685, addrs=[0:0:0:0:0:0:0:1, 10.140.48.140, 127.0.0.1, 2001:420:589a:1250:516a:c5d4:fe7d:863c], sockAddrs=[/10.140.48.140:47500, /0:0:0:0:0:0:0:1:47500, /127.0.0.1:47500, /2001:420:589a:1250:516a:c5d4:fe7d:863c:47500], discPort=47500, order=1, intOrder=1, lastExchangeTime=1505284895513, loc=true, ver=2.1.0#20170721-sha1:a6ca5c8a, isClient=false]], specialize=null]
[2017-09-13T14:42:33,078][DEBUG][pub-#310%null%][GridCacheProcessor] Getting internal cache adapter: person
[2017-09-13T14:42:33,084][DEBUG][pub-#310%null%][GridReduceQueryExecutor] Processed response: ad9ca7a8-4ed2-47ac-b5fe-074f3b4a7685->ad9ca7a8-4ed2-47ac-b5fe-074f3b4a7685 GridQueryNextPageResponse [qryReqId=12, segmentId=0, qry=0, page=0, allRows=2, cols=2, retry=null, valsSize=0, rowsSize=2]
[2017-09-13T14:42:33,085][DEBUG][pub-#310%null%][GridMapQueryExecutor] Processed request: ad9ca7a8-4ed2-47ac-b5fe-074f3b4a7685->ad9ca7a8-4ed2-47ac-b5fe-074f3b4a7685 GridH2QueryRequest [reqId=12, caches=[-991716523], topVer=AffinityTopologyVersion [topVer=9, minorTopVer=0], parts=null, qryParts=null, pageSize=1024, qrys=[GridCacheSqlQuery [qry=SELECT
"person".__Z0._KEY __C0_0,
"person".__Z0._VAL __C0_1
FROM "person".PERSON __Z0 LIMIT 2, paramIdxs=[], cols={__C0_0=GridSqlType [type=4, scale=0, precision=10, displaySize=11, sql=INTEGER], __C0_1=GridSqlType [type=19, scale=0, precision=2147483647, displaySize=2147483647, sql=OTHER]}, alias=null, sort=[], partitioned=true, node=null, derivedPartitions=null]], flags=2, tbls=null, timeout=0, params=[], schemaName=person]
[2017-09-13T14:42:33,086][DEBUG][pub-#310%null%][GridReduceQueryExecutor] Sending: [msg=GridQueryCancelRequest [qryReqId=12], nodes=[TcpDiscoveryNode [id=ad9ca7a8-4ed2-47ac-b5fe-074f3b4a7685, addrs=[0:0:0:0:0:0:0:1, 10.140.48.140, 127.0.0.1, 2001:420:589a:1250:516a:c5d4:fe7d:863c], sockAddrs=[/10.140.48.140:47500, /0:0:0:0:0:0:0:1:47500, /127.0.0.1:47500, /2001:420:589a:1250:516a:c5d4:fe7d:863c:47500], discPort=47500, order=1, intOrder=1, lastExchangeTime=1505284895513, loc=true, ver=2.1.0#20170721-sha1:a6ca5c8a, isClient=false]], specialize=null]
[2017-09-13T14:42:33,087][DEBUG][pub-#310%null%][GridMapQueryExecutor] Processed request: ad9ca7a8-4ed2-47ac-b5fe-074f3b4a7685->ad9ca7a8-4ed2-47ac-b5fe-074f3b4a7685 GridQueryCancelRequest [qryReqId=12]
[2017-09-13T14:42:33,088][DEBUG][pub-#310%null%][GridClosureProcessor] Grid runnable finished normally: closure-proc-worker
[2017-09-13T14:42:33,088][DEBUG][qtp379430898-73][GridJettyRestProtocol] Parsed command response into JSON object: {"successStatus":1,"error":"IgniteTest$Person","response":null,"sessionToken":null}
[2017-09-13T14:42:33,089][DEBUG][qtp379430898-73][GridJettyRestProtocol] Processed HTTP request [action=/ignite, jsonRes=GridRestResponse [successStatus=1, sesTokStr=null, err=IgniteTest$Person, obj=null], req=(GET /ignite?cacheName=person&pageSize=1&qry=limit+2&cmd=qryexe&type=Person)@1162648735 org.eclipse.jetty.server.Request@454c9c9f<ma...@454c9c9f>]
Please help me.
Thanks