第五章：spark-sql实验使用python操作流程

启动hdfs

/opt/module/hadoop-3.1.3/sbin/start-dfs.sh

启动spark集群

/opt/module/spark-standalone/sbin/start-all.sh

查看集群启动情况

jpsall

注：下方标红路径根据自己实际文件位置

☀️有问题及时联系🎅

[1] question 1

-----------启动spark集群之后----------------------------------------------------
打开pyspark （python版本3.X）

bin/pyspark

// 以dataFrame形式导入json数据到spark中

{"id":1 ,"name":" Ella","age":36}
{"id":2,"name":"Bob","age":29}
{"id":3 ,"name":"Jack","age":29}
{"id":4 ,"name":"Jim","age":28}
{"id":5 ,"name":"Damon"}
{"id":5 ,"name":"Damon"}

df = spark.read.json(“file:// /home/wsf/worksapces/spark-python/datas/spark_sql_datas/employee.json”)

(1)查询DataFrame的所有数据
df.show()

(2)查询所有数据，并去除重复的数据
df.distinct().show()

(3)查询所有数据，打印时去除id字段
df.drop(“id”).show()

(4)筛选age>20的记录
df.filter(df.age > 30 ).show()

(5)将数据按name分组
df.groupBy(“name”).count().show()

(6)将数据按name升序排列
df.sort(df.name.asc()).show()

(7)取出前3行数据
df.take(3)

(8)查询所有记录的name列，并为其取别名为username
df.select(df.name.alias(“username”)).show()

(9)查询年龄age的平均值
df.agg({“age”: “mean”}).show()

(10)查询年龄age的最大值
df.agg({“age”: “max”}).show()

退出pysaprk

exit()

[2] question 2

修改rddtodf1.py中文件路径，要与自己虚拟机上的数据路径保持一致

// txt文档
1,Ella,36
2,Bob,29
3,Jack,29

执行py程序，查看结果
python3 /home/wsf/worksapces/spark-python/rddtodf1.py

from pyspark.conf import SparkConf
from pyspark.sql.session import SparkSession
from pyspark import SparkContext
from pyspark.sql.types import Row
from pyspark.sql import SQLContext

if __name__ == "__main__":
    sc = SparkContext("local", "Simple App")
    spark = SparkSession(sc)

    # 导入文本，创建RDD
    peopleRDD = sc.textFile("file:///home/wsf/worksapces/spark-python/datas/spark_sql_datas/employee.txt")
    
    # solve the question:AttributeError: 'PipelinedRDD' object has no attribute 'toDF'
    sqlContext = SQLContext(sc)

    # 对RDD进行切分处理，并转为DataFrame
    rowRDD = peopleRDD.map(lambda line: line.split(",")).map(lambda attributes: Row(int(attributes[0]), attributes[1], int(attributes[2]))).toDF()

    # 创建mysql查询的临时表
    rowRDD.createOrReplaceTempView("employee")

    # sql查询DataFrame
    personsDF = spark.sql("select * from employee")

    # 将DataFrame转为rdd，并输出到控制台
    personsDF.rdd.map(lambda t: "id:" + str(t[0]) + "," + "Name:" + t[1] + "," + "age:" + str(t[2])).foreach(print)

[3] question 3

[1] 在master上导入数据库
mysql -uroot -p123 </home/wsf/worksapces/spark-python/datas/spark_sql_datas/employee.sql
有输出数据代表输出成功

# 编写脚本，直接导入数据库
# 脚本式执行sql语句
# mysql -uroot -p123<D:\web前端技术\server\MYSQL\01.sql


DROP DATABASE IF EXISTS sparktest;
CREATE DATABASE sparktest;

# 进入sparktest数据库
USE sparktest;

# 判断表employee是否存在
# DROP TABLE IF EXISTS employee

CREATE TABLE employee(
  id INT(4), # 编号
  name VARCHAR(20), # 姓名
  gender VARCHAR(4), # 性别
  age INT(4) # 年龄
);

# 插入两条数据
INSERT INTO employee VALUES(1,'Alice','F',22),(2,'jony','M',25);

#控制台输出创建表后的结果
SELECT * FROM employee;

[2]配置jdbc连接（可从官网下载，如果数据库为8版本，最好配套使用对应版本驱动）
将文件中mysql-connector-java-5.1.27-bin.jar包放到下面目录中
/opt/module/spark-standalone/jars

执行以下语句
cp mysql-connector-java-5.1.27-bin.jar /opt/module/python3/lib/python3.6/site-packages/pyspark/jars

执行py程序，查看结果
python3 /home/wsf/worksapces/spark-python/testmysql.py

from pyspark import SparkContext
from pyspark.sql import SQLContext
from pyspark.sql.types import Row
from pyspark.sql.types import StructType
from pyspark.sql.types import StructField
from pyspark.sql.types import StringType
from pyspark.sql.types import IntegerType

if __name__ == "__main__":
    sc = SparkContext( 'local', 'test')
    spark=SQLContext(sc)
    jdbcDF=spark.read.format("jdbc").option("url","jdbc:mysql://localhost:3306/sparktest").option("driver","com.mysql.jdbc.Driver").option("dbtable","employee")\
.option("user", "root").option("password", "123").load()

    jdbcDF.filter(jdbcDF.age>20).collect() #检测是否连接成功
    studentRDD = sc.parallelize(["3 Mary F 26","4 Tom M 23"]).map(lambda line : line.split(" "))
    schema = StructType([StructField("id",IntegerType(),True),StructField("name", StringType(), True),StructField("gender", StringType(), True),StructField("age",IntegerType(), True)])
    rowRDD = studentRDD.map(lambda p : Row(int(p[0]),p[1].strip(), p[2].strip(),int(p[3])))

    employeeDF = spark.createDataFrame(rowRDD, schema)
    prop = {}
    prop['user'] = 'root'
    prop['password'] = '123'
    prop['driver'] = "com.mysql.jdbc.Driver"
    employeeDF.write.jdbc("jdbc:mysql://localhost:3306/sparktest",'employee','append', prop)
    jdbcDF.collect()
    jdbcDF.agg({"age": "max"}).show()
    jdbcDF.agg({"age": "sum"}).show()

关闭spark集群

/opt/module/spark-standalone/sbin/stop-all.sh

关闭hdfs

/opt/module/hadoop-3.1.3/sbin/stop-dfs.sh

查看集群关闭情况

jpsall

实验所需文件和数据可通过百度网盘下载

链接：https://pan.baidu.com/s/1pZToR6USvn0pihU6CX9EeA?pwd=1314
提取码：1314