DataFrame数据写出
SparkSQL 统一API写出DataFrame数据
基本语法:
df.write.mode().format().option().option().save()
# mode:传入模式字符串“append 追加、overwrite 覆盖、ignore 忽略、error 重复报异常(默认)”
# format:传入格式字符串“text、csv、parquet、json、orc、avro、jdbc等”
# text源只能支持单列df写出
# option 设置属性,例如.option("sep", ";")
# save写出路径(HDFS、GFS等)
常见数据源写出代码及示例
from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StringType, IntegerType
from pyspark.sql import functions as F
if __name__ == '__main__':
ss = SparkSession.builder \
.appName("test") \
.master("local[*]") \
.getOrCreate()
sc = ss.sparkContext
# 1. 读取数据集
schema = StructType().add("user_id", StringType(), nullable=True) \
.add("movie_id", IntegerType(), nullable=True) \
.add("rank", IntegerType(), nullable=True) \
.add("ts", StringType(), nullable=True)
df = ss.read.format("csv") \
.option("sep", "\t") \
.option("header", False) \
.option("encoding", "utf-8") \
.schema(schema=schema) \
.load("../Data/input/sql/u.data")
# Write Text写出,只能写出一个单列数据
df.select(F.concat_ws("---", "user_id", "movie_id", "rank", "ts")) \
.write.mode("overwrite") \
.format("text") \
.save("../Data/output/sql/text")
# Write CSV写出
df.write.mode("overwrite") \
.format("csv") \
.option("sep", ";") \
.option("header", True) \
.save("../Data/output/sql/csv")
# Write Json写出
df.write.mode("overwrite") \
.format("json") \
.save("../Data/output/sql/json")
# Parquet写出(默认)
df.write.mode("overwrite") \
.save("../Data/output/sql/parquet")
- text文件输出示例:
- CSV文件输出示例:
- Json文件输出示例:
- Parquet文件输出示例(在pycharm中需要介入avro viewer工具查看):
DataFrame 通过JDBC读写数据库
from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StringType, IntegerType
if __name__ == '__main__':
ss = SparkSession.builder \
.appName("test") \
.master("local[*]") \
.getOrCreate()
sc = ss.sparkContext
# 1. 读取数据集
schema = StructType().add("user_id", StringType(), nullable=True) \
.add("movie_id", IntegerType(), nullable=True) \
.add("rank", IntegerType(), nullable=True) \
.add("ts", StringType(), nullable=True)
df = ss.read.format("csv") \
.option("sep", "\t") \
.option("header", False) \
.option("encoding", "utf-8") \
.schema(schema=schema) \
.load("../Data/input/sql/u.data")
df.write.mode("overwrite") \
.format("jdbc") \
.option("url", "jdbc:mysql://node1:3306/bigdata?useSSL=false&useUnicode=true") \
.option("dbtable", "movie") \
.option("user", "root") \
.option("password", "123456") \
.save()
- 数据写入到MySQL示例: