ニューヨーク公共図書館「メニューは何ですか？」データセット

このデータセットは、ニューヨーク公共図書館によって作成されました。ホテル、レストラン、カフェのメニューに関する歴史的データで、料理とその価格が含まれています。

ソース: http://menus.nypl.org/data
データはパブリックドメインです。

このデータは図書館のアーカイブに由来しており、不完全で統計分析には難しい場合があります。それでも、とても美味しいデータです。
サイズは僅か130万レコードのメニューに関するもので、ClickHouse にとっては非常に小さなデータボリュームですが、良い例です。

データセットのダウンロード

次のコマンドを実行します:

必要に応じて、http://menus.nypl.org/data から最新のリンクに置き換えてください。
ダウンロードサイズは約35MBです。

データセットの解凍

解凍後のサイズは約150MBです。

データは正規化されており、4つのテーブルで構成されています:

• Menu — メニューに関する情報: レストランの名前、メニューが表示された日など。
• Dish — 料理に関する情報: 料理の名前といくつかの特徴。
• MenuPage — メニューのページに関する情報、なぜなら各ページは何らかのメニューに属するからです。
• MenuItem — メニューの項目。あるメニューページにおける料理とその価格: 料理とメニューページへのリンク。

テーブルの作成

価格を保存するために Decimal データ型を使用します。

データのインポート

ClickHouse にデータをアップロードするため、次のコマンドを実行します:

データはヘッダー付きのCSVで表されるため、CSVWithNames フォーマットを使用します。

データフィールドにはダブルクォートのみが使用され、値の中にシングルクォートが含まれる可能性があるため、format_csv_allow_single_quotes を無効にします。

また、私たちのデータには NULL がないため、input_format_null_as_default を無効にします。そうでないと、ClickHouse は \N シーケンスを解析しようとして混乱する可能性があります。

設定 date_time_input_format best_effort は、さまざまな形式の DateTime フィールドを解析可能にします。この設定なしでは固定の DateTime 形式のみが許可されます。

データの非正規化

データは 正規化された形式 で複数のテーブルに提示されています。つまり、例えばメニューアイテムから料理の名前をクエリする場合、JOIN を行う必要があります。
典型的な分析タスクでは、毎回 JOIN を行うのを避けるために、事前に JOIN されたデータを扱う方がはるかに効率的です。これを「非正規化」データと呼びます。

全てのデータを結合した menu_item_denorm テーブルを作成します:

データの検証

クエリ:

結果:

クエリの実行

料理の平均歴史的価格

クエリ:

結果:

鵜呑みにしないでください。

バーガーの価格

クエリ:

結果:

ウォッカ

クエリ:

結果:

ウォッカを得るためには ILIKE '%vodka%' と書く必要があり、これは間違いなく言えることです。

キャビア

キャビアの価格を印刷しましょう。また、キャビアを含む任意の料理の名前も印刷します。

クエリ:

結果:

少なくとも彼らはウォッカと一緒にキャビアを持っています。とても素敵です。

オンラインプレイグラウンド

データは ClickHouse Playground にアップロードされており、例もあります。