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 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