DuckDB สำหรับการวิเคราะห์เฉพาะกิจ: เปลี่ยน CSV หลายพันไฟล์เป็นแดชบอร์ด

Amazon Seller Central สร้างข้อมูลเร็วกว่าที่เครื่องมือวิเคราะห์ส่วนใหญ่รองรับได้ รายงานการเข้าชม ข้อมูลอัตราคอนเวอร์ชัน สรุปสถานะสินค้าคงคลัง รายงานผลธุรกิจ และรายละเอียดแคมเปญโฆษณาแต่ละรายการส่งมาเป็นไฟล์ CSV หรือ TSV สะสมตลอดหลายเดือนและหลายปีจนแต่ละไดเรกทอรีมีไฟล์หลายพันไฟล์ วิธีปกติคือนำทุกอย่างเข้าฐานข้อมูล แต่การตั้งค่า ดูแล และค้นผ่าน PostgreSQL สำหรับงานวิเคราะห์ที่อ่านเป็นหลักและโครงสร้างเปลี่ยนได้กลับสร้างภาระในการดำเนินงานมากเกินความจำเป็น

เราสร้างไปป์ไลน์รายงานให้ผู้ขาย Amazon ที่ขายในมาร์เก็ตเพลซยุโรปห้าแห่ง ข้อมูลดิบคือไฟล์ส่งออกจาก Seller Central หกปี จำนวน 4,200 ไฟล์ ครอบคลุมรายงานธุรกิจ ผลโฆษณา สแนปช็อตสินค้าคงคลัง และบัญชีค่าธรรมเนียม FBA แนวทางแรกใช้สคีมา PostgreSQL กับสคริปต์ ETL ใช้เวลาสามสัปดาห์กว่าจะเสถียรและต้องดูแลต่อเนื่องเมื่อ Amazon เปลี่ยนชื่อคอลัมน์และรูปแบบรายงานตามเวอร์ชัน API การค้นข้อมูลเร็ว แต่งานบำรุงรักษาไม่ใช่

DuckDB เปลี่ยนการประเมินความคุ้มค่านี้ เป็นฐานข้อมูลวิเคราะห์แบบฝังตัวที่ทำงานในโปรเซสเดียวกับโค้ดวิเคราะห์ ไม่ต้องมีเซิร์ฟเวอร์ โปรเซสเบื้องหลัง หรือสตริงเชื่อมต่อ และอ่านไฟล์จากดิสก์โดยตรง ใช้ SQL มาตรฐานและเอนจินแบบคอลัมน์ที่ออกแบบเพื่อรวมข้อมูลปริมาณมาก สำหรับงานนี้ DuckDB ไม่ได้แค่พอใช้งานได้ แต่เป็นเครื่องมือที่เหมาะสม

DuckDB คืออะไรกันแน่

ความต่างสำคัญคือการจัดเก็บตามคอลัมน์กับตามแถว PostgreSQL เก็บทุกฟิลด์ของแต่ละระเบียนต่อเนื่องบนดิสก์ เมื่อใช้ SELECT revenue, units_sold FROM orders WHERE marketplace = 'DE' การสแกนแบบลำดับอ่านทุกฟิลด์ของแต่ละแถวที่สแกน แล้วคืนเฉพาะสองฟิลด์ที่ขอ ดัชนี รวมถึงการสแกนเฉพาะดัชนี ช่วยจำกัดการอ่านได้ แต่แทบไม่ช่วยในการรวมข้อมูลที่ครอบคลุมส่วนใหญ่ของตาราง เหมาะกับธุรกรรมที่เพิ่ม แก้ หรือดึงระเบียนทีละรายการ แต่ไม่เหมาะกับการวิเคราะห์ที่รวมหลายล้านแถวโดยใช้เพียงบางคอลัมน์

DuckDB จัดข้อมูลตามคอลัมน์ ค่าของ revenue อยู่ด้วยกัน เช่นเดียวกับ units_sold คำสั่งค้นหาเดียวกันอ่านเฉพาะสามคอลัมน์ที่ต้องใช้ (สองคอลัมน์ที่คืนค่าและ marketplace สำหรับตัวกรอง)และข้ามที่เหลือ บนชุดข้อมูลสี่สิบคอลัมน์ อาจลด I/O ได้ราว 90 เปอร์เซ็นต์หรือมากกว่าสำหรับคำสั่งค้นหาที่ใช้เพียงไม่กี่คอลัมน์ เมื่อรวมกับการประมวลผลแบบเวกเตอร์เป็นชุด 2,048 ค่าตามค่าเริ่มต้นโดยใช้คำสั่ง SIMD แทนทีละแถว จึงอาจเร็วกว่า PostgreSQL ที่ปรับแต่งดีบนฮาร์ดแวร์เดียวกัน โดยเฉพาะเมื่อใช้รูปแบบข้อมูลตามคอลัมน์ เช่น Parquet

สถาปัตยกรรมแบบฝังตัวก็สำคัญไม่แพ้กัน ไม่มีเวลาแฝงจากการรับส่งข้อมูลผ่านเครือข่าย ภาระจากการยืนยันตัวตน หรือพูลการเชื่อมต่อให้ดูแล DuckDB เปิดไฟล์หรือไดเรกทอรี วางแผนคำสั่งค้นหา แล้วประมวลผล ไบนารีเล็กกว่า 100MB ใช้บนแล็ปท็อป ในฟังก์ชัน Lambda หรือสคริปต์ Python ที่เรียกจาก cron ได้ ทำให้ภาระดูแลไปป์ไลน์วิเคราะห์ที่ติดตั้งเองต่ำมาก

SQL โดยตรงบนไฟล์

คุณสมบัติที่เหมาะกับข้อมูล Seller Central คือการสแกนไฟล์ตามรูปแบบ glob คำสั่ง SQL เดียวค้น CSV หลายพันไฟล์พร้อมกันได้:

SELECT
  date_trunc('month', "Date") AS month,
  SUM("Units Ordered")          AS units,
  SUM("Ordered Product Sales")  AS revenue
FROM read_csv_auto('/data/seller-central/business-reports/*.csv')
GROUP BY 1
ORDER BY 1;

read_csv_auto อนุมานสคีมาจากไฟล์ด้วยการสุ่มอ่านแถว ตรวจชนิดคอลัมน์ จัดการตัวคั่นต่างกัน และเมื่อใช้ union_by_name=true จะรวมคอลัมน์ตามชื่อข้ามไฟล์ที่มีชุดคอลัมน์ต่างกัน (ค่าเริ่มต้นใช้สคีมาของไฟล์แรกและจับคู่ไฟล์ถัดไปตามตำแหน่งคอลัมน์) ฟังก์ชันนี้ไม่จับคู่คอลัมน์ที่เปลี่ยนชื่อให้เอง จึงยังต้องใช้ชื่อแทนหรือ COALESCE ในคำสั่งค้นหา สิ่งนี้สำคัญเมื่อ Amazon เปลี่ยนชื่อคอลัมน์อย่างน้อยสี่ครั้งในสามปี คำสั่งค้นหาใช้ไฟล์ที่มีอยู่ขณะประมวลผล โดยไม่ต้องนำเข้า ย้ายสคีมา หรือล้างแคชเก่า

สำหรับไฟล์ TSV ที่ส่งออกจากคอนโซลโฆษณา ใช้แนวทางเดียวกันได้โดยระบุรูปแบบให้ชัดเจน:

FROM read_csv('/data/advertising/*.tsv', delim='	', header=true)

คุณสมบัตินี้ไม่ได้ให้เพียงความสะดวก แต่เปลี่ยนวิธีคิดในการทำงานกับข้อมูลในไฟล์ ให้ระบบไฟล์ทำหน้าที่เป็นฐานข้อมูล อนุมานสคีมาแทนการประกาศไว้ล่วงหน้า และใช้คำสั่งค้นหาข้ามไฟล์โดยไม่ต้องประมวลผลข้อมูลล่วงหน้า สำหรับการวิเคราะห์เฉพาะกิจที่คำถามเปลี่ยนเร็วกว่าจะปรับไปป์ไลน์ ETL ตามได้ สถาปัตยกรรมนี้เป็นทางเลือกเริ่มต้นที่เหมาะสม

คุณสมบัติด้านประสิทธิภาพ

ชุดข้อมูลอ้างอิง 4,200 ไฟล์ ขนาดประมาณ 2.8GB ก่อนบีบอัด ใช้เวลา 4.1 วินาทีรวมรายได้ทั้งหมดตามมาร์เก็ตเพลซ ASIN และเดือนบนแล็ปท็อป Apple M2 ที่มี RAM 16GB PostgreSQL ใช้ 2.8 วินาทีเมื่อข้อมูลนำเข้าและจัดทำดัชนีแล้ว แต่ไม่รวมการนำเข้าเริ่มต้นสี่สิบนาที การย้ายสคีมาทุกครั้งที่ Amazon เปลี่ยนชื่อคอลัมน์ และค่าดูแล ETL เมื่อคิดต้นทุนดำเนินงานรวม DuckDB เหนือกว่าสำหรับงานนี้

หากประสิทธิภาพการค้นหากลายเป็นคอขวด ซึ่งมักเกิดเมื่อข้อมูลมีขนาดหลายเทราไบต์ DuckDB อ่าน Parquet ได้โดยตรง และการแปลงไฟล์ CSV ที่มีอยู่เป็น Parquet ทำเพียงครั้งเดียว:

COPY (FROM read_csv_auto('/data/**/*.csv'))
TO '/data/consolidated.parquet'
(FORMAT PARQUET);

การค้นหาภายหลังเร็วขึ้น 8 ถึง 12 เท่า Parquet เก็บค่าต่ำสุดและสูงสุดของคอลัมน์ในแต่ละกลุ่มแถว จึงข้ามข้อมูลทั้งส่วนได้ ตัวกรอง marketplace = 'DE' บน Parquet ที่เรียงแล้วอาจอ่านข้อมูลไม่ถึงสิบเปอร์เซ็นต์ การส่งเงื่อนไขกรองไปประมวลผลที่แหล่งข้อมูล (predicate pushdown) เช่นนี้ใช้กับ CSV แบบแบนไม่ได้

การสร้างแดชบอร์ดที่เปิดได้เองด้วย Chart.js

ผลลัพธ์ของคำสั่งค้นหา DuckDB คือข้อมูล คำถามในทางปฏิบัติคือจะนำไปทำอะไรต่อ สำหรับรายงานภายในของธุรกิจขายบน Amazon ขนาดเล็กถึงกลาง ไฟล์ HTML ที่เปิดได้เองและใช้ Chart.js มักมีประโยชน์กว่าแดชบอร์ดที่โฮสต์บนเซิร์ฟเวอร์ เพราะไม่ต้องรันเซิร์ฟเวอร์ ดูแลข้อมูลเข้าสู่ระบบ หรือบำรุงรักษาอินสแตนซ์ Superset หรือ Metabase

ไปป์ไลน์มีสามขั้นตอน ขั้นแรก DuckDB ส่งออกผลการค้นหาเป็น JSON:

COPY (
  SELECT
    strftime(date_trunc('week', "Date"), '%Y-%m-%d') AS week,
    SUM("Units Ordered")         AS units,
    SUM("Ordered Product Sales") AS revenue
  FROM read_csv_auto('/data/**/*.csv')
  GROUP BY 1 ORDER BY 1
) TO 'weekly.json' (FORMAT JSON, ARRAY true);

ขั้นที่สอง สคริปต์ Python อ่านไฟล์ JSON เหล่านั้นและฝังข้อมูลลงในเทมเพลต HTML ที่เรียก Chart.js ผ่าน CDN โดยตรง ขั้นที่สาม เปิดไฟล์ HTML ในเบราว์เซอร์ได้โดยไม่ต้องมีเว็บเซิร์ฟเวอร์

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Seller Dashboard</title>
  <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<body>
  <canvas id="revenue"></canvas>
  <script>
    const rows = /* JSON_DATA_HERE */;
    new Chart(document.getElementById('revenue'), {
      type: 'line',
      data: {
        labels: rows.map(r => r.week),
        datasets: [{ label: 'Revenue', data: rows.map(r => r.revenue), tension: 0.3 }]
      }
    });
  </script>
</body>
</html>

ผลลัพธ์คือไฟล์ HTML ไฟล์เดียว (โหลด Chart.js จาก CDN ผู้เปิดดูจึงต้องเข้าถึงอินเทอร์เน็ตได้ เว้นแต่จะฝังไลบรารีลงในไฟล์โดยตรง) ส่งทางอีเมล เก็บใน S3 bucket ส่วนตัว หรือเปิดจากไดเรกทอรีในเครื่องได้ สำหรับทีมปฏิบัติการที่ต้องการรายงานรายสัปดาห์โดยไม่มีภาระดูแลโครงสร้างพื้นฐานแดชบอร์ดออนไลน์ แนวทางนี้ใช้งานได้จริงและดูแลรักษาง่าย สร้างแดชบอร์ดใหม่ด้วยการรันสคริปต์เชลล์: DuckDB ค้นข้อมูลจาก CSV แล้ว Python สร้าง HTML ก็เสร็จ ไม่ต้องมีโครงสร้างพื้นฐานเซิร์ฟเวอร์หรือต้นทุนโฮสติ้งประจำ และมีเพียงคำสั่งค้นหากับสคริปต์เทมเพลตให้บำรุงรักษา

เมื่อใด DuckDB เหมาะกว่า PostgreSQL

DuckDB ไม่ได้มาแทน PostgreSQL การเปรียบเทียบมีความหมายเฉพาะงานวิเคราะห์จริงที่สแกน รวม และจัดกลุ่มข้อมูลปริมาณมาก โดยอ่านมากกว่าเขียน PostgreSQL เหนือกว่าชัดเจนสำหรับการเขียนพร้อมกัน ความถูกต้องของความสัมพันธ์ระหว่างข้อมูล การควบคุมสิทธิ์เข้าถึงระดับแถว และข้อกำหนดด้านการดำเนินงานทั้งหมดของแอปในสภาพแวดล้อมใช้งานจริง จึงเป็นตัวเลือกที่ถูกต้องสำหรับระบบหลายผู้ใช้ที่โค้ดแอปเพิ่ม แก้ไข และค้นหาระเบียน

DuckDB เหมาะกว่าเมื่อ:

ข้อมูลมีอยู่แล้วเป็นไฟล์บนดิสก์ และไม่ได้วางแผนขั้นตอนนำเข้า
ยังไม่ทราบสคีมาทั้งหมดล่วงหน้า หรือสคีมาเปลี่ยนบ่อย
คำสั่งค้นหาเป็นแบบเฉพาะกิจและคาดรูปแบบการเข้าถึงล่วงหน้าไม่ได้
อายุหรือขนาดโครงการไม่คุ้มกับภาระดูแลเซิร์ฟเวอร์ฐานข้อมูล

ข้อมูล Amazon Seller Central ตรงตามเกณฑ์ทั้งสี่ข้ออย่างพอดี สคีมาเปลี่ยนทุกครั้งที่ Seller Central ออกรุ่นใหม่ ไฟล์มีอยู่ในเครื่องแล้วหลังส่งออก คำถามทางธุรกิจเปลี่ยนทุกสัปดาห์ตามสิ่งที่กำลังปรับปรุง ส่วนทางเลือกอย่างไปป์ไลน์ ETL ของ PostgreSQL ที่ต้องดูแลต่อเนื่อง ใช้เวลาวิศวกรรมหลายสัปดาห์กับปัญหาที่ DuckDB แก้ได้ภายในบ่ายเดียว

หลักปฏิบัติคือ หากปกติคุณจะใช้ Excel หรือ Jupyter notebook วิเคราะห์ข้อมูลนี้ ให้ใช้ DuckDB แทน SQL แสดงคำถามวิเคราะห์ได้ยืดหยุ่นกว่า โดยทั่วไปประสิทธิภาพสูงกว่ามากเมื่อข้อมูลเกินไม่กี่พันแถว และสถาปัตยกรรมที่ใช้ไฟล์ทำให้ข้อมูลอยู่ในที่เดิม