Mapping data to a desired schema

This page goes over how to map your data to a desired schema using Transform with SQL in Upsolver.

Transform with SQL enables you to map your data into a desired schema. The process of mapping in Upsolver is done in two simple steps:

  1. Ingest your raw data into the data lake by configuring a data source in Upsolver.

  2. Map your data to a desired schema by configuring an output using SQL.

To demonstrate, assume we have the following data in CSV format in a data source called Purchases (all values in this table are strings).

Define simple schema

If we define a table as:

SELECT customer_id, purchase_id, product_name
FROM Purchases

The resulting table reflects that query as events stream in. The final table contains the data:

Rename column names

Renaming of column names is being done as follows using the AS statement:

SELECT customer_id AS Customer, purchase_id AS Purchase, 
       product_name AS Product
FROM Purchases

The column names in the resulting table have been renamed:

Data type conversion

Conversion of data types is done using : next to the selected column name.

We will demonstrate a conversion of the column quantity from the example data source Purchases (which is in string format) into BIGINT format.

SELECT customer_id, purchase_id, product_name, quantity:BIGINT
FROM Purchases

The resulting table reflects the conversion:

Perform calculations

It is possible to perform inline calculations when defining the schema.

If we define a table as:

SELECT customer_id, purchase_id, product_name, 
       quantity:BIGINT * unit_price:BIGINT as total_cost:BIGINT
FROM Purchases

The result of the query is the following table which contains the calculated field total_cost:

It is also possible to first calculate the field and then just use it in the query:

SET total_cost = quantity:BIGINT * unit_price:BIGINT;
SELECT customer_id, purchase_id, product_name, total_cost
FROM Purchases

Last updated