spark-sql读写数据源

spark读写数据源的相关操作

一、读FileSystem

1. 使用说明

1 基本用法

select col_01, col_02 from file_format.`save_path` where dt = '20220413';

注意：

1) from后的表名用“文件格式.`存储路径`”代替;

2) 可以使用分区字段, 但路径要指定到分区字段的上一层;

2 高级用法

create temporary view view_name using json options ('path'='test2.txt', 'multiline'='true');

select name, age from view_name where dt = '20220413';

注意：

1) 用options设定参数, 实现个性化读取;

2) 可以使用分区字段, 但路径要指定到分区字段的上一层;

3）获取记录所在文件路径：input_file_name()

-- 获取某些记录所在文件
select
    input_file_name() as file_name
    ,count(1) as cnt
from 
    table_name
where
    dt = '20220720' 
group by
    file_name
order by
    cnt
;

3 参考参数

1）文件异常处理

set spark.sql.files.ignoreCorruptFiles=true;   -- 忽略损坏的文件
set spark.sql.files.ignoreMissingFiles=true;  -- 忽略丢失的文件

2）参考链接

用途	链接
文件源参数	Generic File Source Options - Spark 3.1.2 Documentation
读文件优化	Performance Tuning - Spark 3.1.2 Documentation
数据源-参考options参数	Data Sources - Spark 3.2.1 Documentation

2. 使用样例

1 读取json文件

-- 方式01，自动推断数据类型
select id,age from json.`save_path`

-- 方式02，都是字符串类型
select
    name,
    age
from
    text.`save_path`
    lateral view json_tuple(value, 'name',  'age') tmp as name, age
;

注意：

1） json文件自带结构信息，select后可以直接通过key获取数据；

2）以上两种解析方式，执行效率差不多；

2 读取parquet文件

select id,age from parquet.`save_path`

注意：parquet文件自带结构信息，select后可以直接读取parquet文件中所包含的列；

3 读取orc文件

select id,age from orc.`save_path`

注意：orc文件自带结构信息，select后可以直接读取orc文件中所包含的列；

4 读取text文件

select value from text.`save_path`

注意：text文件没有自带结构信息，读取后只有一个value列；

5. 读取csv文件

CSV Files - Spark 3.3.1 Documentation

方式01
-- 设定路径
set path=oss://path/20230607.csv;

-- 设定读取参数
create temporary view view_name using csv options ('path'='${path}', 'delimiter'=',', 'header'='true','encoding'='gbk');

-- 写出到parquet格式表
insert overwrite table table_name
select * from view_name;


-- 方式02
-- 设定路径
set path=oss://path/20230607.csv;
 
-- 设定读取参数
create temporary view view_name using csv options ('path'='${path}', 'delimiter'=',', 'header'='true','encoding'='utf-8');
 
-- 写出到parquet格式表
create table table_name using parquet as
select * from view_name;

常见问题：

中文乱码，确认文件的字符集与hive表（一般是utf-8）是否一致；linux上查看文件字符集 file ${file_name}

分割异常，字段值中有逗号，要么更改csv的分割符，要么使用能正常处理的引擎（spark）处理成parquet文件；

处理方式：

文件格式转换 --> 导入目标hive表

本地使用spark转成parquet文件，再上传到hive表；

csv文件先上传，再使用spark读取后写入hive表；

3. 读取错误

1. select from parquet.`save_path`

1）  读取的路径存在, 但没有数据

Error in query: Unable to infer schema for Parquet. It must be specified manually.

Error in query: Unable to infer schema for Orc. It must be specified manually.

2）  读取的路径不存在

Error in query: Path does not exist: s3://save_path

3） 增加字段后，报字段不存在错误

Error in query: cannot resolve '`name`' given input columns: [id, age]; 

-- 报字段不存在（读基础路径，会根据这个目录下的所有数据获取schema，历史数据中无新增字段，可以设定spark.sql.parquet.mergeSchema=true来解决）
select 
    id
    ,age
    ,name
from 
  parquet.`save_base_path`
where 
   dt='20220614'
;

-- 可以正常读取（新目录是最新的schema，包含新增字段，可以正常读取）
select 
    id
    ,age
    ,name
from 
  parquet.`save_base_path/dt=20220614`
;

2. 递归读取分区目录下的子目录

-- 目录结构
oss://path/20230216/00/
oss://path/20230216/01/

-- 添加分区
alter table table_name add if not exists partition (dt='20230216') location 'oss://path/20230216/';

-- 读取数据
set mapreduce.input.fileinputformat.input.dir.recursive=true;
select
    *
from
    table_name
where
    dt = '20230216'
limit 10
;

3. 文件损坏

报错现象：

FileScanRDD: Skipped the rest of the content in the corrupted file: path: ks3://aa/test.gz, range: 0-23046, partition values: [empty row]
java.io.IOException: invalid block type
        at org.apache.hadoop.io.compress.zlib.ZlibDecompressor.inflateBytesDirect(Native Method)

任务卡住不继续运行也不退出，占用资源却没有任何任务运行

解决方式：

set spark.sql.files.ignoreCorruptFiles=true;

-

二、写FileSystem

options能使用的参数和读FileSystem一样

insert overwrite directory 'oss//base_path/' using json options('compression'='gzip')
select
    name
    ,age
from
    tbl_name
;

三、Mysql

1. Spark-SQL中直接操作Mysql

-- 临时视图（推荐）
CREATE TEMPORARY VIEW mysql_table
USING org.apache.spark.sql.jdbc
OPTIONS (
    url "jdbc:mysql://mysql_host:3306/database",
    driver "com.mysql.cj.jdbc.Driver",
    dbtable "table_name",
    user "root",
    password "123456
);
SELECT
    * 
FROM
    mysql_table2
LIMIT 2
;


-- 临时表（已弃用）
CREATE TEMPORARY TABLE mysql_table2
USING org.apache.spark.sql.jdbc
OPTIONS (
    url "jdbc:mysql://mysql_host:3306/database",
    driver "com.mysql.cj.jdbc.Driver",
    dbtable "table_name",
    user "root",
    password "123456
);
SELECT
    * 
FROM
    mysql_table2 
LIMIT 2
;

-

一百、相关参考

Spark 内置数据源 options

DataFrameReader (Spark 2.3.0 JavaDoc)

JDBC To Other Databases - Spark 3.2.1 Documentation

---