我的书签
添加书签
移除书签Recipes Dataset
RecipeNLG dataset is available for download here. It contains 2.2 million recipes. The size is slightly less than 1 GB.
Download and unpack the dataset
Accept Terms and Conditions and download it here. Unpack the zip file with unzip. You will get the full_dataset.csv file.
Create a table
Run clickhouse-client and execute the following CREATE query:
CREATE TABLE recipes(title String,ingredients Array(String),directions Array(String),link String,source LowCardinality(String),NER Array(String)) ENGINE = MergeTree ORDER BY title;
Insert the data
Run the following command:
clickhouse-client --query "INSERT INTO recipesSELECTtitle,JSONExtract(ingredients, 'Array(String)'),JSONExtract(directions, 'Array(String)'),link,source,JSONExtract(NER, 'Array(String)')FROM input('num UInt32, title String, ingredients String, directions String, link String, source LowCardinality(String), NER String')FORMAT CSVWithNames" --input_format_with_names_use_header 0 --format_csv_allow_single_quote 0 --input_format_allow_errors_num 10 < full_dataset.csv
This is a showcase how to parse custom CSV, as it requires multiple tunes.
Explanation:
- the dataset is in CSV format, but it requires some preprocessing on insertion; we use table function input to perform preprocessing;
- the structure of CSV file is specified in the argument of the table function input;
- the field num (row number) is unneeded - we parse it from file and ignore;
- we use FORMAT CSVWithNames but the header in CSV will be ignored (by command line parameter --input_format_with_names_use_header 0), because the header does not contain the name for the first field;
- file is using only double quotes to enclose CSV strings; some strings are not enclosed in double quotes, and single quote must not be parsed as the string enclosing - that’s why we also add the --format_csv_allow_single_quote 0 parameter;
- some strings from CSV cannot parse, because they contain \M/ sequence at the beginning of the value; the only value starting with backslash in CSV can be \N that is parsed as SQL NULL. We add --input_format_allow_errors_num 10 parameter and up to ten malformed records can be skipped;
- there are arrays for ingredients, directions and NER fields; these arrays are represented in unusual form: they are serialized into string as JSON and then placed in CSV - we parse them as String and then use JSONExtract function to transform it to Array.
Validate the inserted data
By checking the row count:
SELECT count() FROM recipes┌─count()─┐│ 2231141 │└─────────┘
Example queries
Top components by the number of recipes:
SELECTarrayJoin(NER) AS k,count() AS cFROM recipesGROUP BY kORDER BY c DESCLIMIT 50┌─k────────────────────┬──────c─┐│ salt │ 890741 ││ sugar │ 620027 ││ butter │ 493823 ││ flour │ 466110 ││ eggs │ 401276 ││ onion │ 372469 ││ garlic │ 358364 ││ milk │ 346769 ││ water │ 326092 ││ vanilla │ 270381 ││ olive oil │ 197877 ││ pepper │ 179305 ││ brown sugar │ 174447 ││ tomatoes │ 163933 ││ egg │ 160507 ││ baking powder │ 148277 ││ lemon juice │ 146414 ││ Salt │ 122557 ││ cinnamon │ 117927 ││ sour cream │ 116682 ││ cream cheese │ 114423 ││ margarine │ 112742 ││ celery │ 112676 ││ baking soda │ 110690 ││ parsley │ 102151 ││ chicken │ 101505 ││ onions │ 98903 ││ vegetable oil │ 91395 ││ oil │ 85600 ││ mayonnaise │ 84822 ││ pecans │ 79741 ││ nuts │ 78471 ││ potatoes │ 75820 ││ carrots │ 75458 ││ pineapple │ 74345 ││ soy sauce │ 70355 ││ black pepper │ 69064 ││ thyme │ 68429 ││ mustard │ 65948 ││ chicken broth │ 65112 ││ bacon │ 64956 ││ honey │ 64626 ││ oregano │ 64077 ││ ground beef │ 64068 ││ unsalted butter │ 63848 ││ mushrooms │ 61465 ││ Worcestershire sauce │ 59328 ││ cornstarch │ 58476 ││ green pepper │ 58388 ││ Cheddar cheese │ 58354 │└──────────────────────┴────────┘50 rows in set. Elapsed: 0.112 sec. Processed 2.23 million rows, 361.57 MB (19.99 million rows/s., 3.24 GB/s.)
In this example we learn how to use arrayJoin function to multiply data by array elements.
The most complex recipes with strawberry
SELECTtitle,length(NER),length(directions)FROM recipesWHERE has(NER, 'strawberry')ORDER BY length(directions) DESCLIMIT 10┌─title────────────────────────────────────────────────────────────┬─length(NER)─┬─length(directions)─┐│ Chocolate-Strawberry-Orange Wedding Cake │ 24 │ 126 ││ Strawberry Cream Cheese Crumble Tart │ 19 │ 47 ││ Charlotte-Style Ice Cream │ 11 │ 45 ││ Sinfully Good a Million Layers Chocolate Layer Cake, With Strawb │ 31 │ 45 ││ Sweetened Berries With Elderflower Sherbet │ 24 │ 44 ││ Chocolate-Strawberry Mousse Cake │ 15 │ 42 ││ Rhubarb Charlotte with Strawberries and Rum │ 20 │ 42 ││ Chef Joey's Strawberry Vanilla Tart │ 7 │ 37 ││ Old-Fashioned Ice Cream Sundae Cake │ 17 │ 37 ││ Watermelon Cake │ 16 │ 36 │└──────────────────────────────────────────────────────────────────┴─────────────┴────────────────────┘10 rows in set. Elapsed: 0.215 sec. Processed 2.23 million rows, 1.48 GB (10.35 million rows/s., 6.86 GB/s.)
In this example, we involve has function to filter by array elements and sort by the number of directions.
There is a wedding cake that requires the whole 126 steps to produce!
Show that directions:
SELECT arrayJoin(directions)FROM recipesWHERE title = 'Chocolate-Strawberry-Orange Wedding Cake'┌─arrayJoin(directions)───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐│ Position 1 rack in center and 1 rack in bottom third of oven and preheat to 350F. ││ Butter one 5-inch-diameter cake pan with 2-inch-high sides, one 8-inch-diameter cake pan with 2-inch-high sides and one 12-inch-diameter cake pan with 2-inch-high sides. ││ Dust pans with flour; line bottoms with parchment. ││ Combine 1/3 cup orange juice and 2 ounces unsweetened chocolate in heavy small saucepan. ││ Stir mixture over medium-low heat until chocolate melts. ││ Remove from heat. ││ Gradually mix in 1 2/3 cups orange juice. ││ Sift 3 cups flour, 2/3 cup cocoa, 2 teaspoons baking soda, 1 teaspoon salt and 1/2 teaspoon baking powder into medium bowl. ││ using electric mixer, beat 1 cup (2 sticks) butter and 3 cups sugar in large bowl until blended (mixture will look grainy). ││ Add 4 eggs, 1 at a time, beating to blend after each. ││ Beat in 1 tablespoon orange peel and 1 tablespoon vanilla extract. ││ Add dry ingredients alternately with orange juice mixture in 3 additions each, beating well after each addition. ││ Mix in 1 cup chocolate chips. ││ Transfer 1 cup plus 2 tablespoons batter to prepared 5-inch pan, 3 cups batter to prepared 8-inch pan and remaining batter (about 6 cups) to 12-inch pan. ││ Place 5-inch and 8-inch pans on center rack of oven. ││ Place 12-inch pan on lower rack of oven. ││ Bake cakes until tester inserted into center comes out clean, about 35 minutes. ││ Transfer cakes in pans to racks and cool completely. ││ Mark 4-inch diameter circle on one 6-inch-diameter cardboard cake round. ││ Cut out marked circle. ││ Mark 7-inch-diameter circle on one 8-inch-diameter cardboard cake round. ││ Cut out marked circle. ││ Mark 11-inch-diameter circle on one 12-inch-diameter cardboard cake round. ││ Cut out marked circle. ││ Cut around sides of 5-inch-cake to loosen. ││ Place 4-inch cardboard over pan. ││ Hold cardboard and pan together; turn cake out onto cardboard. ││ Peel off parchment.Wrap cakes on its cardboard in foil. ││ Repeat turning out, peeling off parchment and wrapping cakes in foil, using 7-inch cardboard for 8-inch cake and 11-inch cardboard for 12-inch cake. ││ Using remaining ingredients, make 1 more batch of cake batter and bake 3 more cake layers as described above. ││ Cool cakes in pans. ││ Cover cakes in pans tightly with foil. ││ (Can be prepared ahead. ││ Let stand at room temperature up to 1 day or double-wrap all cake layers and freeze up to 1 week. ││ Bring cake layers to room temperature before using.) ││ Place first 12-inch cake on its cardboard on work surface. ││ Spread 2 3/4 cups ganache over top of cake and all the way to edge. ││ Spread 2/3 cup jam over ganache, leaving 1/2-inch chocolate border at edge. ││ Drop 1 3/4 cups white chocolate frosting by spoonfuls over jam. ││ Gently spread frosting over jam, leaving 1/2-inch chocolate border at edge. ││ Rub some cocoa powder over second 12-inch cardboard. ││ Cut around sides of second 12-inch cake to loosen. ││ Place cardboard, cocoa side down, over pan. ││ Turn cake out onto cardboard. ││ Peel off parchment. ││ Carefully slide cake off cardboard and onto filling on first 12-inch cake. ││ Refrigerate. ││ Place first 8-inch cake on its cardboard on work surface. ││ Spread 1 cup ganache over top all the way to edge. ││ Spread 1/4 cup jam over, leaving 1/2-inch chocolate border at edge. ││ Drop 1 cup white chocolate frosting by spoonfuls over jam. ││ Gently spread frosting over jam, leaving 1/2-inch chocolate border at edge. ││ Rub some cocoa over second 8-inch cardboard. ││ Cut around sides of second 8-inch cake to loosen. ││ Place cardboard, cocoa side down, over pan. ││ Turn cake out onto cardboard. ││ Peel off parchment. ││ Slide cake off cardboard and onto filling on first 8-inch cake. ││ Refrigerate. ││ Place first 5-inch cake on its cardboard on work surface. ││ Spread 1/2 cup ganache over top of cake and all the way to edge. ││ Spread 2 tablespoons jam over, leaving 1/2-inch chocolate border at edge. ││ Drop 1/3 cup white chocolate frosting by spoonfuls over jam. ││ Gently spread frosting over jam, leaving 1/2-inch chocolate border at edge. ││ Rub cocoa over second 6-inch cardboard. ││ Cut around sides of second 5-inch cake to loosen. ││ Place cardboard, cocoa side down, over pan. ││ Turn cake out onto cardboard. ││ Peel off parchment. ││ Slide cake off cardboard and onto filling on first 5-inch cake. ││ Chill all cakes 1 hour to set filling. ││ Place 12-inch tiered cake on its cardboard on revolving cake stand. ││ Spread 2 2/3 cups frosting over top and sides of cake as a first coat. ││ Refrigerate cake. ││ Place 8-inch tiered cake on its cardboard on cake stand. ││ Spread 1 1/4 cups frosting over top and sides of cake as a first coat. ││ Refrigerate cake. ││ Place 5-inch tiered cake on its cardboard on cake stand. ││ Spread 3/4 cup frosting over top and sides of cake as a first coat. ││ Refrigerate all cakes until first coats of frosting set, about 1 hour. ││ (Cakes can be made to this point up to 1 day ahead; cover and keep refrigerate.) ││ Prepare second batch of frosting, using remaining frosting ingredients and following directions for first batch. ││ Spoon 2 cups frosting into pastry bag fitted with small star tip. ││ Place 12-inch cake on its cardboard on large flat platter. ││ Place platter on cake stand. ││ Using icing spatula, spread 2 1/2 cups frosting over top and sides of cake; smooth top. ││ Using filled pastry bag, pipe decorative border around top edge of cake. ││ Refrigerate cake on platter. ││ Place 8-inch cake on its cardboard on cake stand. ││ Using icing spatula, spread 1 1/2 cups frosting over top and sides of cake; smooth top. ││ Using pastry bag, pipe decorative border around top edge of cake. ││ Refrigerate cake on its cardboard. ││ Place 5-inch cake on its cardboard on cake stand. ││ Using icing spatula, spread 3/4 cup frosting over top and sides of cake; smooth top. ││ Using pastry bag, pipe decorative border around top edge of cake, spooning more frosting into bag if necessary. ││ Refrigerate cake on its cardboard. ││ Keep all cakes refrigerated until frosting sets, about 2 hours. ││ (Can be prepared 2 days ahead. ││ Cover loosely; keep refrigerated.) ││ Place 12-inch cake on platter on work surface. ││ Press 1 wooden dowel straight down into and completely through center of cake. ││ Mark dowel 1/4 inch above top of frosting. ││ Remove dowel and cut with serrated knife at marked point. ││ Cut 4 more dowels to same length. ││ Press 1 cut dowel back into center of cake. ││ Press remaining 4 cut dowels into cake, positioning 3 1/2 inches inward from cake edges and spacing evenly. ││ Place 8-inch cake on its cardboard on work surface. ││ Press 1 dowel straight down into and completely through center of cake. ││ Mark dowel 1/4 inch above top of frosting. ││ Remove dowel and cut with serrated knife at marked point. ││ Cut 3 more dowels to same length. ││ Press 1 cut dowel back into center of cake. ││ Press remaining 3 cut dowels into cake, positioning 2 1/2 inches inward from edges and spacing evenly. ││ Using large metal spatula as aid, place 8-inch cake on its cardboard atop dowels in 12-inch cake, centering carefully. ││ Gently place 5-inch cake on its cardboard atop dowels in 8-inch cake, centering carefully. ││ Using citrus stripper, cut long strips of orange peel from oranges. ││ Cut strips into long segments. ││ To make orange peel coils, wrap peel segment around handle of wooden spoon; gently slide peel off handle so that peel keeps coiled shape. ││ Garnish cake with orange peel coils, ivy or mint sprigs, and some berries. ││ (Assembled cake can be made up to 8 hours ahead. ││ Let stand at cool room temperature.) ││ Remove top and middle cake tiers. ││ Remove dowels from cakes. ││ Cut top and middle cakes into slices. ││ To cut 12-inch cake: Starting 3 inches inward from edge and inserting knife straight down, cut through from top to bottom to make 6-inch-diameter circle in center of cake. ││ Cut outer portion of cake into slices; cut inner portion into slices and serve with strawberries. │└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘126 rows in set. Elapsed: 0.011 sec. Processed 8.19 thousand rows, 5.34 MB (737.75 thousand rows/s., 480.59 MB/s.)
Online playground
The dataset is also available in the Playground.
当前内容版权归 ClickHouse 或其关联方所有，如需对内容或内容相关联开源项目进行关注与资助，请访问 ClickHouse .
