Re: pre-load data (Apache ignite native persistence store or Cassandra) into two partitioned cache tables
Hello!
If you are using two rows as @AffinityKeyMapped, you can join other tables
which use same two rows, BUT you can't join tables which use only first row
as @AffinityKeyMapped or only second row.
That's why you can join invoice_line to fact_purhcase_line - I guess they
both have invoiceId and factLineId and annotation on both.
Regards,
--
Ilya Kasnacheev
чт, 28 февр. 2019 г. в 22:38, xmw45688 :
> Can some one comments on the following questions in my previous post?
>
>
> 4. fact_purhcase_line, invoice and invoice line via factLineId and
> InvoiceID
> do not work, please see annotation below
>
> public class InvoiceLineKey {
> /** Primary key. */
> private long id;
>
> /** Foreign key to fact_purhcase_line */
> @AffinityKeyMapped
> private long factLineId;
>
> /** Foreign key to invoice */
> @AffinityKeyMapped
> private long invoiceId;
>
>
> 5. I don't quite understand that invoiceId affinity key mapped between
> invoice and invoice_line does not require factLineId key mapped between
> fact_purchase_line and invoice_line. Is this because of having factId key
> affinity between purchase_fact and purchase_fact_line, between
> purchase_fact
> and invoice.
>
> So I just have the following key affinity mapped -
>
> purchase_fact -> factId-> purchase_fact_line
> purchase_fact -> factId -> invoice
> invoice -> invoiceId -> invoice_line
>
> Interestingly, invoice_line join fact_purhcase_line works fine (see
> queries
> below). Can someone please shed some lights on this?
>
> // expected
> SELECT count(*) from PARTITION.invoice inv, PARTITION.invoiceline il
> WHERE inv.id = il.invoiceid;
>
> // why does this query work? note there is a join between
> li.id=il.factLineId which is not a key affinity mapped.
> SELECT count(*)
> from PARTITION.factpurchaseline li, PARTITION.invoice inv,
> PARTITION.invoiceline il
> WHERE li.id = il.factlineid
> AND inv.id = il.invoiceid
> ;
>
> // why does this query work? note there is a join between
> li.id=il.factLineId which is not a key affinity mapped.
> SELECT count(*) from PARTITION.factpurchaseline li, PARTITION.invoiceline
> il
> WHERE li.id = il.factlineid
> ;
>
>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: pre-load data (Apache ignite native persistence store or Cassandra) into two partitioned cache tables
Can some one comments on the following questions in my previous post?
4. fact_purhcase_line, invoice and invoice line via factLineId and InvoiceID
do not work, please see annotation below
public class InvoiceLineKey {
/** Primary key. */
private long id;
/** Foreign key to fact_purhcase_line */
@AffinityKeyMapped
private long factLineId;
/** Foreign key to invoice */
@AffinityKeyMapped
private long invoiceId;
5. I don't quite understand that invoiceId affinity key mapped between
invoice and invoice_line does not require factLineId key mapped between
fact_purchase_line and invoice_line. Is this because of having factId key
affinity between purchase_fact and purchase_fact_line, between purchase_fact
and invoice.
So I just have the following key affinity mapped -
purchase_fact -> factId-> purchase_fact_line
purchase_fact -> factId -> invoice
invoice -> invoiceId -> invoice_line
Interestingly, invoice_line join fact_purhcase_line works fine (see queries
below). Can someone please shed some lights on this?
// expected
SELECT count(*) from PARTITION.invoice inv, PARTITION.invoiceline il
WHERE inv.id = il.invoiceid;
// why does this query work? note there is a join between
li.id=il.factLineId which is not a key affinity mapped.
SELECT count(*)
from PARTITION.factpurchaseline li, PARTITION.invoice inv,
PARTITION.invoiceline il
WHERE li.id = il.factlineid
AND inv.id = il.invoiceid
;
// why does this query work? note there is a join between
li.id=il.factLineId which is not a key affinity mapped.
SELECT count(*) from PARTITION.factpurchaseline li, PARTITION.invoiceline il
WHERE li.id = il.factlineid
;
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: pre-load data (Apache ignite native persistence store or Cassandra) into two partitioned cache tables
Thanks for reply. I got the most working thanks to the example
(https://github.com/apache/ignite/blob/master/examples/src/main/java/org/apache/ignite/examples/datagrid/starschema/CacheStarSchemaExample.java)
provided by Ignite.
Here is my sql for POC (Cassandra DDL scripts)
create table ignitetest.dim_store (id bigint primary key, name varchar, addr
varchar, zip varchar);
create table ignitetest.dim_product (id bigint primary key, name varchar,
price double, qty int);
create table ignitetest.fact_purchase (id bigint primary key, productId
bigint, storeId bigint, purchasePrice double);
create table ignitetest.fact_purchase_line(id bigint , factId bigint, line
int, linePrice double, lineQty int, primary key (id, factId));
create table ignitetest.invoice (id bigint, factId bigint, productId bigint,
storeId bigint, purchasePrice double, primary key (id));
create table ignitetest.invoice_line (id bigint, invoiceId bigint,
factLineId bigint, line int, price double, qty int, primary key (id,
invoiceId, factLineId));
Have following key affinity mapped -
purchase_fact -> factId-> purchase_fact_line
purchase_fact -> factId -> invoice
invoice -> invoiceId -> invoice_line
1. fact_purchase and fact_purchase_line via factId affinity works expected.
2. fact_purchase and invoice via factId affinity works expected.
3. invoice and invoice_line via invoiceId affinity works expected.
However,
4. fact_purhcase_line, invoice and invoice line via factLineId and InvoiceID
do not work, please see annotation below
public class InvoiceLineKey {
/** Primary key. */
private long id;
/** Foreign key to fact_purhcase_line */
@AffinityKeyMapped
private long factLineId;
/** Foreign key to invoice */
@AffinityKeyMapped
private long invoiceId;
5. I don't quite understand that invoiceId affinity key mapped between
invoice and invoice_line does not require factLineId key mapped between
fact_purchase_line and invoice_line. Is this because of having factId key
affinity between purchase_fact and purchase_fact_line, between purchase_fact
and invoice.
So I just have the following key affinity mapped -
purchase_fact -> factId-> purchase_fact_line
purchase_fact -> factId -> invoice
invoice -> invoiceId -> invoice_line
Interestingly, invoice_line join fact_purhcase_line works fine (see queries
below). Can someone please shed some lights on this?
// expected
SELECT count(*) from PARTITION.invoice inv, PARTITION.invoiceline il
WHERE inv.id = il.invoiceid;
// why does this query work? note there is a join between
li.id=il.factLineId which is not a key affinity mapped.
SELECT count(*)
from PARTITION.factpurchaseline li, PARTITION.invoice inv,
PARTITION.invoiceline il
WHERE li.id = il.factlineid
AND inv.id = il.invoiceid
;
// why does this query work? note there is a join between
li.id=il.factLineId which is not a key affinity mapped.
SELECT count(*) from PARTITION.factpurchaseline li, PARTITION.invoiceline il
WHERE li.id = il.factlineid
;
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: pre-load data (Apache ignite native persistence store or Cassandra) into two partitioned cache tables
Hello!
1) The best way of preloading data is to add CacheLoader (or CacheStore) to
cache configuration and call loadCache().
2) Data will be collocated as long as both caches use same affinity field
(via annotation, AffinityKey or configuration).
Then if you join tables by that field Ignite will know.
This of course requires that other settings of AffinityFunction are the
same.
Regards,
--
Ilya Kasnacheev
пн, 18 февр. 2019 г. в 09:43, xmw45688 :
> Hi All,
>
> Apache Ignite recommends to collocate data with data, which is a very nice
> feature. This works well if you insert data through APIs provided by
> Ignite
> (see the following code I copy from Ignite Doc). However, I don't know how
> to handle the following cases
> 1. the data (company and person) exist in the other databases such as
> Cassandra. How do I pre-cache company and person from pre-existed data?
> Do
> I have to use JDBC to load company, person data row by row, insert into
> person and cache objects, such as
>for org: orgs {
>find persons for org {
>personCache.put(affinityKey(p.id, org.id), org);
>}
>orgCache.put(org.id, org);
>}
>
>If I used Ignite native persistence store, how does Ignite cluster know
> that both company and person are collocated?
>
> 2. How do I know that @AffinityKeyMapped annotated key comes from the
> cache I need to map. I don't see the logic to define relationship between
> person.companyId and company.id. If I have two separated two methods to
> load company and person cache separately, will the collocation of persons
> with companies still work?
> preloadCompany();
> preloadPerson(long companyId);
>
> preloadAllPersons() {
> 1. get all companies from the companyCache
> // how personsCache will know this companyId is the same cluster node
> as
> companyCache?
> for (c: companies) {
>preloadPerson(c.companyId);
> }
> }
>
> The following code is excerpted from
> https://apacheignite.readme.io/docs/affinity-collocation.
>
>
> public class PersonKey {
> // Person ID used to identify a person.
> private String personId;
>
> // Company ID which will be used for affinity.
> @AffinityKeyMapped
> private String companyId;
> ...
> }
>
> // Instantiate person keys with the same company ID which is used as
> affinity key.
> Object personKey1 = new PersonKey("myPersonId1", "myCompanyId");
> Object personKey2 = new PersonKey("myPersonId2", "myCompanyId");
>
> Person p1 = new Person(personKey1, ...);
> Person p2 = new Person(personKey2, ...);
>
> // Both, the company and the person objects will be cached on the same
> node.
> comCache.put("myCompanyId", new Company(...));
> perCache.put(personKey1, p1);
> perCache.put(personKey2, p2);
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
pre-load data (Apache ignite native persistence store or Cassandra) into two partitioned cache tables
Hi All,
Apache Ignite recommends to collocate data with data, which is a very nice
feature. This works well if you insert data through APIs provided by Ignite
(see the following code I copy from Ignite Doc). However, I don't know how
to handle the following cases
1. the data (company and person) exist in the other databases such as
Cassandra. How do I pre-cache company and person from pre-existed data? Do
I have to use JDBC to load company, person data row by row, insert into
person and cache objects, such as
for org: orgs {
find persons for org {
personCache.put(affinityKey(p.id, org.id), org);
}
orgCache.put(org.id, org);
}
If I used Ignite native persistence store, how does Ignite cluster know
that both company and person are collocated?
2. How do I know that @AffinityKeyMapped annotated key comes from the
cache I need to map. I don't see the logic to define relationship between
person.companyId and company.id. If I have two separated two methods to
load company and person cache separately, will the collocation of persons
with companies still work?
preloadCompany();
preloadPerson(long companyId);
preloadAllPersons() {
1. get all companies from the companyCache
// how personsCache will know this companyId is the same cluster node as
companyCache?
for (c: companies) {
preloadPerson(c.companyId);
}
}
The following code is excerpted from
https://apacheignite.readme.io/docs/affinity-collocation.
public class PersonKey {
// Person ID used to identify a person.
private String personId;
// Company ID which will be used for affinity.
@AffinityKeyMapped
private String companyId;
...
}
// Instantiate person keys with the same company ID which is used as
affinity key.
Object personKey1 = new PersonKey("myPersonId1", "myCompanyId");
Object personKey2 = new PersonKey("myPersonId2", "myCompanyId");
Person p1 = new Person(personKey1, ...);
Person p2 = new Person(personKey2, ...);
// Both, the company and the person objects will be cached on the same node.
comCache.put("myCompanyId", new Company(...));
perCache.put(personKey1, p1);
perCache.put(personKey2, p2);
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
