//Oracle_time.java
 import java.sql.*;
 import java.time.LocalDateTime;

    public class Oracle_time {
    private static final String url = "jdbc:oracle:thin:elvira/elvira@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = viumdbtest)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME = iumdb)))";
    public static void main(String args[]) throws SQLException {
    String query1 = "UPDATE elvira.pgw_lastfiletimedownload set LastTime='"+ LocalDateTime.now()+"'";

    try(Connection con = DriverManager.getConnection(url);PreparedStatement stmt = con.prepareStatement(query1))
    {
        stmt.executeUpdate(query1);
    }
    catch (SQLException sqlEx) {
        sqlEx.printStackTrace();
    }
}

//Oracle_export.java
 import com.cloudera.sqoop.SqoopOptions;
 import org.apache.sqoop.tool.ExportTool;

 import java.sql.*;
 import org.apache.hadoop.conf.Configuration;
 import org.apache.hadoop.fs.Path;

 public class Oracle_export {

   private static final String url = "jdbc:oracle:thin:elvira/elvira@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = viumdbtest)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME = iumdb)))";


  static boolean flag = false;

  public static void main(String[] args) throws SQLException {

  String query1 = "UPDATE elvira.PGW_INPUTDATA set STATE='2' where STATE='1'";
    try(Connection con = DriverManager.getConnection(url);PreparedStatement stmt = con.prepareStatement(query1))
    {
        System.out.println(export());
        if(flag) {
            stmt.executeUpdate(query1);
        }

    }
    catch (SQLException sqlEx)
    {
        sqlEx.printStackTrace();
    }
}

public static int export() throws SQLException
{
    SqoopOptions options = new SqoopOptions();
    try(Connection con = DriverManager.getConnection(url))
    {
        //con = DriverManager.getConnection(url);
        //options.setDriverClassName("oracle.jdbc.driver.OracleDriver");
        options.setConnectString("jdbc:oracle:thin:elvira/elvira@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = viumdbtest)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME = iumdb)))");

        options.setTableName("elvira.PGW_OUT");

        options.setColumns(new String[]
                {
                        "numrec",
                        "TypeOfRec",
                        "accesspointnameni",
                        "apnselectionmode",
                        "causeforrecordclosing",
                        "chargingcharact",
                        "chargingchmode",
                        "chargingid",
                        "chargingrulebasename",
                        "datavolumefbcdownlink",
                        "datavolumefbcuplink",
                        "duration",
                        "dynamicaddressflag",
                        "dynamicaddressflag2",
                        "localsequencenumber",
                        "localsequencenumber2",
                        "mstimezone",
                        "networkinit1",
                        "nodeid",
                        "pgwaddress",
                        "pgwplmnidentifier",
                        "pdnconnectionchargingid",
                        "pdptypeorganization",
                        "pdptypenumber",
                        "qosinformationneg",
                        "ratinggroup",
                        "rattype",
                        "rattype2",
                        "recordopeningtime",
                        "recordsequencenumber",
                        "recordtype",
                        "served3gpp2meid",
                        "servedimeisv",
                        "servedimsi",
                        "servedmsisdn",
                        "servedpdp_pdnaddress",
                        "serviceconditionchange",
                        "servingnodeaddress",
                        "servingnode2",
                        "servingnodetype",
                        "sgsnaddress",
                        "sgsnplmnidentifier",
                        "starttime",
                        "stoptime",
                        "timeoffirstusage",
                        "timeoflastusage",
                        "timeofreport",
                        "timeusage",
                        "userlocationinformation",
                        "userlocationinformation2",
                        "id"
                }
        );
        Configuration config = new Configuration();
        config.addResource(new Path("/Users/tat100alar/hadoop_files/core-site.xml"));
        config.addResource(new Path("/Users/tat100alar/hadoop_files/hdfs-site.xml"));

        options.setConf(config);

        options.setExportDir("/user/hive/warehouse/pgw_impala.db/pgw_oracle/*.*");

        options.setNumMappers(1);

        options.setNullStringValue("null");
        options.setNullNonStringValue("null");
        options.setInputLinesTerminatedBy('n');
        options.setInputFieldsTerminatedBy('01');
    }
    catch(SQLException ee)
    {
        //con = DriverManager.getConnection(url);
        ee.printStackTrace();
    }
    int ret = new ExportTool().run(options);
    if (0 != ret) {

        flag=false;

    }
    else
    {
        flag=true;
    }
    return ret;
}

//Oracle_import.java
  import org.apache.hadoop.conf.Configuration;
  import org.apache.hadoop.fs.Path;
  import org.apache.sqoop.tool.ImportTool;
  import com.cloudera.sqoop.*;

  import java.io.BufferedReader;
  import java.io.IOException;
  import java.io.InputStreamReader;
  import java.sql.*;

  public class Oracle_import {

  private static final String url = "jdbc:oracle:thin:elvira/elvira@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = viumdbtest)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME = iumdb)))";
  static boolean flag = false;

  public static void main(String[] args) throws SQLException, InterruptedException, IOException {

    String query1 = "UPDATE elvira.PGW_INPUTDATA set STATE='1' where STATE='0'";
    Process proc = Runtime.getRuntime().exec("sh/home/progr/work/test1.sh");
    try(BufferedReader read = new BufferedReader(new InputStreamReader(
            proc.getInputStream()));)
    {
        try {
            proc.waitFor();
        } catch (InterruptedException e) {
            System.out.println(e.getMessage());
        }
        while (read.ready()) {
            System.out.println(read.readLine());
        }
    } catch (IOException e) {
        System.out.println(e.getMessage());
    }

    try(Connection con = DriverManager.getConnection(url);PreparedStatement stmt = con.prepareStatement(query1))
    {
        System.out.println(export());
        if(flag) {
            stmt.executeUpdate(query1);
        }
    }
    catch (SQLException sqlEx)
    {
        sqlEx.printStackTrace();
    }
}

public static int export() throws SQLException
{
    SqoopOptions options = new SqoopOptions();
    try(Connection con = DriverManager.getConnection(url);)
    {   options.setConnectString("jdbc:oracle:thin:elvira/elvira@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = viumdbtest)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME = iumdb)))");
        String query = "select ID,SERVEDIMEISV,SERVEDIMSI,FIRSTDATETIME,SECONDDATETIME " +
                "from elvira.PGW_INPUTDATA where $CONDITIONS and STATE='0' order by DATA_ID";

        options.setSqlQuery(query);

        Configuration config = new Configuration();
        config.addResource(new Path("/Users/tat100alar/hadoop_files/core-site.xml"));
        config.addResource(new Path("/Users/tat100alar/hadoop_files/hdfs-site.xml"));

        options.setConf(config);
        options.setNumMappers(1);

        options.setTargetDir("/user/abulal/test");

        options.setHiveDatabaseName("pgw");
        options.setOverwriteHiveTable(true);
        options.setHiveTableName("pgw_oracle");
        options.setHiveImport(true);

        options.setFieldsTerminatedBy('01');
        options.setLinesTerminatedBy('n');
        options.setDeleteMode(true);
    }
    catch(SQLException ee)
    {
        ee.printStackTrace();
    }
    int ret = new ImportTool().run(options);
    if (0 != ret) {

        flag=false;

    }
    else
    {
        flag=true;
    }
    return ret;
}