文本文件清洗入库（MySQL）一例

用户1148526

发布于 2022-05-07 13:31:42

9130

文章被收录于专栏：Hadoop数据仓库Hadoop数据仓库

一、需求

有一批文本文件如下： numt_qe_0411.txt numt_qe_0412.txt numt_qe_0413.txt numt_qe_0414.txt ...

每个文件的内容格式如下： 9435446,问题1：,答案1 9575177,问题2? ,答案2 9575177,问题3? ,答案3 ...

可能存在以下几种情况： 1. 文件中有空行 2. 行中可能存在Tab符 3. 可能存在错误折行的情况，如： 9594287,您的性别：,男 9594287,您的年龄： ,70 ～80岁 9594287,您的学历：,大专

应为： 9594287,您的性别：,男 9594287,您的年龄： ,70～80岁 9594287,您的学历：,大专要求将所有文件内容导入如下结构的 t1 表中：

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| sdate    | date         | YES  |     | NULL    |                |
| userid   | bigint(20)   | YES  |     | NULL    |                |
| qu_title | varchar(500) | YES  |     | NULL    |                |
| qu_item  | varchar(255) | YES  |     | NULL    |                |
| dstate   | int(255)     | YES  |     | 0       |                |
+----------+--------------+------+-----+---------+----------------+

第一个逗号之前为一列，对应到字段userid，最后一个逗号后面为第三个字段，对应到字段qu_item；中间内容对应到字段qu_title；文件名中的日期，导入的时候写到字段 sdate。并且由于资源限制，一次性导入整个一个文件会使得服务器卡死。

二、方案

先处理文本文件，去掉空行和Tab符号，然后将一个大文件分割成若干小文件。将每个小文件的数据导入一个中间过渡表，之后再将用SQL查询中的字符串和窗口函数处理后的结果插入目标表。

三、实现

1. 创建过渡表

create table t_question (`txt` text) engine=innodb default charset=utf8mb4;

2. 文本文件处理

（1）改成unix格式

cd /data/software/xxqg/
dos2unix *.txt

（2）去掉空行

sed -i '/^$/d' *.txt

（3）Tab符替换为空格

sed -i 's/\t/ /g' *.txt

（4）分成100000行一个的小文件

mkdir small_file
cd small_file
split -l 100000 -d -a 5 ../numt_qe_0411.txt 04_11_
split -l 100000 -d -a 5 ../numt_qe_0412.txt 04_12_
split -l 100000 -d -a 5 ../numt_qe_0413.txt 04_13_
split -l 100000 -d -a 5 ../numt_qe_0414.txt 04_14_
...

（5）生成SQL命令文件

ls -l | awk '{print "truncate table t_question; load data infile '\''\/data\/software\/xxqg\/small_file\/"$9 "'\'' into table t_question ;"}' | grep 04 > sql_cmd.sql

3. 入库执行内容如下的load_to_table.sh脚本文件

#!/bin/bash
source ~/.bashrc

cat /data/software/xxqg/small_file/sql_cmd.sql|while read line
do
    read mon day <<< `echo $line | awk -F/ '{print $6}' | awk -F_ '{print $1,$2}'`
    sdate='2022-'$mon'-'$day

    # userid >= 1000000 为正常数据
    # userid < 1000000 为错误折行数据，用lag函数合并到上一行
    # instr(reverse(txt),',') 取得最后一个逗号的位置
    # sleep 1 为缓解负载压力
    
    mysql -u root -p123456 -S /data/18251/mysqldata/mysql.sock -Dtest -e "${line}" 
    mysql -u root -p123456 -S /data/18251/mysqldata/mysql.sock -Dtest -e "
    insert into t1 (sdate,userid,qu_title,qu_item)
    select sdate,userid,qu_title,qu_item 
      from (select '$sdate' sdate,substring_index(txt,',',1) userid,substr(txt,instr(txt,',')+1,char_length(txt) - instr(reverse(txt),',')-instr(txt,',')) qu_title,substring_index(txt,',',-1) qu_item from t_question) t  where userid >= 1000000;
    insert into t1 (sdate,userid,qu_title,qu_item)
    select '$sdate' sdate,substring_index(txt,',',1) userid,substr(txt,instr(txt,',')+1,char_length(txt) - instr(reverse(txt),',')-instr(txt,',')) qu_title,substring_index(txt,',',-1) qu_item 
      from (select concat(lag_txt,userid) txt from (select txt,substring_index(txt,',',1) userid,lag(txt,1) over () lag_txt from t_question) t where userid < 1000000) t;
    commit;
    " 
    sleep 1;
done

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-05-03，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

sql

登录后参与评论

0 条评论

热度