SparkSQL

spark-shell读取文件创建DataFrame

parquet文件转换DataFrame

DataFrame保存成parquet文件

spark通过JDBC连接mysql

• 复制mysql驱动到spark目录

• mysql建表

mysql> create database spark;
mysql> use spark
mysql> create table student (id int(4), name char(20), gender char(4), age int(4));
mysql> insert into student values(1,'Xueqian','F',23);
mysql> insert into student values(2,'Weiliang','M',24);
mysql> select * from student;

• idea编写功能

build.sbt

name := "HbaseOperation"

version := "0.1"

scalaVersion := "2.11.0"

libraryDependencies += "org.apache.spark" %% "spark-core" % "2.4.3"
libraryDependencies += "org.apache.spark" %% "spark-sql" % "2.4.3"
libraryDependencies += "mysql" % "mysql-connector-java" % "5.1.32"
libraryDependencies += "org.apache.hbase" % "hbase-client" % "1.1.5"
libraryDependencies += "org.apache.hbase" % "hbase-common" % "1.1.5"
libraryDependencies += "org.apache.hbase" % "hbase-server" % "1.1.5"

测试代码

import java.util.Properties
import org.apache.spark.sql.{SQLContext, Row}
import org.apache.spark.sql.types.{StringType, IntegerType, StructField, StructType}
import org.apache.spark.{SparkConf, SparkContext}

object SparkWriteMysql {
  def main(args: Array[String]): Unit = {
    val sparkConf = new SparkConf().setAppName("SparkWriteMysql").setMaster("local")
    val sc = new SparkContext(sparkConf)
    val sqlContext = new SQLContext(sc)
    //下面我们设置两条数据表示两个学生信息
    val studentRDD = sc.parallelize(Array("3 Rongcheng M 26", "4 Guanhua M 27")).map(_.split(" "))
    //下面要设置模式信息
    val schema = StructType(List(StructField("id", IntegerType, true), StructField("name", StringType, true), StructField("gender", StringType, true), StructField("age", IntegerType, true)))
    //下面创建Row对象，每个Row对象都是rowRDD中的一行
    val rowRDD = studentRDD.map(p => Row(p(0).toInt, p(1).trim, p(2).trim, p(3).toInt))
    //建立起Row对象和模式之间的对应关系，也就是把数据和模式对应起来
    val studentDataFrame = sqlContext.createDataFrame(rowRDD, schema)
    //下面创建一个prop变量用来保存JDBC连接参数
    val prop = new Properties()
    prop.put("user", "irdms") //表示用户名是root
    prop.put("password", "irdms") //表示密码是hadoop
    prop.put("driver", "com.mysql.jdbc.Driver") //表示驱动程序是com.mysql.jdbc.Driver
    //下面就可以连接数据库，采用append模式，表示追加记录到数据库spark的student表中
    studentDataFrame.write.mode("append").jdbc("jdbc:mysql://172.18.0.104:3306/spark", "spark.student", prop)

  }
}

• 测试

sbt package

在spark的bin目录下提交任务

./spark-submit --driver-class-path /home/hadoop/spark-2.5.3/jars/*:/opt/hbase-2.2.0/conf --class "SparkWriteMysql"  /home/hadoop/hbaseoperation_2.11-0.1.jar

提示错误：Exception in thread "main" java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

解决：把mysql驱动复制到spark/jars目录

再次查询student表数据

spark读写hive数据

1. 打开A终端，在hive中创建sparktest.student表 插入数据

2. 修改spark-env.sh

export SPARK_DIST_CLASSPATH=$(/opt/hadoop-3.1.2/bin/hadoop classpath)
export JAVA_HOME=/usr/lib/jvm/default-java
export CLASSPATH=$CLASSPATH:/home/hadoop/hive-2.3.5/lib
export SCALA_HOME=/home/hadoop/scala-2.4.3
export HADOOP_CONF_DIR=/opt/hadoop-3.1.2/etc/hadoop
export HIVE_CONF_DIR=/home/hadoop/hive-2.3.5/conf
export SPARK_CLASSPATH=$SPARK_CLASSPATH:/home/hadoop/hive-2.3.5/lib/mysql-connector-java-5.1.32.jar 

3. 打开B终端，进入spark-shell

输入以下命令：

报错：

解决办法：hive的配置文件hive-site.xml，将该文件复制到spark的conf目录下，重新运行spark shell

运行结果：

4. spark写入hive数据

查看hive数据

转载于:https://my.oschina.net/odetteisgorgeous/blog/3084589