簡(jiǎn)介本資源圍繞通話掉線率統(tǒng)計(jì)場(chǎng)景提供基站維度數(shù)據(jù)分析的原始數(shù)據(jù)集與入庫(kù)腳本適合Hive/MySQL方向的數(shù)據(jù)分析初學(xué)者及需要處理話單數(shù)據(jù)的運(yùn)維人員。壓縮包內(nèi)共2個(gè)文件包含1個(gè)SQL建表與導(dǎo)入腳本和1個(gè)CSV原始數(shù)據(jù)文件整體約13.03MBCSV對(duì)應(yīng)表結(jié)構(gòu)中的record_time、imei、cell、drop_num、duration字段SQL便于直接將數(shù)據(jù)導(dǎo)入MySQL進(jìn)行進(jìn)一步統(tǒng)計(jì)可據(jù)此還原出掉線率最高的前10基站分析過(guò)程。已有242人學(xué)習(xí)下載。讀者可獲得一份可直接導(dǎo)入使用的MySQL版本話單數(shù)據(jù)以及配套的建表語(yǔ)句和字段說(shuō)明便于快速開(kāi)展掉話率計(jì)算、基站排名等實(shí)踐練習(xí)也可作為學(xué)習(xí)Hive與MySQL數(shù)據(jù)入庫(kù)、查詢分析的入門數(shù)據(jù)素材。1. cdr_summ_imei_cell_info 是什么一個(gè) 7z 壓縮包里的信令數(shù)據(jù)底座拿到cdr_summ_imei_cell_info(csv-mysql).7z這類文件名時(shí)別急著解壓先把這個(gè)名字拆開(kāi)讀——它基本把整個(gè)數(shù)據(jù)交付方案寫(xiě)臉上了cdr_summ是 CDRCall Detail Record通話/呼叫詳情記錄的匯總結(jié)果imei是設(shè)備標(biāo)識(shí)cell_info是基站小區(qū)信息csv是交付格式mysql是目標(biāo)存儲(chǔ)7z是壓縮打包方式。這套東西在手機(jī)信令分析、區(qū)域人流洞察、通信網(wǎng)絡(luò)質(zhì)量評(píng)估項(xiàng)目里非常常見(jiàn)是一張已經(jīng)清洗好的“設(shè)備-小區(qū)-時(shí)間”三維事實(shí)表。本文會(huì)按“解壓 → 預(yù)檢 → 建表導(dǎo)入 → 字段處理 → 踩坑 → 聚合查詢”的順序把這條路完整走一遍。適合手里剛拿到這種 CSV 數(shù)據(jù)包、正琢磨怎么落庫(kù)的工程師和數(shù)據(jù)分析師。2. 先把 7z 安全解開(kāi)Linux 與 Windows 下的解壓命令和預(yù)檢清單2.1 Linux 下解壓 7z 的最小命令7z 不是 Linux 默認(rèn)自帶的格式很多最小化安裝的服務(wù)器上連7z命令都不存在。所以第一步是裝工具。Debian/Ubuntu 系用p7zip-fullCentOS/RHEL 系用p7zip這個(gè)包提供7z、7za、7zr三個(gè)二進(jìn)制日常用7z就夠了。# Debian/Ubuntu sudo apt update sudo apt install -y p7zip-full # CentOS/RHEL sudo yum install -y p7zip裝完之后不要直接7z x解壓先看一下壓縮包里到底有什么。7z l只列清單不落盤能讓你確認(rèn)里面是單個(gè) CSV 還是多個(gè) CSV路徑結(jié)構(gòu)長(zhǎng)什么樣還能順帶看每個(gè)文件的壓縮前大小。這一步對(duì)后面判斷“要不要拆文件導(dǎo)、單表多大、磁盤夠不夠”非常關(guān)鍵。# 查看壓縮包內(nèi)容清單不占用磁盤 7z l cdr_summ_imei_cell_info(csv-mysql).7z # 解壓到指定目錄-o 后面直接跟路徑不要留空格 7z x cdr_summ_imei_cell_info(csv-mysql).7z -o./cdr_data有幾個(gè)細(xì)節(jié)值得強(qiáng)調(diào)。7z x會(huì)保留壓縮包內(nèi)的目錄結(jié)構(gòu)7z e會(huì)把所有文件平鋪到當(dāng)前目錄——如果壓縮包里按日期分了好幾個(gè)子目錄建議用x保留結(jié)構(gòu)否則同名文件會(huì)互相覆蓋。再有就是解壓路徑如果不存在7z會(huì)自動(dòng)創(chuàng)建不需要先mkdir。另外如果這個(gè)包加密了解壓時(shí)會(huì)提示輸入密碼交互式輸入就行但把密碼直接寫(xiě)在命令行參數(shù)里-p密碼會(huì)被 shell 歷史記錄和進(jìn)程列表泄露多人在同一臺(tái)服務(wù)器上時(shí)尤其注意我一般寧可交互輸入。2.2 Windows 下解壓與編碼檢查Windows 端最省事的做法是裝 7-Zip右鍵壓縮包選“提取到當(dāng)前位置”或“提取到指定文件夾”。但如果你的數(shù)據(jù)流水線要寫(xiě)成批處理腳本命令行方式更可靠。7-Zip 安裝目錄下的7z.exe可以在 cmd 里直接用注意路徑帶空格要加引號(hào)。C:\Program Files\7-Zip\7z.exe x D:\data\cdr_summ_imei_cell_info(csv-mysql).7z -oD:\data\cdr_csv -y-y是遇到同名文件直接覆蓋批量跑腳本時(shí)能省去卡在交互提示的問(wèn)題。Windows 端比 Linux 多一個(gè)容易被忽略的檢查CSV 的編碼。用記事本或 Notepad 打開(kāi) CSV如果能看到中文看右下角編碼顯示常見(jiàn)的是 UTF-8、ANSIGBK、UTF-8 BOM 三種。后面的 MySQL 導(dǎo)入環(huán)節(jié)編碼必須和建表語(yǔ)句里的CHARACTER SET對(duì)上否則查出來(lái)的中文全是問(wèn)號(hào)。2.3 解壓后第一時(shí)間做四件事解壓只是開(kāi)始別急著往 MySQL 里灌。我拿到任何 CSV 數(shù)據(jù)包必做的四步預(yù)檢是看體積、看行數(shù)、看表頭、看編碼。# 看解壓后的文件體積和行數(shù) du -sh cdr_data/ wc -l cdr_data/*.csv # 看表頭前 5 行確認(rèn)列名和字段順序 head -n 5 cdr_data/cdr_summ_imei_cell_info.csv # 看文件編碼 file -i cdr_data/cdr_summ_imei_cell_info.csv這四個(gè)命令能過(guò)濾掉一大半后續(xù)麻煩。wc -l得到一個(gè)總行數(shù)這個(gè)數(shù)字要記住導(dǎo)入完成后和SELECT COUNT(*)對(duì)賬head看的是列結(jié)構(gòu)通常這類 CSV 的表頭類似date,imei,cell_id,lac_id,call_cnt,call_dur,data_bytes這種但也可能沒(méi)有表頭、直接是數(shù)據(jù)行這直接決定LOAD DATA要不要IGNORE 1 LINESfile -i看到的編碼決定導(dǎo)入時(shí)用utf8mb4還是先轉(zhuǎn)碼。另外用sed -n 2,5p抽幾行中間的數(shù)據(jù)看重點(diǎn)看有沒(méi)有奇怪的換行符、引號(hào)、逗號(hào)換位這關(guān)系到后面導(dǎo)入會(huì)不會(huì)錯(cuò)位。3. 把 CSV 灌進(jìn) MySQL建表、LOAD DATA 與導(dǎo)入后校驗(yàn)3.1 先定表結(jié)構(gòu)字段類型建錯(cuò)后面全是淚看完了表頭下一步是建表。字段類型的選擇標(biāo)準(zhǔn)只有一個(gè)這個(gè)字段最終要拿來(lái)做什么。imei用來(lái)做設(shè)備去重和關(guān)聯(lián)必須能精確區(qū)分設(shè)備不能丟精度cell_id用來(lái)做小區(qū)聚合經(jīng)常作為GROUP BY和JOIN的關(guān)聯(lián)鍵時(shí)間字段要做范圍過(guò)濾和按天統(tǒng)計(jì)計(jì)數(shù)字段要聚合求和。把這些需求想清楚再動(dòng)手寫(xiě) 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 統(tǒng)計(jì)日期, imei VARCHAR(20) NOT NULL COMMENT 設(shè)備唯一標(biāo)識(shí)不參與數(shù)值運(yùn)算, cell_id VARCHAR(32) NOT NULL COMMENT 小區(qū)全球標(biāo)識(shí)或本地小區(qū)號(hào), lac_id INT UNSIGNED NULL COMMENT 位置區(qū)碼, call_cnt INT UNSIGNED DEFAULT 0 COMMENT 呼叫次數(shù), call_dur INT UNSIGNED DEFAULT 0 COMMENT 通話時(shí)長(zhǎng)單位秒, data_bytes BIGINT UNSIGNED DEFAULT 0 COMMENT 數(shù)據(jù)流量單位字節(jié), update_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 入庫(kù)時(shí)間, PRIMARY KEY (id), KEY idx_imei (imei), KEY idx_cell_date (cell_id, stat_date) ) ENGINEInnoDB;這里有兩個(gè)設(shè)計(jì)選擇值得展開(kāi)。第一imei用VARCHAR(20)而不是BIGINT原因會(huì)在第四章詳細(xì)講核心是一個(gè)數(shù)字一旦超過(guò) 2^63-1BIGINT就裝不下了而且如果有前導(dǎo)零BIGINT會(huì)把它吞掉。第二索引不是越多越好idx_imei支撐“這個(gè)設(shè)備出現(xiàn)在哪些小區(qū)”的設(shè)備級(jí)查詢idx_cell_date (cell_id, stat_date)是個(gè)組合索引支撐“這個(gè)小區(qū)某天有多少設(shè)備”的區(qū)域級(jí)聚合。如果還有按小時(shí)分析的場(chǎng)景可以把stat_date改成stat_time DATETIME再加一個(gè)KEY idx_time (stat_time)。但這個(gè)表是匯總表粒度通常是“天設(shè)備小區(qū)”所以DATE夠了。3.2 LOAD DATA 導(dǎo)入的最小可用命令導(dǎo) CSV 進(jìn) MySQL最穩(wěn)的方式是LOAD DATA LOCAL INFILE。它比逐行INSERT快一個(gè)數(shù)量級(jí)幾千萬(wàn)行的文件也能在分鐘級(jí)導(dǎo)完。但 MySQL 5.7 之后local_infile默認(rèn)是關(guān)的要先把開(kāi)關(guān)打開(kāi)。# 查看 local_infile 是否開(kāi)啟 mysql -uroot -p -e SHOW VARIABLES LIKE local_infile; # 設(shè)置為 1當(dāng)前實(shí)例立即生效不用重啟 mysql -uroot -p -e SET GLOBAL local_infile 1;SET GLOBAL是運(yùn)行時(shí)生效但新連接才有效所以你會(huì)看到有人開(kāi)了開(kāi)關(guān)后導(dǎo)入還是報(bào)錯(cuò)那是因?yàn)槟莻€(gè)會(huì)話是在開(kāi)關(guān)打開(kāi)前建立的重新連接一次即可。如果不想每次手動(dòng)開(kāi)可以在my.cnf的[mysqld]段寫(xiě)local_infile1。接著是導(dǎo)入語(yǔ)句的核心部分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);逐段解釋參數(shù)。CHARACTER SET utf8mb4要和 CSV 實(shí)際編碼一致如果源文件是 GBK 這里填gbk但更推薦先iconv轉(zhuǎn)成 UTF-8 再導(dǎo)因?yàn)?MySQL 的gbk跟你手頭那個(gè) GBK 版本不一定完全對(duì)齊。FIELDS TERMINATED BY ,是列分隔符OPTIONALLY ENCLOSED BY 是說(shuō)字段可能被雙引號(hào)包著也可能沒(méi)有這能解決一部分“字段里含逗號(hào)”的臟數(shù)據(jù)。LINES TERMINATED BY \n是行分隔符如果 CSV 是 Windows 下生成的行尾是\r\n這里最好寫(xiě)成\r\n或者導(dǎo)入前用sed -i s/\r$//把\r剝掉否則最后一列會(huì)帶一個(gè)看不見(jiàn)的回車。IGNORE 1 LINES是跳過(guò)首行表頭如果文件沒(méi)有表頭這行刪掉。列清單(stat_date, imei, cell_id, lac_id, call_cnt, call_dur, data_bytes)的順序必須和 CSV 列順序完全一致。如果 CSV 里有不想導(dǎo)入的列可以用用戶變量占位比如(dummy, imei, cell_id, ...)MySQL 會(huì)把值扔進(jìn)變量里不落表。這個(gè)技巧在實(shí)際項(xiàng)目中非常常用因?yàn)樯嫌谓桓兜?CSV 常常混著一些內(nèi)部字段。3.3 導(dǎo)入后三步校驗(yàn)別相信導(dǎo)入過(guò)程沒(méi)報(bào)錯(cuò)導(dǎo)入完成不報(bào)錯(cuò)只是最低標(biāo)準(zhǔn)數(shù)據(jù)對(duì)不對(duì)要主動(dòng)驗(yàn)證。我的習(xí)慣是連續(xù)跑三條 SQL逐條過(guò)。-- 1. 行數(shù)對(duì)賬和 wc -l 的結(jié)果比對(duì) SELECT COUNT(*) FROM cdr_summ_imei_cell_info; -- 2. 關(guān)鍵字段完整度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;第二步的價(jià)值容易被低估。行數(shù)對(duì)賬對(duì)上不代表數(shù)據(jù)沒(méi)問(wèn)題IMEI 為空或小區(qū) ID 為空的記錄在后面的聚合里會(huì)產(chǎn)生一個(gè)幽靈設(shè)備或幽靈小區(qū)查出來(lái)數(shù)據(jù)后發(fā)現(xiàn)對(duì)不上是常事。如果bad_imei或bad_cell不為零說(shuō)明源文件里就有臟行這時(shí)候先別急著全量清掉用帶條件的DELETE把這批數(shù)據(jù)隔離出來(lái)統(tǒng)計(jì)占比再?zèng)Q定是丟棄還是回填。4. 字段與數(shù)據(jù)的落地處理IMEI、時(shí)間、小區(qū)信息的三個(gè)硬骨頭4.1 IMEI 別用整數(shù)前導(dǎo)零、超長(zhǎng)與科學(xué)計(jì)數(shù)法IMEI 是 15 位或 17 位的數(shù)字串但“看著像數(shù)字”不代表“應(yīng)該用數(shù)字類型存”。這里有個(gè)很多新手踩過(guò)的坑如果用BIGINT存MySQL 底層是按整數(shù)處理的一旦 CSV 里的 IMEI 長(zhǎng)度超過(guò)BIGINT能表達(dá)的范圍導(dǎo)入時(shí)要么報(bào)錯(cuò)要么變成最接近的浮點(diǎn)數(shù)精度直接丟失。而且某些老設(shè)備的 IMEI 是 14 位數(shù)字開(kāi)頭帶一個(gè)校驗(yàn)位個(gè)別數(shù)據(jù)源還有前導(dǎo)零BIGINT會(huì)把它存成去掉前導(dǎo)零的整數(shù)后續(xù)和別的表做匹配時(shí)永遠(yuǎn)對(duì)不上。即使數(shù)值范圍沒(méi)超用整數(shù)存也有隱患。假如你中間用 pandas 讀 CSV 做清洗pd.read_csv(cdr.csv, dtype{imei: str})這行里的dtype一旦忘記指定pandas 會(huì)自作主張把它讀成int64等到寫(xiě)回 CSV 時(shí)再丟一次精度。這種鏈條上的損耗最好在源頭就用VARCHAR切斷。我見(jiàn)過(guò)一個(gè)反例某團(tuán)隊(duì)把 IMEI 存成DOUBLE結(jié)果查詢時(shí)看到1.23456E14這類科學(xué)計(jì)數(shù)法所有關(guān)聯(lián)查詢?nèi)珡U。所以imei字段請(qǐng)無(wú)條件用VARCHAR(20)別給它任何參與數(shù)值運(yùn)算的機(jī)會(huì)。如果需要做設(shè)備級(jí)去重直接COUNT(DISTINCT imei)字符串去重對(duì)索引的壓力遠(yuǎn)小于超長(zhǎng)整數(shù)的類型轉(zhuǎn)換。4.2 時(shí)間字段Unix 時(shí)間戳與字符串的解析時(shí)間字段是另一個(gè)容易翻車的點(diǎn)。CSV 里最常見(jiàn)的有三種表示標(biāo)準(zhǔn)字符串2023-06-01 12:00:00、日期2023-06-01、Unix 時(shí)間戳秒或毫秒級(jí)別的整數(shù)。第一種直接落進(jìn)DATETIME列沒(méi)問(wèn)題MySQL 能自動(dòng)識(shí)別第二種落進(jìn)DATE列沒(méi)問(wèn)題第三種必須先轉(zhuǎn)換。-- 如果 CSV 里是 Unix 秒級(jí)時(shí)間戳導(dǎo)入時(shí)同步轉(zhuǎn)換 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);毫秒級(jí)時(shí)間戳要小心13 位的毫秒值直接FROM_UNIXTIME得到的是 1970 年附近的日期因?yàn)楹瘮?shù)默認(rèn)按秒解析。處理方法是先除以 1000FROM_UNIXTIME(ts / 1000)。這個(gè)坑我真實(shí)踩過(guò)導(dǎo)完才發(fā)現(xiàn)日期全在 1970 年整批數(shù)據(jù)重跑了一次。如果是2023/06/01這種斜杠分隔的字符串MySQL 的嚴(yán)格模式下會(huì)直接報(bào)錯(cuò)非嚴(yán)格模式下會(huì)寫(xiě)入0000-00-00。這就是為什么前面一直強(qiáng)調(diào)先head看幾行數(shù)據(jù)看到格式不對(duì)先統(tǒng)一處理再導(dǎo)。4.3 cell_info 關(guān)聯(lián)基站維度表讓數(shù)據(jù)可用cell_id列本身只是一個(gè)編號(hào)它對(duì)應(yīng)哪個(gè)小區(qū)、在哪個(gè)經(jīng)緯度、覆蓋哪個(gè)行政區(qū)通常不在這張匯總表里。想要讓數(shù)據(jù)真正可分析得維護(hù)一張基站維度表把小區(qū)編號(hào)翻譯成地理位置和行政歸屬。常見(jiàn)的做法是建一張dim_cell_base列結(jié)構(gòu)大致是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 小區(qū)標(biāo)識(shí), 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;維度表建好后查詢時(shí)用LEFT JOIN關(guān)聯(lián)比如統(tǒng)計(jì)某市某區(qū)每天活躍設(shè)備數(shù)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;如果聚合查詢頻繁且對(duì)響應(yīng)時(shí)間敏感可以把這個(gè) JOIN 的結(jié)果物化成一張帶行政區(qū)的明細(xì)表省得每次查都做一次幾億行的大 JOIN。但物化會(huì)帶來(lái)數(shù)據(jù)冗余和更新成本建議一開(kāi)始先用 JOIN等確認(rèn)了查詢模式再考慮物化。另外dim_cell_base里可能有部分cell_id匹配不上——這通常意味著上游數(shù)據(jù)里出現(xiàn)了新開(kāi)基站的cell_id維度表還沒(méi)更新。這類缺失要在月度維護(hù)窗口統(tǒng)一補(bǔ)齊而不是邊查邊補(bǔ)。5. 避坑導(dǎo)入這類 CSV 進(jìn) MySQL 的 5 個(gè)高頻翻車點(diǎn)5.1 7z 密碼正確但一直報(bào)錯(cuò)不是密碼問(wèn)題是版本和編碼現(xiàn)象用7z x解壓一個(gè)帶密碼的包密碼確認(rèn)無(wú)誤終端卻反復(fù)提示W(wǎng)rong password或者報(bào)Cannot open file as archive。原因分兩類一是解壓工具的版本太舊不支持壓縮方使用的 AES-256 高強(qiáng)度加密二是密碼里含中文或特殊字符終端 locale 是POSIX或C時(shí)輸入的字節(jié)流和壓縮時(shí)不一致。解決先升級(jí)p7zip到 16.02 以上版本再把終端環(huán)境變量切到 UTF-8執(zhí)行export LC_ALLC.UTF-8后重試。如果還有問(wèn)題換7z的 Windows 最新版試一次多半能解開(kāi)。5.2 導(dǎo)入后中文全是亂碼CSV 編碼與表字符集不一致現(xiàn)象SELECT查出來(lái)的中文全是???或方框。原因CSV 是 GBK 編碼建表和導(dǎo)入都寫(xiě)了utf8mb4數(shù)據(jù)在導(dǎo)入時(shí)被強(qiáng)制按 UTF-8 解析中文字節(jié)對(duì)不上。解決先f(wàn)ile -i確認(rèn)源文件編碼再做一次轉(zhuǎn)碼再導(dǎo)入用iconv -f GBK -t UTF-8 cdr.csv cdr_utf8.csv。轉(zhuǎn)碼后別忘了重新看一次表頭因?yàn)檗D(zhuǎn)換過(guò)程可能改變文件大小和末尾換行符LOAD DATA的LINES TERMINATED BY也要相應(yīng)調(diào)整。5.3 大 CSV 導(dǎo)入到一半斷開(kāi)兩個(gè)全局參數(shù)和一個(gè)分片技巧現(xiàn)象幾千萬(wàn)行的文件導(dǎo)到一半報(bào)MySQL server has gone away或Lost connection。原因單個(gè) LOAD DATA 事務(wù)太大超過(guò)了max_allowed_packet或者超過(guò)了net_write_timeout的默認(rèn)時(shí)間——導(dǎo)入太久連接被服務(wù)端斷開(kāi)。解決導(dǎo)入前調(diào)大這兩個(gè)參數(shù)SET GLOBAL max_allowed_packet 512 * 1024 * 1024; SET GLOBAL net_write_timeout 3600;但參數(shù)調(diào)大只是治標(biāo)。更穩(wěn)的通用做法是分片導(dǎo)入。用 Linux 的split命令把大文件拆成多個(gè)小文件每個(gè) 100 萬(wàn)行左右一個(gè)文件導(dǎo)一次哪個(gè)文件失敗就只重導(dǎo)哪個(gè)。# 每 100 萬(wàn)行切一個(gè)文件前綴 part_ split -l 1000000 cdr_summ_imei_cell_info.csv part_ # 對(duì)每個(gè) part 文件循環(huán)導(dǎo)入 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循環(huán)腳本注意兩點(diǎn)第一個(gè)文件要帶IGNORE 1 LINES跳過(guò)表頭后面的文件表頭已經(jīng)沒(méi)了要再帶IGNORE 1 LINES會(huì)把第一行真實(shí)數(shù)據(jù)丟掉。所以第一個(gè)文件單獨(dú)導(dǎo)其余文件不帶IGNORE。這個(gè)細(xì)節(jié)出錯(cuò)率高我建議直接寫(xiě)兩次命令而不是在循環(huán)里做區(qū)分。5.4 IMEI 變成科學(xué)計(jì)數(shù)法 1.23E14Excel 和 pandas 的雙重陷阱現(xiàn)象用 Excel 打開(kāi) CSVIMEI 列顯示1.23457E14精確數(shù)字全沒(méi)了。原因Excel 對(duì)超過(guò) 11 位的數(shù)字自動(dòng)轉(zhuǎn)科學(xué)計(jì)數(shù)法而且一旦保存原始精度無(wú)法恢復(fù)。解決不要在 Excel 里打開(kāi)這種原始數(shù)據(jù)文件。但這個(gè)坑還有一種隱蔽變體——如果你用 pandas 讀 CSV 后再寫(xiě)回 CSV沒(méi)指定dtype同樣會(huì)經(jīng)歷int64再轉(zhuǎn)字符串的過(guò)程超長(zhǎng)數(shù)字后半段變成 0。所以處理這類文件pd.read_csv(file, dtype{imei: str})是必寫(xiě)參數(shù)不是可選項(xiàng)。如果發(fā)現(xiàn)手頭的 CSV 已經(jīng)被 Excel 污染過(guò)IMEI 已經(jīng)是科學(xué)計(jì)數(shù)法文本那只能找上游要原始文件重導(dǎo)或者用小區(qū)編號(hào) 時(shí)間 其他字段做近似匹配但這屬于緊急修復(fù)結(jié)構(gòu)化數(shù)據(jù)里不建議這么搞。5.5 數(shù)據(jù)錯(cuò)位和行數(shù)對(duì)不上引號(hào)、逗號(hào)與行分隔符的隱藏問(wèn)題現(xiàn)象LOAD DATA導(dǎo)完SELECT COUNT(*)的行數(shù)比wc -l少了或者某一行的字段值串到了相鄰列。原因CSV 個(gè)別字段內(nèi)部暗藏了逗號(hào)、換行符或雙引號(hào)導(dǎo)致解析器把一行拆成了兩行或把一列拆成了兩列。解決把FIELDS TERMINATED BY ,改成帶引號(hào)識(shí)別的版本即FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY 。如果源文件是用 Python 的csv.writer生成的它默認(rèn)會(huì)把含特殊字符的字段用雙引號(hào)包起來(lái)這個(gè)參數(shù)正好能處理。如果是手拼的 CSV 或者從 Excel 另存的 CSV字段里的逗號(hào)可能沒(méi)被正確轉(zhuǎn)義那就得先清洗。遇到臟數(shù)據(jù)用awk -F, {print NF}統(tǒng)計(jì)每行列數(shù)找出列數(shù)異于表頭的行單獨(dú)處理。6. 從一個(gè)可導(dǎo)入的表到一個(gè)可查詢的庫(kù)聚合驗(yàn)證與增量更新6.1 用三條 SQL 驗(yàn)證數(shù)據(jù)到底能不能用數(shù)據(jù)導(dǎo)入 MySQL 只是第一步真正判斷數(shù)據(jù)是否可用要看它能不能穩(wěn)定回答業(yè)務(wù)問(wèn)題。我的驗(yàn)證套路是三條 SQL 并行跑按天看設(shè)備數(shù)、按小區(qū)看呼叫量、抽樣看單個(gè)設(shè)備的行為軌跡。-- 按日活躍設(shè)備數(shù)驗(yàn)證時(shí)間維度的覆蓋度 SELECT stat_date, COUNT(DISTINCT imei) AS active_devices FROM cdr_summ_imei_cell_info GROUP BY stat_date ORDER BY stat_date; -- 按小區(qū)看呼叫總量驗(yàn)證空間維度的聚合能力 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; -- 抽查一個(gè)設(shè)備的跨小區(qū)移動(dòng)軌跡 SELECT stat_date, cell_id, lac_id FROM cdr_summ_imei_cell_info WHERE imei 861234567890123 ORDER BY stat_date;這三條 SQL 分別驗(yàn)證了時(shí)間、空間、設(shè)備三個(gè)維度的可用性。如果某一天設(shè)備數(shù)驟降到 0大概率是當(dāng)天數(shù)據(jù)缺失如果某個(gè)小區(qū)的呼叫量異常高可能是同名小區(qū) ID 撞了如果單個(gè)設(shè)備的軌跡出現(xiàn)“瞬間跨省”那就要檢查是不是 IMEI 被多設(shè)備復(fù)用或數(shù)據(jù)源本身有噪聲。6.2 小區(qū)級(jí)、設(shè)備級(jí)的聚合查詢模板數(shù)據(jù)驗(yàn)證通過(guò)后業(yè)務(wù)查詢就有了固定模板。區(qū)域人流分析最常用的是“按日按小區(qū)統(tǒng)計(jì)活躍設(shè)備數(shù)”設(shè)備畫(huà)像最常用的是“單個(gè)設(shè)備的時(shí)間-小區(qū)序列表”。前者適合用 GROUP BY 配合組合索引后者適合用單個(gè) IMEI 做等值查詢。-- 按日按小區(qū)統(tǒng)計(jì)活躍設(shè)備數(shù)和人均呼叫次數(shù) 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;如果聚合查詢經(jīng)常按照這個(gè)模板跑可以把這個(gè)匯總結(jié)果物化成一張小表agg_cell_daily每天由定時(shí)任務(wù)刷新前端查詢走小表響應(yīng)時(shí)間能壓到百毫秒級(jí)別。物化表不需要保留全部字段只需要stat_date、cell_id、active_devices、total_calls、total_dur這幾列體積比明細(xì)表小兩個(gè)數(shù)量級(jí)維護(hù)成本很低。6.3 用導(dǎo)入日志表實(shí)現(xiàn)增量的可重跑機(jī)制這種 CSV 數(shù)據(jù)包往往是按周期交付的比如每天凌晨生成前一天的匯總文件。如果每次都用LOAD DATA直接往正式表里插一旦同一個(gè)文件被重復(fù)執(zhí)行就會(huì)產(chǎn)生重復(fù)行。我的做法是建一張導(dǎo)入日志表每次導(dǎo)入前先查這個(gè)文件是否處理過(guò)處理過(guò)就跳過(guò)形成一個(gè)天然的冪等機(jī)制。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 );導(dǎo)入流程變成三步算文件的 MD5 → 查cdr_import_log里有沒(méi)有同 MD5 的記錄 → 沒(méi)有才執(zhí)行LOAD DATA然后向日志表插一條記錄。這樣即使腳本被調(diào)度系統(tǒng)重跑或者有人手動(dòng)重復(fù)執(zhí)行數(shù)據(jù)也不會(huì)重復(fù)入庫(kù)。如果發(fā)現(xiàn)某個(gè)文件導(dǎo)壞了刪除數(shù)據(jù)時(shí)也先查日志定位到具體文件和導(dǎo)入時(shí)間處理起來(lái)不會(huì)大海撈針。數(shù)據(jù)導(dǎo)入這件事做得多了之后我對(duì)任何數(shù)據(jù)包的第一反應(yīng)都是先看結(jié)構(gòu)再做操作從不在沒(méi)看清表頭、沒(méi)確認(rèn)編碼、沒(méi)記錄行數(shù)的情況下直接灌庫(kù)。灌庫(kù)后也一定留一手校驗(yàn)和日志因?yàn)閷幙陕昼娨膊灰谝粋€(gè)臟數(shù)據(jù)上重跑一整夜。希望這些流程和坑能幫你在自己的環(huán)境里少走一圈彎路。本文還有配套的精品資源點(diǎn)擊獲取