Support for deprecated data types text and ntext

[aaronsteers]

Background:

The tap currently does not support the data types text and ntext. Although these are deprecated data types as noted here, there are still many environments with instances of these column types in the wild.

Our organization has some such columns (type of text) and the resulting generated catalog is omitting the column's type declaration. Since the downstream target (snowflake in our case) is expecting a data type, this causes a hard failure trying to parsing a dict with unknown key.

Proposal:

The proposed solution would handle text as varchar(max) and ntext as nvarchar(max).

Code location:

tap-mssql/src/tap_mssql/catalog.clj

Line 171 in b044ae1

"varchar" {"type" ["string"]

Alternative considered:

An alternative or additional solution would be to fallback to string data types as an else clause in the column type mapping, which would ensure that downstream targets always receive a valid catalog file with some kind of type declaration for each column, rather than having an omitted type field. Since basically all types can be represented as strings in some form, this seems like a good fallback rather than generating a catalog with missing types.

A benefit to this else condition alternative is that there are sometimes other reasons why columns cannot declare their metadata. In rare scenarios we've seen MS SQL Server return a null/missing data type for views that can/t or don't properly inspect their own metdata, for instance in this hypothetical sample scenario:

select null as col_a from table_one
union all
select 123 as col_a from table_two
union all
select '234' as col_b from table_three

The first instance in the union normally declares type but it is null so it may default to int or unknown. The third instance in the union being a string will probably succeed at implicit conversion to integer type, but again, this might not be able to be resolved - or might be resolved inconsistently - at view creation time.

Related article regarding text and ntext data types:

https://docs.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-ver15