Synapse Analytics and .NET for Apache Spark Example 3 - CTE()

The next example is how to do a CTE (Common Table Expression). When creating the CTE I will also rename one of the columns from “dataType” to “x”.

T-SQL

WITH CTE(x, dataType, dataSubType)
AS
(
  SELECT dateTime, dataType, dataSubType FROM chicago.safety_data
)
SELECT * FROM CTE;

Spark SQL

WITH CTE 
AS 
    (SELECT dateTime as x, dataType, dataSubType FROM chicago.safety_data)

SELECT * FROM CTE

DataFrame API (C#)

The DataFrame example is a bit odd - by creating a data frame with the first query we have the CTE that we can use:

var dataFrame = spark
                    .Read()
                    .Table("chicago.safety_data")
                    .Select("dateTime", "dataType", "dataSubType")
                    .WithColumnRenamed("dateTime", "x");
dataFrame.Show();

To see this in action, please feel free to deploy this repo to your Synapse Analytics repo: https://github.com/GoEddie/SynapseSparkExamples