Upsolver
Search…
Welcome to Upsolver
Getting Started
Start using Upsolver for free
Get started as a Upsolver user
Upsolver concepts
Upsolver Amazon AWS deployment guide
Tutorials and FAQ
Tutorials
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
Glossary
Troubleshooting
Connecting data sources
Amazon AWS data sources
Microsoft Azure data sources
Kafka data source
Google Cloud Storage data source
File upload data source
CDC data sources (Debezium)
JDBC data source
HDFS data source
Data source UI
Data source properties
Data outputs and data transformation
Data outputs
Data transformation
Guide for developers
Upsolver REST API
CI/CD on Upsolver
Administration
Connections
Clusters
Python UDF
Reference data
Workspaces
Monitoring
Security
Release notes
Change log
Support
Upsolver support portal
Powered By GitBook
Merge data use case
This article outlines a use case in which you merge data from two sources together to create a final output.
A very common use case is merging data from a data stream (A) with a reference data source (B). This requires you to use a field to lookup (and merge) data from the reference data source (B) with the data stream (A) and then save the resulting data in the output.
This can be achieved by adding a lookup to the output and specifying the matching key. Any data brought in from the data source (B) comes in via an aggregation in the lookup.
For example, you can use the LAST aggregation function to select the last value of a field within a time window. This can also be used to look up user information that is stored in a user data table—that is, given the USER_ID, look up the USER_DETAIL, USER_DETAIL2, and so on.

  • Intermediate

1. On the Outputs page, select the desired output and click Edit (or create a new output).
2. Click Add Lookup and select the lookup source (e.g. an existing data source or lookup table). Then click Next.
3. In Join Fields, select the field to join to in data source (B) (e.g.USER_ID). The matching field in data stream (A) is selected automatically.
Note: The field on the left is the field in data source (B) that we are looking up from. The field on the right is the key from data stream (A).
4. Add aggregations; for example:
  • LAST(USER_ID)
  • LAST(USER_DETAIL1)
  • LAST(USER_DETAIL2)
5. Set the Time Frame to infinite, by entering -10000 in the From field. This ensures you can access all the data irrespective of when it was written.
6. Select Use Full History to treats the data source (B) as a reference data table. This is useful if the latest view of the data is required, and the actual time that the data was written is irrelevant.
7. Enter an Alias for the lookup and click Save.
Toggle from the UI to SQL view to see the JOIN statement (it can appear as part of a WITH statement or inline).
8. Click Run and fill in the required details to deploy the output. See: Run an output​
Previous
AWS S3 to Athena use case
Next
Full vs. Partial Inbound Data Records
Last modified 2yr ago
Copy link