SparkSQL（14）：jdbc访问hive表

最新推荐文章于 2024-08-04 00:33:27 发布

RayBreslin

最新推荐文章于 2024-08-04 00:33:27 发布

阅读量2.9k

点赞数

分类专栏：大数据开发 Spark hive 文章标签： SparkSQL jdbc hive

本文链接：https://blog.csdn.net/u010886217/article/details/82916492

版权

大数据开发同时被 3 个专栏收录

204 篇文章 8 订阅

订阅专栏

Spark

68 篇文章 0 订阅

订阅专栏

hive

51 篇文章 3 订阅

订阅专栏

一、目的：

使用jdbc访问hive表

二、操作：

1.前提

开启thriftserver

sbin/start-thriftserver.sh  \
--master local[2] \
--jars /opt/datas/mysql-connector-java-5.1.27-bin.jar  \
--hiveconf hive.server2.thrift.port=14000

2.如果配置hivemetastore则需要开启

服务器：

bin/hive --service metastore &

3.加载hive-jdbc依赖

（1）虚拟机

    <dependency>
      <groupId>org.spark-project.hive</groupId>
      <artifactId>hive-jdbc</artifactId>
      <version>0.13.1</version>
    </dependency>

（2）服务器

服务器环境：centos7.4，cdh5.7.0的版本，spark2.1.0，所以依赖是：

<!-- https://mvnrepository.com/artifact/org.spark-project.hive/hive-jdbc -->
<dependency>
    <groupId>org.spark-project.hive</groupId>
    <artifactId>hive-jdbc</artifactId>
    <version>1.2.1.spark2</version>
</dependency>

4.代码

（1）虚拟机

package SparkSQL

import java.sql.DriverManager

/**
  * 通过jdbc的方式访问
  */
object SparkSQLThriftServerApp {
  def main(args: Array[String]): Unit = {

    Class.forName("org.apache.hive.jdbc.HiveDriver")

    val conn=DriverManager.getConnection("jdbc:hive2://bigdata.ibeifeng.com:14000","bigdata.ibeifeng.com","")
    val pstmt=conn.prepareStatement("select empno,ename,sal from imooc.emp")
    val rs =pstmt.executeQuery()
    while (rs.next()){
      println("empno:"+rs.getInt("empno")+",ename:"+rs.getString("ename")+
        ",sal:"+rs.getDouble("sal"))
    }

    rs.close()
    pstmt.close()


  }
}

（2）服务器

package sparkworking
import java.sql.DriverManager

object _05SparksqlThriftServerApp {
  def main(args: Array[String]): Unit = {
    //1.拿到驱动
    Class.forName("org.apache.hive.jdbc.HiveDriver")

    //2.获得connection
    //使用启动beeline的时候的url：bin/beeline -u jdbc:hive2://hadoop:14000 -n hadoop
    val conn=DriverManager.getConnection("jdbc:hive2://hadoop:14000","hadoop","")
    //3.执行sql语句
    val pstmt=conn.prepareStatement("select empno,ename from emp")
    val rs=pstmt.executeQuery()

    //4.获得结果
    while (rs.next()){
      println("empno:"+rs.getInt("empno")+" ,ename:"+rs.getString("ename"))
    }

    //5.关闭
    rs.close()
    pstmt.close()
    conn.close()

  }
}
结果：
empno:7369 ,ename:SMITH
empno:7499 ,ename:ALLEN
empno:7521 ,ename:WARD
empno:7566 ,ename:JONES
empno:7654 ,ename:MARTIN
empno:7698 ,ename:BLAKE
empno:7782 ,ename:CLARK
empno:7788 ,ename:SCOTT
empno:7839 ,ename:KING
empno:7844 ,ename:TURNER
empno:7876 ,ename:ADAMS
empno:7900 ,ename:JAMES
empno:7902 ,ename:FORD
empno:7934 ,ename:MILLER