Until INSERT S3 object into EC2 DB with Lambda @ java: Java [Continued]
Introduction
This article is a reminder of what AWS, java (almost) beginners did before inserting S3 objects into EC2 DB (SQL Server) with Lambda @ java. This is the final edition of the first post trilogy (+ α). If you point out that this is better here, please! !!
1: AWS Edition 2: Java part [Part 1] 3: Java [Part 2] 3.5: Java [Continued] <-Main
Lambda role and network settings
IAM role settings
Before you edit the source to the final stage, let's set up Lambda. If Lambda wants to access other services in your VPC, such as EC2 or RDS, the functions you run in Lambda must also belong to your VPC. For that purpose, it is necessary to set the IAM role, etc., but when completed, the role that enables access to the VPC when Lambda detects a trigger event, ENI (Elastic Network Interface) Since //docs.aws.amazon.com/ja_jp/AWSEC2/latest/UserGuide/using-eni.html) is automatically allocated, Lambda functions can access resources in the VPC. I will. (The more you know, the better AWS is ...)
Note) The source version should be mssql-jdbc-7.0.0.jre8.jar.
From the Lambda management console, select the created lambda_s3_exec_role and add a policy. Let's add a policy, "AWSLambdaVPCAccessExecutionRole", to access resources in your VPC. If the access authority to CloudWatch Logs is also attached and entered, the preparation is complete.
network settings
Open the Lambda console, set the execution environment to Java8, and create a new function.
Open the function details page and set the Execution Role to lambda_s3_exec_role.
Next, in [Network], select "test_vpc" created in the AWS edition (to be exact, select "VPC to which the resource to be acted belongs" such as EC2 or RDS). * 1
This will cause your Lambda function to belong to your VPC and issue an ENI.
Also, select the appropriate subnet and security group.
After completing the network settings, save the function settings.
Caution
There is one part to be aware of here.
In order to access the resources (EC2, RDS, etc.) in the VPC, it was necessary to register the Lambda function in the VPC as in the previous flow. However, once the Lambda function becomes a resource in the VPC, you will no longer be able to access services outside the VPC (S3, Kinesis, etc.).
Please refer to the following article for details. How to access external services from Lambda in VPC
As a means to avoid this,
--Creating a NAT gateway --Create endpoint
There is, but this time it is easy, so I would like to proceed with the setting with the latter.
Creating an endpoint
Select Endpoints from the navigation bar of the VPC Management Console and click Create Endpoint.
This time, access to other than S3 is unnecessary, so the situation will be as follows.
Next, make your VPC selection.
You can select a route table by selecting the VPC to which your Lambda function or EC2 instance belongs.
If you have multiple route tables, choose the right one.
At this point, the settings are almost complete. The AWS configuration diagram that summarizes the changes made so far is as follows.
1. EC2 belongs to a VPC and is in one subnet. EIP is also assigned.
2. Lambda belongs to the same subnet as EC2.
3. Endpoint is used to establish communication between Lambda inside the VPC and S3 outside the VPC.
4. Endpoint is set in the route table of the subnet to which EC2 and Lambda belong.
Edit source
Now, let's confirm again that "the Lambda function can be executed and the corresponding file can be read by using the upload of the CSV file to S3 as a trigger".
DetectS3Event.java
package lambdaTest.S3toLambda;
/*import omitted*/
public class DetectS3Event implements RequestHandler<S3Event, Object>
{
LambdaLogger lambdaLogger = null;
public AmazonS3 createS3Client() throws Exception
{
AWSCredentials credentials = new BasicAWSCredentials("<accessKey>","<secretKey>");
AmazonS3 client = AmazonS3ClientBuilder.standard()
.withCredentials(new AWSStaticCredentialsProvider(credentials))
.build();
return client;
}
@Override
public Context handleRequest(S3Event event, Context context) {
context.getLogger().log("Input: " + event);
lambdaLogger = context.getLogger();
// ===== get event information (S3 Put) =====
S3EventNotificationRecord record = event.getRecords().get(0);
String bucketName = record.getS3().getBucket().getName(); // s3 bucket name
String key = record.getS3().getObject().getKey(); // s3 object key
lambdaLogger.log("bucketName = " + bucketName);
lambdaLogger.log("object key = " + key);
try {
AmazonS3 client = createS3Client();
// Get target object of event
S3Object object = client.getObject(new GetObjectRequest(bucketName, key));
lambdaLogger.log("S3 client = " + client.toString());
BufferedInputStream bis = new BufferedInputStream(object.getObjectContent());
BufferedReader br = new BufferedReader(new InputStreamReader(bis));
String line = "";
// Output contents of object line by line
while((line = br.readLine()) != null) {
lambdaLogger.log(line);
}
} catch (IOException e) {
lambdaLogger.log("IOException error message : " + e.getErrorMessage());
} catch (AmazonServiceException e) {
lambdaLogger.log("AWSException error message : " + e.getErrorMessage());
} catch (Exception e) {
lambdaLogger.log("Exception error message : " + e.getMessage());
}
return null;
}
}
After creating the above file, upload the jar packaged with Maven etc. to Lambda.
Upload the following CSV to S3.
emp_values.csv
1001,Dean,28
1002,Sam,25
1003,John,51
1004,Bobby,54
1005,Meg,26
Let's connect to CloudWatch and check the log.
You can see that the above CSV is output to the log line by line.
Now, let's insert this CSV into the DB on EC2.
INSERT into DB
Databases and tables are the previous [Java [Part 2]](https://qiita.com/yut0201/items/0d955b37f1f461cce815#%E3%83%87%E3%83%BC%E3%82%BF%E3% 83% 99% E3% 83% BC% E3% 82% B9% E3% 81% 8A% E3% 82% 88% E3% 81% B3% E3% 83% A6% E3% 83% BC% E3% 82% Use the LambdaTestDB employee table created in B6% E3% 81% AE% E4% BD% 9C% E6% 88% 90).
Make sure there are no records in the table.
1> SELECT * FROM employee;
2> go
emp_id emp_name age
----------- -------------------- -----------
(0 rows affected)
Now let's edit the source.
DetectS3Event.java
package lambdaTest.S3toLambda;
/*import omitted*/
public class DetectS3Event implements RequestHandler<S3Event, Object>
{
LambdaLogger lambdaLogger = null;
Connection mssqlCon = null;
public AmazonS3 createS3Client() throws Exception
{
AWSCredentials credentials = new BasicAWSCredentials(
"<accessKey>",
"<secretKey>");
AmazonS3 client = AmazonS3ClientBuilder.standard()
.withCredentials(new AWSStaticCredentialsProvider(credentials))
.build();
return client;
}
@Override
public Context handleRequest(S3Event event, Context context) {
context.getLogger().log("Input: " + event);
lambdaLogger = context.getLogger();
// ===== get event information (S3 Put) =====
S3EventNotificationRecord record = event.getRecords().get(0);
String bucketName = record.getS3().getBucket().getName(); // s3 bucket name
String key = record.getS3().getObject().getKey(); // s3 object key
lambdaLogger.log("bucketName = " + bucketName);
lambdaLogger.log("object key = " + key);
try {
AmazonS3 client = createS3Client();
S3Object object = client.getObject(new GetObjectRequest(bucketName, key));
lambdaLogger.log("S3 client = " + client.toString());
BufferedInputStream bis = new BufferedInputStream(object.getObjectContent());
BufferedReader br = new BufferedReader(new InputStreamReader(bis));
ResourceBundle sqlServerResource = ResourceBundle.getBundle("sqlServer");
DatabaseInfo sqlServerInfo = new DatabaseInfo(sqlServerResource.getString("driver"),
sqlServerResource.getString("url"),
sqlServerResource.getString("user"),
sqlServerResource.getString("pass"));
/* ======== SQL Server connection ======== */
lambdaLogger.log("SQL Server Session creating...");
SqlServer sqlServer = new SqlServer();
mssqlCon = sqlServer.openConnection(sqlServerInfo);
lambdaLogger.log("SUCCESS : SQL Server session created !!");
String line = "";
Statement insertStmt = mssqlCon.createStatement();
while((line = br.readLine()) != null) {
String[] values = line.split(",");
String insertRecord = "INSERT INTO employee VALUES("
+ values[0] + ", '"
+ values[1] + "', "
+ values[2] + ");";
insertStmt.executeUpdate(insertRecord);
}
if (insertStmt != null) insertStmt.close();
if (mssqlCon != null) sqlServer.closeConnection(mssqlCon);
if (br != null) br.close();
} catch (IOException e) {
e.printStackTrace();
} catch (AmazonServiceException e) {
lambdaLogger.log("Error message : " + e.getErrorMessage());
} catch (Exception e) {
lambdaLogger.log("Exception error message : " + e.getMessage());
}
return null;
}
}
In the while statement, CSV read line by line is made into an array separated by commas, and each is inserted as the value of each column of the record. You will need a connection to connect to the DB, so create a new file below.
SqlServer.java
package lambdaTest.db;
/*import omitted*/
public class SqlServer {
public SqlServer() {
super();
}
public Connection openConnection(DatabaseInfo dbInfo)
{
Connection con = null;
try {
Class.forName(dbInfo.getDriver());
con = DriverManager.getConnection(dbInfo.getUrl(), dbInfo.getDbUser(), dbInfo.getDbPass());
} catch (SQLException | ClassNotFoundException e) {
System.out.println("anything Exception : " + e);
}
return con;
}
public void closeConnection(Connection con) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Delegate connection management to the DB to this class. Hard-coding is fine for each parameter required for connection, such as the connection URL, user name, and password. Put it out as a properties file with an emphasis on reusability.
sqlServer.properties
driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
url=jdbc:sqlserver://<EC2-privateIP>:<portNo>;databaseName=<databaseName>
user=<userName>
pass=<password>
Also, since you need a class to link the properties file, let's also create the following class.
DatabaseInfo.java
package lambdaTest.S3toLambda;
final public class DatabaseInfo {
String driver;
String url;
String dbUser;
String dbPass;
public String getDriver() {
return driver;
}
public String getUrl() {
return url;
}
public String getDbUser() {
return dbUser;
}
public String getDbPass() {
return dbPass;
}
public DatabaseInfo(String driver, String url, String dbUser, String dbPass) {
this.driver = driver;
this.url = url;
this.dbUser = dbUser;
this.dbPass = dbPass;
}
}
Finally, add a few lines to pom.xml so that Maven can read the external properties file correctly. Here, only the build section to be added is excerpted, and the added line is marked with a star.
pom.xml
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-shade-plugin</artifactId>
<version>2.3</version>
<configuration>
<encoding>UTF-8</encoding>
<createDependencyReducedPom>false</createDependencyReducedPom>
<filters>
<filter>
<artifact>*:*</artifact>
<excludes>
<exclude>META-INF/*.SF</exclude>
<exclude>META-INF/*.DSA</exclude>
<exclude>META-INF/*.RSA</exclude>
</excludes>
</filter>
</filters>
<archive>
<manifest>
<mainClass>com.gmail.greencoffeemaker.MainApp</mainClass>
<addClasspath>true</addClasspath>
</manifest>
<manifestEntries>
<Class-Path>./src/resources/</Class-Path>
</manifestEntries>
</archive>
</configuration>
<executions>
<execution>
<phase>package</phase>
<!--Postscript part-->
<configuration>
<target>
<!--copy: /src/main/resources => /target/resources -->
<mkdir dir="${project.build.directory}/resources" />
<copy todir="${project.build.directory}/resources">
<fileset dir="${project.basedir}/src/main/resources" />
</copy>
</target>
</configuration>
<!--So far-->
<goals>
<goal>shade</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
Editing is now complete. After building, upload the jar to Lambda and put emp_values.csv in S3 again.
When I connect to SQL Server on EC2 and look at the table. .. ..
1> SELECT * FROM employee;
2> go
emp_id emp_name age
----------- -------------------- -----------
1001 Dean 28
1002 Sam 25
1003 John 51
1004 Bobby 54
1005 Meg 26
(5 rows affected)
** It's done! !! !! ** **
Summary
It's been a few years since I first touched Java in writing this article & AWS is the first I was in a tattered state with almost zero preparation, but I was relieved to be able to realize what I wanted to do. Reading the manual, searching for knowledge, etc., the work time per day was less than 3 hours, and the work feeling was about 2 weeks. I feel that most of the work was to avoid errors that stumbled on the way.
I want to improve work efficiency. Desperate.
Recommended Posts