= Reading Data from Amazon S3 Based Source

This article shows an example to read comma seperated (CSV) file from a Amazon S3 as Entity (Table). In this example  CSV data is loaded into a JPA Entity class that can be used exactly as a RDBMS based entity, i.e. you can issue queries against it. Yes, that is correct!, you can issue JPA queries against a CSV document to filter the data, or join the entity with another entity to integrate data using Teiid. 

You can also read JSON files, or Excel files from Amazon S3 similar way. Otherwise you can get to the raw content of file too, however this example only shows CSV file, for JSON use the @JsonTable as entity annotation similarly @ExcelTable for excel content (See TEIID-5872). for more information see Json and Excel examples.

=== What you’ll need

* About 15 minutes
* A favorite text editor or IDE
* JDK 1.8 or later
* Maven 3.0+

=== Build With Maven
First you set up a basic build script. You can use any build system you like when building apps with Spring, but the code you need to work with Maven is included here. If you’re not familiar with Maven, refer to link:https://spring.io/guides/gs/maven[Building Java Projects with Maven].

Go to link:http://start.spring.io/[Spring Initializer] and type in "JPA" in dependencies and generate a project. Then open the generated code in your favorite IDE, and edit the pom.xml to add the below dependencies.

Otherwise, in a project directory of your choosing, create the following sub-directory structure; for example, with
----
mkdir -p src/main/java/example on *nix systems:
----
and create pom.xml file of your choosing and add following maven dependencies Spring Boot


[source,xml]
.*spring-boot-starter-data-jpa*
----
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
----

The following are the Teiid related dependencies
[source,xml]
.*teiid-spring-boot-starter,data-amazon-s3*
----
<dependency>
   <groupId>org.teiid</groupId>
   <artifactId>teiid-spring-boot-starter</artifactId>
</dependency>
<dependency>
    <groupId>org.teiid.spring</groupId>
    <artifactId>spring-data-amazon-s3</artifactId>
</dependency>
----

in this example, we will create Entity class that can read CSV data from Amazon S3, that has following structure with name "marketdata-price.txt" in bucket called "teiidtest"

----
SYMBOL,PRICE
RHT,30.00
BA,42.75
MON,78.75
PNRA,84.97
SY,24.30
BTU,41.25
IBM,80.89
DELL,10.75
HPQ,31.52
GE,16.45
----


=== Define View/Entity Class
Now it is time to define the main Entity or View class. Since we are reading a `StockPrice` information, let's call it StockPrice class

[source,java]
.src/main/java/com/example/StockPrice.java
----
@Entity
@TextTable(file = "marketdata-price.txt", source="tests3")
public class StockPrice {

    @Id
    String symbol;

    double price;
    
    // rest of the class ignored for brevity. create all the getters and setters. See example code.
----

Here you have a StockPrice class with two attributes, the symbol, price. 
The "StockPrice" class is annotated with `@Entity`, indicating that it is a JPA entity. For `@Table` annotation, is optional, but use it to give a different name. Sometimes @Table also need to be used to avoid the naming conflicts.

*@TextTable* annotation is where most of the magic of Teiid occurring. This defines the document name,  the header and data rows (these are optional, if not defined the document is scanned for these properties) and creates the entity. 

At application boot time, Teiid Spring Boot scans the application's packages for these annotations and builds the respective metadata required to create a virtual database internally and deploys to server. To do this scan, define the application package name in this property to the `application.properties` file.

[source,text]
.*src/main/resources/application.properties*
----
spring.teiid.model.package=org.example
----

In absence of this property entire classpath is scanned, that could take significant time depending upon all the libraries in your application.

Since we are reading the CSV file from a Amazon S3 define following properties in `application.properties` file 

[source,text]
.*src/main/resources/application.properties*
----
spring.teiid.data.amazon-s3.tests3.region=us-east-2
spring.teiid.data.amazon-s3.tests3.bucket=<bucket-name>
spring.teiid.data.amazon-s3.tests3.accesskey=xxxxx
spring.teiid.data.amazon-s3.tests3.secretkey=xxxx
----

NOTE: In aboue properties `tests3` is bean name that is providing the connection to the Amazon S3


=== Create Spring Data Repository class

Spring Data JPA focuses on using JPA to store data in a relational database. Its most compelling feature is the ability to create repository implementations automatically, at runtime, from a repository interface. 

To see how this works, create a repository interface that works with StockPrice entities:

[source,java]
.src/main/java/org/example/StockPriceRepository.java
----
@Repository
public interface StockPriceRepository extends CrudRepository<StockPrice, String>{

}  
----

Spring Data JPA also allows you to define other query methods by simply declaring their method signature. In a typical Java application, you’d expect to write a class that implements StockPriceRepository. But that’s what makes Spring Data JPA so powerful: You don’t have to write an implementation of the repository interface. Spring Data JPA creates an implementation on the fly when you run the application.

Let’s wire this up and see what it looks like!

=== Create an DataSources class
Create Datasource bean that can provide a access to the Amazon S3, Teiid already provides the Connection classes, you just need to provide them as beans with configuration as shown in `application.properties`.

[source,java]
.src/main/java/org/example/DataSources.java
----
package org.example;

@Configuration
public class DataSources {

    @ConfigurationProperties(prefix = "spring.teiid.data.amazon-s3.tests3")
    @Bean(name="tests3")
    public AmazonS3ConnectionFactory tests3() {
        return new AmazonS3ConnectionFactory();
    }
}
----

NOTE: See reference of `tests3` name from above class, and how it is used in the `application.properties` to provide the properties.

=== Create an Application class

Here you create an Application class with all the components.

[source,java]
.src/main/java/org/example/Application.java
----
package org.example;

@SpringBootApplication
public class Application implements CommandLineRunner {

    @Autowired
    private StockPriceRepository repository;
    
	public static void main(String[] args) {
		SpringApplication.run(Application.class, args).close();
	}
	
    @Override
    public void run(String... args) throws Exception {
        repository.findAll().forEach(c -> System.out.println(c));
    }
}
----

Now when you execute this application, you should see results like below.

----
StockPrice [symbol=RHT, price=30.0]
StockPrice [symbol=BA, price=42.75]
StockPrice [symbol=MON, price=78.75]
StockPrice [symbol=PNRA, price=84.97]
StockPrice [symbol=SY, price=24.3]
StockPrice [symbol=BTU, price=41.25]
StockPrice [symbol=IBM, price=80.89]
StockPrice [symbol=DELL, price=10.75]
StockPrice [symbol=HPQ, price=31.52]
StockPrice [symbol=GE, price=16.45]
----

You can instead of calling `repository.findAll()` call any other query or use `JDBCTemplate` class to issue a custom query, and Teiid engine will treat the CSV document data exactly any RDBMS data and apply any filters or aggregations for you and return the data. 

NOTE: You can always integrate data from multiple different types of data sources using Teiid. Even though Repository interface supports inserts, updates and deletes they will not be supported here, only selection. Usage of those related functions will end up in error
