def basic_compiled_model():
return CompiledModelNode(
package_name='test',
root_path='/root/',
path='/root/models/foo.sql',
original_file_path='models/foo.sql',
raw_sql='select * from {{ ref("other") }}',
name='foo',
resource_type=NodeType.Model,
unique_id='model.test.foo',
fqn=['test', 'models', 'foo'],
refs=[],
sources=[],
depends_on=DependsOn(),
deferred=True,
description='',
database='test_db',
schema='test_schema',
alias='bar',
tags=[],
config=NodeConfig(),
meta={},
compiled=True,
extra_ctes=[InjectedCTE('whatever', 'select * from other')],
extra_ctes_injected=True,
compiled_sql='with whatever as (select * from other) select * from whatever',
checksum=FileHash.from_contents(''),
unrendered_config={}
)
def recursively_prepend_ctes(model, manifest):
if model.extra_ctes_injected:
return (model, model.extra_ctes, manifest)
if dbt.flags.STRICT_MODE:
if not isinstance(model, tuple(COMPILED_TYPES.values())):
raise dbt.exceptions.InternalException('Bad model type: {}'.format(
type(model)))
prepended_ctes = []
for cte in model.extra_ctes:
cte_id = cte.id
cte_to_add = manifest.nodes.get(cte_id)
cte_to_add, new_prepended_ctes, manifest = recursively_prepend_ctes(
cte_to_add, manifest)
_extend_prepended_ctes(prepended_ctes, new_prepended_ctes)
new_cte_name = '__dbt__CTE__{}'.format(cte_to_add.name)
sql = ' {} as (\n{}\n)'.format(new_cte_name, cte_to_add.compiled_sql)
_add_prepended_cte(prepended_ctes, InjectedCTE(id=cte_id, sql=sql))
model.prepend_ctes(prepended_ctes)
manifest.update_node(model)
return (model, prepended_ctes, manifest)
def _add_ctes(
self,
compiled_node: NonSourceCompiledNode,
manifest: Manifest,
extra_context: Dict[str, Any],
) -> NonSourceCompiledNode:
"""Wrap the data test SQL in a CTE."""
# for data tests, we need to insert a special CTE at the end of the
# list containing the test query, and then have the "real" query be a
# select count(*) from that model.
# the benefit of doing it this way is that _add_ctes() can be
# rewritten for different adapters to handle databases that don't
# support CTEs, or at least don't have full support.
if isinstance(compiled_node, CompiledDataTestNode):
# the last prepend (so last in order) should be the data test body.
# then we can add our select count(*) from _that_ cte as the "real"
# compiled_sql, and do the regular prepend logic from CTEs.
name = self._get_dbt_test_name()
cte = InjectedCTE(
id=name, sql=f' {name} as (\n{compiled_node.compiled_sql}\n)')
compiled_node.extra_ctes.append(cte)
compiled_node.compiled_sql = f'\nselect count(*) from {name}'
return compiled_node
def basic_compiled_schema_test_node():
return CompiledSchemaTestNode(
package_name='test',
root_path='/root/',
path='/root/x/path.sql',
original_file_path='/root/path.sql',
raw_sql='select * from {{ ref("other") }}',
name='foo',
resource_type=NodeType.Test,
unique_id='model.test.foo',
fqn=['test', 'models', 'foo'],
refs=[],
sources=[],
depends_on=DependsOn(),
deferred=False,
description='',
database='test_db',
schema='test_schema',
alias='bar',
tags=[],
config=TestConfig(severity='warn'),
meta={},
compiled=True,
extra_ctes=[InjectedCTE('whatever', 'select * from other')],
extra_ctes_injected=True,
compiled_sql='with whatever as (select * from other) select * from whatever',
column_name='id',
test_metadata=TestMetadata(namespace=None, name='foo', kwargs={}),
checksum=FileHash.from_contents(''),
unrendered_config={
'severity': 'warn',
}
)
def _recursively_prepend_ctes(
self,
model: NonSourceCompiledNode,
manifest: Manifest,
extra_context: Dict[str, Any],
) -> Tuple[NonSourceCompiledNode, List[InjectedCTE]]:
if model.extra_ctes_injected:
return (model, model.extra_ctes)
if flags.STRICT_MODE:
if not isinstance(model, tuple(COMPILED_TYPES.values())):
raise InternalException(f'Bad model type: {type(model)}')
prepended_ctes: List[InjectedCTE] = []
dbt_test_name = self._get_dbt_test_name()
for cte in model.extra_ctes:
if cte.id == dbt_test_name:
sql = cte.sql
else:
cte_model = self._get_compiled_model(
manifest,
cte.id,
extra_context,
)
cte_model, new_prepended_ctes = self._recursively_prepend_ctes(
cte_model, manifest, extra_context)
_extend_prepended_ctes(prepended_ctes, new_prepended_ctes)
new_cte_name = self.add_ephemeral_prefix(cte_model.name)
sql = f' {new_cte_name} as (\n{cte_model.compiled_sql}\n)'
_add_prepended_cte(prepended_ctes, InjectedCTE(id=cte.id, sql=sql))
model = self._model_prepend_ctes(model, prepended_ctes)
manifest.update_node(model)
return model, prepended_ctes
def test__prepend_ctes__already_has_cte(self):
ephemeral_config = self.model_config.replace(materialized='ephemeral')
manifest = Manifest(
macros={},
nodes={
'model.root.view': CompiledModelNode(
name='view',
database='dbt',
schema='analytics',
alias='view',
resource_type=NodeType.Model,
unique_id='model.root.view',
fqn=['root', 'view'],
package_name='root',
root_path='/usr/src/app',
refs=[],
sources=[],
depends_on=DependsOn(nodes=['model.root.ephemeral']),
config=self.model_config,
tags=[],
path='view.sql',
original_file_path='view.sql',
raw_sql='select * from {{ref("ephemeral")}}',
compiled=True,
extra_ctes_injected=False,
extra_ctes=[InjectedCTE(id='model.root.ephemeral', sql='select * from source_table')],
compiled_sql=(
'with cte as (select * from something_else) '
'select * from __dbt__cte__ephemeral'),
checksum=FileHash.from_contents(''),
),
'model.root.ephemeral': CompiledModelNode(
name='ephemeral',
database='dbt',
schema='analytics',
alias='view',
resource_type=NodeType.Model,
unique_id='model.root.ephemeral',
fqn=['root', 'ephemeral'],
package_name='root',
root_path='/usr/src/app',
refs=[],
sources=[],
depends_on=DependsOn(),
config=ephemeral_config,
tags=[],
path='ephemeral.sql',
original_file_path='ephemeral.sql',
raw_sql='select * from source_table',
compiled=True,
compiled_sql='select * from source_table',
extra_ctes_injected=False,
extra_ctes=[],
checksum=FileHash.from_contents(''),
),
},
sources={},
docs={},
disabled=[],
files={},
exposures={},
selectors={},
)
compiler = dbt.compilation.Compiler(self.config)
result, _ = compiler._recursively_prepend_ctes(
manifest.nodes['model.root.view'],
manifest,
{}
)
self.assertEqual(result, manifest.nodes['model.root.view'])
self.assertEqual(result.extra_ctes_injected, True)
self.assertEqualIgnoreWhitespace(
result.compiled_sql,
('with __dbt__cte__ephemeral as ('
'select * from source_table'
'), cte as (select * from something_else) '
'select * from __dbt__cte__ephemeral'))
self.assertEqual(
manifest.nodes['model.root.ephemeral'].extra_ctes_injected,
False)
def ref(name):
result = f'__dbt__cte__{name}'
unique_id = f'model.root.{name}'
model.extra_ctes.append(InjectedCTE(id=unique_id, sql=None))
return result
def test_basic_compiled(self):
node_dict = {
'name': 'foo',
'root_path': '/root/',
'resource_type': str(NodeType.Test),
'path': '/root/x/path.sql',
'original_file_path': '/root/path.sql',
'package_name': 'test',
'raw_sql': 'select * from {{ ref("other") }}',
'unique_id': 'model.test.foo',
'fqn': ['test', 'models', 'foo'],
'refs': [],
'sources': [],
'depends_on': {'macros': [], 'nodes': []},
'database': 'test_db',
'description': '',
'schema': 'test_schema',
'alias': 'bar',
'tags': [],
'config': {
'column_types': {},
'enabled': True,
'materialized': 'view',
'persist_docs': {},
'post-hook': [],
'pre-hook': [],
'quoting': {},
'tags': [],
'vars': {},
'severity': 'warn',
},
'docs': {'show': True},
'columns': {},
'meta': {},
'compiled': True,
'compiled_sql': 'select * from whatever',
'extra_ctes': [{'id': 'whatever', 'sql': 'select * from other'}],
'extra_ctes_injected': True,
'injected_sql': 'with whatever as (select * from other) select * from whatever',
'column_name': 'id',
'test_metadata': {
'name': 'foo',
'kwargs': {},
},
}
node = self.ContractType(
package_name='test',
root_path='/root/',
path='/root/x/path.sql',
original_file_path='/root/path.sql',
raw_sql='select * from {{ ref("other") }}',
name='foo',
resource_type=NodeType.Test,
unique_id='model.test.foo',
fqn=['test', 'models', 'foo'],
refs=[],
sources=[],
depends_on=DependsOn(),
description='',
database='test_db',
schema='test_schema',
alias='bar',
tags=[],
config=TestConfig(severity='warn'),
meta={},
compiled=True,
compiled_sql='select * from whatever',
extra_ctes=[InjectedCTE('whatever', 'select * from other')],
extra_ctes_injected=True,
injected_sql='with whatever as (select * from other) select * from whatever',
column_name='id',
test_metadata=TestMetadata(namespace=None, name='foo', kwargs={}),
)
self.assert_symmetric(node, node_dict)
self.assertFalse(node.empty)
self.assertFalse(node.is_refable)
self.assertFalse(node.is_ephemeral)
self.assertEqual(node.local_vars(), {})
def _recursively_prepend_ctes(
self,
model: NonSourceCompiledNode,
manifest: Manifest,
extra_context: Optional[Dict[str, Any]],
) -> Tuple[NonSourceCompiledNode, List[InjectedCTE]]:
"""This method is called by the 'compile_node' method. Starting
from the node that it is passed in, it will recursively call
itself using the 'extra_ctes'. The 'ephemeral' models do
not produce SQL that is executed directly, instead they
are rolled up into the models that refer to them by
inserting CTEs into the SQL.
"""
if model.compiled_sql is None:
raise RuntimeException('Cannot inject ctes into an unparsed node',
model)
if model.extra_ctes_injected:
return (model, model.extra_ctes)
# Just to make it plain that nothing is actually injected for this case
if not model.extra_ctes:
model.extra_ctes_injected = True
manifest.update_node(model)
return (model, model.extra_ctes)
# This stores the ctes which will all be recursively
# gathered and then "injected" into the model.
prepended_ctes: List[InjectedCTE] = []
dbt_test_name = self._get_dbt_test_name()
# extra_ctes are added to the model by
# RuntimeRefResolver.create_relation, which adds an
# extra_cte for every model relation which is an
# ephemeral model.
for cte in model.extra_ctes:
if cte.id == dbt_test_name:
sql = cte.sql
else:
if cte.id not in manifest.nodes:
raise InternalException(
f'During compilation, found a cte reference that '
f'could not be resolved: {cte.id}')
cte_model = manifest.nodes[cte.id]
if not cte_model.is_ephemeral_model:
raise InternalException(f'{cte.id} is not ephemeral')
# This model has already been compiled, so it's been
# through here before
if getattr(cte_model, 'compiled', False):
assert isinstance(cte_model,
tuple(COMPILED_TYPES.values()))
cte_model = cast(NonSourceCompiledNode, cte_model)
new_prepended_ctes = cte_model.extra_ctes
# if the cte_model isn't compiled, i.e. first time here
else:
# This is an ephemeral parsed model that we can compile.
# Compile and update the node
cte_model = self._compile_node(cte_model, manifest,
extra_context)
# recursively call this method
cte_model, new_prepended_ctes = \
self._recursively_prepend_ctes(
cte_model, manifest, extra_context
)
# Save compiled SQL file and sync manifest
self._write_node(cte_model)
manifest.sync_update_node(cte_model)
_extend_prepended_ctes(prepended_ctes, new_prepended_ctes)
new_cte_name = self.add_ephemeral_prefix(cte_model.name)
rendered_sql = (cte_model._pre_injected_sql
or cte_model.compiled_sql)
sql = f' {new_cte_name} as (\n{rendered_sql}\n)'
_add_prepended_cte(prepended_ctes, InjectedCTE(id=cte.id, sql=sql))
injected_sql = self._inject_ctes_into_sql(
model.compiled_sql,
prepended_ctes,
)
model._pre_injected_sql = model.compiled_sql
model.compiled_sql = injected_sql
model.extra_ctes_injected = True
model.extra_ctes = prepended_ctes
model.validate(model.to_dict(omit_none=True))
manifest.update_node(model)
return model, prepended_ctes
def test__prepend_ctes__already_has_cte(self):
ephemeral_config = self.model_config.replace(materialized='ephemeral')
input_graph = Manifest(
macros={},
nodes={
'model.root.view':
CompiledModelNode(
name='view',
database='dbt',
schema='analytics',
alias='view',
resource_type=NodeType.Model,
unique_id='model.root.view',
fqn=['root_project', 'view'],
package_name='root',
root_path='/usr/src/app',
refs=[],
sources=[],
depends_on=DependsOn(nodes=['model.root.ephemeral']),
config=self.model_config,
tags=[],
path='view.sql',
original_file_path='view.sql',
raw_sql='select * from {{ref("ephemeral")}}',
compiled=True,
extra_ctes_injected=False,
extra_ctes=[
InjectedCTE(id='model.root.ephemeral',
sql='select * from source_table')
],
injected_sql='',
compiled_sql=('with cte as (select * from something_else) '
'select * from __dbt__CTE__ephemeral')),
'model.root.ephemeral':
CompiledModelNode(name='ephemeral',
database='dbt',
schema='analytics',
alias='view',
resource_type=NodeType.Model,
unique_id='model.root.ephemeral',
fqn=['root_project', 'ephemeral'],
package_name='root',
root_path='/usr/src/app',
refs=[],
sources=[],
depends_on=DependsOn(),
config=ephemeral_config,
tags=[],
path='ephemeral.sql',
original_file_path='ephemeral.sql',
raw_sql='select * from source_table',
compiled=True,
compiled_sql='select * from source_table',
extra_ctes_injected=False,
extra_ctes=[],
injected_sql=''),
},
docs={},
# '2018-02-14T09:15:13Z'
generated_at=datetime(2018, 2, 14, 9, 15, 13),
disabled=[],
files={},
)
result, output_graph = dbt.compilation.prepend_ctes(
input_graph.nodes['model.root.view'], input_graph)
self.assertEqual(result, output_graph.nodes['model.root.view'])
self.assertEqual(result.extra_ctes_injected, True)
self.assertEqualIgnoreWhitespace(
result.injected_sql, ('with __dbt__CTE__ephemeral as ('
'select * from source_table'
'), cte as (select * from something_else) '
'select * from __dbt__CTE__ephemeral'))
self.assertEqual(
input_graph.nodes['model.root.ephemeral'].extra_ctes_injected,
True)
def _recursively_prepend_ctes(
self,
model: NonSourceCompiledNode,
manifest: Manifest,
extra_context: Optional[Dict[str, Any]],
) -> Tuple[NonSourceCompiledNode, List[InjectedCTE]]:
if model.compiled_sql is None:
raise RuntimeException('Cannot inject ctes into an unparsed node',
model)
if model.extra_ctes_injected:
return (model, model.extra_ctes)
# Just to make it plain that nothing is actually injected for this case
if not model.extra_ctes:
model.extra_ctes_injected = True
manifest.update_node(model)
return (model, model.extra_ctes)
# This stores the ctes which will all be recursively
# gathered and then "injected" into the model.
prepended_ctes: List[InjectedCTE] = []
# extra_ctes are added to the model by
# RuntimeRefResolver.create_relation, which adds an
# extra_cte for every model relation which is an
# ephemeral model.
for cte in model.extra_ctes:
if cte.id not in manifest.nodes:
raise InternalException(
f'During compilation, found a cte reference that '
f'could not be resolved: {cte.id}')
cte_model = manifest.nodes[cte.id]
if not cte_model.is_ephemeral_model:
raise InternalException(f'{cte.id} is not ephemeral')
# This model has already been compiled, so it's been
# through here before
if getattr(cte_model, 'compiled', False):
assert isinstance(cte_model, tuple(COMPILED_TYPES.values()))
cte_model = cast(NonSourceCompiledNode, cte_model)
new_prepended_ctes = cte_model.extra_ctes
# if the cte_model isn't compiled, i.e. first time here
else:
# This is an ephemeral parsed model that we can compile.
# Compile and update the node
cte_model = self._compile_node(cte_model, manifest,
extra_context)
# recursively call this method
cte_model, new_prepended_ctes = \
self._recursively_prepend_ctes(
cte_model, manifest, extra_context
)
# Save compiled SQL file and sync manifest
self._write_node(cte_model)
manifest.sync_update_node(cte_model)
_extend_prepended_ctes(prepended_ctes, new_prepended_ctes)
new_cte_name = self.add_ephemeral_prefix(cte_model.name)
sql = f' {new_cte_name} as (\n{cte_model.compiled_sql}\n)'
_add_prepended_cte(prepended_ctes, InjectedCTE(id=cte.id, sql=sql))
# We don't save injected_sql into compiled sql for ephemeral models
# because it will cause problems with processing of subsequent models.
# Ephemeral models do not produce executable SQL of their own.
if not model.is_ephemeral_model:
injected_sql = self._inject_ctes_into_sql(
model.compiled_sql,
prepended_ctes,
)
model.compiled_sql = injected_sql
model.extra_ctes_injected = True
model.extra_ctes = prepended_ctes
model.validate(model.to_dict(omit_none=True))
manifest.update_node(model)
return model, prepended_ctes