Advance database systems

INF5100-ADVANCED DATABASE SYSTEMS Page 1

Mandatory Exercise – Esper

Introduction

Esper is a component for complex event processing (CEP). We use Esper in this exercise to learn

about stream database systems and complex event processing. The scenario used in the exercise

is building a stock trading robot. The events (ticks) are simulated instead of fetched from a real

stock exchange.

Delivery

Who: Alone

When: 30. September 2015, 12:00

Where: https://devilry.ifi.uio.no/

What: 1 PDF file with all queries, explanations of how you resonate, table with results and

graphs where suited. Your queries should be explained thoroughly, since there are many possible

solutions.

Administrative

The ordering of questions has meaning. Firstly, the first questions are easier than the later

questions. Secondly, the text questions are meant to prepare you for the coming queries. This

means that you should answer the questions in order. All questions must be answered in order to

pass the exercise.

Regarding the text questions:

You are not meant to copy/paste from either the Esper web site or the slides or the links provided

for hint. Rather read, understand and write the things in your own words. Failing to do this

properly will result in a fail on this exercise. Take your time, and follow the suggested page

requirements.

INF5100-ADVANCED DATABASE SYSTEMS Page 2

Getting Started

Download the mandatory exercise bundle from

www.uio.no/studier/emner/matnat/ifi/INF5100/h15/exercise/mandatory_exercise.tar.gz and

unzip it.

The bundle contains two directories:

esper-4.9.0/ contains Esper and its libraries. You do not need to change anything in this

directory, but you should explore it in order to understand better how Esper works.

inf5100/ contains the code and query files we will use in the exercise. The files are:

• Makefile - Regular makefile with three commands:

- make compiles the exercise

- make run runs the queries

- make clean removes the Java class files.

You are free to expand the file if needed.

• BTC.csv - The emulated stock exchange, consisting of events (ticks). You are not allowed to

modify this file, but you may look at it in order to verify the correctness of your queries.

• INF5100.java - The code used to generate events and read your queries. By default the code

will loop over all your queries, this can make debugging your queries difficult. If you want to

only test one query, you will need to modify the for-loop on line 28 inside this file, so it only

reads one query.

• query_[...].epl - These are the files you should change according to the questions.

• Tick.java - The event class. An event consists of the following attributes:

◦ symbol = Stock ticker or stock alias. This is only BTC in our simulated stock exchange.

◦ timestamp = The date of the event/tick. Our stock exchange sends one tick per day.

◦ open = The price of the stock at the beginning of the day.

◦ high = The highest price the stock was traded for this day.

◦ low = The lowest price the stock was traded for this day.

◦ close = The price of the stock at the end of the day.

◦ volume = The number of times the stock was bought/sold this day.

◦ weightedPrice = A weighted average of the stock price this day.

INF5100-ADVANCED DATABASE SYSTEMS Page 3

Questions:

QUESTION 1 – TEXT (~0.5 PAGE)

What are some typical uses for complex event processing applications? Give examples and

elaborate on the possibilities.

HINT: Slides + In your mandatory exercise folder which you have downloaded from

“www.uio.no/studier/emner/matnat/ifi/INF5100/h15/exercise/mandatory_exercise.tar.gz” follow

this path,

/mandatory_exercise/esper-4.9.0/esper/doc/reference/html/technology_overview.html

QUESTION 2 – QUERY_1.EPL (QUERY + RESULTS + GRAPH)

Find the date of the BTC stock when closing price is greater than 0.1 and less than 20.

HINT: Little or no difference from standard SELECT-FROM-WHERE along with relational

operators in SQL.

QUESTION 3 – TEXT (~0.5 PAGE)

What is the difference between doing aggregated queries (AVG/SUM/MAX/ETC) in standard

databases and stream databases?

HINT: In your mandatory exercise folder which you have downloaded from

“www.uio.no/studier/emner/matnat/ifi/INF5100/h15/exercise/mandatory_exercise.tar.gz” follow

this path,

/mandatory_exercise/esper-4.9.0/esper/doc/reference/html/processingmodel.html

INF5100-ADVANCED DATABASE SYSTEMS Page 4

QUESTION 4 – QUERY_2.EPL (THOUGHTS + QUERY + RESULTS)

For each non-overlapping 100 tick interval of the BTC stock, find the minimum and maximum

weighted price in the interval.

HINT: In your mandatory exercise folder which you have downloaded from

“www.uio.no/studier/emner/matnat/ifi/INF5100/h15/exercise/mandatory_exercise.tar.gz” follow

this path,

/mandatory_exercise/esper-4.9.0/esper/doc/reference/html/processingmodel.html

Sub Topic – 3.6 Batch Window

QUESTION 5 – QUERY_3.EPL (THOUGHTS + QUERY + RESULTS)

Extend the previous query so that it now also shows the difference between the minimum and

maximum weighted price for each interval.

QUESTION 6 – QUERY_4.EPL (THOUGHTS + QUERY + RESULTS)

Find the total weighted price of all stock tick events in each non-overlapping 100 tick interval of

the BTC stock.

QUESTION 7 – QUERY_5.EPL (THOUGHTS + QUERY + RESULTS + GRAPH)

Find the dates and minimum weighted price for the BTC stock for each, non-overlapping 30 tick

interval.

Be aware that this is trickier than anticipated. (Double check that prices corresponds to correct

dates!)

INF5100-ADVANCED DATABASE SYSTEMS Page 5

QUESTION 8 – TEXT (~0.5 PAGE)

What are patterns and pattern operators? What does the every and followed-by (->) patterns do?

Explain the difference between the following:

every A -> B

A -> every B

every A -> every B

every (A -> B)

HINT: In your mandatory exercise folder which you have downloaded from,

“www.uio.no/studier/emner/matnat/ifi/INF5100/h15/exercise/mandatory_exercise.tar.gz” follow

this path,

/mandatory_exercise/esper-4.9.0/esper/doc/reference/html/event_patterns.html

QUESTION 9 – QUERY_6.EPL (THOUGHTS + QUERY + RESULTS)

Find all the start and stop dates with volume, when the weighted price of the BTC stock

increases more than 100 times and volume increases by 40%.

HINT: In your mandatory exercise folder, which you have downloaded from,

“www.uio.no/studier/emner/matnat/ifi/INF5100/h15/exercise/mandatory_exercise.tar.gz” follow

this path,

/mandatory_exercise/esper-4.9.0/esper/doc/reference/html/event_patterns.html

Sub Topic: 5.4.2 Pattern-based Event Streams

Use AND to satisfy second condition.

QUESTION 10 – TEXT (0.1 - 1 PAGE; UP TO YOU HOW INTERESTED YOU ARE.)

What is technical analysis? What is algorithmic trading?

INF5100-ADVANCED DATABASE SYSTEMS Page 6

QUESTION 11 – QUERY_7.EPL (THOUGHTS + QUERY + RESULTS)

We claim that a good buying date for the BTC stock is the date after which the stock has a lower

weighted price than a date before it (not necessarily right before), and the volume has increased

by 70% as that same earlier date. Vice versa, a good selling date is when the stock has three

times increased weighted price than a date before it (not necessarily right before) and a 70%

increase in volume. Find the buy dates and closing prices and the sell dates and closing prices to

test this hypothesis. Was the trading algorithm successful? Assume you always buy 1 and sell 1

share, and that you always have enough shares to sell even if you did not buy all of them, how

much did this algorithm earn or loose? HINT: OR, not AND

QUESTION 12 – QUERY_8.EPL (THOUGHTS + QUERY + RESULTS)

This question is an extension to question 11, therefor you should solve question 11 before

you solve this.

We will now try a different algorithm for buying and selling BTC stocks. This algorithm will be

based on trend following, which is an investment strategy based on technical analysis of market

prices, rather than on of the fundamental strengths of the companies. We will try to take

advantage of the market trends by observing the current direction and using this to decide

whether to buy or sell. The strategy is simple: If we see that the weighted price and closing

prices are going up over three days, we buy.

Example: If we have an event of three days (not necessarily adjacent days), Monday, Tuesday

and Wednesday. If we see that the weighted price has increased from Monday to Tuesday and

then again from Tuesday to Wednesday, and in addition the closing price on Wednesday is larger

than Monday, we buy. And vice versa for selling, if we see that the weighted price has gone

down from Monday to Tuesday and then again from Tuesday to Wednesday, and in addition the

closing price on Wednesday is larger than the closing price on Monday, we sell.

INF5100-ADVANCED DATABASE SYSTEMS Page 7

QUESTION 13 – TEXT (~0.5 PAGE)

What are pattern guards? Why cannot the timer:within pattern guard be used in simulated

environments? What alternatives exist to control time and dates?

HINT: In your mandatory exercise folder, which you have downloaded from,

“www.uio.no/studier/emner/matnat/ifi/INF5100/h15/exercise/mandatory_exercise.tar.gz” follow

this path,

/mandatory_exercise/esper-4.9.0/esper/doc/reference/html/event_patterns.html

Sub Topic: 6.5.9 Pattern Guards

QUESTION 14 – QUERY_9.EPL (THOUGHTS + QUERY + RESULTS)

Find the start date, stop date and weighted prices when the BTC stock value has decreased by

three times within a 15 day period,

e.g. suppose from 2014-06-09 to 2014-06-24 the weighted price has gone down from 15 to 5.

HINT: In your mandatory exercise folder, which you have downloaded from,

“www.uio.no/studier/emner/matnat/ifi/INF5100/h15/exercise/mandatory_exercise.tar.gz” follow

this path,

/mandatory_exercise/esper-4.9.0/esper/doc/reference/html/datetimereference.html

QUESTION 15 – TEXT (~0.3 PAGE)

What are variables in Esper and how can they be used to hold and change state? Give examples!

HINT: In your mandatory exercise folder, which you have downloaded from,

“www.uio.no/studier/emner/matnat/ifi/INF5100/h15/exercise/mandatory_exercise.tar.gz” follow

this path,

/mandatory_exercise/esper-4.9.0/esper/doc/reference/html/epl_clauses.html

Sub Topic: 5.18 Variables and Constants

INF5100-ADVANCED DATABASE SYSTEMS Page 8

QUESTION 16 – QUERY_10.EPL (THOUGHTS + QUERY + RESULTS)

Write your own trading algorithm and use it to find buy and sell dates. No hard-coding of dates

or numbers are allowed! Use the knowledge you have on Esper to write your query of choice. If

you want to change the Java files, you are allowed to, but you do not have to (e.g. needed for

multiple queries).

QUESTION 17 – TEXT (~1 PAGES)

What did you struggle with during this exercise? Was it the provided code's fault, Esper's or your

own fault? What was different from working with regular database systems?