def test_fixed_sql():
from airflow.operators.postgres_operator import PostgresOperator
from airflow.sensors.sql_sensor import SqlSensor
from flowetl.mixins.table_name_macros_mixin import TableNameMacrosMixin
from flowetl.mixins.fixed_sql_mixin import fixed_sql_operator
new_type = fixed_sql_operator(class_name="DUMMY_TYPE", sql="FIXED_SQL")
new_instance = new_type(task_id="DUMMY")
assert new_type.fixed_sql == "FIXED_SQL"
assert isinstance(new_instance, PostgresOperator)
assert isinstance(new_instance, TableNameMacrosMixin)
assert type(new_instance).__name__ == "DUMMY_TYPE"
new_instance.prepare_template()
assert new_instance.sql == "FIXED_SQL"
new_type = fixed_sql_operator(class_name="DUMMY_TYPE",
sql="FIXED_SQL",
is_sensor=True)
new_instance = new_type(task_id="DUMMY", conn_id="DUMMY_CONNECTION")
assert new_type.fixed_sql == "FIXED_SQL"
assert isinstance(new_instance, SqlSensor)
assert isinstance(new_instance, TableNameMacrosMixin)
assert type(new_instance).__name__ == "DUMMY_TYPE"
new_instance.prepare_template()
assert new_instance.sql == "FIXED_SQL"
# This Source Code Form is subject to the terms of the Mozilla Public
# License, v. 2.0. If a copy of the MPL was not distributed with this
# file, You can obtain one at http://mozilla.org/MPL/2.0/.
from flowetl.mixins.fixed_sql_mixin import fixed_sql_operator
DataPresentSensor = fixed_sql_operator(
class_name="DataPresentSensor",
sql="SELECT * FROM {{ staging_table }} LIMIT 1;",
is_sensor=True,
)
# This Source Code Form is subject to the terms of the Mozilla Public
# License, v. 2.0. If a copy of the MPL was not distributed with this
# file, You can obtain one at http://mozilla.org/MPL/2.0/.
from flowetl.mixins.fixed_sql_mixin import fixed_sql_operator
AddConstraintsOperator = fixed_sql_operator(
class_name="AddConstraintsOperator",
sql="""
ALTER TABLE {{ extract_table }}
ALTER COLUMN msisdn SET NOT NULL,
ALTER COLUMN datetime SET NOT NULL;
ALTER TABLE {{ extract_table }} DROP CONSTRAINT IF EXISTS {{ table_name }}_date_constraint;
ALTER TABLE {{ extract_table }} ADD CONSTRAINT {{ table_name }}_date_constraint CHECK (
datetime >= '{{ ds }}'::TIMESTAMPTZ AND
datetime < '{{ tomorrow_ds }}'::TIMESTAMPTZ
);
""",
)
# This Source Code Form is subject to the terms of the Mozilla Public
# License, v. 2.0. If a copy of the MPL was not distributed with this
# file, You can obtain one at http://mozilla.org/MPL/2.0/.
from flowetl.mixins.fixed_sql_mixin import fixed_sql_operator
AttachOperator = fixed_sql_operator(
class_name="AttachOperator",
sql="""
DROP TABLE IF EXISTS {{ final_table }};
ALTER TABLE {{ extract_table }} RENAME TO {{ table_name }};
ALTER TABLE {{ etl_schema }}.{{ table_name }} SET SCHEMA {{ final_schema }};
ALTER TABLE {{ final_table }} INHERIT {{ parent_table }};
""",
)
# This Source Code Form is subject to the terms of the Mozilla Public
# License, v. 2.0. If a copy of the MPL was not distributed with this
# file, You can obtain one at http://mozilla.org/MPL/2.0/.
from flowetl.mixins.fixed_sql_mixin import fixed_sql_operator
UpdateETLTableOperator = fixed_sql_operator(
class_name="UpdateETLTableOperator",
sql="""
INSERT INTO etl.etl_records (cdr_type, cdr_date, state, timestamp) VALUES ('{{ params.cdr_type }}', '{{ ds }}'::DATE, 'ingested', NOW());
INSERT INTO available_tables (table_name, has_locations, has_subscribers{% if params.cdr_type in ['calls', 'sms'] %}, has_counterparts {% endif %}) VALUES ('{{ params.cdr_type }}', true, true{% if params.cdr_type in ['calls', 'sms'] %}, true {% endif %})
ON conflict (table_name)
DO UPDATE SET has_locations=EXCLUDED.has_locations, has_subscribers=EXCLUDED.has_subscribers{% if params.cdr_type in ['calls', 'sms'] %}, has_counterparts=EXCLUDED.has_counterparts {% endif %};
""",
)