SparkSQL电商案例

一、自定义函数

1-1 Pandas

Pandas是python的一个数据分析包（numpy，matlab），最初由AQR Capital Management于2008年4月开发，并于2009年底开源出来，目前由专注于Python数据包开发的PyData开发team继续开发和维护，属于PyData项目的一部分。

Pandas最初被作为金融数据分析工具而开发出来，因此，pandas为时间序列分析提供了很好的支持。

Pandas的主要数据结构是 Series （一维数据）与 DataFrame（二维数据），这两种数据结构足以处理金融、统计、社会科学、工程等领域里的大多数典型用例。

通过索引取值 行索引 列索引

Series 代表一列数据 只有行索引 取出某行数据

DataFrame 有行和列 同时有行索引 列索引 ，通过列索引取出的值就转为series类型

spark的dataframe row对象 row[0] 和 schema信息（列的名字）

spark dataframe 是由一行一行的row对象构成 分布式计算

pandas dataframe 是由一列一列的series构成 单机计算

Anconda中已经安装好pandas

• Series

是一种类似于一维数组的对象，它由一组数据以及一组与之相关的数据标签（即索引）组成。

import pandas as pd

# series类型的定义
# 代表一列数据
# 定义时，没有指定索引会自动生成
s1 = pd.Series(['a','b','c','d'])
s2 = pd.Series([10,11,12,13])

print(s1)
print(s2)

# 取值
# print(s1[0])

# 指定索引index
s3 = pd.Series(['张三','李四'],index=['a','b'])
print(s3)
print(s3['a'])

# 计算方法
data = s2.sum()
print(data)
print(f'平均数:{s2.mean()}')
print(f'最大值:{s2.max()}')
print(f'最小值:{s2.min()}')
print(f'个数:{s2.count()}')

• DataFrame

是一个表格型的数据结构，它含有一组有序的列，每列可以是不同的值类型（数值、字符串、布尔型值）,可以看作是一种二维表格型数据的结构，既有行索引也有列索引，行索引是 index，列索引是 columns。它可以被看做由 Series 组成的字典（共同用一个索引）。

import pandas as pd

# 定义
df = pd.DataFrame([
    [1, '张三', 20, '男'],
    [2, '李四', 25, '男'],
    [3, '王五', 22, '男'],
    [4, '赵六', 27, '男']
])

print(df)

# df取值
# 先取列，在取行
print('*'*100)
print(df[1])  # 取出一列数据，是series类型
print('*'*100)
print(df[1][1])

# 指定行index和列的索引columns
df2 = pd.DataFrame([
    [1, '张三', 20, '男'],
    [2, '李四', 25, '男'],
    [3, '小红', 22, '女'],
    [4, '赵六', 27, '男']
],index=[1,2,3,4],columns=['id','name','age','gender'])
print('*'*100)
print(df2)

# 取值
print('*'*100)
print(df2['name'][2])
print('*'*100)
# 数据过滤
print(df2[df2['id'] > 1])
# 多条件  与 &  或 |  非  ！
print('*'*100)
print(df2[  (df2['id'] > 1) & (df2['age'] == 25 )  ])
print('*'*100)
data = df2[df2['id'] > 1].groupby('gender').sum()
print(data)

• 文件的读写

import pandas as pd
import json # Python处理json数据的模块


# 通过Python的方式读文件转为df
with open('/export/server/spark/examples/src/main/resources/people.csv','r') as f:
    data_list = []
    for i in f.readlines():
        i=i.replace('\n','')
        data_list.append(i.split(';'))


df_list = []
for i in range(1,len(data_list)):
    df_list.append(data_list[i])


df = pd.DataFrame(df_list,columns=data_list[0])
print(df)



# 使用pandas方法读取数据
print("*"*100)
df2 = pd.read_csv('/export/server/spark/examples/src/main/resources/people.csv',sep=';')
print(df2)

# 写入  data是文件
df2.to_csv('/root/data.csv')

• pandas的dataframe和spark的dataframe的转化
  • pandas的数据在计算使用的是单机资源进行计算，想要进行分布式计算，利用多台计算机资源，此时就可以将pandas的dataframe转化为spark的dataframe

from pyspark.sql import SparkSession
import pandas as pd

# 定义pandas的df
pd_df = pd.DataFrame({
    'id':[1,2,3,4],
    'name':['张三','李四','王五','赵六']
})

print(pd_df)

# pandas 转为spark
ss =SparkSession.builder.getOrCreate()

spark_df = ss.createDataFrame(pd_df)
spark_df.show()

# spark 转为pandass
pd_df = spark_df.toPandas()
print(pd_df)

1-2 UDAF函数

多进一出 主要是聚合

使用pandas中的series实现，可以读取一列数据存储在pandas的seriess中进行数据的聚合

# 导入window类 定义窗口
from pyspark.sql import SparkSession, Window, functions as F
from pyspark.sql.types import *
import pandas as pd

# 1、生成SparkSession对象
ss = SparkSession.builder.config('spark.sql.execution.arrow.pyspark.enabled','true').getOrCreate()
# 2、获取sparkcontext对象
sc = ss.sparkContext

# 3、 读取文件数据转为rdd
rdd = sc.textFile('/student')
# 4、查看rdd数据
# 5、对每行数据进行切割
rdd_map = rdd.map(
    lambda x: [int(x.split(',')[0]), x.split(',')[1], x.split(',')[2], int(x.split(',')[3]), x.split(',')[4]])

# 6、rdd转df
# 7、定义 表信息
schema_type = StructType(). \
    add('id', IntegerType()). \
    add('name', StringType()). \
    add('gender', StringType()). \
    add('age', IntegerType()). \
    add('cls', StringType())

df = rdd_map.toDF(schema_type)

df.show()


# 自定义函数
# 使用panda实现udaf
# 需要按照固定格式
# pandas自定义函数的装饰器
@F.pandas_udf(returnType=FloatType())  # returnType=FloatType 指定返回类型
def func(data: pd.Series) -> float: #  -> 指定返回的结果类型
    # data 是接受参数,接受一列数据， 冒号后是指定接受的的类型是seriess类型
    # 按照pandas的series类型操作data
    return  data.mean()

# 注册到spark中使用
# 第一个参数  指定注册的函数名
# 第二个参数  指定自己定义的函数名
# 注册完成后会返回注册的函数
avg_it = ss.udf.register('avg_it', func)

# DSL方法中使用
df_new = df.select(avg_it(df['age']))
df_new.show()

# SQL中使用
df.createTempView('stu')
df_new2 = ss.sql('select avg_it(age) from stu ')
df_new2.show()

• Py4J模块（了解）
  • Python的代码最终是转化为java执行spark的计算
  • 通过Py4J，Python程序能够动态访问 Java虚拟机 中的 Java对象，Java程序 也能够回调 Python对象。
  • 在Driver端，通过Py4j实现在Python中调用Java的方法，即将用户写的PySpark程序”映射”到JVM
  • 在Executor端，直接为每个Task单独启一个Python进程，通过socket通信方式将Python函数或Lambda表达式发给Python进程执行。

• arrow框架
  • Apache Arrow 是一种内存中的列式数据格式，用于Spark中，以在JVM和Python进程之间有效地传输数据。目前这对使用 Pandas/NumPy 数据的 Python 用户最有益，提升传输速度。
  • pip install pyspark[sql] 下载
  • pip install pyspark[sql] -i https://pypi.mirrors.ustc.edu.cn/simple/

spark = SparkSession.builder.config('spark.sql.execution.arrow.pyspark.enabled','true').getOrCreate()

二、SpakrSQL & hive（配置）

python代码方式操作的是Dataframe数据（row和schema）

• 表的信息
• 表的数据

SQL工具操作 row数据保存在hdfs上 schema 通过metastor保存在mysql上

• 映射表
  • 表信息 （metastore管理的元数据）
  • 表数据 （hdfs的文件存储）

使用纯sql进行数据的计算

2-1 SparkSQL配置启动

• 启动hive的metastore服务，才能让sparksql连接
  • 管理表的元数据
• 验证方式：一代客户端hive连接一下，能够进行hivesql数据查询操作说明启动成功了

nohup hive --service metastore &

• 在spark中需要配置hive的连接
  • spark要连接metastore，如何告知spark的metastore的连接？需要hive下的连接配置文件
  • 将hive的hive-site.xml配置文件拷贝到spark的conf目录下

cp /export/server/hive/conf/hive-site.xml /export/server/spark/conf/

2-2 使用

• spark-sql终端使用
  • spark-sql --master yarn --name sparksql
  • 可以在交互页面使用sql和hive的sql语句一样
• 代码中使用

from pyspark.sql import SparkSession

# spark.sql.warehouse.dir 指定数仓路径
# hive.metastore.uris  指定metasstore的连接信息
# 指定开启hive操作
ss = SparkSession.builder. \
    config('spark.sql.warehouse.dir', 'hdfs://node1:8020/user/hive/warehouse'). \
    config('hive.metastore.uris', 'thrift://node1:9083'). \
    enableHiveSupport(). \
    getOrCreate()

# 写sql
df_new = ss.sql('show databases')
df_new.show()

ss.sql('create database itcst')

• pycharm客户端连接使用
  • 需要启动spark的二代服务thriftserver2
    • 在hiveserver2基础上进行封装，提供了一个sparksumit服务，可以将sql转化的rdd算子提交给spark进行计算
  • 验证是否启动成功 使用beeline连接验证

cd /export/server/spark/sbin

./start-thriftserver.sh --hiveconf hive.server2.thrift.port=10000 --hiveconf hive.server2.thrift.bind.host=node1  --conf spark.sql.warehouse.dir=hdfs://node1:8020/user/hive/warehouse

• 自动下载驱动

• 手动添加驱动

2-3 spark的thirftserver的补充

• 指定计算采用yarn进行资源调度计算

./start-thriftserver.sh --hiveconf hive.server2.thrift.port=10000 --hiveconf hive.server2.thrift.bind.host=node1  --conf spark.sql.warehouse.dir=hdfs://node1:8020/user/hive/warehouse  --master  yarn

• hiveSQL和sparkSQL同时使用
  • hive的hiveserver2 端口10000
  • spark的thriftserver2 端口也是10000
  • 两个都启动会端口冲突
  • 可以修改spark的运行端口号 hive.server2.thrift.port=10001

三、SparkSQL的运行流程

catalyst引擎,将sparkSQL代码转化成rdd交给spark执行

catalyst中的类功能

• 解析器 解析sql语句 形成语法树
• 分析器 分析sql与对应的rdd方法
• 优化器 优化rdd的方法
• 执行器 将rdd方法交给spark执行

四、电商案例

对订单表的数据进行计算

数据源是一个csv的文件

使用sparkSQL搭建数仓

• ods层

create database mall_ods;

create table mall_ods.ECommerce_ods
(
    InvoiceNo   string comment '订单编号（退货订单以C开头）   536365',
    StockCode   string comment '产品代码                   85123A',
    Description string comment "产品描述                   WHITE METALLANTERN",
    Quantity    int comment "购买数量（负数表示退货）      6",
    InvoiceDate string comment "订单日期和时间              12/1/2010 8∶26",
    UnitPrice   double comment "单价（英镑）                3.39",
    CustomerID  int comment "客户编号                   17850",
    Country     string comment "国家名称                   United Kingdom"
)row format delimited 
fields terminated by ',';

• dw层 将处理后的ods数据导入dw层

-- 创建dw层
create database mall_dw;

create table mall_dw.ECommerce_dw
(
    InvoiceNo   string comment '订单编号（退货订单以C开头）   536365',
    StockCode   string comment '产品代码                   85123A',
    Description string comment "产品描述                   WHITE METALLANTERN",
    Quantity    int comment "购买数量（负数表示退货）      6",
    InvoiceDate string comment "订单日期和时间              12/1/2010 8∶26",
    UnitPrice   double comment "单价（英镑）                3.39",
    CustomerID  int comment "客户编号                   17850",
    Country     string comment "国家名称                   United Kingdom"
)partitioned by(dt string)
row format delimited
fields terminated by ',';

-- ods层数据清洗，过滤空数据  Description 产品描述字段是空字符过滤  与 CustomerID客户编号为0的过滤掉

-- 手动指定分区字段数据  静态插入
insert into mall_dw.ecommerce_dw partition(dt='2022-09-12')
select * from mall_ods.ecommerce_ods where Description !=' ' and CustomerID != 0;

-- 根字段数据自动进行分区数据生成
set hive.exec.dynamic.partition.mode=nonstrict;
insert into mall_dw.ecommerce_dw partition(dt)
select *,'2022-09-23' from mall_ods.ecommerce_ods where Description !=' ' and CustomerID != 0;
insert into mall_dw.ecommerce_dw partition(dt)
select *,'2022-09-22' from mall_ods.ecommerce_ods where Description !=' ' or CustomerID != 0;

• app 层 对dw层的数据按照需求进行计算

1、销量最高的10个国家

2、各个国家的总销售额分布情况

3、销售最高的10个商品

4、商品描述的热门关键词TOP300

5、退货订单数最多的10个国家

6、商品的平均单价与销量的关系

-- 创建app层
create database mall_app;

-- 从dw层读取表数据计算
-- 1、销量最高的10个国家
create table mall_app.top10_sale_sum(
    Country string,
    sum_data int
)row format delimited
fields terminated by ',';

insert into mall_app.top10_sale_sum
select Country, sum(Quantity) as sum_data
from mall_dw.ecommerce_dw
where dt = '2022-09-22'
group by Country order by sum_data desc limit 10;


-- 2、各个国家的总销售额分布情况
select Country,round(sum(Quantity*UnitPrice),2) as price_data from mall_dw.ecommerce_dw group by Country;


-- 3、销售量最高的10个商品


-- 4、商品描述的热门关键词TOP300
set spark.sql.shuffle.partitions=4;
with tb as(
select  explode(split(Description,' ')) as word from mall_dw.ecommerce_dw)
select word,count(word) from tb group by word;


-- 5、退货订单数最多的10个国家
select Country, count(InvoiceNo) as sum_data
from mall_dw.ecommerce_dw
where dt = '2022-09-22' and InvoiceNo like 'C%'
group by Country order by sum_data desc limit 10;

-- 6、商品的平均单价与销量的关系

select StockCode, avg(UnitPrice) ,sum(Quantity)
from mall_dw.ecommerce_dw
where dt = '2022-09-22' 
group by StockCode order by sum_data desc limit 10;