Confused about QueryEntity configuration

[franck102 <fr...@yahoo.com>]

Hi all,

Despite reading the doc many times I can't quite figure out how SQL queries
should be configured. In particular what is query.keyType used for in that
context?
The key type doesn't seem to have any impact on what SQL queries I can
execute on the cache?
Does it simply have to match the cache key type for things to work
internally?

Say for example I have this (pseudo-code) CacheConfiguration:


... what can I / should I use for a keyType in the QueryEntities I define on
the cache? And what will the impact be on the SQL queries I can execute
agains the cache?

Thanks!
Franck




--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Confused-about-QueryEntity-configuration-tp16281.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Confused about QueryEntity configuration

[Vasiliy Sisko <vs...@gridgain.com>]

I'm sorry. I found that you are subscribed but mail put your letter to spam




--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Confused-about-QueryEntity-configuration-tp16281p16357.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Confused about QueryEntity configuration

[franck102 <fr...@yahoo.com>]

Hi Val,

I am able to use _key sucessfully, that is acceptable.

I tried defining an alias, using all combinations of _key/DB name and all
orders, and I couldn't get that to work.

Looking through the code it seems to me that aliases are only used to map
result column names to binary object field names; they are not used to
"rewrite" the query to replace java names with SQL names that the SQL engine
would understand - so aliases probably cannot help here.

Franck



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Confused about QueryEntity configuration

[vkulichenko <va...@gmail.com>]

Franck,

Option #1 should address that. Did you try it?

-Val



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Confused-about-QueryEntity-configuration-tp16281p16385.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Confused about QueryEntity configuration

[franck102 <fr...@yahoo.com>]

Thanks Val, I meant #1 indeed. Using _key works.

What I'd like to achieve is for users to be able to use javaNames in their
queries, rather than "_key" or databaseFieldName: I consider the javaName as
a public API, whereas "_key" or the database field name are implementation
details that are subject to change.

But if I understand correctly there is no way to achieve that with aliases,
unless I duplicate the key in the value object which I would rather not
do...
The aliases it seems are not used to "rewrite" incoming SQL queries, they
are used to map to the binary object after the query has been executed,
right?

Franck



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Confused-about-QueryEntity-configuration-tp16281p16375.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Confused about QueryEntity configuration

[vkulichenko <va...@gmail.com>]

Franck,

#2 in Vasiliy's response suggested to use predefined '_key' field name to
reference the key value. This doesn't require additional configuration.

Moreover, this alias is wrong anyway:

<entry key="myKeyAlias" value="empNo"/>

Because 'key' here is the name of the field in binary object, while 'value'
is the alias. You have it other way around.

Hope this helps.

-Val



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Confused-about-QueryEntity-configuration-tp16281p16371.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Confused about QueryEntity configuration

[franck102 <fr...@yahoo.com>]

Thanks Vasiliy!

#1 works fine, I can go with that

I can't get #2 to work, with this configuration:



I get the exception below:





--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Confused-about-QueryEntity-configuration-tp16281p16359.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Confused about QueryEntity configuration

[Vasiliy Sisko <vs...@gridgain.com>]

Hello @franck102

Please properly subscribe to the mailing list so that the community can
receive email notifications for your messages. To subscribe, send empty
email to user-subscribe@ignite.apache.org and follow simple instructions in
the reply.

'empNo' field is a key field and is not included into stored object
(org.apache.ignite.sample.model.Employees) for usage in query

To extract that field you can use one of the following solutions:
1) Configure an own key field alias in QueryEntity instance. F.e.: 
<property name="keyFieldName" value="empNo"/>
2) Use ‘_key’ alias for the key field in qyery. F.e.: select _key from
Employees
3) Include 'empNo' field into the stored object how you described in message
from Aug 18, 2017; 9:35pm



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Confused-about-QueryEntity-configuration-tp16281p16356.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Confused about QueryEntity configuration

[franck102 <fr...@yahoo.com>]

empNo is in fact the key. 

I have attached a small sample project that demonstrates the problem; I can
in fact make the query work normally if I add keyFieldName=empNo to the
queryEntity configuration.
I don't see why this is required, for example with another cache that uses a
composite key:
TitlesCache: keyFields: { empNo, date_from }

... I can perfectly execute "select empNo from Titles" and I get non-null
values.

To reproduce the problem:
- mvn clean install
- Run the org.apache.ignite.sample.test.ServerTests#queryKey test

The configuration is in ignite-server/src/main/resources/ignite-server.xml

Franck

ignite-binary-sample.zip
<http://apache-ignite-users.70518.x6.nabble.com/file/n16317/ignite-binary-sample.zip>  



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Confused-about-QueryEntity-configuration-tp16281p16317.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Confused about QueryEntity configuration

[vkulichenko <va...@gmail.com>]

JdbcType and QueryEntity configurations are unrelated to each other. The
former configures persistence (i.e. mapping between cache and database),
while the latter configures queryable and indexed fields for Ignite SQL
engine.

It sounds like empNo field is part of value object. If you do not provide
mapping for it in JdbcType#valueFields, then persistence store will not
initialize it and it always be null. I believe this is the reason for your
issue.

-Val



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Confused-about-QueryEntity-configuration-tp16281p16308.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Confused about QueryEntity configuration

[franck102 <fr...@yahoo.com>]

After some experimentation I have one answer, and more questions :)

I guess QueryEntity.keyType is only used to identify which objects will be
considered in the queries - I had forgotten that a cache can contain objects
of multiple types.

I am still struggling to configure queries for a JDBC store though. With
this store configuration:



...and this QueryEntity:



... running 
returns the expected number of results, but the single value inside the
result is null.

If I copy the JdbcTypeField from keyFields and add it to valueFields then
the query works as expected... so question is, are SQL field queries only
able to extract values from valueFields?

I also wonder what the aliases are for? I would prefer to use "SELECT empNo
..." in my queries, but I can't figure out how to configure that?

Thanks!
Franck



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Confused-about-QueryEntity-configuration-tp16281p16286.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.