求职帖：数据清洗

[tysonnn]

求助帖：

有一个 MySQL 表，总数据量 2-3 亿，每天新增 60w 数据。表有个字段用于记录图片文件信息（ file_id ），一年大概有 100w 条记录的 file_id 有值。现在这个表需要清洗数据，需要将近一年的图片文件数据（ 100w 条）进行更新，具体是将文件下载下来，重新上传到文件平台生成 file_id ，然后替换旧的 file_id 。 文件主要是一些截图、照片，文件大小大约 2M 左右。

想问下大佬们怎么去处理比较好？

对性能和时间没要求，能处理完就行，一天或者一个月都可以。Java 语言。

[tysonnn]

表做了分区，没有分库分表

[qping]

看了下应该有三步吧
1 、捞出近一年图片数据（约 100w 条）
2 、依次下载
3 、重新上传文件平台生成新的 file_id
4 、替换数据库中旧的 file_id

瓶颈应该是在 2 和 3 吧，IO 比较多
同时处理的线程数量估计也不能太多，可以做个试验试试最佳线程数量

[siknet]

老哥，我正在学这个，大体积的 xlsx 或者 csv 怎么快速导入 mysql ？ navicat 还是 MySQL 命令行

[qping]

漏了：
预估数据量 100 万* 2M = 2T
写个脚本处理，跑个 1 万条看下时间，看能不能接受？
这么多小文件的读写比较考验磁盘，要是有权限的话，先在文件服务器上运行脚本，拷贝文件打包，然后下载，可以节约下网络传输的损耗

[ksc010]

用异步队列的方式
1. 查询出 100W 数据记录 放入队列 A
2. 开启 worker：消耗队列 A 下载文件，上传文件，将新 fileid 和老的数据主键，放入队列 B
这里的 worker 可以根据 负载情况，确定开启多少个
3. 在开启一个 worker2 消耗 队列 B 将新的 fileid 存入数据库

[xoxo419]

条件和效果要求是怎么样呢，多长时间内需要处理完 是否是周期性的？
语言有要求吗 go or java
以及可使用分配的服务器

[noparking188]

我以前就做数据清洗掉，经常有类似需求。

我写了个工具辅助做数据清洗，楼主有需要可以参考下，https://github.com/zhiweio/porter

描述：Porter 是一个数据清洗辅助工具，能够将 MySQL 、MongoDB 和文本文件（ CSV/TSV/JSON ）等数据源数据全量扫描推入到 Redis 队列，支持断点续传、自定义等待延迟和 Batch Size 。

流程：全量遍历数据不断推进 Redis 队列，然后另外写脚本并发取数后清洗再更新，写业务逻辑就行，不需要考虑多线程编程，脚本多起几个任务就行，更推荐的是用 Lambda Function 。

以前做过的方案，仅供参考

[tysonnn]

@xoxo419 对性能和时间没要求，能处理完就行，一天或者一个月都可以。Java 语言。16 个 docker 实例，4 核 16g

[akira]

没有什么好办法 就是按照你说的流程重新跑一遍。 就算有工具 估计也不一定能直接用

[zddwj]

这个好像没什么难点吧，就写个脚本扫表，然后下载上传，程序测试好了再运行，然后控制好服务器负载，条件允许的话可以考虑省掉下载的步骤，直接在服务器端或者文件平台端运行脚本，压不压缩无所谓了，图片文件一般也压不动

[zddwj]

Java Programming Requested


admin@**.com
你会写 java 程序吗

是的，我可以编写 Java 程序。Java 是一种广泛使用的编程语言，它具有广泛的应用领域，包括 Web 应用程序、移动应用程序、游戏、企业应用程序等。作为一个人工智能语




admin@**.com
写一个 java 程序，从 mysql 表 posts 中读取 file_id 字段

好的，以下是一个 Java 程序示例，使用 JDBC 连接到 MySQL 数据库，查询 posts 表中的 file_id 字段：

java
Copy code
import java.sql.*;

public class ReadFileIdFromMysql {

public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/your_database_name";
String username = "your_username";
String password = "your_password";

try {
// Connect to MySQL database
Connection conn = DriverManager.getConnection(url, username, password);
System.out.println("Connected to MySQL database!");

// Create a SQL statement
String sql = "SELECT file_id FROM posts";
Statement stmt = conn.createStatement();

// Execute the SQL statement and get the results
ResultSet rs = stmt.executeQuery(sql);

// Loop through the results and print out the file IDs
while (rs.next()) {
int fileId = rs.getInt("file_id");
System.out.println("File ID: " + fileId);
}

// Clean up the resources
rs.close();
stmt.close();
conn.close();

} catch (SQLException e) {
System.out.println("Error connecting to MySQL database: " + e.getMessage());
}
}
}
在该示例中，我们首先定义了连接 MySQL 数据库所需的 URL 、用户名和密码。然后，我们使用 DriverManager 类建立了与数据库的连接，并创建了一个 Statement 对象来执行查询。执行查询后，我们使用 ResultSet 对象遍历结果并打印出每个 file_id 的值。最后，我们关闭了资源并处理了任何可能的异常。

请注意，您需要将 url 变量中的 your_database_name 替换为您实际使用的数据库名称，将 username 和 password 变量中的值替换为您自己的 MySQL 用户名和密码。另外，您还需要在代码中引入 JDBC 驱动程序，这可以通过在您的项目中包含相应的 jar 文件来完成。




admin@**.com
使用 getfileurl 函数 file_id 为参数获取图片链接，然后传输图片到文件平台

好的，以下是一个 Java 程序示例，使用 getfileurl()函数从 MySQL 表中获取图片链接，然后使用 Java 的 URL 和 URLConnection 类将图片上传到文件平台：

java
Copy code
import java.io.*;
import java.net.*;
import java.sql.*;

public class UploadImageToPlatform {

public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/your_database_name";
String username = "your_username";
String password = "your_password";
String platformUrl = "http://your_platform_url/upload";
String platformApiKey = "your_platform_api_key";

try {
// Connect to MySQL database
Connection conn = DriverManager.getConnection(url, username, password);
System.out.println("Connected to MySQL database!");

// Create a SQL statement to get file URL
int fileId = 1; // Replace with the actual file ID
String sql = "SELECT file_url FROM posts WHERE file_id = " + fileId;
Statement stmt = conn.createStatement();

// Execute the SQL statement and get the results
ResultSet rs = stmt.executeQuery(sql);
rs.next();
String fileUrl = rs.getString("file_url");

// Upload image to file platform
URL platformApi = new URL(platformUrl);
HttpURLConnection conn2 = (HttpURLConnection) platformApi.openConnection();
conn2.setRequestMethod("POST");
conn2.setRequestProperty("Api-Key", platformApiKey);
conn2.setDoOutput(true);

OutputStream os = conn2.getOutputStream();
os.write(fileUrl.getBytes());
os.flush();

if (conn2.getResponseCode() != HttpURLConnection.HTTP_OK) {
throw new RuntimeException("Failed : HTTP error code : " + conn2.getResponseCode());
}

BufferedReader br = new BufferedReader(new InputStreamReader((conn2.getInputStream())));

String output;
System.out.println("Output from Server .... \n");
while ((output = br.readLine()) != null) {
System.out.println(output);
}

// Clean up the resources
rs.close();
stmt.close();
conn.close();

} catch (SQLException e) {
System.out.println("Error connecting to MySQL database: " + e.getMessage());
} catch (MalformedURLException e) {
System.out.println("Invalid platform URL: " + e.getMessage());
} catch (IOException e) {
System.out.println("Error uploading image to platform: " + e.getMessage());
}
}
}
在该示例中，我们首先定义了连接 MySQL 数据库所需的 URL 、用户名和密码，以及文件平台的 URL 和 API 密钥。然后，我们使用 getfileurl()函数从 MySQL 表中获取图片链接，将其写入到 OutputStream 对象中，并使用 HttpURLConnection 类将其上传到文件平台。如果上传成功，则我们打印出从文件平台返回的响应。最后，我们关闭了资源并处理了任何可能的异常。

请注意，您需要将 fileId 变量中的值替换为您实际使用的文件 ID 。此外，您还需要将 platformUrl 和 platformApiKey 变量中的值替换为您自己的文件平台的 URL 和 API 密钥。

[acctv2]

@siknet Python

[DinnyXu]

我感觉大多数人都没理解到 OP 的意思，查个数据，上传并更新 OP 不会写这些代码？

[DinnyXu]

@DinnyXu 主要的瓶颈是这个单表有 2-3 亿的数据，查询更新个 100w 数据不是轻轻松松吗，而是如何在 2-3 亿的表中去更这 100w 条数据。

这里 op 还没有把表结构发出来，我们不知道这个表是否有对应的索引，如果没有索引，在单表 2-3 亿的数据量下去查询，效率极低，每次只能分页查询一定的数据量。

个人建议：op 第一步先针对改表的索引进行查询，使查询语句尽量走在索引上，然后分页查询一定的数据，并开启轮询进行更新，上传和更新不一定是一个步骤，可以分为多个步骤。

通过分页查询出这些数据，集体上传，并获取 url ，上传完毕后，再分页进行更新。

[QuinceyWu]

https://github.com/alibaba/DataX

[xinxingi]

@siknet spark 搞一搞，相当的快 三行代码搞定

[xinxingi]

@DinnyXu 把近一年的数据直接捞 spark 里就行了。可以 jdbc 去读，但肯定最好用语句直接到出成文件快，spark 并行读取该文件就可以，无视 mysql 索引。具体怎么替换图片，直接在 map 算子里搞定

[xinxingi]

@zddwj 这玩意这么吊呢？给出的代码还关联上你问题的上下文了

[zddwj]

@xinxingi 还行，写的东西能当模板用，不过得一步一步引导，直接发一大段需求它就直接报错了

[DinnyXu]

@xinxingi 你也要看看 op 会不会 spark ，搞这么个数据替换又重新学个新技能，而且这个新技能还不熟练