hbase-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Omkar Joshi <Omkar.Jo...@lntinfotech.com>
Subject RE: Problem in filters
Date Fri, 19 Apr 2013 09:59:14 GMT
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:ivarley@salesforce.com] 
Sent: Wednesday, April 17, 2013 9:46 PM
To: user@hbase.apache.org
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 <Omkar.Joshi@lntinfotech.com<mailto:Omkar.Joshi@lntinfotech.com>>

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:yuzhihong@gmail.com]
Sent: Wednesday, April 17, 2013 6:46 PM
To: user@hbase.apache.org<mailto:user@hbase.apache.org>
Cc: user@hbase.apache.org<mailto:user@hbase.apache.org>
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 <Omkar.Joshi@lntinfotech.com<mailto:Omkar.Joshi@lntinfotech.com>>
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"



Mime
View raw message