You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Zhou Shuaifeng <zh...@huawei.com> on 2010/06/17 08:21:21 UTC
hive-hbase integration client error, please help
Hi All ,
I've got some problem in programming hive-hbase client, could someone help
me?
The code is very simple, select some data from a hbase-based table.
public static void main(String[] args) throws SQLException {
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
// System.out.println(e);
e.printStackTrace();
System.exit(1);
}
Connection con = DriverManager.getConnection(
"jdbc:hive://2.1.37.110:10000/default", "", "");
Statement stmt = con.createStatement();
String tableName = "hive_zsf11";
String sql = "select * from " + tableName + " where id = 1";
System.out.println("Running: " + sql);
ResultSet res = stmt.executeQuery(sql);
while (res.next()) {
System.out.println(String.valueOf(res.getInt(1)) + "\t"
+ res.getString(2));
}
}
}
error info is below:
Hive history
file=/tmp/z00100568/hive_job_log_z00100568_201006171106_406425331.txt
10/06/17 11:06:20 INFO exec.HiveHistory: Hive history
file=/tmp/z00100568/hive_job_log_z00100568_201006171106_406425331.txt
Running: select * from hive_zsf11 where id = 1 Exception in thread "main"
java.sql.SQLException: Query returned non-zero code: 9, cause: FAILED:
Execution Error, return code 2 from
org.apache.hadoop.hive.ql.exec.ExecDriver
at
org.apache.hadoop.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:19
7)
at com.huawei.hive.HiveJdbcClient.main(HiveJdbcClient.java:69)
Before doing this, I have build the hive-hbase integration code
successfully, and can run SQL to operate hbase-based table well on the
shell.
So, what's more need to do? Thanks a lot.
Best Regards,
Zhou
----------------------------------------------------------------------------
---------------------------------------------------------
This e-mail and its attachments contain confidential information from
HUAWEI, which is intended only for the person or entity whose address is
listed above. Any use of the information contained herein in any way
(including, but not limited to, total or partial disclosure, reproduction,
or dissemination) by persons other than the intended
recipient(s) is prohibited. If you receive this e-mail in error, please
notify the sender by phone or email immediately and delete it!
Re: hive-hbase integration client error, please help
Posted by John Sichi <js...@facebook.com>.
Great, glad to hear you figured it out. I think you might be able to achieve the same effect by specifying --auxpath when you start the Thrift hiveserver (similar to the instructions for running CLI). That way JDBC client programs won't need to do anything special.
JVS
On Jun 17, 2010, at 5:26 AM, Zhou Shuaifeng wrote:
> This problem is solved.
>
> The JDBC client need to execute "add jar" operation to add
> "hive_hbase-handler.jar", which is needed by the hive-server to run
> map/reduce job.
> The demo hive-hbase integration client code is below:
>
> import java.sql.SQLException;
> import java.sql.Connection;
> import java.sql.ResultSet;
> import java.sql.Statement;
> import java.sql.DriverManager;
>
> public class HiveJdbcClient {
> private static String driverName =
> "org.apache.hadoop.hive.jdbc.HiveDriver";
>
> /**
> * @param args
> * @throws SQLException
> */
> public static void main(String[] args) throws SQLException {
>
> try {
> Class.forName(driverName);
> } catch (ClassNotFoundException e) {
> e.printStackTrace();
> System.exit(1);
> }
>
> Connection con = DriverManager.getConnection(
> "jdbc:hive://2.1.37.110:10000/default", "",
> "");
> Statement stmt = con.createStatement();
> String tableName = "hive_zsf11";
>
> // add needed jar: "hive_hbase-handler.jar"
> String sql = "add jar
> /opt/hadoop/hive/lib/hive_hbase-handler.jar";
> System.out.println("Running: " + sql);
> ResultSet res = stmt.executeQuery(sql);
>
> // drop exist table
> System.out.println("drop table: " + tableName);
> stmt.executeQuery("drop table " + tableName);
>
> // create table
> System.out.println("create table: " +
> tableName);
> sql = "create table "
> + tableName
> + "(id int, name string) STORED BY
> 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES
> ('hbase.columns.mapping' = ':key,cf1:val') TBLPROPERTIES ('hbase.table.name'
> = '"
> + tableName + "' )";
> System.out.println("Running: " + sql);
> res = stmt.executeQuery(sql);
>
> // show tables
> sql = "show tables '" + tableName + "'";
> System.out.println("Running: " + sql);
> res = stmt.executeQuery(sql);
> if (res.next()) {
> System.out.println(res.getString(1));
> }
>
> // describe table
> sql = "describe " + tableName;
> System.out.println("Running: " + sql);
> res = stmt.executeQuery(sql);
> while (res.next()) {
> System.out.println(res.getString(1) + "\t" +
> res.getString(2));
> }
>
> // insert data from source Table
> String sourceTable = "zsf";
> sql = "INSERT OVERWRITE TABLE " + tableName + " SELECT *
> FROM "
> + sourceTable;
> System.out.println("Running: " + sql);
> res = stmt.executeQuery(sql);
>
> // select * query
> sql = "select * from " + tableName + " where id = 1";
> System.out.println("Running: " + sql);
> res = stmt.executeQuery(sql);
> while (res.next()) {
> System.out.println(String.valueOf(res.getInt(1)) +
> "\t"
> + res.getString(2));
> }
>
> // count
> sql = "select count(1) from " + tableName;
> System.out.println("Running: " + sql);
> res = stmt.executeQuery(sql);
> while (res.next()) {
> System.out.println(res.getString(1));
> }
>
> }
> }
>
> The execute result is below:
>
> Hive history
> file=/tmp/z00100568/hive_job_log_z00100568_201006171621_541044051.txt
> 10/06/17 16:21:11 INFO exec.HiveHistory: Hive history
> file=/tmp/z00100568/hive_job_log_z00100568_201006171621_541044051.txt
> Running: add jar /opt/hadoop/hive/lib/hive_hbase-handler.jar
> drop table: hive_zsf11
> create table: hive_zsf11
> Running: create table hive_zsf11(id int, name string) STORED BY 'org.apache.
> hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ('hbase.columns.
> mapping' = ':key,cf1:val') TBLPROPERTIES ('hbase.table.name' = 'hive_zsf11'
> )
> Running: show tables 'hive_zsf11'
> hive_zsf11
> Running: describe hive_zsf11
> id int
> name string
> Running: INSERT OVERWRITE TABLE hive_zsf11 SELECT * FROM zsf
> Running: select * from hive_zsf11 where id = 1
> 1 zsf
> Running: select count(1) from hive_zsf11
> 4
>
> After this, the hive server create a hbase talbe "hive_zsf11" in hbase, and
> insert some data into it.
>
> hbase(main):002:0> scan 'hive_zsf11'
> ROW COLUMN+CELL
> 1 column=cf1:val, timestamp=1276762938488, value=zsf
> 2 column=cf1:val, timestamp=1276762938488, value=zw
> 3 column=cf1:val, timestamp=1276762938294, value=zzf
> 4 column=cf1:val, timestamp=1276762938294, value=cjl
> 4 row(s) in 0.0160 seconds
> hbase(main):003:0>
>
>
>
> -----邮件原件-----
> 发件人: Zhou Shuaifeng [mailto:zhoushuaifeng@huawei.com]
> 发送时间: 2010年6月17日 14:21
> 收件人: hive-user@hadoop.apache.org
> 抄送: 'zhaozhifeng 00129982'
> 主题: hive-hbase integration client error, please help
>
> Hi All ,
>
> I've got some problem in programming hive-hbase client, could someone help
> me?
>
>
> The code is very simple, select some data from a hbase-based table.
>
>
> public static void main(String[] args) throws SQLException {
>
> try {
> Class.forName(driverName);
> } catch (ClassNotFoundException e) {
> // System.out.println(e);
> e.printStackTrace();
> System.exit(1);
> }
>
> Connection con = DriverManager.getConnection(
> "jdbc:hive://2.1.37.110:10000/default", "", "");
> Statement stmt = con.createStatement();
> String tableName = "hive_zsf11";
>
> String sql = "select * from " + tableName + " where id = 1";
> System.out.println("Running: " + sql);
> ResultSet res = stmt.executeQuery(sql);
> while (res.next()) {
> System.out.println(String.valueOf(res.getInt(1)) + "\t"
> + res.getString(2));
> }
> }
> }
>
> error info is below:
> Hive history
> file=/tmp/z00100568/hive_job_log_z00100568_201006171106_406425331.txt
> 10/06/17 11:06:20 INFO exec.HiveHistory: Hive history
> file=/tmp/z00100568/hive_job_log_z00100568_201006171106_406425331.txt
> Running: select * from hive_zsf11 where id = 1 Exception in thread "main"
> java.sql.SQLException: Query returned non-zero code: 9, cause: FAILED:
> Execution Error, return code 2 from
> org.apache.hadoop.hive.ql.exec.ExecDriver
> at
> org.apache.hadoop.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:19
> 7)
> at com.huawei.hive.HiveJdbcClient.main(HiveJdbcClient.java:69)
>
> Before doing this, I have build the hive-hbase integration code
> successfully, and can run SQL to operate hbase-based table well on the
> shell.
> So, what's more need to do? Thanks a lot.
>
> Best Regards,
> Zhou
> ----------------------------------------------------------------------------
> ---------------------------------------------------------
> This e-mail and its attachments contain confidential information from
> HUAWEI, which is intended only for the person or entity whose address is
> listed above. Any use of the information contained herein in any way
> (including, but not limited to, total or partial disclosure, reproduction,
> or dissemination) by persons other than the intended
> recipient(s) is prohibited. If you receive this e-mail in error, please
> notify the sender by phone or email immediately and delete it!
>
>
>
>
>
Re: hive-hbase integration client error, please help
Posted by Zhou Shuaifeng <zh...@huawei.com>.
This problem is solved.
The JDBC client need to execute "add jar" operation to add
"hive_hbase-handler.jar", which is needed by the hive-server to run
map/reduce job.
The demo hive-hbase integration client code is below:
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;
public class HiveJdbcClient {
private static String driverName =
"org.apache.hadoop.hive.jdbc.HiveDriver";
/**
* @param args
* @throws SQLException
*/
public static void main(String[] args) throws SQLException {
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.exit(1);
}
Connection con = DriverManager.getConnection(
"jdbc:hive://2.1.37.110:10000/default", "",
"");
Statement stmt = con.createStatement();
String tableName = "hive_zsf11";
// add needed jar: "hive_hbase-handler.jar"
String sql = "add jar
/opt/hadoop/hive/lib/hive_hbase-handler.jar";
System.out.println("Running: " + sql);
ResultSet res = stmt.executeQuery(sql);
// drop exist table
System.out.println("drop table: " + tableName);
stmt.executeQuery("drop table " + tableName);
// create table
System.out.println("create table: " +
tableName);
sql = "create table "
+ tableName
+ "(id int, name string) STORED BY
'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES
('hbase.columns.mapping' = ':key,cf1:val') TBLPROPERTIES ('hbase.table.name'
= '"
+ tableName + "' )";
System.out.println("Running: " + sql);
res = stmt.executeQuery(sql);
// show tables
sql = "show tables '" + tableName + "'";
System.out.println("Running: " + sql);
res = stmt.executeQuery(sql);
if (res.next()) {
System.out.println(res.getString(1));
}
// describe table
sql = "describe " + tableName;
System.out.println("Running: " + sql);
res = stmt.executeQuery(sql);
while (res.next()) {
System.out.println(res.getString(1) + "\t" +
res.getString(2));
}
// insert data from source Table
String sourceTable = "zsf";
sql = "INSERT OVERWRITE TABLE " + tableName + " SELECT *
FROM "
+ sourceTable;
System.out.println("Running: " + sql);
res = stmt.executeQuery(sql);
// select * query
sql = "select * from " + tableName + " where id = 1";
System.out.println("Running: " + sql);
res = stmt.executeQuery(sql);
while (res.next()) {
System.out.println(String.valueOf(res.getInt(1)) +
"\t"
+ res.getString(2));
}
// count
sql = "select count(1) from " + tableName;
System.out.println("Running: " + sql);
res = stmt.executeQuery(sql);
while (res.next()) {
System.out.println(res.getString(1));
}
}
}
The execute result is below:
Hive history
file=/tmp/z00100568/hive_job_log_z00100568_201006171621_541044051.txt
10/06/17 16:21:11 INFO exec.HiveHistory: Hive history
file=/tmp/z00100568/hive_job_log_z00100568_201006171621_541044051.txt
Running: add jar /opt/hadoop/hive/lib/hive_hbase-handler.jar
drop table: hive_zsf11
create table: hive_zsf11
Running: create table hive_zsf11(id int, name string) STORED BY 'org.apache.
hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ('hbase.columns.
mapping' = ':key,cf1:val') TBLPROPERTIES ('hbase.table.name' = 'hive_zsf11'
)
Running: show tables 'hive_zsf11'
hive_zsf11
Running: describe hive_zsf11
id int
name string
Running: INSERT OVERWRITE TABLE hive_zsf11 SELECT * FROM zsf
Running: select * from hive_zsf11 where id = 1
1 zsf
Running: select count(1) from hive_zsf11
4
After this, the hive server create a hbase talbe "hive_zsf11" in hbase, and
insert some data into it.
hbase(main):002:0> scan 'hive_zsf11'
ROW COLUMN+CELL
1 column=cf1:val, timestamp=1276762938488, value=zsf
2 column=cf1:val, timestamp=1276762938488, value=zw
3 column=cf1:val, timestamp=1276762938294, value=zzf
4 column=cf1:val, timestamp=1276762938294, value=cjl
4 row(s) in 0.0160 seconds
hbase(main):003:0>
-----邮件原件-----
发件人: Zhou Shuaifeng [mailto:zhoushuaifeng@huawei.com]
发送时间: 2010年6月17日 14:21
收件人: hive-user@hadoop.apache.org
抄送: 'zhaozhifeng 00129982'
主题: hive-hbase integration client error, please help
Hi All ,
I've got some problem in programming hive-hbase client, could someone help
me?
The code is very simple, select some data from a hbase-based table.
public static void main(String[] args) throws SQLException {
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
// System.out.println(e);
e.printStackTrace();
System.exit(1);
}
Connection con = DriverManager.getConnection(
"jdbc:hive://2.1.37.110:10000/default", "", "");
Statement stmt = con.createStatement();
String tableName = "hive_zsf11";
String sql = "select * from " + tableName + " where id = 1";
System.out.println("Running: " + sql);
ResultSet res = stmt.executeQuery(sql);
while (res.next()) {
System.out.println(String.valueOf(res.getInt(1)) + "\t"
+ res.getString(2));
}
}
}
error info is below:
Hive history
file=/tmp/z00100568/hive_job_log_z00100568_201006171106_406425331.txt
10/06/17 11:06:20 INFO exec.HiveHistory: Hive history
file=/tmp/z00100568/hive_job_log_z00100568_201006171106_406425331.txt
Running: select * from hive_zsf11 where id = 1 Exception in thread "main"
java.sql.SQLException: Query returned non-zero code: 9, cause: FAILED:
Execution Error, return code 2 from
org.apache.hadoop.hive.ql.exec.ExecDriver
at
org.apache.hadoop.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:19
7)
at com.huawei.hive.HiveJdbcClient.main(HiveJdbcClient.java:69)
Before doing this, I have build the hive-hbase integration code
successfully, and can run SQL to operate hbase-based table well on the
shell.
So, what's more need to do? Thanks a lot.
Best Regards,
Zhou
----------------------------------------------------------------------------
---------------------------------------------------------
This e-mail and its attachments contain confidential information from
HUAWEI, which is intended only for the person or entity whose address is
listed above. Any use of the information contained herein in any way
(including, but not limited to, total or partial disclosure, reproduction,
or dissemination) by persons other than the intended
recipient(s) is prohibited. If you receive this e-mail in error, please
notify the sender by phone or email immediately and delete it!