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 _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 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())
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'))