CDR信令CSV导入MySQL：7z解压、建表与LOAD DATA避坑指南

简介本资源围绕通话掉线率统计场景提供基站维度数据分析的原始数据集与入库脚本适合Hive/MySQL方向的数据分析初学者及需要处理话单数据的运维人员。压缩包内共2个文件包含1个SQL建表与导入脚本和1个CSV原始数据文件整体约13.03MBCSV对应表结构中的record_time、imei、cell、drop_num、duration字段SQL便于直接将数据导入MySQL进行进一步统计可据此还原出掉线率最高的前10基站分析过程。已有242人学习下载。读者可获得一份可直接导入使用的MySQL版本话单数据以及配套的建表语句和字段说明便于快速开展掉话率计算、基站排名等实践练习也可作为学习Hive与MySQL数据入库、查询分析的入门数据素材。1. cdr_summ_imei_cell_info 是什么一个 7z 压缩包里的信令数据底座拿到cdr_summ_imei_cell_info(csv-mysql).7z这类文件名时别急着解压先把这个名字拆开读——它基本把整个数据交付方案写脸上了cdr_summ是 CDRCall Detail Record通话/呼叫详情记录的汇总结果imei是设备标识cell_info是基站小区信息csv是交付格式mysql是目标存储7z是压缩打包方式。这套东西在手机信令分析、区域人流洞察、通信网络质量评估项目里非常常见是一张已经清洗好的“设备-小区-时间”三维事实表。本文会按“解压 → 预检 → 建表导入 → 字段处理 → 踩坑 → 聚合查询”的顺序把这条路完整走一遍。适合手里刚拿到这种 CSV 数据包、正琢磨怎么落库的工程师和数据分析师。2. 先把 7z 安全解开Linux 与 Windows 下的解压命令和预检清单2.1 Linux 下解压 7z 的最小命令7z 不是 Linux 默认自带的格式很多最小化安装的服务器上连7z命令都不存在。所以第一步是装工具。Debian/Ubuntu 系用p7zip-fullCentOS/RHEL 系用p7zip这个包提供7z、7za、7zr三个二进制日常用7z就够了。# Debian/Ubuntu sudo apt update sudo apt install -y p7zip-full # CentOS/RHEL sudo yum install -y p7zip装完之后不要直接7z x解压先看一下压缩包里到底有什么。7z l只列清单不落盘能让你确认里面是单个 CSV 还是多个 CSV路径结构长什么样还能顺带看每个文件的压缩前大小。这一步对后面判断“要不要拆文件导、单表多大、磁盘够不够”非常关键。# 查看压缩包内容清单不占用磁盘 7z l cdr_summ_imei_cell_info(csv-mysql).7z # 解压到指定目录-o 后面直接跟路径不要留空格 7z x cdr_summ_imei_cell_info(csv-mysql).7z -o./cdr_data有几个细节值得强调。7z x会保留压缩包内的目录结构7z e会把所有文件平铺到当前目录——如果压缩包里按日期分了好几个子目录建议用x保留结构否则同名文件会互相覆盖。再有就是解压路径如果不存在7z会自动创建不需要先mkdir。另外如果这个包加密了解压时会提示输入密码交互式输入就行但把密码直接写在命令行参数里-p密码会被 shell 历史记录和进程列表泄露多人在同一台服务器上时尤其注意我一般宁可交互输入。2.2 Windows 下解压与编码检查Windows 端最省事的做法是装 7-Zip右键压缩包选“提取到当前位置”或“提取到指定文件夹”。但如果你的数据流水线要写成批处理脚本命令行方式更可靠。7-Zip 安装目录下的7z.exe可以在 cmd 里直接用注意路径带空格要加引号。C:\Program Files\7-Zip\7z.exe x D:\data\cdr_summ_imei_cell_info(csv-mysql).7z -oD:\data\cdr_csv -y-y是遇到同名文件直接覆盖批量跑脚本时能省去卡在交互提示的问题。Windows 端比 Linux 多一个容易被忽略的检查CSV 的编码。用记事本或 Notepad 打开 CSV如果能看到中文看右下角编码显示常见的是 UTF-8、ANSIGBK、UTF-8 BOM 三种。后面的 MySQL 导入环节编码必须和建表语句里的CHARACTER SET对上否则查出来的中文全是问号。2.3 解压后第一时间做四件事解压只是开始别急着往 MySQL 里灌。我拿到任何 CSV 数据包必做的四步预检是看体积、看行数、看表头、看编码。# 看解压后的文件体积和行数 du -sh cdr_data/ wc -l cdr_data/*.csv # 看表头前 5 行确认列名和字段顺序 head -n 5 cdr_data/cdr_summ_imei_cell_info.csv # 看文件编码 file -i cdr_data/cdr_summ_imei_cell_info.csv这四个命令能过滤掉一大半后续麻烦。wc -l得到一个总行数这个数字要记住导入完成后和SELECT COUNT(*)对账head看的是列结构通常这类 CSV 的表头类似date,imei,cell_id,lac_id,call_cnt,call_dur,data_bytes这种但也可能没有表头、直接是数据行这直接决定LOAD DATA要不要IGNORE 1 LINESfile -i看到的编码决定导入时用utf8mb4还是先转码。另外用sed -n 2,5p抽几行中间的数据看重点看有没有奇怪的换行符、引号、逗号换位这关系到后面导入会不会错位。3. 把 CSV 灌进 MySQL建表、LOAD DATA 与导入后校验3.1 先定表结构字段类型建错后面全是泪看完了表头下一步是建表。字段类型的选择标准只有一个这个字段最终要拿来做什么。imei用来做设备去重和关联必须能精确区分设备不能丢精度cell_id用来做小区聚合经常作为GROUP BY和JOIN的关联键时间字段要做范围过滤和按天统计计数字段要聚合求和。把这些需求想清楚再动手写 DDL。CREATE DATABASE IF NOT EXISTS sig_data DEFAULT CHARACTER SET utf8mb4; USE sig_data; CREATE TABLE cdr_summ_imei_cell_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, stat_date DATE NOT NULL COMMENT 统计日期, imei VARCHAR(20) NOT NULL COMMENT 设备唯一标识不参与数值运算, cell_id VARCHAR(32) NOT NULL COMMENT 小区全球标识或本地小区号, lac_id INT UNSIGNED NULL COMMENT 位置区码, call_cnt INT UNSIGNED DEFAULT 0 COMMENT 呼叫次数, call_dur INT UNSIGNED DEFAULT 0 COMMENT 通话时长单位秒, data_bytes BIGINT UNSIGNED DEFAULT 0 COMMENT 数据流量单位字节, update_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 入库时间, PRIMARY KEY (id), KEY idx_imei (imei), KEY idx_cell_date (cell_id, stat_date) ) ENGINEInnoDB;这里有两个设计选择值得展开。第一imei用VARCHAR(20)而不是BIGINT原因会在第四章详细讲核心是一个数字一旦超过 2^63-1BIGINT就装不下了而且如果有前导零BIGINT会把它吞掉。第二索引不是越多越好idx_imei支撑“这个设备出现在哪些小区”的设备级查询idx_cell_date (cell_id, stat_date)是个组合索引支撑“这个小区某天有多少设备”的区域级聚合。如果还有按小时分析的场景可以把stat_date改成stat_time DATETIME再加一个KEY idx_time (stat_time)。但这个表是汇总表粒度通常是“天设备小区”所以DATE够了。3.2 LOAD DATA 导入的最小可用命令导 CSV 进 MySQL最稳的方式是LOAD DATA LOCAL INFILE。它比逐行INSERT快一个数量级几千万行的文件也能在分钟级导完。但 MySQL 5.7 之后local_infile默认是关的要先把开关打开。# 查看 local_infile 是否开启 mysql -uroot -p -e SHOW VARIABLES LIKE local_infile; # 设置为 1当前实例立即生效不用重启 mysql -uroot -p -e SET GLOBAL local_infile 1;SET GLOBAL是运行时生效但新连接才有效所以你会看到有人开了开关后导入还是报错那是因为那个会话是在开关打开前建立的重新连接一次即可。如果不想每次手动开可以在my.cnf的[mysqld]段写local_infile1。接着是导入语句的核心部分LOAD DATA LOCAL INFILE /data/cdr_data/cdr_summ_imei_cell_info.csv INTO TABLE cdr_summ_imei_cell_info CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (stat_date, imei, cell_id, lac_id, call_cnt, call_dur, data_bytes);逐段解释参数。CHARACTER SET utf8mb4要和 CSV 实际编码一致如果源文件是 GBK 这里填gbk但更推荐先iconv转成 UTF-8 再导因为 MySQL 的gbk跟你手头那个 GBK 版本不一定完全对齐。FIELDS TERMINATED BY ,是列分隔符OPTIONALLY ENCLOSED BY 是说字段可能被双引号包着也可能没有这能解决一部分“字段里含逗号”的脏数据。LINES TERMINATED BY \n是行分隔符如果 CSV 是 Windows 下生成的行尾是\r\n这里最好写成\r\n或者导入前用sed -i s/\r$//把\r剥掉否则最后一列会带一个看不见的回车。IGNORE 1 LINES是跳过首行表头如果文件没有表头这行删掉。列清单(stat_date, imei, cell_id, lac_id, call_cnt, call_dur, data_bytes)的顺序必须和 CSV 列顺序完全一致。如果 CSV 里有不想导入的列可以用用户变量占位比如(dummy, imei, cell_id, ...)MySQL 会把值扔进变量里不落表。这个技巧在实际项目中非常常用因为上游交付的 CSV 常常混着一些内部字段。3.3 导入后三步校验别相信导入过程没报错导入完成不报错只是最低标准数据对不对要主动验证。我的习惯是连续跑三条 SQL逐条过。-- 1. 行数对账和 wc -l 的结果比对 SELECT COUNT(*) FROM cdr_summ_imei_cell_info; -- 2. 关键字段完整度imei 和 cell_id 不能有空值 SELECT COUNT(*) AS total, SUM(imei IS NULL OR imei ) AS bad_imei, SUM(cell_id IS NULL OR cell_id ) AS bad_cell FROM cdr_summ_imei_cell_info; -- 3. 抽样肉眼检查 SELECT * FROM cdr_summ_imei_cell_info LIMIT 10;第二步的价值容易被低估。行数对账对上不代表数据没问题IMEI 为空或小区 ID 为空的记录在后面的聚合里会产生一个幽灵设备或幽灵小区查出来数据后发现对不上是常事。如果bad_imei或bad_cell不为零说明源文件里就有脏行这时候先别急着全量清掉用带条件的DELETE把这批数据隔离出来统计占比再决定是丢弃还是回填。4. 字段与数据的落地处理IMEI、时间、小区信息的三个硬骨头4.1 IMEI 别用整数前导零、超长与科学计数法IMEI 是 15 位或 17 位的数字串但“看着像数字”不代表“应该用数字类型存”。这里有个很多新手踩过的坑如果用BIGINT存MySQL 底层是按整数处理的一旦 CSV 里的 IMEI 长度超过BIGINT能表达的范围导入时要么报错要么变成最接近的浮点数精度直接丢失。而且某些老设备的 IMEI 是 14 位数字开头带一个校验位个别数据源还有前导零BIGINT会把它存成去掉前导零的整数后续和别的表做匹配时永远对不上。即使数值范围没超用整数存也有隐患。假如你中间用 pandas 读 CSV 做清洗pd.read_csv(cdr.csv, dtype{imei: str})这行里的dtype一旦忘记指定pandas 会自作主张把它读成int64等到写回 CSV 时再丢一次精度。这种链条上的损耗最好在源头就用VARCHAR切断。我见过一个反例某团队把 IMEI 存成DOUBLE结果查询时看到1.23456E14这类科学计数法所有关联查询全废。所以imei字段请无条件用VARCHAR(20)别给它任何参与数值运算的机会。如果需要做设备级去重直接COUNT(DISTINCT imei)字符串去重对索引的压力远小于超长整数的类型转换。4.2 时间字段Unix 时间戳与字符串的解析时间字段是另一个容易翻车的点。CSV 里最常见的有三种表示标准字符串2023-06-01 12:00:00、日期2023-06-01、Unix 时间戳秒或毫秒级别的整数。第一种直接落进DATETIME列没问题MySQL 能自动识别第二种落进DATE列没问题第三种必须先转换。-- 如果 CSV 里是 Unix 秒级时间戳导入时同步转换 LOAD DATA LOCAL INFILE /data/cdr_data/ts.csv INTO TABLE cdr_summ_imei_cell_info FIELDS TERMINATED BY , IGNORE 1 LINES (stat_date, ts, imei, cell_id) SET stat_date FROM_UNIXTIME(ts, %Y-%m-%d %H:%i:%s);毫秒级时间戳要小心13 位的毫秒值直接FROM_UNIXTIME得到的是 1970 年附近的日期因为函数默认按秒解析。处理方法是先除以 1000FROM_UNIXTIME(ts / 1000)。这个坑我真实踩过导完才发现日期全在 1970 年整批数据重跑了一次。如果是2023/06/01这种斜杠分隔的字符串MySQL 的严格模式下会直接报错非严格模式下会写入0000-00-00。这就是为什么前面一直强调先head看几行数据看到格式不对先统一处理再导。4.3 cell_info 关联基站维度表让数据可用cell_id列本身只是一个编号它对应哪个小区、在哪个经纬度、覆盖哪个行政区通常不在这张汇总表里。想要让数据真正可分析得维护一张基站维度表把小区编号翻译成地理位置和行政归属。常见的做法是建一张dim_cell_base列结构大致是cell_id、base_station_name、province、city、district、longitude、latitude、coverage_type。CREATE TABLE dim_cell_base ( cell_id VARCHAR(32) NOT NULL PRIMARY KEY COMMENT 小区标识, base_name VARCHAR(128) COMMENT 基站名称, province VARCHAR(32), city VARCHAR(32), district VARCHAR(32), longitude DECIMAL(10, 6), latitude DECIMAL(10, 6), coverage_type VARCHAR(16) COMMENT 覆盖类型4G/5G/室分/室外 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;维度表建好后查询时用LEFT JOIN关联比如统计某市某区每天活跃设备数SELECT b.city, b.district, d.stat_date, COUNT(DISTINCT d.imei) AS active_devices FROM cdr_summ_imei_cell_info d LEFT JOIN dim_cell_base b ON d.cell_id b.cell_id WHERE b.city 上海市 GROUP BY b.city, b.district, d.stat_date;如果聚合查询频繁且对响应时间敏感可以把这个 JOIN 的结果物化成一张带行政区的明细表省得每次查都做一次几亿行的大 JOIN。但物化会带来数据冗余和更新成本建议一开始先用 JOIN等确认了查询模式再考虑物化。另外dim_cell_base里可能有部分cell_id匹配不上——这通常意味着上游数据里出现了新开基站的cell_id维度表还没更新。这类缺失要在月度维护窗口统一补齐而不是边查边补。5. 避坑导入这类 CSV 进 MySQL 的 5 个高频翻车点5.1 7z 密码正确但一直报错不是密码问题是版本和编码现象用7z x解压一个带密码的包密码确认无误终端却反复提示Wrong password或者报Cannot open file as archive。原因分两类一是解压工具的版本太旧不支持压缩方使用的 AES-256 高强度加密二是密码里含中文或特殊字符终端 locale 是POSIX或C时输入的字节流和压缩时不一致。解决先升级p7zip到 16.02 以上版本再把终端环境变量切到 UTF-8执行export LC_ALLC.UTF-8后重试。如果还有问题换7z的 Windows 最新版试一次多半能解开。5.2 导入后中文全是乱码CSV 编码与表字符集不一致现象SELECT查出来的中文全是???或方框。原因CSV 是 GBK 编码建表和导入都写了utf8mb4数据在导入时被强制按 UTF-8 解析中文字节对不上。解决先file -i确认源文件编码再做一次转码再导入用iconv -f GBK -t UTF-8 cdr.csv cdr_utf8.csv。转码后别忘了重新看一次表头因为转换过程可能改变文件大小和末尾换行符LOAD DATA的LINES TERMINATED BY也要相应调整。5.3 大 CSV 导入到一半断开两个全局参数和一个分片技巧现象几千万行的文件导到一半报MySQL server has gone away或Lost connection。原因单个 LOAD DATA 事务太大超过了max_allowed_packet或者超过了net_write_timeout的默认时间——导入太久连接被服务端断开。解决导入前调大这两个参数SET GLOBAL max_allowed_packet 512 * 1024 * 1024; SET GLOBAL net_write_timeout 3600;但参数调大只是治标。更稳的通用做法是分片导入。用 Linux 的split命令把大文件拆成多个小文件每个 100 万行左右一个文件导一次哪个文件失败就只重导哪个。# 每 100 万行切一个文件前缀 part_ split -l 1000000 cdr_summ_imei_cell_info.csv part_ # 对每个 part 文件循环导入 for f in part_*; do mysql -uroot -p --local-infile sig_data -e LOAD DATA LOCAL INFILE $f INTO TABLE cdr_summ_imei_cell_info FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY \ IGNORE 1 LINES (stat_date, imei, cell_id, lac_id, call_cnt, call_dur, data_bytes); done循环脚本注意两点第一个文件要带IGNORE 1 LINES跳过表头后面的文件表头已经没了要再带IGNORE 1 LINES会把第一行真实数据丢掉。所以第一个文件单独导其余文件不带IGNORE。这个细节出错率高我建议直接写两次命令而不是在循环里做区分。5.4 IMEI 变成科学计数法 1.23E14Excel 和 pandas 的双重陷阱现象用 Excel 打开 CSVIMEI 列显示1.23457E14精确数字全没了。原因Excel 对超过 11 位的数字自动转科学计数法而且一旦保存原始精度无法恢复。解决不要在 Excel 里打开这种原始数据文件。但这个坑还有一种隐蔽变体——如果你用 pandas 读 CSV 后再写回 CSV没指定dtype同样会经历int64再转字符串的过程超长数字后半段变成 0。所以处理这类文件pd.read_csv(file, dtype{imei: str})是必写参数不是可选项。如果发现手头的 CSV 已经被 Excel 污染过IMEI 已经是科学计数法文本那只能找上游要原始文件重导或者用小区编号 时间 其他字段做近似匹配但这属于紧急修复结构化数据里不建议这么搞。5.5 数据错位和行数对不上引号、逗号与行分隔符的隐藏问题现象LOAD DATA导完SELECT COUNT(*)的行数比wc -l少了或者某一行的字段值串到了相邻列。原因CSV 个别字段内部暗藏了逗号、换行符或双引号导致解析器把一行拆成了两行或把一列拆成了两列。解决把FIELDS TERMINATED BY ,改成带引号识别的版本即FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY 。如果源文件是用 Python 的csv.writer生成的它默认会把含特殊字符的字段用双引号包起来这个参数正好能处理。如果是手拼的 CSV 或者从 Excel 另存的 CSV字段里的逗号可能没被正确转义那就得先清洗。遇到脏数据用awk -F, {print NF}统计每行列数找出列数异于表头的行单独处理。6. 从一个可导入的表到一个可查询的库聚合验证与增量更新6.1 用三条 SQL 验证数据到底能不能用数据导入 MySQL 只是第一步真正判断数据是否可用要看它能不能稳定回答业务问题。我的验证套路是三条 SQL 并行跑按天看设备数、按小区看呼叫量、抽样看单个设备的行为轨迹。-- 按日活跃设备数验证时间维度的覆盖度 SELECT stat_date, COUNT(DISTINCT imei) AS active_devices FROM cdr_summ_imei_cell_info GROUP BY stat_date ORDER BY stat_date; -- 按小区看呼叫总量验证空间维度的聚合能力 SELECT cell_id, SUM(call_cnt) AS total_calls FROM cdr_summ_imei_cell_info GROUP BY cell_id ORDER BY total_calls DESC LIMIT 20; -- 抽查一个设备的跨小区移动轨迹 SELECT stat_date, cell_id, lac_id FROM cdr_summ_imei_cell_info WHERE imei 861234567890123 ORDER BY stat_date;这三条 SQL 分别验证了时间、空间、设备三个维度的可用性。如果某一天设备数骤降到 0大概率是当天数据缺失如果某个小区的呼叫量异常高可能是同名小区 ID 撞了如果单个设备的轨迹出现“瞬间跨省”那就要检查是不是 IMEI 被多设备复用或数据源本身有噪声。6.2 小区级、设备级的聚合查询模板数据验证通过后业务查询就有了固定模板。区域人流分析最常用的是“按日按小区统计活跃设备数”设备画像最常用的是“单个设备的时间-小区序列表”。前者适合用 GROUP BY 配合组合索引后者适合用单个 IMEI 做等值查询。-- 按日按小区统计活跃设备数和人均呼叫次数 SELECT stat_date, cell_id, COUNT(DISTINCT imei) AS active_devices, SUM(call_cnt) / COUNT(DISTINCT imei) AS avg_calls_per_device FROM cdr_summ_imei_cell_info WHERE stat_date BETWEEN 2023-06-01 AND 2023-06-30 GROUP BY stat_date, cell_id ORDER BY stat_date, active_devices DESC;如果聚合查询经常按照这个模板跑可以把这个汇总结果物化成一张小表agg_cell_daily每天由定时任务刷新前端查询走小表响应时间能压到百毫秒级别。物化表不需要保留全部字段只需要stat_date、cell_id、active_devices、total_calls、total_dur这几列体积比明细表小两个数量级维护成本很低。6.3 用导入日志表实现增量的可重跑机制这种 CSV 数据包往往是按周期交付的比如每天凌晨生成前一天的汇总文件。如果每次都用LOAD DATA直接往正式表里插一旦同一个文件被重复执行就会产生重复行。我的做法是建一张导入日志表每次导入前先查这个文件是否处理过处理过就跳过形成一个天然的幂等机制。CREATE TABLE cdr_import_log ( file_name VARCHAR(255) NOT NULL PRIMARY KEY, file_md5 CHAR(32) NOT NULL, row_count INT NOT NULL, imported_at DATETIME DEFAULT CURRENT_TIMESTAMP );导入流程变成三步算文件的 MD5 → 查cdr_import_log里有没有同 MD5 的记录 → 没有才执行LOAD DATA然后向日志表插一条记录。这样即使脚本被调度系统重跑或者有人手动重复执行数据也不会重复入库。如果发现某个文件导坏了删除数据时也先查日志定位到具体文件和导入时间处理起来不会大海捞针。数据导入这件事做得多了之后我对任何数据包的第一反应都是先看结构再做操作从不在没看清表头、没确认编码、没记录行数的情况下直接灌库。灌库后也一定留一手校验和日志因为宁可慢十分钟也不要在一个脏数据上重跑一整夜。希望这些流程和坑能帮你在自己的环境里少走一圈弯路。本文还有配套的精品资源点击获取