class ADH(object): """Run ADH queries This class runs ADH queries. Where they output is determined by the query iteslf, in ADH. All we can specify here is the date range - and we do this by accepting a lookback window and doing "yesterday - lookback". TODO (davidharcombe@) ADH Query Parameters """ def __init__(self, email: str, project: str, adh_customer: str, adh_query: str, api_key: str, days: int, dest_project: str=None, dest_dataset: str=None): """Constructor Setus up the ADH helper Arguments: email {str} -- authenticated user email (for the token) project {str} -- GCP project adh_customer {str} -- ADH customer id, 9-digit number, NO DASHES adh_query {str} -- ADH query id api_key {str} -- API Key (has to be set up in APIs and Libraries in GCP) days {int} -- Lookback window (default: 60) dest_project {str} -- target GCP project for results dest_dataset {str} -- target BQ dataset for results """ self.email = email self.project = project self.adh_customer = adh_customer self.adh_query = adh_query self.api_key = api_key self.days = days self.dest_project = dest_project self.dest_dataset = dest_dataset self.credentials = Credentials(email=email, project=project) self.storage = Cloud_Storage(email=email, project=project) self.firestore = Firestore(email=email, project=project) def run(self, unattended: bool=True): """Run the ADH query Execute the ADH query, storing the run job result in Firestore. The data itself will be written to Big Query by ADH. Remember that ADH queries have many, many constraints so use this wisely: DON'T set up an hourly run - check with ADH. Keyword Arguments: unattended {bool} -- run unattended. Unused, but there for compatibility (default: {True}) """ query_details = self.fetch_query_details() if query_details: report = { 'id': self.adh_query, 'details': query_details, 'customer_id': self.adh_customer, 'table_name': self._sanitize_string(query_details['title']), } if self.dest_project: report['dest_project'] = self.dest_project if self.dest_dataset: report['dest_dataset'] = self.dest_dataset self.firestore.store_report_config( type=Type.ADH, report_data=report, id=self.adh_query) result = self.run_query(report) report['last_run'] = result self.firestore.store_report_config( type=Type.ADH, report_data=report, id=self.adh_query) logging.info('Result: {result}'.format(result=result)) def _get_adh_service(self) -> Resource: """Create the ADH Service Use the discovery API to create the ADH service Returns: Resource -- ADH service """ adh_service = DiscoverService.get_service(Service.ADH, self.credentials, self.api_key) return adh_service def _sanitize_string(self, original: str) -> str: """Sanitize Strings Convert any non alphanumeric into an '_' as per BQ requirements Arguments: original {str} -- Returns: str -- """ return re.sub('[^a-zA-Z0-9,]', '_', original) def fetch_query_details(self) -> Dict[str, Any]: """Get the Query details Returns: Dict[str, Any] -- [description] """ service = self._get_adh_service() query_id = 'customers/{customer_id}/analysisQueries/{query_id}'.format( customer_id=self.adh_customer, query_id=self.adh_query) query = service.customers().analysisQueries().get(name=query_id).execute() return query def run_query(self, query_details: Dict[str, Any]) -> Dict[str, Any]: """Run the ADH query Arguments: query_details {Dict[str, Any]} -- the details of the query job Returns: Dict[str, Any] -- result of the query run directive """ service = self._get_adh_service() yesterday = datetime.now(tz=pytz.timezone('US/Eastern')) - timedelta(days=1) earliest = yesterday - timedelta(days=60) body = { "spec": { "startDate": { "year": earliest.year, "month": earliest.month, "day": earliest.day }, "endDate": { "year": yesterday.year, "month": yesterday.month, "day": yesterday.day } }, "destTable": '{project}.{dataset}.{table_name}'.format( project=query_details['dest_project'] if 'dest_project' in query_details else self.project, dataset=query_details['dest_dataset'] if 'dest_dataset' in query_details else 'adh_results', table_name=query_details['table_name'] ), "customerId": query_details['customer_id'] } result = service.customers().analysisQueries().start( name=query_details['details']['name'], body=body).execute() return result
class Report2BQ(object): def __init__(self, product: Type, email=None, project=None, report_id=None, profile=None, sa360_url=None, force: bool = False, append: bool = False, infer_schema: bool = False, dest_project: str = None, dest_dataset: str = 'report2bq', notify_topic: str = None, notify_message: str = None): self.product = product self.force = force self.email = email self.append = append self.infer_schema = infer_schema self.report_id = report_id self.sa360_url = unquote(sa360_url) if sa360_url else None self.cm_profile = profile self.project = project self.dest_project = dest_project self.dest_dataset = dest_dataset self.notify_topic = notify_topic self.notify_message = notify_message self.firestore = Firestore(email=email, project=project) def handle_report_fetcher(self, fetcher: ReportFetcher): # Get Latest Report report_object = fetcher.get_latest_report_file(self.report_id) # Normalize Report Details report_data = fetcher.fetch_report_config(report_object=report_object, report_id=self.report_id) last_report = self.firestore.get_report_config(fetcher.report_type, self.report_id) if last_report: if report_data['last_updated'] == last_report[ 'last_updated'] and not self.force: logging.info('No change: ignoring.') return report_data = fetcher.normalize_report_details( report_object=report_object, report_id=self.report_id) report_data['email'] = self.email report_data['append'] = self.append if self.dest_project: report_data['dest_project'] = self.dest_project if self.dest_dataset: report_data['dest_dataset'] = self.dest_dataset if self.notify_topic: report_data['notifier'] = { 'topic': self.notify_topic, } if self.notify_message: report_data['notifier']['message'] = self.notify_message if report_object: csv_header, csv_types = fetcher.read_header(report_data) if csv_header: schema = CSVHelpers.create_table_schema( csv_header, csv_types if self.infer_schema else None) report_data['schema'] = schema fetcher.stream_to_gcs(f'{self.project}-report2bq-upload', report_data) self.firestore.store_report_config(fetcher.report_type, self.report_id, report_data) def handle_sa360(self): sa360 = SA360(project=self.project, email=self.email, infer_schema=self.infer_schema, append=self.append) logging.info(self.sa360_url) id = re.match(r'^.*rid=([0-9]+).*$', self.sa360_url).group(1) report_data = self.firestore.get_report_config(Type.SA360, id) if not report_data: # Create new report details structure report_data = {'id': id, 'url': self.sa360_url} report_data['table_name'] = 'SA360_{id}'.format(id=id) report_data['email'] = self.email if self.dest_project: report_data['dest_project'] = self.dest_project if self.dest_dataset: report_data['dest_dataset'] = self.dest_dataset if self.notify_topic: report_data['notifier'] = { 'topic': self.notify_topic, } if self.notify_message: report_data['notifier']['message'] = self.notify_message sa360.process( bucket='{project}-report2bq-upload'.format(project=self.project), report_details=report_data) self.firestore.store_report_config(Type.SA360, id, report_data) def run(self): logging.info(f'Product: {self.product}') if self.product in [Type.DV360, Type.CM]: fetcher = FetcherFactory.create_fetcher(self.product, email=self.email, project=self.project, profile=self.cm_profile) self.handle_report_fetcher(fetcher=fetcher) elif self.product == Type.SA360: self.handle_sa360()