def _generate_query_with_params(
ingest_view: DirectIngestPreProcessedIngestView,
ingest_view_export_args: GcsfsIngestViewExportArgs
) -> Tuple[str, List[bigquery.ScalarQueryParameter]]:
"""Generates a date bounded query that represents the data that has changed for this view between the specified
date bounds in the provided export args.
If there is no lower bound, this produces a query for a historical query up to the upper bound date. Otherwise,
it diffs two historical queries to produce a delta query, using the SQL 'EXCEPT DISTINCT' function.
Returns the query, along with the query params that must be passed to the BigQuery query job.
"""
query_params = [
bigquery.ScalarQueryParameter(UPPER_BOUND_TIMESTAMP_PARAM_NAME,
bigquery.enums.SqlTypeNames.DATETIME.value,
ingest_view_export_args.upper_bound_datetime_to_export)
]
query = ingest_view.date_parametrized_view_query(UPPER_BOUND_TIMESTAMP_PARAM_NAME)
if ingest_view_export_args.upper_bound_datetime_prev:
query_params.append(
bigquery.ScalarQueryParameter(LOWER_BOUND_TIMESTAMP_PARAM_NAME,
bigquery.enums.SqlTypeNames.DATETIME.value,
ingest_view_export_args.upper_bound_datetime_prev)
)
query = query.rstrip().rstrip(';')
filter_query = \
ingest_view.date_parametrized_view_query(LOWER_BOUND_TIMESTAMP_PARAM_NAME).rstrip().rstrip(';')
query = f'(\n{query}\n) EXCEPT DISTINCT (\n{filter_query}\n)'
return query, query_params
def _debug_generate_unified_query(
ingest_view: DirectIngestPreProcessedIngestView,
ingest_view_export_args: GcsfsIngestViewExportArgs
) -> Tuple[str, List[bigquery.ScalarQueryParameter]]:
"""Generates a single query that is date bounded such that it represents the data that has changed for this view
between the specified date bounds in the provided export args.
If there is no lower bound, this produces a query for a historical query up to the upper bound date. Otherwise,
it diffs two historical queries to produce a delta query, using the SQL 'EXCEPT DISTINCT' function.
Important Note: This query is meant for debug use only. In the actual DirectIngest flow, query results for
individual dates are persisted into temporary tables, and those temporary tables are then diff'd using SQL's
`EXCEPT DISTINCT` function.
"""
query_params = [
bigquery.ScalarQueryParameter(UPPER_BOUND_TIMESTAMP_PARAM_NAME,
bigquery.enums.SqlTypeNames.DATETIME.value,
ingest_view_export_args.upper_bound_datetime_to_export)
]
query = ingest_view.date_parametrized_view_query(UPPER_BOUND_TIMESTAMP_PARAM_NAME)
if ingest_view_export_args.upper_bound_datetime_prev:
query_params.append(
bigquery.ScalarQueryParameter(LOWER_BOUND_TIMESTAMP_PARAM_NAME,
bigquery.enums.SqlTypeNames.DATETIME.value,
ingest_view_export_args.upper_bound_datetime_prev)
)
query = DirectIngestIngestViewExportManager.create_date_diff_query(
upper_bound_query=query,
upper_bound_prev_query=ingest_view.date_parametrized_view_query(LOWER_BOUND_TIMESTAMP_PARAM_NAME),
do_reverse_date_diff=ingest_view.do_reverse_date_diff)
query = DirectIngestPreProcessedIngestView.add_order_by_suffix(
query=query, order_by_cols=ingest_view.order_by_cols)
return query, query_params
def test_direct_ingest_preprocessed_view_with_reference_table(self):
region_config = DirectIngestRegionRawFileConfig(
region_code='us_xx',
yaml_config_file_path=fixtures.as_filepath(
'us_xx_raw_data_files.yaml'),
)
view_query_template = """SELECT * FROM {file_tag_first}
LEFT OUTER JOIN `{{project_id}}.reference_tables.my_table`
USING (col1);"""
view = DirectIngestPreProcessedIngestView(
ingest_view_name='ingest_view_tag',
view_query_template=view_query_template,
region_raw_table_config=region_config,
order_by_cols='col1, col2')
self.assertEqual(
['file_tag_first'],
[c.file_tag for c in view.raw_table_dependency_configs])
expected_view_query = """WITH
file_tag_first_generated_view AS (
SELECT * FROM `recidiviz-456.us_xx_raw_data_up_to_date_views.file_tag_first_latest`
)
SELECT * FROM file_tag_first_generated_view
LEFT OUTER JOIN `recidiviz-456.reference_tables.my_table`
USING (col1)
ORDER BY col1, col2;"""
self.assertEqual(expected_view_query, view.view_query)
expected_date_parametrized_view_query = """WITH
file_tag_first_generated_view AS (
WITH rows_with_recency_rank AS (
SELECT
* EXCEPT (file_id, update_datetime),
ROW_NUMBER() OVER (PARTITION BY col_name_1a, col_name_1b
ORDER BY update_datetime DESC) AS recency_rank
FROM
`recidiviz-456.us_xx_raw_data.file_tag_first`
WHERE
update_datetime <= @my_param
)
SELECT *
EXCEPT (recency_rank)
FROM rows_with_recency_rank
WHERE recency_rank = 1
)
SELECT * FROM file_tag_first_generated_view
LEFT OUTER JOIN `recidiviz-456.reference_tables.my_table`
USING (col1)
ORDER BY col1, col2;"""
self.assertEqual(expected_date_parametrized_view_query,
view.date_parametrized_view_query('my_param'))
def test_direct_ingest_preprocessed_view_other_materialized_subquery(self) -> None:
region_config = DirectIngestRegionRawFileConfig(
region_code='us_xx',
yaml_config_file_path=fixtures.as_filepath('us_xx_raw_data_files.yaml'),
)
view_query_template = """
CREATE TEMP TABLE my_subquery AS (SELECT * FROM {file_tag_first});
SELECT * FROM my_subquery;"""
view = DirectIngestPreProcessedIngestView(
ingest_view_name='ingest_view_tag',
view_query_template=view_query_template,
region_raw_table_config=region_config,
order_by_cols='col1, col2',
is_detect_row_deletion_view=False,
primary_key_tables_for_entity_deletion=[],
materialize_raw_data_table_views=True
)
expected_view_query = """CREATE TEMP TABLE file_tag_first_generated_view AS (
SELECT * FROM `recidiviz-456.us_xx_raw_data_up_to_date_views.file_tag_first_latest`
);
CREATE TEMP TABLE my_subquery AS (SELECT * FROM file_tag_first_generated_view);
SELECT * FROM my_subquery
ORDER BY col1, col2;"""
self.assertEqual(expected_view_query, view.view_query)
expected_parametrized_view_query = """CREATE TEMP TABLE file_tag_first_generated_view AS (
WITH rows_with_recency_rank AS (
SELECT
* EXCEPT (file_id, update_datetime),
ROW_NUMBER() OVER (PARTITION BY col_name_1a, col_name_1b
ORDER BY update_datetime DESC) AS recency_rank
FROM
`recidiviz-456.us_xx_raw_data.file_tag_first`
WHERE
update_datetime <= @my_update_timestamp_param_name
)
SELECT *
EXCEPT (recency_rank)
FROM rows_with_recency_rank
WHERE recency_rank = 1
);
CREATE TEMP TABLE my_subquery AS (SELECT * FROM file_tag_first_generated_view);
SELECT * FROM my_subquery
ORDER BY col1, col2;"""
self.assertEqual(expected_parametrized_view_query,
view.date_parametrized_view_query('my_update_timestamp_param_name'))
def test_direct_ingest_preprocessed_view_same_table_multiple_places(self):
region_config = DirectIngestRegionRawFileConfig(
region_code='us_xx',
yaml_config_file_path=fixtures.as_filepath(
'us_xx_raw_data_files.yaml'),
)
view_query_template = """SELECT * FROM {file_tag_first}
LEFT OUTER JOIN {file_tag_first}
USING (col1);"""
view = DirectIngestPreProcessedIngestView(
ingest_view_name='ingest_view_tag',
view_query_template=view_query_template,
region_raw_table_config=region_config)
self.assertEqual(
['file_tag_first'],
[c.file_tag for c in view.raw_table_dependency_configs])
expected_view_query = """WITH
file_tag_first_generated_view AS (
SELECT * FROM `recidiviz-456.us_xx_raw_data_up_to_date_views.file_tag_first_latest`
)
SELECT * FROM file_tag_first_generated_view
LEFT OUTER JOIN file_tag_first_generated_view
USING (col1);"""
self.assertEqual(expected_view_query, view.view_query)
def collect_views(self) -> List[DirectIngestPreProcessedIngestView]:
views = [
DirectIngestPreProcessedIngestView(
ingest_view_name=tag,
view_query_template=('SELECT * FROM {' + tag + '}'),
region_raw_table_config=FakeDirectIngestRegionRawFileConfig(
region_code=self.region.region_code))
for tag in self.controller_file_tags
]
return views
def _generate_query_and_params_for_date(
ingest_view: DirectIngestPreProcessedIngestView,
update_timestamp: datetime.datetime
) -> Tuple[str, List[bigquery.ScalarQueryParameter]]:
"""Generates a single query for the provided |ingest view| that is date bounded by |update_timestamp|."""
query_params = [
bigquery.ScalarQueryParameter(UPDATE_TIMESTAMP_PARAM_NAME,
bigquery.enums.SqlTypeNames.DATETIME.value,
update_timestamp)
]
query = ingest_view.date_parametrized_view_query(UPDATE_TIMESTAMP_PARAM_NAME)
logging.info('Generated bound query with params \nquery: [%s]\nparams: [%s]', query, query_params)
return query, query_params
def collect_views(self) -> List[DirectIngestPreProcessedIngestView]:
views = [
DirectIngestPreProcessedIngestView(
ingest_view_name=tag,
view_query_template=('SELECT * FROM {' + tag + '}'),
region_raw_table_config=FakeDirectIngestRegionRawFileConfig(
region_code=self.region.region_code),
order_by_cols=None,
is_detect_row_deletion_view=False,
primary_key_tables_for_entity_deletion=[],
) for tag in self.controller_file_tags
]
return views
def test_direct_ingest_preprocessed_view_throws_for_unexpected_tag(self):
region_config = DirectIngestRegionRawFileConfig(
region_code='us_xx',
yaml_config_file_path=fixtures.as_filepath(
'us_xx_raw_data_files.yaml'),
)
view_query_template = """SELECT * FROM {file_tag_first}
LEFT OUTER JOIN {file_tag_not_in_config}
USING (col1);"""
with self.assertRaises(ValueError):
DirectIngestPreProcessedIngestView(
ingest_view_name='ingest_view_tag',
view_query_template=view_query_template,
region_raw_table_config=region_config)
def collect_views(self) -> List[DirectIngestPreProcessedIngestView]:
region_config = DirectIngestRegionRawFileConfig(
region_code='us_xx',
yaml_config_file_path=fixtures.as_filepath('us_xx_raw_data_files.yaml', subdir='fixtures'),
)
query = 'select * from {file_tag_first}'
views = [
DirectIngestPreProcessedIngestView(
ingest_view_name=tag,
view_query_template=query,
region_raw_table_config=region_config,
order_by_cols='colA, colC',
)
for tag in self.controller_file_tags
]
return views
def test_direct_ingest_preprocessed_view_detect_row_deletion_unknown_pk_table_specified(self) -> None:
region_config = DirectIngestRegionRawFileConfig(
region_code='us_xx',
yaml_config_file_path=fixtures.as_filepath('us_xx_raw_data_files.yaml'),
)
view_query_template = """SELECT * FROM {file_tag_first}
LEFT OUTER JOIN {tagFullHistoricalExport}
USING (col1);"""
for materialize_raw_data_table_views in {True, False}:
with self.assertRaises(ValueError):
DirectIngestPreProcessedIngestView(
ingest_view_name='ingest_view_tag',
view_query_template=view_query_template,
region_raw_table_config=region_config,
order_by_cols='col1, col2',
is_detect_row_deletion_view=True,
primary_key_tables_for_entity_deletion=['tagFullHistoricalExport', 'unknown'],
materialize_raw_data_table_views=materialize_raw_data_table_views
)
def test_direct_ingest_preprocessed_view_other_materialized_subquery_materialize_flag_is_false(self) -> None:
region_config = DirectIngestRegionRawFileConfig(
region_code='us_xx',
yaml_config_file_path=fixtures.as_filepath('us_xx_raw_data_files.yaml'),
)
view_query_template = """
CREATE TEMP TABLE my_subquery AS (SELECT * FROM {file_tag_first});
SELECT * FROM my_subquery;"""
with self.assertRaises(ValueError) as e:
_ = DirectIngestPreProcessedIngestView(
ingest_view_name='ingest_view_tag',
view_query_template=view_query_template,
region_raw_table_config=region_config,
order_by_cols='col1, col2',
is_detect_row_deletion_view=False,
primary_key_tables_for_entity_deletion=[],
)
self.assertEqual(str(e.exception),
'Found CREATE TEMP TABLE clause in this query - you must set '
'|materialize_raw_data_table_views| to True to include a temp table subquery in your view.')
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <https://www.gnu.org/licenses/>.
# =============================================================================
"""A test view builder file for big_query_view_collector_test.py"""
from recidiviz.ingest.direct.controllers.direct_ingest_big_query_view_types import DirectIngestPreProcessedIngestView
from recidiviz.ingest.direct.controllers.direct_ingest_raw_file_import_manager import DirectIngestRegionRawFileConfig
from recidiviz.tests.big_query.fake_big_query_view_builder import FakeBigQueryViewBuilder
from recidiviz.utils.metadata import local_project_id_override
with local_project_id_override('my-project-id'):
GOOD_VIEW_2 = DirectIngestPreProcessedIngestView(
ingest_view_name='ingest_view_name',
view_query_template='SELECT * FROM table1',
region_raw_table_config=DirectIngestRegionRawFileConfig(
region_code='us_xx',
yaml_config_file_path='/a/path/to/a.yaml',
raw_file_configs={}),
order_by_cols=None,
)
VIEW_BUILDER = FakeBigQueryViewBuilder(GOOD_VIEW_2)
def test_direct_ingest_preprocessed_view_with_subqueries(self):
region_config = DirectIngestRegionRawFileConfig(
region_code='us_xx',
yaml_config_file_path=fixtures.as_filepath(
'us_xx_raw_data_files.yaml'),
)
view_query_template = """WITH
foo AS (SELECT * FROM bar)
SELECT * FROM {file_tag_first}
LEFT OUTER JOIN {file_tag_second}
USING (col1);"""
view = DirectIngestPreProcessedIngestView(
ingest_view_name='ingest_view_tag',
view_query_template=view_query_template,
region_raw_table_config=region_config)
self.assertEqual(
['file_tag_first', 'file_tag_second'],
[c.file_tag for c in view.raw_table_dependency_configs])
expected_view_query = """WITH
file_tag_first_generated_view AS (
SELECT * FROM `recidiviz-456.us_xx_raw_data_up_to_date_views.file_tag_first_latest`
),
file_tag_second_generated_view AS (
SELECT * FROM `recidiviz-456.us_xx_raw_data_up_to_date_views.file_tag_second_latest`
),
foo AS (SELECT * FROM bar)
SELECT * FROM file_tag_first_generated_view
LEFT OUTER JOIN file_tag_second_generated_view
USING (col1);"""
self.assertEqual(expected_view_query, view.view_query)
expected_parametrized_view_query = """WITH
file_tag_first_generated_view AS (
WITH rows_with_recency_rank AS (
SELECT
* EXCEPT (file_id, update_datetime),
ROW_NUMBER() OVER (PARTITION BY col_name_1a, col_name_1b
ORDER BY update_datetime DESC) AS recency_rank
FROM
`recidiviz-456.us_xx_raw_data.file_tag_first`
WHERE
update_datetime <= @update_timestamp
)
SELECT *
EXCEPT (recency_rank)
FROM rows_with_recency_rank
WHERE recency_rank = 1
),
file_tag_second_generated_view AS (
WITH rows_with_recency_rank AS (
SELECT
* EXCEPT (file_id, update_datetime),
ROW_NUMBER() OVER (PARTITION BY col_name_2a
ORDER BY update_datetime DESC) AS recency_rank
FROM
`recidiviz-456.us_xx_raw_data.file_tag_second`
WHERE
update_datetime <= @update_timestamp
)
SELECT *
EXCEPT (recency_rank)
FROM rows_with_recency_rank
WHERE recency_rank = 1
),
foo AS (SELECT * FROM bar)
SELECT * FROM file_tag_first_generated_view
LEFT OUTER JOIN file_tag_second_generated_view
USING (col1);"""
self.assertEqual(expected_parametrized_view_query,
view.date_parametrized_view_query())
# Also check that appending whitespace before the WITH prefix produces the same results
view_query_template = '\n ' + view_query_template
view = DirectIngestPreProcessedIngestView(
ingest_view_name='ingest_view_tag',
view_query_template=view_query_template,
region_raw_table_config=region_config)
self.assertEqual(
['file_tag_first', 'file_tag_second'],
[c.file_tag for c in view.raw_table_dependency_configs])
self.assertEqual(expected_view_query, view.view_query)
self.assertEqual(expected_parametrized_view_query,
view.date_parametrized_view_query())
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <https://www.gnu.org/licenses/>.
# =============================================================================
"""A test view builder file for big_query_view_collector_test.py"""
from recidiviz.ingest.direct.controllers.direct_ingest_big_query_view_types import DirectIngestPreProcessedIngestView
from recidiviz.ingest.direct.controllers.direct_ingest_raw_file_import_manager import DirectIngestRegionRawFileConfig
from recidiviz.tests.big_query.fake_big_query_view_builder import FakeBigQueryViewBuilder
from recidiviz.utils.metadata import local_project_id_override
with local_project_id_override('my-project-id'):
GOOD_VIEW_2 = DirectIngestPreProcessedIngestView(
ingest_view_name='ingest_view_name',
view_query_template='SELECT * FROM table1',
region_raw_table_config=DirectIngestRegionRawFileConfig(
region_code='us_xx',
yaml_config_file_path='/a/path/to/a.yaml',
raw_file_configs={}),
order_by_cols=None,
is_detect_row_deletion_view=False,
primary_key_tables_for_entity_deletion=[],
)
VIEW_BUILDER = FakeBigQueryViewBuilder(GOOD_VIEW_2)
def export_view_for_args(
self, ingest_view_export_args: GcsfsIngestViewExportArgs) -> bool:
"""Performs an Cloud Storage export of a single ingest view with date bounds specified in the provided args. If
the provided args contain an upper and lower bound date, the exported view contains only the delta between the
two dates. If only the upper bound is provided, then the exported view contains historical results up until the
bound date.
Note: In order to prevent resource exhaustion in BigQuery, the ultimate query in this method is broken down
into distinct parts. This method first persists the results of historical queries for each given bound date
(upper and lower) into temporary tables. The delta between those tables is then queried separately using
SQL's `EXCEPT DISTINCT` and those final results are exported to Cloud Storage.
"""
if not self.region.are_ingest_view_exports_enabled_in_env():
raise ValueError(
f'Ingest view exports not enabled for region [{self.region.region_code}]'
)
metadata = self.file_metadata_manager.get_ingest_view_metadata_for_export_job(
ingest_view_export_args)
if not metadata:
raise ValueError(
f'Found no metadata for the given job args: [{ingest_view_export_args}].'
)
if metadata.export_time:
logging.warning('Already exported view for args [%s] - returning.',
ingest_view_export_args)
return False
output_path = self._generate_output_path(ingest_view_export_args,
metadata)
logging.info('Generated output path [%s]', output_path.uri())
if not metadata.normalized_file_name:
self.file_metadata_manager.register_ingest_view_export_file_name(
metadata, output_path)
ingest_view = self.ingest_views_by_tag[
ingest_view_export_args.ingest_view_name]
single_date_table_ids = []
single_date_table_export_jobs = []
upper_bound_table_name = \
f'{ingest_view_export_args.ingest_view_name}_' \
f'{ingest_view_export_args.upper_bound_datetime_to_export.strftime(TABLE_NAME_DATE_FORMAT)}_' \
f'upper_bound'
export_job = self._generate_export_job_for_date(
table_name=upper_bound_table_name,
ingest_view=ingest_view,
date_bound=ingest_view_export_args.upper_bound_datetime_to_export)
single_date_table_ids.append(upper_bound_table_name)
single_date_table_export_jobs.append(export_job)
query = SELECT_SUBQUERY.format(
project_id=self.big_query_client.project_id,
dataset_id=ingest_view.dataset_id,
table_name=upper_bound_table_name)
if ingest_view_export_args.upper_bound_datetime_prev:
lower_bound_table_name = \
f'{ingest_view_export_args.ingest_view_name}_' \
f'{ingest_view_export_args.upper_bound_datetime_prev.strftime(TABLE_NAME_DATE_FORMAT)}_' \
f'lower_bound'
export_job = self._generate_export_job_for_date(
table_name=lower_bound_table_name,
ingest_view=ingest_view,
date_bound=ingest_view_export_args.upper_bound_datetime_prev)
single_date_table_export_jobs.append(export_job)
single_date_table_ids.append(lower_bound_table_name)
filter_query = SELECT_SUBQUERY.format(
project_id=self.big_query_client.project_id,
dataset_id=ingest_view.dataset_id,
table_name=lower_bound_table_name).rstrip().rstrip(';')
query = query.rstrip().rstrip(';')
query = f'(\n{query}\n) EXCEPT DISTINCT (\n{filter_query}\n);'
query = DirectIngestPreProcessedIngestView.add_order_by_suffix(
query=query, order_by_cols=ingest_view.order_by_cols)
# Wait for completion of all async date queries
for query_job in single_date_table_export_jobs:
query_job.result()
logging.info(
'Completed loading results of individual date queries into intermediate tables.'
)
logging.info('Generated final export query [%s]', str(query))
export_configs = [
ExportQueryConfig(
query=query,
query_parameters=[],
intermediate_dataset_id=ingest_view.dataset_id,
intermediate_table_name=
f'{ingest_view_export_args.ingest_view_name}_latest_export',
output_uri=output_path.uri(),
output_format=bigquery.DestinationFormat.CSV)
]
logging.info('Starting export to cloud storage.')
self.big_query_client.export_query_results_to_cloud_storage(
export_configs=export_configs)
logging.info('Export to cloud storage complete.')
for table_id in single_date_table_ids:
self.big_query_client.delete_table(
dataset_id=ingest_view.dataset_id, table_id=table_id)
logging.info('Deleted intermediate table [%s]', table_id)
self.file_metadata_manager.mark_ingest_view_exported(metadata)
return True
def test_direct_ingest_preprocessed_view_detect_row_deletion(self) -> None:
region_config = DirectIngestRegionRawFileConfig(
region_code='us_xx',
yaml_config_file_path=fixtures.as_filepath('us_xx_raw_data_files.yaml'),
)
view_query_template = """SELECT * FROM {file_tag_first}
LEFT OUTER JOIN {tagFullHistoricalExport}
USING (col1);"""
view = DirectIngestPreProcessedIngestView(
ingest_view_name='ingest_view_tag',
view_query_template=view_query_template,
region_raw_table_config=region_config,
order_by_cols='col1, col2',
is_detect_row_deletion_view=True,
primary_key_tables_for_entity_deletion=['tagFullHistoricalExport'],
)
self.assertEqual(['file_tag_first', 'tagFullHistoricalExport'],
[c.file_tag for c in view.raw_table_dependency_configs])
expected_view_query = """WITH
file_tag_first_generated_view AS (
SELECT * FROM `recidiviz-456.us_xx_raw_data_up_to_date_views.file_tag_first_latest`
),
tagFullHistoricalExport_generated_view AS (
SELECT * FROM `recidiviz-456.us_xx_raw_data_up_to_date_views.tagFullHistoricalExport_latest`
)
SELECT * FROM file_tag_first_generated_view
LEFT OUTER JOIN tagFullHistoricalExport_generated_view
USING (col1)
ORDER BY col1, col2;"""
self.assertEqual(expected_view_query, view.view_query)
expected_parametrized_view_query = """WITH
file_tag_first_generated_view AS (
WITH rows_with_recency_rank AS (
SELECT
* EXCEPT (file_id, update_datetime),
ROW_NUMBER() OVER (PARTITION BY col_name_1a, col_name_1b
ORDER BY update_datetime DESC) AS recency_rank
FROM
`recidiviz-456.us_xx_raw_data.file_tag_first`
WHERE
update_datetime <= @my_update_timestamp_param_name
)
SELECT *
EXCEPT (recency_rank)
FROM rows_with_recency_rank
WHERE recency_rank = 1
),
tagFullHistoricalExport_generated_view AS (
WITH max_update_datetime AS (
SELECT
MAX(update_datetime) AS update_datetime
FROM
`recidiviz-456.us_xx_raw_data.tagFullHistoricalExport`
WHERE
update_datetime <= @my_update_timestamp_param_name
),
max_file_id AS (
SELECT
MAX(file_id) AS file_id
FROM
`recidiviz-456.us_xx_raw_data.tagFullHistoricalExport`
WHERE
update_datetime = (SELECT update_datetime FROM max_update_datetime)
),
rows_with_recency_rank AS (
SELECT
* EXCEPT (file_id, update_datetime),
ROW_NUMBER() OVER (PARTITION BY COL_1
ORDER BY update_datetime DESC) AS recency_rank
FROM
`recidiviz-456.us_xx_raw_data.tagFullHistoricalExport`
WHERE
file_id = (SELECT file_id FROM max_file_id)
)
SELECT *
EXCEPT (recency_rank)
FROM rows_with_recency_rank
WHERE recency_rank = 1
)
SELECT * FROM file_tag_first_generated_view
LEFT OUTER JOIN tagFullHistoricalExport_generated_view
USING (col1)
ORDER BY col1, col2;"""
self.assertEqual(expected_parametrized_view_query,
view.date_parametrized_view_query('my_update_timestamp_param_name'))
