Java tutorial
/* * Copyright 2010-2013 Amazon.com, Inc. or its affiliates. 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. * A copy of the License is located at * * http://aws.amazon.com/apache2.0 * * or in the "license" file accompanying this file. This file 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. */ package jp.sanix; import java.io.BufferedReader; import java.io.BufferedWriter; import java.io.File; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.io.FileOutputStream; import java.io.FileWriter; import java.io.IOException; import java.io.InputStreamReader; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.zip.GZIPInputStream; import java.util.Calendar; import java.util.Date; import java.util.TimeZone; import java.util.regex.Pattern; import org.json.JSONException; import org.postgresql.util.PSQLException; import com.amazonaws.AmazonServiceException; import com.amazonaws.regions.Region; import com.amazonaws.regions.Regions; import com.amazonaws.services.s3.AmazonS3; import com.amazonaws.services.s3.AmazonS3Client; import com.amazonaws.services.s3.model.GetObjectRequest; import com.amazonaws.services.s3.model.ListObjectsRequest; import com.amazonaws.services.s3.model.ObjectListing; import com.amazonaws.services.s3.model.PutObjectRequest; import com.amazonaws.services.s3.model.S3Object; import com.amazonaws.services.s3.model.S3ObjectSummary; public class analysis { // public static final String PG_CON = "jdbc:postgresql://localhost:19000/sanix_eye_rawdata_production"; public static final String PG_CON = "jdbc:postgresql://sanix-eye-rawdata-r01.cpoo4hhfnsex.ap-northeast-1.rds.amazonaws.com:5432/sanix_eye_rawdata_production"; public static final String PG_USER = "sanix_eye_rawdat"; public static final String PG_PASS = "HFKbK_4whYj2"; public static final String BUCKET = "sanix-data-analysis"; public static final String FOLDER = "fhRK0XGVb3cR1r1S3x9j3j3DRFGUyRYC/"; public static void main(String[] args) throws IOException, SQLException, AmazonServiceException, JSONException, NullPointerException, ParseException { if (args.length < 1) { System.out.println("Please specify at least an arguments that is pvs_serial_id."); System.out.println("analysis A9990004 // get today's data"); System.out.println("analysis A9990004 2015/05/30 // get from 2015/05/30 to today's data"); System.out.println("analysis A9990004 2015/05/30 2015/06/30"); System.exit(-1); } String id = args[0]; SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd"); format.setTimeZone(TimeZone.getTimeZone("JST")); SimpleDateFormat normalformat = new SimpleDateFormat("yyyy/MM/dd'T'HH:mm:ss'Z'"); normalformat.setTimeZone(TimeZone.getTimeZone("JST")); SimpleDateFormat pgformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); pgformat.setTimeZone(TimeZone.getTimeZone("UTC")); Calendar cal = Calendar.getInstance(); Calendar end = Calendar.getInstance(); String today = toDate(cal); System.out.println(id); try { cal.setTime(format.parse(args[1])); } catch (ParseException e) { } catch (ArrayIndexOutOfBoundsException e) { } try { end.setTime(format.parse(args[2])); } catch (ParseException e) { } catch (ArrayIndexOutOfBoundsException e) { } end.add(Calendar.DAY_OF_MONTH, 1); AmazonS3 s3 = new AmazonS3Client(); s3.setRegion(Region.getRegion(Regions.AP_NORTHEAST_1)); Connection db = DriverManager.getConnection(PG_CON, PG_USER, PG_PASS); Statement st = db.createStatement(); ResultSet rs = st.executeQuery( "SELECT data, pvs_unique_code FROM data WHERE pvs_serial_id='" + id + "' OFFSET 0 LIMIT 1;"); rs.next(); String json = rs.getString(1); String key = rs.getString(2); rs.close(); db.close(); Date recent = new Date(); while (cal.before(end)) { xlsSheet xls = new xlsSheet(json); System.out.println("Getting data of " + toDate(cal)); /* AWS S3????? */ String bucketName = "pvdata-storage-production"; // String bucketName = "pvdata-storage-staging"; System.out.println("Get s3 data by key='" + bucketName + "/data/" + key + "/" + toDate(cal) + "/'"); ObjectListing objectListing = s3.listObjects(new ListObjectsRequest().withBucketName(bucketName) .withPrefix("data/" + key + "/" + toDate(cal) + "/")); /* get data from s3 */ do { for (S3ObjectSummary objectSummary : objectListing.getObjectSummaries()) { String keyname = objectSummary.getKey(); S3Object object = s3.getObject(new GetObjectRequest(bucketName, keyname)); BufferedReader reader = new BufferedReader( new InputStreamReader(new GZIPInputStream(object.getObjectContent()))); while (reader.ready()) { String line = reader.readLine(); try { json = line.substring(line.indexOf("{")); recent = parseDate(line.substring(0, 25)); xls.putData(json); } catch (NullPointerException e) { } } reader.close(); object.close(); } objectListing = s3.listNextBatchOfObjects(objectListing); } while (objectListing.getMarker() != null); /* if today, read postgres to get recent data */ if (toDate(cal).equals(today)) { System.out.println("Get recent data from postgres"); try { db = DriverManager.getConnection(PG_CON, PG_USER, PG_PASS); st = db.createStatement(); String sql = "SELECT data FROM data WHERE pvs_unique_code='" + key + "' AND created_at > '" + pgformat.format(recent) + "';"; System.out.println(sql); rs = st.executeQuery(sql); while (rs.next()) { json = rs.getString(1); xls.putData(json); } rs.close(); db.close(); } catch (PSQLException e) { } catch (ParseException e) { } } System.out.println("Write Buffer"); xls.writeBuffer(); File file = File.createTempFile("temp", ".xlsx"); file.deleteOnExit(); xls.putFile(new FileOutputStream(file)); System.out.println("Put S3"); s3.putObject(new PutObjectRequest("sanix-data-analysis", FOLDER + id + "-" + toDate(cal).replace("/", "-") + ".xlsx", file)); System.out.println("Finished: " + toDate(cal)); cal.add(Calendar.DAY_OF_MONTH, 1); } File file = File.createTempFile("temp", ".html"); file.deleteOnExit(); BufferedWriter bw = new BufferedWriter(new FileWriter(file)); ObjectListing objectListing = s3 .listObjects(new ListObjectsRequest().withBucketName("sanix-data-analysis").withPrefix(FOLDER)); bw.write("<html><head></head><body><ul>\n"); do { for (S3ObjectSummary objectSummary : objectListing.getObjectSummaries()) { String keyname = objectSummary.getKey(); if (Pattern.compile("\\.xlsx$|\\.html$").matcher(keyname).find()) { bw.write("<li><a href=\"https://s3-ap-northeast-1.amazonaws.com/sanix-data-analysis/" + keyname + "\">" + keyname.replaceAll("^[^\\/]*\\/", "") + "</a></li>\n"); } } objectListing = s3.listNextBatchOfObjects(objectListing); } while (objectListing.getMarker() != null); bw.write("</ul></body></html>\n"); bw.flush(); bw.close(); s3.putObject(new PutObjectRequest("sanix-data-analysis", FOLDER + "index.html", file)); } public static String toDate(Calendar cal) { return (String.format("%04d/%02d/%02d", cal.get(Calendar.YEAR), cal.get(Calendar.MONTH) + 1, cal.get(Calendar.DAY_OF_MONTH))); } public static Date parseDate(String sDate) { Date myDate = new Date(); SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'"); format1.setTimeZone(TimeZone.getTimeZone("UST")); SimpleDateFormat format2 = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'+09:00'"); format2.setTimeZone(TimeZone.getTimeZone("JST")); try { myDate = format1.parse(sDate); } catch (ParseException e) { try { myDate = format2.parse(sDate); } catch (ParseException e1) { } } return myDate; } }