在旧笔记本上分析2亿OnTime数据

有没有想过在一台小笔记本上处理和分析上亿行的数据？ 网上有很多关于OnTime数据的测试报告，今天让我们也尝试下DuckDB的，如果你想在Windows或者Linux下做测试，可以告诉我你的结果吗？

硬件情况

2014 年购入的 MacBook Pro，

• 4 核 2.8G i7

• 16G 内存

• 1T SSD

数据来源

美国从 1987 年至今持续更新的的民航数据^[1]， 2 亿条多些。

准备工作

• 安装好 Jupyter Lab
• 安装必要的包

pip install duckdb duckdb-engine ipython-sql ipython-autotime

• 下载 ontime 数据

wget --no-check-certificate --c \https://transtats.bts.gov/PREZIP/On_Time_Reporting_Carrier_On_Time_Performance_1987_present_{1987..2021}_{1..12}.zip

为了简化下工作，这里我下载了一个合并好的包，

wget --no-check-certificate -c \https://repo.databend.rs/t_ontime/t_ontime.csv.zip

为啥要转成 Parquet？

想较多了解 Parquet 的可以看看我之前的一篇文章什么是 Parquet 文件格式以及为什么要使用它。

或者直接看个最简单的对比

• 使用 DuckDB 查询 csv.gz

  select count(*) from read_csv_auto('sample.csv.gz',delim='\t') count_star()0 2008064time: 7.82 s (started: 2022-04-11 16:02:55 +08:00)
  

• 查询 Parquet 格式

  select count(*) from 'ontime.parquet' count_star()0 202687655time: 513 ms (started: 2022-04-11 16:05:18 +08:00)
  

转 Parquet

zip 文件大概 7G, encoding 为 ascii， 因为含有拉丁字符，需要做进一步转换, 转为 gzip 格式，方便 DuckDB 转为 Parquet(snappy 压缩， 11G） 格式，

unzip -p t_ontime.csv.zip|iconv -f latin1 -t utf-8|gzip >t_ontime.csv.gz

本文的查询都仅仅为 SQL 语句，因此可以直接在 DuckDB 命令行下执行，也可以在 Jupyter Notebook 里执行， 我为了做笔记方便，都放到了 Notebook 中。

• Notebook 导入必要的包和进行必要的设置，

import warnings; warnings.filterwarnings("ignore")%load_ext autotime%load_ext sql%sql duckdb:///%config SqlMagic.autopandas=True%config SqlMagic.feedback = False%config SqlMagic.displaycon = False%sql pragma threads=4#导入必要的包import pandas as pdimport numpy as np

• 字段与类型定义

columns = {'Year':'UInt16','Quarter':'UInt8','Month':'UInt8','DayofMonth':'UInt8','DayOfWeek':'UInt8','FlightDate':'Date','Reporting_Airline':'String','DOT_ID_Reporting_Airline':'Int32','IATA_CODE_Reporting_Airline':'String','Tail_Number':'String','Flight_Number_Reporting_Airline':'String','OriginAirportID':'Int32','OriginAirportSeqID':'Int32','OriginCityMarketID':'Int32','Origin':'String','OriginCityName':'String','OriginState':'String','OriginStateFips':'String','OriginStateName':'String','OriginWac':'Int32','DestAirportID':'Int32','DestAirportSeqID':'Int32','DestCityMarketID':'Int32','Dest':'String','DestCityName':'String','DestState':'String','DestStateFips':'String','DestStateName':'String','DestWac':'Int32','CRSDepTime':'Int32','DepTime':'Int32','DepDelay':'Int32','DepDelayMinutes':'Int32','DepDel15':'Int32','DepartureDelayGroups':'String','DepTimeBlk':'String','TaxiOut':'Int32','WheelsOff':'Int32','WheelsOn':'Int32','TaxiIn':'Int32','CRSArrTime':'Int32','ArrTime':'Int32','ArrDelay':'Int32','ArrDelayMinutes':'Int32','ArrDel15':'Int32','ArrivalDelayGroups':'Int32','ArrTimeBlk':'String','Cancelled':'UInt8','CancellationCode':'String','Diverted':'UInt8','CRSElapsedTime':'Int32','ActualElapsedTime':'Int32','AirTime':'Int32','Flights':'Int32','Distance':'Int32','DistanceGroup':'UInt8','CarrierDelay':'Int32','WeatherDelay':'Int32','NASDelay':'Int32','SecurityDelay':'Int32','LateAircraftDelay':'Int32','FirstDepTime':'String','TotalAddGTime':'String','LongestAddGTime':'String','DivAirportLandings':'String','DivReachedDest':'String','DivActualElapsedTime':'String','DivArrDelay':'String','DivDistance':'String','Div1Airport':'String','Div1AirportID':'Int32','Div1AirportSeqID':'Int32','Div1WheelsOn':'String','Div1TotalGTime':'String','Div1LongestGTime':'String','Div1WheelsOff':'String','Div1TailNum':'String','Div2Airport':'String','Div2AirportID':'Int32','Div2AirportSeqID':'Int32','Div2WheelsOn':'String','Div2TotalGTime':'String','Div2LongestGTime':'String','Div2WheelsOff':'String','Div2TailNum':'String','Div3Airport':'String','Div3AirportID':'Int32','Div3AirportSeqID':'Int32','Div3WheelsOn':'String','Div3TotalGTime':'String','Div3LongestGTime':'String','Div3WheelsOff':'String','Div3TailNum':'String','Div4Airport':'String','Div4AirportID':'Int32','Div4AirportSeqID':'Int32','Div4WheelsOn':'String','Div4TotalGTime':'String','Div4LongestGTime':'String','Div4WheelsOff':'String','Div4TailNum':'String','Div5Airport':'String','Div5AirportID':'Int32','Div5AirportSeqID':'Int32','Div5WheelsOn':'String','Div5TotalGTime':'String','Div5LongestGTime':'String','Div5WheelsOff':'String','Div5TailNum':'String',}

• csv.gz to Parquet,

copy(select * from read_csv_auto('t_ontime.csv.gz',delim='\t', columns={columns}))to 'ontime.parquet'

• 创建视图（为了后面 SQL 更简洁）

create or replace view ontime as select * from 'ontime.parquet'

SQL benchmark

• Q0. 1.39s

  SELECT avg(c1)FROM(    SELECT Year, Month, count(*) AS c1    FROM ontime    GROUP BY Year, Month);
  

• Q1. 查询从 2000 年到 2008 年每天的航班数,860ms

  SELECTDayOfWeek, count(*) AS cFROM ontimeWHERE Year >= 2000AND Year <= 2008GROUP BY DayOfWeekORDER BY c DESC;
  

• Q2. 查询从 2000 年到 2008 年每周延误超过 10 分钟的航班数。1.26s

   SELECT DayOfWeek, count(*) AS cFROM ontimeWHERE DepDelay>10AND Year >= 2000 AND Year <= 2008GROUP BY DayOfWeekORDER BY c DESC;
  

• Q3.查询 2000 年到 2008 年每个机场延误超过 10 分钟以上的次数 1.45s

  SELECT Origin, count(*) AS cFROM ontimeWHERE DepDelay>10 AND Year>=2000 AND Year<=2008GROUP BY OriginORDER BY c DESCLIMIT 10;
  

• Q4. 查询 2007 年各航空公司延误超过 10 分钟以上的次数 572ms

  SELECT IATA_CODE_Reporting_Airline AS Carrier, count(*)FROM ontimeWHERE DepDelay>10 AND Year=2007GROUP BY CarrierORDER BY count(*) DESC;
  

• Q5. 查询 2007 年各航空公司延误超过 10 分钟以上的百分比 1.17s

  SELECT qq.Carrier, c, c2, c*100/c2 as c3FROM(    SELECT        IATA_CODE_Reporting_Airline AS Carrier,        count(*) AS c    FROM ontime    WHERE DepDelay>10        AND Year=2007    GROUP BY Carrier) qJOIN(    SELECT        IATA_CODE_Reporting_Airline AS Carrier,        count(*) AS c2    FROM ontime    WHERE Year=2007    GROUP BY Carrier) qq on  q.Carrier = qq.CarrierORDER BY c3 DESC;
  

• Q5 优化版本, 622ms

  SELECT IATA_CODE_Reporting_Airline AS Carrier, avg(cast(DepDelay>10 as Int8))*100 AS c3FROM ontimeWHERE Year=2007GROUP BY CarrierORDER BY c3 DESC;
  

• Q6. 同上一个查询一致,只是查询范围扩大到 2000 年到 2008 年 2.82s

  SELECT q.Carrier, c, c2, c*100/c2 as c3FROM(    SELECT        IATA_CODE_Reporting_Airline AS Carrier,        count(*) AS c    FROM ontime    WHERE DepDelay>10        AND Year>=2000 AND Year<=2008    GROUP BY Carrier) qJOIN(    SELECT        IATA_CODE_Reporting_Airline AS Carrier,        count(*) AS c2    FROM ontime    WHERE Year>=2000 AND Year<=2008    GROUP BY Carrier) qq on q.Carrier=qq.CarrierORDER BY c3 DESC;
  

• Q6 优化版本 1.57s

  SELECT IATA_CODE_Reporting_Airline AS Carrier, avg(cast(DepDelay>10 as Int8))*100 AS c3FROM ontimeWHERE Year>=2000 AND Year <=2008GROUP BY CarrierORDER BY c3 DESC;
  

• Q7. 每年航班延误超过 10 分钟的百分比 1.54s

  SELECT Year, avg(cast(DepDelay>10 as Int8))*100FROM ontimeGROUP BY YearORDER BY Year;
  

• Q8. 每年更受人们喜爱的目的地 9.92s

  SELECT DestCityName, count(distinct OriginCityName) AS uFROM ontimeWHERE Year >= 2000 and Year <= 2010GROUP BY DestCityNameORDER BY u DESC LIMIT 10;
  

• Q8 优化下， 4.17s

  SELECT DestCityName,count(OriginCityName) as u from(SELECT DestCityName, OriginCityNameFROM ontime
  WHERE Year >= 2000 and Year <= 2010
  GROUP BY DestCityName, OriginCityName)
  GROUP BY DestCityName
  ORDER BY u DESC LIMIT 10;
  
  

• Q9. 910ms

  SELECT Year, count(*) AS c1
  FROM ontime
  GROUP BY Year;
  
  

• Q10. 9.12s

  SELECT
     min(Year), max(Year), IATA_CODE_Reporting_Airline AS Carrier, count(*) AS cnt,
     sum(cast(ArrDelayMinutes>30 as Int8)) AS flights_delayed,
     round(sum(cast(ArrDelayMinutes>30 as Int8))/count(*),2) AS rate
  FROM ontime
  WHERE
     DayOfWeek NOT IN (6,7) AND OriginState NOT IN ('AK', 'HI', 'PR', 'VI')
     AND DestState NOT IN ('AK', 'HI', 'PR', 'VI')
     AND FlightDate < '2010-01-01'
  GROUP by Carrier
  HAVING count(*)>100000 and max(Year)>1990
  ORDER by rate DESC
  LIMIT 1000;
  
  

这个速度你还满意吧？

有兴趣的可以尝试使用 ClickHouse 导入该数据，并做个对比测试，反正我的电脑，经过一番折腾后，风扇狂转，内存溢出，然后我放弃了。

从上面的效果看，如果配合Superset， 是不是可以做到单机轻松分析上亿数据（并且跨平台支持）。

参考资料