Revision 50431
Added by Claudio Atzori about 6 years ago
| modules/dnet-openaireplus-workflows/branches/dsm/src/main/resources/eu/dnetlib/msro/openaireplus/workflows/hbase/queryDatasources.sql | ||
|---|---|---|
| 1 |
EXPLAIN SELECT
|
|
| 1 |
SELECT |
|
| 2 | 2 |
d.id AS datasourceid, |
| 3 | 3 |
d.id || array_agg(di.pid) AS identities, |
| 4 | 4 |
d.officialname AS officialname, |
| ... | ... | |
| 65 | 65 |
d.qualitymanagementkind AS qualitymanagementkind, |
| 66 | 66 |
d.pidsystems AS pidsystems, |
| 67 | 67 |
d.certificates AS certificates, |
| 68 |
'{}'::text[] AS policies,
|
|
| 68 |
ARRAY[]::text[] AS policies,
|
|
| 69 | 69 |
dc.id AS collectedfromid, |
| 70 | 70 |
dc.officialname AS collectedfromname, |
| 71 | 71 |
d.typology || '@@@' || d.typology || '@@@dnet:datasource_typologies@@@dnet:datasource_typologies' AS datasourcetype, |
| modules/dnet-openaireplus-workflows/branches/dsm/src/main/resources/eu/dnetlib/msro/openaireplus/workflows/hbase/queryDatasourceOrganization.sql | ||
|---|---|---|
| 1 | 1 |
SELECT |
| 2 | 2 |
dor.datasource AS datasource, |
| 3 | 3 |
dor.organization AS organization, |
| 4 |
dor.startdate AS startdate,
|
|
| 5 |
dor.enddate AS enddate,
|
|
| 6 |
dor.inferred AS inferred,
|
|
| 7 |
dor.deletedbyinference AS deletedbyinference,
|
|
| 8 |
dor.trust AS trust,
|
|
| 9 |
dor.inferenceprovenance AS inferenceprovenance,
|
|
| 4 |
NULL AS startdate,
|
|
| 5 |
NULL AS enddate,
|
|
| 6 |
false AS inferred,
|
|
| 7 |
false AS deletedbyinference,
|
|
| 8 |
0.9 AS trust,
|
|
| 9 |
NULL AS inferenceprovenance,
|
|
| 10 | 10 |
|
| 11 |
doc.code || '@@@' || doc.name || '@@@' || dos.code || '@@@' || dos.name AS semantics,
|
|
| 12 |
pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name AS provenanceaction
|
|
| 11 |
'providedBy@@@provided by@@@dnet:datasources_organizations_typologies@@@dnet:datasources_organizations_typologies' AS semantics,
|
|
| 12 |
d.provenanceaction || '@@@' || d.provenanceaction || '@@@dnet:provenanceActions@@@dnet:provenanceActions' AS provenanceaction
|
|
| 13 | 13 |
|
| 14 |
FROM datasource_organization dor |
|
| 14 |
FROM dsm_datasource_organization dor |
|
| 15 |
LEFT OUTER JOIN dsm_datasources d ON (dor.datasource = d.id) |
|
| 15 | 16 |
|
| 16 |
LEFT OUTER JOIN datasources d ON (dor.datasource = d.id) |
|
| 17 |
|
|
| 18 |
LEFT OUTER JOIN class pac ON (pac.code = d.provenanceactionclass) |
|
| 19 |
LEFT OUTER JOIN scheme pas ON (pas.code = d.provenanceactionscheme) |
|
| 20 |
|
|
| 21 |
LEFT OUTER JOIN class doc ON (doc.code = dor.semanticclass) |
|
| 22 |
LEFT OUTER JOIN scheme dos ON (dos.code = dor.semanticscheme) |
|
| 23 |
|
|
| 24 |
-- These clauses have been commented to allow the import of all the datasource on the index |
|
| 25 |
-- WHERE d.datasourceclass != 'entityregistry' |
|
| 26 |
-- AND d.openairecompatibilityclass IS NOT null |
|
| 27 |
-- AND d.openairecompatibilityclass != 'UNKNOWN' |
|
| 28 |
-- AND d.openairecompatibilityclass != 'notCompatible' |
|
| modules/dnet-openaireplus-workflows/branches/dsm/src/main/resources/eu/dnetlib/msro/openaireplus/workflows/hbase/queryDatasourcesForSize.sql | ||
|---|---|---|
| 1 | 1 |
SELECT count(*) |
| 2 |
FROM datasources; |
|
| 2 |
FROM dsm_datasources; |
|
Also available in: Unified diff
updated sql queries used to map datasources and organizations to HBase