Setting up the dbt SQL Server Adapter

Setting up the dbt SQL Server Adapter

  • data
  • 2024-05-09
  • 6 minutes to read
Table of Contents

Introduction

I am currently researching dbt , a data transformation tool that allows you to transform data in your warehouse more effectively. One of the things that is really interesting about it, is that dbt has a set of adapters that allow you to interchange between different database engines.

It is also really easy to get started with local development. There are a few hoops to jump through to get the sql server adapter working with macOS, so I thought I would document them here. Beyond the driver pre-requisites, you can pretty much follow these same steps for Windows.

Adapters?

dbt uses dedicated adapters to connect to different databases. The adapter is responsible for translating dbt’s SQL into the SQL dialect of the database you are using. dbt seems to interchange the words adapters and plugins. Adapters are built as python modules and will be discovered by dbt if installed. There are two types of adapters, trusted and community.

Trusted adapter

The trusted adapters are managed through a Trusted Adapter Program, it suggests that there is a commitment to maintaining the state of the adapter and adhering to a contractual requirement for development, documentation, user experience, and maintenance.

Community adapter

The community adapters are open-source and community maintained, what this does mean is that the adapters might not be fully functional or may have usage inconsistencies.

SQL Server adapter is a community adapter

The SQL Server adapter is a community adapter, so it may not be as complete, or maintained as well as the trusted adapters. Just downloading the quickstart project and running dbt run I have run into errors in syntax. It’s likely to be that a) the project hasn’t been designed to cater for the sql adapter or b) the adapter isn’t fully functional or probably a combination of both. So whilst I’m finding my way with dbt, I’ll be focusing on a trusted adapter and exploring how to handle the community adapters once I have a bit more knowledge.

Postgres for the win

Whilst I’m exploring dbt, I’ll be using the Postgres adapter. It’s a trusted adapter and I can have it spin up in a docker container locally in a few minutes, meaning I can get on with learning dbt without signing up for any cloud services. You can read about that here . Nevertheless, here are the issues I encountered getting the SQL Server adapter up and running on macOS and how I fixed them…

Pre-requisites

Docker and SQL Server

First things first, you will need to have SQL Server running locally. I have written about that here .

Install Microsoft ODBC driver

The dbt SQL adapter requires the Microsoft ODBC Driver 18 for SQL Server. Windows users can download it here . You can install on macOS using homebrew:

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_ACCEPT_EULA=Y brew install msodbcsql18 mssql-tools18

Install supporting drivers

From here you should be able to just run pip install dbt-sqlserver and be good to go. However, there are some issues with the supporting drivers on macOS and they need to be installed first. If you have already run the command, you may have seen an error like:

ImportError: dlopen(/Users/justin/python/dbt/lib/python3.12/site-packages/pyodbc.cpython-312-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle'

If you’re not interested in the details, run this command, that should do the trick:

pip install --no-binary :all: pyodbc==5.0.1 --force-reinstall

You can now move on to installing the dbt-sqlserver adapter. For those interested in the issues, read on…

Troubleshooting the supporting drivers

So a bit of a rabbit hole here, I started uninstalling unixodbc and pyodbc trying a few suggestions I found online. I did end up causing myself a secondary issue at this point:

ERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.
dbt-sqlserver 1.7.4 requires pyodbc<5.1.0,>=4.0.35, but you have pyodbc 5.1.0 which is incompatible.

By reinstalling pyodbc I installed a version not compatible with dbt-sqlserver, so pip install pyodbc==5.0.1 fixed that however, I still had the original issue. The error message was:

ImportError: dlopen(/Users/justin/python/dbt/lib/python3.12/site-packages/pyodbc.cpython-312-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle'

The suggestion is that the package is not compiled properly for ARM as suggested here and sure enough, using the --no-binary flag along with the version number fixed the issue. Therefore the command to install the supporting drivers is as follows:

pip install --no-binary :all: pyodbc==5.0.1 --force-reinstall

Install and configure dbt-sqlserver

Just as a summary, the complete set of commands is:

# installs the Microsoft ODBC driver and unixodbc
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_ACCEPT_EULA=Y brew install msodbcsql18 mssql-tools18

# installs pyodbc
pip install --no-binary :all: pyodbc==5.0.1 --force-reinstall

# installs dbt adapter
pip install dbt-sqlserver

With everything in place, I just need to set up my profile in the profiles.yml file. This is the file that dbt uses to connect to your database. Using the container I have talked through here , my profile would look like this:

quickstart-sql-local:
  target: dev
  outputs:
    dev:
      database: db1
      driver: ODBC Driver 18 for SQL Server
      server: localhost
      port: 2022
      schema: dbo
      type: sqlserver
      trust_cert: true
      user: justin
      password: 'ADB@5XRB7k$$N#!9EUDQ!o808*OPQ6AO*deQ0!P$17U2Lw%iEQ'

I can now run dbt commands against my SQL Server database. If I run dbt seed in my project directory, dbt will connect to my SQL Server database and run the seed files I have defined. Based on the jaffle-shop sandbox project , here are the tables present in the database:

The image shows Azure Data Studio connected to my local Sql Server 2022 runtime, the db1 database is expanded showing multiple tables created by the dbt seed command

Want to connect to an Azure SQL Database?

In the above example I am using the sql login that was created by the container, but if using an Azure SQL Database it’s preferable to use Microsoft Entra authentication. There are multiple methods within the scope of Entra which you can see here . To repoint my project to an Azure SQL Database, I would simply create a new profile in the profiles.yml file:

jaffle_shop_sql_azure:
  target: dev
  outputs:
    dev:
      database: jjb-dev-scratch-db
      driver: ODBC Driver 18 for SQL Server
      server: jjb-dev-sql.database.windows.net
      schema: dbo
      type: sqlserver
      authentication: CLI

I have picked the CLI authentication method, which is the easiest to set up because I just need to run az login and pass in my azure credentials, dbt will then be able to authenticate using my credentials (assuming I have access to the database). Running the dbt seed command will now create the tables in the Azure SQL Database:

The image shows Azure Data Studio connected to my Azure SQLDB, the database is expanded showing multiple tables created by the dbt seed command

To uninstall

To fully uninstall the dbt-sqlserver adapter and the related drivers you would run the following commands in this order to avoid any dependency issues:

brew uninstall mssql-tools18
brew uninstall msodbcsql18
brew uninstall unixodbc
pip uninstall dbt-sqlserver
pip uninstall pyodbc

Conclusion

Getting dbt set up with SQL Server on a silicon Mac is a bit of a pain, but once you have the correct drivers installed everything else is pretty straightforward. Because this is a community adapter and I just want to focus on learning dbt, I’m going to work with the postgres adapter for now. I’ll come back to the SQL Server adapter once I have a bit more knowledge.

References

#mtfbwy



Recent Posts

Hugo Live Reload Keeps Reloading

Hugo Live Reload Keeps Reloading

  • 2024-05-18
  • 3 minutes to read

I have run into an issue from time to time where Hugo's live reload feature keeps reloading the page. This is how I fixed it.

Read More
First Steps with Mermaid in Vscode

First Steps with Mermaid in Vscode

  • 2024-05-17
  • 7 minutes to read

My blog theme supports mermaid, a visual diagram syntax, so I wanted to try it out in vscode.

Read More
T-SQL Tuesday #174 My favourite job interview question

T-SQL Tuesday #174 My favourite job interview question

  • 2024-05-14
  • 4 minutes to read

This is my 12th contribution to TSQL Tuesday, the data community blog party. Have a read, and why not join in next time!

Read More