def run(self): """Write data to ElasticSearch if required""" if not self.write_es: return self.cherry_picked = (f'gtr/{self.date}/'.encode('utf-8') + b'COREX_TOPIC_MODEL.n_hidden_140-0.' b'VECTORIZER.binary_True.' b'min_df_0-001.' b'text_field_abstractText' b'.NGRAM.TEST_False.json') if self.cherry_picked is None: # Read the topics data file_ptr = self.input().open("rb") path = file_ptr.read() file_ptr.close() else: path = self.cherry_picked file_io_topics = s3.S3Target( f's3://clio-data/{path.decode("utf-8")}').open("rb") topic_json = json.load(file_io_topics) file_io_topics.close() topic_lookup = topic_json['data']['topic_names'] topic_json = {row['id']: row for row in topic_json['data']['rows']} # Read the raw data file_io_input = s3.S3Target(self.s3_path_in).open("rb") dirty_json = json.load(file_io_input) file_io_input.close() uid, cleaned_json, fields = clean(dirty_json, self.dataset) # Assign topics n_topics, n_found = 0, 0 for row in cleaned_json: id_ = row[f'id_of_{self.dataset}'] if id_ not in topic_json: continue topics = [ k for k, v in topic_json[id_].items() if k != 'id' and v >= 0.2 ] n_found += 1 if len(topics) > 0: n_topics += 1 row[f"terms_topics_{self.dataset}"] = topics logging.info(f'{n_found} documents processed from a possible ' f'{len(cleaned_json)}, of which ' f'{n_topics} have been assigned topics.') fields.add(f"terms_topics_{self.dataset}") fields.add("terms_of_countryTags") fields.add("type_of_entity") # Prepare connection to ES prod_label = '' if self.production else '_dev' es_config = get_config('elasticsearch.config', 'clio') es_config['index'] = f"clio_{self.dataset}{prod_label}" aws_auth_region = es_config.pop('region') es = ElasticsearchPlus(hosts=es_config['host'], port=int(es_config['port']), use_ssl=True, entity_type=self.dataset, aws_auth_region=aws_auth_region, country_detection=True, caps_to_camel_case=True) # Dynamically generate the mapping based on a template with open("clio_mapping.json") as f: mapping = json.load(f) for f in fields: kwargs = {} _type = "text" if f.startswith("terms"): kwargs = { "fields": { "keyword": { "type": "keyword" } }, "analyzer": "terms_analyzer" } elif not f.startswith("textBody"): _type = "keyword" mapping["mappings"]["_doc"]["properties"][f] = dict(type=_type, **kwargs) # Drop, create and send data if es.indices.exists(index=es_config['index']): es.indices.delete(index=es_config['index']) es.indices.create(index=es_config['index'], body=mapping) for id_, row in zip(uid, cleaned_json): es.index(index=es_config['index'], doc_type=es_config['type'], id=id_, body=row) # Drop, create and send data es = ElasticsearchPlus(hosts=es_config['host'], port=int(es_config['port']), use_ssl=True, entity_type='topics', aws_auth_region=aws_auth_region, country_detection=False, caps_to_camel_case=False) topic_idx = f"{es_config['index']}_topics" if es.indices.exists(index=topic_idx): es.indices.delete(index=topic_idx) es.indices.create(index=topic_idx) es.index(index=topic_idx, doc_type=es_config['type'], id='topics', body=topic_lookup) # Touch the checkpoint self.output().touch()
def run(): start_index = os.environ["BATCHPAR_start_index"] end_index = os.environ["BATCHPAR_end_index"] #mysqldb_config = os.environ["BATCHPAR_config"] es_host = os.environ["BATCHPAR_outinfo"] es_port = os.environ["BATCHPAR_out_port"] es_index = os.environ["BATCHPAR_out_index"] es_type = os.environ["BATCHPAR_out_type"] entity_type = os.environ["BATCHPAR_entity_type"] db = os.environ["BATCHPAR_db"] aws_auth_region = os.environ["BATCHPAR_aws_auth_region"] # Read in the US states static_engine = get_mysql_engine("BATCHPAR_config", "mysqldb", "static_data") states_lookup = { row['state_code']: row['state_name'] for _, row in pd.read_sql_table('us_states_lookup', static_engine).iterrows() } states_lookup[None] = None states_lookup[''] = None # Get continent lookup continent_lookup = get_continent_lookup() engine = get_mysql_engine("BATCHPAR_config", "mysqldb", db) Session = sessionmaker(bind=engine) session = Session() cols = [ "application_id", "full_project_num", "fy", "org_city", "org_country", "org_state", "org_zipcode", "org_name", "project_start", "project_end", "project_terms", "project_title", "total_cost", "phr", "ic_name" ] cols_attrs = [getattr(Projects, c) for c in cols] batch_selection = session.query(*cols_attrs).filter( Projects.application_id >= start_index, Projects.application_id <= end_index).selectable df = pd.read_sql(batch_selection, session.bind) df.columns = [c[13::] for c in df.columns] # remove the 'nih_projects_' prefix # geocode the dataframe df = df.rename(columns={'org_city': 'city', 'org_country': 'country'}) df = geocode_dataframe(df) # append iso codes for country df = country_iso_code_dataframe(df) # clean start and end dates for col in ["project_start", "project_end"]: df[col] = df[col].apply(lambda x: _extract_date(x)) # currency is the same for the whole dataset df['total_cost_currency'] = 'USD' # output to elasticsearch field_null_mapping = load_json_from_pathstub("tier_1/field_null_mappings/", "health_scanner.json") strans_kwargs = { 'filename': 'nih.json', 'from_key': 'tier_0', 'to_key': 'tier_1', 'ignore': ['application_id'] } es = ElasticsearchPlus( hosts=es_host, port=es_port, aws_auth_region=aws_auth_region, no_commit=("AWSBATCHTEST" in os.environ), entity_type=entity_type, strans_kwargs=strans_kwargs, field_null_mapping=field_null_mapping, null_empty_str=True, coordinates_as_floats=True, country_detection=True, listify_terms=True, terms_delimiters=(";", ","), caps_to_camel_case=True, null_pairs={"currency_total_cost": "cost_total_project"}) for _, row in df.iterrows(): doc = dict(row.loc[~pd.isnull(row)]) if 'country' in doc: # Try to patch broken US data if doc['country'] == '' and doc['org_state'] != '': doc['country'] = "United States" doc['continent'] = "NA" doc['placeName_state_organisation'] = states_lookup[ doc['org_state']] if 'continent' in doc: continent_code = doc['continent'] else: continent_code = None doc['placeName_continent_organisation'] = continent_lookup[ continent_code] if 'ic_name' in doc: doc['ic_name'] = [doc['ic_name']] uid = doc.pop("application_id") es.index(index=es_index, doc_type=es_type, id=uid, body=doc)
def run(): # Fetch the input parameters s3_bucket = os.environ["BATCHPAR_bucket"] batch_file = os.environ["BATCHPAR_batch_file"] members_perc = int(os.environ["BATCHPAR_members_perc"]) db_name = os.environ["BATCHPAR_db_name"] es_host = os.environ['BATCHPAR_outinfo'] es_port = int(os.environ['BATCHPAR_out_port']) es_index = os.environ['BATCHPAR_out_index'] es_type = os.environ['BATCHPAR_out_type'] entity_type = os.environ["BATCHPAR_entity_type"] aws_auth_region = os.environ["BATCHPAR_aws_auth_region"] routine_id = os.environ["BATCHPAR_routine_id"] # Get continent lookup url = ("https://nesta-open-data.s3.eu-west-2" ".amazonaws.com/rwjf-viz/continent_codes_names.json") continent_lookup = {row["Code"]: row["Name"] for row in requests.get(url).json()} continent_lookup[None] = None # Extract the core topics logging.debug('Getting topics') s3 = boto3.resource('s3') topics_key = f'meetup-topics-{routine_id}.json' topics_obj = s3.Object(s3_bucket, topics_key) core_topics = set(json.loads(topics_obj.get()['Body']._raw_stream.read())) # Extract the group ids for this task ids_obj = s3.Object(s3_bucket, batch_file) group_ids = set(json.loads(ids_obj.get()['Body']._raw_stream.read())) # Extract the mesh terms for this task mesh_obj = s3.Object('innovation-mapping-general', 'meetup_mesh/meetup_mesh_processed.txt') df_mesh = retrieve_mesh_terms('innovation-mapping-general', 'meetup_mesh/meetup_mesh_processed.txt') mesh_terms = format_mesh_terms(df_mesh) # Setup ES+ field_null_mapping = load_json_from_pathstub(("tier_1/" "field_null_mappings/"), "health_scanner.json") strans_kwargs={'filename':'meetup.json', 'from_key':'tier_0', 'to_key':'tier_1', 'ignore':[]} es = ElasticsearchPlus(hosts=es_host, port=es_port, aws_auth_region=aws_auth_region, no_commit=("AWSBATCHTEST" in os.environ), entity_type=entity_type, strans_kwargs=strans_kwargs, field_null_mapping=field_null_mapping, null_empty_str=True, coordinates_as_floats=True, country_detection=True, auto_translate=True) # Generate the lookup for geographies engine = get_mysql_engine("BATCHPAR_config", "mysqldb", db_name) geo_lookup = {} with db_session(engine) as session: query_result = session.query(Geographic).all() for geography in query_result: geo_lookup[geography.id] = {k: v for k, v in geography.__dict__.items() if k in geography.__table__.columns} # Pipe the groups members_limit = get_members_by_percentile(engine, perc=members_perc) with db_session(engine) as session: query_result = (session .query(Group) .filter(Group.members >= members_limit) .filter(Group.id.in_(group_ids)) .all()) for count, group in enumerate(query_result, 1): row = {k: v for k, v in group.__dict__.items() if k in group.__table__.columns} # Filter groups without the required topics topics = [topic['name'] for topic in group.topics if topic['name'] in core_topics] if len(topics) == 0: continue # Assign mesh terms mesh_id = f'{row["id"]}'.zfill(8) row['mesh_terms'] = None if mesh_id in mesh_terms: row['mesh_terms'] = mesh_terms[mesh_id] # Get the geographic data for this row country_name = country_iso_code_to_name(row['country'], iso2=True) geo_key = generate_composite_key(row['city'], country_name) geo = geo_lookup[geo_key] # Clean up the input data row['topics'] = topics row['urlname'] = f"https://www.meetup.com/{row['urlname']}" row['coordinate'] = dict(lat=geo['latitude'], lon=geo['longitude']) row['created'] = dt.strftime(dt.fromtimestamp(row['created']/1000), format="%Y-%m-%d") if row['description'] is not None: row['description'] = BeautifulSoup(row['description'], 'lxml').text row['continent'] = continent_lookup[geo['continent']] row['country_name'] = geo['country'] row['continent_id'] = geo['continent'] row['country'] = geo['country_alpha_2'] row['iso3'] = geo['country_alpha_3'] row['isoNumeric'] = geo['country_numeric'] # Insert to ES _row = es.index(index=es_index, doc_type=es_type, id=row['id'], body=row) if not count % 1000: logging.info(f"{count} rows loaded to elasticsearch") logging.info("Batch job complete.")
def run(): test = literal_eval(os.environ["BATCHPAR_test"]) bucket = os.environ['BATCHPAR_bucket'] batch_file = os.environ['BATCHPAR_batch_file'] db_name = os.environ["BATCHPAR_db_name"] es_host = os.environ['BATCHPAR_outinfo'] es_port = int(os.environ['BATCHPAR_out_port']) es_index = os.environ['BATCHPAR_out_index'] es_type = os.environ['BATCHPAR_out_type'] entity_type = os.environ["BATCHPAR_entity_type"] aws_auth_region = os.environ["BATCHPAR_aws_auth_region"] # database setup engine = get_mysql_engine("BATCHPAR_config", "mysqldb", db_name) static_engine = get_mysql_engine("BATCHPAR_config", "mysqldb", "static_data") states_lookup = { row['state_code']: row['state_name'] for _, row in pd.read_sql_table('us_states_lookup', static_engine).iterrows() } states_lookup["AE"] = "Armed Forces (Canada, Europe, Middle East)" states_lookup["AA"] = "Armed Forces (Americas)" states_lookup["AP"] = "Armed Forces (Pacific)" states_lookup[None] = None # default lookup for non-US countries # Get continent lookup url = "https://nesta-open-data.s3.eu-west-2.amazonaws.com/rwjf-viz/continent_codes_names.json" continent_lookup = { row["Code"]: row["Name"] for row in requests.get(url).json() } continent_lookup[None] = None # es setup field_null_mapping = load_json_from_pathstub("tier_1/field_null_mappings/", "health_scanner.json") strans_kwargs = { 'filename': 'crunchbase_organisation_members.json', 'from_key': 'tier_0', 'to_key': 'tier_1', 'ignore': ['id'] } es = ElasticsearchPlus( hosts=es_host, port=es_port, aws_auth_region=aws_auth_region, no_commit=("AWSBATCHTEST" in os.environ), entity_type=entity_type, strans_kwargs=strans_kwargs, field_null_mapping=field_null_mapping, null_empty_str=True, coordinates_as_floats=True, country_detection=True, listify_terms=True, terms_delimiters=("|", ), null_pairs={"currency_of_funding": "cost_of_funding"}) # collect file nrows = 20 if test else None s3 = boto3.resource('s3') obj = s3.Object(bucket, batch_file) org_ids = json.loads(obj.get()['Body']._raw_stream.read()) logging.info(f"{len(org_ids)} organisations retrieved from s3") org_fields = set(c.name for c in Organization.__table__.columns) geo_fields = [ 'country_alpha_2', 'country_alpha_3', 'country_numeric', 'continent', 'latitude', 'longitude' ] # First get all funders investor_names = defaultdict(list) with db_session(engine) as session: rows = (session.query(Organization, FundingRound).join( FundingRound, Organization.id == FundingRound.company_id).filter( Organization.id.in_(org_ids)).all()) for row in rows: _id = row.Organization.id _investor_names = row.FundingRound.investor_names investor_names[_id] += parse_investor_names(_investor_names) # Pipe orgs to ES with db_session(engine) as session: rows = (session.query(Organization, Geographic).join( Geographic, Organization.location_id == Geographic.id).filter( Organization.id.in_(org_ids)).limit(nrows).all()) for count, row in enumerate(rows, 1): # convert sqlalchemy to dictionary row_combined = { k: v for k, v in row.Organization.__dict__.items() if k in org_fields } row_combined[ 'currency_of_funding'] = 'USD' # all values are from 'funding_total_usd' row_combined.update({ k: v for k, v in row.Geographic.__dict__.items() if k in geo_fields }) row_combined['investor_names'] = list( set(investor_names[row_combined['id']])) # reformat coordinates row_combined['coordinates'] = { 'lat': row_combined.pop('latitude'), 'lon': row_combined.pop('longitude') } # iterate through categories and groups row_combined['category_list'] = [] row_combined['category_group_list'] = [] for category in (session.query(CategoryGroup).select_from( OrganizationCategory).join(CategoryGroup).filter( OrganizationCategory.organization_id == row.Organization.id).all()): row_combined['category_list'].append(category.category_name) row_combined['category_group_list'] += [ group for group in str(category.category_group_list).split('|') if group is not 'None' ] # Add a field for US state name state_code = row_combined['state_code'] row_combined['placeName_state_organisation'] = states_lookup[ state_code] continent_code = row_combined['continent'] row_combined[ 'placeName_continent_organisation'] = continent_lookup[ continent_code] row_combined['updated_at'] = row_combined['updated_at'].strftime( '%Y-%m-%d %H:%M:%S') uid = row_combined.pop('id') _row = es.index(index=es_index, doc_type=es_type, id=uid, body=row_combined) if not count % 1000: logging.info(f"{count} rows loaded to elasticsearch") logging.warning("Batch job complete.")
def run(): bucket = os.environ["BATCHPAR_s3_bucket"] abstract_file = os.environ["BATCHPAR_s3_key"] dupe_file = os.environ["BATCHPAR_dupe_file"] es_config = literal_eval(os.environ["BATCHPAR_outinfo"]) db = os.environ["BATCHPAR_db"] entity_type = os.environ["BATCHPAR_entity_type"] # mysql setup engine = get_mysql_engine("BATCHPAR_config", "mysqldb", db) Session = sessionmaker(bind=engine) session = Session() # retrieve a batch of meshed terms mesh_terms = retrieve_mesh_terms(bucket, abstract_file) mesh_terms = format_mesh_terms(mesh_terms) logging.info(f'batch {abstract_file} contains ' f'{len(mesh_terms)} meshed abstracts') # retrieve duplicate map dupes = retrieve_duplicate_map(bucket, dupe_file) dupes = format_duplicate_map(dupes) # Set up elastic search connection field_null_mapping = load_json_from_pathstub( "tier_1/" "field_null_mappings/", "health_scanner.json") es = ElasticsearchPlus(hosts=es_config['host'], port=es_config['port'], aws_auth_region=es_config['region'], use_ssl=True, entity_type=entity_type, strans_kwargs=None, field_null_mapping=field_null_mapping, null_empty_str=True, coordinates_as_floats=True, country_detection=True, listify_terms=True) all_es_ids = get_es_ids(es, es_config) docs = [] for doc_id, terms in mesh_terms.items(): if doc_id not in all_es_ids: continue try: _filter = Abstracts.application_id == doc_id abstract = (session.query(Abstracts).filter(_filter).one()) except NoResultFound: logging.warning(f'Not found {doc_id} in database') raise NoResultFound(doc_id) clean_abstract_text = clean_abstract(abstract.abstract_text) docs.append({ 'doc_id': doc_id, 'terms_mesh_abstract': terms, 'textBody_abstract_project': clean_abstract_text }) duped_docs = dupes.get(doc_id, []) if len(duped_docs) > 0: logging.info(f'Found {len(duped_docs)} duplicates') for duped_doc in duped_docs: docs.append({ 'doc_id': duped_doc, 'terms_mesh_abstract': terms, 'textBody_abstract_project': clean_abstract_text, 'booleanFlag_duplicate_abstract': True }) # output to elasticsearch logging.warning(f'Writing {len(docs)} documents to elasticsearch') for doc in docs: uid = doc.pop("doc_id") # Extract existing info existing = es.get(es_config['index'], doc_type=es_config['type'], id=uid)['_source'] # Merge existing info into new doc doc = {**existing, **doc} es.index(index=es_config['index'], doc_type=es_config['type'], id=uid, body=doc)
def run(): test = literal_eval(os.environ["BATCHPAR_test"]) bucket = os.environ['BATCHPAR_bucket'] batch_file = os.environ['BATCHPAR_batch_file'] db_name = os.environ["BATCHPAR_db_name"] es_host = os.environ['BATCHPAR_outinfo'] es_port = int(os.environ['BATCHPAR_out_port']) es_index = os.environ['BATCHPAR_out_index'] es_type = os.environ['BATCHPAR_out_type'] entity_type = os.environ["BATCHPAR_entity_type"] aws_auth_region = os.environ["BATCHPAR_aws_auth_region"] # database setup logging.info('Retrieving engine connection') engine = get_mysql_engine("BATCHPAR_config", "mysqldb", db_name) logging.info('Building FOS lookup') fos_lookup = build_fos_lookup(engine, max_lvl=6) nf = NutsFinder() # es setup logging.info('Connecting to ES') strans_kwargs = { 'filename': 'eurito/arxiv-eu.json', 'from_key': 'tier_0', 'to_key': 'tier_1', 'ignore': ['id'] } es = ElasticsearchPlus(hosts=es_host, port=es_port, aws_auth_region=aws_auth_region, no_commit=("AWSBATCHTEST" in os.environ), entity_type=entity_type, strans_kwargs=strans_kwargs, null_empty_str=True, coordinates_as_floats=True, listify_terms=True, do_sort=False, ngram_fields=['textBody_abstract_article']) # collect file logging.info('Retrieving article ids') nrows = 20 if test else None s3 = boto3.resource('s3') obj = s3.Object(bucket, batch_file) art_ids = json.loads(obj.get()['Body']._raw_stream.read()) logging.info(f"{len(art_ids)} article IDs " "retrieved from s3") # Get all grid countries # and country: continent lookup logging.info('Doing country lookup') country_lookup = get_country_region_lookup() eu_countries = get_eu_countries() with db_session(engine) as session: grid_regions = { obj.id: country_lookup[obj.country_code] for obj in session.query(Inst).all() if obj.country_code is not None } grid_countries = { obj.id: obj.country_code for obj in session.query(Inst).all() if obj.country_code is not None } grid_institutes = { obj.id: obj.name for obj in session.query(Inst).all() } grid_latlon = { obj.id: (obj.latitude, obj.longitude) for obj in session.query(Inst).all() } # logging.info('Processing rows') with db_session(engine) as session: for count, obj in enumerate( (session.query(Art).filter(Art.id.in_(art_ids)).all())): row = object_to_dict(obj) # Extract year from date if row['created'] is not None: row['year'] = row['created'].year # Normalise citation count for searchkit if row['citation_count'] is None: row['citation_count'] = 0 # Extract field of study row['fields_of_study'] = make_fos_tree(row['fields_of_study'], fos_lookup) row['_fields_of_study'] = [ f for fields in row['fields_of_study']['nodes'] for f in fields if f != [] ] # Format hierarchical fields as expected by searchkit row['categories'] = [ cat['description'] for cat in row.pop('categories') ] institutes = row.pop('institutes') good_institutes = [ i['institute_id'] for i in institutes if i['matching_score'] > 0.9 ] # Add NUTS regions for inst_id in good_institutes: if inst_id not in grid_latlon: continue lat, lon = grid_latlon[inst_id] if lat is None or lon is None: continue nuts = nf.find(lat=lat, lon=lon) for i in range(0, 4): name = f'nuts_{i}' if name not in row: row[name] = set() for nut in nuts: if nut['LEVL_CODE'] != i: continue row[name].add(nut['NUTS_ID']) for i in range(0, 4): name = f'nuts_{i}' if name in row: row[name] = list(row[name]) # Add other geographies countries = set(grid_countries[inst_id] for inst_id in good_institutes if inst_id in grid_countries) regions = set(grid_regions[inst_id] for inst_id in good_institutes if inst_id in grid_countries) row['countries'] = list(countries) #[c for c, r in countries] row['regions'] = [r for c, r in regions] row['is_eu'] = any(c in eu_countries for c in countries) # Pull out international institute info has_mn = any( is_multinational(inst, grid_countries.values()) for inst in good_institutes) row['has_multinational'] = has_mn # Generate author & institute properties mag_authors = row.pop('mag_authors') if mag_authors is None: row['authors'] = None row['institutes'] = None else: if all('author_order' in a for a in mag_authors): mag_authors = sorted(mag_authors, key=lambda a: a['author_order']) row['authors'] = [ author['author_name'].title() for author in mag_authors ] gids = [ author['affiliation_grid_id'] for author in mag_authors if 'affiliation_grid_id' in author ] row['institutes'] = [ grid_institutes[g].title() for g in gids if g in grid_institutes and g in good_institutes ] if row['institutes'] in (None, []): row['institutes'] = [ grid_institutes[g].title() for g in good_institutes ] uid = row.pop('id') _row = es.index(index=es_index, doc_type=es_type, id=uid, body=row) if not count % 1000: logging.info(f"{count} rows loaded to " "elasticsearch") logging.warning("Batch job complete.")
def run(): test = literal_eval(os.environ["BATCHPAR_test"]) bucket = os.environ['BATCHPAR_bucket'] batch_file = os.environ['BATCHPAR_batch_file'] db_name = os.environ["BATCHPAR_db_name"] es_host = os.environ['BATCHPAR_outinfo'] es_port = int(os.environ['BATCHPAR_out_port']) es_index = os.environ['BATCHPAR_out_index'] es_type = os.environ['BATCHPAR_out_type'] entity_type = os.environ["BATCHPAR_entity_type"] aws_auth_region = os.environ["BATCHPAR_aws_auth_region"] # database setup logging.info('Retrieving engine connection') engine = get_mysql_engine("BATCHPAR_config", "mysqldb", db_name) _engine = get_mysql_engine("BATCHPAR_config", "readonly", "patstat_2019_05_13") # es setup logging.info('Connecting to ES') strans_kwargs = { 'filename': 'eurito/patstat-eu.json', 'from_key': 'tier_0', 'to_key': 'tier_1', 'ignore': ['id'] } es = ElasticsearchPlus(hosts=es_host, port=es_port, aws_auth_region=aws_auth_region, no_commit=("AWSBATCHTEST" in os.environ), entity_type=entity_type, strans_kwargs=strans_kwargs, auto_translate=True, auto_translate_kwargs={'min_len': 20}, null_empty_str=True, coordinates_as_floats=True, do_sort=True, ngram_fields=['textBody_abstract_patent']) # collect file logging.info('Retrieving patent family ids') nrows = 20 if test else None s3 = boto3.resource('s3') obj = s3.Object(bucket, batch_file) docdb_fam_ids = json.loads(obj.get()['Body']._raw_stream.read()) logging.info(f"{len(docdb_fam_ids)} patent family IDs " "retrieved from s3") eu_countries = get_eu_countries() logging.info('Processing rows') _filter = ApplnFamily.docdb_family_id.in_(docdb_fam_ids) with db_session(engine) as session: for obj in session.query(ApplnFamily).filter(_filter).all(): row = object_to_dict(obj) appln_ids = row.pop('appln_id') with db_session(_engine) as _session: _titles = metadata(Tls202ApplnTitle, _session, appln_ids) _abstrs = metadata(Tls203ApplnAbstr, _session, appln_ids) ipcs = metadata(Tls209ApplnIpc, _session, appln_ids) nace2s = metadata(Tls229ApplnNace2, _session, appln_ids) techs = metadata(Tls230ApplnTechnField, _session, appln_ids) # Get persons _pers_applns = metadata(Tls207PersAppln, _session, appln_ids) pers_ids = set(pa['person_id'] for pa in _pers_applns) persons = metadata(Tls906Person, _session, pers_ids, field_selector=Tls906Person.person_id) title = select_text(_titles, 'appln_title_lg', 'appln_title') abstr = select_text(_abstrs, 'appln_abstract_lg', 'appln_abstract') # Get names from lookups ipcs = list(set(i['ipc_class_symbol'].split()[0] for i in ipcs)) nace2s = list(set(n['nace2_code'] for n in nace2s)) techs = list(set(t['techn_field_nr'] for t in techs)) ctrys = list(set(p['person_ctry_code'] for p in persons)) nuts = list(set(p['nuts'] for p in persons)) is_eu = any(c in eu_countries for c in ctrys) # Index the data row = dict(title=title, abstract=abstr, ipc=ipcs, nace2=nace2s, tech=techs, ctry=ctrys, nuts=nuts, is_eu=is_eu, **row) uid = row.pop('docdb_family_id') _row = es.index(index=es_index, doc_type=es_type, id=uid, body=row) logging.warning("Batch job complete.")
def run(): test = literal_eval(os.environ["BATCHPAR_test"]) bucket = os.environ['BATCHPAR_bucket'] batch_file = os.environ['BATCHPAR_batch_file'] db_name = os.environ["BATCHPAR_db_name"] es_host = os.environ['BATCHPAR_outinfo'] es_port = int(os.environ['BATCHPAR_out_port']) es_index = os.environ['BATCHPAR_out_index'] es_type = os.environ['BATCHPAR_out_type'] entity_type = os.environ["BATCHPAR_entity_type"] aws_auth_region = os.environ["BATCHPAR_aws_auth_region"] # database setup engine = get_mysql_engine("BATCHPAR_config", "mysqldb", db_name) fos_lookup = build_fos_lookup(engine) # Setup ngrammer os.environ['MYSQLDBCONF'] = os.environ['BATCHPAR_config'] ngrammer = Ngrammer(database="production") # es setup strans_kwargs={'filename':'arxiv.json', 'from_key':'tier_0', 'to_key':'tier_1', 'ignore':['id']} es = ElasticsearchPlus(hosts=es_host, port=es_port, aws_auth_region=aws_auth_region, no_commit=("AWSBATCHTEST" in os.environ), entity_type=entity_type, strans_kwargs=strans_kwargs, null_empty_str=True, coordinates_as_floats=True, listify_terms=True, do_sort=False) # collect file nrows = 20 if test else None s3 = boto3.resource('s3') obj = s3.Object(bucket, batch_file) art_ids = json.loads(obj.get()['Body']._raw_stream.read()) logging.info(f"{len(art_ids)} article IDs " "retrieved from s3") # Get all grid countries # and country: continent lookup country_lookup = get_country_region_lookup() with db_session(engine) as session: grid_countries = {obj.id: country_lookup[obj.country_code] for obj in session.query(Institute).all() if obj.country_code is not None} grid_institutes = {obj.id: obj.name for obj in session.query(Institute).all()} # current_year = dt.now().year with db_session(engine) as session: for count, obj in enumerate((session.query(Article) .filter(Article.id.in_(art_ids)) .all())): row = object_to_dict(obj) # Extract year from date year = 1990 if row['created'] is not None: row['year'] = row['created'].year year = row['created'].year # Normalise citation count for searchkit if row['citation_count'] is None: row['citation_count'] = 0 row['normalised_citation'] = row['citation_count']/np.log(current_year-year+2) # If abstract doesn't meet requirements, zero novelty # all other novelty will be assigned in a later task text = row['abstract'] + ' ' + row['title'] if (len(text) < 400 or any(x in row['abstract'].lower() for x in ('withdrawn', 'arxiv administrators')) or any(x in row['title'].lower() for x in ('reply to', 'reply on', 'comment to', 'comment on', 'remarks to', 'remarks on'))): row['novelty_of_article'] = 0 processed_tokens = ngrammer.process_document(row['abstract']) row['tokens'] = [t.replace('_', ' ') for tokens in processed_tokens for t in tokens] # Extract field of study Level 0 --> Level 1 paths fos = [] fos_objs = row.pop('fields_of_study') fos_ids = set(fos['id'] for fos in fos_objs) for f in fos_objs: if f['level'] > 0: continue fos += [reversed(fos_lookup[(f['id'], cid)]) for cid in split_ids(f['child_ids']) if cid in fos_ids] # Format hierarchical fields as expected by searchkit cats = [(cat['description'], cat['id'].split('.')[0]) for cat in row.pop('categories')] institutes = row.pop('institutes') good_institutes = [i['institute_id'] for i in institutes if i['matching_score'] > 0.9] countries = set(grid_countries[inst_id] for inst_id in good_institutes if inst_id in grid_countries) row['categories'], _, _ = hierarchy_field(cats) row['fos'], _, _ = hierarchy_field(fos) row['countries'], _, _ = hierarchy_field(countries) # Pull out international institute info has_mn = any(is_multinational(inst, grid_countries.values()) for inst in good_institutes) row['has_multinational'] = has_mn # Generate author & institute properties mag_authors = row.pop('mag_authors') if mag_authors is None: row['authors'] = None row['institutes'] = None #row['novelty_of_article'] = 0 else: if all('author_order' in a for a in mag_authors): mag_authors = sorted(mag_authors, key=lambda a: a['author_order']) row['authors'] = [author['author_name'].title() for author in mag_authors] if len(row['authors']) > 10: row['authors'] = [f"{row['authors'][0]}, et al"] gids = [author['affiliation_grid_id'] for author in mag_authors if 'affiliation_grid_id' in author] row['institutes'] = [grid_institutes[g].title() for g in gids if g in grid_institutes and g in good_institutes] #row['novelty_of_article'] = novelty0 + np.log(novelty1+1) uid = row.pop('id') _row = es.index(index=es_index, doc_type=es_type, id=uid, body=row) if not count % 1000: logging.info(f"{count} rows loaded to elasticsearch") logging.warning("Batch job complete.")
def run(): # Fetch the input parameters s3_bucket = os.environ["BATCHPAR_bucket"] batch_file = os.environ["BATCHPAR_batch_file"] es_host = os.environ['BATCHPAR_outinfo'] es_port = int(os.environ['BATCHPAR_out_port']) es_new_index = os.environ['BATCHPAR_out_index'] es_old_index = os.environ['BATCHPAR_in_index'] es_type = os.environ['BATCHPAR_out_type'] entity_type = os.environ["BATCHPAR_entity_type"] aws_auth_region = os.environ["BATCHPAR_aws_auth_region"] # Extract the article ids in this chunk s3 = boto3.resource('s3') ids_obj = s3.Object(s3_bucket, batch_file) art_ids = json.loads(ids_obj.get()['Body']._raw_stream.read()) logging.info(f'Processing {len(art_ids)} article ids') field_null_mapping = load_json_from_pathstub(("tier_1/" "field_null_mappings/"), "health_scanner.json") es = ElasticsearchPlus(hosts=es_host, port=es_port, aws_auth_region=aws_auth_region, no_commit=("AWSBATCHTEST" in os.environ), entity_type=entity_type, field_null_mapping=field_null_mapping, send_get_body_as='POST') # Iterate over article IDs processed_ids = set() for _id in art_ids: if _id in processed_ids: # To avoid duplicated effort continue # Collect all duplicated data together dupe_ids = {} # For identifying the most recent dupe yearly_funds = [] # The new deduped collection of annual funds hits = {} for hit in es.near_duplicates(index=es_old_index, doc_id=_id, doc_type=es_type, fields=[ "textBody_descriptive_project", "title_of_project", "textBody_abstract_project" ]): # Extract key values src = hit['_source'] hit_id = hit['_id'] # Record this hit processed_ids.add(hit_id) hits[hit_id] = src # Extract year and funding info yearly_funds += extract_yearly_funds(src) year = get_value(src, 'year_fiscal_funding') if year is not None: dupe_ids[hit_id] = year # Get the most recent instance of the duplicates final_id = sorted(hits.keys())[-1] # default if years are all null if len(dupe_ids) > 0: # implies years are not all null final_id, year = Counter(dupe_ids).most_common()[0] body = hits[final_id] processed_ids = processed_ids.union(set(dupe_ids)) # Sort and sum the funding yearly_funds = sorted(yearly_funds, key=lambda row: row['year']) sum_funding = sum(row['cost_ref'] for row in yearly_funds if row['cost_ref'] is not None) # Add funding info and commit to the new index body['json_funding_project'] = yearly_funds body['cost_total_project'] = sum_funding body['date_start_project'] = yearly_funds[0][ 'start_date'] # just in case es.index(index=es_new_index, doc_type=es_type, id=final_id, body=body) logging.info(f'Processed {len(processed_ids)} ids') logging.info("Batch job complete.")