No Results
Example 1C: Create SQL Query Transform

In this step, you will create a dataset with summary totals for each sales region. Since this information is not in the CSV files, you can use the tools in the pipeline to enrich the data with this summary information. In this case, you can write an SQL query to add up the sales and then group them into columns by region.

Perform the following steps to create a transform from the states feed:

  1. Click Pipeline.
    The getting_started connection and the states feed you created are displayed in the pipeline.
  2. Click the gear icon button in the states feed box.
  3. Select +, and then New Transform.
    A pop-up for choosing a transform type is displayed.
  4. In the pop-up, select SQL Transform.
    A new page is displayed.
  5. In Transform Name, enter region_summary.
  6. Turn on the Enable Server Subscription toggle switch in order for data for this transform to be continually fetched, updated, and cached.
  7. Click Next.
    You are taken to the Dataset References tab. No changes are needed here, so click Next to proceed. You are taken to the Query tab.
  8. In Query, paste the following into the query box to replace the default query text:
    select
    (select sum(TotalSales) from {src.states}
    where SalesRegion='Northeast' group by SalesRegion) as "Northeast",
    (select sum(TotalSales) from {src.states}
    where SalesRegion='Midwest' group by SalesRegion) as "Midwest",
    (select sum(TotalSales) from {src.states}
    where SalesRegion='West' group by SalesRegion) as "West",
    (select sum(TotalSales) from {src.states}
    where SalesRegion='South' group by SalesRegion) as "South",
    '2020' as "Year"
    
  9. Click Next.
    You are taken to the Attribute Indexing tab. No changes are needed here, so click Next to proceed. Similarly, no changes are needed for the Upstream Variables tab, so click Next to go to Data Preview. On the Data Preview tab, you can observe the data preview of the region_summary transform from the getting_started connection.
  10. Click Save and Close.
    The newly created transform is displayed in the pipeline.

For more information on Transforms, refer to the corresponding chapter.


Terms | Privacy