Skip to content

Latest commit

 

History

History

examples

Examples

Running the example

java -Dspark.master=local[*] -cp metorikku-standalone.jar com.yotpo.metorikku.Metorikku -c examples/movies.yaml

The Data

Let's analyze a small movie lens data set!

We have our movies.csv in our file_inputs folder

+-------+-------------------------------------------------------------------------------+-------------------------------------------+
|movieId|title                                                                          |genres                                     |
+-------+-------------------------------------------------------------------------------+-------------------------------------------+
|1      |Toy Story (1995)                                                               |Adventure|Animation|Children|Comedy|Fantasy|
|2      |Jumanji (1995)                                                                 |Adventure|Children|Fantasy                 |
|3      |Grumpier Old Men (1995)                                                        |Comedy|Romance                             |
|4      |Waiting to Exhale (1995)                                                       |Comedy|Drama|Romance                       |
|5      |Father of the Bride Part II (1995)                                             |Comedy                                     |
|6      |Heat (1995)                                                                    |Action|Crime|Thriller                      |
|7      |Sabrina (1995)                                                                 |Comedy|Romance                             |
|8      |Tom and Huck (1995)                                                            |Adventure|Children                         |
|9      |Sudden Death (1995)                                                            |Action                                     |
|10     |GoldenEye (1995)                                                               |Action|Adventure|Thriller                  |
|11     |American President, The (1995)                                                 |Comedy|Drama|Romance                       |
|12     |Dracula: Dead and Loving It (1995)                                             |Comedy|Horror                              |
|13     |Balto (1995)                                                                   |Adventure|Animation|Children               |
|14     |Nixon (1995)                                                                   |Drama                                      |
|15     |Cutthroat Island (1995)                                                        |Action|Adventure|Romance                   |

and also our ratings.csv:

+------+-------+------+----------+
|userId|movieId|rating|timestamp |
+------+-------+------+----------+
|1     |31     |2.5   |1260759144|
|1     |1029   |3.0   |1260759179|
|1     |1061   |3.0   |1260759182|
|1     |1129   |2.0   |1260759185|
|1     |1172   |4.0   |1260759205|
|1     |1263   |2.0   |1260759151|
|1     |1287   |2.0   |1260759187|
|1     |1293   |2.0   |1260759148|
|1     |1339   |3.5   |1260759125|
|1     |1343   |2.0   |1260759131|
|1     |1371   |2.5   |1260759135|
|1     |1405   |1.0   |1260759203|
|1     |1953   |4.0   |1260759191|
|1     |2105   |4.0   |1260759139|
|1     |2150   |3.0   |1260759194|
|1     |2193   |2.0   |1260759198|

Configuration

We are registering our data sources, our variables and our output configurations Here's our example configuration:

# The MQL file path
metrics:
  - examples/movies_metric.yaml

inputs:
 movies: examples/file_inputs/movies.csv
 ratings: examples/file_inputs/ratings.csv

# Set custom variables that would be accessible from the SQL
variables:
 myFavoriteMovie: 'Princess Bride, The (1987)'

output:
  file:
    dir: examples/output

# If set to true, triggers Explain before saving
explain: true

# Set Log Level : ALL, DEBUG, ERROR, FATAL, INFO, OFF, TRACE, WARN
logLevel: WARN

# Set Application Name to have app name prefix in spark instrumentation counters
appName: moviesApp

# Shows a Preview of the output
showPreviewLines: 100

# Location to store dataframe checkpoints
checkpointStorageLocation: /tmp/

The Metric

Our metric file is as follows:

steps:
- dataFrameName: moviesWithRatings
  checkpoint: true
  sql:
    SELECT userid,
           movies.movieid,
           rating,
           timestamp,
           title,
           genres
    FROM ratings
    JOIN movies ON ratings.movieid = movies.movieid
- dataFrameName: fantasyMoviesWithRatings
  sql:
    SELECT movieId,
           cast(rating AS float) AS rating,
           timestamp,
           title,
           genres
    FROM moviesWithRatings
    WHERE genres LIKE '%Fantasy%'
- dataFrameName: topFantasyMovies
  sql:
    SELECT movieId,
           title,
           avg(rating) AS averageRating
    FROM fantasyMoviesWithRatings
    GROUP BY movieId,
             title
    ORDER BY averageRating DESC
    LIMIT 100
- dataFrameName: myFavoriteMovieRated
  sql:
    SELECT *
    FROM topFantasyMovies
    WHERE title = ${myFavoriteMovie}
output:
- dataFrameName: topFantasyMovies
  outputType: Parquet
  outputOptions:
    saveMode: Overwrite
    path: topFantasyMovies.parquet

Results

We are running each step sequentially and here are the results:

Step1 - moviesWithRatings

+------+-------+------+----------+----------------------------------------------------------------------------+-----------------------------------------------+
|userId|movieId|rating|timestamp |title                                                                       |genres                                         |
+------+-------+------+----------+----------------------------------------------------------------------------+-----------------------------------------------+
|1     |31     |2.5   |1260759144|Dangerous Minds (1995)                                                      |Drama                                          |
|1     |1029   |3.0   |1260759179|Dumbo (1941)                                                                |Animation|Children|Drama|Musical               |
|1     |1061   |3.0   |1260759182|Sleepers (1996)                                                             |Thriller                                       |
|1     |1129   |2.0   |1260759185|Escape from New York (1981)                                                 |Action|Adventure|Sci-Fi|Thriller               |
|1     |1172   |4.0   |1260759205|Cinema Paradiso (Nuovo cinema Paradiso) (1989)                              |Drama                                          |
|1     |1263   |2.0   |1260759151|Deer Hunter, The (1978)                                                     |Drama|War                                      |
|1     |1287   |2.0   |1260759187|Ben-Hur (1959)                                                              |Action|Adventure|Drama                         |
|1     |1293   |2.0   |1260759148|Gandhi (1982)                                                               |Drama                                          |
|1     |1339   |3.5   |1260759125|Dracula (Bram Stoker's Dracula) (1992)                                      |Fantasy|Horror|Romance|Thriller                |
|1     |1343   |2.0   |1260759131|Cape Fear (1991)                                                            |Thriller                                       |
|1     |1371   |2.5   |1260759135|Star Trek: The Motion Picture (1979)                                        |Adventure|Sci-Fi                               |
|1     |1405   |1.0   |1260759203|Beavis and Butt-Head Do America (1996)                                      |Adventure|Animation|Comedy|Crime

Step2 - fantasyMoviesWithRatings

+-------+------+----------+----------------------------------------------------------------------------------------------+---------------------------------------------------------+
|movieId|rating|timestamp |title                                                                                         |genres                                                   |
+-------+------+----------+----------------------------------------------------------------------------------------------+---------------------------------------------------------+
|1339   |3.5   |1260759125|Dracula (Bram Stoker's Dracula) (1992)                                                        |Fantasy|Horror|Romance|Thriller                          |
|2193   |2.0   |1260759198|Willow (1988)                                                                                 |Action|Adventure|Fantasy                                 |
|2294   |2.0   |1260759108|Antz (1998)                                                                                   |Adventure|Animation|Children|Comedy|Fantasy              |
|2968   |1.0   |1260759200|Time Bandits (1981)                                                                           |Adventure|Comedy|Fantasy|Sci-Fi                          |
|265    |5.0   |835355697 |Like Water for Chocolate (Como agua para chocolate) (1992)                                    |Drama|Fantasy|Romance                                    |
|314    |4.0   |835356044 |Secret of Roan Inish, The (1994)                                                              |Children|Drama|Fantasy|Mystery                           |
|317    |2.0   |835355551 |Santa Clause, The (1994)                                                                      |Comedy|Drama|Fantasy                                     |
|367    |3.0   |835355619 |Mask, The (1994)                                                                              |Action|Comedy|Crime|Fantasy                              |
|405    |2.0   |835356246 |Highlander III: The Sorcerer (a.k.a. Highlander: The Final Dimension) (1994)                  |Action|Fantasy                                           |
|410    |3.0   |835355532 |Addams Family Values (1993)                                                                   |Children|Comedy|Fantasy                                  |
|485    |3.0   |835355918 |Last Action Hero (1993)                                                                       |Action|Adventure|Comedy|Fantasy                          |
|551    |5.0   |835355767 |Nightmare Before Christmas, The (1993)                                                        |Animation|Children|Fantasy|Musical                       |
|587    |3.0   |835355779 |Ghost (1990)                                                                                  |Comedy|Drama|Fantasy|Romance|Thriller                    |
|661    |4.0   |835356141 |James and the Giant Peach (1996)                                                              |Adventure|Animation|Children|Fantasy|Musical             |

Step3 - topFantasyMovies

+-------+------------------------------------------------------------------------------------------------------------------+------------------+
|movieId|title                                                                                                             |averageRating     |
+-------+------------------------------------------------------------------------------------------------------------------+------------------+
|59392  |Stargate: The Ark of Truth (2008)                                                                                 |5.0               |
|3216   |Vampyros Lesbos (Vampiras, Las) (1971)                                                                            |5.0               |
|140747 |16 Wishes (2010)                                                                                                  |5.0               |
|3837   |Phantasm II (1988)                                                                                                |5.0               |
|27792  |Saddest Music in the World, The (2003)                                                                            |5.0               |
|118468 |Mei and the Kittenbus (2002)                                                                                      |5.0               |
|2086   |One Magic Christmas (1985)                                                                                        |5.0               |
|8254   |Arizona Dream (1993)                                                                                              |5.0               |
|4789   |Phantom of the Paradise (1974)                                                                                    |5.0               |
|74089  |Peter Pan (1960)                                                                                                  |5.0               |
|96832  |Holy Motors (2012)                                                                                                |5.0               |
|106471 |One Piece Film: Strong World (2009)                                                                               |5.0               |
|101962 |Wolf Children (Okami kodomo no ame to yuki) (2012)                                                                |5.0               |
|3612   |The Slipper and the Rose: The Story of Cinderella (1976)                                                          |5.0               |
|4591   |Erik the Viking (1989)                                                                                            |5.0               |
|7302   |Thief of Bagdad, The (1924)                                                                                       |5.0               |
|26749  |Prospero's Books (1991)                                                                                           |5.0               |
|99764  |It's Such a Beautiful Day (2012)                                                                                  |5.0               |
|53887  |O Lucky Man! (1973)                                                                                               |5.0               |
|95113  |Eaux d'artifice (1953)                                                                                            |5.0               |
|72356  |Partly Cloudy (2009)                                                                                              |4.75              |
|50641  |House (Hausu) (1977)                                                                                              |4.75              |
|114552 |Boxtrolls, The (2014)                                                                                             |4.5               |
|62764  |Black Moon (1975)                                                                                                 |4.5               |
|136016 |The Good Dinosaur (2015)                                                                                          |4.5               |
|80748  |Alice in Wonderland (1933)                                                                                        |4.5               |
|110645 |Witching and Bitching (Brujas de Zugarramurdi, Las) (2014)                                                        |4.5               |
|27156  |Neon Genesis Evangelion: The End of Evangelion (Shin seiki Evangelion Gekijô-ban: Air/Magokoro wo, kimi ni) (1997)|4.5               |
|50011  |Bothersome Man, The (Brysomme mannen, Den) (2006)                                                                 |4.5               |
|68835  |Were the World Mine (2008)                                                                                        |4.5               |
|4927   |Last Wave, The (1977)                                                                                             |4.5               |

Step2 - myFavoriteMovieRated

+-------+--------------------------+-----------------+
|movieId|title                     |averageRating    |
+-------+--------------------------+-----------------+
|1197   |Princess Bride, The (1987)|4.208588957055214|
+-------+--------------------------+-----------------+

Testing our metric

Running the test

java -Dspark.master=local[*] -cp metorikku-standalone.jar com.yotpo.metorikku.MetorikkuTester -t examples/movies_test.yaml

Metorikku also supports testing your logic, using MetorikkuTester.

Metorikku Tester expects a test-settings YAML file:

metric: movies_metric.yaml
mocks:
- name: movies
  path: mocks/movies.jsonl
- name: ratings
  path: mocks/ratings.jsonl
params:
  variables:
    myFavoriteMovie: Lord of the Rings, The (1978)
tests:
  myFavoriteMovieRated:
  - movieId: 1
    title: Lord of the Rings, The (1978)
    averageRating: 2.5

A test settings file consists of the following:

  • Our metric file which has our business logic
  • A set of mocks in the format of JSONL
  • A set of variables if needed to be used inside our SQL queries
  • A set of expected results

You can run MetorikkuTester as a stand alone application or a spark application