{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# TPC-H Query 5 – Tables\n", "\n", "This IPython notebook is part of a series of tutorials that introduce how data algebra facilitates querying data from multiple sources and in different structures, on the example of a modified [TPC-H][] query. \n", "\n", "The tutorials assume basic familiarity with our library; we suggest working through our [Hello_World][] tutorial first. They also assume basic knowledge of relational data, RDF and XML. \n", "\n", "In some cases, later parts of the tutorial assume knowledge of concepts introduced in earlier parts, so it is best to work through them in the listed sequence:\n", "\n", "- [1-Introduction][]: Introduces this series of tutorials, TPC-H, the TPC-H query 5 and our modifications to it.\n", "- **[2-Tables][] (this tutorial)**: Introduces our representation of tabular data, on the example of CSV.\n", "- [3-Graphs][]: Introduces our representation of RDF-style tabular graph data, on the example of Turtle.\n", "- [4-Hierarchies][]: Introduces our representation of hierarchical data, on the example of XML.\n", "- [5-Query][]: Brings it all together and explains the whole query.\n", "\n", "[TPC-H]: (TPC-H Benchmark Main Page)\n", "[Hello_World]: <../Hello_World.ipynb> (IPython Notebook: Hello World)\n", "[1-Introduction]: <1-Introduction.ipynb> (IPython Notebook: TPC-H Query 5 - Introduction)\n", "[2-Tables]: <2-Tables.ipynb> (IPython Notebook: TPC-H Query 5 - Tables)\n", "[3-Graphs]: <3-Graphs.ipynb> (IPython Notebook: TPC-H Query 5 - Graphs)\n", "[4-Hierarchies]: <4-Hierarchies.ipynb> (IPython Notebook: TPC-H Query 5 - Hierarchies)\n", "[5-Query]: <5-Query.ipynb> (IPython Notebook: TPC-H Query 5 - Query)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Tabular Data In the Query\n", "\n", "In our [modified query 5][], the tables `customer`, `orders` and `lineitem` are provided as CSV data, the original tables `region` and `nation` are provided as XML data and the original table `supplier` is provided as RDF graph. In this tutorial, we present our representation of tabular data, how to import it from the CSV format (see also [RFC 4180][]) and how to execute simple operations on it like [`project`][] and [`restrict`][].\n", "\n", "As examples, we use two subqueries from the modified query 5, one slightly modified. We treat them here as stand-alone queries, so that it is easier to understand what they do.\n", "\n", "\n", "[modified query 5]: <1-Introduction.ipynb#The-Modified-Query> (IPython Notebook: TPC-H Query 5 - Introduction - The Modified Query)\n", "[RFC 4180]: (RFC 4180 - CSV Format)\n", "[`project`]: (function algebraixlib.algebras.clans.project)\n", "[`restrict`]: (function algebraixlib.algebras.sets.restrict)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### The `customers_joined_projected` Subquery\n", "\n", "Our first example query loads the table `customer` and selects the columns `custkey` and `nationkey` for all customers in the nations given by the table `nations`. In the full query, the result of this subquery is then referenced as `customers_joined_projected`.\n", "\n", "``` sql\n", "(\n", " SELECT \n", " custkey, nationkey, nationname\n", " FROM \n", " customer\n", " JOIN\n", " nations\n", " ON \n", " customer.nationkey = nations.nationkey\n", ") AS customers_joined_projected\n", "```\n", " \n", "This example is a simplified version of the following subquery in our modified query 5. To keep this example simple, we replaced the pseudo-subquery (in XQuery) that provides the result that is named `nations` with a hardcoded table `nations`. The translation of the XQuery subquery is shown later, in [4-Hierarchies][]. \n", "\n", "``` sql\n", "(\n", " SELECT \n", " custkey, nationkey, nationname\n", " FROM \n", " customer\n", " JOIN (\n", " -- This is a pseudo-subquery in XQuery. It extracts a list of nations\n", " -- for the desired region, and for every nation it returns an XML 'row' \n", " -- with the columns 'nationkey' and 'nationname' (renamed from 'name').\n", " -- This forms an XML pseudo-'table' named 'nations' that is then joined \n", " -- with 'customer'.\n", " for $x in doc(\"regions.xml\")/regions/region[name=\"MIDDLE EAST\"]/nation\n", " return {$x/nationkey}{data($x/name)}\n", " ) AS nations\n", " ON\n", " customer.nationkey = nations.nationkey\n", ") AS customers_joined_projected\n", "```\n", "\n", "[4-Hierarchies]: <4-Hierarchies.ipynb> (IPython Notebook: TPC-H Query 5 - Hierarchies)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### The `orders_restricted_projected` Subquery\n", "\n", "Our second example query loads the table `orders` and selects the columns `orderkey` and `custkey` for all orders in the given date range. In the full query, the result of this subquery is then referenced as `orders_restricted_projected`.\n", "\n", "``` sql\n", "(\n", " SELECT \n", " orderkey, custkey\n", " FROM \n", " orders\n", " WHERE \n", " startdate <= orders.orderdate and orders.orderdate < enddate \n", ") AS orders_restricted_projected\n", "```\n", "\n", "This example is taken directly from the corresponding subquery in our modified query 5." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Representation of Tables\n", "\n", "We represent tables, rows and columns as constructs built of sets and couplets. \n", "\n", "**Mathematical concepts:**\n", "\n", "- A *couplet* is an ordered pair $a{\\mapsto}b \\in A \\times A$ with $a, b \\in A$ (using the Kuratowski definition $a{\\mapsto}b := \\{\\{a\\}, \\{a, b\\}\\}$). We call $a$ the *left* part of the couplet, and $b$ the *right* part.\n", "- A *relation* is a set of couplets. If the relation is (left-)functional, it is a function from the left parts of the couplets to the right parts. \n", "- A *clan* is a set of relations. \n", "\n", "**Data model:**\n", "\n", "A table can be modeled as a clan. In detail:\n", "\n", "- A *cell* in a table is represented by a couplet, where the left part is the column (column name), and the right part is the cell value. \n", " - In our data algebra notation, this could look like $custkey{\\mapsto}15$.\n", "- A *row* is a relation (a set of couplets). Because every row has only a single value in a given column and no two column names are the same (by definition), row relations are always left-functional and are a function from column names to the cell values.\n", " - In our data algebra notation, this could look like $\\{custkey{\\mapsto}15, phone{\\mapsto}\\text{33-687-542-7601}\\}$.\n", "- A *table* is a set of rows/functions.\n", " - In our data algebra notation, this could look like $\\{\\{custkey{\\mapsto}1, phone{\\mapsto}\\text{25-989-741-2988}\\}, \\{custkey{\\mapsto}15, phone{\\mapsto}\\text{33-687-542-7601}\\}\\}$.\n", "\n", "All three constructs (couplets/cells, relations/rows, clans/tables) are associated with a number of operations. See also our [Hello_World][] introduction.\n", "\n", "[Hello_World]: <../Hello_World.ipynb> (IPython Notebook: Hello World)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# The `customer` Table\n", "\n", "In the first example query, we import the CSV table `customer`, select the customers in a given set of nations `nationkeys` and project the useful columns `custkey`, `nationkey` and `nationname`. The associated SQL query looks like this:\n", "\n", "``` sql\n", "SELECT \n", " custkey, nationkey, nationname\n", "FROM \n", " customer\n", "JOIN\n", " nations\n", "ON \n", " customer.nationkey = nations.nationkey\n", "```\n", "\n", "Below follows example code that shows how to use our API to import the table `customer` from a CSV file into our [`MathObject`][] representation.\n", "\n", "- [`algebraixlib.io.csv`][] provides utilities for importing and exporting CSV data.\n", "- [`iprint_latex`][] is a utility that prints our [`MathObject`][]s in LaTeX format in IPython notebooks. The `short=True` argument tells it to create abbreviated output. It shortenes long strings, indicated by the appended ellipses '...'. It also only prints the first few elements of sets and replaces the remainder with an ellipsis, followed by a number in parentheses that indicates how many objects have been ommitted.\n", "- `customer_types` defines the types for columns that are not strings. (It does not select columns to be imported; all columns not listed here are imported as strings.) It is passed as argument to [`import_csv`][], which then processes the indicated CSV columns accordingly.\n", "\n", "[`MathObject`]: (class algebraixlib.mathobjects.mathobject.MathObject)\n", "[`algebraixlib.io.csv`]: (module algebraixlib.io.csv)\n", "[`iprint_latex`]: (function algebraixlib.util.latexprinter.iprint_latex)\n", "[`import_csv`]: (function algebraixlib.io.csv.import_csv)" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/latex": [ "$$customers = \\left\\{\\begin{array}{l}\\left\\{\\color{red}{\\mbox{'acctbal'}}{\\mapsto}{\\color{blue}{\\mbox{-234.12}}},\\ \\color{red}{\\mbox{'address'}}{\\mapsto}{\\color{blue}{\\mbox{',wSZXdVR ...}}},\\ \\color{red}{\\mbox{'comment'}}{\\mapsto}{\\color{blue}{\\mbox{'x-ray fin...}}},\\ \\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{125}}},\\ \\text{...}(4)\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'acctbal'}}{\\mapsto}{\\color{blue}{\\mbox{-272.6}}},\\ \\color{red}{\\mbox{'address'}}{\\mapsto}{\\color{blue}{\\mbox{'PkWS 3HlX...}}},\\ \\color{red}{\\mbox{'comment'}}{\\mapsto}{\\color{blue}{\\mbox{'ckages. r...}}},\\ \\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{11}}},\\ \\text{...}(4)\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'acctbal'}}{\\mapsto}{\\color{blue}{\\mbox{-362.86}}},\\ \\color{red}{\\mbox{'address'}}{\\mapsto}{\\color{blue}{\\mbox{'putjlmskx...}}},\\ \\color{red}{\\mbox{'comment'}}{\\mapsto}{\\color{blue}{\\mbox{'ithely fi...}}},\\ \\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{72}}},\\ \\text{...}(4)\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'acctbal'}}{\\mapsto}{\\color{blue}{\\mbox{-551.37}}},\\ \\color{red}{\\mbox{'address'}}{\\mapsto}{\\color{blue}{\\mbox{'7yiheXNSp...}}},\\ \\color{red}{\\mbox{'comment'}}{\\mapsto}{\\color{blue}{\\mbox{'ages. fur...}}},\\ \\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{98}}},\\ \\text{...}(4)\\right\\},\\\\\n", "\\text{...}(146)\\end{array}\\right\\}$$" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "import algebraixlib.io.csv as csv\n", "from algebraixlib.util.latexprinter import iprint_latex\n", "\n", "customer_types = {'custkey': int, 'nationkey': int, 'acctbal': float}\n", "customers = csv.import_csv('customer.csv', customer_types)\n", "iprint_latex('customers', short=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Next we create a mockup of the result of the pseudo-subquery in XQuery (which is explained in [4-Hierarchies][]). `nations` is a clan (table) with the lefts (columns) `nationkey` and `nationname` and for our mockup we add three rows/nations.\n", "\n", "A clan is a [`Set`][] of `Set`s of [`Couplet`][]s (a set of relations).\n", "\n", "- [`algebraixlib.mathobjects`][] provides the classes that represent our data.\n", "\n", "[4-Hierarchies]: <4-Hierarchies.ipynb> (IPython Notebook: TPC-H Query 5 - Hierarchies)\n", "[`Set`]: (class algebraixlib.mathobjects.set.Set)\n", "[`Couplet`]: (class algebraixlib.mathobjects.couplet.Couplet)\n", "[`algebraixlib.mathobjects`]: (package algebraixlib.mathobjects)" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/latex": [ "$$nations = \\left\\{\\begin{array}{l}\\left\\{\\color{red}{\\mbox{'nationkey'}}{\\mapsto}{\\color{blue}{\\mbox{10}}},\\ \\color{red}{\\mbox{'nationname'}}{\\mapsto}{\\color{blue}{\\mbox{'IRAN'}}}\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'nationkey'}}{\\mapsto}{\\color{blue}{\\mbox{11}}},\\ \\color{red}{\\mbox{'nationname'}}{\\mapsto}{\\color{blue}{\\mbox{'IRAQ'}}}\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'nationkey'}}{\\mapsto}{\\color{blue}{\\mbox{4}}},\\ \\color{red}{\\mbox{'nationname'}}{\\mapsto}{\\color{blue}{\\mbox{'EGYPT'}}}\\right\\}\\end{array}\\right\\}$$" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "from algebraixlib.mathobjects import Set, Couplet\n", "\n", "nations = Set(Set(Couplet('nationkey', 4), Couplet('nationname', 'EGYPT')),\n", " Set(Couplet('nationkey', 10), Couplet('nationname', 'IRAN')),\n", " Set(Couplet('nationkey', 11), Couplet('nationname', 'IRAQ')))\n", "iprint_latex('nations')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We now join the two tables. They have only one column with matching names, which is the column on which they are to be joined. (While this is a lucky coincidence, the principle of the operation wouldn't be affected if we had to rename a column or two.)\n", "\n", "Mathematically, we represent such a join (where the column names align just right) as [functional union][]:\n", "\n", "$$\n", "\\begin{align*}\n", "\\text{Functional union}:\\ \n", " &R \\underset{f}{\\cup} Q\n", " &&:=\\begin{cases} \n", " R \\cup Q & \\text{ if } R \\cup Q \\text{ is functional } \\\\\n", " \\text{undefined} & \\text{ otherwise }\n", " \\end{cases}\n", " & \\text{ for } R, Q \\in P(M \\times M)\\\\\n", "\\text{Functional cross-union}:\\ \n", " &\\mathbb{C} \\underset{f}{\\blacktriangledown} \\mathbb{D}\n", " &&:= \\{R \\underset{f}{\\cup} Q\\ : R \\in \\mathbb{C} \\text{ and } Q \\in \\mathbb{D}\\} \n", " & \\text{ for } \\mathbb{C}, \\mathbb{D} \\in P^2(M \\times M)\n", "\\end{align*}\n", "$$\n", "\n", "With this we get:\n", "\n", "$$ \n", "CustomersNations = Customer \\underset{f}{\\blacktriangledown} Nations\n", "$$\n", "\n", "Since only a part of the relations in the `customers` clan have a couplet with a left part of `nationkey` and a right that matches a right in the `nations` clan\\`s `nationkey` lefts, the number of relations goes down from 150 (in the `customers` clan) to 19 (in the result of the join).\n", "\n", "- [`algebraixlib.algebras.clans`][] contains the functions that are related to our algebra of clans.\n", "- [`clans.cross_functional_union`][] executes the functional cross-union ($\\underset{f}{\\blacktriangledown}$).\n", "\n", "[functional union]: (aka left-functional union)\n", "[`algebraixlib.algebras.clans`]: (module algebraixlib.algebras.clans)\n", "[`clans.cross_functional_union`]: (function algebraixlib.algebras.clans.cross_functional_union)" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/latex": [ "$$customers\\_nations = \\left\\{\\begin{array}{l}\\left\\{\\color{red}{\\mbox{'acctbal'}}{\\mapsto}{\\color{blue}{\\mbox{-588.38}}},\\ \\color{red}{\\mbox{'address'}}{\\mapsto}{\\color{blue}{\\mbox{'9mcCK L7r...}}},\\ \\color{red}{\\mbox{'comment'}}{\\mapsto}{\\color{blue}{\\mbox{'rate care...}}},\\ \\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{104}}},\\ \\text{...}(5)\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'acctbal'}}{\\mapsto}{\\color{blue}{\\mbox{-986.96}}},\\ \\color{red}{\\mbox{'address'}}{\\mapsto}{\\color{blue}{\\mbox{'AmKUMlJf2...}}},\\ \\color{red}{\\mbox{'comment'}}{\\mapsto}{\\color{blue}{\\mbox{'ing packa...}}},\\ \\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{128}}},\\ \\text{...}(5)\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'acctbal'}}{\\mapsto}{\\color{blue}{\\mbox{162.57}}},\\ \\color{red}{\\mbox{'address'}}{\\mapsto}{\\color{blue}{\\mbox{'QM5YabAsT...}}},\\ \\color{red}{\\mbox{'comment'}}{\\mapsto}{\\color{blue}{\\mbox{'uickly ca...}}},\\ \\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{132}}},\\ \\text{...}(5)\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'acctbal'}}{\\mapsto}{\\color{blue}{\\mbox{2135.6}}},\\ \\color{red}{\\mbox{'address'}}{\\mapsto}{\\color{blue}{\\mbox{'BhSPlEWGv...}}},\\ \\color{red}{\\mbox{'comment'}}{\\mapsto}{\\color{blue}{\\mbox{'ing to th...}}},\\ \\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{148}}},\\ \\text{...}(5)\\right\\},\\\\\n", "\\text{...}(15)\\end{array}\\right\\}$$" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "import algebraixlib.algebras.clans as clans\n", "\n", "customers_nations = clans.cross_functional_union(customers, nations)\n", "iprint_latex('customers_nations', short=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We then compose the result to project the desired columns. \n", "\n", "When composing a clan with a clan diagonal – a clan with a single relation that contains only couplets where left and right are the same – we remove all couplets with left parts not present in the clan diagonal from all relations, effectively projecting the columns present in the clan diagonal.\n", "\n", "$$ \n", "CustomersNations_{Proj} = CustomersNations \\circ \\{\\{custkey{\\mapsto}custkey, nationkey{\\mapsto}nationkey, nationname{\\mapsto}nationname\\}\\}\n", "$$\n", "\n", "Composition on couplets, relations and clans is defined as follows:\n", "\n", "$$\n", "\\begin{align*}\n", "\\text{Couplet composition}:\\ \n", " &c{\\mapsto}d \\circ a{\\mapsto}b\n", " &&:= \\begin{cases}\n", " a{\\mapsto}d & \\text{if } b = c \\\\\n", " \\text{undefined} & \\text{otherwise}\n", " \\end{cases}\n", " &\\text{for } a, b, c, d \\in M \\\\\n", "\\text{Relation composition}:\\ \n", " &Rel_2 \\circ Rel_1\n", " &&:= \\{cp_2 \\circ cp_1\\ :\\ cp_1 \\in Rel_1,\\ cp_2 \\in Rel_2\\} \n", " &\\text{for } Rel_1,\\ Rel_2 \\in P(M \\times M) \\\\\n", "\\text{Clan composition}:\\ \n", " &Clan_2 \\circ Clan_1\n", " &&:= \\{Rel_2 \\circ Rel_1\\ :\\ Rel_1 \\in Clan_1,\\ Rel_2 \\in Clan_2\\} \n", " &\\text{ for } Clan_1,\\ Clan_2 \\in P^2(M \\times M) \\\\\n", "\\end{align*}\n", "$$\n", "\n", "The number of relations in the clan (19) doesn't change, but the number of couplets in each relation is reduced to the ones that have the desired column names as lefts.\n", "\n", "- [`clans.compose`][] executes the clan composition ($\\circ$).\n", "\n", "[`clans.compose`]: (function algebraixlib.algebras.clans.compose)" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/latex": [ "$$customers\\_nations\\_composed = \\left\\{\\begin{array}{l}\\left\\{\\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{104}}},\\ \\color{red}{\\mbox{'nationkey'}}{\\mapsto}{\\color{blue}{\\mbox{10}}},\\ \\color{red}{\\mbox{'nationname'}}{\\mapsto}{\\color{blue}{\\mbox{'IRAN'}}}\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{105}}},\\ \\color{red}{\\mbox{'nationkey'}}{\\mapsto}{\\color{blue}{\\mbox{10}}},\\ \\color{red}{\\mbox{'nationname'}}{\\mapsto}{\\color{blue}{\\mbox{'IRAN'}}}\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{110}}},\\ \\color{red}{\\mbox{'nationkey'}}{\\mapsto}{\\color{blue}{\\mbox{10}}},\\ \\color{red}{\\mbox{'nationname'}}{\\mapsto}{\\color{blue}{\\mbox{'IRAN'}}}\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{128}}},\\ \\color{red}{\\mbox{'nationkey'}}{\\mapsto}{\\color{blue}{\\mbox{4}}},\\ \\color{red}{\\mbox{'nationname'}}{\\mapsto}{\\color{blue}{\\mbox{'EGYPT'}}}\\right\\},\\\\\n", "\\text{...}(15)\\end{array}\\right\\}$$" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "customers_nations_composed = clans.compose(customers_nations, \n", " Set(Set(Couplet('custkey', 'custkey'), \n", " Couplet('nationkey', 'nationkey'), \n", " Couplet('nationname', 'nationname'))))\n", "iprint_latex('customers_nations_composed', short=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This operation (a composition of a clan with a clan diagonal) is so common that we have a wrapper function for it in the `clans` algebra:\n", "\n", "- [`clans.project`][] accepts a clan as argument, followed by one or more atoms. It creates a clan diagonal from these atoms and composes the clan with it, effectively projecting the columns passed in as arguments.\n", "\n", "[`clans.project`]: (function algebraixlib.algebras.clans.project)" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/latex": [ "$$customers\\_nations\\_projected = \\left\\{\\begin{array}{l}\\left\\{\\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{104}}},\\ \\color{red}{\\mbox{'nationkey'}}{\\mapsto}{\\color{blue}{\\mbox{10}}},\\ \\color{red}{\\mbox{'nationname'}}{\\mapsto}{\\color{blue}{\\mbox{'IRAN'}}}\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{105}}},\\ \\color{red}{\\mbox{'nationkey'}}{\\mapsto}{\\color{blue}{\\mbox{10}}},\\ \\color{red}{\\mbox{'nationname'}}{\\mapsto}{\\color{blue}{\\mbox{'IRAN'}}}\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{110}}},\\ \\color{red}{\\mbox{'nationkey'}}{\\mapsto}{\\color{blue}{\\mbox{10}}},\\ \\color{red}{\\mbox{'nationname'}}{\\mapsto}{\\color{blue}{\\mbox{'IRAN'}}}\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{128}}},\\ \\color{red}{\\mbox{'nationkey'}}{\\mapsto}{\\color{blue}{\\mbox{4}}},\\ \\color{red}{\\mbox{'nationname'}}{\\mapsto}{\\color{blue}{\\mbox{'EGYPT'}}}\\right\\},\\\\\n", "\\text{...}(15)\\end{array}\\right\\}$$" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "customers_nations_projected = clans.project(customers_nations, 'custkey', 'nationkey', 'nationname')\n", "iprint_latex('customers_nations_projected', short=True)" ] }, { "cell_type": "markdown", "metadata": { "collapsed": true }, "source": [ "# The `orders` Table\n", "\n", "In the second example query of this notebook, we import the CSV table `orders`, select the orders in a given date range (from `startdate` to `enddate`) and project the useful columns `orderkey` and `custkey`. The associated SQL query looks like this:\n", "\n", "``` sql\n", "SELECT \n", " orderkey, custkey\n", "FROM \n", " orders\n", "WHERE \n", " startdate <= orders.orderdate and orders.orderdate < enddate \n", "```\n", "\n", "First, we again import the table `orders` from a CSV file. \n", "\n", "- `order_types` again defines the types for columns that need special handling. All columns not listed here are imported as strings.\n", "- `read_date` shows how custom functions can be used instead of standard types to perform special conversions, on the example of the column `orderdate`. For each row, the function receives the CSV column value as string and returns what is needed (a `datetime` in this case)." ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The display of this data may take a while. 15 s is not unusual... \n", "\n" ] }, { "data": { "text/latex": [ "$$orders = \\left\\{\\begin{array}{l}\\left\\{\\color{red}{\\mbox{'clerk'}}{\\mapsto}{\\color{blue}{\\mbox{'Clerk#000...}}},\\ \\color{red}{\\mbox{'comment'}}{\\mapsto}{\\color{blue}{\\mbox{'es haggle...}}},\\ \\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{32}}},\\ \\color{red}{\\mbox{'orderdate'}}{\\mapsto}{\\color{blue}{\\mbox{datetime.d...}}},\\ \\text{...}(5)\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'clerk'}}{\\mapsto}{\\color{blue}{\\mbox{'Clerk#000...}}},\\ \\color{red}{\\mbox{'comment'}}{\\mapsto}{\\color{blue}{\\mbox{'n deposit...}}},\\ \\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{28}}},\\ \\color{red}{\\mbox{'orderdate'}}{\\mapsto}{\\color{blue}{\\mbox{datetime.d...}}},\\ \\text{...}(5)\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'clerk'}}{\\mapsto}{\\color{blue}{\\mbox{'Clerk#000...}}},\\ \\color{red}{\\mbox{'comment'}}{\\mapsto}{\\color{blue}{\\mbox{' accounts...}}},\\ \\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{131}}},\\ \\color{red}{\\mbox{'orderdate'}}{\\mapsto}{\\color{blue}{\\mbox{datetime.d...}}},\\ \\text{...}(5)\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'clerk'}}{\\mapsto}{\\color{blue}{\\mbox{'Clerk#000...}}},\\ \\color{red}{\\mbox{'comment'}}{\\mapsto}{\\color{blue}{\\mbox{'ealthy pi...}}},\\ \\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{73}}},\\ \\color{red}{\\mbox{'orderdate'}}{\\mapsto}{\\color{blue}{\\mbox{datetime.d...}}},\\ \\text{...}(5)\\right\\},\\\\\n", "\\text{...}(1496)\\end{array}\\right\\}$$" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "print('The display of this data may take a while. 15 s is not unusual... \\n', flush=True)\n", "\n", "from datetime import datetime\n", "\n", "def read_date(date_str: str) -> datetime:\n", " return datetime.strptime(date_str, '%Y-%m-%d').date()\n", "\n", "order_types = {\n", " 'orderkey': int, 'custkey': int, 'orderdate': read_date,\n", " 'totalprice': float, 'shippriority': int\n", "}\n", "orders = csv.import_csv('orders.csv', order_types)\n", "iprint_latex('orders', short=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Then we restrict the clan `orders` using the function `select_dates`. It returns `True` if the right part associated with the left `orderdate` in the relation `rel` falls between `startdate` (inclusive) and `enddate` (exclusive).\n", "\n", "As used here, our function [`sets.restrict`][] implements a set builder of this form:\n", "\n", "$$ \n", "Orders_{Restricted} = \\{order\\ :\\ order \\in Orders \\text{ and } SelectDates(order) \\text{ is true}\\} \\\\\n", "$$\n", "\n", "The restriction reduces the number of relations in the clan from TODO to 16 (for the given date range).\n", "\n", "[`sets.restrict`]: (function algebraixlib.algebras.sets.restrict)" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/latex": [ "$$orders\\_restricted = \\left\\{\\begin{array}{l}\\left\\{\\color{red}{\\mbox{'clerk'}}{\\mapsto}{\\color{blue}{\\mbox{'Clerk#000...}}},\\ \\color{red}{\\mbox{'comment'}}{\\mapsto}{\\color{blue}{\\mbox{'al ideas ...}}},\\ \\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{149}}},\\ \\color{red}{\\mbox{'orderdate'}}{\\mapsto}{\\color{blue}{\\mbox{datetime.d...}}},\\ \\text{...}(5)\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'clerk'}}{\\mapsto}{\\color{blue}{\\mbox{'Clerk#000...}}},\\ \\color{red}{\\mbox{'comment'}}{\\mapsto}{\\color{blue}{\\mbox{'ts are. f...}}},\\ \\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{70}}},\\ \\color{red}{\\mbox{'orderdate'}}{\\mapsto}{\\color{blue}{\\mbox{datetime.d...}}},\\ \\text{...}(5)\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'clerk'}}{\\mapsto}{\\color{blue}{\\mbox{'Clerk#000...}}},\\ \\color{red}{\\mbox{'comment'}}{\\mapsto}{\\color{blue}{\\mbox{'slyly. pe...}}},\\ \\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{136}}},\\ \\color{red}{\\mbox{'orderdate'}}{\\mapsto}{\\color{blue}{\\mbox{datetime.d...}}},\\ \\text{...}(5)\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'clerk'}}{\\mapsto}{\\color{blue}{\\mbox{'Clerk#000...}}},\\ \\color{red}{\\mbox{'comment'}}{\\mapsto}{\\color{blue}{\\mbox{'ng accoun...}}},\\ \\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{4}}},\\ \\color{red}{\\mbox{'orderdate'}}{\\mapsto}{\\color{blue}{\\mbox{datetime.d...}}},\\ \\text{...}(5)\\right\\},\\\\\n", "\\text{...}(12)\\end{array}\\right\\}$$" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "import algebraixlib.algebras.sets as sets\n", "\n", "startdate = datetime(1994, 1, 1).date()\n", "enddate = datetime(1994, 2, 1).date()\n", "\n", "def select_dates(rel) -> bool:\n", " orderdate = rel('orderdate').value\n", " return (startdate <= orderdate) and (orderdate < enddate)\n", "\n", "orders_restricted = sets.restrict(orders, select_dates)\n", "iprint_latex('orders_restricted', short=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Finally we again project the desired columns `orderkey` and `custkey`, using the function [`clans.project`][]:\n", "\n", "$$ \n", "Orders_{Restricted,Projected} = Orders_{Restricted} \\circ \\{\\{orderkey{\\mapsto}orderkey, custkey{\\mapsto}custkey\\}\\})\n", "$$\n", "\n", "This again doesn't change the number of relations in the clan (16), but the number of couplets in each relation is reduced to the ones that have the desired column name as left.\n", "\n", "[`clans.project`]: (function algebraixlib.algebras.clans.project)" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/latex": [ "$$orders\\_restricted\\_projected = \\left\\{\\begin{array}{l}\\left\\{\\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{100}}},\\ \\color{red}{\\mbox{'orderkey'}}{\\mapsto}{\\color{blue}{\\mbox{5316}}}\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{104}}},\\ \\color{red}{\\mbox{'orderkey'}}{\\mapsto}{\\color{blue}{\\mbox{4323}}}\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{109}}},\\ \\color{red}{\\mbox{'orderkey'}}{\\mapsto}{\\color{blue}{\\mbox{5638}}}\\right\\},\\\\\n", "\\left\\{\\color{red}{\\mbox{'custkey'}}{\\mapsto}{\\color{blue}{\\mbox{118}}},\\ \\color{red}{\\mbox{'orderkey'}}{\\mapsto}{\\color{blue}{\\mbox{290}}}\\right\\},\\\\\n", "\\text{...}(12)\\end{array}\\right\\}$$" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "orders_restricted_projected = clans.project(orders_restricted, 'orderkey', 'custkey')\n", "iprint_latex('orders_restricted_projected', short=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Next Step\n", "\n", "Continue with [3-Graphs][]; it introduces our representation of RDF-style tabular graph data, on the example of Turtle.\n", "\n", "[3-Graphs]: <3-Graphs.ipynb> (IPython Notebook: TPC-H Query 5 - Graphs)" ] }, { "cell_type": "markdown", "metadata": { "collapsed": true }, "source": [ "----\n", "© Copyright Permission.io, Inc. (formerly known as Algebraix Data Corporation), Copyright (c) 2022.\n", "\n", "This file is part of [`algebraixlib`][] .\n", "\n", "[`algebraixlib`][] is free software: you can redistribute it and/or modify it under the terms of [version 3 of the GNU Lesser General Public License][] as published by the [Free Software Foundation][].\n", "\n", "[`algebraixlib`][] is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details.\n", "\n", "You should have received a copy of the GNU Lesser General Public License along with [`algebraixlib`][]. If not, see [GNU licenses][].\n", "\n", "[`algebraixlib`]: (A Python library for data algebra)\n", "[Version 3 of the GNU Lesser General Public License]: \n", "[Free Software Foundation]: \n", "[GNU licenses]: " ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.4.3" } }, "nbformat": 4, "nbformat_minor": 0 }