D-Net

					<ARC to="queryOrganizationsFromOpenOrgsDB"/>

				</ARCS>

			</NODE>

			<NODE name="queryOrganizationsFromOpenOrgsDB" type="QueryDb">

				<DESCRIPTION>query Open Organizations</DESCRIPTION>

				<PARAMETERS>

					<PARAM managedBy="system" name="dbProperty" required="true" type="string">dnet.openorgs.db.name</PARAM>

					<PARAM managedBy="system" name="sql" required="true" type="string">/eu/dnetlib/msro/openaireplus/workflows/hbase/queryOrganizationsFromOpenOrgsDB.sql

					</PARAM>

					<PARAM managedBy="system" name="outputEprParam" required="true" type="string">openOrgsEpr</PARAM>

				</PARAMETERS>

				<ARCS>

					<ARC to="storeOrganizationsFromOpenOrgsDB"/>

				</ARCS>

			</NODE>

			<NODE name="storeOrganizationsFromOpenOrgsDB" type="StoreHBase">

				<DESCRIPTION>Store Open Organizations to HBase</DESCRIPTION>

				<PARAMETERS>

					<PARAM managedBy="system" name="inputEprParam" required="true" type="string">openOrgsEpr</PARAM>

					<PARAM managedBy="system" name="hbaseTableProperty" required="true" type="string">hbase.mapred.datatable</PARAM>

					<PARAM managedBy="system" name="cluster" required="true" type="string">DM</PARAM>

					<PARAM managedBy="user" name="mapping" required="true" type="string" function="obtainValues('dbmf2hbaseMappings', {})"></PARAM>

				</PARAMETERS>

				<ARCS>

  return $x//CONFIGURATION/context/@id/string()

  dnet.openorgs.db.name=dev_orgs

SELECT

	o.id                                                                                                                          AS organizationid,

	coalesce((array_agg(a.acronym))[1], o.name)                                                                                   AS legalshortname,

	o.name                                                                                                                        AS legalname,

	array_agg(DISTINCT n.name)                                                                                                    AS "alternativeNames",

	(array_agg(u.url))[1]                                                                                                         AS websiteurl,

	o.modification_date                                                                                                           AS dateoftransformation,

	false                                                                                                                         AS inferred,

	false                                                                                                                         AS deletedbyinference,

	0.95                                                                                                                          AS trust,

	''                                                                                                                            AS inferenceprovenance,

	'openaire____::openorgs'                                                                                                      AS collectedfromid,

	'OpenOrgs Database'                                                                                                           AS collectedfromname,

	o.country || '@@@dnet:countries'                                                                                              AS country,

	'sysimport:crosswalk:entityregistry@@@sysimport:crosswalk:entityregistry@@@dnet:provenance_actions@@@dnet:provenance_actions' AS provenanceaction,

	array_agg(DISTINCT i.otherid || '###' || i.type)                                                                              AS pid

FROM organizations o

	LEFT OUTER JOIN acronyms a    ON (a.id = o.id)

	LEFT OUTER JOIN urls u        ON (u.id = o.id)

	LEFT OUTER JOIN other_ids i   ON (i.id = o.id)

	LEFT OUTER JOIN other_names n ON (n.id = o.id)

GROUP BY

	o.id,

	o.name,

	o.modification_date,

	o.country