Recent content on Ed Elliott's Agile SQL Club
Running tSQLt unit tests is great from Visual Studio but my development workflow isn’t just write tests, run tests, fix tests, run tests anymore, it is 2026 and I am a big fan of using claude code to help me write more efficiently than I used to be with just a keyboard and mouse. I was using claude and asked it to write tests, there were some errors and so I had to paste the error over to…
I have had a number of requests for me to update the tSqlt Test Adapter over the years so it would work with more recent versions of Visual Studio. I finally got around to doing this and I am pleased to say that the new version works with VS 2022, 2025 and should continue to work in future releases. Between Visual Studio 2017 and 2019 the changes required were minimal but between 2017 and 2022 the…
SQL Server Management Studio (SSMS) has recently added support for Github Copilot. This is a great feature that can help with writing SQL queries and scripts sql development. However, there is a potential security risk that means that I will only be using it for local development with a database that has no externally provided data. When evaluating GitHub copilot I think there are three core…
All Spark Connect Posts I have just finished an update for the spark connect dotnet lib that contains the DeltaTable implementation so that we can now use .NET to maintain delta tables, over and above what we get out of the box by using DataFrame.Write.Format("delta") , this is an example of how to use the delta api from .NET: var deltaTable = DeltaTable.ForPath(spark, deltaPath);…
I recently encountered an interesting issue with ADF where the publish feature suddenly attempted to republish every single object, claiming they were new, despite having incrementally published changed objects for some time. We were using the publish feature where you work on a branch until you are happy, then you raise a PR to main, merge to main, and then switch back to ADF and click publish to…
All Spark Connect Posts Introduction There have been quite a few changes in the last couple of months and I just wanted to give a quick update on the current state of the project. In terms of usage I am starting to hear from people using the library and submitting pr’s and requests so although usage is pretty low (which is expected from the fact that the Microsoft supported version usage…
All Spark Connect Posts I recently published the latest version of the Spark Connect Dotnet library which includes support for the new Variant data type in Apache Spark 4.0 here . One of the new features of Spark 4.0 is the Variant data type which is a faster way of processing Json data ( see here ). Sample data For this post I used a copy of the sample data from Adobe (…
UPDATE - I have implemented delta in spark-connect-dotnet All Spark Connect Posts Code What to do? At some point we will want to do something with delta lake and so I wanted to explore the options. Before we do that there is a little explaining to do about delta lake and Spark. There are two completely separate sides to this, the first is getting Spark to read and write in delta format and the…
All Spark Connect Posts Code Goal of this post I wanted to explore what the Spark Connect API looked like from other languages, I am not a php developer - I used it a long time ago and read up on some of the modern changes but apologies if I insult any php-ers! I will say that I quite like php. Setup The instructions are from https://grpc.io/docs/languages/php/quickstart/ with an extra step. There…
All Spark Connect Posts Code Goal of this post The goal of this post is to look at creating a SparkSession and a DataFrame that will wrap the Range relation and then we will use the WithColumn function to add a column to the DataFrame and then we will use the Show function to show the DataFrame . We won’t have a builder but we are moving towards: var spark = SparkSession .Builder .Remote(…
All Spark Connect Posts Code Goal of this post So there are two goals of this post, the first is to take a look at Apache Arrow and how we can do things like show the output from DataFrame . Show , the second is to start to create objects that look more familiar to us, i.e. the DataFrame API. I want to take it in small steps, I 100% know that this sort of syntax is possible:
All Spark Connect Posts Code Goal of this post In this post we will continue looking at the gRPC API and the AnalyzePlan method which takes a plan and analyzes it. To be honest I expected this to be longer but decided just to do the AnalyzePlan method. There are a few more API’s like ReleaseExecute , InterruptAsync , and ReattachExecute that I was going to cover but changed my mind so…
All Spark Connect Posts Code Goal of this post In the first two posts, we looked at how to run some Spark code, firstly against a local Spark Connect server and then against a Databricks cluster. In this post, we will look more at the actual gRPC API itself, namely ExecutePlan, Config , and AddArtifacts / ArtifactsStatus. SparkConnectService.SparkConnectServiceClient The way we call the API is…
All Spark Connect Posts Goal of this post This post aims to show how we can create a .NET application, deploy it to Databricks, and then run a Databricks job that calls our .NET code, which uses Spark Connect to run a Spark job on the Databricks job cluster to write some data out to Azure storage. In the previous post , I showed how to use the Range command to create a Spark DataFrame and then…
All Spark Connect Posts When I wrote the spark-connect-dotnet lib I didn’t envisage that I would implement every function, instead it would be a combination of implementing the most common functionality and showing people how they can make their own gRPC calls to call the rest of the functions but what I found is that actually implementing the functions once I had figured out the shared…
All Spark Connect Posts Introductory Ramble Spark Connect In July 2022, at the Data and AI summit, Apache Spark announced “Spark Connect,” which was a way of connecting to Apache Spark using the gRPC protocol rather than using the supplied Java or Python APIs. I’ve been using Spark since 2017 and have been completely obsessed with how transformative it has been in the data…
Spark Connect References https://spark.apache.org/docs/latest/spark-connect-overview.html https://www.databricks.com/blog/2022/07/07/introducing-spark-connect-the-power-of-apache-spark-everywhere.html Reference Implementations Rust GOLang Source gRPC proto files https://github.com/apache/spark/tree/master/connector/connect/common/src/main/protobuf/spark/connect Useful Videos If this doesn’t…
The Agile SQL CLub Privacy Policy (google Made me do it) The website uses cookies to track views and where you go, if you block cookies then they will be stopped. The reason for tracking is to see which posts are popular and where a really old post is still popular and is out of date I can put a note on it to say so. I collect no information about you.
I made a mistake recently when I was creating an ADF pipeline, annoyingly I made loads of changes and then clicked the debug button, when I pressed debug the pipeline failed to start and I was presented with this little beaut of an error message: The pipeline was quite complicated and so I didn’t know exactly what was causing it so I went through the usual ADF troubleshooting steps (save all…
In my previous blog post I talked about how to read from an XML Webervice and use xpath to query the XML on the expressions side of things. You can read the XML article here ( https://the.agilesql.club/2021/02/adf-xml-objects-and-xpath-in-the-expression-language/) . Now, what if we don’t have XML but have JSON? Well well indeed, what if there was a way to query JSON documents using a query,…
When you use ADF, there are two sides to the coin. The first is the data itself that ADF does very well, from moving it from one site to another to flattening JSON documents and converting from CSV to Avro, to Parquet, to SQL is powerful. The other side of the coin is how ADF uses data as variables to manage the pipeline, and it is this side of the coin that I wish to talk about today.
This is a bit of a longer one, a look at how to do all the different joins and the exciting thing for MSSQL developers is that we get a couple of extra joins (semi and anti semi oooooooh). T-SQL SELECT * FROM chicago.safety_data one INNER JOIN chicago.safety_data two ON one.Address = two.Address; Spark SQL SELECT * FROM chicago.safety_data one INNER JOIN chicago.safety_data two ON one.Address =…
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)…
The next example is how to do a ROW_NUMBER(), my favourite window function. T-SQL SELECT * , ROW_NUMBER() OVER( ORDER BY dateTime) as RowNumber FROM chicago.safety_data Spark SQL SELECT * , ROW_NUMBER() OVER( ORDER BY dateTime) as RowNumber FROM chicago.safety_data DataFrame API (C#) var dataFrame = spark.Read().Table( "chicago.safety_data" ); var window =…
I have been playing around with the new Azure Synapse Analytics, and I realised that this is an excellent opportunity for people to move to Apache Spark. Synapse Analytics ships with .NET for Apache Spark C# support many people will surely try to convert T-SQL code or SSIS code into Apache Spark code. I thought it would be awesome if there were a set of examples of how to do something in T-SQL,…
When I first started working with Apache Spark, one of the things I struggled with was that I would have some variable or data in my code that I wanted to work on with Apache Spark. To get the data in a state that Apache Spark can process it involves putting the data into a DataFrame. How do you take some data and get it into a DataFrame? This post will cover all the ways to get data into a…
Git is hard, probably harder than it needs to be but I have been using it for about 5 years and have a workflow that works for me. I use git from a command line and have learnt how to use these ten commands. If I need to deal with a merge conflict, I use VS Code and the gitlens extension. If I need to do anything else then I probably copy out the files I want to keep, reset my local repo or delete…
Spark ML in .NET for Apache Spark Apache Spark has had a machine learning API for quite some time and this has been partially implemented in .NET for Apache Spark. In this post we will look at how we can use the Apache Spark ML API from .NET. This is the second version of this post, the first version was written before version 1 of .NET for Apache Spark and there was a vital piece of the…
The Problem When you use a UDF in .NET for Apache Spark, something like this code: class Program { static void Main( string [] args) { var spark = SparkSession.Builder().GetOrCreate(); _logging.AppendLine( "Starting Select" ); var udf = Functions.Udf< int , string >(theUdf); spark.Range( 100 ).Select(udf(Functions.Col( "id" ))).Show(); _logging.AppendLine( "Ending Select" );…
Last Updated: 2020-10-18 NOTE: What you read here was before .NET for Apache Spark 1.0 which includes everything we need to do this purely in .NET - in this post you will see an example that is no longer necessary for TF-IDF, instead view: https://the.agilesql.club/2020/12/spark-dotnet-tf-idf . Spark ML in .NET for Apache Spark Spark is awesome, .NET is awesome, machine learning (ML) is awesome,…
Databricks is fantastic, but there is a small issue with how people use it. The problem is that Databricks is all things to all people. Data scientists and data analysts use Databricks to explore their data and write cool things. ML engineers use it to get their models to execute somewhere. Meanwhile, the cool kids (data engineers obviously) use it to run their ETL code. Some use cases favour…
Every ETL pipeline is only ever as reliable as the data that the upstream system provides. It is inevitable that assumptions you make about the data you are provided will be shattered and there is absolutely nothing you can do about it. So what can we do? Do we just accept that our pipelines will break and fix them when the CEO shouts that the figures are out or even worse if no one notices and…
When we use ADF to call Databricks we can pass parameters, nice. When we finish running the Databricks notebook we often want to return something back to ADF so ADF can do something with it. Think that Databricks might create a file with 100 rows in (actually big data 1,000 rows) and we then might want to move that file or write a log entry to say that 1,000 rows have been written.
When you use delta lake there are a couple of interesting things to note based around the fact that the data is stored in parquet files which are read-only and delta lake includes the ability to delete and update data and view the state of a table at a specific point in time. Obviously read-only and updates and deletes don’t exactly sound like they work together, so how does it all work and…
It is a non-null constraint, not a non-ish-null constraint You are writing an ETL process, part of this process you need to import a semi-structured file (think CSV, JSON, XM-bleurgh-L, etc.) when you import the data into an RDBMS you get all sorts of things that make schema designers excited like unique constraints and check constraints. The problem is that the file you are importing is from…
It has been a little while but I have updated SQLCover to include a number of fixes and small features, the majority of which are improvements to the html output: For full details and to download the latest version see: https://github.com/GoEddie/SQLCover/releases/tag/0.5.0 or https://www.nuget.org/packages/GOEddie.SQLCover/0.5.0 If you get any issues please comment below or raise an issue on…
This is the final part in the four-part series into testing ETL pipelines, how exciting! Part 1 - Unit Testing https://the.agilesql.club/2019/07/how-do-we-test-etl-pipelines-part-one-unit-tests/ Part 2 - Integration Testing https://the.agilesql.club/2019/08/how-do-we-prove-our-etl-processes-are-correct-how-do-we-make-sure-upstream-changes-dont-break-our-processes-and-break-our-beautiful-data/ Part…
“[Error] [JvmBridge] java.sql.SQLException: No suitable driver” - unable to connect spark to Microsoft SQL Server. In spark when you want to connect to a database you use Read() passing in the format “jdbc” and include the options of url, driver and either dbtable or query. DataFrame dataFrame = spark.Read() .Format( "jdbc" ) .Option( "url" ,…
In the first two parts of this series ( https://the.agilesql.club/2019/07/how-do-we-test-etl-pipelines-part-one-unit-tests/ and https://the.agilesql.club/2019/08/how-do-we-prove-our-etl-processes-are-correct-how-do-we-make-sure-upstream-changes-dont-break-our-processes-and-break-our-beautiful-data/) , I talked about how to unit test your business logic and integration test your ETL infrastructure…
I finally got around to updating the tSQLt test adapter for visual studio, you can download it from: https://marketplace.visualstudio.com/items?itemName=vs-publisher-263684.GoEddietSQLt2019 or the search in visual studio extensions thingy finds it as well. For details on what this is and how it works see the original post: https://the.agilesql.club/2016/08/tsqlt-visual-studio-test-adapter/
Steps needed Getting Apache Spark running on windows involves: Installing a JRE 8 (Java 1.8/OpenJDK 8) Downloading and extracting SPARK and setting SPARK_HOME Downloading winutils.exe and setting HADOOP_HOME If using the dotnet driver also downloading the Microsoft.Spark.Worker and setting DOTNET_WORKER_DIR if you are going to use UDF’s Making sure java and %SPARK_HOME%\bin are on your path…
When you run an application using spark-dotnet, to launch the application you need to use spark-submit to start a java virtual machine which starts the spark-dotnet driver which then runs your program so that leaves us a problem, how to write our programs in visual studio and press f5 to debug? There are two approaches, one I have used for years with dotnet when I want to debug something that is…
ETL Testing Part 2 - Operational Data Testing This is the second part of a series on ETL testing, the first part explained about unit testing, and in this part, we will talk about how we can prove the correctness of the actual data, both today and in the future after every ETL run. Testing ETL processes is a multi-layered beast, we need to understand the different types of test, what they do for…
I found this question on stack overflow that went something like this: “I have a file that includes line endings in the wrong place and I need to parse the text manually into rows” ( https://stackoverflow.com/questions/57294619/read-a-textfile-of-fixed-length-with-newline-as-one-of-attribute-value-into-a-ja/57317527) . I thought it would be interesting to implement this with what we…
There was a breaking change with version 0.4.0 that changed the name of the class that is used to load the dotnet driver in Apache Spark. To fix the issue you need to use the new package name which adds an extra dotnet near the end, change: spark-submit --class org.apache.spark.deploy.DotnetRunner into: spark-submit --class org.apache.spark.deploy.dotnet.DotnetRunner What if I have this error but…
Why do we bother testing? Testing isn’t an easy thing to define, we all know we should do it, when something goes wrong in production people shout and ask where the tests were, hell even auditors like to see evidence of tests (whether or not they are good isn’t generally part of an audit) . What do we test, how and why do we even write tests? It is all well and good saying “write unit tests…
How do you read and write CSV files using the dotnet driver for Apache Spark? I have a runnable example here: https://github.com/GoEddie/dotnet-spark-examples Specifcally: https://github.com/GoEddie/dotnet-spark-examples/tree/master/examples/split-csv Let’s take a walkthrough of the demo: Console.WriteLine( "Hello Spark!" ); var spark = SparkSession .Builder() .GetOrCreate(); We start with…
Apache Spark is written in scala, scala compiles to Java and runs inside a Java virtual machine. The spark-dotnet driver runs dotnet code and calls spark functionality, so how does that work? There are two paths to run dotnet code with spark, the first is the general case which I will describe here, the second is UDF’s which I will explain in a later post as it is slightly more involved.
I really like the new dotnet driver for Spark because I think it makes spark more accesable to devs who might not know pythpn or scala. If you want to be able to build and run a dotnet application using the dotnet driver to run locally you will need: jre Spark dotnet sdk Now the JRE has some docker images and so does the dotnet sdk, but what if you want both in a single container? The simplest way…
Here are three scenarios, if you work with SQL Server either as a provider of database environments (DBA) or a consumer of database environments for your application (developer) then you will likely see yourself in one of these descriptions. If you don’t please 100% find some way to tell me (email, comment below etc.) Prod FTW’ers – There is only one place where the developers can…