DVD Rental Database#

This example translates a query from the DVD Rental Sample Database. It covers most of the more advanced features that have been implemented.

Start the database#

Using Docker, start the database by running:

docker run -p 5001:5432 moertel/postgresql-sample-dvdrental@sha256:e35f8dc4011d053777631208c85e3976a422b65e12383579d8a856a7849082c5

from the terminal. To describe the database, run

psql -h localhost -p 5001 -U postgres -d dvdrental -c "\d+"

from a separate terminal window. Leave out the last part (-c "\d+") to query the database manually.

Query to translate#

We will use a small query to describe rental transactions in the database:

Query returning DVD rental transactions.#

SELECT customer_id,
       inventory.film_id,
       film_category.category_id,
       staff_id,
       store_id,
       rental_date,
       return_date
FROM rental
LEFT JOIN inventory ON rental.inventory_id = inventory.inventory_id
LEFT JOIN film_category on film_category.film_id = inventory.film_id;

The query above shows who rented what and when, what store they rented from and from whom.

Randomly sampled rows from the query. The first column is the record index in the query.#
	customer_id	film_id	staff_id	store_id	rental_date	return_date
9564	385	129	1	2	2005-07-31 02:40:37	2005-08-01 01:59:37
14933	328	158	1	1	2005-08-22 05:53:52	2005-08-27 06:20:52
544	506	448	2	1	2005-05-28 07:24:28	2005-06-06 01:31:28
1869	206	786	2	1	2005-06-17 02:27:03	2005-06-20 22:41:03
928	421	743	1	2	2005-05-30 12:53:01	2005-05-31 14:28:01

Configuration files#

The database has a few quirks, which are managed by configuration. See the Translator Configuration Files page to learn more about config files.

Translation configuration, mapping, and definition of the categories.#

################################################################################
# For help, see https://rics.readthedocs.io                                    #
################################################################################
[translator]
fmt = "{id}:{name}[ {last_name}]"

# ------------------------------------------------------------------------------
# Name-to-source mapping configuration. Binds names to source, eg 'cute_animals'
# -> 'my_database.animals'. Overrides take precedence over scoring logic.
[translator.mapping]
unmapped_values_action = "raise"
score_function = "modified_hamming"

[[translator.mapping.score_function_heuristics]]
function = "like_database_table"

[[translator.mapping.filter_functions]]
function = "require_regex_match"
regex = ".*_id$"
where = "name"

################################################################################
# Fetching configuration.
################################################################################
[fetching.MemoryFetcher.data.category]
id = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]
name = [
    "Action", "Animation",
    "Children", "Classics", "Comedy",
    "Documentary", "Drama",
    "Family", "Foreign",
    "Games", "Horror", "Music", "New",
    "Sci-Fi", "Sports", "Travel"
]

Configuration for fetching SQL data.#

[fetching.SqlFetcher]
connection_string = "postgresql+pg8000://postgres@localhost:5001/dvdrental"
blacklist_tables = [ "category" ]  # Let's pretend this table is off-limits

# ------------------------------------------------------------------------------
# Placeholder mapping configuration. Binds actual names in sources (such as
# column names in an SQL table) to wanted names, eg id = 'animal_id'.
[fetching.mapping]
unmapped_values_action = "ignore"

[[fetching.mapping.filter_functions]]
function = "banned_substring"
substrings = ["p2007"]
where = "context"

[[fetching.mapping.score_function_heuristics]]
function = "value_fstring_alias"
fstring = "first_{value}"
[[fetching.mapping.score_function_heuristics]]
function = "value_fstring_alias"
fstring = "{context}_{value}"

[fetching.mapping.overrides.addess]
code = "postal_code"
[fetching.mapping.overrides.film]
name = "title"

Translating#

Translating now becomes a simple matter. The following snippet is a test case which translates all of the ~16000 rows returned by the query, verifying a random sample of 5 rows.

import pandas as pd
import pytest

from rics.translation import Translator

from .conftest import DATE_COLUMNS, DVD_RENTAL_SKIP_REASON, wait_for_dvdrental


@pytest.fixture(scope="module")
def translator():
    extra_fetchers = ["tests/translation/dvdrental/sql-fetcher.toml"]
    config = "tests/translation/dvdrental/translation.toml"
    return Translator.from_config(config, extra_fetchers)


@pytest.mark.skipif(not wait_for_dvdrental(), reason=DVD_RENTAL_SKIP_REASON)
def test_dvd_rental(translator):
    expected = pd.read_csv("tests/translation/dvdrental/translated.csv", index_col=0, parse_dates=DATE_COLUMNS)

    with open("tests/translation/dvdrental/query.sql") as f:
        query = f.read()

    engine = translator._fetcher.fetchers[-1]._engine  # The SQL fetcher was given last
    df: pd.DataFrame = pd.read_sql(query, con=engine)
    translator.translate(df, inplace=True)

    assert (df.select_dtypes("datetime").columns == DATE_COLUMNS).all()
    actual = df.sample(len(expected), random_state=0)
    assert expected.equals(actual), actual.to_string()

The translated rows are:

The rows translated. Dates are not translated, nor is the first (row number/index) column.#
	customer_id	film_id	category_id	staff_id	store_id	rental_date	return_date
12503	29:ANGELA HERNANDEZ	235:DIVIDE MONSTER	14:Sci-Fi	1:Mike Hillyer	2:MySQL	2005-08-18 13:20:13	2005-08-22 12:55:13
13357	565:JAIME NETTLES	191:CROOKED FROGMEN	3:Children	2:Jon Stephens	1:MySakila	2005-08-19 21:07:54	2005-08-20 17:29:54
1638	259:LENA JENSEN	643:ORIENT CLOSER	7:Drama	2:Jon Stephens	2:MySQL	2005-06-16 08:46:26	2005-06-24 14:39:26
7129	574:JULIAN VEST	235:DIVIDE MONSTER	14:Sci-Fi	1:Mike Hillyer	2:MySQL	2005-07-27 06:33:06	2005-07-28 06:15:06
5931	258:MYRTLE FLEMING	739:ROCKY WAR	7:Drama	2:Jon Stephens	2:MySQL	2005-07-10 22:11:04	2005-07-19 18:12:04