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/
