[
https://issues.apache.org/jira/browse/PHOENIX-1999?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14944189#comment-14944189
]
maghamravikiran commented on PHOENIX-1999:
------------------------------------------
[~xakaseanx] I have a quick test confirming joins work when the table name is
escaped correctly.
{code}
@Test
public void testDataFromJoin() throws Exception {
try {
//create the table
String tblA = "CREATE TABLE \"my_table\" (AID INTEGER NOT NULL,
ANAME VARCHAR CONSTRAINT pk PRIMARY KEY (AID)) ";
conn.createStatement().execute(tblA);
String tblB = "CREATE TABLE \"join_table\" (BID INTEGER NOT NULL,
BNAME VARCHAR CONSTRAINT pk PRIMARY KEY (BID)) ";
conn.createStatement().execute(tblB);
//upsert the data.
final String dmlA = "UPSERT INTO \"my_table\" VALUES(?,?)";
PreparedStatement stmt = conn.prepareStatement(dmlA);
int rows = 10;
for(int i = 0 ; i < rows; i++) {
stmt.setInt(1, i);
stmt.setString(2, "a"+i);
stmt.execute();
}
final String dmlB = "UPSERT INTO \"join_table\" VALUES(?,?)";
stmt = conn.prepareStatement(dmlB);
for(int i = 0 ; i < rows; i++) {
stmt.setInt(1, i);
stmt.setString(2, "b"+i);
stmt.execute();
}
conn.commit();
pigServer.registerQuery("A = load 'hbase://query/SELECT AID , BID,
ANAME FROM \"my_table\" AS t1 JOIN \"join_table\" AS t2 ON t1.AID = t2.BID '
using " + PhoenixHBaseLoader.class.getName() + "('"+zkQuorum + "') ;");
Iterator<Tuple> iterator = pigServer.openIterator("A");
int count = 0;
while (iterator.hasNext()) {
Tuple tuple = iterator.next();
count++;
}
assertEquals(rows,count);
} finally {
}
}
{code}
since it is working, I would like to close this ticket. Please feel free to
open it if you are facing any issue.
> Phoenix Pig Loader does not return data when selecting from multiple tables
> in a query with a join
> --------------------------------------------------------------------------------------------------
>
> Key: PHOENIX-1999
> URL: https://issues.apache.org/jira/browse/PHOENIX-1999
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.1.0
> Environment: Pig 0.14.3, Hadoop 2.5.2
> Reporter: Seth Brogan
> Assignee: maghamravikiran
>
> The Phoenix Pig Loader does not return data in Pig when selecting specific
> columns from multiple tables in a join query.
> Example:
> {code}
> DESCRIBE my_table;
> my_table: {a: chararray, my_id: chararray}
> DUMP my_table;
> (abc, 123)
> DESCRIBE join_table;
> join_table: {x: chararray, my_id: chararray}
> DUMP join_table;
> (xyz, 123)
> A = LOAD 'hbase://query/SELECT "t1"."a", "t2"."x" FROM "my_table" AS "t1"
> JOIN "join_table" AS "t2" ON "t1"."my_id" = "t2"."my_id"' using
> org.apache.phoenix.pig.PhoenixHBaseLoader('localhost');
> DUMP A;
> (,)
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
