Project

General

Profile

« Previous | Next » 

Revision 57608

Added by Michele Artini over 4 years ago

query to import OpenOrgs without acronyms

View differences:

modules/dnet-openaireplus-workflows/trunk/src/main/resources/eu/dnetlib/msro/openaireplus/workflows/hbase/queryOrganizationsFromOpenOrgsDB.sql
26 26 
	o.country
27 27
28 28 
UNION ALL
29
29
30 30 
SELECT
31 
	'openorgsmesh'||substring(o.id, 13)||'-'||md5(a.acronym)                                                                      AS organizationid,
32 
	a.acronym                                                                                                                     AS legalshortname,
33 
	a.acronym                                                                                                                     AS legalname,
34 
	ARRAY[]::text[]                                                                                                               AS "alternativeNames",
35 
	(array_agg(u.url))[1]                                                                                                         AS websiteurl,
36 
	o.modification_date                                                                                                           AS dateoftransformation,
37 
	false                                                                                                                         AS inferred,
38 
	false                                                                                                                         AS deletedbyinference,
39 
	0.88                                                                                                                          AS trust,
40 
	''                                                                                                                            AS inferenceprovenance,
41 
	'openaire____::openorgs'                                                                                                      AS collectedfromid,
42 
	'OpenOrgs Database'                                                                                                           AS collectedfromname,
43 
	o.country || '@@@dnet:countries'                                                                                              AS country,
44 
	'sysimport:crosswalk:entityregistry@@@sysimport:crosswalk:entityregistry@@@dnet:provenance_actions@@@dnet:provenance_actions' AS provenanceaction,
45 
	array_agg(DISTINCT i.otherid || '###' || i.type || '@@@dnet:pid_types')                                                       AS pid
46 
FROM acronyms a
47 
	LEFT OUTER JOIN organizations o ON (a.id = o.id)
48 
	LEFT OUTER JOIN urls u          ON (u.id = o.id)
49 
	LEFT OUTER JOIN other_ids i     ON (i.id = o.id)
50 
GROUP BY
51 
	o.id, o.modification_date, o.country, a.acronym
52 

  		




  53
  
  
    

  		




  54
  
  
    
UNION ALL


  		




  55
  
  
    
		


  		




  56
  
  
    
SELECT


  		




  57
  31
  
    
	'openorgsmesh'||substring(o.id, 13)||'-'||md5(n.name)                                                                         AS organizationid,


  		




  58
  32
  
    
	n.name                                                                                                                        AS legalshortname,


  		




  59
  33
  
    
	n.name                                                                                                                        AS legalname,


  		












Also available in:  Unified diff