步骤分析
idea如果想通过spark连接hive,首先pom文件中需要添加hive,除此之外必须要把hive-site.xml放到idea的resources下面。hive-site.xml里面是配置的hive的元数据库地址,hdfs-site.xml、core-cite.xml可以选择是否放进去。Windows系统操作hadoop肯定会出现一个警告。找不到winutils.exe。这个警告一般不用管,但是要想连接hive就必须得消除它,消除它只需要去GitHub上面下载hadoop-common-2.2.0-bin-master.zip。解压后把这个包的bin路径配置到Windows的环境变量下面就可以解决。解压后的这个包的bin目录下面有winutils.exe,找到这个就不会报错了。
操作步骤
1.配置idea的pom文件
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>www.ruozedata.bigdata</groupId>
<artifactId>g5-spark</artifactId>
<version>1.0</version>
<inceptionYear>2008</inceptionYear>
<properties>
<scala.version>2.11.8</scala.version>
<spark.version>2.4.0</spark.version>
<hadoop.version>2.6.0-cdh5.7.0</hadoop.version>
<scalikejdbc.version>2.5.2</scalikejdbc.version>
<mysql.version>5.1.38</mysql.version>
</properties>
<repositories>
<repository>
<id>scala-tools.org</id>
<name>Scala-Tools Maven2 Repository</name>
<url>http://scala-tools.org/repo-releases</url>
</repository>
<repository>
<id>cloudera</id>
<url>https://repository.cloudera.com/artifactory/cloudera-repos/</url>
</repository>
</repositories>
<pluginRepositories>
<pluginRepository>
<id>scala-tools.org</id>
<name>Scala-Tools Maven2 Repository</name>
<url>http://scala-tools.org/repo-releases</url>
</pluginRepository>
</pluginRepositories>
<dependencies>
<!--Scala dependency-->
<dependency>
<groupId>org.scala-lang</groupId>
<artifactId>scala-library</artifactId>
<version>${scala.version}</version>
</dependency>
<!--Spark core dependency-->
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-core_2.11</artifactId>
<version>${spark.version}</version>
</dependency>
<!--hadoop dependency-->
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-client</artifactId>
<version>${hadoop.version}</version>
</dependency>
<!--Test dependency-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<!--scalikejdbc dependency-->
<dependency>
<groupId>org.scalikejdbc</groupId>
<artifactId>scalikejdbc_2.11</artifactId>
<version>${scalikejdbc.version}</version>
</dependency>
<dependency>
<groupId>org.scalikejdbc</groupId>
<artifactId>scalikejdbc-config_2.11</artifactId>
<version>${scalikejdbc.version}</version>
</dependency>
<!--mysql dependency-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
<!--日志-->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-repl_2.11</artifactId>
<version>${spark.version}</version>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-sql_2.11</artifactId>
<version>${spark.version}</version>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-hive_2.11</artifactId>
<version>${spark.version}</version>
</dependency>
</dependencies>
<build>
<sourceDirectory>src/main/scala</sourceDirectory>
<testSourceDirectory>src/test/scala</testSourceDirectory>
<plugins>
<plugin>
<groupId>org.scala-tools</groupId>
<artifactId>maven-scala-plugin</artifactId>
<executions>
<execution>
<goals>
<goal>compile</goal>
<goal>testCompile</goal>
</goals>
</execution>
</executions>
<configuration>
<scalaVersion>${scala.version}</scalaVersion>
<args>
<arg>-target:jvm-1.5</arg>
</args>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-eclipse-plugin</artifactId>
<configuration>
<downloadSources>true</downloadSources>
<buildcommands>
<buildcommand>ch.epfl.lamp.sdt.core.scalabuilder</buildcommand>
</buildcommands>
<additionalProjectnatures>
<projectnature>ch.epfl.lamp.sdt.core.scalanature</projectnature>
</additionalProjectnatures>
<classpathContainers>
<classpathContainer>org.eclipse.jdt.launching.JRE_CONTAINER</classpathContainer>
<classpathContainer>ch.epfl.lamp.sdt.launching.SCALA_CONTAINER</classpathContainer>
</classpathContainers>
</configuration>
</plugin>
</plugins>
</build>
<reporting>
<plugins>
<plugin>
<groupId>org.scala-tools</groupId>
<artifactId>maven-scala-plugin</artifactId>
<configuration>
<scalaVersion>${scala.version}</scalaVersion>
</configuration>
</plugin>
</plugins>
</reporting>
</project>
2.解决警告:找不到winutils.exe
1.下载hadoop-common-2.2.0-bin-master.zip解压到E:\maven3.3.9\App\hadoop-common-2.2.0-bin。下载地址如下:
https://github.com/srccodes/hadoop-common-2.2.0-bin
2.配置环境变量
3.实现idea通过spark连接hive
1.idea里面创建一个resources,把hive-site.xml hdfs-site.xml core-cite.xml放进去
hive-site.xml配置如下:
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://192.168.2.65:3306/ruoze_d5?createDatabaseIfNotExist=true&characterEncoding=UTF-8</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>******</value>
</property>
<property>
<name>hive.cli.print.current.db</name>
<value>true</value>
</property>
</configuration>
idea连接hive的代码及运行结果
1.idea代码
import org.apache.spark.sql.SparkSession
object SparkSessionApp1 {
def main(args: Array[String]): Unit = {
val sparkSession=SparkSession.builder()
.appName("SparkSessionApp")
.config("spark.sql.warehouse.dir", "/tmp/hive/")
.enableHiveSupport()//使用到hive一定要开启这个,idea要想连接hive得在pom中加上hive
.master("local[2]")
.getOrCreate()
val gg=sparkSession.sql("show tables")
gg.show
sparkSession.stop()
}
}
2.运行结果
可能会遇到的问题
1.hive-site.xml 里面配置的数据库是一开始的时候在hive中以root用户创建的,所以mysql里面可能没有给它其他ip访问的权限,所以Windows的IP地址它肯定会报权限错误。这时候千万不要异想天开给hive-site.xml 换另一个mysql里面的%权限的数据库,这样虽然解决了权限错误,但是这个拥有%的数据库并不是hive的元数据库,它里面没有hive的元数据信息,所以就会报空指针异常的错误,也就意味着拿不到元数据信息。那么就只能按部就班去mysql里面改hive-site.xml 里面配置的数据库权限,给我们的Windows的IP一个访问它的权限,我的解决方式如下:
2.解决方法
mysql> use mysql;
mysql> select user,password,host from user;
此时其实发现root用户我已经给了host一个%的权限了,但是还是报了权限错误(其实是因为我当时改完权限忘了刷新权限。。。后来才想起来是这个原因)
看到已经给了root用户%的权限,接下来我就查root用户下面host为%的数据库,竟然没有。。
mysql> show grants for 'root'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'root' on host '%'
接着我又给hive-site.xml 里面配置的数据库ruoze_d5一个% ,然而报错了,意思是在user表里找不到这个这个用户。但是使用select查询user表,是可以查到的,原因是我们创建用户后,只是写入了user表,并没有写入权限表(grant table)。所以应该刷新权限
mysql> grant all privileges on ruoze_d5.* to root@'%' identified by '******';
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> flush privileges;刷新权限
Query OK, 0 rows affected (0.00 sec)
接着我又重新给它 ruoze_d5这个数据库下面的数据给了它root用户的。其实这一步本可以不用做的。。。因为刷新权限之后我之前配置的%的权限应该也是生效了的。从下图就能看到有一个是给了* ,也就是之前修改的权限给了root用户下面的所有的host都能访问的权限,上面没查到就是因为忘了刷新权限。。。还有一个是ruoze_d5也就是刚刚才改的那个
mysql> grant all privileges on ruoze_d5.* to root@'%' identified by '******';
Query OK, 0 rows affected (0.00 sec)
再次查询root用户下面host为%的数据库
mysql> show grants for 'root'@'%';
改完之后去idea运行,权限问题解决,成功访问hive的元数据