# MySQL (AWS RDS) data output

To help you get started with Upsolver, you can try it out for free. You can choose between [**Upsolver Dedicated Compute**](https://app.upsolver.com/signup) and [**Community**](https://app.upsolver.com/signup/free) subscriptions. Both options give you free Upsolver units (UUs), units of processing capability per hour based on VM instance type.

## Create a data source

1\. Click on **DATA SOURCES > NEW** to connect to various data sources. Upsolver works with both streaming and static data sources.

![](https://lh3.googleusercontent.com/VEBtmN-b2sXlGI8KbaKyeZtTRuXAqt4NkBess6US8LAc6NxoQAvGaQLhr_2lTKRH6V3Pe2JglaQvDSlA5hcep_DedbJFNS7ayYi2Cx-uozzUkzfZW79DqsqJutaVp6-f0l799Goz)

2\. Click on **SELECT** next to **Amazon S3** data source. There are many data sources to choose from. The Quickstart uses *pre-populated* mobile data.

![](https://lh6.googleusercontent.com/Q4l7k9KjZetrJL8f1dtauybuIMw329ecce7bib5JDhOrOl4KasXJiSBxGAbTPujlaxGUHb92okdSyGH72L_Tj_j-mVvwuyOP8je5qXfrQPkuQq5w6njRXEgPTIx-O3n-csJnPuC_)

3\. Choose **BUCKET >** **upsolver-tutorials-mobile-users** by clicking on the dropdown box.  Fill in **FOLDER > data** and **DATE PATTERN > yyyy/MM/dd/HH/** click on **CONTINUE**. Upsolver automatically verifies the files in the bucket and displays blue checkmarks next to the file name.

![](https://1488427404-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MAbbZ6XL3iXMHb3pjqP%2F-MKbIJRmdCtTn8mHnmjD%2F-MKbInL8CXw4uMRSamob%2Fimage.png?alt=media\&token=0fd1abb6-75d9-4261-9f09-9687850a4b17)

4\. Upsolver automatically parses the data and displays a sample of the data that you’re working with. You can expand each record to view each event in a formatted view.

![](https://lh4.googleusercontent.com/VSsLMilef5nd-Vgy7IyjZlPk4wzq_IBmo6kLKnIcVZqgrCkmVnYUKeJuzkV5hb1ZIWX5BzjPeq4OC0BV1Cfx8oXcM3HeVf0N3B8ow229INdD-aGIkei3KPrfMn_DFhxDgQ2P-MUh)

5\. Click on **CREATE** to complete the Amazon S3 Data Source. Notice the schema is automatically generated and data statistics are also displayed.

![](https://lh4.googleusercontent.com/1TzV0loU_yMvnGi1-Yxk28yrhiR1ITl0IxeZTHMKROZQsOdVctaTtmNaWTe4wS1CAp4DWIyhUGYQVF8GkTV5qKsQFIphxoemWPxNzimagThCnDD9P9FmKZewIAzZkEknvTdeM_lf)

## **Create a data output**

1\. Click on **NEW OUTPUT** on the upper right corner. (You can also get to the output screen by clicking on **OUTPUTS > NEW**)

![](https://lh4.googleusercontent.com/MWKDM4NzS3bsF93Pq5DZ-4nd5QV6B7JXOS2cA1N8vy92qij_fPmPTPeilDb1XiW3DjcZMc8L0YC-62snOK6wiXnefjBe-Ppku7s9ymdk3M5sS7NEUNiYPpJaQeHTe5mYgkfgBHV0)

2\. Choose **MySQL** output by clicking on **SELECT**. We’re going to use MySQL as our output for this tutorial. You can output to any Upsolver data outputs.&#x20;

![](https://lh4.googleusercontent.com/PXhvs6a7EiGfmAHcgqwN3_Jwz96iPo6L17sUl5E534p062iFtkakUxPbFWcx8g2BIVYVSV26ayqeEaPMGXLmkAOXmcIoxewZkcuVT2iyOgDUlaU1860lpsaVBNCNIwk1rv3s-19T)

3\. Enter **gaming** as the name of the output. Leave everything as default and click on **NEXT**.

![](https://lh5.googleusercontent.com/pwqXwMAeu4kndQzv2fMDh4-N5rB_q6S6vpsR10OEITDHI4aatdyMWBEfhO8dpdBuaxsa5hVm1195UMzW9qM38Jnb6miZ49_RR8Koi7LBaRr8v7Y2P68vzdsJFE5Ck4Ppi6ByzegG)

4\. Expand the **payload** field. Add a field to your output by clicking on the **+** sign next to **eventName**. The field that we’re adding is. **data.payload.eventName**.

![](https://lh4.googleusercontent.com/kYfGKdqytOPUi7-qXFq-lStbLuIpgE1bv8L_Q-gOPypxSCCCNmpcK7Wk-FtVcYA9d_BQKMbAzg8_Cjb36rumK2Z-QC_m921h05G4hRnss1Qxvtc1x3H_vU8ZwMc_qnOXKqVpnvAE)

## **Data transformation**

1\. Rename the fields by clicking on the field under **SCHEMA COLUMN** and rename **payload.eventname** to **event\_name**

![](https://lh4.googleusercontent.com/v_6Rqnc7nFQq3xCUF4DBwELml7m1ZhvPkZIBpIZ6kg7pLHIaO1oJchci6FFf4xX-xKAswiu6GemKqHAH5_ztLKOAmh8FZa-XSthejIQDOtXmSsP-0cEt59NMJkzxqmcUEZJNPBjo)

2\. Transform **data.clientEventTime** to from unix epoch to human readable format in UTC. Click on **Add Calculated Field** on the upper left and find the **TO\_DATE** function, then click on **SELECT**. Enter **data.clientEventTime** in the **DATETIME** box and give the field a **NAME** called **event\_time**. Click on **PREVIEW** to make sure the date looks right and click on **SAVE**.&#x20;

![Click on Add Calculated Field](https://1488427404-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MAbbZ6XL3iXMHb3pjqP%2F-MLsuS8DqXNAjHhivDHf%2F-MLsvXRnwt5KgUL6oS3p%2Fimage.png?alt=media\&token=64df0af0-0bb2-467b-b2ae-87ac5b3c8c63)

![Add Calculated Field](https://1488427404-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MAbbZ6XL3iXMHb3pjqP%2F-MLtxeO7LqCLiY5a9J6U%2F-MLtyNKwDgmpekp8nVyy%2Fimage.png?alt=media\&token=20f880f6-5405-4857-8acc-a4b1fe53a84c)

![Find the TO\_DATE() function and click on SELECT](https://1488427404-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MAbbZ6XL3iXMHb3pjqP%2F-MLtyS_yYbm6YC2MVMVU%2F-MLtyc9lEi001qvuIF7s%2Fimage.png?alt=media\&token=4658e24e-2a30-400c-b777-8b9906b355fa)

![Convert data.clientEventTime to UTC time named event\_time. Click on PREVIEW and SAVE.](https://1488427404-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MAbbZ6XL3iXMHb3pjqP%2F-MLtyS_yYbm6YC2MVMVU%2F-MLtyoDJQGyKqpkuMbIp%2Fimage.png?alt=media\&token=e981ee52-fc04-4c56-b453-c880148819be)

![Find the TO\_DATE() function and click on SELECT](https://1488427404-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MAbbZ6XL3iXMHb3pjqP%2F-MLsuS8DqXNAjHhivDHf%2F-MLsvhcRXsH5FuM9rwOL%2Fimage.png?alt=media\&token=85d0af33-d029-4f0b-84e4-e88e29243db2)

![Convert data.clientEventTime and to human readable format in UTC. Click on PREVIEW and SAVE. ](https://1488427404-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MAbbZ6XL3iXMHb3pjqP%2F-MLsuS8DqXNAjHhivDHf%2F-MLsw225ZMq9J_k7IU4P%2Fimage.png?alt=media\&token=641ed00c-ace3-4393-a9f4-e1c90ef0698f)

3\. Add **event\_time** to your output by clicking on **+** next to the field.

4\. Add a count for each event by clicking on **Make Aggregated > OK > Add Aggregation**. Click on **SELECT** next to **COUNT (\*)** and click on **SAVE**.

![Click on Make Aggregated](https://1488427404-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MAbbZ6XL3iXMHb3pjqP%2F-MNR2wny0h0xfVtelb1y%2F-MNR3fNTpq8QxtJ46Mos%2Fimage.png?alt=media\&token=0df81647-09e1-451c-904f-ef8ae8ff539d)

![Click OK](https://1488427404-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MAbbZ6XL3iXMHb3pjqP%2F-MLsuS8DqXNAjHhivDHf%2F-MLt3tghPE1w6wTh1AU-%2Fimage.png?alt=media\&token=a0fed81b-3ecf-41dd-a0c9-717f8e1af3c3)

![Click on Add Aggregation and SELECT COUNT(\*)](https://1488427404-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MAbbZ6XL3iXMHb3pjqP%2F-MLsuS8DqXNAjHhivDHf%2F-MLt47GcTmZbELEPnl2b%2Fimage.png?alt=media\&token=08954e8f-0fc4-43a8-b876-3e0307ce778f)

![Click on SAVE](https://1488427404-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MAbbZ6XL3iXMHb3pjqP%2F-MLsuS8DqXNAjHhivDHf%2F-MLt4PQpq-U87KvtNyHi%2Fimage.png?alt=media\&token=93c3aff1-5961-4980-81fa-492a6e21e055)

{% hint style="info" %}
Click over to the **SQL tab** on the upper right hand corner. Keep in mind all changes that are made in the SQL view will also be represented in the UI view and vice-versa. You can see the work from the UI automatically generated a SELECT statement. Click on **PREVIEW** to ensure data looks correct.
{% endhint %}

## **Connect to MySQL and start streaming**

1\. Click on **RUN** on the upper right corner and choose **Create a new MySQL Connection** from the **MySQL Connection** dropdown.

![](https://lh5.googleusercontent.com/ukOA0BD-g9cxsdWuJhI9ab7g_aOJZn3JpfA6PlbGmNbaeO3dcUrPXY5bskTj7gjxFqgKqYhSeoGP_Ix0lN6nj-PPI9HJEmD1jP4qxMct5aYUkd8fq3UDIJHbSOnseGepvSaSIDGc)

2\. (Optional) Setup a RDS MySQL environment if you don’t have an existing environment.

{% hint style="info" %}
If you don’t have an existing MySQL connection, you can either start up a MySQL instance following the instructions [here](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CreateDBInstance.html) or you can install it locally on your computer by following instructions [here](https://upsolver.gitbook.io/upsolver-1/getting-started/start-using-upsolver/upsolver-in-10-minutes/mysql-data-output-option).

Make sure you have the correct inbound rule configured for the MySQL (RDS) security group. The inbound rule allowing all IPs (0.0.0.0/0) to access 3306 need to be created.
{% endhint %}

3\. Define MySQL Connection in the following format and click on **CREATE.**

* **CONNECTION STRING** (make sure the schema name exists in your MySQL environment)**:** `jdbc:mysql://database-2.eyb4e3j72ydseds.us-east-1.rds.amazonaws.com:3306/dev`
* **USER NAME: `<mysql username> (AWS RDS default username is admin)`**
* **PASSWORD: `<mysql password>`**
* **NAME: `<name of the connection>`**

4\. Select **OUTPUTS > gaming > Run** and choose the connection that you just defined as **MYSQL CONNECTION.** Choose **dev** (make sure this database exists in your MySQL environment) under **SCHEMA** and **TABLE NAME > gaming.** Click on **NEXT**.

![](https://1488427404-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MAbbZ6XL3iXMHb3pjqP%2F-MKm96ciSrQ1LulqjnnQ%2F-MKm9oLpyIV2euPl30FB%2Fimage.png?alt=media\&token=abc44497-9ee3-47b9-83c8-9e2cdbb8b99d)

5\. Use the sliding bar to only load from the data from last day click on **DEPLOY**. Keep in mind that **ENDING AT** set as **Never** means it's a continuous data stream. Click on OK if a warning appears. &#x20;

![](https://1488427404-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MAbbZ6XL3iXMHb3pjqP%2F-MNR4J8kfyUUSJIizfoY%2F-MNR4bgtvI-3ievmKfip%2Fimage.png?alt=media\&token=c18fd1db-5500-4713-86b2-05605c2c22b9)

6\. Upsolver starts outputting the data to MySQL. You can keep track of the output status by clicking on PROGRESS. Wait for a moment for the data to output.

![](https://lh3.googleusercontent.com/VwPyk_YOC03OdL3EvZBYOQFEJsOS38WMBspKx45LIV3Vg4ulerw3IXVcrqkurp0OfW-9yGsqJh0_NZfLJpjG2S1-vkeogtzkC-PsPo_zqk42pqjG2Od8VZ8vgaiGBoRjHlfCCMZE)

## **Verify your data in MySQL**

![](https://1488427404-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MAbbZ6XL3iXMHb3pjqP%2F-MLtzb4f6iA-oWvR_9gT%2F-MLtzoH5LHzDU2Picu1B%2Fimage.png?alt=media\&token=f5d711fb-33be-4a03-bd45-cc7581c41c4c)
