Skip to content

Executing statements in MSSQL

Warning

Methods below read all the rows returned from DB to Spark driver memory, and then convert them to DataFrame.

Do NOT use them to read large amounts of data. Use DBReader or MSSQL.sql instead.

How to

There are 2 ways to execute some statement in MSSQL

Use MSSQL.fetch

Use this method to perform some SELECT query which returns small number or rows, like reading MSSQL config, or reading data from some reference table. Method returns Spark DataFrame.

Method accepts MSSQL.FetchOptions.

Warning

Please take into account MSSQL types.

Syntax support in MSSQL.fetch

This method supports any query syntax supported by MSSQL, like:

  • ✅︎ SELECT ... FROM ...
  • ✅︎ WITH alias AS (...) SELECT ...
  • ✅︎ SELECT func(arg1, arg2) FROM DUAL - call function
  • ❌ SET ...; SELECT ...; - multiple statements not supported

Examples for MSSQL.fetch

from onetl.connection import MSSQL

mssql = MSSQL(...)

df = mssql.fetch(
    "SELECT value FROM some.reference_table WHERE key = 'some_constant'",
    options=MSSQL.FetchOptions(queryTimeout=10),
)
mssql.close()
value = df.collect()[0][0]  # get value from first row and first column

Use MSSQL.execute

Use this method to execute DDL and DML operations. Each method call runs operation in a separated transaction, and then commits it.

Method accepts MSSQL.ExecuteOptions.

Syntax support in MSSQL.execute

This method supports any query syntax supported by MSSQL, like:

  • ✅︎ CREATE TABLE ..., CREATE VIEW ...
  • ✅︎ ALTER ...
  • ✅︎ INSERT INTO ... AS SELECT ...
  • ✅︎ DROP TABLE ..., DROP VIEW ..., TRUNCATE TABLE, and so on
  • ✅︎ EXEC procedure(arg1, arg2) ... or {call procedure(arg1, arg2)} - special syntax for calling procedure
  • ✅︎ DECLARE ... BEGIN ... END - execute PL/SQL statement
  • ✅︎ other statements not mentioned here
  • ❌ SET ...; SELECT ...; - multiple statements not supported

Examples for MSSQL.execute

from onetl.connection import MSSQL

mssql = MSSQL(...)

mssql.execute("DROP TABLE schema.table")
mssql.execute(
    """
    CREATE TABLE schema.table (
        id bigint GENERATED ALWAYS AS IDENTITY,
        key VARCHAR2(4000),
        value NUMBER
    )
    """,
    options=MSSQL.ExecuteOptions(queryTimeout=10),
)

Options

MSSQLFetchOptions

Bases: JDBCFetchOptions

fetchsize = None class-attribute instance-attribute

How many rows to fetch per round trip.

Tuning this option can influence performance of reading.

Warning

Default value depends on driver. For example, Oracle has default fetchsize=10.

query_timeout = Field(default=None, alias='queryTimeout') class-attribute instance-attribute

The number of seconds the driver will wait for a statement to execute. Zero means there is no limit.

This option depends on driver implementation, some drivers can check the timeout of each query instead of an entire JDBC batch.

parse(options) classmethod

If a parameter inherited from the ReadOptions class was passed, then it will be returned unchanged. If a Dict object was passed it will be converted to ReadOptions.

Otherwise, an exception will be raised

MSSQLExecuteOptions

Bases: JDBCExecuteOptions

fetchsize = None class-attribute instance-attribute

How many rows to fetch per round trip.

Tuning this option can influence performance of reading.

Warning

Default value depends on driver. For example, Oracle has default fetchsize=10.

query_timeout = Field(default=None, alias='queryTimeout') class-attribute instance-attribute

The number of seconds the driver will wait for a statement to execute. Zero means there is no limit.

This option depends on driver implementation, some drivers can check the timeout of each query instead of an entire JDBC batch.

parse(options) classmethod

If a parameter inherited from the ReadOptions class was passed, then it will be returned unchanged. If a Dict object was passed it will be converted to ReadOptions.

Otherwise, an exception will be raised