def setUp(self):
super(MapperTestCase, self).setUp()
self.modelmd = self.model_metadata("mapper_test.json")
self.workspace = self.create_workspace(model=self.modelmd)
self.cube = self.workspace.cube("sales")
self.mapper = SnowflakeMapper(self.cube,
dimension_prefix='dim_',
dimension_suffix="_dim")
self.mapper.mappings = {
"product.name": "product.product_name",
"product.category": "product.category_id",
"subcategory.name.en": "subcategory.subcategory_name_en",
"subcategory.name.sk": "subcategory.subcategory_name_sk"
}
def setUp(self):
super(MapperTestCase, self).setUp()
self.modelmd = self.model_metadata("mapper_test.json")
self.workspace = self.create_workspace(model=self.modelmd)
self.cube = self.workspace.cube("sales")
self.mapper = SnowflakeMapper(self.cube, dimension_prefix="dim_", dimension_suffix="_dim")
self.mapper.mappings = {
"product.name": "product.product_name",
"product.category": "product.category_id",
"subcategory.name.en": "subcategory.subcategory_name_en",
"subcategory.name.sk": "subcategory.subcategory_name_sk",
}
class MapperTestCase(CubesTestCaseBase):
def setUp(self):
super(MapperTestCase, self).setUp()
self.modelmd = self.model_metadata("mapper_test.json")
self.workspace = self.create_workspace(model=self.modelmd)
self.cube = self.workspace.cube("sales")
self.mapper = SnowflakeMapper(self.cube, dimension_prefix="dim_", dimension_suffix="_dim")
self.mapper.mappings = {
"product.name": "product.product_name",
"product.category": "product.category_id",
"subcategory.name.en": "subcategory.subcategory_name_en",
"subcategory.name.sk": "subcategory.subcategory_name_sk",
}
def test_logical_reference(self):
dim = self.workspace.dimension("date")
attr = Attribute("month", dimension=dim)
self.assertEqual("date.month", self.mapper.logical(attr))
attr = Attribute("month", dimension=dim)
dim = self.workspace.dimension("product")
attr = Attribute("category", dimension=dim)
self.assertEqual("product.category", self.mapper.logical(attr))
self.assertEqual(True, self.mapper.simplify_dimension_references)
dim = self.workspace.dimension("flag")
attr = Attribute("flag", dimension=dim)
self.assertEqual("flag", self.mapper.logical(attr))
attr = Attribute("measure", dimension=None)
self.assertEqual("measure", self.mapper.logical(attr))
def test_logical_reference_as_string(self):
self.assertRaises(AttributeError, self.mapper.logical, "amount")
def test_dont_simplify_dimension_references(self):
self.mapper.simplify_dimension_references = False
dim = self.workspace.dimension("flag")
attr = Attribute("flag", dimension=dim)
self.assertEqual("flag.flag", self.mapper.logical(attr))
attr = Attribute("measure", dimension=None)
self.assertEqual("measure", self.mapper.logical(attr))
def test_logical_split(self):
split = self.mapper.split_logical
self.assertEqual(("foo", "bar"), split("foo.bar"))
self.assertEqual(("foo", "bar.baz"), split("foo.bar.baz"))
self.assertEqual((None, "foo"), split("foo"))
def assertMapping(self, expected, logical_ref, locale=None):
"""Create string reference by concatentanig table and column name.
No schema is expected (is ignored)."""
attr = self.mapper.attributes[logical_ref]
ref = self.mapper.physical(attr, locale)
sref = ref[1] + "." + ref[2]
self.assertEqual(expected, sref)
def test_physical_refs_dimensions(self):
"""Testing correct default mappings of dimensions (with and without
explicit default prefix) in physical references."""
# No dimension prefix
self.mapper.dimension_prefix = ""
self.mapper.dimension_suffix = ""
self.assertMapping("date.year", "date.year")
self.assertMapping("sales.flag", "flag")
self.assertMapping("sales.amount", "amount")
# self.assertEqual("fact.flag", sref("flag.flag"))
# With prefix
self.mapper.dimension_prefix = "dm_"
self.assertMapping("dm_date.year", "date.year")
self.assertMapping("dm_date.month_name", "date.month_name")
self.assertMapping("sales.flag", "flag")
self.assertMapping("sales.amount", "amount")
self.mapper.dimension_prefix = ""
self.mapper.dimension_suffix = ""
def test_physical_refs_flat_dims(self):
self.cube.fact = None
self.assertMapping("sales.flag", "flag")
def test_physical_refs_facts(self):
"""Testing correct mappings of fact attributes in physical references"""
fact = self.cube.fact
self.cube.fact = None
self.assertMapping("sales.amount", "amount")
# self.assertEqual("sales.flag", sref("flag.flag"))
self.cube.fact = fact
def test_physical_refs_with_mappings_and_locales(self):
"""Testing correct mappings of mapped attributes and localized
attributes in physical references"""
# Test defaults
self.assertMapping("dim_date_dim.month_name", "date.month_name")
self.assertMapping("dim_category_dim.category_name_en", "product.category_name")
self.assertMapping("dim_category_dim.category_name_sk", "product.category_name", "sk")
self.assertMapping("dim_category_dim.category_name_en", "product.category_name", "de")
# Test with mapping
self.assertMapping("dim_product_dim.product_name", "product.name")
self.assertMapping("dim_product_dim.category_id", "product.category")
self.assertMapping("dim_product_dim.product_name", "product.name", "sk")
self.assertMapping("dim_category_dim.subcategory_name_en", "product.subcategory_name")
self.assertMapping("dim_category_dim.subcategory_name_sk", "product.subcategory_name", "sk")
self.assertMapping("dim_category_dim.subcategory_name_en", "product.subcategory_name", "de")
class MapperTestCase(CubesTestCaseBase):
def setUp(self):
super(MapperTestCase, self).setUp()
self.modelmd = self.model_metadata("mapper_test.json")
self.workspace = self.create_workspace(model=self.modelmd)
self.cube = self.workspace.cube("sales")
self.mapper = SnowflakeMapper(self.cube,
dimension_prefix='dim_',
dimension_suffix="_dim")
self.mapper.mappings = {
"product.name": "product.product_name",
"product.category": "product.category_id",
"subcategory.name.en": "subcategory.subcategory_name_en",
"subcategory.name.sk": "subcategory.subcategory_name_sk"
}
def test_logical_reference(self):
dim = self.workspace.dimension("date")
attr = Attribute("month", dimension=dim)
self.assertEqual("date.month", self.mapper.logical(attr))
attr = Attribute("month", dimension=dim)
dim = self.workspace.dimension("product")
attr = Attribute("category", dimension=dim)
self.assertEqual("product.category", self.mapper.logical(attr))
self.assertEqual(True, self.mapper.simplify_dimension_references)
dim = self.workspace.dimension("flag")
attr = Attribute("flag", dimension=dim)
self.assertEqual("flag", self.mapper.logical(attr))
attr = Attribute("measure", dimension=None)
self.assertEqual("measure", self.mapper.logical(attr))
def test_logical_reference_as_string(self):
self.assertRaises(AttributeError, self.mapper.logical, "amount")
def test_dont_simplify_dimension_references(self):
self.mapper.simplify_dimension_references = False
dim = self.workspace.dimension("flag")
attr = Attribute("flag", dimension=dim)
self.assertEqual("flag.flag", self.mapper.logical(attr))
attr = Attribute("measure", dimension=None)
self.assertEqual("measure", self.mapper.logical(attr))
def test_logical_split(self):
split = self.mapper.split_logical
self.assertEqual(('foo', 'bar'), split('foo.bar'))
self.assertEqual(('foo', 'bar.baz'), split('foo.bar.baz'))
self.assertEqual((None, 'foo'), split('foo'))
def assertMapping(self, expected, logical_ref, locale=None):
"""Create string reference by concatentanig table and column name.
No schema is expected (is ignored)."""
attr = self.mapper.attributes[logical_ref]
ref = self.mapper.physical(attr, locale)
sref = ref[1] + "." + ref[2]
self.assertEqual(expected, sref)
def test_physical_refs_dimensions(self):
"""Testing correct default mappings of dimensions (with and without
explicit default prefix) in physical references."""
# No dimension prefix
self.mapper.dimension_prefix = ""
self.mapper.dimension_suffix = ""
self.assertMapping("date.year", "date.year")
self.assertMapping("sales.flag", "flag")
self.assertMapping("sales.amount", "amount")
# self.assertEqual("fact.flag", sref("flag.flag"))
# With prefix
self.mapper.dimension_prefix = "dm_"
self.assertMapping("dm_date.year", "date.year")
self.assertMapping("dm_date.month_name", "date.month_name")
self.assertMapping("sales.flag", "flag")
self.assertMapping("sales.amount", "amount")
self.mapper.dimension_prefix = ""
self.mapper.dimension_suffix = ""
def test_physical_refs_flat_dims(self):
self.cube.fact = None
self.assertMapping("sales.flag", "flag")
def test_physical_refs_facts(self):
"""Testing correct mappings of fact attributes in physical references"""
fact = self.cube.fact
self.cube.fact = None
self.assertMapping("sales.amount", "amount")
# self.assertEqual("sales.flag", sref("flag.flag"))
self.cube.fact = fact
def test_physical_refs_with_mappings_and_locales(self):
"""Testing correct mappings of mapped attributes and localized
attributes in physical references"""
# Test defaults
self.assertMapping("dim_date_dim.month_name", "date.month_name")
self.assertMapping("dim_category_dim.category_name_en",
"product.category_name")
self.assertMapping("dim_category_dim.category_name_sk",
"product.category_name", "sk")
self.assertMapping("dim_category_dim.category_name_en",
"product.category_name", "de")
# Test with mapping
self.assertMapping("dim_product_dim.product_name", "product.name")
self.assertMapping("dim_product_dim.category_id", "product.category")
self.assertMapping("dim_product_dim.product_name", "product.name",
"sk")
self.assertMapping("dim_category_dim.subcategory_name_en",
"product.subcategory_name")
self.assertMapping("dim_category_dim.subcategory_name_sk",
"product.subcategory_name", "sk")
self.assertMapping("dim_category_dim.subcategory_name_en",
"product.subcategory_name", "de")
def create_denormalized_view(self,
cube,
view_name=None,
materialize=False,
replace=False,
create_index=False,
keys_only=False,
schema=None):
"""Creates a denormalized view named `view_name` of a `cube`. If
`view_name` is ``None`` then view name is constructed by pre-pending
value of `denormalized_view_prefix` from workspace options to the cube
name. If no prefix is specified in the options, then view name will be
equal to the cube name.
Options:
* `materialize` - whether the view is materialized (a table) or
regular view
* `replace` - if `True` then existing table/view will be replaced,
otherwise an exception is raised when trying to create view/table
with already existing name
* `create_index` - if `True` then index is created for each key
attribute. Can be used only on materialized view, otherwise raises
an exception
* `keys_only` - if ``True`` then only key attributes are used in the
view, all other detail attributes are ignored
* `schema` - target schema of the denormalized view, if not specified,
then `denormalized_view_schema` from options is used if specified,
otherwise default workspace schema is used (same schema as fact
table schema).
"""
cube = self.model.cube(cube)
mapper = SnowflakeMapper(cube, cube.mappings, **self.options)
context = QueryContext(cube, mapper, metadata=self.metadata)
key_attributes = []
for dim in cube.dimensions:
key_attributes += dim.key_attributes()
if keys_only:
statement = context.denormalized_statement(
attributes=key_attributes, expand_locales=True)
else:
statement = context.denormalized_statement(expand_locales=True)
schema = schema or self.options.get(
"denormalized_view_schema") or self.schema
dview_prefix = self.options.get("denormalized_view_prefix", "")
view_name = view_name or dview_prefix + cube.name
table = sqlalchemy.Table(view_name,
self.metadata,
autoload=False,
schema=schema)
preparer = self.engine.dialect.preparer(self.engine.dialect)
full_name = preparer.format_table(table)
if mapper.fact_name == view_name and schema == mapper.schema:
raise WorkspaceError(
"target denormalized view is the same as source fact table")
if table.exists():
if not replace:
raise WorkspaceError("View or table %s (schema: %s) already exists." % \
(view_name, schema))
inspector = sqlalchemy.engine.reflection.Inspector.from_engine(
self.engine)
view_names = inspector.get_view_names(schema=schema)
if view_name in view_names:
# Table reflects a view
drop_statement = "DROP VIEW %s" % full_name
self.engine.execute(drop_statement)
else:
# Table reflects a table
table.drop(checkfirst=False)
if materialize:
create_stat = "CREATE TABLE"
else:
create_stat = "CREATE OR REPLACE VIEW"
statement = "%s %s AS %s" % (create_stat, full_name, str(statement))
self.logger.info("creating denormalized view %s (materialized: %s)" \
% (full_name, materialize))
# print("SQL statement:\n%s" % statement)
self.engine.execute(statement)
if create_index:
if not materialize:
raise WorkspaceError(
"Index can be created only on a materialized view")
# self.metadata.reflect(schema = schema, only = [view_name] )
table = sqlalchemy.Table(view_name,
self.metadata,
autoload=True,
schema=schema)
self.engine.reflecttable(table)
for attribute in key_attributes:
label = attribute.ref()
self.logger.info("creating index for %s" % label)
column = table.c[label]
name = "idx_%s_%s" % (view_name, label)
index = sqlalchemy.schema.Index(name, column)
index.create(self.engine)
return statement
