The running theme...

Replace [Thing A] with better, but equivalent [Thing B].

1. How can we tell if [Thing B] is equivalent to [Thing A]?
2. How can we tell if [Thing B] is better than [Thing A]?

Replace [Query A] with better, but equivalent [Query B].

1. How can we tell if [Query B] is equivalent to [Query A]?
2. How can we tell if [Query B] is better than [Query A]?

... but first let's remind ourselves of few definitions.

Relational Data

Relation (Table)

A collection of Tuples of Primitive Values

All tuples have the same set of attributes, or schema

Relational Data

Declarative Languages

Look at every T report 
For each week:
  Sum up the sprocket count
  Find that week's S report
  ... 

Declarative languages make it easier to explore equivalent computations to find the best one.

SQL

                      SELECT R.A FROM R, S 
                      WHERE R.B = S.B AND S.C = 10
    

Python

          with open_csv("R.csv") as R:
            with open_csv("S.csv") as S:
              for r_row in R:
                for s_row in S:
                  if r_row[1] == s_row[0] and s_row[1] == "10":
                    print(r_row[0])
    

Declarative Queries

SQL

Readability: Focus on what and where from

Relational Algebra

Data Flow: Focus on transformations

Relational Calculus

Minimality: Focus on attribute relationships

Relational Algebra

Relational Calculus

$$Q(A) :- R(A, B), S(B, 10)$$

Declarative Queries

SQL

Readability: Focus on what and where from

Relational Algebra

Data Flow: Focus on transformations

Relational Calculus

Minimality: Focus on attribute relationships

Preliminaries

We start with a database instance with a fixed schema

Queries are applied to Relations $$Q(\textbf{Trees}, \textbf{SpeciesInfo})$$

Queries are also Relations! $$Q_2(\textbf{SpeciesInfo}, Q_1(\textbf{Trees}))$$ (Relational Algebra is Closed)

Relational Algebra

Also: Intersection, Join, Division, Renaming

(Not essential, but can be useful)

First we focus on sets and bags.

Selection ($\sigma_{c}$)

Delete rows that fail the condition $c$.

          SELECT * FROM Trees [[ WHERE BORONAME = 'Brooklyn' ]]
      

Projection ($\pi_{A}$)

Delete attributes not in the projection list $A$.

Only 5 results... not 683788?

Set and Bag Projection are different

              SELECT [[ DISTINCT BORONAME ]] FROM Trees;
      

Reminder: Queries are Relations

What are these queries' schemas?

Union ($\cup$)

Takes two relations that are union-compatible...

(Both relations have the same number of fields with the same types)

... and returns all tuples appearing in either relation

We use $\uplus$ if we explicitly mean bag union

Intersection ($\cap$)

Return all tuples appearing in both
of two union-compatible relations

Set Difference

Return all tuples appearing in the first, but not the second
of two union-compatible relations

Union, Intersection, Set Difference

What is the schema of the result of any of these operators?

Cross (Cartesian) Product ($\times$)

Create all pairs of tuples.

Cross (Cartesian) Product ($\times$)

What is the schema of the resulting relation?

The relation has a naming conflict
(two attributes with the same name)

Renaming ($\rho$)

What is the schema of the resulting relation?

When writing cross-products,
I will use implicit renaming

Join ($\bowtie_c$)

Pair tuples according to a condition c.

Join Shorthands

Equi-joins are joins with only equality tests in the condition.

Join on attribute(s)

$R \bowtie_{A} S \equiv R \bowtie_{R.A = S.A} S$

Same values on the listed attributes

Natural Join

$R \bowtie S \equiv R \bowtie_{attrs(R) \cap attrs(S)} S$

Same values on all shared attributes

Join Shorthands

Formally: The output schema of a natural or equi-join still has both copies of each attribute.

Informally: I'll write things on the board assuming only one copy passes the join.

Which operators can create duplicates?

(Which operators behave differently in Set- and Bag-RA?)

Division ($/$)

Not typically supported as a primitive operator,
but useful for expressing queries like:

Find species that appear in all boroughs

$$R / S \equiv \{\; \left<\vec t\right> \;|\; \forall \left<\vec s\right> \in S, \left< \vec t \vec s \right> \in R \;\}$$

Division ($/$)

Group Work

If time permits: Implement division using other operators.

Abstract Syntax Trees (ASTs)

A tree-shaped representation of a computation.

RA is a model for SQL ASTs

Spark ASTs

org.apache.spark.sql.catalyst.expressions.Expression

Primitive-valued expressions

org.apache.spark.sql.catalyst.plans.logical.LogicalPlan

"Relational Algebra" (plus things)

SparkSQL uses a combination of Bag and Extended RA.

Expression

  abstract class Expression extends TreeNode[Expression] {
    ...
    def nullable: Boolean
    ...
    def references: AttributeSet = ...
    ...
    def eval(input: InternalRow = null): Any
    ...
    def dataType: DataType
    ...
  }
    

More on Expressions later, but...

Expression

  abstract class Expression extends TreeNode[Expression] {
    

↑↑
TreeNode[Expression]

  abstract class TreeNode[BaseType <: TreeNode[BaseType]] extends Product {
    ...
    def children: Seq[BaseType]
    ...
    def foreach(f: BaseType => Unit): Unit
    ...
    def map[A](f: BaseType => A): Seq[A]
    ...
    def transform(rule: PartialFunction[BaseType, BaseType]): BaseType
    ...
    final def treeString: String = treeString(verbose = true)
    ...
  }
    

PartialFunction[A,B]

    { case Literal(y: Int, IntegerType) => Literal(y + 1) }
    

A Partial Function is a normal lambda paired with a match

"Unmatched" cases should be handled gracefully.

With tree.transform on an Expression

  { x => x match {
      case Literal(y: Int, IntegerType) => Literal(y + 1) 
      case _                            => x
  } }
    

  myExpression.transform {
    case Literal(y: Int, IntegerType) => Literal(y + 1) 
  }
    

What does this do?

Replace every integer in the tree with the same integer + 1

Digression over...
How does Spark encode Relational Algebra?

Logical Plans

Relational Algebra Nodes

Case Classes for each RA operator.

More than RA

Query support for JSON, ML Models, more.

SQL DDL/DML

e.g., CREATE TABLE (To make the parser simpler?)

LogicalPlan

  abstract class LogicalPlan
    extends QueryPlan[LogicalPlan]
    with AnalysisHelper
    with LogicalPlanStats
    with QueryPlanConstraints
    with Logging {
    ...
  }
    

Most useful tools live in QueryPlan

QueryPlan

  abstract class QueryPlan[PlanType <: QueryPlan[PlanType]]
    extends TreeNode[PlanType] with SQLConfHelper {
    ...
    def output: Seq[Attribute]
    ...
    def transformExpressions(rule: PartialFunction[Expression, Expression]): ...
    ...
    def transformAllExpressions(rule: PartialFunction[Expression, Expression]): ...
    ...
    lazy val schema: StructType = StructType.fromAttributes(output)
    ...
  }
    

Relational Algebra in Spark

  case class Project(projectList: Seq[NamedExpression], child: LogicalPlan)

  case class Filter(condition: Expression, child: LogicalPlan)

  case class Union(children: Seq[LogicalPlan], byName: Boolean = false, 
                   allowMissingCol: Boolean = false)

  case class Join(left: LogicalPlan, right: LogicalPlan, joinType: JoinType, 
                  condition: Option[Expression], hint: JoinHint)
    

Note: Spark's union is a Bag-union.

Relational Algebra in Spark

    /**
     * Holds the name of a relation that has yet to be looked up in a catalog.
     *
     * @param multipartIdentifier table name
     * @param options options to scan this relation. Only applicable to v2 table scan.
     */
    case class UnresolvedRelation(
        multipartIdentifier: Seq[String],
        options: CaseInsensitiveStringMap = CaseInsensitiveStringMap.empty(),
        override val isStreaming: Boolean = false)
    extends LeafNode with NamedRelation {
    

QueryPlan

  abstract class QueryPlan[PlanType <: QueryPlan[PlanType]]
    extends TreeNode[PlanType] with SQLConfHelper {
    ...
    def output: Seq[Attribute]
    ...
    def transformExpressions(rule: PartialFunction[Expression, Expression]): ...
    ...
    def transformAllExpressions(rule: PartialFunction[Expression, Expression]): ...
    ...
    lazy val schema: StructType = StructType.fromAttributes(output)
    ...
  }
    

Challenge: Remove all projection operators from a LogicalPlan

  plan.transform { 
    case Project(_, child) => child
  }
    

Aside: Dataframe/Dataset is a thin wrapper around LogicalPlan.

class Dataset[T] private[sql](...) extends Serializable {
    ...
    /**
     * Filters rows using the given condition.
     * {{{
     *   // The following are equivalent:
     *   peopleDs.filter($"age" > 15)
     *   peopleDs.where($"age" > 15)
     * }}}
     *
     * @group typedrel
     * @since 1.6.0
     */
    def filter(condition: Column): Dataset[T] = withTypedPlan {
      Filter(condition.expr, logicalPlan)
    }
    ...
  }