You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Bill Carroll <wc...@microsoft.com> on 2015/10/27 16:24:49 UTC
Hbase and pig integration
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,zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net,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,zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net,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,zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net,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,zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net,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,zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net,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
RE: Hbase and pig integration
Posted by Bill Carroll <wc...@microsoft.com>.
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 <wc...@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
Re: Hbase and pig integration
Posted by Ravi Kiran <ma...@gmail.com>.
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 <wc...@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,
> zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net
> ,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,
> zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net
> ,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,
> zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net
> ,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,
> zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net
> ,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,
> zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net
> ,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
>
>
>
Re: Hbase and pig integration
Posted by Steve Terrell <st...@oculus360.us>.
I think you need to replace 'John' with \'John\' .
On Tue, Oct 27, 2015 at 10:24 AM, Bill Carroll <wc...@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,
> zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net
> ,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,
> zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net
> ,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,
> zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net
> ,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,
> zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net
> ,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,
> zookeepernode1.l2lfbpukh1nufbvgr2lbypaclg.bx.internal.cloudapp.net
> ,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
>
>
>