Columns of type datetimeoffset are formatted strangely

[edgarrmondragon]

Reporting here in lieu of a user of this tap.

It seems this tap formats these fields in a strange way, e.g. b096207679000000d5aa0000000007e0.

Links:

• https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql?view=sql-server-ver16
• feat: Support for datetimeoffset data type #31

[BuzzCutNorman]

@edgarrmondragon Thanks for raising this issue. I am able to replicate the issue when using the driver_type: pymssql. The driver type of pyodbc does not have the issue with datetimeoffset fields.

There is an open issue at pymssql which leads to a couple of FAQs explaining how FreeTDS and pymssql interact when processing date, time, and datetimes.

• DATETIMEOFFSET returns binary object instead of a datetime object pymssql/pymssql#649
• http://www.pymssql.org/faq.html#pymssql-does-not-unserialize-date-and-time-columns-to-datetime-date-and-datetime-time-instances
• http://www.pymssql.org/en/stable/freetds_and_dates.html

[wesseljt]

I'm having this issue as well. I switched to pyodbc but when I'm connecting to a sqlserver cluster pyodbc doesn't seem to work.

[vmesel]

@wesseljt what is the error you are getting?

[wesseljt]

@vmesel ill try to recreate it again and let you know.

Thanks!

[vmesel]

Just tested it locally, and it works perfectly. You will just need to set the SQLAlchemy driver.

meltano config tap-mssql set sqlalchemy_url_query.driver "ODBC Driver 18 for SQL Server"
meltano config tap-mssql set sqlalchemy_eng_params.fast_executemany "True"

[wesseljt]

At this point I'm not even sure what I did to create the initial error - however I was use the 17 ODBC Driver

[wesseljt]

Ok @vmesel and @BuzzCutNorman I have come full circle here. I was ONLY getting this error with pymssql as well and for unrelated reasons I am having issues connecting to a specific DB using pyodbc. Here are logs from my pymssql issue if thats helpful:

2023-09-27 11:00:02,505 | ERROR    | sqlalchemy.pool.impl.QueuePool | Exception during reset or similar
Traceback (most recent call last):
  File "/Users/john/Documents/Code/loader/elt/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 763, in _finalize_fairy
    fairy._reset(pool, transaction_was_reset)
  File "/Users/john/Documents/Code/loader/elt/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1038, in _reset
    pool._dialect.do_rollback(self)
  File "/Users/john/Documents/Code/loader/elt/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/dialects/mssql/base.py", line 2845, in do_rollback
    super(MSDialect, self).do_rollback(dbapi_connection)
  File "/Users/john/Documents/Code/loader/elt/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 683, in do_rollback
    dbapi_connection.rollback()
  File "src/pymssql/_pymssql.pyx", line 316, in pymssql._pymssql.Connection.rollback
  File "src/pymssql/_pymssql.pyx", line 300, in pymssql._pymssql.Connection.rollback
  File "src/pymssql/_mssql.pyx", line 1056, in pymssql._mssql.MSSQLConnection.execute_non_query
  File "src/pymssql/_mssql.pyx", line 1077, in pymssql._mssql.MSSQLConnection.execute_non_query
  File "src/pymssql/_mssql.pyx", line 1251, in pymssql._mssql.MSSQLConnection.format_and_run_query
  File "src/pymssql/_mssql.pyx", line 1789, in pymssql._mssql.check_cancel_and_raise
  File "src/pymssql/_mssql.pyx", line 1835, in pymssql._mssql.raise_MSSQLDatabaseException
pymssql._mssql.MSSQLDatabaseException: (20004, b'DB-Lib error message 20004, severity 9:\nRead from the server failed\nNet-Lib error during Connection reset by peer (54)\n')
2023-09-27 11:00:02,512 | INFO     | singer_sdk.metrics   | METRIC: {"type": "timer", "metric": "sync_duration", "value": 5.569470405578613, "tags": {"stream": "dbo-table5", "context": {}, "status": "succeeded"}}
2023-09-27 11:00:02,512 | INFO     | singer_sdk.metrics   | METRIC: {"type": "counter", "metric": "record_count", "value": 0, "tags": {"stream": "dbo-table5", "context": {}}}
2023-09-27 11:00:02,513 | INFO     | tap-mssql            | Skipping deselected stream 'dbo-table1'.
2023-09-27 11:00:02,513 | INFO     | tap-mssql            | Skipping deselected stream 'dbo-table2'.
2023-09-27 11:00:02,518 | INFO     | tap-mssql            | Beginning full_table sync of 'dbo-table3'...
Traceback (most recent call last):
  File "/Users/john/Documents/Code/loader/elt/.meltano/extractors/tap-mssql/venv/bin/tap-mssql", line 8, in <module>
    sys.exit(Tapmssql.cli())
  File "/Users/john/Documents/Code/loader/elt/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
  File "/Users/john/Documents/Code/loader/elt/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
  File "/Users/john/Documents/Code/loader/elt/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/john/Documents/Code/loader/elt/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
  File "/Users/john/Documents/Code/loader/elt/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/singer_sdk/tap_base.py", line 498, in invoke
    tap.sync_all()
  File "/Users/john/Documents/Code/loader/elt/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/singer_sdk/tap_base.py", line 456, in sync_all
    stream.sync()
  File "/Users/john/Documents/Code/loader/elt/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/singer_sdk/streams/core.py", line 1161, in sync
    signpost = self.get_replication_key_signpost(context)
  File "/Users/john/Documents/Code/loader/elt/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/singer_sdk/streams/core.py", line 408, in get_replication_key_signpost
    if self.is_timestamp_replication_key:
  File "/Users/john/Documents/Code/loader/elt/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/singer_sdk/streams/core.py", line 219, in is_timestamp_replication_key
    return is_datetime_type(type_dict)
  File "/Users/john/Documents/Code/loader/elt/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/singer_sdk/helpers/_typing.py", line 137, in is_datetime_type
    raise EmptySchemaTypeError
singer_sdk.helpers._typing.EmptySchemaTypeError: Could not detect type from empty type_dict. Did you forget to define a property in the stream schema?