Revision 53226
Added by Alessia Bardi over 5 years ago
| modules/dnet-openaireplus-workflows/trunk/src/main/resources/eu/dnetlib/msro/openaireplus/workflows/hbase/queryOrganizations.sql | ||
|---|---|---|
| 1 |
SELECT * FROM orgs |
|
| 2 |
UNION |
|
| 3 | 1 |
SELECT |
| 4 | 2 |
o.id AS organizationid, |
| 5 | 3 |
o.legalshortname AS legalshortname, |
| 6 | 4 |
o.legalname AS legalname, |
| 7 | 5 |
o.websiteurl AS websiteurl, |
| 8 | 6 |
o.logourl AS logourl, |
| 9 |
null AS eclegalbody,
|
|
| 10 |
null AS eclegalperson,
|
|
| 11 |
null AS ecnonprofit,
|
|
| 12 |
null AS ecresearchorganization,
|
|
| 13 |
null AS echighereducation,
|
|
| 14 |
null AS ecinternationalorganizationeurinterests,
|
|
| 15 |
null AS ecinternationalorganization,
|
|
| 16 |
null AS ecenterprise,
|
|
| 17 |
null AS ecsmevalidated,
|
|
| 18 |
null AS ecnutscode,
|
|
| 7 |
o.ec_legalbody AS eclegalbody,
|
|
| 8 |
o.ec_legalperson AS eclegalperson,
|
|
| 9 |
o.ec_nonprofit AS ecnonprofit,
|
|
| 10 |
o.ec_researchorganization AS ecresearchorganization,
|
|
| 11 |
o.ec_highereducation AS echighereducation,
|
|
| 12 |
o.ec_internationalorganizationeurinterests AS ecinternationalorganizationeurinterests,
|
|
| 13 |
o.ec_internationalorganization AS ecinternationalorganization,
|
|
| 14 |
o.ec_enterprise AS ecenterprise,
|
|
| 15 |
o.ec_smevalidated AS ecsmevalidated,
|
|
| 16 |
o.ec_nutscode AS ecnutscode,
|
|
| 19 | 17 |
o.dateofcollection AS dateofcollection, |
| 20 | 18 |
o.lastupdate AS dateoftransformation, |
| 21 | 19 |
false AS inferred, |
| 22 | 20 |
false AS deletedbyinference, |
| 23 |
0.9 AS trust,
|
|
| 21 |
o.trust AS trust,
|
|
| 24 | 22 |
'' AS inferenceprovenance, |
| 25 | 23 |
d.id AS collectedfromid, |
| 26 | 24 |
d.officialname AS collectedfromname, |
| ... | ... | |
| 28 | 26 |
o.country || '@@@' || o.country || '@@@dnet:countries@@@dnet:countries' AS country, |
| 29 | 27 |
'sysimport:crosswalk:entityregistry@@@sysimport:crosswalk:entityregistry@@@dnet:provenance_actions@@@dnet:provenance_actions' AS provenanceaction, |
| 30 | 28 |
|
| 31 |
ARRAY[]::text[] AS pid
|
|
| 29 |
ARRAY[]::text[] AS pid |
|
| 32 | 30 |
FROM dsm_organizations o |
| 33 | 31 |
LEFT OUTER JOIN dsm_datasources d ON (d.id = o.collectedfrom) |
| 34 | 32 |
|
Also available in: Unified diff
Removed reference to old orgs table and added ec specific fields of organizations when loading onto HBASE