[jira] [Commented] (PHOENIX-1999) Phoenix Pig Loader does not return data when selecting from multiple tables in a query with a join

["maghamravikiran (JIRA)" <ji...@apache.org>]

    [ 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)