뉴욕 공립 도서관 "What's on the Menu?" 데이터셋
뉴욕 공립 도서관 "What's on the Menu?" 데이터셋
뉴욕 공립 도서관이 만든 역사적인 메뉴 데이터셋입니다. 호텔, 식당, 카페 메뉴의 요리와 가격 기록을 담고 있으며, ClickHouse에서 정규화된 테이블을 비정규화(denormalize)하고 분석하는 과정을 보여줍니다.
출처: 문서
본문
이 데이터셋은 뉴욕 공립 도서관(New York Public Library)이 만들었습니다. 호텔, 식당, 카페의 메뉴와 함께 요리 및 그 가격에 대한 역사적 데이터를 포함합니다.
출처: https://www.nypl.org/research/support/whats-on-the-menu 데이터는 공개 도메인(public domain)입니다.
데이터는 도서관 아카이브에서 비롯되었으며 불완전하고 통계 분석이 어려울 수 있습니다. 그럼에도 매우 맛있습니다. 크기는 메뉴의 요리 기록 약 130만 개에 불과합니다 — ClickHouse에게는 아주 작은 데이터 양이지만, 그래도 좋은 예시입니다.
데이터셋 다운로드 (Download the dataset)
다음 명령을 실행하세요:
wget https://s3.amazonaws.com/menusdata.nypl.org/gzips/2021_08_01_07_01_17_data.tgz
# 옵션: 체크섬 검증
md5sum 2021_08_01_07_01_17_data.tgz
# Checksum should be equal to: db6126724de939a5481e3160a2d67d15
필요하다면 링크를 https://www.nypl.org/research/support/whats-on-the-menu의 최신 링크로 바꾸세요. 다운로드 크기는 약 35 MB입니다.
데이터셋 압축 해제 (Unpack the dataset)
tar xvf 2021_08_01_07_01_17_data.tgz
압축 해제 크기는 약 150 MB입니다.
데이터는 정규화되어 있으며 네 개의 테이블로 구성됩니다:
Menu— 메뉴에 대한 정보: 식당 이름, 메뉴가 보였던 날짜 등Dish— 요리에 대한 정보: 요리 이름과 일부 특성MenuPage— 메뉴의 페이지에 대한 정보, 모든 페이지는 어떤 메뉴에 속합니다MenuItem— 메뉴 항목. 어떤 메뉴 페이지의 요리와 가격: 요리와 메뉴 페이지에 대한 링크
테이블 생성 (Create the tables)
가격을 저장하기 위해 Decimal 데이터 타입을 사용합니다.
CREATE TABLE dish
(
id UInt32,
name String,
description String,
menus_appeared UInt32,
times_appeared Int32,
first_appeared UInt16,
last_appeared UInt16,
lowest_price Decimal64(3),
highest_price Decimal64(3)
) ENGINE = MergeTree ORDER BY id;
CREATE TABLE menu
(
id UInt32,
name String,
sponsor String,
event String,
venue String,
place String,
physical_description String,
occasion String,
notes String,
call_number String,
keywords String,
language String,
date String,
location String,
location_type String,
currency String,
currency_symbol String,
status String,
page_count UInt16,
dish_count UInt16
) ENGINE = MergeTree ORDER BY id;
CREATE TABLE menu_page
(
id UInt32,
menu_id UInt32,
page_number UInt16,
image_id String,
full_height UInt16,
full_width UInt16,
uuid UUID
) ENGINE = MergeTree ORDER BY id;
CREATE TABLE menu_item
(
id UInt32,
menu_page_id UInt32,
price Decimal64(3),
high_price Decimal64(3),
dish_id UInt32,
created_at DateTime,
updated_at DateTime,
xpos Float64,
ypos Float64
) ENGINE = MergeTree ORDER BY id;
데이터 임포트 (Import the data)
데이터를 ClickHouse에 업로드하려면:
clickhouse-client --format_csv_allow_single_quotes 0 --input_format_null_as_default 0 --query "INSERT INTO dish FORMAT CSVWithNames" < Dish.csv
clickhouse-client --format_csv_allow_single_quotes 0 --input_format_null_as_default 0 --query "INSERT INTO menu FORMAT CSVWithNames" < Menu.csv
clickhouse-client --format_csv_allow_single_quotes 0 --input_format_null_as_default 0 --query "INSERT INTO menu_page FORMAT CSVWithNames" < MenuPage.csv
clickhouse-client --format_csv_allow_single_quotes 0 --input_format_null_as_default 0 --date_time_input_format best_effort --query "INSERT INTO menu_item FORMAT CSVWithNames" < MenuItem.csv
데이터가 헤더가 있는 CSV로 표현되므로 CSVWithNames 형식을 사용합니다.
데이터 필드에는 큰따옴표만 사용되고 작은따옴표는 값 안에 있을 수 있어 CSV 파서를 혼동시키면 안 되므로 format_csv_allow_single_quotes를 비활성화합니다.
데이터에 NULL이 없으므로 input_format_null_as_default를 비활성화합니다. 그렇지 않으면 ClickHouse가 \N 시퀀스를 파싱하려 시도하고 데이터의 \와 혼동될 수 있습니다.
date_time_input_format best_effort 설정은 DateTime 필드를 다양한 형식으로 파싱할 수 있게 합니다. 예를 들어 초가 없는 ISO-8601인 '2000-01-01 01:02'가 인식됩니다. 이 설정이 없으면 고정된 DateTime 형식만 허용됩니다.
데이터 비정규화 (Denormalize the data)
데이터는 정규화된 형태의 여러 테이블로 제공됩니다. 즉, 예를 들어 메뉴 항목에서 요리 이름을 조회하려면 JOIN을 수행해야 합니다. 일반적인 분석 작업에서는 매번 JOIN을 피하기 위해 미리 JOIN된 데이터를 다루는 것이 훨씬 효율적입니다. 이를 "비정규화된" 데이터라고 합니다.
모든 데이터가 함께 JOIN된 menu_item_denorm 테이블을 만들 것입니다:
CREATE TABLE menu_item_denorm
ENGINE = MergeTree ORDER BY (dish_name, created_at)
AS SELECT
price,
high_price,
created_at,
updated_at,
xpos,
ypos,
dish.id AS dish_id,
dish.name AS dish_name,
dish.description AS dish_description,
dish.menus_appeared AS dish_menus_appeared,
dish.times_appeared AS dish_times_appeared,
dish.first_appeared AS dish_first_appeared,
dish.last_appeared AS dish_last_appeared,
dish.lowest_price AS dish_lowest_price,
dish.highest_price AS dish_highest_price,
menu.id AS menu_id,
menu.name AS menu_name,
menu.sponsor AS menu_sponsor,
menu.event AS menu_event,
menu.venue AS menu_venue,
menu.place AS menu_place,
menu.physical_description AS menu_physical_description,
menu.occasion AS menu_occasion,
menu.notes AS menu_notes,
menu.call_number AS menu_call_number,
menu.keywords AS menu_keywords,
menu.language AS menu_language,
menu.date AS menu_date,
menu.location AS menu_location,
menu.location_type AS menu_location_type,
menu.currency AS menu_currency,
menu.currency_symbol AS menu_currency_symbol,
menu.status AS menu_status,
menu.page_count AS menu_page_count,
menu.dish_count AS menu_dish_count
FROM menu_item
JOIN dish ON menu_item.dish_id = dish.id
JOIN menu_page ON menu_item.menu_page_id = menu_page.id
JOIN menu ON menu_page.menu_id = menu.id;
데이터 검증 (Validate the data)
Query
SELECT count() FROM menu_item_denorm;
Response
┌─count()─┐
│ 1329175 │
└─────────┘
몇 가지 쿼리 실행 (Run some queries)
요리들의 평균 역사적 가격 (Averaged historical prices of dishes)
Query
SELECT
round(toUInt32OrZero(extract(menu_date, '^\\d{4}')), -1) AS d,
count(),
round(avg(price), 2),
bar(avg(price), 0, 100, 100)
FROM menu_item_denorm
WHERE (menu_currency = 'Dollars') AND (d > 0) AND (d < 2022)
GROUP BY d
ORDER BY d ASC;
Response
┌────d─┬─count()─┬─round(avg(price), 2)─┬─bar(avg(price), 0, 100, 100)─┐
│ 1850 │ 618 │ 1.5 │ █▍ │
│ 1860 │ 1634 │ 1.29 │ █▎ │
│ 1870 │ 2215 │ 1.36 │ █▎ │
│ 1880 │ 3909 │ 1.01 │ █ │
│ 1890 │ 8837 │ 1.4 │ █▍ │
│ 1900 │ 176292 │ 0.68 │ ▋ │
│ 1910 │ 212196 │ 0.88 │ ▊ │
│ 1920 │ 179590 │ 0.74 │ ▋ │
│ 1930 │ 73707 │ 0.6 │ ▌ │
│ 1940 │ 58795 │ 0.57 │ ▌ │
│ 1950 │ 41407 │ 0.95 │ ▊ │
│ 1960 │ 51179 │ 1.32 │ █▎ │
│ 1970 │ 12914 │ 1.86 │ █▋ │
│ 1980 │ 7268 │ 4.35 │ ████▎ │
│ 1990 │ 11055 │ 6.03 │ ██████ │
│ 2000 │ 2467 │ 11.85 │ ███████████▋ │
│ 2010 │ 597 │ 25.66 │ █████████████████████████▋ │
└──────┴─────────┴──────────────────────┴──────────────────────────────┘
가볍게(약간의 소금을 곁들여) 받아들이세요.
버거 가격 (Burger prices)
Query
SELECT
round(toUInt32OrZero(extract(menu_date, '^\\d{4}')), -1) AS d,
count(),
round(avg(price), 2),
bar(avg(price), 0, 50, 100)
FROM menu_item_denorm
WHERE (menu_currency = 'Dollars') AND (d > 0) AND (d < 2022) AND (dish_name ILIKE '%burger%')
GROUP BY d
ORDER BY d ASC;
Response
┌────d─┬─count()─┬─round(avg(price), 2)─┬─bar(avg(price), 0, 50, 100)───────────┐
│ 1880 │ 2 │ 0.42 │ ▋ │
│ 1890 │ 7 │ 0.85 │ █▋ │
│ 1900 │ 399 │ 0.49 │ ▊ │
│ 1910 │ 589 │ 0.68 │ █▎ │
│ 1920 │ 280 │ 0.56 │ █ │
│ 1930 │ 74 │ 0.42 │ ▋ │
│ 1940 │ 119 │ 0.59 │ █▏ │
│ 1950 │ 134 │ 1.09 │ ██▏ │
│ 1960 │ 272 │ 0.92 │ █▋ │
│ 1970 │ 108 │ 1.18 │ ██▎ │
│ 1980 │ 88 │ 2.82 │ █████▋ │
│ 1990 │ 184 │ 3.68 │ ███████▎ │
│ 2000 │ 21 │ 7.14 │ ██████████████▎ │
│ 2010 │ 6 │ 18.42 │ ████████████████████████████████████▋ │
└──────┴─────────┴──────────────────────┴───────────────────────────────────────┘
보드카 (Vodka)
Query
SELECT
round(toUInt32OrZero(extract(menu_date, '^\\d{4}')), -1) AS d,
count(),
round(avg(price), 2),
bar(avg(price), 0, 50, 100)
FROM menu_item_denorm
WHERE (menu_currency IN ('Dollars', '')) AND (d > 0) AND (d < 2022) AND (dish_name ILIKE '%vodka%')
GROUP BY d
ORDER BY d ASC;
Response
┌────d─┬─count()─┬─round(avg(price), 2)─┬─bar(avg(price), 0, 50, 100)─┐
│ 1910 │ 2 │ 0 │ │
│ 1920 │ 1 │ 0.3 │ ▌ │
│ 1940 │ 21 │ 0.42 │ ▋ │
│ 1950 │ 14 │ 0.59 │ █▏ │
│ 1960 │ 113 │ 2.17 │ ████▎ │
│ 1970 │ 37 │ 0.68 │ █▎ │
│ 1980 │ 19 │ 2.55 │ █████ │
│ 1990 │ 86 │ 3.6 │ ███████▏ │
│ 2000 │ 2 │ 3.98 │ ███████▊ │
└──────┴─────────┴──────────────────────┴─────────────────────────────┘
보드카를 얻으려면 ILIKE '%vodka%'를 써야 하며, 이것은 확실히 한 가지 진술을 만들어 냅니다.
캐비어 (Caviar)
캐비어 가격을 출력해 봅시다. 또한 캐비어가 든 요리의 이름도 출력해 봅시다.
Query
SELECT
round(toUInt32OrZero(extract(menu_date, '^\\d{4}')), -1) AS d,
count(),
round(avg(price), 2),
bar(avg(price), 0, 50, 100),
any(dish_name)
FROM menu_item_denorm
WHERE (menu_currency IN ('Dollars', '')) AND (d > 0) AND (d < 2022) AND (dish_name ILIKE '%caviar%')
GROUP BY d
ORDER BY d ASC;
Response
┌────d─┬─count()─┬─...─┬─any(dish_name)────────────────────────────────────────────┐
│ 1090 │ 1 │ 0 │ Caviar │
│ ...(원문에 여러 연도별 결과가 있음)...
└──────┴─────────┴──────┴──────────────────────────────────────────────────────────┘
적어도 그들은 보드카와 함께 캐비어를 제공합니다. 아주 좋습니다.
(원문의 결과 표에는 각 연도별 캐비어 요리 이름(예: "Beluga Caviar", "ASTRAKAN CAVIAR")이 전체로 포함되어 있습니다.)
온라인 플레이그라운드 (Online playground)
데이터는 ClickHouse Playground에 업로드되어 있습니다. 예시.