Missing shu is a cross-cache associated query.

classic Classic list List threaded Threaded
2 messages Options
hulitao198758 hulitao198758
Reply | Threaded
Open this post in threaded view
|

Missing shu is a cross-cache associated query.

My ignite configuration file looks something like this.


<property name="cacheConfiguration">
    <list>

       
        <bean class="org.apache.ignite.configuration.CacheConfiguration">
            <property name='dataRegionName' value='Subscription_Region'/>
            <property name="name" value="SubscriptionOrderCache"/>
            <property name="cacheMode" value="PARTITIONED"/>
            <property name="atomicityMode" value="TRANSACTIONAL"/>
            <property name="backups" value="0"/>
            <property name="rebalanceMode" value="SYNC"/>
            <property name="rebalanceBatchSize" value="#{2 * 1024 * 1024}"/>
            <property name="rebalanceThrottle" value="100"/>

            <property name="writeBehindEnabled" value="true"/>
            <property name="writeBehindFlushThreadCount" value="10"/>
            <property name="writeBehindFlushFrequency" value="100"/>
            <property name="writeBehindFlushSize" value="51200"/>

            <property name="queryEntities">
                <list>
                    <bean class="org.apache.ignite.cache.QueryEntity">
                        <property name="keyType" value="java.lang.Long"/>
                        <property name="valueType"
value="subscription.domain.subscription.entity.SubscriptionOrder"/>
                        <property name="keyFieldName" value="uuid"/>

                        <property name="keyFields">
                            <list>
                                <value>uuid</value>
                            </list>
                        </property>

                        <property name="fields">
                            <map>
                                <entry key="uuid" value="java.lang.Long"/>
                                <entry key="id" value="java.lang.Long"/>
                                <entry key="orderNumber"
value="java.lang.Long"/>
                                <entry key="status"
value="java.lang.String"/>
                                <entry key="originalOrderNumber"
value="java.lang.String"/>
                                <entry key="poNumber"
value="java.lang.String"/>
                                <entry key="salesOrg"
value="java.lang.String"/>
                                <entry key="salesOffice"
value="java.lang.String"/>
                                <entry key="sourceSystem"
value="java.lang.String"/>
                                <entry key="segment"
value="java.lang.String"/>
                                <entry key="rrCustomerNumber"
value="java.lang.Long"/>
                                <entry key="currency"
value="java.lang.String"/>
                                <entry key="paymentMethod"
value="java.lang.String"/>
                                <entry key="subscriptionStartDate"
value="java.lang.Long"/>
                                <entry key="subscriptionEndDate"
value="java.lang.Long"/>
                                <entry key="creditcardTokenNumber"
value="java.lang.String"/>
                                <entry key="creditcardType"
value="java.lang.String"/>
                                <entry key="creditcardName"
value="java.lang.String"/>
                                <entry key="creditcardExpireationDate"
value="java.lang.Long"/>
                                <entry key="initialAuthorizedAmt"
value="java.math.BigDecimal"/>
                                <entry key="initialAuthorizationNumber"
value="java.lang.String"/>
                                <entry key="billingTimeZone"
value="java.lang.String"/>
                                <entry key="eccCustomerId"
value="java.lang.String"/>
                                <entry key="referenceId"
value="java.lang.Long"/>
                                <entry key="orderVersionNumber"
value="java.lang.Long"/>
                                <entry key="effectiveFrom"
value="java.lang.Long"/>
                                <entry key="effectiveTo"
value="java.lang.Long"/>
                                <entry key="transactionId"
value="java.lang.String"/>
                                <entry key="phoneAuthorizationStatus"
value="java.lang.String"/>
                                <entry key="authorizationResultStatusCode"
value="java.lang.String"/>
                                <entry key="requestedAuthorizationAmount"
value="java.math.BigDecimal"/>
                                <entry
key="addressVerificationServiceResult" value="java.lang.String"/>
                                <entry
key="addressVerificationServiceZipCodeResult" value="java.lang.String"/>
                                <entry key="authorizationMessageText"
value="java.lang.String"/>
                                <entry key="origOrderUserId"
value="java.lang.String"/>
                                <entry key="updatedAt"
value="java.lang.Long"/>
                                <entry key="updatedBy"
value="java.lang.String"/>
                            </map>
                        </property>

                    </bean>
                </list>
            </property>
        </bean>

       
        <bean class="org.apache.ignite.configuration.CacheConfiguration">
            <property name='dataRegionName' value='Subscription_Region'/>
            <property name="name" value="SubscriptionCustomerCache"/>
            <property name="cacheMode" value="PARTITIONED"/>
            <property name="atomicityMode" value="TRANSACTIONAL"/>
            <property name="backups" value="0"/>
            <property name="rebalanceMode" value="SYNC"/>
            <property name="rebalanceBatchSize" value="#{2 * 1024 * 1024}"/>
            <property name="rebalanceThrottle" value="100"/>

            <property name="writeBehindEnabled" value="true"/>
            <property name="writeBehindFlushThreadCount" value="10"/>
            <property name="writeBehindFlushFrequency" value="100"/>
            <property name="writeBehindFlushSize" value="51200"/>

            <property name="queryEntities">
                <list>
                    <bean class="org.apache.ignite.cache.QueryEntity">
                        <property name="keyType" value="java.lang.Long"/>
                        <property name="valueType"
value="subscription.domain.customer.entity.SubscriptionCustomer"/>
                        <property name="keyFieldName" value="uuid"/>

                        <property name="keyFields">
                            <list>
                                <value>uuid</value>
                            </list>
                        </property>

                        <property name="fields">
                            <map>
                                <entry key="uuid" value="java.lang.Long"/>
                                <entry key="id" value="java.lang.Long"/>
                                <entry key="rrCustomerNumber"
value="java.lang.Long"/>
                                <entry key="lenovoBpId"
value="java.lang.String"/>
                                <entry key="lenovoId"
value="java.lang.String"/>
                                <entry key="customerName"
value="java.lang.String"/>
                                <entry key="customerType"
value="java.lang.String"/>
                                <entry key="status"
value="java.lang.String"/>
                                <entry key="domain"
value="java.lang.String"/>
                                <entry key="emailAddress"
value="java.lang.String"/>
                                <entry key="currency"
value="java.lang.String"/>
                                <entry key="language"
value="java.lang.String"/>
                                <entry key="country"
value="java.lang.String"/>
                                <entry key="city" value="java.lang.String"/>
                                <entry key="state"
value="java.lang.String"/>
                                <entry key="address"
value="java.lang.String"/>
                                <entry key="address1"
value="java.lang.String"/>
                                <entry key="postalCode"
value="java.lang.String"/>
                                <entry key="firstName"
value="java.lang.String"/>
                                <entry key="lastName"
value="java.lang.String"/>
                                <entry key="phoneNumber"
value="java.lang.String"/>
                                <entry key="updatedAt"
value="java.lang.Long"/>
                                <entry key="updatedBy"
value="java.lang.String"/>
                                <entry key="timeZone"
value="java.lang.String"/>
                                <entry key="referenceId"
value="java.lang.Long"/>
                            </map>
                        </property>

                    </bean>
                </list>
            </property>
        </bean>

      </list>
</property>


Then, after launching the ignite project with spring boot, I wrote
@repositoryconfig (cacheName = "SubscriptionOrderCache")
Public interface SubscriptionOrderRepository extends IgniteRepository <
SubscriptionOrder, Long > Save method, respectively in the two Cache to Save
a few same data, they all have UUID, is a primary key, and then select *
from "SubscriptionOrderCache" SubscriptionOrder shows three data, select *
from "SubscriptionCustomerCache" SUBSCRIPTIONCustomer also shows three data,
their RRCUSTOMERNUMBER the value of the field is the same, but the execution
SELECT * FROM "SubscriptionOrderCache".SUBSCRIPTIONOrder a WHERE r
RCUSTOMERNUMBER in(
The SELECT RRCUSTOMERNUMBER FROM "SubscriptionCustomerCache".
SUBSCRIPTIONCustomer
) the result of this statement is not correct. Where did I configure it?


JAVA POJO Like this:
 IgniteCache<AffinityKey&lt;DomainEntity>, SubscriptionOrder> cache
=ignite.cache("SubscriptionOrderCache");

        String sql ="SELECT * FROM SUBSCRIPTIONOrder WHERE RRCUSTOMERNUMBER
in(" +
                "SELECT RRCUSTOMERNUMBER FROM
\"SubscriptionCustomerCache\".SUBSCRIPTIONCustomer" +
                ")";
        List<List&lt;?>> cursor = cache.query(new
SqlFieldsQuery(sql)).getAll();
        System.out.println(cursor);








--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Stanislav Lukyanov Stanislav Lukyanov
Reply | Threaded
Open this post in threaded view
|

RE: Missing shu is a cross-cache associated query.

Hi,

 

I would assume that data in your caches is not collocated (https://apacheignite.readme.io/docs/affinity-collocation).

You should make sure that the entries in SubscriptionOrder and in SubscriptionCustomer that have the same RRCUSTOMERNUMBER are stored on the same node.

 

By default, SQL assumes that the data is not collocated. If it isn’t, SQL may yield incorrect results (because it will not find some combinations).

 

Also, you should rewrite your query to use JOIN instead of WHERE … IN (SELECT …).

First of all, it will have better performance.

Second, JOINs may be configured to work on non-collocated data:

new SqlFieldsQuery(sql).setDistributedJoins(true).

 

To summarize,

1) Rewrite your SQL to use JOIN

2) Then either

a. Collocate your data as described at https://apacheignite.readme.io/docs/affinity-collocation (e.g. by using @AffinityKeyMapped).

b. OR enable distributed joins (performance will be worse in this case).

 

Thanks,

Stan

 

From: [hidden email]
Sent: 20 апреля 2018 г. 9:13
To: [hidden email]
Subject: Missing shu is a cross-cache associated query.

 

My ignite configuration file looks something like this.

 

 

<property name="cacheConfiguration">

    <list>

 

       

        <bean class="org.apache.ignite.configuration.CacheConfiguration">

            <property name='dataRegionName' value='Subscription_Region'/>

            <property name="name" value="SubscriptionOrderCache"/>

            <property name="cacheMode" value="PARTITIONED"/>

            <property name="atomicityMode" value="TRANSACTIONAL"/>

            <property name="backups" value="0"/>

            <property name="rebalanceMode" value="SYNC"/>

            <property name="rebalanceBatchSize" value="#{2 * 1024 * 1024}"/>

            <property name="rebalanceThrottle" value="100"/>

 

            <property name="writeBehindEnabled" value="true"/>

            <property name="writeBehindFlushThreadCount" value="10"/>

            <property name="writeBehindFlushFrequency" value="100"/>

            <property name="writeBehindFlushSize" value="51200"/>

 

            <property name="queryEntities">

                <list>

                    <bean class="org.apache.ignite.cache.QueryEntity">

                        <property name="keyType" value="java.lang.Long"/>

                        <property name="valueType"

value="subscription.domain.subscription.entity.SubscriptionOrder"/>

                        <property name="keyFieldName" value="uuid"/>

 

                        <property name="keyFields">

                            <list>

                                <value>uuid</value>

                            </list>

                        </property>

 

                        <property name="fields">

                            <map>

                                <entry key="uuid" value="java.lang.Long"/>

                                <entry key="id" value="java.lang.Long"/>

                                <entry key="orderNumber"

value="java.lang.Long"/>

                                <entry key="status"

value="java.lang.String"/>

                                <entry key="originalOrderNumber"

value="java.lang.String"/>

                                <entry key="poNumber"

value="java.lang.String"/>

                                <entry key="salesOrg"

value="java.lang.String"/>

                                <entry key="salesOffice"

value="java.lang.String"/>

                                <entry key="sourceSystem"

value="java.lang.String"/>

                                <entry key="segment"

value="java.lang.String"/>

                                <entry key="rrCustomerNumber"

value="java.lang.Long"/>

                                <entry key="currency"

value="java.lang.String"/>

                                <entry key="paymentMethod"

value="java.lang.String"/>

                                <entry key="subscriptionStartDate"

value="java.lang.Long"/>

                                <entry key="subscriptionEndDate"

value="java.lang.Long"/>

                                <entry key="creditcardTokenNumber"

value="java.lang.String"/>

                                <entry key="creditcardType"

value="java.lang.String"/>

                                <entry key="creditcardName"

value="java.lang.String"/>

                                <entry key="creditcardExpireationDate"

value="java.lang.Long"/>

                                <entry key="initialAuthorizedAmt"

value="java.math.BigDecimal"/>

                                <entry key="initialAuthorizationNumber"

value="java.lang.String"/>

                                <entry key="billingTimeZone"

value="java.lang.String"/>

                                <entry key="eccCustomerId"

value="java.lang.String"/>

                                <entry key="referenceId"

value="java.lang.Long"/>

                                <entry key="orderVersionNumber"

value="java.lang.Long"/>

                                <entry key="effectiveFrom"

value="java.lang.Long"/>

                                <entry key="effectiveTo"

value="java.lang.Long"/>

                                <entry key="transactionId"

value="java.lang.String"/>

                                <entry key="phoneAuthorizationStatus"

value="java.lang.String"/>

                                <entry key="authorizationResultStatusCode"

value="java.lang.String"/>

                                <entry key="requestedAuthorizationAmount"

value="java.math.BigDecimal"/>

                                <entry

key="addressVerificationServiceResult" value="java.lang.String"/>

                                <entry

key="addressVerificationServiceZipCodeResult" value="java.lang.String"/>

                                <entry key="authorizationMessageText"

value="java.lang.String"/>

                                <entry key="origOrderUserId"

value="java.lang.String"/>

                                <entry key="updatedAt"

value="java.lang.Long"/>

                                <entry key="updatedBy"

value="java.lang.String"/>

                            </map>

                        </property>

 

                    </bean>

                </list>

            </property>

        </bean>

 

       

        <bean class="org.apache.ignite.configuration.CacheConfiguration">

            <property name='dataRegionName' value='Subscription_Region'/>

            <property name="name" value="SubscriptionCustomerCache"/>

            <property name="cacheMode" value="PARTITIONED"/>

            <property name="atomicityMode" value="TRANSACTIONAL"/>

            <property name="backups" value="0"/>

            <property name="rebalanceMode" value="SYNC"/>

            <property name="rebalanceBatchSize" value="#{2 * 1024 * 1024}"/>

            <property name="rebalanceThrottle" value="100"/>

 

            <property name="writeBehindEnabled" value="true"/>

            <property name="writeBehindFlushThreadCount" value="10"/>

            <property name="writeBehindFlushFrequency" value="100"/>

            <property name="writeBehindFlushSize" value="51200"/>

 

            <property name="queryEntities">

                <list>

                    <bean class="org.apache.ignite.cache.QueryEntity">

                        <property name="keyType" value="java.lang.Long"/>

                        <property name="valueType"

value="subscription.domain.customer.entity.SubscriptionCustomer"/>

                        <property name="keyFieldName" value="uuid"/>

 

                        <property name="keyFields">

                            <list>

                                <value>uuid</value>

                            </list>

                        </property>

 

                        <property name="fields">

                            <map>

                                <entry key="uuid" value="java.lang.Long"/>

                                <entry key="id" value="java.lang.Long"/>

                                <entry key="rrCustomerNumber"

value="java.lang.Long"/>

                                <entry key="lenovoBpId"

value="java.lang.String"/>

                                <entry key="lenovoId"

value="java.lang.String"/>

                                <entry key="customerName"

value="java.lang.String"/>

                                <entry key="customerType"

value="java.lang.String"/>

                                <entry key="status"

value="java.lang.String"/>

                                <entry key="domain"

value="java.lang.String"/>

                                <entry key="emailAddress"

value="java.lang.String"/>

                                <entry key="currency"

value="java.lang.String"/>

                                <entry key="language"

value="java.lang.String"/>

                                <entry key="country"

value="java.lang.String"/>

                                <entry key="city" value="java.lang.String"/>

                                <entry key="state"

value="java.lang.String"/>

                                <entry key="address"

value="java.lang.String"/>

                                <entry key="address1"

value="java.lang.String"/>

                                <entry key="postalCode"

value="java.lang.String"/>

                                <entry key="firstName"

value="java.lang.String"/>

                                <entry key="lastName"

value="java.lang.String"/>

                                <entry key="phoneNumber"

value="java.lang.String"/>

                                <entry key="updatedAt"

value="java.lang.Long"/>

                                <entry key="updatedBy"

value="java.lang.String"/>

                                <entry key="timeZone"

value="java.lang.String"/>

                                <entry key="referenceId"

value="java.lang.Long"/>

                            </map>

                        </property>

 

                    </bean>

                </list>

            </property>

        </bean>

 

      </list>

</property>

 

 

Then, after launching the ignite project with spring boot, I wrote

@repositoryconfig (cacheName = "SubscriptionOrderCache")

Public interface SubscriptionOrderRepository extends IgniteRepository <

SubscriptionOrder, Long > Save method, respectively in the two Cache to Save

a few same data, they all have UUID, is a primary key, and then select *

from "SubscriptionOrderCache" SubscriptionOrder shows three data, select *

from "SubscriptionCustomerCache" SUBSCRIPTIONCustomer also shows three data,

their RRCUSTOMERNUMBER the value of the field is the same, but the execution

SELECT * FROM "SubscriptionOrderCache".SUBSCRIPTIONOrder a WHERE r

RCUSTOMERNUMBER in(

The SELECT RRCUSTOMERNUMBER FROM "SubscriptionCustomerCache".

SUBSCRIPTIONCustomer

) the result of this statement is not correct. Where did I configure it?

 

 

JAVA POJO Like this

IgniteCache<AffinityKey&lt;DomainEntity>, SubscriptionOrder> cache

=ignite.cache("SubscriptionOrderCache");

 

        String sql ="SELECT * FROM SUBSCRIPTIONOrder WHERE RRCUSTOMERNUMBER

in(" +

                "SELECT RRCUSTOMERNUMBER FROM

\"SubscriptionCustomerCache\".SUBSCRIPTIONCustomer" +

                ")";

        List<List&lt;?>> cursor = cache.query(new

SqlFieldsQuery(sql)).getAll();

        System.out.println(cursor);

 

 

 

 

 

 

 

 

--

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