Skip to content

mssql.md

Latest commit

 

History

History
65 lines (51 loc) · 6.13 KB

mssql.md

File metadata and controls

65 lines (51 loc) · 6.13 KB

MSSQL

Installation

User / Password Authentication:

pip install "sqlmesh[mssql]"

Microsoft Entra ID / Azure Active Directory Authentication:

pip install "sqlmesh[mssql-odbc]"

Incremental by unique key MERGE

SQLMesh executes a MERGE statement to insert rows for incremental by unique key model kinds.

By default, the MERGE statement updates all non-key columns of an existing row when a new row with the same key values is inserted. If all column values match between the two rows, those updates are unnecessary.

SQLMesh provides an optional performance optimization that skips unnecessary updates by comparing column values with the EXISTS and EXCEPT operators.

Enable the optimization by setting the mssql_merge_exists key to true in the physical_properties section of the MODEL statement.

For example:

MODEL (
    name sqlmesh_example.unique_key,
    kind INCREMENTAL_BY_UNIQUE_KEY (
        unique_key id
    ),
    cron '@daily',
    physical_properties (
        mssql_merge_exists = true
    )
);

!!! warning "Not all column types supported" The mssql_merge_exists optimization is not supported for all column types, including GEOMETRY, XML, TEXT, NTEXT, IMAGE, and most user-defined types.

Learn more in the [MSSQL `EXCEPT` statement documentation](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-ver17#arguments).

Local/Built-in Scheduler

Engine Adapter Type: mssql

Connection options

Option Description Type Required
type Engine type name - must be mssql string Y
host The hostname of the MSSQL server string Y
user The username / client id to use for authentication with the MSSQL server string N
password The password / client secret to use for authentication with the MSSQL server string N
port The port number of the MSSQL server int N
database The target database string N
charset The character set used for the connection string N
timeout The query timeout in seconds. Default: no timeout int N
login_timeout The timeout for connection and login in seconds. Default: 60 int N
appname The application name to use for the connection string N
conn_properties The list of connection properties list[string] N
autocommit Is autocommit mode enabled. Default: false bool N
driver The driver to use for the connection. Default: pymssql string N
driver_name The driver name to use for the connection (e.g., ODBC Driver 18 for SQL Server). string N
odbc_properties ODBC connection properties (e.g., authentication: ActiveDirectoryServicePrincipal). See more here. dict N