一、SparkSQL读写数据的方式
复习：
如何读取json? df = spark.read.json("json文件") --> 读取到之后直接就有表结构了
读取csv ? df = spark.read.csv("csv文件") --> 表结构只有一列，叫做valueSparkSQL将所有常用的数据源接口都进行了封装，只需要指定读写的类型和地址就可以实现读写。
1）输入Source
- 类型：text / csv【任意固定分隔符】 / json / orc / parquet / jdbc / table【Hive表】
- 语法：spark.read.format(格式).load(读取的地址)
方式一：给定读取数据源的类型和地址
spark.read.format("json").load(path)
spark.read.format("csv").load(path)
spark.read.format("parquet").load(path)方式二：直接调用对应数据源类型的方法
spark.read.json(path)
spark.read.csv(path)
spark.read.parquet(path)特殊参数：option，用于指定读取时的一些配置选项
spark.read.format("csv").option("sep", "\t").load(path)
jdbcDF = spark.read \
.format("jdbc") \
.option("url", "jdbc:postgresql:dbserver") \
.option("dbtable", "schema.tablename") \
.option("user", "username") \
.option("password", "password") \
.load()1、代码演示最普通的文件读取方式：
from pyspark.sql import SparkSession
"""
------------------------------------------
Description : TODO：
SourceFile : _02_SparkSQLReadDemo
Author : 闫哥
Date : 2025/3/21 星期五
-------------------------------------------
"""
if __name__ == '__main__':
with SparkSession.builder.master("local[2]").appName("").config(
"spark.sql.shuffle.partitions", 2).getOrCreate() as spark:
# 读取数据，我们以前学的是sparkCore
spark.read.json("../../datas/sparksql/person.json").show()
spark.read.format("json").load("../../datas/sparksql/person.json").show()
spark.read.csv("../../datas/emp/dept.csv").show()
spark.read.format("csv").option("sep","\t").load("../../datas/emp/emp.tsv").show()
spark.read.load(
"../../datas/emp/emp.tsv",
format="csv",
sep="\t"
).show()
2） 通过jdbc读取数据库数据
先在本地数据库或者linux数据库中插入一张表：
CREATE TABLE `emp` (
`empno` int(11) NULL DEFAULT NULL,
`ename` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`job` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`mgr` int(11) NULL DEFAULT NULL,
`hiredate` date NULL DEFAULT NULL,
`sal` decimal(7, 2) NULL DEFAULT NULL,
`comm` decimal(7, 2) NULL DEFAULT NULL,
`deptno` int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20);
INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30);
INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30);
INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20);
INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30);
INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30);
INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10);
INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, NULL, 20);
INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10);
INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30);
INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, NULL, 20);
INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30);
INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20);
INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);dept的数据：
CREATE TABLE `dept` (
`deptno` int(11) NULL DEFAULT NULL,
`dname` varchar(14) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`loc` varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES (40, 'OPERATIONS', 'BOSTON');接着放驱动程序：
py4j.protocol.Py4JJavaError: An error occurred while calling o67.load.
: java.lang.ClassNotFoundException: com.mysql.cj.jdbc.Driver
at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
at java.lang.ClassLoader.loadClass(ClassLoader.java:418)
at java.lang.ClassLoader.loadClass(ClassLoader.java:351)
at org.apache.spark.sql.execution.datasources.jdbc.DriverRegistry$.register(DriverRegistry.scala:46)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.$anonfun$driverClass$1(JDBCOptions.scala:102)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.$anonfun$driverClass$1$adapted(JDBCOptions.scala:102)Python环境放入MySQL连接驱动
- 找到工程中pyspark库包所在的环境，将驱动包放入环境所在的jars目录中
- 如果是Linux上：注意集群模式所有节点都要放。
第一种情况：
假如你是windows环境：
我的最终的路径是在这里：
第二种情况：linux环境下，按照如下方式进行
# 进入目录
cd /opt/installs/anaconda3/lib/python3.8/site-packages/pyspark/jars
# 上传jar包：mysql-connector-java-5.1.32.jar随堂代码练习：
spark.read.format("jdbc") \
.option("url", "jdbc:mysql://localhost:3306/edu") \
.option("dbtable", "emp") \
.option("user", "root") \
.option("password", "123456") \
.load() .show()3) 读取table中的数据【hive】
假如领导给了一个海量数据，让你处理？两种思路
海量数据：存储在hdfs上
第一种：
使用spark读取hdfs上的数据（可以使用sparkCore读取，也可以使用sparksql读取），将数据变为表【数据+Schema】，然后编写sql或者sparkCore代码。
rdd --> dataFrame
第二种：推荐
将hdfs上的数据映射成hive的表，然后通过sparkSql连接hive, 编写 sql 处理需求。
- 场景：Hive底层默认是MR引擎，计算性能特别差，一般用Hive作为数据仓库，使用SparkSQL对Hive中的数据进行计算
-
- 存储：数据仓库：Hive：将HDFS文件映射成表
- 计算：计算引擎：SparkSQL、Impala、Presto：对Hive中的数据表进行处理
- 问题：SparkSQL怎么能访问到Hive中有哪些表，以及如何知道Hive中表对应的HDFS的地址？
Hive中的表存在哪里？元数据--MySQL , 启动metastore服务即可。
本质上：SparkSQL访问了Metastore服务获取了Hive元数据，基于元数据提供的地址进行计算
先退出base环境：conda deactivate
启动服务：
启动hdfs: start-dfs.sh 因为hive的数据在那里存储着
启动yarn: start-yarn.sh 因为spark是根据yarn部署的，假如你的spark是standalone模式，不需要启动yarn.
日志服务也需要启动一下：
mapred --daemon start historyserver
# 启动Spark的HistoryServer:18080
/opt/installs/spark/sbin/start-history-server.sh
启动metastore服务： 因为sparkSQL需要知道表结构，和表数据的位置
hive-server-manager.sh start metastore
启动spark服务： 啥服务也没有了，已经启动完了。
查看metastore服务：
hive-server-manager.sh status metastore修改配置：
是进入到spark目录，不要进hive目录
cd /opt/installs/spark/conf
新增：hive-site.xml
vi hive-site.xml
在这个文件中，编写如下配置：
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>hive.metastore.uris</name>
<value>thrift://caijing:9083</value>
</property>
</configuration>
接着将该文件进行分发：
xsync.sh hive-site.xml操作sparkSQL：
/opt/installs/spark/bin/pyspark --master local[2] --conf spark.sql.shuffle.partitions=2此处的pyspark更像是一个客户端，里面可以通过python编写spark代码而已。而我们以前安装的pyspark更像是spark的python运行环境。
进入后，通过内置对象spark:
>>> spark.sql("show databases").show()
+---------+
|namespace|
+---------+
|caijing01|
|caijing02|
| default|
| finance|
+---------+
>>> spark.sql("select * from caijing01.t_user").show()
+---+----------+
| id| name|
+---+----------+
| 1| 宝总|
| 2| 李李|
| 3| 小汪|
| 4| 爷叔|
| 10| zhangfei|
| 11| zhaoyun|
| 12|zhugeliang|
| 1| 宝总|
| 2| 李李|
| 3| 小汪|
| 4| 爷叔|
+---+----------+开发环境如何编写代码，操作hive:
Pycharm工具集成Hive开发SparkSQL，必须申明Metastore的地址和启用Hive的支持
spark = SparkSession \
.builder \
.appName("HiveAPP") \
.master("local[2]") \
.config("spark.sql.warehouse.dir", 'hdfs://bigdata01:9820/user/hive/warehouse') \
.config('hive.metastore.uris', 'thrift://bigdata01:9083') \
.config("spark.sql.shuffle.partitions", 2) \
.enableHiveSupport()\
.getOrCreate()代码实战：
from pyspark.sql import SparkSession
"""
------------------------------------------
Description : TODO：
SourceFile : _03_sparksql操作hive
Author : 闫哥
Date : 2025/3/21 星期五
-------------------------------------------
"""
if __name__ == '__main__':
spark = SparkSession \
.builder \
.appName("sparksql操作hive") \
.master("local[2]") \
.config("spark.sql.warehouse.dir", 'hdfs://caijing:9820/user/hive/warehouse') \
.config('hive.metastore.uris', 'thrift://caijing:9083') \
.config("spark.sql.shuffle.partitions", 2) \
.enableHiveSupport() \
.getOrCreate()
spark.sql("""
select * from caijing01.t_user
""").show()
spark.stop()代码还可以这样写：
方式二：加载Hive表的数据变成DF，可以调用DSL或者SQL的方式来实现计算
# 读取Hive表构建DataFrame
hiveData = spark.read.table("yhdb.student")
hiveData.printSchema()
hiveData.show()
# 读取hive表中的数据
spark2 = SparkSession \
.builder \
.appName("HiveAPP") \
.master("local[2]") \
.config("spark.sql.warehouse.dir", 'hdfs://192.168.233.128:9820/user/hive/warehouse') \
.config('hive.metastore.uris', 'thrift://192.168.233.128:9083') \
.config("spark.sql.shuffle.partitions", 2) \
.enableHiveSupport() \
.getOrCreate()
#spark2.sql("show databases").show()
#spark2.sql("show tables").show()
#spark2.sql("select * from yhdb.t_user").show()
spark2.read.table("t_user2").show()不要在一个python 文件中，创建两个不同的sparkSession对象，否则对于sparksql获取hive的元数据，有影响。另外，记得添加一个权限校验的语句：
# 防止在本地操作hdfs的时候，出现权限问题
os.environ['HADOOP_USER_NAME'] = 'root'为什么有些平台不支持，不兼容 sqoop flume datax 这些工具呢？
spark 可以读取日志数据
spark 可以读取数据库数据
spark 可以读取 hdfs 数据
spark 可以读取 hive 数据
------------------------------------
spark 可以读取日志数据，形成一个 A 表，读取 mysql 数据，形成一个 B 表
A 表和 B 表还可以相互关联，此时也就不需要 sqoop、flume、datax 去导入导出了。
spark 还可以将统计出来的结果直接放入 mysql 或者直接放入 hive
--------------------
我们后面学习的内容还是沿用 将日志数据，数据库数据等所有数据抽取到 hive ，然后呢，使用 spark 去统计，统计完之后还是放入 hive ,使用 datax 等工具将结果导出 mysql。
2）输出Sink
sink --> 下沉 --> 落盘 --> 保存起来
如果输出路径或者表已经存在了怎么办
- 类型：text /csv【所有具有固定分隔符的文件】/ json/ orc/ parquet / jdbc / table【Hive表】
- 语法：DataFrame.write.format(保存的类型).save(保存到哪)
-
- 方法：save-保存到文件save(path)或者数据库表save()中，saveAsTable-用于保存到Hive表
方式一：给定输出数据源的类型和地址
df.write.format("json").save(path)
df.write.format("csv").save(path)
df.write.format("parquet").save(path)方式二：直接调用对应数据源类型的方法
df.write.json(path)
df.write.csv(path)
df.write.parquet(path)特殊参数：option，用于指定输出时的一些配置选项
df.write \
.format("jdbc") \
.option("url", "jdbc:postgresql:dbserver") \
.option("dbtable", "schema.tablename") \
.option("user", "username") \
.option("password", "password") \
.save()输出模式：Save Mode
append: 追加模式，当数据存在时，继续追加
overwrite: 覆写模式，当数据存在时，覆写以前数据，存储当前最新数据；
error/errorifexists: 如果目标存在就报错，默认的模式
ignore: 忽略，数据存在时不做任何操作代码如何编写：
df.write.mode(saveMode="append").format("csv").save(path)实战一：保存普通格式：
from pyspark.sql import SparkSession
"""
------------------------------------------
Description : TODO：
SourceFile : _04_sparksql_sink
Author : 闫哥
Date : 2025/3/21 星期五
-------------------------------------------
"""
if __name__ == '__main__':
spark = SparkSession.builder.master("local[2]").appName("").config(
"spark.sql.shuffle.partitions", 2).getOrCreate()
df = spark.read.json("../../datas/sparksql/person.json")
df.show()
df.createOrReplaceTempView("person")
# # 获取年龄最大的人的名字
maxAgeDf = spark.sql("""
select * from person where age = (select max(age) from person)
""")
maxAgeDf.show()
# 将数据写入文件中
maxAgeDf.write.csv("../../datas/sparksql/maxAge",mode="overwrite")
maxAgeDf.write.mode("overwrite").format("csv").save("../../datas/sparksql/maxAge1")
# text 保存路径为hdfs 直接报错，不支持,需要将结果拼接为一个长的字符串，text格式，只支持一列，并且这一列必须是string类型的
#maxAgeDf.write.mode(saveMode='overwrite').text("hdfs://caijing:9820/result")
maxAgeDf.write.orc("hdfs://caijing:9820/result",mode="overwrite")
# maxAgeDf.write.parquet("hdfs://bigdata01:9820/result", mode="overwrite")
spark.stop()假如:
spark.sql("select concat(name,' ',age) from person").write.text("hdfs://caijing:9820/spark/result")
直接报错：假如你的输出类型是text类型，直接报错
pyspark.sql.utils.AnalysisException: Text data source does not support bigint data type.
假如修改为parquet等类型，是可以直接保存的：
rsDf.write.parquet("hdfs://caijing:9820/result")
假如你将上面的sql语句，变为如下格式，就可以保存啦：
spark.sql("select concat(name,' ',cast(age as string)) from person").write.text("hdfs://caijing:9820/spark/result") 实战二：保存到数据库中：
代码演示：
from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, IntegerType, StringType, DoubleType
"""
------------------------------------------
Description : TODO：
SourceFile : _04_sparksql_sink
Author : 闫哥
Date : 2025/3/21 星期五
-------------------------------------------
"""
if __name__ == '__main__':
spark = SparkSession.builder.master("local[2]").appName("").config(
"spark.sql.shuffle.partitions", 2).getOrCreate()
emp_schema = StructType([
StructField("empno", IntegerType(), True),
StructField("ename", StringType(), True),
StructField("salary", DoubleType(), True),
StructField("jiangjin", DoubleType(), True),
StructField("deptno", IntegerType(), True),
])
df = spark.read.format("csv").option("sep","\t").load("../../datas/emp/emp.tsv",schema=emp_schema)
df.write.format("jdbc") \
.option("url", "jdbc:mysql://localhost:3306/edu") \
.option("dbtable", "emp2") \
.option("user", "root") \
.option("password", "123456") \
.mode("overwrite") \
.save()
spark.stop()
CSDN：Spark中给读取到的数据 的列 重命名的几种方式！_spark withcolumnrenamed-CSDN博客
实战三：将结果保存在hive表中
from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, IntegerType, StringType, DoubleType
"""
------------------------------------------
Description : TODO：
SourceFile : _03_sparksql操作hive
Author : 闫哥
Date : 2025/3/21 星期五
-------------------------------------------
"""
if __name__ == '__main__':
spark = SparkSession \
.builder \
.appName("sparksql操作hive") \
.master("local[2]") \
.config("spark.sql.warehouse.dir", 'hdfs://caijing:9820/user/hive/warehouse') \
.config('hive.metastore.uris', 'thrift://caijing:9083') \
.config("spark.sql.shuffle.partitions", 2) \
.enableHiveSupport() \
.getOrCreate()
emp_schema = StructType([
StructField("empno", IntegerType(), True),
StructField("ename", StringType(), True),
StructField("salary", DoubleType(), True),
StructField("jiangjin", DoubleType(), True),
StructField("deptno", IntegerType(), True),
])
df = spark.read.format("csv").option("sep", "\t").load("../../datas/emp/emp.tsv", schema=emp_schema)
# 将奖金为null数据过滤掉，留下奖金不为null的数据
df.createOrReplaceTempView("t_emp")
rsDf = spark.sql("""
select * from t_emp where cast(jiangjin as int) !=0
""")
rsDf.write.saveAsTable("caijing01.emp2",mode="overwrite")
spark.stop()二：SparkSQL的UDF
- UDF：一对一的函数【User Defined Functions】
- substr、split、concat、instr、length、from_unixtime
- UDAF：多对一的函数【User Defined Aggregation Functions】 聚合函数
- count、sum、max、min、avg、collect_set/list
- UDTF：一对多的函数【User Defined Tabular Functions】
- explode、json_tuple【解析JSON格式】、parse_url_tuple【解析URL函数】Spark中支持UDF和UDAF两种，支持直接使用Hive中的UDF、UDAF、UDTF.
pyspark中自定义函数的三种写法：
假如别人问：你都自定义过哪些函数?
- 解析IP地址获取用户所在的国家、省份、城市
def transIp2Region(ip: str) -> [国家，省份，城市]
- 解析GPS地址获取用户所在的国家、省份、城市
def transGPS2Region(lng, lat) -> [国家，省份，城市]
- 对原始数据进行解密：原始数据是加密的
def fieldDecode(col) -> decode_col
- 根据身份证号判断年龄以及性别如何在sparkSQL中自定义函数呢？
使用最常用的regeister方式自定义函数
最常用的方式，这种方式编写的函数，既能用于SQL中，也能用于DSL中语法：
UDF变量名 = spark.udf.register(UDF函数名, 函数的处理逻辑)
定义：spark.udf.register()
UDF变量名：DSL中调用UDF使用的
UDF函数名：SQL中调用UDF使用案例：
查看以下数据：
id name msg
01 周杰伦 150/175
02 周杰 130/185
03 周华健 148/178
04 周星驰 130/175
05 闫妮 110/180
将以上数据，通过自定义函数，变为如下数据：
01 周杰伦 150斤/175cm
02 周杰 130斤/185cm
03 周华健 148斤/178cm第一步：自定义函数
# 编写一个普通的函数，用于写逻辑
def get_data(str1):
list1 = str1.split("/")
return list1[0] + "斤/" + list1[1] + "cm"第二步：注册函数
# 定义一个UDF：变量名-dsl = spark.udf.register（函数名-sql， 处理逻辑， 返回值）
# get_new_info 用于 sql 中
# get_info 用于DSL
get_info = spark.udf.register(name="get_new_info", f=lambda oldinfo: get_data(oldinfo))第三步：使用函数
#使用sql的方式调用
spark.sql("select id,name,get_new_info(msg) from star").show()
# 使用dsl的方式调用
# DSL：用变量名
import pyspark.sql.functions as F
new_df.select(F.col("id"), F.col("name"), F.col("msg"), get_info(F.col("msg")).alias("newinfo")).show()代码演示：
import os
from pyspark.sql import SparkSession
"""
------------------------------------------
Description : TODO：
SourceFile : _07_sparksql_udf
Author : 闫哥
Date : 2025/3/21 星期五
-------------------------------------------
"""
def get_data(str1):
arr1 = str1.split("/")
return arr1[0]+"斤/"+arr1[1]+"cm"
if __name__ == '__main__':
os.environ['JAVA_HOME'] = 'C:\Program Files\Java\jdk1.8.0_241'
# 配置Hadoop的路径，就是前面解压的那个路径
os.environ['HADOOP_HOME'] = 'D:\hadoop-3.3.1'
# 配置base环境Python解析器的路径
os.environ['PYSPARK_PYTHON'] = 'C:/ProgramData/Miniconda3/python.exe' # 配置base环境Python解析器的路径
os.environ['PYSPARK_DRIVER_PYTHON'] = 'C:/ProgramData/Miniconda3/python.exe'
spark = SparkSession.builder.master("local[2]").appName("自定义函数").config(
"spark.sql.shuffle.partitions", 2).getOrCreate()
# getmsg 用于 sql 中
# get_info 用于DSL
#get_info = spark.udf.register("getmsg", get_data)
spark.udf.register("getmsg", get_data)
spark.read.format("csv").option("sep","\t").option("header","true").load("../../datas/testudf.txt").createOrReplaceTempView("stars")
spark.sql("select name,getmsg(msg) from stars").show()
spark.stop()三：SparkSQL使用方式
1、平常做SQL测试，能不能像Hive中一样，直接启动一个命令行做测试？
我们可以使用一个命令，这个命令叫做spark-sql
# 启动HDFS服务：NameNode和DataNodes
start-dfs.sh
# 启动HiveMetaStore 服务
hive-server-manager.sh start metastore
# 设置Spark日志级别
cd /opt/installs/spark/conf/
mv log4j.properties.template log4j.properties
vi log4j.properties
#修改19行INFO为WARN
log4j.rootCategory=WARN, console接着开始启动客户端：
spark-sql --master yarn --conf spark.sql.shuffle.partitions=2
2、问题：Hive中可以使用Beeline或者DataGrip连接HiveServer2进行测试，那SparkSQL中如何实现？
ThriftServer
- 功能：类似于HiveServer2，负责解析客户端提交的SQL语句，转换成Spark的任务进行执行
- 本质：Spark中的一个特殊的程序，利用程序的资源运行所有SQL，该程序除非手动关闭，否则一直运行
/opt/installs/spark/sbin/start-thriftserver.sh \
--hiveconf hive.server2.thrift.port=10001 \
--hiveconf hive.server2.thrift.bind.host=caijing \
--master yarn \
--conf spark.sql.shuffle.partitions=2启动服务，该服务不会停止，一直在后台启动，假如启动不了，记得查看日志。
停止thiftServer:
/opt/installs/spark/sbin/stop-thriftserver.sh 使用beeline也可以连接：
/opt/installs/spark/bin/beeline -u jdbc:hive2://caijing:10001 -n root -p 123456
遇到的问题：hive中的数据库和sparksql 操作的数据库不是同一个。
解决方案：在hive 下修改hive-site.xml
<property>
<name>hive.metastore.schema.verification</name>
<value>false</value>
</property>将hive-site.xml 复制到 spark的conf 下
cp /opt/installs/hive/conf/hive-site.xml /opt/installs/spark/conf修改spark下的hive-site.xml
<property>
<name>hive.server2.thrift.port</name>
<value>10001</value>
<description>Port number of HiveServer2 Thrift interface when hive.server2.transport.mode is 'binary'.</description>
</property>接着分发一下：
xsync.sh /opt/installs/spark/conf/hive-site.xml重启thrift服务：
/opt/installs/spark/sbin/stop-thriftserver.sh
/opt/installs/spark/sbin/start-thriftserver.sh --hiveconf hive.server2.thrift.port=10001 --hiveconf hive.server2.thrift.bind.host=bigdata01 --master yarn --conf spark.sql.shuffle.partitions=2使用datagrip 重新连接一下，记得刷新一下连接。
3、在工作过程中如何使用sparkSQL
1) 使用python编写sparkSQL
使用Python代码在本地运行或者远程提交，测试SQL或者DSL的开发
- 场景：数据源多样化：Hive表、MySQL、结构化文件，可以写SQL或者DSL、RDD
- 产出：Python文件
- 运行：spark-submit
比如：
/opt/installs/spark-yarn/bin/spark-submit \
--master yarn \
--deploy-mode client \
--conf "spark.pyspark.driver.python=/opt/installs/anaconda3/bin/python3" \
--conf "spark.pyspark.python=/opt/installs/anaconda3/bin/python3" \
--driver-memory 512m \
--executor-memory 512m \
--executor-cores 1 \
--num-executors 2 \
--queue default \
开发的Python程序.py \
参数……2) 不会写python，只会sql
启动ThriftServer，使用Beeline或者DataGrip连接ThriftServer，测试SQL语句的开发
- 场景：数据源主要用于对Hive表做处理，只写SQL
- 产生：SQL语句/SQL文件
- 运行：spark-sql -f
spark-sql -f /opt/installs/a.sql
实战：
vi a.sql
代码如下：
show databases;
use db_hive;
show tables;
select * from emp;
create table test_rs03
select
d.dname,
round(avg(e.sal), 2) as avg_sal
from db_hive.emp e
join db_hive.dept d
on e.deptno = d.deptno
group by d.dname
order by avg_sal desc;
接着运行该sql:
# 运行SQL文件
/opt/installs/spark/bin/spark-sql --master local[2] -f /opt/data/spark-app.sql四、综合案例
数据如下:📎retail.json
收银系统中，每个订单会产生一条JSON数据，传递到后台的数据存储系统中
{
"discountRate":1,
"dayOrderSeq":8,
"storeDistrict":"雨花区",
"isSigned":0,
"storeProvince":"湖南省",
"origin":0,
"storeGPSLongitude":"113.01567856440359",
"discount":0,
"storeID":4064,
"productCount":4,
"operatorName":"OperatorName",
"operator":"NameStr",
"storeStatus":"open",
"storeOwnUserTel":12345678910,
"corporator":"hnzy",
"serverSaved":true,
"payType":"alipay",
"discountType":2,
"storeName":"杨光峰南食店",
"storeOwnUserName":"OwnUserNameStr",
"dateTS":1563758583000,
"smallChange":0,
"storeGPSName":"",
"erase":0,
"product":[
{
"count":1,
"name":"百事可乐可乐型汽水",
"unitID":0,
"barcode":"6940159410029",
"pricePer":3,
"retailPrice":3,
"tradePrice":0,
"categoryID":1
},
{
"count":1,
"name":"馋大嘴盐焗鸡筋110g",
"unitID":0,
"barcode":"6951027300076",
"pricePer":2.5,
"retailPrice":2.5,
"tradePrice":0,
"categoryID":1
},
{
"count":2,
"name":"糯米锅巴",
"unitID":0,
"barcode":"6970362690000",
"pricePer":2.5,
"retailPrice":2.5,
"tradePrice":0,
"categoryID":1
},
{
"count":1,
"name":"南京包装",
"unitID":0,
"barcode":"6901028300056",
"pricePer":12,
"retailPrice":12,
"tradePrice":0,
"categoryID":1
}
],
"storeGPSAddress":"",
"orderID":"156375858240940641230",
"moneyBeforeWholeDiscount":22.5,
"storeCategory":"normal",
"receivable":22.5,
"faceID":"",
"storeOwnUserId":4082,
"paymentChannel":0,
"paymentScenarios":"PASV",
"storeAddress":"StoreAddress",
"totalNoDiscount":22.5,
"payedTotal":22.5,
"storeGPSLatitude":"28.121213726311993",
"storeCreateDateTS":1557733046000,
"payStatus":-1,
"storeCity":"长沙市",
"memberID":"0"
}字段解析：
- 核心字段
- "storeProvince"：订单所在的省份信息，例如：湖南省
- "storeID"：订单所产生的店铺ID，例如：4064
- "receivable"：订单收款金额：例如：22.5
- "payType"：订单支付方式，例如：alipay
- "dateTS"：订单产生时间，例如：15637585830001-统计查询每个省份的总销售额【订单金额要小于1万】：省份、订单金额
2-统计查询销售额最高的前3个省份中，统计各省份单日销售额超过1000的各省份的店铺个数
- 只对销售额最高的前3个省份做统计：将这三个省份的数据过滤出来
- 统计每个省份每个店铺每天的销售额超过1000的店铺个数
- 省份、店铺id【去重】、销售额、天
3-统计查询销售额最高的前3个省份中，每个省份的平均订单金额
- 只对销售额最高的前3个省份做统计：将这三个省份的数据过滤出来
- 按照省份分组，求订单金额平均值
4-统计查询销售额最高的前3个省份中，每个省份的每种支付类型的占比
- 只对销售额最高的前3个省份做统计：将这三个省份的数据过滤出来
- 支付类型：微信、刷卡、支付宝、现金
- 支付类型的占比 = 类型支付个数 / 总个数
- 分组：每个省份每种类型支付的个数 / 每个省份总支付个数
- 省份、支付类型
第一步：数据清洗
字段说明：
- 核心字段
- "storeProvince"：订单所在的省份信息，例如：湖南省
- "storeID"：订单所产生的店铺ID，例如：4064
- "receivable"：订单收款金额：例如：22.5
- "payType"：订单支付方式，例如：alipay
- "dateTS"：订单产生时间，例如：1563758583000读取数据变成DataFrame，并对不合法的数据进行清洗【过滤、转换】
- 订单金额超过10000的订单不参与统计
- storeProvince不为空：None， 也不为 ‘null’值
- 只保留需要用到的字段，将字段名称转换成Python规范：a_b_c
- 并对时间戳进行转换成日期，获取天
- 对订单金额转换为decimal类型第二步：获取销售额前三的数据，放入缓存中
第三步：编写各种指标。
最后的代码：
import os
from pyspark.sql import SparkSession
"""
------------------------------------------
Description : TODO：
SourceFile : _06综合案例
Author : 老闫
Date : 2024/11/6 星期三
-------------------------------------------
"""
if __name__ == '__main__':
# 配置环境
os.environ['JAVA_HOME'] = 'C:/Program Files/Java/jdk1.8.0_241'
# 配置Hadoop的路径，就是前面解压的那个路径
os.environ['HADOOP_HOME'] = 'D:/hadoop-3.3.1'
# 配置base环境Python解析器的路径
os.environ['PYSPARK_PYTHON'] = 'C:/ProgramData/Miniconda3/python.exe' # 配置base环境Python解析器的路径
os.environ['PYSPARK_DRIVER_PYTHON'] = 'C:/ProgramData/Miniconda3/python.exe'
spark = SparkSession.builder.master("local[2]").appName("").config(
"spark.sql.shuffle.partitions", 2).getOrCreate()
df = spark.read.json("../../datas/function/retail.json")
df.createOrReplaceTempView("orders")
"""
- 订单金额超过10000的订单不参与统计
- storeProvince不为空：None， 也不为 ‘null’值
- 只保留需要用到的字段，将字段名称转换成sql规范：a_b_c
- 并对时间戳进行转换成日期，获取天
- 对订单金额转换为decimal类型
"""
"""
- 核心字段
- "storeProvince"：订单所在的省份信息，例如：湖南省
- "storeID"：订单所产生的店铺ID，例如：4064
- "receivable"：订单收款金额：例如：22.5
- "payType"：订单支付方式，例如：alipay
- "dateTS"：订单产生时间，例如：1563758583000
"""
cleared_df = spark.sql("""
select
storeProvince store_province,
storeID store_id,
cast(receivable as decimal(10,2)) receivable,
payType pay_type,
from_unixtime(dateTS/1000,"yyyy-MM-dd") date_ts
from orders where receivable <10000 and storeProvince is not null and storeProvince!= 'null'
""")
cleared_df.createOrReplaceTempView("user_orders")
# 统计查询每个省份的总销售额
df1 = spark.sql("""
select store_province,sum(receivable) total_money from user_orders group by store_province
""")
df1.createOrReplaceTempView("every_province_total_money")
df1.show()
# 统计查询销售额最高的前3个省份中，统计各省份单日销售额超过1000的各省份的店铺个数
qsDf = spark.sql("""
select * from user_orders where store_province in (
select store_province from every_province_total_money order by total_money desc limit 3
)
""")
qsDf.createOrReplaceTempView("qs_details")
# 统计查询销售额最高的前3个省份中，统计各省份单日销售额超过1000的各省份的店铺个数
spark.sql("""
with t as(
select date_ts,store_id,store_province from qs_details group by date_ts,store_id,store_province having sum(receivable) > 1000
)
select store_province,count(distinct store_id) store_num from t group by store_province
""").show()
# 每个省份的平均订单金额
spark.sql("""
select store_province,round(avg(receivable),2) avg_receivable from qs_details group by store_province
""").show()
# 每个省份的每种支付类型的占比
spark.sql("""
with t as(
select store_province,pay_type,count(1) total_order from qs_details group by store_province,pay_type
)
select store_province,pay_type,round(total_order/(sum(total_order) over(partition by store_province )),2) rate from t
""").show()
spark.stop()
DSL语法如下：
# !/usr/bin/env python
# -*- coding: utf-8 -*-
from pyspark import StorageLevel
from pyspark.sql import SparkSession
import pyspark.sql.functions as F
import os
from pyspark.sql.types import DecimalType
"""
-------------------------------------------------
Description : TODO：基于SparkSQL实现零售分析案例
SourceFile : 07.pyspark_sql_sale_case
Author : Wang
Date : 2023/1/2
-------------------------------------------------
"""
if __name__ == '__main__':
# todo:0-设置系统环境变量:全部换成Linux地址
os.environ['JAVA_HOME'] = '/opt/installs/jdk'
os.environ['HADOOP_HOME'] = '/opt/installs/hadoop'
os.environ['PYSPARK_PYTHON'] = '/opt/installs/anaconda3/bin/python3'
os.environ['PYSPARK_DRIVER_PYTHON'] = '/opt/installs/anaconda3/bin/python3'
# todo:1-构建SparkSession
spark = SparkSession \
.builder \
.master("local[2]") \
.appName("SparkSQL APP") \
.config("spark.sql.shuffle.partitions", 2) \
.getOrCreate()
# todo:2-数据处理：读取、转换、保存
# step1: 读取数据
input_df = spark.read.json(path="../datas/order/retail.json")
# input_df.show()
# step2: 处理数据
"""ETL：数据清洗"""
etl_df = ( input_df
# 行的过滤：where
.where( (F.col("receivable") < 10000) & (F.col("storeProvince").isNotNull()) & (F.col("storeProvince") != 'null') )
# 列的过滤和转换：select
.select(
F.col("storeProvince").alias("store_province"),
F.col("storeID").alias("store_id"),
F.col("payType").alias("pay_type"),
# 将订单时间转换为订单日期：yyyy-MM-dd，时间戳转成日期：from_unixtime , 日期转换成时间戳：unix_timestamp
F.from_unixtime(F.substring(F.col("dateTS"), 1, 10), 'yyyy-MM-dd').alias("daystr"),
# 类型转换
F.col("receivable").cast(DecimalType(10, 2)).alias("receivable_money")
)
)
# etl_df.show()
"""需求1：统计查询每个省份的订单金额"""
# 将ETL的数据进行持久化
etl_df.persist(StorageLevel.MEMORY_AND_DISK)
rs1 = ( etl_df
# 先按照省份分组
.groupBy(F.col("store_province"))
# 聚合
.agg(F.sum(F.col("receivable_money")).alias("total_money"))
)
# rs1.show()
"""过滤：将销售额最高的前3个省份的数据先过滤出来"""
# 获取前三省份的名称的列表
top3_province = rs1.orderBy(F.col("total_money").desc()).limit(3).rdd.map(lambda row: row.store_province).collect()
# print(top3_province)
# 实现过滤
top3_province_data = ( etl_df
# 过滤出前三个省份的数据
.where(F.col("store_province").isin(top3_province))
)
# top3_province_data.show()
# 释放缓存
etl_df.unpersist()
"""需求2：对销售额最高的前3个省份的数据进行统计，统计单日销售额超过1000的各省份的店铺个数"""
# 先将DF注册为临时的视图
top3_province_data.createOrReplaceTempView("tmp_top3_order")
# 如果一个视图被使用多次，可以进行缓存
spark.catalog.cacheTable("tmp_top3_order")
# 使用SQL进行处理
rs2 = spark.sql("""
with tmp as (
select
store_province, store_id, daystr,
sum(receivable_money) as day_money
from tmp_top3_order
group by store_province, store_id, daystr
having day_money > 1000
)
select
store_province,
count(distinct store_id) as store_cnt
from tmp
group by store_province
""")
# rs2.show()
"""需求3：统计销售额最高的前3个省份的每个省份的平均订单金额"""
rs3 = spark.sql("""
select
store_province,
round(avg(receivable_money), 2) as avg_money
from tmp_top3_order
group by store_province
""")
# rs3.show()
"""需求4：统计销售额最高的前3个省份的数据，查询每个省份的每种支付类型的订单个数占比"""
rs4 = spark.sql("""
with t1 as (
select
store_province,
pay_type,
count(1) as pay_type_cnt
from tmp_top3_order
group by store_province, pay_type
), t2 as (
select
*,
sum(pay_type_cnt) over (partition by store_province) as total_cnt
from t1
)
select
store_province,
pay_type,
round((pay_type_cnt / total_cnt), 2) as rate
from t2
""")
rs4.show()
# 视图不再被使用，需要释放
spark.catalog.uncacheTable("tmp_top3_order")
# step3: 保存结果
# todo:3-关闭SparkSession
spark.stop()
扫一扫
1105
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
