Left inner join in Brewery2 – three different joins: * SQL with iterable * SQL with SQL in the same store * SQL with SQL in another store Demonstrates multiple dispatch.

b2-left_inner_join.py

from brewery import data_object, FieldList, open_store
from brewery import kernel as k
import brewery.base.iterator
import brewery.backends.sql

FACT_DATA = [
            [0, 1, 100.0],
            [1, 1, 200.0],
            [2, 2, 150.0],
            [3, 2, 250.0],
            [4, 3, 300.0],
            [5, 3, 350.0]
        ]

MONTHS_DATA = [ [1, "january"], [2, "february"], [3, "march"] ]

# Initialization:
#
# Prepare two separate databases
#

db = open_store("sql", url="sqlite:///")
db_foreign = open_store("sql", url="sqlite:///")

k.debug_print_catalogue()

#
# 1. Create fact table as SQL table
# 
fields = FieldList(("id", "integer"),
                    ("month", "integer"),
                    ("amount", "float"))

facts = db.create("fact", fields=fields)
facts.append_from_iterable(FACT_DATA)


#
# 2. Create months dimension table
#
# 2a. Create as iterable
#
dim_months_iter = data_object("iterable", MONTHS_DATA, FieldList("id", "month"))

# 2b. Create as table
dim_months_table = db.create("months", fields=fields)
dim_months_table.append_from_iterable(MONTHS_DATA)

# 2c. Create as foreign table
dim_months_foreign = db_foreign.create("months", fields=fields)
dim_months_foreign.append_from_iterable(MONTHS_DATA)

# 3. Do the joins
# 

joins = [ ("month", "id") ]


print "=== JOIN SQL with ITERABLE"

result = k.left_inner_join(facts, [dim_months_iter], joins)

print "--- result type: %s" % type(result)
print "--- result reps: %s" % (result.representations(), )


print "=== JOIN SQL with SQL from same store"

result = k.left_inner_join(facts, [dim_months_table], joins)

print "--- result type: %s" % type(result)
print "--- result reps: %s" % (result.representations(), )


print "=== JOIN SQL with SQL from foreign store"

result = k.left_inner_join(facts, [dim_months_foreign], joins)

print "--- result type: %s" % type(result)
print "--- result reps: %s" % (result.representations(), )

output

=== JOIN SQL with ITERABLE
--- result type: <class 'brewery.objects.IterableDataSource'>
--- result reps: ['rows', 'records']

=== JOIN SQL with SQL from same store
--- result type: <class 'brewery.backends.sql.objects.SQLStatement'>
--- result reps: ['sql', 'rows', 'records']

=== JOIN SQL with SQL from foreign store
--- result type: <class 'brewery.objects.IterableDataSource'>
--- result reps: ['rows', 'records']