Setting up a Dataflow
Let's see how to set up a simple Dataflow.
Introduction
When you are working directly on a database server, whether entering SQL queries or performing data manipulations, there is a really good chance you are using an SQL editor that provides a run button. Of course, this is the case with Azure Data Studio, SQL Server Management Studio, or any of the non-Microsoft SQL editing programs including Toad, MySQL Workbench, or Oracle SQL Developer.
This is fine if your task at hand is fully SQL-centric. However, oftentimes we need to go beyond the SQL language constructs and leverage capabilities better suited to other languages, computer applications, or existing system utilities. A common example of this would be a database backup (using the SQL BACKUP command) that needs to be copied over the network to another computer (using perhaps Windows’ XCOPY command).
Leaning on what you learned in the previous chapter, we can apply this BACKUP and COPY to the car_crash database with a Jupyter Notebook, using the PowerShell Kernel, and the following script:
Invoke-Sqlcmd -Query "BACKUP DATABASE car_crash TO DISK = 'C:\temp\car_crash.bak'" -\ServerInstance "localhost"Copy-Item "C:\temp\car_crash.bak" -Destination "\\network_server\db backup"
This approach issues both a SQL command (BACKUP) and a PowerShell cmdlet (Copy-Item). Since this script is based on the Notebook’s PowerShell Kernel, the SQL command must be wrapped in PowerShell’s Invoke-Sqlcmd cmdlet. This example essentially fuses together two languages, using ...