D-Net

package eu.dnetlib.iis.core.workflows.stats;

import eu.dnetlib.iis.core.java.PortBindings;

import eu.dnetlib.iis.core.java.Process;

import eu.dnetlib.iis.core.java.porttype.PortType;

import eu.dnetlib.usagestats.export.UsageStatsExporter;

import org.apache.hadoop.conf.Configuration;

import org.apache.log4j.Logger;

import java.util.HashMap;

import java.util.Map;

public class UsageStatsWrapper implements Process {

    private Logger log = Logger.getLogger(this.getClass());

    private UsageStatsExporter usageStatsExporter;

    @Override

    public void run(PortBindings portBindings, Configuration context, Map<String, String> parameters) throws Exception {

        usageStatsExporter = new UsageStatsExporter();

        usageStatsExporter.setUsername(parameters.get("piwik_username"));

        usageStatsExporter.setPassword(parameters.get("piwik_pass"));

        usageStatsExporter.setHttpProtocol(parameters.get("piwik_httpProtocol"));

        usageStatsExporter.setPiwikUrl(parameters.get("piwik_url"));

        usageStatsExporter.setSiteId(parameters.get("piwik_siteId"));

        usageStatsExporter.setStartDate(parameters.get("piwik_startDate"));

        usageStatsExporter.setStartDate(parameters.get("piwik_startDate"));

        usageStatsExporter.setFinalDate(parameters.get("piwik_finalDate"));

        usageStatsExporter.setLogsPath(parameters.get("piwik_logsPath"));

        usageStatsExporter.setFilterOffset(parameters.get("piwik_filterOffset"));

        //usageStatsExporter.setDbSchema(parameters.get("piwik_schema"));

        usageStatsExporter.setDbUrl(parameters.get("Stats_db_Url"));

        usageStatsExporter.setDbUsername(parameters.get("Stats_db_User"));

        usageStatsExporter.setDbPassword(parameters.get("Stats_db_Pass"));

        usageStatsExporter.setTokenAuth(parameters.get("token_auth"));

        log.info("Parameters: " + usageStatsExporter.getDbUrl() + usageStatsExporter.getUsername() + usageStatsExporter.getDbPassword());

        log.info("Parameters: " + usageStatsExporter.getDbSchema() + usageStatsExporter.getLogsPath());

        for (Map.Entry<String, String> e : parameters.entrySet()) {

            log.info(e.getKey() + ":" + e.getValue());

        }

        try {

            usageStatsExporter.export();

            log.info("Usage Stats Exported!...");

        } catch (Exception e){

            log.error("Usage Stats Export failed: ", e);

            throw new Exception("Usage Stats Export failed: " + e.toString(), e);

        }

    }

    @Override

    public Map<String, PortType> getInputPorts() {

        return createInputPorts();

    }

    @Override

    public Map<String, PortType> getOutputPorts() {

        return createOutputPorts();

    }

    private static HashMap<String, PortType> createInputPorts() {

        HashMap<String, PortType> inputPorts = new HashMap<String, PortType>();

        return inputPorts;

    }

    private static HashMap<String, PortType> createOutputPorts() {

        HashMap<String, PortType> outputPorts = new HashMap<String, PortType>();

        return outputPorts;

    }

}

mvn clean package -Poozie,deploy-local -Dworkflow.source.dir=eu/dnetlib/iis/core/examples/cloner

#!/usr/bin/env python

## Generates a new version of the "generate_example_workflow_apps.properties" 

## file that contains paths to all of the example workflows stored in this

## project. This is done by scanning the directory tree and searching for

## directories that look like they contain workflow definitions.

from __future__ import print_function

import os

import os.path

dir_with_examples = "src/test/resources/eu/dnetlib/iis/core/examples"

dirs_to_ignore = [".svn"]

output_file = "src/main/scripts/generate_example_workflow_apps.properties"

def does_contain_example(dir_path):

	if os.path.exists(os.path.join(dir_path, "oozie_app")):

		return True

	else:

		return False

examples = []

for root, dirs, files in os.walk(dir_with_examples):

	for dir_to_ignore in dirs_to_ignore:

		dirs.remove(dir_to_ignore)

	dirs_to_remove = []

	for dir_ in dirs:

		dir_path = os.path.join(root, dir_)

		if does_contain_example(dir_path):

			examples.append(dir_path)

			dirs_to_remove.append(dir_)

	for dir_to_remove in dirs_to_remove:

		dirs.remove(dir_to_remove)

examples = sorted(examples)

with open(output_file, "w") as f:

	for e in examples:

		print(e, file=f)

	print("# remember to leave '\\n' after the last line\n", file=f)

eu/dnetlib/iis/core/examples/hadoopstreaming/cloner_with_unicode_escape_codes

eu/dnetlib/iis/core/examples/hadoopstreaming/cloner_without_reducer

eu/dnetlib/iis/core/examples/hadoopstreaming/cloner_without_reducer_with_explicit_schema_file

eu/dnetlib/iis/core/examples/hadoopstreaming/cloner_without_reducer_with_subworkflow

eu/dnetlib/iis/core/examples/hadoopstreaming/wordcount_with_distributed_cache

eu/dnetlib/iis/core/examples/java/cloner

eu/dnetlib/iis/core/examples/java/joiner

eu/dnetlib/iis/core/examples/java/json_based_producer_and_consumer

eu/dnetlib/iis/core/examples/java/json_based_producer_and_consumer-failing

eu/dnetlib/iis/core/examples/java/line_by_line_copier

eu/dnetlib/iis/core/examples/javamapreduce/cloner

eu/dnetlib/iis/core/examples/javamapreduce/cloner_with_explicit_schema

eu/dnetlib/iis/core/examples/javamapreduce/cloner_with_multiple_output

eu/dnetlib/iis/core/examples/javamapreduce/cloner_with_multiple_output_with_explicit_schema

eu/dnetlib/iis/core/examples/javamapreduce/cloner_with_multiple_output_without_reducer

eu/dnetlib/iis/core/examples/javamapreduce/cloner_with_multiple_output_without_reducer_with_explicit_schema

eu/dnetlib/iis/core/examples/javamapreduce/cloner_without_reducer

eu/dnetlib/iis/core/examples/javamapreduce/oldapi/cloner

eu/dnetlib/iis/core/examples/javamapreduce/oldapi/cloner_with_explicit_schema

eu/dnetlib/iis/core/examples/javamapreduce/person_by_age_splitter

eu/dnetlib/iis/core/examples/javamapreduce/reverse_relation

eu/dnetlib/iis/core/examples/parallel/parallel_joiner

eu/dnetlib/iis/core/examples/pig/basic

eu/dnetlib/iis/core/examples/pig/joiner

eu/dnetlib/iis/core/examples/pig/joiner_with_explicit_schema

eu/dnetlib/iis/core/examples/pig/person_by_docs_filter

eu/dnetlib/iis/core/examples/pig/person_by_docs_filter_with_subworkflow

eu/dnetlib/iis/core/examples/protobuf/java/cloner

eu/dnetlib/iis/core/examples/protobuf/java/joiner

eu/dnetlib/iis/core/examples/protobuf/java/line_by_line_copier

eu/dnetlib/iis/core/examples/protobuf/javamapreduce/cloner

eu/dnetlib/iis/core/examples/protobuf/javamapreduce/cloner_with_multiple_output

eu/dnetlib/iis/core/examples/protobuf/javamapreduce/cloner_without_reducer

eu/dnetlib/iis/core/examples/protobuf/javamapreduce/person_by_age_splitter

eu/dnetlib/iis/core/examples/protobuf/subworkflow/cloners

eu/dnetlib/iis/core/examples/subworkflow/cloners

# remember to leave '\n' after the last line

It is assumed that the scripts in this directory are executed with the current directory set to the main directory of the project - the one that contains the `pom.xml` file.

The scripts with the `run_locally` prefix generate example Oozie workflow applications that are supposed to be run on a local installation of Hadoop (in the standalone or pseudo-distributed mode). On the other hand, scripts with the `run_on_cluster` prefix generate example Oozie workflow applications that are supposed to be run on OpenAIRE+ Hadoop cluster.

mvn clean package -Pattach-test-resources,oozie,deploy -Dworkflow.source.dir=eu/dnetlib/iis/core/examples/javamapreduce/reverse_relation

mvn clean package -Poozie,deploy -Dworkflow.source.dir=eu/dnetlib/iis/core/examples/cloner

mvn clean package -Pattach-test-resources,oozie,deploy-local -Dworkflow.source.dir=eu/dnetlib/iis/core/examples/javamapreduce/reverse_relation

Stats_db_Driver=org.postgresql.Driver

Stats_db_Pass=sqoop

Stats_db_Url=jdbc:postgresql://vatopedi.di.uoa.gr:5432/stats

Stats_db_User=sqoop

Stats_db_table_map=datasourceLanguage=datasource_languages,datasource=datasource,project=project,result=result,organization=organization,datasourceOrganization=datasource_organizations,datasourceTopic=datasource_topics,projectOrganization=project_organizations,resultClaim=result_claims,resultClassification=result_classifications,resultConcept=result_concepts,resultLanguage=result_languages,resultOrganization=result_organizations,resultResult=result_results,resultProject=project_results,resultResult=result_results,resultTopic=result_topics,category=category,claim=claim,concept=concept,category=category,context=context,datasourceLanguage=datasource_languages,resultLanguage=result_languages,resultDatasource=result_datasources, resultDescription=result_descriptions,datasourceStats=datasource_stats,organizationStats=organization_stats,projectStats=project_stats,resultStats=result_stats,resultOid=result_oids,projectOid=project_oids,datasourceOid=datasource_oids,organizationOid=organization_oids,resultPid=result_pids,resultCitation=result_citations

Stats_delim_Character=!

Stats_enclosing_Character=#

Stats_output_Path=/tmp/tstats/

Stats_sqoop_RecsPerStatement=10000

Stats_sqoop_ReducersCount=8

Stats_sqoop_StatementPerTrans=1000000

dfs.client.failover.proxy.provider.dm-cluster-nn=org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider

dfs.ha.namenodes.dm-cluster-nn=nn1,nn2

dfs.namenode.http-address.dm-cluster-nn.nn2=namenode2.hadoop.dm.openaire.eu:50070

dfs.namenode.rpc-address.dm-cluster-nn.nn2=namenode2.hadoop.dm.openaire.eu:8020

dfs.replication=2

fs.defaultFS=hdfs://dm-cluster-nn

hadoop.rpc.socket.factory.class.default=org.apache.hadoop.net.StandardSocketFactory

hadoop.security.auth_to_local=DEFAULT

hadoop.security.authentication=simple

hbase.rootdir=hdfs://dm-cluster-nn/hbase

hbase.security.authentication=simple

hbase.zookeeper.client.port=2181

hbase.zookeeper.property.clientPort=2181

hbase.zookeeper.quorum=namenode1.hadoop.dm.openaire.eu,namenode2.hadoop.dm.openaire.eu,jobtracker1.hadoop.dm.openaire.eu,jobtracker2.hadoop.dm.openaire.eu,hbase-master1.hadoop.dm.openaire.eu

mapred.client.failover.proxy.provider.dm-cluster-jt=org.apache.hadoop.mapred.ConfiguredFailoverProxyProvider

mapred.job.tracker=dm-cluster-jt

mapred.jobtrackers.dm-cluster-jt=jt1,jt2

mapred.mapper.new-api=true

mapred.reducer.new-api=true

oozie.service.loc=http://oozie.hadoop.dm.openaire.eu:11000/oozie

oozie.wf.application.path=hdfs://dm-cluster-nn/user/tsampikos.livisianos/core/javamapreduce/stats/oozie_app

piwik_filterOffset=5

piwik_finalDate=2016-01-03

piwik_httpProtocol=https

piwik_logsPath=/tmp/tpiwikLogs_update/

piwik_pass=eiLae9EiyahXea9aemiesiiJ

piwik_siteId=5

piwik_startDate=2016-01-01

piwik_url=analytics.openaire.eu

piwik_username=usage_openaire

token_auth=32846584f571be9b57488bf4088f30ea

workingDir=/user/tsampikos.livisianos/core/javamapreduce/stats/working_dir

zookeeper.znode.parent=/hbase

zookeeper.znode.rootserver=root-region-server

<workflow-app name="usage-stats-export"

              xmlns="uri:oozie:workflow:0.4">

    <global>

        <job-tracker>${jobTracker}</job-tracker>

        <name-node>${nameNode}</name-node>

        <configuration>

            <property>

                <name>mapred.job.queue.name</name>

                <value>${queueName}</value>

            </property>

            <property>

                <name>oozie.sqoop.log.level</name>

                <value>DEBUG</value>

            </property>

            <property>

                <name>oozie.launcher.mapred.job.priority</name>

                <value>NORMAL</value>

            </property>

            <property>

                <name>mapred.job.priority</name>

                <value>NORMAL</value>

            </property>

        </configuration>

    </global>

    <start to="exportUsageStats"/>

    <action name='exportUsageStats'>

        <java>

            <prepare>

                <delete path="${nameNode}${piwik_logsPath}"/>

                <mkdir path="${nameNode}${piwik_logsPath}"/>

            </prepare>

            <configuration>

                <property>

                    <name>mapred.job.queue.name</name>

                    <value>${queueName}</value>

                </property>

                <property>

                    <name>oozie.sqoop.log.level</name>

                    <value>DEBUG</value>

                </property>

            </configuration>

            <main-class>eu.dnetlib.iis.core.java.ProcessWrapper</main-class>

            <arg>-SworkingDir=${workingDir}</arg>

            <arg>eu.dnetlib.iis.core.workflows.stats.UsageStatsWrapper</arg>

            <arg>-Ppiwik_username=${piwik_username}</arg>

            <arg>-Ppiwik_pass=${piwik_pass}</arg>

            <arg>-Ppiwik_httpProtocol=${piwik_httpProtocol}</arg>

            <arg>-Ppiwik_url=${piwik_url}</arg>

            <arg>-Ppiwik_siteId=${piwik_siteId}</arg>

            <arg>-Ppiwik_startDate=${piwik_startDate}</arg>

            <arg>-Ppiwik_finalDate=${piwik_finalDate}</arg>

            <arg>-Ppiwik_logsPath=${piwik_logsPath}</arg>

            <arg>-Ppiwik_filterOffset=${piwik_filterOffset}</arg>

            <arg>-Ppiwiki_schema=${piwiki_schema}</arg>

            <arg>-PStats_db_Url=${Stats_db_Url}</arg>

            <arg>-PStats_db_User=${Stats_db_User}</arg>

            <arg>-PStats_db_Pass=${Stats_db_Pass}</arg>

            <arg>-Ptoken_auth=${token_auth}</arg>

        </java>

        <ok to="cleanUpHDFS"/>

        <error to="fail"/>

    </action>

    <action name="cleanUpHDFS">

        <fs>

            <delete path="${nameNode}${piwik_logsPath}"/>

        </fs>

        <ok to="end"/>

        <error to="fail"/>

    </action>

    <kill name="fail">

        <message>

            Unfortunately, the process failed -- error message: [${wf:errorMessage(wf:lastErrorNode())}]

        </message>

    </kill>

    <end name="end"/>

</workflow-app>

log4j.rootLogger=DEBUG,oozie

log4j.appender.oozie=org.apache.log4j.RollingFileAppender

log4j.appender.oozie.Target=System.out

log4j.appender.oozie.layout=org.apache.log4j.PatternLayout

log4j.appender.oozie.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

log4j.appender.oozie.DatePattern='.'yyyy-MM-dd-HH

log4j.appender.none=org.apache.log4j.varia.NullAppender

# Direct log messages to a log file

log4j.appender.oozie=org.apache.log4j.RollingFileAppender

log4j.appender.oozie.File=/tmp/statsExportLog

log4j.appender.oozie.MaxFileSize=1MB

log4j.appender.oozie.MaxBackupIndex=1

log4j.appender.oozie.layout=org.apache.log4j.PatternLayout

log4j.appender.oozie.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n

log4j.logger.org.apache.hadoop=oozie,DEBUG, test

log4j.logger.eu.dnetlib.iis=DEBUG,oozie

<?xml version="1.0"?>

<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<!--

  Copyright (c) 2010 Yahoo! Inc. All rights reserved.

  Licensed under the Apache License, Version 2.0 (the "License");

  you may not use this file except in compliance with the License.

  You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software

  distributed under the License is distributed on an "AS IS" BASIS,

  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

  See the License for the specific language governing permissions and

  limitations under the License. See accompanying LICENSE file.

-->

<configuration>

    <property>

        <name>oozie.service.JPAService.jdbc.driver</name>

        <value>org.hsqldb.jdbcDriver</value>

    </property>

    <property>

        <name>oozie.service.JPAService.jdbc.url</name>

        <value>jdbc:hsqldb:mem:oozie-db;create=true</value>

    </property>

</configuration>

<?xml version="1.0"?>

<!--

  Licensed to the Apache Software Foundation (ASF) under one

  or more contributor license agreements.  See the NOTICE file

  distributed with this work for additional information

  regarding copyright ownership.  The ASF licenses this file

  to you under the Apache License, Version 2.0 (the

  "License"); you may not use this file except in compliance

  with the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software

  distributed under the License is distributed on an "AS IS" BASIS,

  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

  See the License for the specific language governing permissions and

  limitations under the License.

-->

<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<configuration>

    <property>

      <name>oozie.service.JPAService.jdbc.driver</name>

        <value>com.mysql.jdbc.Driver</value>

        <description>JDBC driver class.</description>

    </property>

    <property>

        <name>oozie.test.db.port</name>

        <value>3306</value>

    </property>

    <property>

      <name>oozie.service.JPAService.jdbc.url</name>

        <value>jdbc:mysql://${oozie.test.db.host}:${oozie.test.db.port}/oozie</value>

        <description>JDBC URL.</description>

    </property>

    <property>

        <name>oozie.service.JPAService.jdbc.username</name>

        <value>oozie</value>

        <description>DB user name.</description>

    </property>

    <property>

        <name>oozie.service.JPAService.jdbc.password</name>

        <value>oozie</value>

        <description>

            DB user password. IMPORTANT: if password is emtpy leave a 1 space string, the service trims the

            value, if empty Configuration assumes it is NULL.

        </description>

    </property>

</configuration>

<?xml version="1.0"?>

<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<!--

  Copyright (c) 2010 Yahoo! Inc. All rights reserved.

  Licensed under the Apache License, Version 2.0 (the "License");

  you may not use this file except in compliance with the License.

  You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software

  distributed under the License is distributed on an "AS IS" BASIS,

  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

  See the License for the specific language governing permissions and

  limitations under the License. See accompanying LICENSE file.

-->

<configuration>

    <property>

        <name>oozie.service.JPAService.jdbc.driver</name>

        <value>oracle.jdbc.driver.OracleDriver</value>

    </property>

    <property>

        <name>oozie.test.db.port</name>

        <value>1521</value>

    </property>

    <property>

        <name>oozie.test.db.name</name>

        <value>xe</value>

    </property>

    <property>

        <name>oozie.service.JPAService.jdbc.url</name>

        <value>jdbc:oracle:thin:@//${oozie.test.db.host}:${oozie.test.db.port}/${oozie.test.db.name}</value>

    </property>

    <property>

        <name>oozie.service.JPAService.jdbc.username</name>

        <value>oozie</value>

    </property>

    <property>

        <name>oozie.service.JPAService.jdbc.password</name>

        <value>oozie</value>

    </property>

</configuration>

<?xml version="1.0"?>

<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<!--

  Copyright (c) 2010 Yahoo! Inc. All rights reserved.

  Licensed under the Apache License, Version 2.0 (the "License");

  you may not use this file except in compliance with the License.

  You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software

  distributed under the License is distributed on an "AS IS" BASIS,

  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

  See the License for the specific language governing permissions and

  limitations under the License. See accompanying LICENSE file.

-->

<configuration>

    <property>

        <name>oozie.service.JPAService.jdbc.driver</name>

        <value>org.postgresql.Driver</value>

    </property>

    <property>

        <name>oozie.test.db.port</name>

        <value>5432</value>

    </property>

    <property>

        <name>oozie.test.db.name</name>

        <value>oozie</value>

    </property>

    <property>

        <name>oozie.service.JPAService.jdbc.url</name>

        <value>jdbc:postgresql://${oozie.test.db.host}:${oozie.test.db.port}/${oozie.test.db.name}</value>

    </property>

    <property>

        <name>oozie.service.JPAService.jdbc.username</name>

        <value>oozie</value>

    </property>

    <property>

        <name>oozie.service.JPAService.jdbc.password</name>

        <value>oozie</value>

    </property>

</configuration>

#

# Licensed to the Apache Software Foundation (ASF) under one

# or more contributor license agreements.  See the NOTICE file

# distributed with this work for additional information

# regarding copyright ownership.  The ASF licenses this file

# to you under the Apache License, Version 2.0 (the

# "License"); you may not use this file except in compliance

# with the License.  You may obtain a copy of the License at

# 

#      http://www.apache.org/licenses/LICENSE-2.0

# 

# Unless required by applicable law or agreed to in writing, software

# distributed under the License is distributed on an "AS IS" BASIS,

# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

# See the License for the specific language governing permissions and

# limitations under the License.

#

#    http://www.apache.org/licenses/LICENSE-2.0

#

# Unless required by applicable law or agreed to in writing, software

# distributed under the License is distributed on an "AS IS" BASIS,

# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

# See the License for the specific language governing permissions and

# limitations under the License. See accompanying LICENSE file.

#

log4j.appender.oozie=org.apache.log4j.ConsoleAppender

log4j.appender.oozie.Target=System.out

log4j.appender.oozie.layout=org.apache.log4j.PatternLayout

log4j.appender.oozie.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

log4j.appender.null=org.apache.log4j.varia.NullAppender

log4j.logger.org.apache=INFO, oozie

log4j.logger.org.mortbay=WARN, oozie

log4j.logger.org.hsqldb=WARN, oozie

log4j.logger.opslog=NONE, null

log4j.logger.applog=NONE, null

log4j.logger.instrument=NONE, null

log4j.logger.a=ALL, null

<?xml version="1.0"?>

<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<!--

  Licensed to the Apache Software Foundation (ASF) under one

  or more contributor license agreements.  See the NOTICE file

  distributed with this work for additional information

  regarding copyright ownership.  The ASF licenses this file

  to you under the Apache License, Version 2.0 (the

  "License"); you may not use this file except in compliance

  with the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software

  distributed under the License is distributed on an "AS IS" BASIS,

  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

  See the License for the specific language governing permissions and

  limitations under the License.

-->

<configuration>

  <property>

<name>fs.default.name</name>

<value>hdfs://nmis-hadoop-cluster</value>

  </property>

    <property>

        <name>mapreduce.jobtracker.kerberos.principal</name>

        <value>mapred/_HOST@LOCALREALM</value>

    </property>

    <property>

        <name>dfs.namenode.kerberos.principal</name>

        <value>hdfs/_HOST@LOCALREALM</value>

    </property>

    <property>

        <name>mapreduce.framework.name</name>

        <value>yarn</value>

    </property>

</configuration>

#

# Licensed to the Apache Software Foundation (ASF) under one

# or more contributor license agreements.  See the NOTICE file

# distributed with this work for additional information

# regarding copyright ownership.  The ASF licenses this file

# to you under the Apache License, Version 2.0 (the

# "License"); you may not use this file except in compliance

# with the License.  You may obtain a copy of the License at

# 

#      http://www.apache.org/licenses/LICENSE-2.0

# 

# Unless required by applicable law or agreed to in writing, software

# distributed under the License is distributed on an "AS IS" BASIS,

# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

# See the License for the specific language governing permissions and

# limitations under the License.

#

#    http://www.apache.org/licenses/LICENSE-2.0

#

# Unless required by applicable law or agreed to in writing, software

# distributed under the License is distributed on an "AS IS" BASIS,

# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

# See the License for the specific language governing permissions and

# limitations under the License. See accompanying LICENSE file.

#

log4j.appender.oozie=org.apache.log4j.ConsoleAppender

log4j.appender.oozie.Target=System.out

log4j.appender.oozie.layout=org.apache.log4j.PatternLayout

log4j.appender.oozie.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

log4j.appender.null=org.apache.log4j.varia.NullAppender

log4j.logger.org.apache=INFO, oozie

log4j.logger.org.mortbay=WARN, oozie

log4j.logger.org.hsqldb=WARN, oozie

log4j.logger.opslog=NONE, null

log4j.logger.applog=NONE, null

log4j.logger.instrument=NONE, null

log4j.logger.a=NONE, null

ATTENTION: Please **do not touch this directory** nor its contents since it might break the integration tests.

This directory, its subdirectories, and contained files are here as a hack to make the Oozie integration unit tests work.

Details

-------

Oozie tests assume that they're placed inside directory tree of Oozie source code -- see the source code of class `XTestCase` which is an ancestor of `MiniOozieTestCase` class which, in turn, should be inherited by your test case class. 

How to get the source code of the `XTestCase` class:

- download source code of the Ubuntu's `oozie` package prepared by Cloudera (`apt-get source oozie`). It is version 3.1.3+155 of this package. 

- open file `oozie-3.1.3+155/src/core/src/test/java/org/apache/oozie/test/XTestCase.java` and look at lines 93-105.

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">

    <parent>

        <groupId>eu.dnetlib</groupId>

        <artifactId>icm-iis-parent-container</artifactId>

        <version>1.0.3</version>

    </parent>

    <modelVersion>4.0.0</modelVersion>

    <artifactId>dnet-openaire-usage-stats-workflow</artifactId>

    <packaging>jar</packaging>

    <version>2.0.0</version>

    <!-- 	REMOVE BEFORE DEPLOYMENT -->

    <properties>

        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

        <iis.hadoop.frontend.home.dir>home</iis.hadoop.frontend.home.dir>

        <queueName>default</queueName>

        <iis.hadoop.frontend.host.name>gateway.hadoop.dm.openaire.eu</iis.hadoop.frontend.host.name>

        <oozieServiceLoc>http://oozie.hadoop.dm.openaire.eu:11000/oozie</oozieServiceLoc>

        <nameNode>hdfs://dm-cluster-nn</nameNode>

        <jobTracker>dm-cluster-jt</jobTracker>

    </properties>

    <dependencies>

        <dependency>

            <groupId>eu.dnetlib</groupId>

            <artifactId>icm-iis-core</artifactId>

            <version>1.0.2</version>

        </dependency>

        <dependency>

            <groupId>eu.dnetlib</groupId>

            <artifactId>dnet-openaire-usage-stats</artifactId>

            <version>2.0.0-SNAPSHOT</version>

        </dependency>

    </dependencies>

    <repositories>

        <repository>

            <id>cloudera</id>

            <name>Cloudera Repository</name>

            <url>https://repository.cloudera.com/artifactory/cloudera-repos</url>

            <releases>

                <enabled>true</enabled>

            </releases>

            <snapshots>

                <enabled>false</enabled>

            </snapshots>

        </repository>

    </repositories>

</project>

Execute the scripts in the following order:

1. `upload_workflow.sh`

2. `run_workflow.sh`

3. `print_working_dir.sh` or `get_working_dir.sh`

#!/bin/bash

cd ~/workspace/DNET45/dnet-openaire-stats/trunk

mvn clean install -Dmaven.test.skip=true

cd ~/workspace/DNET45/dnet-openaire-usage-stats-export/trunk

mvn clean install -Dmaven.test.skip=true

cd ~/workspace/DNET45/dnet-openaire-stats-workflow/trunk

mvn clean package  -Dworkflow.source.dir=eu/dnetlib/iis/core/javamapreduce/stats -Poozie-package -Diis.hadoop.frontend.home.dir=/home  -Duser.name=tsampikos.livisianos

scp ./target/oozie-package.tar.gz tsampikos.livisianos@gateway.hadoop.dm.openaire.eu:~/dnet45

<?xml version="1.0" encoding="UTF-8"?>

<module org.jetbrains.idea.maven.project.MavenProjectsManager.isMavenModule="true" type="JAVA_MODULE" version="4">

  <component name="NewModuleRootManager" LANGUAGE_LEVEL="JDK_1_8">

    <output url="file://$MODULE_DIR$/target/classes" />

    <output-test url="file://$MODULE_DIR$/target/test-classes" />

    <content url="file://$MODULE_DIR$">

      <sourceFolder url="file://$MODULE_DIR$/src/main/java" isTestSource="false" />

      <sourceFolder url="file://$MODULE_DIR$/src/main/resources" type="java-resource" />

      <excludeFolder url="file://$MODULE_DIR$/target" />

    </content>

    <orderEntry type="inheritedJdk" />

    <orderEntry type="sourceFolder" forTests="false" />

    <orderEntry type="library" name="Maven: eu.dnetlib:icm-iis-core:1.0.2" level="project" />

    <orderEntry type="library" name="Maven: eu.dnetlib:icm-iis-3rdparty-avrojsoncoders:1.0.3" level="project" />

    <orderEntry type="library" name="Maven: org.apache.commons:commons-lang3:3.1" level="project" />

    <orderEntry type="library" name="Maven: commons-cli:commons-cli:1.2" level="project" />

    <orderEntry type="library" name="Maven: org.apache.avro:avro:1.7.4" level="project" />

    <orderEntry type="library" name="Maven: org.codehaus.jackson:jackson-core-asl:1.8.8" level="project" />

    <orderEntry type="library" name="Maven: org.codehaus.jackson:jackson-mapper-asl:1.8.8" level="project" />

    <orderEntry type="library" name="Maven: com.thoughtworks.paranamer:paranamer:2.3" level="project" />

    <orderEntry type="library" name="Maven: org.xerial.snappy:snappy-java:1.0.4.1" level="project" />

    <orderEntry type="library" name="Maven: org.apache.commons:commons-compress:1.4.1" level="project" />

    <orderEntry type="library" name="Maven: org.tukaani:xz:1.0" level="project" />

    <orderEntry type="library" name="Maven: org.slf4j:slf4j-api:1.6.4" level="project" />

    <orderEntry type="library" name="Maven: org.apache.avro:avro-mapred:hadoop2:1.7.4" level="project" />

    <orderEntry type="library" name="Maven: org.apache.avro:avro-ipc:1.7.4" level="project" />

    <orderEntry type="library" name="Maven: io.netty:netty:3.4.0.Final" level="project" />

    <orderEntry type="library" name="Maven: org.apache.velocity:velocity:1.7" level="project" />

    <orderEntry type="library" name="Maven: org.mortbay.jetty:servlet-api:2.5-20081211" level="project" />

    <orderEntry type="library" name="Maven: org.apache.avro:avro-ipc:tests:1.7.4" level="project" />

    <orderEntry type="library" name="Maven: com.google.code.gson:gson:2.2.4" level="project" />

    <orderEntry type="module" module-name="dnet-openaire-usage-stats" />

    <orderEntry type="library" name="Maven: com.googlecode.json-simple:json-simple:1.1.1" level="project" />

    <orderEntry type="library" name="Maven: junit:junit:4.10" level="project" />

    <orderEntry type="library" name="Maven: org.hamcrest:hamcrest-core:1.1" level="project" />

    <orderEntry type="library" name="Maven: org.apache.hadoop:hadoop-common:2.0.0-cdh4.7.0" level="project" />

    <orderEntry type="library" name="Maven: org.apache.hadoop:hadoop-annotations:2.0.0-cdh4.7.0" level="project" />

    <orderEntry type="library" name="Maven: com.google.guava:guava:11.0.2" level="project" />

    <orderEntry type="library" name="Maven: org.apache.commons:commons-math:2.1" level="project" />

    <orderEntry type="library" name="Maven: xmlenc:xmlenc:0.52" level="project" />

    <orderEntry type="library" name="Maven: commons-httpclient:commons-httpclient:3.1" level="project" />

    <orderEntry type="library" name="Maven: commons-codec:commons-codec:1.4" level="project" />

    <orderEntry type="library" name="Maven: commons-io:commons-io:2.1" level="project" />

    <orderEntry type="library" name="Maven: commons-net:commons-net:3.1" level="project" />

    <orderEntry type="library" name="Maven: javax.servlet:servlet-api:2.5" level="project" />

    <orderEntry type="library" name="Maven: org.mortbay.jetty:jetty:6.1.26.cloudera.2" level="project" />

    <orderEntry type="library" name="Maven: org.mortbay.jetty:jetty-util:6.1.26.cloudera.2" level="project" />

    <orderEntry type="library" name="Maven: com.sun.jersey:jersey-core:1.8" level="project" />

    <orderEntry type="library" name="Maven: com.sun.jersey:jersey-json:1.8" level="project" />

    <orderEntry type="library" name="Maven: org.codehaus.jettison:jettison:1.1" level="project" />

    <orderEntry type="library" name="Maven: stax:stax-api:1.0.1" level="project" />

    <orderEntry type="library" name="Maven: com.sun.xml.bind:jaxb-impl:2.2.3-1" level="project" />

    <orderEntry type="library" name="Maven: javax.xml.bind:jaxb-api:2.2.2" level="project" />

    <orderEntry type="library" name="Maven: javax.activation:activation:1.1" level="project" />

    <orderEntry type="library" name="Maven: org.codehaus.jackson:jackson-jaxrs:1.7.1" level="project" />

    <orderEntry type="library" name="Maven: org.codehaus.jackson:jackson-xc:1.7.1" level="project" />

    <orderEntry type="library" name="Maven: com.sun.jersey:jersey-server:1.8" level="project" />

    <orderEntry type="library" name="Maven: asm:asm:3.1" level="project" />

    <orderEntry type="library" scope="RUNTIME" name="Maven: tomcat:jasper-compiler:5.5.23" level="project" />

    <orderEntry type="library" scope="RUNTIME" name="Maven: tomcat:jasper-runtime:5.5.23" level="project" />

    <orderEntry type="library" scope="RUNTIME" name="Maven: javax.servlet.jsp:jsp-api:2.1" level="project" />

    <orderEntry type="library" scope="RUNTIME" name="Maven: commons-el:commons-el:1.0" level="project" />

    <orderEntry type="library" name="Maven: commons-logging:commons-logging:1.1.1" level="project" />

    <orderEntry type="library" name="Maven: org.apache.hadoop:cloudera-jets3t:2.0.0-cdh4.7.0" level="project" />

    <orderEntry type="library" name="Maven: net.java.dev.jets3t:jets3t:0.6.1" level="project" />

    <orderEntry type="library" name="Maven: commons-lang:commons-lang:2.5" level="project" />

    <orderEntry type="library" name="Maven: commons-configuration:commons-configuration:1.6" level="project" />

    <orderEntry type="library" name="Maven: commons-collections:commons-collections:3.2.1" level="project" />

    <orderEntry type="library" name="Maven: commons-digester:commons-digester:1.8" level="project" />

    <orderEntry type="library" name="Maven: commons-beanutils:commons-beanutils:1.7.0" level="project" />

    <orderEntry type="library" name="Maven: commons-beanutils:commons-beanutils-core:1.8.0" level="project" />

    <orderEntry type="library" scope="RUNTIME" name="Maven: org.slf4j:slf4j-log4j12:1.6.1" level="project" />

    <orderEntry type="library" name="Maven: org.mockito:mockito-all:1.8.5" level="project" />

    <orderEntry type="library" name="Maven: net.sf.kosmosfs:kfs:0.3" level="project" />

    <orderEntry type="library" name="Maven: com.google.protobuf:protobuf-java:2.4.0a" level="project" />

    <orderEntry type="library" name="Maven: org.apache.hadoop:hadoop-auth:2.0.0-cdh4.7.0" level="project" />

    <orderEntry type="library" name="Maven: com.jcraft:jsch:0.1.42" level="project" />

    <orderEntry type="library" name="Maven: org.apache.zookeeper:zookeeper:3.4.5-cdh4.7.0" level="project" />

    <orderEntry type="library" name="Maven: jline:jline:0.9.94" level="project" />

    <orderEntry type="library" name="Maven: eu.dnetlib:icm-iis-assembly-resources:1.0.1" level="project" />

    <orderEntry type="library" name="Maven: log4j:log4j:1.2.17" level="project" />

  </component>

</module>

package eu.dnetlib.usagestats.export;

import org.apache.log4j.Logger;

public class UsageStatsExporter {

    private String username;

    private String password;

    private String httpProtocol;

    private String piwikUrl;

    private String siteId;

    private String startDate;

    private String finalDate;

    private String logsPath;

    private String filterOffset;

    private String tokenAuth;

    private String irusBaseURL;

    private String irusReportType;

    private String irusRelease;

    private String irusRequestorID;

    private String irusRepositoryIdentifier;

    private String irusPeriodGranularity;

    private String irusPeriod;

    private String dbUrl;

    private String dbSchema;

    private String dbUsername;

    private String dbPassword;

    private Logger log = Logger.getLogger(this.getClass());

    public UsageStatsExporter() {}

    public void export() throws Exception {

        PiwikDownloadLogs piwd = new PiwikDownloadLogs(username, password, tokenAuth, httpProtocol, piwikUrl, startDate, logsPath, dbUrl, dbUsername, dbPassword);

        piwd.getPiwikLogs();

        /*

         *  Create DB tables, insert/update statistics

         */

        PiwikStatsDB piwikstats = new PiwikStatsDB(dbUrl, dbUsername, dbPassword, logsPath);

        piwikstats.processLogs();

        //Create Usage Stats tables from Downloaded Logs

        piwikstats.usageStats();

        IrusStats irusStats = new IrusStats(dbUrl, dbUsername, dbPassword);

        irusStats.processIrusRRReport();

        irusStats.irusStats();

        SarcStats sarcStats = new SarcStats(dbUrl, dbUsername, dbPassword);

        sarcStats.processSarc();

        sarcStats.sarcStats();

        //finalize stats

        piwikstats.finalizeStats();

    }

    public String getUsername() {

        return username;

    }

    public void setUsername(String username) {

        this.username = username;

    }

    public String getPassword() {

        return password;

    }

    public void setPassword(String password) {

        this.password = password;

    }

    public String getHttpProtocol() {

        return httpProtocol;

    }

    public void setHttpProtocol(String httpProtocol) {

        this.httpProtocol = httpProtocol;

    }

    public String getPiwikUrl() {

        return piwikUrl;

    }

    public void setPiwikUrl(String piwikUrl) {

        this.piwikUrl = piwikUrl;

    }

    public String getSiteId() {

        return siteId;

    }

    public void setSiteId(String siteId) {

        this.siteId = siteId;

    }

    public String getStartDate() {

        return startDate;

    }

    public void setStartDate(String startDate) {

        this.startDate = startDate;

    }

    public String getFinalDate() {

        return finalDate;

    }

    public void setFinalDate(String finalDate) {

        this.finalDate = finalDate;

    }

    public String getLogsPath() {

        return logsPath;

    }

    public void setLogsPath(String logsPath) {

        this.logsPath = logsPath;

    }

    public String getFilterOffset() {

        return filterOffset;

    }

    public void setFilterOffset(String filterOffset) {

        this.filterOffset = filterOffset;

    }

    public String getDbUrl() {

        return dbUrl;

    }

    public void setDbUrl(String dbUrl) {

        this.dbUrl = dbUrl;

    }

    public String getDbSchema() {

        return dbSchema;

    }

    public void setDbSchema(String dbSchema) {

        this.dbSchema = dbSchema;

    }

    public String getDbUsername() {

        return dbUsername;

    }

    public void setDbUsername(String dbUsername) {

        this.dbUsername = dbUsername;

    }

    public String getDbPassword() {

        return dbPassword;

    }

    public void setDbPassword(String dbPassword) {

        this.dbPassword = dbPassword;

    }

    public String getTokenAuth() {

        return tokenAuth;

    }

    public void setTokenAuth(String tokenAuth) {

        this.tokenAuth = tokenAuth;

    }

    public String getIrusBaseURL() {

        return irusBaseURL;

    }

    public void setIrusBaseURL(String irusBaseURL) {

        this.irusBaseURL = irusBaseURL;

    }

    public String getIrusReportType() {

        return irusReportType;

    }

    public void setIrusReportType(String irusReportType) {

        this.irusReportType = irusReportType;

    }

    public String getIrusRelease() {

        return irusRelease;

    }

    public void setIrusRelease(String irusRelease) {

        this.irusRelease = irusRelease;

    }

    public String getIrusRequestorID() {

        return irusRequestorID;

    }

    public void setIrusRequestorID(String irusRequestorID) {

        this.irusRequestorID = irusRequestorID;

    }

    public String getIrusRepositoryIdentifier() {

        return irusRepositoryIdentifier;

    }

    public void setIrusRepositoryIdentifier(String irusRepositoryIdentifier) {

        this.irusRepositoryIdentifier = irusRepositoryIdentifier;

    }

    public String getIrusPeriodGranularity() {

        return irusPeriodGranularity;

    }

    public void setIrusPeriodGranularity(String irusPeriodGranularity) {

        this.irusPeriodGranularity = irusPeriodGranularity;

    }

    public String getIrusPeriod() {

        return irusPeriod;

    }

    public void setIrusPeriod(String irusPeriod) {

        this.irusPeriod = irusPeriod;

    }

}

package eu.dnetlib.usagestats.export;

import org.apache.hadoop.conf.Configuration;

import org.apache.hadoop.fs.FSDataOutputStream;

import org.apache.hadoop.fs.Path;

import org.apache.hadoop.fs.FileSystem;

import org.apache.log4j.Logger;

import java.io.*;

import java.net.URL;

import java.net.URLConnection;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.Calendar;

public class PiwikDownloadLogs {

    private final String piwikUsername;

    private final String piwikPassword;

    private final String httpProtocol;

    private final String piwikUrl;

    private final Date startDate;

    private final String tokenAuth;

    private final String logsPath;

    private final String dbUrl;

    private final String dbUserName;

    private final String dbPassword;

    /*

       The Piwik's API method 

    */

    private final String APImethod = "?module=API&method=Live.getLastVisitsDetails";

    private final String format = "&format=json";

    private final Logger log = Logger.getLogger(this.getClass());

    public PiwikDownloadLogs(String username, String password, String tokenAuth, String httpProtocol, String piwikURl, String sDate, String logsPath, String dbUrl, String dbUsername, String dbPassword) throws Exception{

        this.piwikUsername = username;

        this.piwikPassword = password;

        this.httpProtocol = httpProtocol;

        this.piwikUrl = piwikURl;

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        this.startDate = sdf.parse(sDate);

        this.tokenAuth = tokenAuth;

        this.logsPath = logsPath;

        this.dbUrl = dbUrl;

        this.dbUserName = dbUsername;

        this.dbPassword = dbPassword;

    }

    private String getPiwikLogUrl(){

        return httpProtocol + "://" + piwikUrl + "/";

    }

    private String getJson(String url,String username, String password) throws Exception {

        //String cred=username+":"+password;

        //String encoded = new sun.misc.BASE64Encoder().encode (cred.getBytes());

        try {

            URL website = new URL(url);

            URLConnection connection = website.openConnection();

            //connection.setRequestProperty ("Authorization", "Basic "+encoded);

            StringBuilder response;

            try (BufferedReader in = new BufferedReader(new InputStreamReader(connection.getInputStream()))) {

                response = new StringBuilder();

                String inputLine;

                while ((inputLine = in.readLine()) != null) {

                    response.append(inputLine);

                    response.append("\n");

                }

            }

            return response.toString();

        }catch (Exception e){

            log.error("Failed to get URL: " + e);

            throw new Exception("Failed to get URL: " + e.toString(), e);

        }

    }

    public void getPiwikLogs() throws Exception{

        GetPortalLogs();

        GetRepositoriesLogs();

    }

    private void GetPortalLogs() throws Exception{

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        Calendar start = Calendar.getInstance();

        start.setTime(startDate);

        Calendar end = Calendar.getInstance();

        end.add(Calendar.DAY_OF_MONTH, -1);

        //end.setTime(getFinalDate());

        try{

            log.info("downloading logs for site with piwik_id: 5");

            Class.forName("org.postgresql.Driver");

            Connection conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);

            PreparedStatement st = conn.prepareStatement("SELECT max(timestamp) FROM public.piwiklog WHERE source='5' HAVING max(timestamp) is not null;");

            ResultSet rs_date = st.executeQuery();

            while(rs_date.next()){

                start.setTime(sdf.parse(rs_date.getString(1)));

            }

            rs_date.close();

            conn.close();

            for (Date date = start.getTime(); start.before(end); start.add(Calendar.DATE, 1), date = start.getTime()) {

                String period="&period=day&date="+sdf.format(date);

                log.info("Downloading logs for " + sdf.format(date));

                FileSystem fs = FileSystem.get(new Configuration());

                FSDataOutputStream fin = fs.create(new Path(logsPath + "portallog/" + "5_Piwiklog"+sdf.format((date))+".json"), true);

                String baseApiUrl = getPiwikLogUrl() + APImethod + "&idSite=5" + period + format + "&expanded=5&filter_limit=1000&token_auth=" + tokenAuth;

                String content = "";

                int i=0;

                while(!content.equals("[]\n")) {

                    String apiUrl = baseApiUrl;

                    if (i > 0)

                        apiUrl += "&filter_offset=" + (i*1000);

                    content = getJson(apiUrl, piwikUsername, piwikPassword);

                    fin.write(content.getBytes());

                    i++;

                }

                fin.close();

//

//

//

//

//

//

//                String apiUrl=getPiwikLogUrl()+APImethod+"&idSite=5"+period+format+"&expanded=5&filter_limit=1000&token_auth="+tokenAuth;

//                String content = getJson(apiUrl,piwikUsername,piwikPassword);

//

//                //for (int i=1;i<10;i++){

//                int i = 1;

//                while(true) {

//                    String apiUrlnew=apiUrl+"&filter_offset="+i*1000;

//                    String contentNew = getJson(apiUrlnew,piwikUsername,piwikUsername);

//                    content += contentNew;

//                    i++;

//                    if(contentNew.equals("[]\n")){

//                        break;

//                    }

//                }

//                flushString(content, logsPath + "portallog/" + "5_Piwiklog"+sdf.format((date))+".json");

            }

        } catch (Exception e) {

            log.error("Failed to get portal logs", e);

            throw new Exception("Failed to get portal logs: " + e.toString(), e);

        }

    }

    private void GetRepositoriesLogs() throws Exception{

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        Calendar start = Calendar.getInstance();

        start.setTime(startDate);

        Calendar end = Calendar.getInstance();

        end.add(Calendar.DAY_OF_MONTH, -1);

        //end.setTime(getFinalDate());

        Class.forName("org.postgresql.Driver");

        Connection conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);

        Statement statement = conn.createStatement();

        ResultSet rs = statement.executeQuery("SELECT distinct piwik_id from shadow.datasource where piwik_id is not null and piwik_id!='5' order by piwik_id;");

        while(rs.next()){

            int siteId = rs.getInt(1);

            PreparedStatement st = conn.prepareStatement("SELECT max(timestamp) FROM public.piwiklog WHERE source=?;");

            start.setTime(startDate);

            log.info("downloading logs for site with piwik_id: " + siteId);

            st.setInt(1, siteId);

            ResultSet rs_date = st.executeQuery();

            while(rs_date.next()){

                //log.info("source: " + siteId + " - date: " + rs_date.getString(1));

                if(rs_date.getString(1) == null || rs_date.getString(1).equals("null") || rs_date.getString(1).equals("")) {

//                        start = Calendar.getInstance();

//                        start.add(Calendar.MONTH, -1);

                    // DO NOTHING USE this.startDate!!!

                }

                else {

                    start.setTime(sdf.parse(rs_date.getString(1)));

                }

            }

            rs_date.close();

            for (Date date = start.getTime(); start.before(end); start.add(Calendar.DATE, 1), date = start.getTime()) {

                log.info("Downloading logs for " + sdf.format(date));

                String period="&period=day&date="+sdf.format(date);

                FileSystem fs = FileSystem.get(new Configuration());

                String baseApiUrl = getPiwikLogUrl() + APImethod + "&idSite=" + siteId + period + format + "&expanded=5&filter_limit=1000&token_auth=" + tokenAuth;

                String content = "";

                int i=0;

                while(!content.equals("[]\n")) {

                    FSDataOutputStream fin = fs.create(new Path(logsPath + "repolog/" + siteId + "_Piwiklog"+sdf.format((date)) + "_" + i + ".json"), true);

                    String apiUrl = baseApiUrl;

                    if (i > 0)

                        apiUrl += "&filter_offset=" + (i*1000);

                    content = getJson(apiUrl, piwikUsername, piwikPassword);

                    fin.write(content.getBytes());

                    fin.close();

                    i++;

                }

            }

        }

        rs.close();

        conn.close();

    }

//    private void flushString(String data, String destination) throws Exception {

//        FSDataOutputStream fin;

//        try {

//            FileSystem fs = FileSystem.get(new Configuration());

//            fin = fs.create(new Path(destination), true);

//            fin.write(data.getBytes());

//            fin.close();

//        } catch (Exception e) {

//            log.error("Failed  to write exported data to a file : ", e);

//            throw new Exception("Failed  to write exported data to a file : " + e.toString(), e);

//        }

//    }

}

package eu.dnetlib.usagestats.export;

import java.io.*;

//import java.io.BufferedReader;

//import java.io.InputStreamReader;

import java.net.URL;

import java.net.URLConnection;

import java.sql.ResultSet;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.Calendar;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.Statement;

import org.json.simple.JSONArray;

import org.json.simple.JSONObject;

import org.json.simple.parser.JSONParser;

import org.apache.log4j.Logger;

/**

 * Created by tsampikos on 28/3/2017.

 */

public class IrusStats {

     private final String dbUrl;

    private final String dbSchema;

    private final String dbUserName;

    private final String dbPassword;

    private Connection conn = null;

    private Statement stmt = null;

    private final Logger log = Logger.getLogger(this.getClass());

    public IrusStats(String dbUrl, String dbUsername, String dbPassword) throws Exception {

        this.dbUrl = dbUrl;

        this.dbSchema = "shadow";

        this.dbUserName = dbUsername;

        this.dbPassword = dbPassword;

        connectDB();

        createTables();

    }

    private void connectDB() throws Exception {

        try {

            Class.forName("org.postgresql.Driver");

            conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);

            stmt = conn.createStatement();

            String sqlSetSearchPath = "SET search_path TO " + dbSchema + ";";

            stmt.executeUpdate(sqlSetSearchPath);

            log.info("Opened database successfully");

        } catch (Exception e) {

            log.error("Connect to db failed: " + e);

            throw new Exception("Failed to connect to db: " + e.toString(), e);

        }

    }

    private void createTables() throws Exception {

        try {

            stmt = conn.createStatement();

            String sqlCreateTableSushiLog = "CREATE TABLE IF NOT EXISTS sushilog(source TEXT, repository TEXT, rid TEXT, date TEXT, metric_type TEXT, count INT, PRIMARY KEY(source, repository, rid, date, metric_type));";

            stmt.executeUpdate(sqlCreateTableSushiLog);

            stmt.executeUpdate("CREATE TABLE IF NOT EXISTS public.sushilog(source TEXT, repository TEXT, rid TEXT, date TEXT, metric_type TEXT, count INT, PRIMARY KEY(source, repository, rid, date, metric_type));");

            String sqlCopyPublicSushiLog="INSERT INTO sushilog SELECT * FROM public.sushilog;";

            stmt.executeUpdate(sqlCopyPublicSushiLog);

            String sqlcreateRuleSushiLog = "CREATE OR REPLACE RULE ignore_duplicate_inserts AS " +

                " ON INSERT TO sushilog " +

                " WHERE (EXISTS ( SELECT sushilog.source, sushilog.repository," +

                "sushilog.rid, sushilog.date " +

                "FROM sushilog " +

                "WHERE sushilog.source = new.source AND sushilog.repository = new.repository AND sushilog.rid = new.rid AND sushilog.date = new.date AND sushilog.metric_type = new.metric_type)) DO INSTEAD NOTHING;";

            stmt.executeUpdate(sqlcreateRuleSushiLog);

            stmt.close();

            conn.close();

            log.info("Sushi Tables Created");

        } catch (Exception e) {

            log.error("Failed to create tables: " + e);

            throw new Exception("Failed to create tables: " + e.toString(), e);

        }

    }

    public void irusStats() throws Exception {

        if (conn.isClosed())

            connectDB();

        stmt = conn.createStatement();

        conn.setAutoCommit(false);

        //String sql = "INSERT INTO sushi_result_downloads SELECT s.source, d.id AS repository, ro.id, s.date, s.count FROM sushilog s, datasource_oids d, result_oids ro WHERE s.repository=d.orid AND s.oai=ro.orid AND metric_type='ft_total'";

        //String sql = "SELECT s.source, d.id AS repository_id, ro.id as result_id, extract('year' from s.date::date) ||'/'|| LPAD(CAST(extract('month' from s.date::date) AS VARCHAR), 2, '0') as date, s.count INTO downloads_stats FROM sushilog s, datasource_oids d, result_oids ro WHERE s.repository=d.orid AND s.oai=ro.orid AND metric_type='ft_total'";

        //String sql = "INSERT INTO downloads_stats SELECT s.source, d.id AS repository_id, ro.id as result_id, extract('year' from s.date::date) ||'/'|| LPAD(CAST(extract('month' from s.date::date) AS VARCHAR), 2, '0') as date, s.count FROM sushilog s, datasource_oids d, result_oids ro WHERE s.repository=d.orid AND s.oai=ro.orid AND metric_type='ft_total';";

        String sql = "INSERT INTO downloads_stats SELECT s.source, d.id AS repository_id, ro.id as result_id, extract('year' from s.date::date) ||'/'|| LPAD(CAST(extract('month' from s.date::date) AS VARCHAR), 2, '0') as date, s.count, '0' FROM sushilog s, datasource_oids d, result_oids ro WHERE s.repository=d.orid AND s.rid=ro.orid AND metric_type='ft_total' AND s.source='IRUS-UK';";

        stmt.executeUpdate(sql);

        stmt.close();

        conn.commit();