简介面向Hive/MySQL数据分析场景的基站掉线率统计实践数据包适合网络优化工程师、数据分析师及数据课程学习者。压缩包内共2个文件包含1个CSV原始数据文件和1个MySQL版本SQL脚本整体大小13.03MBCSV承载通话明细SQL辅助建表与预处理。原始字段覆盖通话时间、基站编号、手机编号、掉话秒数、通话持续总秒数可用于计算各基站掉线率并定位掉线率最高的前10个基站。已有242人浏览学习。读者可直接复用这套CSVSQL组合在Hive或MySQL环境快速完成掉线率统计与基站排名省去造数据和设计表结构的时间也可据此延伸网络质量指标看板或课程实验。1. 拿到 cdr_summ_imei_cell_info(csv-mysql).7z 之后先想清楚这包东西要解决什么问题Hive 里跑完按 IMEI、小区lac cell_id聚合的 CDR 摘要下游业务方往往只认 MySQL于是「摘要导出 CSV、再灌进 MySQL」这条链路在位置分析项目里几乎都要走一遍。这份cdr_summ_imei_cell_info(csv-mysql).7z打包的就是这条链路的两头一份按 IMEI 和小区汇总的 CDR 摘要 CSV以及配套的建表和导入脚本。它不是讲原理的教程是能直接拆开复现的数据落地件。适合正在搭信令摘要取数口径、或者接到「把 CSV 变成 MySQL 可查表」需求的人纯学 MySQL 语法的读者不必下但如果你担心导入时丢 IMEI、空串变 NULL 这类细节这份资源值得拆开看看。下面按我实际拆包、导数据、对账的顺序写。2. 拆包验货7z 解压、编码识别与字段摸底拿到一个.7z先别急着解压。我习惯先用7za l列一下包内清单确认里面是 CSV 还是脚本、有没有子目录、是否加密。这一步能避免解压到一半发现密码不对白折腾。解压之后第一件事也不是导入而是验货确认文件编码、换行符、字段类型这些直接决定后面建表和 LOAD DATA 怎么写。2.1 Linux 下解压 7z 的两条命令与解压前的清单确认后缀是.7z而不是.zip意味着不能指望所有服务器都自带解压工具。Debian/Ubuntu 上装 p7zip 后先用7za l看清单再决定怎么解压# Debian/Ubuntu 系 apt-get install -y p7zip-full # CentOS/RHEL 系 yum install -y p7zip # 查看压缩包内文件清单不实际解压 7za l cdr_summ_imei_cell_info\(csv-mysql\).7z # 完整解压到当前目录 7za x cdr_summ_imei_cell_info\(csv-mysql\).7z注意我给压缩包的括号加了反斜杠转义因为 bash 里()是语法字符不转义命令会直接报错。解压出来一般会看到类似cdr_summ_imei_cell_info.csv的摘要文件外加一到两个.sql或.sh的导入脚本如果包是加密的7za x会交互式提示输入密码也可以用-p密码直接带过去但这样会在 shell 历史里留痕。常见的一个坑是「密码明明正确却一直报错」多半是终端 locale 或字符集导致特殊字符输入走了不同的编码换成交互输入、别用命令行传参基本能解决。提示解压后先用file和wc -l验货再动手改数据。这两个命令的输出要记下来导入后跟 MySQL 里的行数对账用。file cdr_summ_imei_cell_info.csv # 看编码和换行符UTF-8 还是 GBK是否 CRLF wc -l cdr_summ_imei_cell_info.csv # 记住这个行数导入后必须对得上file输出里如果出现CRLF line terminators要特别留意因为它决定第 4 章 LOAD DATA 的LINES TERMINATED是写\n还是\r\n。这个细节最容易让最后一列出现残留的\r表现形式是数字能导入但对账时总和差一点点。老手和新手在第一步就拉开差距的地方就在这里。2.2 用 pandas 摸清 CSV 的字段类型与脏数据在写建表语句之前我一般会用 pandas 先读一小部分把字段的 dtype、空值、脏数据摸一遍。这步看起来多余实际能省掉后面改表结构的返工。信令摘要这种文件动辄几千万行全量读进 pandas 不现实读前 20 万行足够判断了import pandas as pd # 只读前 20 万行够摸清类型和空值规则不用一次吃进整个文件 df pd.read_csv( cdr_summ_imei_cell_info.csv, nrows200000, dtype{imei: str}, # 强制按字符串读避免 IMEI 前导零被吃掉 keep_default_naFalse, # 空字符串保留为 方便判断是空还是 NULL ) print(df.dtypes) # 看每列推断类型 print(df.isna().sum().head()) # 看哪些列有 NaN print(df[imei].str.len().value_counts().head()) # IMEI 长度分布正常应集中在 15 print(df[[lac, cell_id]].describe()) # 小区编号取值范围判断是否需要无符号整型 print(df.duplicated(subset[imei, lac, cell_id, stat_date]).sum()) # 重复行数量pandas 读取 csv 文件时默认会把 IMEI 这种纯数字列推断成 int64一旦源文件里 IMEI 以0开头前导零直接丢而 csv 里的空白单元格会被读成 NaN但 LOAD DATA 里它又是个空字符串两种状态必须区分。上面keep_default_naFalse就是为了把「空字符串」和「缺失值」分开对待。检查完 dtype还要顺手确认时间列格式比如是2024-05-01 10:00:00还是2024/5/1 10:00MySQL 的 DATETIME 对前者直接认对后者要STR_TO_DATE转这个结论直接决定建表时用 DATE 还是 VARCHAR。如果 pandas 里读出来是乱码多半是文件是 GBK 编码而默认按 UTF-8 读了。把encoding参数改成gbk再试一次能通就说明文件确实是 GBK后续 LOAD DATA 的CHARACTER SET也要跟着改成gbk。这里的编码判断错了导入后中文列全是乱码而数字列看起来正常很容易漏掉。字段摸完第 3 章的建表语句才有依据。3. 建表设计CDR 摘要表怎么建才不返工字段摸完之后再写 CREATE TABLE顺序不能反。CDR 摘要表的核心字段就那几类IMEI、LAC、Cell ID、时间、通话/流量统计值。类型选错导入后查出来全是脏数据。这一章把字段映射、类型选择、Hive 侧对照一次说清。3.1 字段映射与类型选择IMEI 别用 BIGINT最常见的坑是把 IMEI 建成 BIGINT。IMEI 是 15 位数字BIGINT 装得下但 CSV 里一旦有前导零、或者文件被 Excel 打开过变成科学计数法BIGINT 一存信息就丢了。我一般直接用 VARCHAR(15)加索引后查询性能没有可感知的差异。建表语句这样写CREATE TABLE cdr_summ_imei_cell_info ( imei VARCHAR(15) NOT NULL COMMENT IMEI 号码统一按字符串存避免前导零丢失, lac INT UNSIGNED NULL COMMENT 位置区编码允许为空表示无信号区, cell_id INT UNSIGNED NULL COMMENT 小区编码与 lac 联合定位, stat_date DATE NOT NULL COMMENT 聚合日期, hour TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 聚合小时 0-23, call_cnt INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 呼叫次数空串按 0 处理, call_dur_sec INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 通话时长秒, data_up_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 上行流量字节数, data_down_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 下行流量字节数, last_seen_at DATETIME NULL COMMENT 该 IMEI 在该小区最后一次出现时间, PRIMARY KEY (imei, stat_date, lac, cell_id), KEY idx_stat_cell (stat_date, lac, cell_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENTCDR 按 IMEI小区聚合摘要表;几个容易被问到的点。DEFAULT 0和 NULL 是两回事CSV 里空字符串导入后默认变 NULL除非 LOAD DATA 的 SET 里显式转 0而 MySQL 设置默认值为 0 只在导入语句「缺列」时生效空字符串不属于缺列——这是很多人对「默认值为什么不生效」的误解根源。lac 和 cell_id 用INT UNSIGNED小区编码没有负数UNSIGNED 让上限翻倍也避免导入负数脏数据时静默存入而不是报错。主键选(imei, stat_date, lac, cell_id)正好对应 Hive 侧 GROUP BY 的粒度如果业务还需要按小时聚合查询把 hour 也加进主键但主键过长会拖慢写入要权衡。这里多说一句字符集utf8mb4和utf8在 MySQL 里是两回事utf8实际是 utf8mb3存不了 emoji 和生僻字。信令摘要里虽然主要是数字但只要有备注或地区名字段就统一用 utf8mb4省得后面扩容。排序规则选utf8mb4_general_ci还是utf8mb4_0900_ai_ci对纯数字查询无差别我习惯用通用默认值不额外花时间调。3.2 Hive 侧摘要与 MySQL 落地表的对照这个资源名里的cdr_summ和关键词里的 hive指向的流程多半是Hive 把 CDR 明细按 IMEI、小区、小时聚合成摘要导出 CSV再灌 MySQL。Hive 侧的产出语句大概长这样-- Hive 侧跑摘要的典型写法产出字段与 MySQL 目标表一一对应 INSERT OVERWRITE TABLE cdr_summ_imei_cell_info SELECT imei, lac, cell_id, stat_date, hour, COUNT(1) AS call_cnt, ROUND(SUM(call_dur)) AS call_dur_sec, NVL(SUM(up_bytes), 0) AS data_up_bytes, NVL(SUM(down_bytes), 0) AS data_down_bytes, MAX(record_time) AS last_seen_at FROM cdr_detail WHERE stat_date ${bizdate} GROUP BY imei, lac, cell_id, stat_date, hour;Hive 跑完的结果是文本文件导出时字段分隔符一般是\t。要变成 MySQL LOAD DATA 能吃的 CSV常见做法是hive -e SELECT ... | sed s/\t/,/g重写分隔符再用 gzip 或 7z 打包就成了你现在拿到的这份资源形态。Hive 导出文本里有个隐藏坑NULL 在 Hive 文本输出里显示为\N反斜杠加大写 N如果直接灌进 MySQL字符串列会存成字面量\N数字列在严格模式直接报错。所以导入前要么sed s/\\N//g把\N替换成空要么在 LOAD DATA 的 SET 里显式判断第 4 章的语句就是这么处理的。MySQL 端与 Hive 端的类型对照不用死记按这张表对就行Hive 类型MySQL 类型说明stringVARCHAR(15)IMEI 一律当字符串别转 numericint / bigintINT UNSIGNED / BIGINT UNSIGNED计数类字段不可能为负加 UNSIGNEDdate / timestampDATE / DATETIMEHive 的yyyy-MM-dd可以直落doubleDECIMAL(12,2)金额、时长类要显式定精度别用 FLOAT文本里输出为 \N 的 NULL先转空串再转 NULL见第 5 章避坑建完表别急着导入先DESC cdr_summ_imei_cell_info确认列顺序和 CSV 表头一致。列顺序不一致是 LOAD DATA 缺列错位的第一大原因我吃过这个亏后来每次都先导出表结构、再跟 CSV 列名手工对照一遍对照完再动手。4. 导入 MySQLLOAD DATA INFILE 参数逐个说清CSV 进 MySQL 有两条路图形工具和命令行 LOAD DATA。十万行以内用 Workbench 的 Import Wizard 点几下没问题一旦到千万行图形工具慢到怀疑人生命令行才是常态。这个资源既然从 Hive 过来数据量不会小直接说命令行。这一章把 LOAD DATA 的每个参数拆开讲清楚为什么这么写。4.1 基础导入语句与 local_infile 开关LOAD DATA LOCAL INFILE /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 -- 如果 file 命令看到 CRLF这行要改成 \r\n IGNORE 1 LINES -- CSV 有表头就留这行没表头就删掉 (imei, lac, cell_id, stat_date, hour, call_cnt, call_dur_sec, data_up_bytes, data_down_bytes, last_seen) SET lac IF(lac OR lac \\N, NULL, lac), cell_id IF(cell_id OR cell_id \\N, NULL, cell_id), hour IF(hour OR hour \\N, 0, hour), last_seen_at IF(last_seen OR last_seen \\N, NULL, last_seen);参数逐个说。LOCAL表示文件在客户端机器上服务端不需要开secure-file-priv但要求连接时允许 local infileCHARACTER SET utf8mb4要和 CSV 实际编码一致第 2 章验出来是 GBK 就写gbk写错的结果是中文列出乱码数字列没事很容易漏看。FIELDS TERMINATED BY ,是字段分隔符有的导出工具用\t或;必须和文件真实分隔符一致OPTIONALLY ENCLOSED BY 表示字符串字段可能带双引号数字字段不带这一行能避免带引号的 IMEI 被连引号一起存进去。LINES TERMINATED BY \n对应 LF 换行file命令看到 CRLF 就改\r\n否则最后一个字段会莫名多一个\r表现为 cell_id 查出来对不上。lac、cell_id这种带的变量是接收原始值的暂存列LOAD DATA 不会直接把它写进表而是走 SET 逻辑做清洗。这里IF(hour, 0, hour)把空字符串转成 0因为空串直接入库会被当 NULL而不是逻辑上的「0 点」IF(lac OR lac \\N, NULL, lac)同时处理了 Hive 导出的空串和\N两种情况。注意字符串里\\N在 MySQL 里转义后就是\N字面量写成\N反而会被当成换行转义这是新手最容易写错的地方。MySQL 8 默认local_infile0直接执行会报ERROR 3948 (42000): Loading local data is disabled。解决方式是两边都开# 服务端开一次全局变量不用重启 mysql -uroot -p -e SET GLOBAL local_infile1; # 客户端连接时带参数 mysql --local-infile1 -uroot -p这个开关有安全隐患生产库上用完最好再关回去。如果用的是连接池注意池里旧的连接对象不会自动感知这个变量改完要重连一次才能生效。4.2 大文件分批导入超过千万行就不要一把梭单条 LOAD DATA 硬灌上千万行一旦中途断掉就是半截数据重跑又可能主键冲突。我习惯按行数切分再串行导入# 去掉表头后每 100 万行切一个分片 tail -n 2 cdr_summ_imei_cell_info.csv | split -l 1000000 - part_ # 循环导入每个分片失败的单独记日志 for f in part_*; do mysql --local-infile1 -uroot -pyour_pass your_db SQL LOAD DATA LOCAL INFILE $f INTO TABLE cdr_summ_imei_cell_info CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n (imei, lac, cell_id, stat_date, hour, call_cnt, call_dur_sec, data_up_bytes, data_down_bytes, last_seen_at); SQL if [ $? -eq 0 ]; then echo $f ok else echo $f failed load_fail.log fi done为什么每片 100 万而不是 500 万单片失败的代价是重新导入这一片片越小重试成本越低但片太小循环次数多IO 开销和进程启动开销也不划算。100 万是我试过多个规模之后取的平衡点如果你的机器磁盘是 SSD可以调到 200 万。导入期间另开一个会话用SHOW PROCESSLIST看进度LOAD DATA 在转储阶段显示copy to tmp table是正常现象。如果开了 binlog 且是单机环境导入时可以用SET sql_log_bin0临时提速但主从环境千万别这么干会导致从库丢数据。导入完成第一件事是看警告LOAD DATA 默认会把部分问题降级成 warning 而不是 errorSHOW WARNINGS;这里经常能看到Incorrect integer value: \N for column lac之类的记录。出现这种告警就说明 SET 里的\\N判断没起作用优先检查 MySQL 转义有没有写错别等到对账时才发现数据不对。表里数据全对之后再用mysqlimport这类工具自动化mysqlimport本质是 LOAD DATA 的命令行封装要求文件名和表名一致才认资源包里如果脚本用的它留意文件名别乱改。5. 避坑清单CSV 导入 MySQL 最容易翻车的五个现场导入看着就是一条 SQL但每个参数背后都有坑。下面五个是我在不同项目里真实踩过的按「现象 → 原因 → 解决」写清楚前两个是环境类后三个是数据类。这条避坑清单里的每条都对应一次让我加班到凌晨的经历。5.1 环境类报错local_infile 与 secure-file-priv现象 1ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sideLOCAL 方式的 LOAD DATA 被直接拒绝。原因MySQL 8.0 默认把local_infile关掉了客户端和服务端必须同时允许光开一边都不行。解决第 4 章那两行——服务端SET GLOBAL local_infile1;客户端连接加--local-infile1。如果两端都开了还在报错检查是不是用了连接池池里旧连接没重建重连一次就好。这个错误在中文社区里搜「error 3948」能找到一堆案例但九成是同一根因。现象 2把LOCAL关键字去掉后报ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement。原因不写 LOCAL 时文件必须在服务端secure-file-priv指定目录里如果该变量为NULL服务端导入被彻底禁止。解决用SHOW VARIABLES LIKE secure_file_priv看目录把 CSV 挪进去再导或者干脆继续用 LOCAL因为 CSV 本来就落在客户端机器上改动最小。我一般直接选 LOCAL前提是能接受客户端有读任意文件的权限风险生产环境要做权限收敛。5.2 数据类翻车IMEI 丢失、空字符串变 NULL、重复导入现象 3导入后SELECT imei FROM t LIMIT 10看着正常但LENGTH(imei) 15或者拿到 Excel 里末尾几位全变0和源数据对不上。原因IMEI 在 CSV 里是纯数字Excel 打开保存一次15 位数字超出 Excel 精度被写成科学计数法或者建表时建成了 BIGINT导入时前导零和末尾精度都保不住。解决源头是第 3 章表结构VARCHAR(15)建表就不会有这个问题如果 CSV 已经被 Excel 祸害过只能回源头重新导出别想着用 SQL 补。导入后立刻跑一句SELECT COUNT(*) FROM t WHERE LENGTH(imei) 15有异常马上停。这个检查我写进了自动导入脚本成了固定步骤。现象 4期望call_cnt为 0 的行查出来是 NULL期望 hour 为 0 的行分组时跑进了 NULL 组。原因CSV 里的空字符串LOAD DATA 默认按 NULL 处理。DEFAULT 0只在列缺失时生效对「空字符串」完全不生效——这就是「mysql设置默认值为0」背后真正的坑不是建表写了 DEFAULT 0 就完事LOAD DATA 有自己的空值规则优先级高于列默认值。解决用暂存变量接住再显式转换SET hour IF(hour, 0, hour)如果是已经导入完的存量表UPDATE ... SET call_cnt0 WHERE call_cnt IS NULL补一遍。判断到底是哪种情况看导入后COUNT(*) WHERE hour IS NULL的结果就行。现象 5导入到一半报Duplicate entry xxx for key PRIMARY整个导入中断。原因源 CSV 有重复行通常是 Hive 侧聚合维度选少了漏了 hour或者任务重跑时把同一天数据导了两次。解决先TRUNCATE再重新导入不要带着半截数据硬补如果线上不能 TRUNCATE先把 CSV 灌进临时表再用INSERT IGNORE或ON DUPLICATE KEY UPDATE合并到正式表。「导入中断、半截数据、重跑冲突」这三连最省心的做法是固定流程先进临时表对账通过后切换到正式表等于随时有后悔药。6. 验证与自动化导入完怎么证明数据是对的数据灌进去了不等于能用。报表哪怕错一天责任就是你的所以对账这一步我从不跳过。验证分三层行数、总量、抽样三层全过才算完。6.1 三方对账行数、总量、抽样第一层行数wc -l的结果和表里 COUNT 必须一致第二层总量源 CSV 的求和与表的 SUM 必须一致第三层抽样随机抽几行逐字段比对。把三层串在一个脚本里# 行数层CSV 行数减去表头 echo csv rows: $(( $(wc -l cdr_summ_imei_cell_info.csv) - 1 )) # 总量层MySQL 里聚合同一天的数据和 Hive 侧同一天的聚合值逐行对 mysql -uroot -p your_db -e SELECT stat_date, COUNT(1) AS row_cnt, SUM(call_cnt) AS call_cnt, SUM(call_dur_sec) AS dur_sec, SUM(data_up_bytes) AS up_bytes FROM cdr_summ_imei_cell_info GROUP BY stat_date ORDER BY stat_date; MySQL 排序在这里的作用是把多天结果按日期排开方便和 Hive 侧输出一行一行对。行数和总量都对上以后再随机抽一条具体记录SELECT * FROM cdr_summ_imei_cell_info WHERE imei 861234567890123 AND stat_date 2024-05-01;拿这行的 lac、cell_id、call_cnt 去源 CSV 里 grep 出来比对全对上才算完。总量对不上时优先查两类原因空字符串转 NULL 导致 sum 偏小以及 CRLF 多出来的\r把最后一列搞脏。这两个占了九成对账失败现场。6.2 把导入固化成定时任务并留出后悔药对账通过后我习惯把这套动作压缩成一个脚本落进 crontab0 3 * * * bash /opt/scripts/load_cdr.sh /var/log/cdr_load.log 21脚本内部顺序固定解压 7z → 验行数 → 灌临时表 → 对账 → 通过后换表。临时表换表那步是关键任何一次失败都不影响线上正式表也算给第二天留了后悔药。脚本最后用对账结果决定退出码失败时日志里出现一行 FAIL配合告警第一时间暴露问题。从那以后我每次做完导入都强制走一遍「先数行、再比总量、最后抽三行看细节」的对账流程哪怕只有几万行的小文件也不跳过。这套习惯是付过一次报表错误的学费才养成的写在这里希望帮到你。本文还有配套的精品资源点击获取 CDR摘要CSV高效导入MySQL：从解压验字段到LOAD DATA避坑全流程
简介面向Hive/MySQL数据分析场景的基站掉线率统计实践数据包适合网络优化工程师、数据分析师及数据课程学习者。压缩包内共2个文件包含1个CSV原始数据文件和1个MySQL版本SQL脚本整体大小13.03MBCSV承载通话明细SQL辅助建表与预处理。原始字段覆盖通话时间、基站编号、手机编号、掉话秒数、通话持续总秒数可用于计算各基站掉线率并定位掉线率最高的前10个基站。已有242人浏览学习。读者可直接复用这套CSVSQL组合在Hive或MySQL环境快速完成掉线率统计与基站排名省去造数据和设计表结构的时间也可据此延伸网络质量指标看板或课程实验。1. 拿到 cdr_summ_imei_cell_info(csv-mysql).7z 之后先想清楚这包东西要解决什么问题Hive 里跑完按 IMEI、小区lac cell_id聚合的 CDR 摘要下游业务方往往只认 MySQL于是「摘要导出 CSV、再灌进 MySQL」这条链路在位置分析项目里几乎都要走一遍。这份cdr_summ_imei_cell_info(csv-mysql).7z打包的就是这条链路的两头一份按 IMEI 和小区汇总的 CDR 摘要 CSV以及配套的建表和导入脚本。它不是讲原理的教程是能直接拆开复现的数据落地件。适合正在搭信令摘要取数口径、或者接到「把 CSV 变成 MySQL 可查表」需求的人纯学 MySQL 语法的读者不必下但如果你担心导入时丢 IMEI、空串变 NULL 这类细节这份资源值得拆开看看。下面按我实际拆包、导数据、对账的顺序写。2. 拆包验货7z 解压、编码识别与字段摸底拿到一个.7z先别急着解压。我习惯先用7za l列一下包内清单确认里面是 CSV 还是脚本、有没有子目录、是否加密。这一步能避免解压到一半发现密码不对白折腾。解压之后第一件事也不是导入而是验货确认文件编码、换行符、字段类型这些直接决定后面建表和 LOAD DATA 怎么写。2.1 Linux 下解压 7z 的两条命令与解压前的清单确认后缀是.7z而不是.zip意味着不能指望所有服务器都自带解压工具。Debian/Ubuntu 上装 p7zip 后先用7za l看清单再决定怎么解压# Debian/Ubuntu 系 apt-get install -y p7zip-full # CentOS/RHEL 系 yum install -y p7zip # 查看压缩包内文件清单不实际解压 7za l cdr_summ_imei_cell_info\(csv-mysql\).7z # 完整解压到当前目录 7za x cdr_summ_imei_cell_info\(csv-mysql\).7z注意我给压缩包的括号加了反斜杠转义因为 bash 里()是语法字符不转义命令会直接报错。解压出来一般会看到类似cdr_summ_imei_cell_info.csv的摘要文件外加一到两个.sql或.sh的导入脚本如果包是加密的7za x会交互式提示输入密码也可以用-p密码直接带过去但这样会在 shell 历史里留痕。常见的一个坑是「密码明明正确却一直报错」多半是终端 locale 或字符集导致特殊字符输入走了不同的编码换成交互输入、别用命令行传参基本能解决。提示解压后先用file和wc -l验货再动手改数据。这两个命令的输出要记下来导入后跟 MySQL 里的行数对账用。file cdr_summ_imei_cell_info.csv # 看编码和换行符UTF-8 还是 GBK是否 CRLF wc -l cdr_summ_imei_cell_info.csv # 记住这个行数导入后必须对得上file输出里如果出现CRLF line terminators要特别留意因为它决定第 4 章 LOAD DATA 的LINES TERMINATED是写\n还是\r\n。这个细节最容易让最后一列出现残留的\r表现形式是数字能导入但对账时总和差一点点。老手和新手在第一步就拉开差距的地方就在这里。2.2 用 pandas 摸清 CSV 的字段类型与脏数据在写建表语句之前我一般会用 pandas 先读一小部分把字段的 dtype、空值、脏数据摸一遍。这步看起来多余实际能省掉后面改表结构的返工。信令摘要这种文件动辄几千万行全量读进 pandas 不现实读前 20 万行足够判断了import pandas as pd # 只读前 20 万行够摸清类型和空值规则不用一次吃进整个文件 df pd.read_csv( cdr_summ_imei_cell_info.csv, nrows200000, dtype{imei: str}, # 强制按字符串读避免 IMEI 前导零被吃掉 keep_default_naFalse, # 空字符串保留为 方便判断是空还是 NULL ) print(df.dtypes) # 看每列推断类型 print(df.isna().sum().head()) # 看哪些列有 NaN print(df[imei].str.len().value_counts().head()) # IMEI 长度分布正常应集中在 15 print(df[[lac, cell_id]].describe()) # 小区编号取值范围判断是否需要无符号整型 print(df.duplicated(subset[imei, lac, cell_id, stat_date]).sum()) # 重复行数量pandas 读取 csv 文件时默认会把 IMEI 这种纯数字列推断成 int64一旦源文件里 IMEI 以0开头前导零直接丢而 csv 里的空白单元格会被读成 NaN但 LOAD DATA 里它又是个空字符串两种状态必须区分。上面keep_default_naFalse就是为了把「空字符串」和「缺失值」分开对待。检查完 dtype还要顺手确认时间列格式比如是2024-05-01 10:00:00还是2024/5/1 10:00MySQL 的 DATETIME 对前者直接认对后者要STR_TO_DATE转这个结论直接决定建表时用 DATE 还是 VARCHAR。如果 pandas 里读出来是乱码多半是文件是 GBK 编码而默认按 UTF-8 读了。把encoding参数改成gbk再试一次能通就说明文件确实是 GBK后续 LOAD DATA 的CHARACTER SET也要跟着改成gbk。这里的编码判断错了导入后中文列全是乱码而数字列看起来正常很容易漏掉。字段摸完第 3 章的建表语句才有依据。3. 建表设计CDR 摘要表怎么建才不返工字段摸完之后再写 CREATE TABLE顺序不能反。CDR 摘要表的核心字段就那几类IMEI、LAC、Cell ID、时间、通话/流量统计值。类型选错导入后查出来全是脏数据。这一章把字段映射、类型选择、Hive 侧对照一次说清。3.1 字段映射与类型选择IMEI 别用 BIGINT最常见的坑是把 IMEI 建成 BIGINT。IMEI 是 15 位数字BIGINT 装得下但 CSV 里一旦有前导零、或者文件被 Excel 打开过变成科学计数法BIGINT 一存信息就丢了。我一般直接用 VARCHAR(15)加索引后查询性能没有可感知的差异。建表语句这样写CREATE TABLE cdr_summ_imei_cell_info ( imei VARCHAR(15) NOT NULL COMMENT IMEI 号码统一按字符串存避免前导零丢失, lac INT UNSIGNED NULL COMMENT 位置区编码允许为空表示无信号区, cell_id INT UNSIGNED NULL COMMENT 小区编码与 lac 联合定位, stat_date DATE NOT NULL COMMENT 聚合日期, hour TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 聚合小时 0-23, call_cnt INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 呼叫次数空串按 0 处理, call_dur_sec INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 通话时长秒, data_up_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 上行流量字节数, data_down_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 下行流量字节数, last_seen_at DATETIME NULL COMMENT 该 IMEI 在该小区最后一次出现时间, PRIMARY KEY (imei, stat_date, lac, cell_id), KEY idx_stat_cell (stat_date, lac, cell_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENTCDR 按 IMEI小区聚合摘要表;几个容易被问到的点。DEFAULT 0和 NULL 是两回事CSV 里空字符串导入后默认变 NULL除非 LOAD DATA 的 SET 里显式转 0而 MySQL 设置默认值为 0 只在导入语句「缺列」时生效空字符串不属于缺列——这是很多人对「默认值为什么不生效」的误解根源。lac 和 cell_id 用INT UNSIGNED小区编码没有负数UNSIGNED 让上限翻倍也避免导入负数脏数据时静默存入而不是报错。主键选(imei, stat_date, lac, cell_id)正好对应 Hive 侧 GROUP BY 的粒度如果业务还需要按小时聚合查询把 hour 也加进主键但主键过长会拖慢写入要权衡。这里多说一句字符集utf8mb4和utf8在 MySQL 里是两回事utf8实际是 utf8mb3存不了 emoji 和生僻字。信令摘要里虽然主要是数字但只要有备注或地区名字段就统一用 utf8mb4省得后面扩容。排序规则选utf8mb4_general_ci还是utf8mb4_0900_ai_ci对纯数字查询无差别我习惯用通用默认值不额外花时间调。3.2 Hive 侧摘要与 MySQL 落地表的对照这个资源名里的cdr_summ和关键词里的 hive指向的流程多半是Hive 把 CDR 明细按 IMEI、小区、小时聚合成摘要导出 CSV再灌 MySQL。Hive 侧的产出语句大概长这样-- Hive 侧跑摘要的典型写法产出字段与 MySQL 目标表一一对应 INSERT OVERWRITE TABLE cdr_summ_imei_cell_info SELECT imei, lac, cell_id, stat_date, hour, COUNT(1) AS call_cnt, ROUND(SUM(call_dur)) AS call_dur_sec, NVL(SUM(up_bytes), 0) AS data_up_bytes, NVL(SUM(down_bytes), 0) AS data_down_bytes, MAX(record_time) AS last_seen_at FROM cdr_detail WHERE stat_date ${bizdate} GROUP BY imei, lac, cell_id, stat_date, hour;Hive 跑完的结果是文本文件导出时字段分隔符一般是\t。要变成 MySQL LOAD DATA 能吃的 CSV常见做法是hive -e SELECT ... | sed s/\t/,/g重写分隔符再用 gzip 或 7z 打包就成了你现在拿到的这份资源形态。Hive 导出文本里有个隐藏坑NULL 在 Hive 文本输出里显示为\N反斜杠加大写 N如果直接灌进 MySQL字符串列会存成字面量\N数字列在严格模式直接报错。所以导入前要么sed s/\\N//g把\N替换成空要么在 LOAD DATA 的 SET 里显式判断第 4 章的语句就是这么处理的。MySQL 端与 Hive 端的类型对照不用死记按这张表对就行Hive 类型MySQL 类型说明stringVARCHAR(15)IMEI 一律当字符串别转 numericint / bigintINT UNSIGNED / BIGINT UNSIGNED计数类字段不可能为负加 UNSIGNEDdate / timestampDATE / DATETIMEHive 的yyyy-MM-dd可以直落doubleDECIMAL(12,2)金额、时长类要显式定精度别用 FLOAT文本里输出为 \N 的 NULL先转空串再转 NULL见第 5 章避坑建完表别急着导入先DESC cdr_summ_imei_cell_info确认列顺序和 CSV 表头一致。列顺序不一致是 LOAD DATA 缺列错位的第一大原因我吃过这个亏后来每次都先导出表结构、再跟 CSV 列名手工对照一遍对照完再动手。4. 导入 MySQLLOAD DATA INFILE 参数逐个说清CSV 进 MySQL 有两条路图形工具和命令行 LOAD DATA。十万行以内用 Workbench 的 Import Wizard 点几下没问题一旦到千万行图形工具慢到怀疑人生命令行才是常态。这个资源既然从 Hive 过来数据量不会小直接说命令行。这一章把 LOAD DATA 的每个参数拆开讲清楚为什么这么写。4.1 基础导入语句与 local_infile 开关LOAD DATA LOCAL INFILE /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 -- 如果 file 命令看到 CRLF这行要改成 \r\n IGNORE 1 LINES -- CSV 有表头就留这行没表头就删掉 (imei, lac, cell_id, stat_date, hour, call_cnt, call_dur_sec, data_up_bytes, data_down_bytes, last_seen) SET lac IF(lac OR lac \\N, NULL, lac), cell_id IF(cell_id OR cell_id \\N, NULL, cell_id), hour IF(hour OR hour \\N, 0, hour), last_seen_at IF(last_seen OR last_seen \\N, NULL, last_seen);参数逐个说。LOCAL表示文件在客户端机器上服务端不需要开secure-file-priv但要求连接时允许 local infileCHARACTER SET utf8mb4要和 CSV 实际编码一致第 2 章验出来是 GBK 就写gbk写错的结果是中文列出乱码数字列没事很容易漏看。FIELDS TERMINATED BY ,是字段分隔符有的导出工具用\t或;必须和文件真实分隔符一致OPTIONALLY ENCLOSED BY 表示字符串字段可能带双引号数字字段不带这一行能避免带引号的 IMEI 被连引号一起存进去。LINES TERMINATED BY \n对应 LF 换行file命令看到 CRLF 就改\r\n否则最后一个字段会莫名多一个\r表现为 cell_id 查出来对不上。lac、cell_id这种带的变量是接收原始值的暂存列LOAD DATA 不会直接把它写进表而是走 SET 逻辑做清洗。这里IF(hour, 0, hour)把空字符串转成 0因为空串直接入库会被当 NULL而不是逻辑上的「0 点」IF(lac OR lac \\N, NULL, lac)同时处理了 Hive 导出的空串和\N两种情况。注意字符串里\\N在 MySQL 里转义后就是\N字面量写成\N反而会被当成换行转义这是新手最容易写错的地方。MySQL 8 默认local_infile0直接执行会报ERROR 3948 (42000): Loading local data is disabled。解决方式是两边都开# 服务端开一次全局变量不用重启 mysql -uroot -p -e SET GLOBAL local_infile1; # 客户端连接时带参数 mysql --local-infile1 -uroot -p这个开关有安全隐患生产库上用完最好再关回去。如果用的是连接池注意池里旧的连接对象不会自动感知这个变量改完要重连一次才能生效。4.2 大文件分批导入超过千万行就不要一把梭单条 LOAD DATA 硬灌上千万行一旦中途断掉就是半截数据重跑又可能主键冲突。我习惯按行数切分再串行导入# 去掉表头后每 100 万行切一个分片 tail -n 2 cdr_summ_imei_cell_info.csv | split -l 1000000 - part_ # 循环导入每个分片失败的单独记日志 for f in part_*; do mysql --local-infile1 -uroot -pyour_pass your_db SQL LOAD DATA LOCAL INFILE $f INTO TABLE cdr_summ_imei_cell_info CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n (imei, lac, cell_id, stat_date, hour, call_cnt, call_dur_sec, data_up_bytes, data_down_bytes, last_seen_at); SQL if [ $? -eq 0 ]; then echo $f ok else echo $f failed load_fail.log fi done为什么每片 100 万而不是 500 万单片失败的代价是重新导入这一片片越小重试成本越低但片太小循环次数多IO 开销和进程启动开销也不划算。100 万是我试过多个规模之后取的平衡点如果你的机器磁盘是 SSD可以调到 200 万。导入期间另开一个会话用SHOW PROCESSLIST看进度LOAD DATA 在转储阶段显示copy to tmp table是正常现象。如果开了 binlog 且是单机环境导入时可以用SET sql_log_bin0临时提速但主从环境千万别这么干会导致从库丢数据。导入完成第一件事是看警告LOAD DATA 默认会把部分问题降级成 warning 而不是 errorSHOW WARNINGS;这里经常能看到Incorrect integer value: \N for column lac之类的记录。出现这种告警就说明 SET 里的\\N判断没起作用优先检查 MySQL 转义有没有写错别等到对账时才发现数据不对。表里数据全对之后再用mysqlimport这类工具自动化mysqlimport本质是 LOAD DATA 的命令行封装要求文件名和表名一致才认资源包里如果脚本用的它留意文件名别乱改。5. 避坑清单CSV 导入 MySQL 最容易翻车的五个现场导入看着就是一条 SQL但每个参数背后都有坑。下面五个是我在不同项目里真实踩过的按「现象 → 原因 → 解决」写清楚前两个是环境类后三个是数据类。这条避坑清单里的每条都对应一次让我加班到凌晨的经历。5.1 环境类报错local_infile 与 secure-file-priv现象 1ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sideLOCAL 方式的 LOAD DATA 被直接拒绝。原因MySQL 8.0 默认把local_infile关掉了客户端和服务端必须同时允许光开一边都不行。解决第 4 章那两行——服务端SET GLOBAL local_infile1;客户端连接加--local-infile1。如果两端都开了还在报错检查是不是用了连接池池里旧连接没重建重连一次就好。这个错误在中文社区里搜「error 3948」能找到一堆案例但九成是同一根因。现象 2把LOCAL关键字去掉后报ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement。原因不写 LOCAL 时文件必须在服务端secure-file-priv指定目录里如果该变量为NULL服务端导入被彻底禁止。解决用SHOW VARIABLES LIKE secure_file_priv看目录把 CSV 挪进去再导或者干脆继续用 LOCAL因为 CSV 本来就落在客户端机器上改动最小。我一般直接选 LOCAL前提是能接受客户端有读任意文件的权限风险生产环境要做权限收敛。5.2 数据类翻车IMEI 丢失、空字符串变 NULL、重复导入现象 3导入后SELECT imei FROM t LIMIT 10看着正常但LENGTH(imei) 15或者拿到 Excel 里末尾几位全变0和源数据对不上。原因IMEI 在 CSV 里是纯数字Excel 打开保存一次15 位数字超出 Excel 精度被写成科学计数法或者建表时建成了 BIGINT导入时前导零和末尾精度都保不住。解决源头是第 3 章表结构VARCHAR(15)建表就不会有这个问题如果 CSV 已经被 Excel 祸害过只能回源头重新导出别想着用 SQL 补。导入后立刻跑一句SELECT COUNT(*) FROM t WHERE LENGTH(imei) 15有异常马上停。这个检查我写进了自动导入脚本成了固定步骤。现象 4期望call_cnt为 0 的行查出来是 NULL期望 hour 为 0 的行分组时跑进了 NULL 组。原因CSV 里的空字符串LOAD DATA 默认按 NULL 处理。DEFAULT 0只在列缺失时生效对「空字符串」完全不生效——这就是「mysql设置默认值为0」背后真正的坑不是建表写了 DEFAULT 0 就完事LOAD DATA 有自己的空值规则优先级高于列默认值。解决用暂存变量接住再显式转换SET hour IF(hour, 0, hour)如果是已经导入完的存量表UPDATE ... SET call_cnt0 WHERE call_cnt IS NULL补一遍。判断到底是哪种情况看导入后COUNT(*) WHERE hour IS NULL的结果就行。现象 5导入到一半报Duplicate entry xxx for key PRIMARY整个导入中断。原因源 CSV 有重复行通常是 Hive 侧聚合维度选少了漏了 hour或者任务重跑时把同一天数据导了两次。解决先TRUNCATE再重新导入不要带着半截数据硬补如果线上不能 TRUNCATE先把 CSV 灌进临时表再用INSERT IGNORE或ON DUPLICATE KEY UPDATE合并到正式表。「导入中断、半截数据、重跑冲突」这三连最省心的做法是固定流程先进临时表对账通过后切换到正式表等于随时有后悔药。6. 验证与自动化导入完怎么证明数据是对的数据灌进去了不等于能用。报表哪怕错一天责任就是你的所以对账这一步我从不跳过。验证分三层行数、总量、抽样三层全过才算完。6.1 三方对账行数、总量、抽样第一层行数wc -l的结果和表里 COUNT 必须一致第二层总量源 CSV 的求和与表的 SUM 必须一致第三层抽样随机抽几行逐字段比对。把三层串在一个脚本里# 行数层CSV 行数减去表头 echo csv rows: $(( $(wc -l cdr_summ_imei_cell_info.csv) - 1 )) # 总量层MySQL 里聚合同一天的数据和 Hive 侧同一天的聚合值逐行对 mysql -uroot -p your_db -e SELECT stat_date, COUNT(1) AS row_cnt, SUM(call_cnt) AS call_cnt, SUM(call_dur_sec) AS dur_sec, SUM(data_up_bytes) AS up_bytes FROM cdr_summ_imei_cell_info GROUP BY stat_date ORDER BY stat_date; MySQL 排序在这里的作用是把多天结果按日期排开方便和 Hive 侧输出一行一行对。行数和总量都对上以后再随机抽一条具体记录SELECT * FROM cdr_summ_imei_cell_info WHERE imei 861234567890123 AND stat_date 2024-05-01;拿这行的 lac、cell_id、call_cnt 去源 CSV 里 grep 出来比对全对上才算完。总量对不上时优先查两类原因空字符串转 NULL 导致 sum 偏小以及 CRLF 多出来的\r把最后一列搞脏。这两个占了九成对账失败现场。6.2 把导入固化成定时任务并留出后悔药对账通过后我习惯把这套动作压缩成一个脚本落进 crontab0 3 * * * bash /opt/scripts/load_cdr.sh /var/log/cdr_load.log 21脚本内部顺序固定解压 7z → 验行数 → 灌临时表 → 对账 → 通过后换表。临时表换表那步是关键任何一次失败都不影响线上正式表也算给第二天留了后悔药。脚本最后用对账结果决定退出码失败时日志里出现一行 FAIL配合告警第一时间暴露问题。从那以后我每次做完导入都强制走一遍「先数行、再比总量、最后抽三行看细节」的对账流程哪怕只有几万行的小文件也不跳过。这套习惯是付过一次报表错误的学费才养成的写在这里希望帮到你。本文还有配套的精品资源点击获取