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
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
Transform with SQL
Mapping data to a desired schema
Transforming data with SQL
Aggregate streaming data
Query hierarchical data
Work with Arrays
View outputs
Create an output
Run an output
Edit an output
Duplicate an output
Stop an output
Delete an output
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
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:
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!
Previous
Transform with SQL
Next
Transforming data with SQL
Last modified
1yr ago
Copy link
Contents
Define simple schema
Rename column names
Data type conversion
Perform calculations