Data outputs
This page provides an overview of the different data outputs that can be created in Upsolver.
Last updated
Was this helpful?
This page provides an overview of the different data outputs that can be created in Upsolver.
Last updated
Was this helpful?
Upsolver supports sending data to various data outputs including Amazon Athena, Redshift Spectrum, Upsolver, and more.
These data outputs enable you to write data to blob storage and continue processing in these systems (e.g. send data to S3 then trigger notifications and send emails).
Athena is a managed query engine over the data stored in files in Amazon S3. Through a configuration in the Glue Data Catalog (a metastore that holds the metadata of the location and format of the files), Athena is able to provide SQL access to the data.
Amazon Athena is a core data output that enables you to get your data into a data lake and query it in a cost-effective manner. After the data is organized, compacted, and stored in an Athena table on Amazon S3, you can run fast, cheap queries on the data lake.
A queryable data lake is a useful step in the process as it allows you to inspect your data and run ad-hoc queries to understand your data (using an SQL interface) before writing to a database (which is more expensive and requires a database). Data is stored in parquet format; it is columnar and partitioned, thus enabling you to query only the data you need.
There are also significant cost/speed benefits to using Athena as it only charges for data scanned (e.g. $X per TB).
For example, a 100 MB compressed parquet file may include a GB of raw data. If you only query one out of the ten columns in the data file, you are only charged for scanning 10 MB of data.
Athena also allows you to partition the data by date (e.g. per year). This date will appear as a column and can be added to your WHERE clause. Since Athena resolves this condition first and only scans the relevant partitions, this will further reduce the cost.
Consider if you have stored 50 TB of data over two years at a rate of 50 GB of data a week:
If you only need to query the past 5 weeks, you will be querying only 250 GB of data.
If you only need to access a tenth of the columns, this means that you are only paying to access 25 GB.
As a result, you can pay just cents for a query instead of hundreds of dollars. This huge reduction in cost makes data that would have previously been too expensive to use now useable.
Redshift Spectrum provides similar functionality to Amazon Athena at a similar cost. The data is stored in an Amazon S3 bucket that is connected to a Redshift cluster. Since the data is partitioned, any query looks in the relevant partition only.
A Redshift Spectrum data output provides you with speedy and cost-effective access to data and is also useful for querying and joining data in S3 and a Redshift Spectrum DB.
These data outputs enable you to stream your events to your Amazon Kinesis stream or Apache Kafka topic.
Database data outputs are used when projects require a specific database output.
One use case would be if you write all your data into staging tables, use periodic queries to aggregate the data, and then write this to the final data tables (this follows an Extract Load Translate methodology). You may then clear your staging tables periodically, preserving only the transformed data, ensuring that excessive storage is not required and controlling the costs.
Given that the cost of a database is hundreds of times the cost of storing data in S3, it is important to plan your DB data outputs very carefully.
A common scenario for customers moving to Upsolver is to store the raw data in one of the more cost-effective data sources, pre-aggregate the data, and then write the output to the smaller tables on the database.
Another use case would be storing log analytics in Elasticsearch, resulting in a fast-growing database with many duplicates. By deduping the data, it would be possible to make significant savings without losing any fidelity.
Upsolver is the data output for plain ETLs and is useful when you need to transform data and create a new data source with this transformed data. Typically this would include cleaning up and sanitizing data, ordering data, aggregating data, and/or splitting data into multiple streams.
This sort of data output can be created as part of a pre-preparation process. Once the data source is created this can be duplicated and the output type can be switched.
Upsolver supports Qubole, a cloud-native data platform external system that provides you with managed clusters on various open sources systems such as Presto (which is the engine under Athena).
With Qubole, you have control over the servers and can spin up additional servers if required. Qubole pricing is based on servers and not on data, making it more cost effective if you have huge volumes of data to query.
Lookup tables are defined as a transformation like any other output, with the output being a key-value store that you can query to enable faster lookups (e.g. to get all users who clicked on a specific ad in "real-time").
Lookup tables are useful for:
By querying a lookup table, it is possible to enrich one stream with data from another stream.