Hi, There was small issue with the data(delimiters were messed up) - the filters seem to work correctly. I'm now working on Hive+HBase integration, Phoenix will be taken up later.
Regards, Omkar Joshi -----Original Message----- From: Ian Varley [mailto:[email protected]] Sent: Wednesday, April 17, 2013 9:46 PM To: [email protected] Subject: Re: Problem in filters Omkar, Have you considered using Phoenix (https://github.com/forcedotcom/phoenix), a SQL skin over HBase to execute your SQL directly? That'll save you from learning all the nuances of HBase filters and give you as good or better performance. Once you've downloaded and installed Phoenix, here's what you'd need to do: // One time DDL statement Connection conn = DriverManager.getConnection("jdbc:phoenix:your-zookeeper-quorum-host"); conn.createStatement().execute("CREATE VIEW ORDERS(\n" + // Not sure what the PK is, so I added this column "ORDER_DETAILS.ORDER_DETAILS_ID VARCHAR NOT NULL PRIMARY KEY,\n" + // If you have fixed length IDs, then use CHAR(xxx) "ORDER_DETAILS.CUSTOMER_ID VARCHAR,\n" + "ORDER_DETAILS.PRODUCT_ID VARCHAR,\n" + "ORDER_DETAILS.REQUEST_DATE DATE,\n" + "ORDER_DETAILS.PRODUCT_QUANTITY INTEGER,\n" + "ORDER_DETAILS.PRICE DECIMAL(10,2),\n" + // not sure on the type here, but this might map to an Enum "ORDER_DETAILS.PAYMENT_MODE CHAR(1)\n" + ")"); // Running the query: Connection conn = DriverManager.getConnection("jdbc:phoenix:your-zookeeper-quorum-host"); PreparedStatement stmt = conn.prepareStatement("SELECT ORDER_ID,CUSTOMER_ID,PRODUCT_ID,QUANTITY\n" + "FROM ORDERS WHERE QUANTITY >= ? and PRODUCT_ID=?"); stmt.setInt(1,16); stmt.setString(2,"P60337998"); ResultSet rs = stmt.executeQuery(); while (rs.next()) { System.out.println("ORDER_ID=" + rs.getString("ORDER_ID") + ",CUSTOMER_ID=" + rs.getString("CUSTOMER_ID")+ ",PRODUCT_ID=" + rs.getString("PRODUCT_ID") + ",QUANTITY=" + rs.getInt("QUANTITY")); } There are different trade-offs for the make up of the columns in your PK, depending on your access patterns. Getting this right could prevent full table scans and make your query execute much faster. Also, there are performance trade-offs for using a VIEW versus a TABLE. Ian On Apr 17, 2013, at 8:32 AM, Jean-Marc Spaggiari wrote: Hi Omkar, Using the shell, can you scan the few first lines from your table to make sure it's store with the expected format? Don't forget the limit the number of rows retrieved. JM 2013/4/17 Omkar Joshi <[email protected]<mailto:[email protected]>> Hi Ted, I tried using only productIdFilter without FilterList but still no output. public void executeOrdersQuery() { /* * SELECT ORDER_ID,CUSTOMER_ID,PRODUCT_ID,QUANTITY FROM ORDERS WHERE * QUANTITY >=16 and PRODUCT_ID='P60337998' */ String tableName = "ORDERS"; String family = "ORDER_DETAILS"; int quantity = 16; String productId = "P60337998"; SingleColumnValueFilter quantityFilter = new SingleColumnValueFilter( Bytes.toBytes(family), Bytes.toBytes("PRODUCT_QUANTITY"), CompareFilter.CompareOp.GREATER_OR_EQUAL, Bytes.toBytes(quantity)); SingleColumnValueFilter productIdFilter = new SingleColumnValueFilter( Bytes.toBytes(family), Bytes.toBytes("PRODUCT_ID"), CompareFilter.CompareOp.EQUAL, Bytes.toBytes(productId)); FilterList filterList = new FilterList( FilterList.Operator.MUST_PASS_ALL); // filterList.addFilter(quantityFilter); filterList.addFilter(productIdFilter); Scan scan = new Scan(); scan.addColumn(Bytes.toBytes(family), Bytes.toBytes("ORDER_ID")); scan.addColumn(Bytes.toBytes(family), Bytes.toBytes("CUSTOMER_ID")); scan.addColumn(Bytes.toBytes(family), Bytes.toBytes("PRODUCT_ID")); scan.addColumn(Bytes.toBytes(family), Bytes.toBytes("QUANTITY")); // scan.setFilter(filterList); scan.setFilter(productIdFilter); HTableInterface tbl = hTablePool.getTable(Bytes.toBytes(tableName)); ResultScanner scanResults = null; try { scanResults = tbl.getScanner(scan); System.out.println("scanResults : "); for (Result result : scanResults) { System.out.println("The result is " + result); } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { tbl.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } Regards, Omkar Joshi -----Original Message----- From: Ted Yu [mailto:[email protected]] Sent: Wednesday, April 17, 2013 6:46 PM To: [email protected]<mailto:[email protected]> Cc: [email protected]<mailto:[email protected]> Subject: Re: Problem in filters If you specify producIdFilter without using FilterList, what would you get ? Thanks On Apr 17, 2013, at 4:51 AM, Omkar Joshi <[email protected]<mailto:[email protected]>> wrote: Hi, I'm having the a table named ORDERS with 1000851 rows: rowkey : ORDER_ID column family : ORDER_DETAILS columns : CUSTOMER_ID PRODUCT_ID REQUEST_DATE PRODUCT_QUANTITY PRICE PAYMENT_MODE I'm using the following code to access the data : public void executeOrdersQuery() { /* * SELECT ORDER_ID,CUSTOMER_ID,PRODUCT_ID,QUANTITY FROM ORDERS WHERE * QUANTITY >=16 and PRODUCT_ID='P60337998' */ String tableName = "ORDERS"; String family = "ORDER_DETAILS"; int quantity = 16; String productId = "P60337998"; SingleColumnValueFilter quantityFilter = new SingleColumnValueFilter( Bytes.toBytes(family), Bytes.toBytes("PRODUCT_QUANTITY"), CompareFilter.CompareOp.GREATER_OR_EQUAL, Bytes.toBytes(quantity)); SingleColumnValueFilter productIdFilter = new SingleColumnValueFilter( Bytes.toBytes(family), Bytes.toBytes("PRODUCT_ID"), CompareFilter.CompareOp.EQUAL, Bytes.toBytes(productId)); FilterList filterList = new FilterList( FilterList.Operator.MUST_PASS_ALL); // filterList.addFilter(quantityFilter); filterList.addFilter(productIdFilter); Scan scan = new Scan(); scan.addColumn(Bytes.toBytes(family), Bytes.toBytes("ORDER_ID")); scan.addColumn(Bytes.toBytes(family), Bytes.toBytes("CUSTOMER_ID")); scan.addColumn(Bytes.toBytes(family), Bytes.toBytes("PRODUCT_ID")); scan.addColumn(Bytes.toBytes(family), Bytes.toBytes("QUANTITY")); scan.setFilter(filterList); HTableInterface tbl = hTablePool.getTable(Bytes.toBytes(tableName)); ResultScanner scanResults = null; try { scanResults = tbl.getScanner(scan); System.out.println("scanResults : "); for (Result result : scanResults) { System.out.println("The result is " + result); } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { tbl.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } First few records of the table are : O12004457;C110;P60337998;2000-5-17;16;19184.0;cash;Customer is the new emperor. Either you give him what he desires or you are banished from his kingdom.;Before you place your order, we reserve the right to change these terms and conditions at any time .Any such changes will take effect when posted on this website and it is your responsibility to read these terms and condition s on each occasion you use this website. We will never supply you with substitute goods.Our VAT registration number is 875 505 5 01.; O12004458;C425;P50478434;2008-4-30;3;831825.0;debit;In times of change, the learners will inherit the earth, while the knowers will find themselves beautifully equipped to deal with a world that no longer exists;Before you place your order, we reserve the right to change these terms and conditions at any time.Any such changes will take effect when posted on this website and i t is your responsibility to read these terms and conditions on each occasion you use this website. We will never supply you wi th substitute goods.Our VAT registration number is 875 5055 01.; If I don't use any filter, the row that I'm trying to fetch is returned along with the 1000s of others but as soon as I use even a single filter(the other is commented), no results are returned. Is there some problem with my code? Regards, Omkar Joshi ________________________________ The contents of this e-mail and any attachment(s) may contain confidential or privileged information for the intended recipient(s). Unintended recipients are prohibited from taking action on the basis of information in this e-mail and using or disseminating the information, and must notify the sender and delete it from their system. L&T Infotech will not accept responsibility or liability for the accuracy or completeness of, or the presence of any virus or disabling code in this e-mail"
