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).
purchase_id
customer_id
product_name
quantity
unit_price
1
1
Orange
3
0.25
2
1
Apple
1
0.5
3
1
Banana
2
0.25
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:
customer_id
purchase_id
product_name
“1”
“1”
“Orange”
“1”
“2”
“Apple”
“1”
“3”
“Banana”
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:
Customer
Purchase
Product
“1”
“1”
“Orange”
“1”
“2”
“Apple”
“1”
“3”
“Banana”
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:
customer_id
purchase_id
product_name
quantity
“1”
“1”
“Orange”
3
“1”
“2”
“Apple”
1
“1”
“3”
“Banana”
2
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:
customer_id
purchase_id
product_name
total_cost
“1”
“1”
“Orange”
0.75
“1”
“2”
“Apple”
0.5
“1”
“3”
“Banana”
0.5
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

Transform with SQL

Transforming data with SQL

Last modified 2yr ago

purchase_id	customer_id	product_name	quantity	unit_price
1	1	Orange	3	0.25
2	1	Apple	1	0.5
3	1	Banana	2	0.25

Customer	Purchase	Product
“1”	“1”	“Orange”
“1”	“2”	“Apple”
“1”	“3”	“Banana”