Upsolver
Contact Support
  • Welcome to Upsolver
  • Getting Started
    • Start using Upsolver for free
    • Get started as a Upsolver user
      • Upsolver in 5 minutes
        • Upsolver Quickstart in 5 minutes
          • Additional sandbox fun
        • Amazon Athena data output
        • MySQL (AWS RDS) data output
        • Local MySQL data output
      • Upsolver free training
        • Introduction to Upsolver
          • Transform and write data to Amazon Athena
          • Pre-aggregate data for efficiency and performance
          • UPSERT streaming data to Amazon Athena
      • Prerequisites for AWS deployment
      • AWS integration
      • Deploy Upsolver on your AWS account
      • Prerequisites for Azure Deployment
      • Azure Integration
        • Prerequisites for Azure Users
        • Log into Upsolver
        • Log into Azure & Authenticate
        • Set Up and Deploy Azure Resources
        • Delegate Resource Group, and Deploy Upsolver in Azure
        • Integrate Azure with Upsolver
    • Upsolver concepts
      • Deployment models
      • Upsolver components
      • Data ingestion
    • Upsolver Amazon AWS deployment guide
      • Private VPC
      • Upsolver VPC
      • AWS role permissions
      • VPC peering
    • Tutorials and FAQ
      • Tutorials
        • How To Re-process Data
        • Create an Amazon S3 data source
        • Create an Amazon Athena data output
        • Join multiple data streams for real-time analytics
        • Use Upsolver to index less data into Splunk
        • Upsert and delete use case
        • AWS S3 to Athena use case
        • Merge data use case
        • Full vs. Partial Inbound Data Records
      • FAQ
      • Infrastructure
        • What is a dry-run cluster?
    • Glossary
      • Language guide
        • SQL syntax reference
        • Functions
          • Aggregation Functions
            • APPROX_COUNT_DISTINCT
            • APPROX_COUNT_DISTINCT_EACH
            • AVG
            • AVG_EACH
            • AVG_TIME_SERIES
            • COLLECT_SET
            • COLLECT_SET_EACH
            • COUNT
            • COUNT(*)
            • COUNT_DISTINCT
            • COUNT_EACH
            • COUNT_IF
            • DECAYED_SUM
            • DYNAMIC_SESSIONS
            • FIRST
            • FIRST_ARRAY
            • FIRST_EACH
            • FIRST_TIME_SERIES
            • LAST
            • LAST_ARRAY
            • LAST_EACH
            • LAST_K
            • LAST_K_EACH
            • LAST_TIME_SERIES
            • MAX
            • MAX_BY
            • MAX_EACH
            • MAX_TIME_SERIES
            • MIN
            • MIN_BY
            • MIN_EACH
            • MIN_TIME_SERIES
            • SESSION_COUNT
            • STD_DEV
            • STD_DEV_EACH
            • STRING_MAX_EACH
            • STRING_MIN_EACH
            • SUM
            • SUM_EACH
            • SUM_TIME_SERIES
            • WEIGHTED_AVERAGE
          • Calculated functions
            • Aerospike functions
            • Array functions
            • Conditional functions
            • Date functions
            • External API functions
            • Filter functions
            • Numeric functions
            • Spatial functions
            • String functions
            • Structural functions
              • ZIP
            • Type conversion functions
      • Data formats
      • Data types and features
      • Database output options
      • Upsolver shards
      • Permissions list
      • Index
    • Troubleshooting
      • My CloudFormation stack failed to deploy
      • My private API doesn't start or I can't connect to it
        • Elastic IPs limit reached
        • EC2 Spot Instance not running
        • DNS cache
        • Security group not open
      • My compute cluster doesn't start
      • I can't connect to my Kafka cluster
      • I can't create an S3 data source
      • Data doesn't appear in Athena table
      • I get an exception when querying my Athena table
      • Unable to define a JDBC (Postgres) connection
  • Connecting data sources
    • Amazon AWS data sources
      • Amazon S3 data source
        • Quick guide: S3 data source
        • Full guide: S3 data source
      • Amazon Kinesis Stream data source
      • Amazon S3 over SQS data source
      • Amazon AppFlow data source
        • Setup Google Analytics client ID and client secret.
    • Microsoft Azure data sources
      • Azure Blob storage data source
      • Azure Event Hubs data source
    • Kafka data source
    • Google Cloud Storage data source
    • File upload data source
    • CDC data sources (Debezium)
      • MySQL CDC data source
        • Binlog retention in MySQL
      • PostgreSQL CDC database replication
    • JDBC data source
    • HDFS data source
    • Data source UI
    • Data source properties
  • Data outputs and data transformation
    • Data outputs
      • Amazon AWS data outputs
        • Amazon S3 data output
        • Amazon Athena data output
          • Quick guide: Athena data output
          • Full guide: Athena data output
          • Output all data source fields to Amazon Athena
        • Amazon Kinesis data output
        • Amazon Redshift data output
        • Amazon Redshift Spectrum data output
          • Connect Redshift Spectrum to Glue Data Catalog
        • Amazon SageMaker data output
      • Data lake / database data outputs
        • Snowflake data output
          • Upsert data to Snowflake
        • MySQL data output
        • PostgreSQL data output
        • Microsoft SQL Server data output
        • Elasticsearch data output
        • Dremio
        • PrestoDB
      • Upsolver data output
      • HDFS data output
      • Google Storage data output
      • Microsoft Azure Storage data output
      • Qubole data output
      • Lookup table data output
        • Lookup table alias
        • API Playground
        • Serialization of EACH aggregations
      • Kafka data output
    • Data transformation
      • Transform with SQL
        • Mapping data to a desired schema
        • Transforming data with SQL
        • Aggregate streaming data
        • Query hierarchical data
      • Work with Arrays
      • View outputs
      • Create an output
        • Modify an output in SQL
          • Quick guide: SQL data transformation
        • Add calculated fields
        • Add filters
        • Add lookups
          • Add lookups from data sources
          • Add lookups from lookup tables
          • Adding lookups from reference data
        • Output properties
          • General output properties
      • Run an output
      • Edit an output
      • Duplicate an output
      • Stop an output
      • Delete an output
  • Guide for developers
    • Upsolver REST API
      • Create a data source
      • Modify a data source
      • API content formats
    • CI/CD on Upsolver
  • Administration
    • Connections
      • Amazon S3 connection
      • Amazon Kinesis connection
      • Amazon Redshift connection
      • Amazon Athena connection
      • Amazon S3 over SQS connection
      • Google Storage connection
      • Azure Blob storage connection
      • Snowflake connection
      • MySQL connection
      • Elasticsearch connection
      • HDFS connection
      • Qubole connection
      • PostgreSQL connection
      • Microsoft SQL Server connection
      • Spotinst Private VPC connection
      • Kafka connection
    • Clusters
      • Cluster types
        • Compute cluster
        • Query cluster
        • Local API cluster
      • Monitoring clusters
      • Cluster tasks
      • Cluster Elastic IPs
      • Cluster properties
      • Uploading user-provided certificates
    • Python UDF
    • Reference data
    • Workspaces
    • Monitoring
      • Credits
      • Delays In Upsolver pipelines
      • Monitoring reports
        • Monitoring system properties
        • Monitoring metrics
    • Security
      • IAM: Identity and access management
        • Manage users
        • Manage groups
        • Manage policies
      • Git integration
      • Single sign-on with SAML
        • Microsoft Azure AD with SAML sign-on
        • Okta with SAML sign-on
        • OneLogin with SAML sign-on
      • AMI security updates
  • Support
    • Upsolver support portal
  • Change log
  • Legal
Powered by GitBook
On this page
  • Create a data source
  • Create a data output
  • Data transformation
  • Connect to MySQL and start streaming
  • Verify your data in MySQL

Was this helpful?

  1. Getting Started
  2. Get started as a Upsolver user
  3. Upsolver in 5 minutes

MySQL (AWS RDS) data output

Process streaming data from end-to-end within 10 minutes. The data source is an Amazon S3 bucket (updated continuously). Upsolver processes the data and loads to MySQL hosted by AWS.

PreviousAmazon Athena data outputNextLocal MySQL data output

Last updated 4 years ago

Was this helpful?

To help you get started with Upsolver, you can try it out for free. You can choose between and 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.

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.

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.

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.

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

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)

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.

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

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.

Data transformation

1. Rename the fields by clicking on the field under SCHEMA COLUMN and rename payload.eventname to event_name

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.

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 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.

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.

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

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.

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.

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.

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.

Verify your data in MySQL

If you don’t have an existing MySQL connection, you can either start up a MySQL instance following the instructions or you can install it locally on your computer by following instructions .

here
here
Upsolver Dedicated Compute
Community
Click on Add Calculated Field
Add Calculated Field
Find the TO_DATE() function and click on SELECT
Convert data.clientEventTime to UTC time named event_time. Click on PREVIEW and SAVE.
Find the TO_DATE() function and click on SELECT
Convert data.clientEventTime and to human readable format in UTC. Click on PREVIEW and SAVE.
Click on Make Aggregated
Click OK
Click on Add Aggregation and SELECT COUNT(*)
Click on SAVE