<jupyter_start><jupyter_text>Read Data from Silver VAUsage Table and Write to Gold VAUsage Action Counts TableTo run this notebook, import it into Azure Synapse and attach it to an Apache Spark Pool.When creating the Apache Spark Pool, choose "Small" as the Node Size. Choose the option to disable autoscaling. For the number of nodes, choose the lowest number, 3.Be sure to run the "rate-streaming-to-bronze" Notebook and "bronze-to-silver-vausage" Notebook beforehand.<jupyter_code>%%spark
import org.apache.spark.sql.functions._
import org.apache.spark.sql.types._
import org.apache.spark.sql.SaveMode<jupyter_output><empty_output><jupyter_text>Configure the Storage Account (to read from)Replace the value `` with the name of the storage account where the Silver Telemetry Table data is stored.<jupyter_code>%%spark
val storageAccountName = "<storageAccountName>"
val silverDataLocation: String = "abfss://datalake@"+storageAccountName+".dfs.core.windows.net/silverSynapse/VAUsage"
val goldDataLocation : String = "abfss://datalake@"+storageAccountName+".dfs.core.windows.net/goldSynapseCDM/VAUsageActionCounts"<jupyter_output><empty_output><jupyter_text>Prepare to Use CDMIf this Notebook is being run for the first time (i.e. inside the "GoldSynapseCDM/VAUsageActionCounts" folder in the storage account there is no 'default.manifest.cdm.json' file or 'GoldVAUsage' folder), then set the value of "entitiesExist" to 'false'.If this is not the first time the Notebook is being run with this storage account, set to 'true'.<jupyter_code>%%spark
var entitiesExist : Boolean = false // change to 'true' if you have run the Notebook before<jupyter_output><empty_output><jupyter_text>Read the DataHere the data is read from the `silverDataLocation` specified in the previous cell, which is configured using the value inputted for `storageAccount`.<jupyter_code>%%spark
var silverDF = spark.read.format("delta").load(silverDataLocation)<jupyter_output><empty_output><jupyter_text>View the Data from the Silver VAUsage TableRun this cell to see 10 rows from the Silver VAUsage Table, ordered in descending order by *ProcessedTimestamp*.Change the value of `numRows` to however many number of rows you would like to be displayed.<jupyter_code>%%spark
silverDF.orderBy(col("ProcessedTimestamp").desc).show(numRows = 10)
%%spark
silverDF = silverDF.drop("processedTime")<jupyter_output><empty_output><jupyter_text>Run the following cell to get a better look at the Silver VAUsage Delta Table data schema.<jupyter_code>%%spark
silverDF.printSchema()<jupyter_output><empty_output><jupyter_text>Configure the Schema of the DataThe schema of the Dataframe is configured to match the schema of the Gold VAUSage Action Counts Table.<jupyter_code>%%spark
var goldDF = silverDF.withWatermark("ProcessedTimestamp", "10 second").groupBy(
    window(col("ProcessedTimestamp"), "10 seconds", "10 second"),
    col("Object"),
    col("Action")
  ).count().withColumn("WindowStartDate", to_date(col("window").getItem("start")))

goldDF.printSchema()<jupyter_output><empty_output><jupyter_text>Write Data to Gold VAUSage Action Counts Table in the CDM Format<jupyter_code>%%spark
val CDMStorageAccount : String = storageAccountName + ".dfs.core.windows.net" 
val manifestPath : String = "datalake/goldSynapseCDM/VAUsageActionCounts/default.manifest.cdm.json"<jupyter_output><empty_output><jupyter_text>Writing for the First TimeRun this cell if the CDM manifest and entities have not yet been created (if you are running this Notebook for the first time).<jupyter_code>%%spark
if (!entitiesExist) { 
    goldDF.write.format("com.microsoft.cdm").
    option("storage", CDMStorageAccount).
    option("manifestPath", manifestPath).
    option("entity", "GoldVAUsage").
    option("format", "parquet").
    save()
}<jupyter_output><empty_output><jupyter_text>CDM Manifest and Entity Already CreatedRun this cell if you have run the Notebook before, and already have a manifest and entity inside your storage account.The manifest (default.manifest.cdm.json) and entity (GoldVAUsage) can be found in your storage account, inside the "datalake" container, and the "goldSynapseCDM/VAUsageActionCounts" folder.<jupyter_code>%%spark
if (entitiesExist) { 
    goldDF.write.format("com.microsoft.cdm").
    option("storage", CDMStorageAccount).
    option("manifestPath", manifestPath).
    option("entity", "GoldVAUsage").
    mode(SaveMode.Append).
    option("format", "parquet").
    save()
}<jupyter_output><empty_output><jupyter_text>View the Data<jupyter_code>%%spark
var goldReadDF = spark.read.format("com.microsoft.cdm").
option("storage", CDMStorageAccount).
option("manifestPath", manifestPath).
option("entity", "GoldVAUsage").
load()
%%spark
goldReadDF.orderBy(col("window.start").desc).show()
%%spark
goldReadDF.printSchema()
%%spark
display(goldReadDF.orderBy(col("window.start").desc))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10648714810281518, 'fraction_numerical': 0.002243982048143615, 'mean_word_length': 6.508422664624809, 'pattern_counts': {'":': 0, '<': 46, '<?xml version=': 0, '>': 46, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '229646', 'license': 'MIT', 'n_tokens_mistral': 1514, 'n_tokens_neox': 1460, 'n_words': 438, 'path': 'analytics/notebooks/sample-notebooks/CDM-silver-to-gold-vausage.ipynb', 'repo_name': 'shivanispatel/connected-vehicle-samples', 'size': '4902'}