Thanks Everyone! That worked. Bill
From: Ravi Kiran [mailto:[email protected]] Sent: Tuesday, October 27, 2015 11:37 AM To: [email protected] 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 <[email protected]<mailto:[email protected]>> 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
