Mapping data to a desired schema

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:
1
SELECT customer_id, purchase_id, product_name
2
FROM Purchases
Copied!
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:
1
SELECT customer_id AS Customer, purchase_id AS Purchase, 
2
       product_name AS Product
3
FROM Purchases
Copied!
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.
1
SELECT customer_id, purchase_id, product_name, quantity:BIGINT
2
FROM Purchases
Copied!
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:
1
SELECT customer_id, purchase_id, product_name, 
2
       quantity:BIGINT * unit_price:BIGINT as total_cost:BIGINT
3
FROM Purchases
Copied!
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:
1
SET total_cost = quantity:BIGINT * unit_price:BIGINT;
2
SELECT customer_id, purchase_id, product_name, total_cost
3
FROM Purchases
Copied!

Transform with SQL

Transforming data with SQL

Last modified 1yr ago

Copy link

Contents