Springboot数据服务开发：用户上传文件导入到数据库中
需求：用户上传一个Excel文件，自动将该文件数据导入到数据库的对应表中（如果表没有则创建，目前只实现了导入到Clickhouse）
思考如何更快速的导入数据
首先对需求进行分析，按照传统模式，可以采用POI+JDBC的方式来进行导入数据。但是这种方式比较繁琐，同时插入效率在数据量很大时，相对来说还是不够高。还有一种更方便快速的方式实现该功能，就是利用Clickhouse自有的插入数据功能，类似于Mysql的load data语法实现的快速导入大批量数据的功能。
在Clickhouse的官网中给出：
数据可以以ClickHouse支持的任何 输入输出格式 传递给INSERT。格式的名称必须显示的指定在查询中：
INSERT INTO [db.]table [(c1, c2, c3)] FORMAT format_name data_set
同时，官网中的input(structure)表函数中给出:
input(structure) -表函数，可以有效地将发送给服务器的数据转换为具有给定结构的数据并将其插入到具有其他结构的表中。
如果 data.csv 包含与表 test 相同结构 test_structure 的数据，那么这两个查询是相等的:
$ cat data.csv | clickhouse-client --query="INSERT INTO test FORMAT CSV"
$ cat data.csv | clickhouse-client --query="INSERT INTO test SELECT * FROM input('test_structure') FORMAT CSV"
因此我们可以用cat的方式读取表，再将插入的SQL添加到query后面即可实现。后面会给出我的实现。
上传文件的格式转换
这里要注意的是，Clickhouse所支持的数据类型包括了：输入/输出格式，可以发现是不包含Excel文件格式的。因此如果还想要实现上述功能，需要先将Excel转换为CSV格式，
转换代码贴出如下：
@Component
public class ExcelToCsv {
public File convertExcelToCsv(File file)
{
StringBuilder buffer = new StringBuilder();
// 设置读文件编码
try {
FileInputStream fis = new FileInputStream(file);
XSSFWorkbook workbook = new XSSFWorkbook(new BufferedInputStream(fis));
XSSFSheet sheet = workbook.getSheetAt(0);
// 获取sheet中最后一行行号
int lastRowNum = sheet.getLastRowNum();
// 省略掉title
// 将文本内容拼接成一个字符串进行写入
for (int i=1; i <=lastRowNum; i++)
{
XSSFRow row = sheet.getRow(i);
int lastCellNum = row.getLastCellNum();
for(int j=0; j < lastCellNum; j++)
{
XSSFCell cell = row.getCell(j);
String replace = cell.getStringCellValue().replaceAll("\n", "")+",";
buffer.append(replace);
}
buffer = buffer.replace(buffer.lastIndexOf(","), buffer.lastIndexOf(",")+1, "");
buffer.append("\n");
}
} catch (IOException e) {
e.printStackTrace();
}
// write to the file
FileOutputStream fos = null;
BufferedOutputStream bos = null;
String savePath = file.getPath();
String csvPath = savePath.substring(0, savePath.lastIndexOf(".")) + ".csv";
byte[] bytes = new byte[1024];
File csvFile = new File(csvPath);
try {
fos = new FileOutputStream(csvFile);
bos = new BufferedOutputStream(fos);
bos.write(buffer.toString().getBytes());
} catch (IOException e) {
e.printStackTrace();
}
finally {
try {
bos.flush();
fos.close();
bos.close();;
} catch (IOException e) {
e.printStackTrace();
}
}
return csvFile;
}
}
用户上传Excel文件后，因为文件上传一般会限制大小，后台可以自动将文件转成CSV的格式，再利用上面的cat方式读取文件并加载到数据库中。
这里贴出功能的核心代码：
public class DBUtils {
private String dbName;
private String table;
private DataSource dataSource = null;
private Connection conn = null;
public DBUtils(String dbType, String ip, String port, String dbName, String table, String username, String password) {
this.dbName = dbName;
this.table = table;
Properties props = new Properties();
if (dbType.equalsIgnoreCase("clickhouse"))
{
try {
String url = "jdbc:clickhouse://" + ip + ":" + port + "/" + dbName;
ClickHouseProperties properties = new ClickHouseProperties();
properties.setUser(username);
properties.setPassword(password);
dataSource = new ClickHouseDataSource(url, properties);
conn = dataSource.getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
else if (dbType.equalsIgnoreCase("mysql"))
{
try {
String url = "jdbc:mysql://"+ip+":"+port+"/"+dbName+"?characterEncoding=utf-8";
Class.forName("com.mysql.jdbc.Driver");
//获取数据库的连接
conn = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
public boolean isCkTableExists()
{
String sql = "select name from system.tables where name=?";
try {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, table);
ResultSet set = ps.executeQuery();
if(set.next())
{
return true;
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return false;
}
/**
* 根据Excel的title建表，用户没有传入Title对应的类型，则所有Title的类型均为String
* @param file excel文件对象
*/
public boolean createTableBasedExcel(File file) {
// 获取到文件名称，以该文件名称作为数据表名
String fileName = file.getName().substring(0, file.getName().indexOf("."));
StringBuilder buffer = new StringBuilder("create table " + dbName + "." + table + "( ");
String sql = "";
PreparedStatement ps = null;
boolean execute = false;
FileInputStream fis = null;
try {
fis = new FileInputStream(file);
XSSFWorkbook workbook = new XSSFWorkbook(new BufferedInputStream(fis));
XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow title = sheet.getRow(0);
int columnNum = title.getLastCellNum();
for(int i = 0; i < columnNum; i++)
{
String cellValue = title.getCell(i).getStringCellValue();
String tmp = cellValue + " String, ";
buffer.append(tmp);
}
sql = buffer.substring(0, buffer.length() - 2);
sql += ") ENGINE=Log();";
} catch (IOException e) {
e.printStackTrace();
}
try {
ps = conn.prepareStatement(sql);
execute = ps.execute();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return execute;
}
/**
* 根据用户传入的每个Title对应的类型来建表
* @param file excel文件对象
*/
public boolean createTableBasedExcel(File file, String type) {
HashMap<String, String> typeMap = JSONObject.parseObject(type, HashMap.class);
// 获取到文件名称，以该文件名称作为数据表名
String fileName = file.getName().substring(0, file.getName().indexOf("."));
StringBuilder buffer = new StringBuilder("create table " + dbName + "." + table + "( ");
String sql = "";
PreparedStatement ps = null;
boolean execute = false;
FileInputStream fis = null;
try {
fis = new FileInputStream(file);
XSSFWorkbook workbook = new XSSFWorkbook(new BufferedInputStream(fis));
XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow title = sheet.getRow(0);
int columnNum = title.getLastCellNum();
for(int i = 0; i < columnNum; i++)
{
String cellValue = title.getCell(i).getStringCellValue();
String tmp = cellValue + typeMap.get(cellValue) + ", ";
buffer.append(tmp);
}
sql = buffer.substring(0, buffer.length() - 2);
sql += ") ENGINE=Log();";
} catch (IOException e) {
e.printStackTrace();
}
try {
ps = conn.prepareStatement(sql);
execute = ps.execute();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return execute;
}
}
定义了一些连接数据库的信息，以及判断数据表是否存在、创建数据表的通用方法。
核心功能模块的实现
SinkToClickhouseImpl
@Service
public class SinkToClickhouseImpl implements SinkToClickhouse {
@Autowired
private ExcelToCsv excelToCsv;
private DBUtils dbUtils;
/**
*
* @param uploadFile: 上传的文件
* @param jsonObject: 传入的JSON参数
* @return
*/
@Override
public boolean loadFileToDB(File uploadFile, Map<String, String> jsonObject) {
String originalFilename = uploadFile.getName();
// 扩展名 excel
String ext = originalFilename.substring(originalFilename.lastIndexOf(".")+1);
// 文件名
String prefix = originalFilename.substring(0, originalFilename.lastIndexOf("."));
// DefaultConfig设置了一些默认配置，如果用户没有传，则使用默认配置
String dbType = jsonObject.getOrDefault("dbType", DefaultConfig.getDbType());
String ip = jsonObject.getOrDefault("ip", DefaultConfig.getIp());
String port = jsonObject.getOrDefault("port", DefaultConfig.getPort());
String dbName = jsonObject.get("dbName");
String table = jsonObject.getOrDefault("table", prefix);
String username = jsonObject.getOrDefault("username", DefaultConfig.getUsername());
String password = jsonObject.getOrDefault("password", DefaultConfig.getPassword());
dbUtils = new DBUtils(dbType, ip, port, dbName, table, username, password);
// 判断数据库中是否存在对应的数据表
boolean flag = dbUtils.isCkTableExists();
if (!flag)
{
// 如果数据表不存在，则创建
// 如果用户没有传入schema，则自动创建数据表，所有的字段为String
// 如果用户传入shcema，则根据schema的字段类型建表
if (jsonObject.get("schema") == null)
{
dbUtils.createTableBasedExcel(uploadFile);
}
else
{
dbUtils.createTableBasedExcel(uploadFile, jsonObject.get("schema"));
}
}
// 表创建完成后，加载数据入库
// 根据文件后缀类型加载到数据库中
if (ext.equalsIgnoreCase("xls") || ext.equalsIgnoreCase("xlsx"))
{
// 如果上传的是excel文件，后台将文件转为csv 方便后续插入数据库
uploadFile = excelToCsv.convertExcelToCsv(uploadFile);
}
if (jsonObject.get("dbType").equalsIgnoreCase("clickhouse"))
{
return sinkToClickhouse(ip, port, uploadFile, dbName, table,
username, password);
}
return false;
}
// 执行shell脚本，加载数据入库
@Override
public boolean sinkToClickhouse(String ip, String port, File file, String dbName, String tableName, String username, String password) {
String date= new SimpleDateFormat("yyyy-MM-dd").format(new Date());
// 获取文件名
String name = file.getName();
// 获取文件名前缀
String fileName = name.substring(0, name.indexOf("."));
// 获取文件保存的绝对路径
String filePath = file.getPath();
// 获取文件保存路径的父路径
String parentPath = file.getParent();
// 获取文件格式
String format = name.substring(name.indexOf(".")+1);
InputStream inputStream = this.getClass().getResourceAsStream("/shell/fileToClickhouse.sh");
// 将shell脚本生成在跟文件的同一目录下
File shellFile = new File(parentPath+"/fileToClickhouse.sh");
try {
BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(shellFile));
BufferedInputStream bis = new BufferedInputStream(inputStream);
byte[] bytes = new byte[1024];
int byteRead=0;
while((byteRead=bis.read(bytes)) != -1)
{
bos.write(bytes, 0, byteRead);
bos.flush();
}
} catch (IOException e) {
e.printStackTrace();
}
try {
String cmd = "sh " + shellFile.getPath() + " "+ip + " "+port + " "+filePath+" "+format+" "+dbName+" "+tableName+" "+username+" "+password;
Process ps = Runtime.getRuntime().exec(cmd);
int exitValue = ps.waitFor();
if (exitValue != 0)
{
System.out.println("[ERROR] sink execute is failed!!!!!");
return false;
}
} catch (IOException | InterruptedException e) {
e.printStackTrace();
}
return true;
}
}
这里loadFileToDB方法的参数包含了用户上传的文件，以及需要传入的JSON参数。 方法首先获取到文件的后缀名以及前缀，后缀名用来判断文件格式，前缀用来当用户没有指定导入的数据表时，用该文件名作为数据表。
通过用户传入的数据库连接信息，生成数据库连接对象，然后调用isCkTableExists()方法判断数据表是否存在。如果不存在，则创建。创建时如果用户没有传入Ttile对应的类型，则建表时每个Title的类型均为String。
表创建完成后，加载数据入库，根据文件后缀类型加载到数据库中，这里调用的就是上面的convertExcelToCsv()方法。
sinkToClickhouse()方法就是完成数据入库的方法。这里我们需要使用到linux命令（上面的通过cat方式导入数据）。解决方式就是利用Java操作shell脚本的方式实现，将导入数据的命令写入到脚本中再通过java进行调用。java调用执行shell脚本的方法就是
Process ps = Runtime.getRuntime().exec(cmd);
int exitValue = ps.waitFor();
利用Runtime模块来执行shell脚本。exitValue用来根据返回值判断脚本是否执行成功
需要注意的点
因为这是springboot的项目，最后是需要打包成jar包部署到Linux服务器上的。那么问题就是打包成jar包后，让程序如何去找到shell脚本是一个问题。即使我们把脚本放到项目的相对路径，但是脚本执行时系统无法直接读取jar包里的文件。如果是一般自己的服务器，我们可以单独将脚本进行上传到一个位置，然后在程序里写死这个路径。但是一般线上服务器，我们都是无法直接上传文件，都是通过公司给定的界面进行操作。同时为了更灵活的选择shell脚本位置而不至于写死，我们可以采用如下方式：
虽然无法直接读取jar包里的文件，但是我们的项目可以读取resources目录下的文件流。首先将shell脚本写好后放入到项目的resources/shell的路径下，然后在项目中读取文件数据流，再将该数据流写入到系统下与上传文件同一路径的目录下。这样每一次就都可以得到shell脚本路径的绝对地址。
上面try/catch部分的文件输入输出就是为了实现这个功能。
shell脚本编写
贴一下写的shell脚本以供参考:
#!/bin/bash
typeset -u format
ip=$1
port=$2
file=$3
format=$4
dbname=$5
tablename=$6
username=$7
password=$8
####log_correct函数打印正确的输出到日志文件
function log_correct () {
DATE=`date "+%Y-%m-%d %H:%M:%S"` ####显示打印日志的时间
USER=$(whoami) ####那个用户在操作
echo "${DATE} ${USER} execute $0 [INFO] $@" ######（$0脚本本身，$@将参数作为整体传输调用）
}
function log_error ()
{
DATE=`date "+%Y-%m-%d %H:%M:%S"`
USER=$(whoami)
echo "${DATE} ${USER} execute $0 [ERROR] $@"
}
catCmd="cat ${file}"
curlCmd="curl -u ${username}:${password} http://${ip}:${port}?query=INSERT%20INTO%20${dbname}.${tablename}%20FORMAT%20${format} --data-binary @-"
$catCmd | $curlCmd
############ Check whether the statement is successfully executed #############
exitCode=$?
if [ $exitCode -ne 0 ];
then
# echo "[ERROR] sink execute is failed!!!!!"
log_error "sink execute is failed!!!!!"
exit $exitCode
else
# log_correct "${startDate}日期的数据输入导入完成}"
# echo "[INFO] the data is successfully imported into the database"
log_correct "the data is successfully imported into the database"
fi
用的方法基本就是最初给出的cat file | curl '-u username:passord http://ip:port/?query=INSERT%20FORMAT%20CSV'。只是这里多添加了一些打印的日志文件，来显示命令是否执行成功。
因为线上环境没有Clickhouse的客户端，所以需要用HTTP连接的方式连接Clickhouse，因此需要将最初官网中给的clickhouse-client替换成curl http的方式
总结
最后给出Controller部分的代码:
@RestController
@Slf4j
@RequestMapping(value = "/data-import")
public class DataImportController {
@Autowired
Environment env;
@Autowired
private SinkToClickhouse sinkToClickhouse;
/**
* 文件上传
* @return
*/
@RequestMapping(value = "/upload")
public String upload(@RequestParam("json") String json, @RequestParam("uploadFile") MultipartFile uploadFile, HttpSession session
) throws IOException {
// 处理上传文件
// 重命名 ，获取后缀
System.out.println(json);
String originalFilename = uploadFile.getOriginalFilename();// 原始名称
// 扩展名 excel
String ext = originalFilename.substring(originalFilename.lastIndexOf(".") + 1, originalFilename.length());
// String newName = UUID.randomUUID().toString() + "." + ext;
// 存储,要存储到指定的文件夹，/uploads/yyyy-MM-dd，考虑文件过多的情况按照日期，生成一个子文件夹
String realPath = session.getServletContext().getRealPath("/uploads");
System.out.println("realPath: " + realPath);
String datePath = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
File folder = new File(realPath + "/" + datePath);
if(!folder.exists()) {
folder.mkdirs();
}
// 存储文件到目录
File file = new File(folder,originalFilename);
uploadFile.transferTo(file);
// 文件加载到数据库中
HashMap<String, String> map = JSONObject.parseObject(json, HashMap.class);
System.out.println(map);
boolean result = sinkToClickhouse.loadFileToDB(file, map);
if (result)
return "sink success";
else
return "sink failed !!!";
}
}
现在基本的功能就已经实现好了，将Springboot项目打包好上传到服务器上，再利用Postman进行测试，打开数据库查看对应表，就可以发现数据表导入成功啦。
整体的需求实现不算复杂，比较需要注意的就是快速导入数据的方法，以及java如何操作执行shell脚本和如何在系统上执行shell脚本的问题。目前只实现了导入数据到Clickhouse，后续会继续完善好其他数据库的导入。同时，目前功能还比较单一，后续会继续丰富功能。
感谢大家的阅读，运行过程中如果遇到什么问题，欢迎大家积极交流，如果有需要源码的同学也可以私聊我噢。
4688
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
