Next.jsにおけるDuckDB-Wasm: 1000万行に対する超高速クライアントサイド分析

npm install @duckdb/duckdb-wasm @duckdb/duckdb-wasm-shell react-chartjs-2 chart.js
npm install -D worker-loader # For Next.js custom webpack config

// next.config.mjs
import { fileURLToPath } from 'url';
import { dirname } from 'path';

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

/** @type {import('next').NextConfig} */
const nextConfig = {
  webpack: (config, { isServer, webpack }) => {
    // DuckDB-Wasm requires specific asset handling
    config.experiments = { ...config.experiments,
      asyncWebAssembly: true,
      topLevelAwait: true,
      layers: true
    };

    // Configure worker-loader for our DuckDB worker
    config.module.rules.unshift({
      test: /\.worker\.ts$/,
      loader: 'worker-loader',
      options: {
        filename: 'static/[hash].worker.js', // Output worker to static directory
        publicPath: '/_next/', // Ensure correct public path for worker
      },
    });

    // DuckDB-Wasm specific configuration for WASM files
    config.module.rules.push({
      test: /\.wasm$/,
      type: 'asset/resource',
      generator: {
        filename: 'static/wasm/[name].[hash][ext]',
      },
    });

    // Ensure DuckDB-Wasm can find its worker files
    config.plugins.push(
      new webpack.DefinePlugin({
        'process.env.DUCKDB_RUNTIME_URL': JSON.stringify('/_next/static/wasm/duckdb-mvp.wasm'),
        'process.env.DUCKDB_RUNTIME_WORKER_URL': JSON.stringify('/_next/static/wasm/duckdb-browser-mvp.worker.js'),
      })
    );

    return config;
  },
};

export default nextConfig;

// src/workers/duckdb.worker.ts
import * as duckdb from '@duckdb/duckdb-wasm';
import { AsyncDuckDB, DuckDBDataProtocol, ConsoleLogger } from '@duckdb/duckdb-wasm';

// Define the DuckDB-Wasm bundle
const DUCKDB_BUNDLES = {
  mvp: {
    mainModule: '/_next/static/wasm/duckdb-mvp.wasm',
    mainWorker: '/_next/static/wasm/duckdb-browser-mvp.worker.js',
  },
  eh: {
    mainModule: '/_next/static/wasm/duckdb-eh.wasm',
    mainWorker: '/_next/static/wasm/duckdb-browser-eh.worker.js',
  },
};

let db: AsyncDuckDB | null = null;
let conn: duckdb.AsyncDuckDBConnection | null = null;

// Function to initialize DuckDB
async function initializeDuckDB() {
  if (db) return; // Already initialized

  const bundle = await duckdb.selectBundle(DUCKDB_BUNDLES);
  const worker = new Worker(bundle.mainWorker!);

  // Use the custom logger to see DuckDB logs in the worker console
  db = new AsyncDuckDB(new ConsoleLogger(), worker);
  await db.instantiate(bundle.mainModule, bundle.pthreadWorker);
  conn = await db.connect();
  console.log('DuckDB-Wasm initialized in worker.');
}

// Handle messages from the main thread
self.onmessage = async (event: MessageEvent) => {
  const { id, type, payload } = event.data;

  try {
    if (type === 'INIT') {
      await initializeDuckDB();
      self.postMessage({ id, type: 'INIT_SUCCESS' });
    } else if (type === 'QUERY') {
      if (!conn) throw new Error('DuckDB not initialized.');
      const { query, params } = payload;
      console.log(`Executing query: ${query}`);

      // Example: Register a Parquet file if needed
      // For this example, we assume the Parquet file is already accessible via HTTP
      // or registered in a previous step.
      // If you need to register a file from a URL:
      // await db.registerFileURL('my_data.parquet', payload.parquetUrl, DuckDBDataProtocol.HTTP, false);

      const result = await conn.query(query, params);
      self.postMessage({ id, type: 'QUERY_SUCCESS', payload: result.toArray() });
    } else if (type === 'REGISTER_PARQUET_URL') {
      if (!db) throw new Error('DuckDB not initialized.');
      const { name, url } = payload;
      await db.registerFileURL(name, url, DuckDBDataProtocol.HTTP, false);
      console.log(`Registered Parquet file: ${name} from ${url}`);
      self.postMessage({ id, type: 'REGISTER_PARQUET_URL_SUCCESS' });
    } else {
      throw new Error(`Unknown message type: ${type}`);
    }
  } catch (error: any) {
    console.error('DuckDB Worker Error:', error);
    self.postMessage({ id, type: 'ERROR', payload: error.message });
  }
};

// src/hooks/useDuckDB.ts
import { useEffect, useState, useRef, useCallback } from 'react';
import { v4 as uuidv4 } from 'uuid';

// Define message types for communication
interface WorkerMessage {
  id: string;
  type: string;
  payload?: any;
}

interface QueryResult {
  [key: string]: any;
}

export function useDuckDB() {
  const [isReady, setIsReady] = useState(false);
  const [error, setError] = useState<string | null>(null);
  const workerRef = useRef<Worker | null>(null);
  const messageCallbacks = useRef<Map<string, (result: any) => void>>(new Map());

  useEffect(() => {
    // Dynamically import the worker to avoid issues with Next.js SSR
    // This assumes your worker is built by webpack and accessible via a URL
    // The `worker-loader` plugin handles this.
    workerRef.current = new Worker(new URL('../workers/duckdb.worker.ts', import.meta.url));

    const worker = workerRef.current;

    worker.onmessage = (event: MessageEvent<WorkerMessage>) => {
      const { id, type, payload } = event.data;
      if (type === 'INIT_SUCCESS') {
        setIsReady(true);
      } else if (type === 'ERROR') {
        setError(payload);
        const callback = messageCallbacks.current.get(id);
        if (callback) {
          callback(new Error(payload)); // Propagate error to specific call
          messageCallbacks.current.delete(id);
        }
      } else {
        const callback = messageCallbacks.current.get(id);
        if (callback) {
          callback(payload);
          messageCallbacks.current.delete(id);
        }
      }
    };

    worker.onerror = (err) => {
      console.error('DuckDB Worker Error:', err);
      setError('Worker encountered an error.');
    };

    // Initialize the worker
    worker.postMessage({ id: uuidv4(), type: 'INIT' });

    return () => {
      worker.terminate();
      workerRef.current = null;
    };
  }, []);

  const sendWorkerMessage = useCallback((type: string, payload?: any): Promise<any> => {
    return new Promise((resolve, reject) => {
      if (!workerRef.current) {
        return reject(new Error('DuckDB worker not initialized.'));
      }
      const id = uuidv4();
      messageCallbacks.current.set(id, (result: any) => {
        if (result instanceof Error) {
          reject(result);
        } else {
          resolve(result);
        }
      });
      workerRef.current.postMessage({ id, type, payload });
    });
  }, []);

  const query = useCallback(async (sql: string, params?: any[]): Promise<QueryResult[]> => {
    if (!isReady) throw new Error('DuckDB is not ready.');
    return sendWorkerMessage('QUERY', { query: sql, params });
  }, [isReady, sendWorkerMessage]);

  const registerParquetUrl = useCallback(async (name: string, url: string): Promise<void> => {
    if (!isReady) throw new Error('DuckDB is not ready.');
    return sendWorkerMessage('REGISTER_PARQUET_URL', { name, url });
  }, [isReady, sendWorkerMessage]);

  return { isReady, error, query, registerParquetUrl };
}

import pandas as pd
import numpy as np
from datetime import datetime, timedelta

num_rows = 10_000_000

# Generate synthetic data
data = {
    'sale_id': np.arange(num_rows),
    'product_category': np.random.choice(['Electronics', 'Clothing', 'Home Goods', 'Books', 'Food'], num_rows),
    'revenue': np.random.uniform(10, 1000, num_rows).round(2),
    'units_sold': np.random.randint(1, 10, num_rows),
    'sale_date': [datetime(2023, 1, 1) + timedelta(days=np.random.randint(0, 365)) for _ in range(num_rows)],
    'region': np.random.choice(['North', 'South', 'East', 'West'], num_rows),
}

df = pd.DataFrame(data)

# Save to Parquet
df.to_parquet('public/sales_data_10m.parquet', index=False)
print(f"Generated sales_data_10m.parquet with {num_rows} rows.")

// src/app/page.tsx
'use client'; // Mark as client component for Next.js App Router

import { useEffect, useState, useMemo } from 'react';
import { useDuckDB } from '../hooks/useDuckDB';
import { Bar, Line } from 'react-chartjs-2';
import {
  Chart as ChartJS,
  CategoryScale,
  LinearScale,
  BarElement,
  Title,
  Tooltip,
  Legend,
  PointElement,
  LineElement,
} from 'chart.js';

// Register Chart.js components
ChartJS.register(
  CategoryScale,
  LinearScale,
  BarElement,
  Title,
  Tooltip,
  Legend,
  PointElement,
  LineElement
);

interface SalesByCategory {
  product_category: string;
  total_revenue: number;
}

interface DailySales {
  sale_date: string; // DuckDB returns dates as strings by default
  daily_revenue: number;
}

export default function Home() {
  const { isReady, error, query, registerParquetUrl } = useDuckDB();
  const [categoryData, setCategoryData] = useState<SalesByCategory[]>([]);
  const [dailyData, setDailyData] = useState<DailySales[]>([]);
  const [loading, setLoading] = useState(true);
  const [totalRows, setTotalRows] = useState<number | null>(null);

  const PARQUET_FILE_URL = '/sales_data_10m.parquet'; // Adjust if hosted externally
  const PARQUET_FILE_NAME = 'sales_data_10m.parquet';

  useEffect(() => {
    const loadData = async () => {
      if (isReady) {
        setLoading(true);
        try {
          // Register the Parquet file URL with DuckDB
          await registerParquetUrl(PARQUET_FILE_NAME, PARQUET_FILE_URL);
          console.log('Parquet file registered.');

          // Count total rows
          const countResult = await query(`SELECT COUNT(*) as total_rows FROM "${PARQUET_FILE_NAME}"`);
          setTotalRows(countResult[0]?.total_rows || 0);

          // Query 1: Total Revenue by Product Category
          const categoryResults = await query(`
            SELECT
              product_category,
              SUM(revenue) AS total_revenue
            FROM "${PARQUET_FILE_NAME}"
            GROUP BY product_category
            ORDER BY total_revenue DESC
          `);
          setCategoryData(categoryResults as SalesByCategory[]);
          console.log('Category data fetched:', categoryResults);

          // Query 2: Daily Revenue Trend
          const dailyResults = await query(`
            SELECT
              STRFTIME(sale_date, '%Y-%m-%d') AS sale_date,
              SUM(revenue) AS daily_revenue
            FROM "${PARQUET_FILE_NAME}"
            GROUP BY STRFTIME(sale_date, '%Y-%m-%d')
            ORDER BY sale_date ASC
          `);
          setDailyData(dailyResults as DailySales[]);
          console.log('Daily data fetched:', dailyResults);

        } catch (err: any) {
          console.error('Failed to query DuckDB:', err);
          setError(err.message);
        } finally {
          setLoading(false);
        }
      }
    };
    loadData();
  }, [isReady, query, registerParquetUrl, setError]);

  const categoryChartData = useMemo(() => ({
    labels: categoryData.map(d => d.product_category),
    datasets: [
      {
        label: 'Total Revenue',
        data: categoryData.map(d => d.total_revenue),
        backgroundColor: 'rgba(75, 192, 192, 0.6)',
        borderColor: 'rgba(75, 192, 192, 1)',
        borderWidth: 1,
      },
    ],
  }), [categoryData]);

  const dailyChartData = useMemo(() => ({
    labels: dailyData.map(d => d.sale_date),
    datasets: [
      {
        label: 'Daily Revenue',
        data: dailyData.map(d => d.daily_revenue),
        fill: false,
        backgroundColor: 'rgba(153, 102, 255, 0.6)',
        borderColor: 'rgba(153, 102, 255, 1)',
        tension: 0.1,
      },
    ],
  }), [dailyData]);

  if (error) {
    return <div className="p-4 text-red-600">Error: {error}</div>;
  }

  if (!isReady || loading) {
    return <div className="p-4">Loading DuckDB and data... This may take a moment for large files.</div>;
  }

  return (
    <div className="container mx-auto p-4">
      <h1 className="text-3xl font-bold mb-6">Client-Side Sales Dashboard (10M Rows)</h1>
      <p className="mb-4">
        Powered by DuckDB-Wasm. Total rows processed: <span className="font-bold">{totalRows?.toLocaleString()}</span>
      </p>

      <div className="grid grid-cols-1 md:grid-cols-2 gap-8">
        <div className="bg-white p-6 rounded-lg shadow-md">
          <h2 className="text-xl font-semibold mb-4">Revenue by Product Category</h2>
          <Bar data={categoryChartData} options={{ responsive: true, maintainAspectRatio: false }} />
        </div>
        <div className="bg-white p-6 rounded-lg shadow-md">
          <h2 className="text-xl font-semibold mb-4">Daily Revenue Trend</h2>
          <Line data={dailyChartData} options={{ responsive: true, maintainAspectRatio: false }} />
        </div>
      </div>
    </div>
  );
}

// Example for persistence in duckdb.worker.ts
import { AsyncDuckDB, DuckDBDataProtocol, ConsoleLogger, IDBBatchAtomicVFS } from '@duckdb/duckdb-wasm';

// ... inside initializeDuckDB()
const bundle = await duckdb.selectBundle(DUCKDB_BUNDLES);
const worker = new Worker(bundle.mainWorker!);

// Initialize VFS for persistence
const vfs = new IDBBatchAtomicVFS('duckdb-data'); // 'duckdb-data' is the database name
db = new AsyncDuckDB(new ConsoleLogger(), worker);
await db.instantiate(bundle.mainModule, bundle.pthreadWorker, {
  query: {
    'duckdb-data': vfs, // Register the VFS
  },
});
conn = await db.connect();
// Now you can use 'ATTACH 'duckdb-data';' and 'CREATE TABLE ... IN 'duckdb-data';'
// or 'COPY ... TO 'duckdb-data'/my_table.parquet (FORMAT PARQUET);'

// In your worker:
// ...
else if (type === 'REGISTER_FILE_BUFFER') {
  if (!db) throw new Error('DuckDB not initialized.');
  const { name, buffer } = payload; // buffer would be an ArrayBuffer
  await db.registerFileBuffer(name, new Uint8Array(buffer));
  console.log(`Registered file buffer: ${name}`);
  self.postMessage({ id, type: 'REGISTER_FILE_BUFFER_SUCCESS' });
}
// ...

// In your React component:
const handleFileChange = async (event: React.ChangeEvent<HTMLInputElement>) => {
  const file = event.target.files?.[0];
  if (file) {
    const arrayBuffer = await file.arrayBuffer();
    await sendWorkerMessage('REGISTER_FILE_BUFFER', { name: file.name, buffer: arrayBuffer });
    // Now you can query the file by its name
    const result = await query(`SELECT COUNT(*) FROM "${file.name}"`);
    console.log('Local file count:', result);
  }
};

// ... in JSX
<input type="file" onChange={handleFileChange} accept=".parquet" />