Validation Store and Expectation store in MS SQL

Archive
1

Hi there,

I’m trying to set up Great Expectations so that it stores Expectation definitions and validation results in a Microsoft SQL database.
I can set up MS SQL as a datasource - that works great.
I’ve tried setting up PostgreSQL as a Expectations Store and Validations Store and that works too.

These are the stores in my great_expectations.yml:
stores:
expectations_db_store:
class_name: ExpectationsStore
store_backend:
class_name: DatabaseStoreBackend
credentials: ${mssql}
validations_db_store:
class_name: ValidationsStore
store_backend:
class_name: DatabaseStoreBackend
credentials: ${mssql}

expectations_store_name: expectations_db_store
validations_store_name: validations_db_store

This is my config_variables.yml:
mssql:
url: mssql+pyodbc://XXX:XXX@XXX.database.windows.net:1433/XXX?driver=ODBC
Driver 17 for SQL Server&charset=utf&autocommit=true

When I try to do just great_expectations store list it complains that there is no drivername. When I add drivername: mssql+pyodbc, it says __init__() got an unexpected keyword argument 'url'.

It seems to me that MS SQL credentials can’t be configured using the drivername/host/… format and can only be configured through url. However it looks like the DatabaseStoreBackend only supports the drivername/host/… format.

Is this the case or am I doing something wrong? I see you’re using SQL alchemy for both datasources and stores, so it sounds like it should work somehow.

Thanks,

Petr

1 Like
2

@pcv, Great Expectations uses SQLAlchemy to connect to all the databases. Could you please test if you can successfully use your URL with SQLAlchemy (independently of GE)? Thank you!

3

@eugene.mandel Yes, I can connect to MS SQL using SQLAlchemy with that URL. I can even connect to the database from Great Expectations if I set it up as a datasource. But it doesn’t work as a Expectations Store and Validations Store.
Thank you for looking into that!

4

IS url an argument you pass for your db credentials? I tried Oracle and I find the varchar max length would be a limitation, not sure if MS SQL support large enough varchar too?

5

@pcv Thank you for answering my question. If the URL works for you with SQLAlchemy (and in other contexts of GE), but not in the stores, I consider it a bug. Could you please file it as a GitHub issue: https://github.com/great-expectations/great_expectations/issues ? Thank you!

6

Thanks @eugene.mandel, here it is: https://github.com/great-expectations/great_expectations/issues/2073