phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bill Carroll <wcarr...@microsoft.com>
Subject RE: Hbase and pig integration
Date Tue, 27 Oct 2015 16:48:31 GMT
Thanks Everyone! That worked.

Bill

From: Ravi Kiran [mailto:maghamravikiran@gmail.com]
Sent: Tuesday, October 27, 2015 11:37 AM
To: user@phoenix.apache.org
Subject: Re: Hbase and pig integration

Hi Bill,
   Can you please escape the condition in the WHERE clause as follows
Q2 = LOAD 'hbase://query/select * from EXAMPLE where FIRST_NAME = \'John\'' using org.apache.phoenix.pig.PhoenixHBaseLoader();

Thanks
Ravi

On Tue, Oct 27, 2015 at 8:24 AM, Bill Carroll <wcarroll@microsoft.com<mailto:wcarroll@microsoft.com>>
wrote:

I am trying to get the syntax for a load statement in pig to query a string value. But get
a pig parsing error. If I query an integer it is successful. Is it possible to query with
a where clause against a string with Phoenix pig\hbase integration?  Below is the example
and error

Example


0: jdbc:phoenix:zookeepernode1.l2lfbpukh1nufb> select * from EXAMPLE;
+------------------------------------------+----------------------------------------------------+----------------------------------------------------+
|                  MY_PK                   |                     FIRST_NAME              
      |                     LAST_NAME                      |
+------------------------------------------+----------------------------------------------------+----------------------------------------------------+
| 825241648                                | James                                       
      | Dean                                               |
| 825241649                                | Susan                                       
      | Miller                                             |
| 825241650                                | Manish                                      
      | Sarni                                              |
| 825241651                                | Bev                                         
      | Adkins                                             |
| 211295614005                             | John                                        
      | Doe                                                |
| 232854665520                             | Mary                                        
      | Poppins                                            |
+------------------------------------------+----------------------------------------------------+----------------------------------------------------+

# start up sqlline.py and create EXAMPLE table and upsert 1 row.
sqlline.py
==========
/usr/hdp/current/phoenix-client/bin$ python /usr/hdp/current/phoenix-client/bin/sqlline.py
zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net:2181:/hbase-unsecure
CREATE TABLE example (my_pk bigint not null, m.first_name varchar(50), m.last_name varchar(50)
CONSTRAINT pk PRIMARY KEY (my_pk));
upsert into EXAMPLE values (1, 'John', 'Smith');
SELECT * from EXAMPLE;


# Start up pig (grunt shell)
# Register phoenix client jar
REGISTER /usr/hdp/2.2.7.1-10/phoenix/phoenix-client.jar
# load a CSV file of contacts
raw = load '/data/contacts.txt' using PigStorage(',') as (my_pk, first_name, last_name);
# Store the data into hbase using phoenix
STORE raw into 'hbase://EXAMPLE' using org.apache.phoenix.pig.PhoenixHBaseStorage
('zookeepernode0.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net<https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fzookeepernode0.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net&data=01%7c01%7cwcarroll%40microsoft.com%7c2977e98a0a644dff40ee08d2dee474f9%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=Nc1r066Q%2fSudJC3ktoKFzJUYlDg7QMnT9qTHtSNSjv4%3d>,zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net<https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fzookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net&data=01%7c01%7cwcarroll%40microsoft.com%7c2977e98a0a644dff40ee08d2dee474f9%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=L4XYNdl6%2fWQK%2fnfIjgDW3RX6S6tYVvATDIbUwaurpFM%3d>,zookeepernode2.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net:2181:/hbase-unsecure','-batchSize
10-00');
# Load the table out of hbase into pig
T = LOAD 'hbase://table/EXAMPLE' using org.apache.phoenix.pig.PhoenixHBaseLoader
('zookeepernode0.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net<https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fzookeepernode0.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net&data=01%7c01%7cwcarroll%40microsoft.com%7c2977e98a0a644dff40ee08d2dee474f9%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=Nc1r066Q%2fSudJC3ktoKFzJUYlDg7QMnT9qTHtSNSjv4%3d>,zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net<https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fzookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net&data=01%7c01%7cwcarroll%40microsoft.com%7c2977e98a0a644dff40ee08d2dee474f9%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=L4XYNdl6%2fWQK%2fnfIjgDW3RX6S6tYVvATDIbUwaurpFM%3d>,zookeepernode2.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net:2181:/hbase-unsecure');
DUMP T;
# Load data by query out of hbase into pig, successful!
Q1 = LOAD 'hbase://query/SELECT FIRST_NAME, LAST_NAME FROM EXAMPLE WHERE MY_PK = 825241648'
using org.apache.phoenix.pig.PhoenixHBaseLoader
('zookeepernode0.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net<https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fzookeepernode0.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net&data=01%7c01%7cwcarroll%40microsoft.com%7c2977e98a0a644dff40ee08d2dee474f9%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=Nc1r066Q%2fSudJC3ktoKFzJUYlDg7QMnT9qTHtSNSjv4%3d>,zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net<https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fzookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net&data=01%7c01%7cwcarroll%40microsoft.com%7c2977e98a0a644dff40ee08d2dee474f9%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=L4XYNdl6%2fWQK%2fnfIjgDW3RX6S6tYVvATDIbUwaurpFM%3d>,zookeepernode2.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net:2181:/hbase-unsecure');
DUMP Q1;

Q2 = LOAD 'hbase://query/select * from EXAMPLE where FIRST_NAME = 'John';' using org.apache.phoenix.pig.PhoenixHBaseLoader
('zookeepernode0.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net<https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fzookeepernode0.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net&data=01%7c01%7cwcarroll%40microsoft.com%7c2977e98a0a644dff40ee08d2dee474f9%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=Nc1r066Q%2fSudJC3ktoKFzJUYlDg7QMnT9qTHtSNSjv4%3d>,zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net<https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fzookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net&data=01%7c01%7cwcarroll%40microsoft.com%7c2977e98a0a644dff40ee08d2dee474f9%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=L4XYNdl6%2fWQK%2fnfIjgDW3RX6S6tYVvATDIbUwaurpFM%3d>,zookeepernode2.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net:2181:/hbase-unsecure');
DUMP Q2;


grunt> Q2 = LOAD 'hbase://query/select * from EXAMPLE where FIRST_NAME = 'John';' using
org.apache.phoenix.pig.PhoenixHBaseLoader ('zookeepernode0.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net<https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fzookeepernode0.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net&data=01%7c01%7cwcarroll%40microsoft.com%7c2977e98a0a644dff40ee08d2dee474f9%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=Nc1r066Q%2fSudJC3ktoKFzJUYlDg7QMnT9qTHtSNSjv4%3d>,zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net<https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fzookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net&data=01%7c01%7cwcarroll%40microsoft.com%7c2977e98a0a644dff40ee08d2dee474f9%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=L4XYNdl6%2fWQK%2fnfIjgDW3RX6S6tYVvATDIbUwaurpFM%3d>,zookeepernode2.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net:2181:/hbase-unsecure');
2015-10-21 16:34:08,911 [main] ERROR org.apache.pig.tools.grunt.Grunt - ERROR 1200: <line
1, column 67>  mismatched input 'John' expecting SEMI_COLON
Details at logfile: /home/hdiuser/pig_1445445229498.log


Pig Stack Trace
---------------
ERROR 1200: <line 1, column 67>  mismatched input 'John' expecting SEMI_COLON

Failed to parse: <line 1, column 67>  mismatched input 'John' expecting SEMI_COLON
                at org.apache.pig.parser.QueryParserDriver.parse(QueryParserDriver.java:244)
                at org.apache.pig.parser.QueryParserDriver.parse(QueryParserDriver.java:182)
                at org.apache.pig.PigServer$Graph.validateQuery(PigServer.java:1707)
                at org.apache.pig.PigServer$Graph.registerQuery(PigServer.java:1680)
                at org.apache.pig.PigServer.registerQuery(PigServer.java:623)
                at org.apache.pig.tools.grunt.GruntParser.processPig(GruntParser.java:1063)
                at org.apache.pig.tools.pigscript.parser.PigScriptParser.parse(PigScriptParser.java:501)
                at org.apache.pig.tools.grunt.GruntParser.parseStopOnError(GruntParser.java:230)
                at org.apache.pig.tools.grunt.GruntParser.parseStopOnError(GruntParser.java:205)
                at org.apache.pig.tools.grunt.Grunt.run(Grunt.java:66)
                at org.apache.pig.Main.run(Main.java:558)
                at org.apache.pig.Main.main(Main.java:170)
                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
                at java.lang.reflect.Method.invoke(Method.java:606)
                at org.apache.hadoop.util.RunJar.run(RunJar.java:221)

Bill


Mime
View raw message