spark SQL Running the Thrift JDBC/ODBC server

Running the Thrift JDBC/ODBC server

1:运行

./sbin/start-thriftserver.sh  --hiveconf hive.server2.thrift.port=10000  --hiveconf hive.server2.thrift.bind.host=feng02 --master spark://feng02:7077 --driver-class-path /home/jifeng/hadoop/spark-1.2.0-bin-2.4.1/lib/mysql-connector-java-5.1.32-bin.jar --executor-memory 1g

端口:10000

服务器:feng02

spark master:spark://feng02:7077

driver-class-path:mysql驱动包(hive配置的)

[plain]  view plain copy

1. [jifeng@feng02 spark-1.2.0-bin-2.4.1]$ ./sbin/start-thriftserver.sh  --hiveconf hive.server2.thrift.port=10000  --hiveconf hive.server2.thrift.bind.host=feng02 --master spark://feng02:7077 --driver-class-path /home/jifeng/hadoop/spark-1.2.0-bin-2.4.1/lib/mysql-connector-java-5.1.32-bin.jar  
2. starting org.apache.spark.sql.hive.thriftserver.HiveThriftServer2, logging to /home/jifeng/hadoop/spark-1.2.0-bin-2.4.1/sbin/../logs/spark-jifeng-org.apache.spark.sql.hive.thriftserver.HiveThriftServer2-1-feng02.out  

2:运行beeline

Now you can use beeline to test the Thrift JDBC/ODBC server:

./bin/beeline

[plain]  view plain copy

1. [jifeng@feng02 spark-1.2.0-bin-2.4.1]$ ./bin/beeline  
2. Spark assembly has been built with Hive, including Datanucleus jars on classpath  
3. Beeline version ??? by Apache Hive  

3:连接server

参考：https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients#HiveServer2Clients-BeelineExample

[plain]  view plain copy

1. beeline> !connect jdbc:hive2://feng02:10000 jifeng jifeng org.apache.hive.jdbc.HiveDriver  
2. Connecting to jdbc:hive2://feng02:10000  
3. log4j:WARN No appenders could be found for logger (org.apache.thrift.transport.TSaslTransport).  
4. log4j:WARN Please initialize the log4j system properly.  
5. log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.  
6. Connected to: Spark SQL (version 1.2.0)  
7. Driver: null (version null)  
8. Transaction isolation: TRANSACTION_REPEATABLE_READ  

4：查询

[plain]  view plain copy

1. 0: jdbc:hive2://feng02:10000> show tables;                                                                    
2. +----------------+  
3. |     result     |  
4. +----------------+  
5. | course         |  
6. | hbase_table_1  |  
7. | pokes          |  
8. | student        |  
9. +----------------+  
10. 4 rows selected (2.723 seconds)  

[plain]  view plain copy

1. 0: jdbc:hive2://feng02:10000> select * from student;  
2. +-----+----------+------+  
3. | id  |   name   | age  |  
4. +-----+----------+------+  
5. | 1   | nick     | 24   |  
6. | 2   | doping   | 25   |  
7. | 3   | caizhi   | 26   |  
8. | 4   | liaozhi  | 27   |  
9. | 5   | wind     | 30   |  
10. +-----+----------+------+  
11. 5 rows selected (10.554 seconds)  
12. 0: jdbc:hive2://feng02:10000> select a.*,b.* from student a  join course b where a.id=b.id ;  
13. +-----+----------+------+-----+-----+-----+-----+-----+  
14. | id  |   name   | age  | id  | c1  | c2  | c3  | c4  |  
15. +-----+----------+------+-----+-----+-----+-----+-----+  
16. | 1   | nick     | 24   | 1   | 英语  | 中文  | 法文  | 日文  |  
17. | 2   | doping   | 25   | 2   | 中文  | 法文  |     |     |  
18. | 3   | caizhi   | 26   | 3   | 中文  | 法文  | 日文  |     |  
19. | 4   | liaozhi  | 27   | 4   | 中文  | 法文  | 拉丁  |     |  
20. | 5   | wind     | 30   | 5   | 中文  | 法文  | 德文  |     |  
21. +-----+----------+------+-----+-----+-----+-----+-----+  
22. 5 rows selected (2.33 seconds)  

4:Java JDBC连接

[java]  view plain copy

1. package demo.test;  
2.   
3. import java.sql.*;  
4.   
5. public class Pretest {  
6.   
7.   
8.             public static void main( String args[] )  
9.                 throws SQLException , ClassNotFoundException {  
10.                 String jdbcdriver="org.apache.hive.jdbc.HiveDriver";  
11.                 String jdbcurl="jdbc:hive2://feng02:10000";  
12.                 String username="scott";  
13.                 String password="tiger";          
14.                 Class.forName(jdbcdriver);  
15.                 Connection c = DriverManager.getConnection(jdbcurl,username,password);   
16.                 Statement st = c.createStatement();               
17.                 print( "num should be 1 " , st.executeQuery("select * from student"));  
18.   
19.                 // TODO indexing  
20.             }  
21.              static void print( String name , ResultSet res )  
22.                         throws SQLException {  
23.                         System.out.println( name);  
24.                         ResultSetMetaData meta=res.getMetaData();                         
25.                         //System.out.println( "\t"+res.getRow()+"条记录");  
26.                         String  str="";  
27.                         for(int i=1;i<=meta.getColumnCount();i++){  
28.                             str+=meta.getColumnName(i)+"   ";  
29.                             //System.out.println( meta.getColumnName(i)+"   ");  
30.                         }  
31.                         System.out.println("\t"+str);  
32.                         str="";  
33.                         while ( res.next() ){  
34.                             for(int i=1;i<=meta.getColumnCount();i++){     
35.                                 str+= res.getString(i)+"   ";                           }   
36.                             System.out.println("\t"+str);  
37.                             str="";  
38.                         }  
39.                     }         
40. }  

上面是运行参数

结果显示：

[java]  view plain copy

1. num should be 1   
2.     id   name   age     
3.     1   nick   24     
4.     2   doping   25     
5.     3   caizhi   26     
6.     4   liaozhi   27     
7.     5   wind   30