--- title: Databricks Structured Streaming - Part 3 (Creating the Stream) description: This series on Databricks will guide you through structured streaming. In Part 3 we will look at interacting with the data using structured streaming. image: https://blog.coeo.com/hubfs/101219_JW_databricks.png --- [![](https://www.coeo.com/wp-content/themes/coeo/images/logo.svg)](https://blog.coeo.com/) +44 (0)20 3051 3595 | [info@coeo.com](mailto:info@coeo.com) | [Client portal login](https://my.coeo.com) # Databricks Structured Streaming - Part 3 (Creating the Stream) # The Coeo Blog ![Andy Mitchell](https://blog.coeo.com/hubfs/AndyMCircleLow.jpg) In [Part 1](https://blog.coeo.com/databricks-structured-streaming) of this series we created a Databricks Community Account and cluster. In [Part 2](https://blog.coeo.com/databricks-structured-streaming-part2) we captured some streaming data to a file on our Databricks cluster. In Part 3 we will look at interacting with the data using structured streaming. 1. Using the Databricks cluster created in the previous post ![](https://blog.coeo.com/hubfs/image-16.png)     2. Navigate to "Introduction to Databricks Structured Streaming" where we should have the notebook from Part 2. ![](https://blog.coeo.com/hubfs/image-29.png)      3. Create a Python notebook called "Part 3 (Creating the stream)" ![](https://blog.coeo.com/hubfs/image-30.png)      4. Enter the following code into the cmd pane of the notebook. These are the variables that we will use for the schema for the json data and the location where we stored the files. ![](https://blog.coeo.com/hubfs/image-31.png)     5. The Schema for the json can be found on the website [Seattle Real Time Fire 911 Calls](https://data.seattle.gov/Public-Safety/Seattle-Real-Time-Fire-911-Calls/kzjm-xkqj), however this does not contain all of the columns that are in the api. This is then converted to a Pyspark Struct, the following information may be of use [Microsoft.Spark.Sql.Types Namespace](https://docs.microsoft.com/en-us/dotnet/api/microsoft.spark.sql.types?view=spark-dotnet)      6. Get the URL for the data stream and paste it where is above, this can be found from the API button on the following web page:[https://data.seattle.gov/Public-Safety/Seattle-Real-Time-Fire-911-Calls/upug-ckch](https://data.seattle.gov/Public-Safety/Seattle-Real-Time-Fire-911-Calls/upug-ckch)      7. Add another cmd pane by clicking on the + that appears below the middle of the previous one and add the following code. This reads the files from the file location (inputPath) using the schema (jsonSchema) ![](https://blog.coeo.com/hubfs/image-32.png)      8. Run both of the panes above click the ![](https://blog.coeo.com/hubfs/image-26.png) at the top of the notebook or run each cell individually using the play button in the top right. The second pane should return ![](https://blog.coeo.com/hubfs/image-34.png)     9. Click on the arrow to expand the results and you will see the following: ![](https://blog.coeo.com/hubfs/image-35.png)     10. This shows that a DataFrame has been created but we cannot see any content.     11. Add another cmd pane and add the following code, then run the cmd ![](https://blog.coeo.com/hubfs/image-36.png)     12. The first 1000 lines of the results are shown ![](https://blog.coeo.com/hubfs/image-37.png)     13. We have some data and now we would like see what the most frequent call type is, for this we need to aggregate the data by Type.     14. Add another cmd cell and call it "Static aggregation", and add the following code: ![L22-03-02_1-Code](https://blog.coeo.com/hs-fs/hubfs/L22-03-02_1-Code.png?width=934&name=L22-03-02_1-Code.png)     15. Run the code above to generate the dataframe "StaticCountsDF"     16. Note the last line of the code registers a Temporary view called "static\_counts", this can be used in SQL queries external to the dataframe     17. Add another cmd cell and call it "Aggregation results" and add the following code. **Note "%sql" switches the cell to contain SQL code rather than Python** ![L22-03-02_2-AggregationResults](https://blog.coeo.com/hs-fs/hubfs/L22-03-02_2-AggregationResults.png?width=747&name=L22-03-02_2-AggregationResults.png)     18. Run the cell and look at the results ![L22-02-01-results](https://blog.coeo.com/hs-fs/hubfs/L22-02-01-results.png?width=1799&name=L22-02-01-results.png)     19. Switch to "Bar Chart" ![L03-04-08-Visualize](https://blog.coeo.com/hs-fs/hubfs/L03-04-08-Visualize.png?width=63&name=L03-04-08-Visualize.png) and observe the frequency of each type of call ![L22-02-02-chartresults](https://blog.coeo.com/hs-fs/hubfs/L22-02-02-chartresults.png?width=681&name=L22-02-02-chartresults.png)     20. The Data we have displayed above is a static representation of the data that we have saved in the folder. However what we really need is the data automatically loaded     21. Add a new cmd cell and name it "Load the Streaming Dataframe" and add the following code ![L22-03-02_3-LoadTheStreamingDataframe](https://blog.coeo.com/hs-fs/hubfs/L22-03-02_3-LoadTheStreamingDataframe.png?width=1002&name=L22-03-02_3-LoadTheStreamingDataframe.png)     22. Notice the differences between the previous code and this code: 1. **readStream** rather than **read** converts the static read to a streaming read 2. **.option("maxFilesPerTrigger", 1)** treats each file as a separate batch (microbatch)   23. Run the above cmd     24. Is this a streaming dataset? how can we tell?      25. In a new cmd window called "Is this a stream" enter the following code ![L22-03-02_4-IsHhisAStreamingDataframe](https://blog.coeo.com/hs-fs/hubfs/L22-03-02_4-IsHhisAStreamingDataframe.png?width=448&name=L22-03-02_4-IsHhisAStreamingDataframe.png)     26. Run the cmd to check if the "StreamingCountsDF" is a streaming dataframe     27. So this is a streaming DataFrame but as yet we are not streaming the data, add another cmd called "Stream to memory" and add the following code ![L22-03-02_5-StreamtoMemory](https://blog.coeo.com/hs-fs/hubfs/L22-03-02_5-StreamtoMemory.png?width=881&name=L22-03-02_5-StreamtoMemory.png)     28. Run the above cmd and you will see the following ![L22-03-04-StreamingDFinit](https://blog.coeo.com/hs-fs/hubfs/L22-03-04-StreamingDFinit.png?width=462&name=L22-03-04-StreamingDFinit.png)     29. Wait for the Streaming to initialise and you will see the following ![L22-03-03-StreamingDFResults](https://blog.coeo.com/hs-fs/hubfs/L22-03-03-StreamingDFResults.png?width=796&name=L22-03-03-StreamingDFResults.png)     30. Expand the section next to "Counts" and you will see the dashboard ![L22-03-05-StreamingDFDash](https://blog.coeo.com/hs-fs/hubfs/L22-03-05-StreamingDFDash.png?width=1764&name=L22-03-05-StreamingDFDash.png)     31. Switch to the notebook in created in the previous post and run the first 3 cmd cells 1. Set some variables 2. Function to save the api data to a file 3. Test the function        32. Switch back to the notebook for this post and you will see that the dashboard has changed ![L22-03-07-StreamingDFDash2-1](https://blog.coeo.com/hs-fs/hubfs/L22-03-07-StreamingDFDash2-1.png?width=462&name=L22-03-07-StreamingDFDash2-1.png)    33. Wait 5 minutes, then repeat the 2 steps above ![L22-03-08-StreamingDFDash3](https://blog.coeo.com/hs-fs/hubfs/L22-03-08-StreamingDFDash3.png?width=1719&name=L22-03-08-StreamingDFDash3.png)    34. You will see that the data has already updated     35. Above we set the aggregation window to be 1 hour we can view the aggregated data using the following: ![L22-03-02_6-SQLQuery](https://blog.coeo.com/hs-fs/hubfs/L22-03-02_6-SQLQuery.png?width=654&name=L22-03-02_6-SQLQuery.png)     36. Which shows the following results: ![L22-03-09-StreamingDFAggregate](https://blog.coeo.com/hs-fs/hubfs/L22-03-09-StreamingDFAggregate.png?width=1792&name=L22-03-09-StreamingDFAggregate.png)     37. Note that the time is in 1 hour interval, here we are using the window.end where the event falls within the window. In this post we have: - Created a static dataset - Created an aggregation on the dataset - Converted the dataset from static to streamed. In the next post we will further explore structured streaming and additional options that are available. [![Get more Databricks advice](https://no-cache.hubspot.com/cta/default/3356718/6b83727a-9631-4d72-a88d-eb25b03c82a6.png)](https://cta-redirect.hubspot.com/cta/redirect/3356718/6b83727a-9631-4d72-a88d-eb25b03c82a6) ### Subscribe to Email Updates ## Related posts --- ### [Three ways to profile data with Azure Databricks](https://blog.coeo.com/three-ways-to-profile-data-with-azure-databricks) ### [Azure Data Factory & XML: Loading unsupported file types](https://blog.coeo.com/azure-data-factory-xml-loading-unsupported-file-types) ### [Databricks Structured Streaming - Part 2 (Preparing the Data)](https://blog.coeo.com/databricks-structured-streaming-part2) ### [Databricks Structured Streaming - Part 1 (Creating the Cluster)](https://blog.coeo.com/databricks-structured-streaming) ![](https://www.coeo.com/wp-content/uploads/2016/12/logo-invert.png) +44 (0)20 3051 3595 | info@coeo.com ## Contact Us By clicking submit below, you consent to allow Coeo to store and process the personal information submitted above to provide you the content requested. You may unsubscribe from these communications at any time. For more information on how to unsubscribe and our commitment to your privacy, please review our **[Privacy Policy](https://www.coeo.com/privacy/)**. ## Upcoming Events [See all events](https://www.coeo.com/events/) #### NOW Building, Thames Valley Park Drive, Reading, RG6 1RB [![](https://www.coeo.com/wp-content/themes/coeo/images/social-glass.png)](https://www.glassdoor.co.uk/Overview/Working-at-Coeo-EI_IE959052.11,15.htm)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-in.png)](https://www.linkedin.com/company/coeo-ltd)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-twitter.png)](https://twitter.com/CoeoLtd)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-fb.png)](https://www.facebook.com/coeoltd/) ![](https://www.coeo.com/wp-content/themes/coeo/images//menu-icon.png) ![](https://www.coeo.com/wp-content/themes/coeo/images//menu-close.png) ![](https://www.coeo.com/wp-content/uploads/2016/12/logo-invert.png) +44 (0)20 3051 3595 | [info@coeo.com](mailto:info@coeo.com) - [Solutions](https://www.coeo.com/solutions/) - [Next Steps](https://www.coeo.com/next-steps/) - [Dedicated Support](https://www.coeo.com/dedicated-support/) - [Case studies](https://www.coeo.com/case-studies/) - [Technologies](https://www.coeo.com/solutions/technologies/) - [Industries](https://www.coeo.com/industries/) - [Finance](https://www.coeo.com/industries/finance/) - [Retail](https://www.coeo.com/industries/retail/) - [Technology](https://www.coeo.com/industries/technology/) - [The Team](https://www.coeo.com/people/) - [Join Us](https://www.coeo.com/careers/) - [Graduate Programme](https://www.coeo.com/graduate-programme/) - [About Coeo](https://www.coeo.com/about-coeo/) - [The Coeo Blog](https://www.coeo.com/blog/) - [Contact us](https://www.coeo.com/contact-us/) - [Privacy Notice](https://www.coeo.com/privacy/) - [Cookie Policy](https://www.coeo.com/privacy#Cookie_Policy) - [Events](https://www.coeo.com/events) Sign up to our newsletter ![go arrow](https://www.coeo.com/wp-content/themes/coeo/images/newsletter-go.png) Back to top