> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Amazon製品に関する1億5,000万件を超える顧客レビュー # Amazonの顧客レビュー export const RunnableCode = ({children, run = false, showStats = true}) => { const [results, setResults] = useState(null); const [error, setError] = useState(null); const [loading, setLoading] = useState(false); const [showResults, setShowResults] = useState(false); const [stats, setStats] = useState(null); const [isDark, setIsDark] = useState(false); const [hoveredRow, setHoveredRow] = useState(-1); const codeRef = useRef(null); useEffect(() => { if (typeof window !== 'undefined') { const check = () => setIsDark(document.documentElement.classList.contains('dark')); check(); const observer = new MutationObserver(check); observer.observe(document.documentElement, { attributes: true, attributeFilter: ['class'] }); return () => observer.disconnect(); } }, []); useEffect(() => { if (codeRef.current) { const block = codeRef.current.querySelector('.code-block'); if (block) { block.style.marginBottom = '0'; block.style.marginTop = '0'; block.style.borderBottomLeftRadius = '0'; block.style.borderBottomRightRadius = '0'; } } }); const getSqlText = () => { if (!codeRef.current) return ''; const code = codeRef.current.querySelector('code'); return (code || codeRef.current).textContent.trim(); }; const executeQuery = async () => { const sql = getSqlText(); if (!sql) return; setLoading(true); setError(null); setResults(null); setShowResults(true); try { const cleanQuery = sql.replace(/;$/, '').trim(); const params = new URLSearchParams({ query: cleanQuery, default_format: 'JSONCompact', result_overflow_mode: 'break', read_overflow_mode: 'break', allow_experimental_analyzer: '1' }); const res = await fetch(`https://sql-clickhouse.clickhouse.com/?${params.toString()}`, { method: 'POST', headers: { 'Authorization': `Basic ${btoa(`demo:`)}` } }); const text = await res.text(); if (!res.ok) { setError(text || `HTTP ${res.status}`); setLoading(false); return; } const json = JSON.parse(text); setResults(json); setStats(json.statistics || null); } catch (err) { setError(err.message || 'Query execution failed'); } setLoading(false); }; useEffect(() => { if (run) executeQuery(); }, []); const formatRows = n => { if (n >= 1e9) return `${(n / 1e9).toFixed(1)}B`; if (n >= 1e6) return `${(n / 1e6).toFixed(1)}M`; if (n >= 1e3) return `${(n / 1e3).toFixed(1)}K`; return String(n); }; const formatBytes = b => { if (b >= 1e9) return `${(b / 1e9).toFixed(2)} GB`; if (b >= 1e6) return `${(b / 1e6).toFixed(2)} MB`; if (b >= 1e3) return `${(b / 1e3).toFixed(2)} KB`; return `${b} B`; }; const isNumericType = type => { return (/^(UInt|Int|Float|Decimal)/).test(type); }; const isHyperlink = value => { return typeof value === 'string' && (/^https?:\/\//).test(value); }; const computeColumnExtremes = (meta, data) => { const extremes = {}; for (let i = 0; i < meta.length; i++) { if (isNumericType(meta[i].type)) { let min = Infinity, max = -Infinity; for (const row of data) { const v = Number(row[i]); if (!isNaN(v)) { if (v < min) min = v; if (v > max) max = v; } } if (max > -Infinity) { extremes[i] = { min, max }; } } } return extremes; }; const computeColumnWidths = (meta, data) => { const lengths = meta.map((col, i) => { const headerLen = col.name.length + col.type.length + 1; let maxData = 0; for (const row of data) { const v = row[i]; const len = v === null ? 4 : String(v).length; if (len > maxData) maxData = len; } return Math.max(headerLen, maxData); }); const total = lengths.reduce((s, l) => s + l, 0); return lengths.map(l => `${(l / total * 100).toFixed(1)}%`); }; const copyResultsAsTSV = () => { if (!results || !results.meta || !results.data) return; const header = results.meta.map(col => col.name).join('\t'); const rows = results.data.map(row => row.map(cell => cell === null ? 'NULL' : String(cell)).join('\t')); const tsv = [header, ...rows].join('\n'); navigator.clipboard.writeText(tsv); }; const borderColor = isDark ? 'rgba(255,255,255,0.15)' : '#e5e7eb'; const bgColor = isDark ? 'rgba(255,255,255,0.05)' : '#f9fafb'; const headerBg = isDark ? '#2a2a2a' : '#f3f4f6'; const textColor = isDark ? '#e5e7eb' : '#1f2937'; const mutedColor = isDark ? '#d1d5db' : '#6b7280'; const accentColor = isDark ? '#FAFF69' : '#323232'; const accentTextColor = isDark ? '#000' : '#fff'; const barColor = isDark ? '#35372f' : '#d2d2d2'; const cellBg = isDark ? '#1f201b' : '#ffffff'; const cellBgHover = isDark ? 'lch(15.8 0 0)' : '#f0f0f0'; const extremes = results && results.meta && results.data ? computeColumnExtremes(results.meta, results.data) : {}; const colWidths = results && results.meta && results.data ? computeColumnWidths(results.meta, results.data) : []; const getCellBarStyle = (cell, ci, ri) => { if (cell === null) return null; const colMeta = results.meta[ci]; if (!isNumericType(colMeta.type) || !extremes[ci] || results.data.length <= 1 || extremes[ci].max <= 0) return null; const ratio = 100 * Number(cell) / extremes[ci].max; const bg = ri === hoveredRow ? cellBgHover : cellBg; return { background: `linear-gradient(to right, ${barColor} 0%, ${barColor} ${ratio}%, ${bg} ${ratio}%, ${bg} 100%)` }; }; const renderCell = (cell, ci) => { if (cell === null) { return NULL; } const value = String(cell); if (isHyperlink(value)) { return {value} ; } return value; }; return
{}
{children}
{}
{results && } {showStats && stats && Read {formatRows(stats.rows_read)} rows, {formatBytes(stats.bytes_read)} in {stats.elapsed.toFixed(3)}s }
{} {showResults &&
{loading &&
Executing query...
} {error &&
{error}
} {results && results.meta && results.data &&
{results.meta.map((col, i) =>
1 ? 'right' : 'left', backgroundColor: headerBg, borderBottom: `1px solid ${borderColor}`, color: textColor, fontWeight: 600, fontSize: '12px', whiteSpace: 'nowrap', overflow: 'hidden', textOverflow: 'ellipsis' }}> {col.name} {col.type}
)} {results.data.map((row, ri) => row.map((cell, ci) =>
setHoveredRow(ri)} onMouseLeave={() => setHoveredRow(-1)} style={{ padding: '4px 12px', color: textColor, whiteSpace: 'nowrap', overflow: 'hidden', textOverflow: 'ellipsis', textAlign: isNumericType(results.meta[ci].type) && results.meta.length > 1 ? 'right' : 'left', borderBottom: `1px solid ${borderColor}`, backgroundColor: ri === hoveredRow ? cellBgHover : ri % 2 === 0 ? 'transparent' : bgColor, transition: 'background-color 0.1s', ...getCellBarStyle(cell, ci, ri) }}> {renderCell(cell, ci)}
))}
} {results && results.data &&
{results.rows} row{results.rows !== 1 ? 's' : ''}
}
}
; }; このデータセットには、Amazon 製品に関する 1 億 5,000 万件超のカスタマーレビューが含まれています。データは AWS S3 上の snappy 圧縮された Parquet ファイルとして保存されており、圧縮後の合計サイズは 49GB です。これを ClickHouse に挿入する手順を見ていきましょう。 以下のクエリは、ClickHouse Cloud の **Production** インスタンスで実行しています。詳細は ["Playground の仕様"](/ja/get-started/sample-datasets/playground#specifications) を参照してください。
## データセットの読み込み
1. データを ClickHouse に挿入しなくても、その場で直接クエリできます。どのようなデータか確認するために、まずはいくつかの行を取得してみましょう: ```sql theme={null} SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/amazon_reviews/amazon_reviews_2015.snappy.parquet') LIMIT 3 ``` 行は次のようになります: ```response theme={null} Row 1: ────── review_date: 16462 marketplace: US customer_id: 25444946 -- 25.44 million review_id: R146L9MMZYG0WA product_id: B00NV85102 product_parent: 908181913 -- 908.18 million product_title: XIKEZAN iPhone 6 Plus 5.5 inch Waterproof Case, Shockproof Dirtproof Snowproof Full Body Skin Case Protective Cover with Hand Strap & Headphone Adapter & Kickstand product_category: Wireless star_rating: 4 helpful_votes: 0 total_votes: 0 vine: false verified_purchase: true review_headline: case is sturdy and protects as I want review_body: I won't count on the waterproof part (I took off the rubber seals at the bottom because the got on my nerves). But the case is sturdy and protects as I want. Row 2: ────── review_date: 16462 marketplace: US customer_id: 1974568 -- 1.97 million review_id: R2LXDXT293LG1T product_id: B00OTFZ23M product_parent: 951208259 -- 951.21 million product_title: Season.C Chicago Bulls Marilyn Monroe No.1 Hard Back Case Cover for Samsung Galaxy S5 i9600 product_category: Wireless star_rating: 1 helpful_votes: 0 total_votes: 0 vine: false verified_purchase: true review_headline: One Star review_body: Cant use the case because its big for the phone. Waist of money! Row 3: ────── review_date: 16462 marketplace: US customer_id: 24803564 -- 24.80 million review_id: R7K9U5OEIRJWR product_id: B00LB8C4U4 product_parent: 524588109 -- 524.59 million product_title: iPhone 5s Case, BUDDIBOX [Shield] Slim Dual Layer Protective Case with Kickstand for Apple iPhone 5 and 5s product_category: Wireless star_rating: 4 helpful_votes: 0 total_votes: 0 vine: false verified_purchase: true review_headline: but overall this case is pretty sturdy and provides good protection for the phone review_body: The front piece was a little difficult to secure to the phone at first, but overall this case is pretty sturdy and provides good protection for the phone, which is what I need. I would buy this case again. ``` 2. このデータをClickHouseに保存するため、`amazon_reviews` という名前の新しい `MergeTree` テーブルを定義します。 ```sql theme={null} CREATE DATABASE amazon CREATE TABLE amazon.amazon_reviews ( `review_date` Date, `marketplace` LowCardinality(String), `customer_id` UInt64, `review_id` String, `product_id` String, `product_parent` UInt64, `product_title` String, `product_category` LowCardinality(String), `star_rating` UInt8, `helpful_votes` UInt32, `total_votes` UInt32, `vine` Bool, `verified_purchase` Bool, `review_headline` String, `review_body` String, PROJECTION helpful_votes ( SELECT * ORDER BY helpful_votes ) ) ENGINE = MergeTree ORDER BY (review_date, product_category) ``` 3. 次の `INSERT` コマンドでは `s3Cluster` テーブル関数を使用します。これにより、クラスター内のすべてのノードを使って複数の S3 ファイルを並列に処理できます。また、ワイルドカードを使用して、`https://datasets-documentation.s3.eu-west-3.amazonaws.com/amazon_reviews/amazon_reviews_*.snappy.parquet` で始まるファイルをすべて挿入します: ```sql theme={null} INSERT INTO amazon.amazon_reviews SELECT * FROM s3Cluster('default', 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/amazon_reviews/amazon_reviews_*.snappy.parquet') ``` ClickHouse Cloud では、クラスター名は `default` です。`default` をお使いのクラスター名に変更してください。クラスターがない場合は、`s3Cluster` の代わりに `s3` テーブル関数を使用してください。 5. このクエリの実行にはそれほど時間はかからず、平均で毎秒約300,000行を処理します。5分ほどで、すべての行が挿入されたことを確認できるはずです。 ```sql theme={null} SELECT formatReadableQuantity(count()) FROM amazon.amazon_reviews ``` 6. データがどれくらいの領域を使用しているかを見てみましょう。 ```sql theme={null} SELECT disk_name, formatReadableSize(sum(data_compressed_bytes) AS size) AS compressed, formatReadableSize(sum(data_uncompressed_bytes) AS usize) AS uncompressed, round(usize / size, 2) AS compr_rate, sum(rows) AS rows, count() AS part_count FROM system.parts WHERE (active = 1) AND (table = 'amazon_reviews') GROUP BY disk_name ORDER BY size DESC ``` 元のデータは約70Gでしたが、ClickHouse で圧縮すると約30Gになります。
## クエリ例
7. いくつかクエリを実行してみましょう。データセット内で「参考になった」票が最も多いレビューの上位 10 件は次のとおりです。 ```sql theme={null} SELECT product_title, review_headline FROM amazon.amazon_reviews ORDER BY helpful_votes DESC LIMIT 10 ``` このクエリでは、パフォーマンス向上のために [projection](/ja/concepts/features/projections/projections) を使用しています。 8. Amazon でレビュー数が最も多い商品の上位 10 件は次のとおりです。 ```sql theme={null} SELECT any(product_title), count() FROM amazon.amazon_reviews GROUP BY product_id ORDER BY 2 DESC LIMIT 10; ``` 9. 各商品の月ごとの平均レビュー評価は次のとおりです (実際の [Amazon の採用面接問題](https://datalemur.com/questions/sql-avg-review-ratings) です) 。 ```sql theme={null} SELECT toStartOfMonth(review_date) AS month, any(product_title), avg(star_rating) AS avg_stars FROM amazon.amazon_reviews GROUP BY month, product_id ORDER BY month DESC, product_id ASC LIMIT 20; ``` 10. 商品カテゴリごとの総投票数は次のとおりです。このクエリが高速なのは、`product_category` が主キーに含まれているためです。 ```sql theme={null} SELECT sum(total_votes), product_category FROM amazon.amazon_reviews GROUP BY product_category ORDER BY 1 DESC ``` 11. レビュー内で **"awful"** という単語の出現頻度が最も高い商品を探してみましょう。これは大がかりな処理で、1 つの単語を探すために 1 億 5100 万件を超える文字列を解析する必要があります。 ```sql runnable settings={'enable_parallel_replicas':1} theme={null} SELECT product_id, any(product_title), avg(star_rating), count() AS count FROM amazon.amazon_reviews WHERE position(review_body, 'awful') > 0 GROUP BY product_id ORDER BY count DESC LIMIT 50; ``` これほど大量のデータに対するクエリ時間に注目してください。結果を読むのもなかなか面白いです！ 12. 今度はレビュー内で **awesome** を検索する点だけ変えて、同じクエリをもう一度実行します: ```sql runnable settings={'enable_parallel_replicas':1} theme={null} SELECT product_id, any(product_title), avg(star_rating), count() AS count FROM amazon.amazon_reviews WHERE position(review_body, 'awesome') > 0 GROUP BY product_id ORDER BY count DESC LIMIT 50; ```