Repository: meltano/sdk
Pull request #2786 (2024-11-30 20:10:16)

feat(taps,targets): Support the `x-singer.decimal` JSON Schema extension
TODO:

- [x] Docs
- [x] Capability/Built-in setting

Related:

- Closes https://github.com/meltano/sdk/issues/1890

<!-- readthedocs-preview meltano-sdk start -->
----
📚 Documentation preview 📚: https://meltano-sdk--2786.org.readthedocs.build/en/2786/

<!-- readthedocs-preview meltano-sdk end -->

## Summary by Sourcery

Adds support for the `singer.decimal` JSON Schema extension. This allows numbers to be represented as strings with the `singer.decimal` format instead of as numbers. A new `use_singer_decimal` option is added to the `SQLToJSONSchema` class to control whether numbers are represented as `string` with the `singer.decimal` format or as `number`.

New Features:
- Adds support for the `singer.decimal` JSON Schema extension, allowing numbers to be represented as strings with the `singer.decimal` format instead of as numbers.

Enhancements:
- Adds a `use_singer_decimal` option to the `SQLToJSONSchema` class to control whether numbers are represented as `string` with the `singer.decimal` format or as `number`.

Tests:
- Adds a test case for converting numeric types to `singer.decimal` format.
- Adds a test case for converting `singer.decimal` types to SQLAlchemy DECIMAL types.

codecov[bot]: ## [Codecov](https://app.codecov.io/gh/meltano/sdk/pull/2786?dropdown=coverage&src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano) Report
Attention: Patch coverage is `81.81818%` with `2 lines` in your changes missing coverage. Please review.
> Project coverage is 90.89%. Comparing base [(`de53f85`)](https://app.codecov.io/gh/meltano/sdk/commit/de53f859d5761b3ea732af4f590bd3d90a1a0868?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano) to head [(`ff67a40`)](https://app.codecov.io/gh/meltano/sdk/commit/ff67a40acfb600bafae2355ed28f23ac30a62487?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano).

| [Files with missing lines](https://app.codecov.io/gh/meltano/sdk/pull/2786?dropdown=coverage&src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano) | Patch % | Lines |
|---|---|---|
| [singer\_sdk/connectors/sql.py](https://app.codecov.io/gh/meltano/sdk/pull/2786?src=pr&el=tree&filepath=singer_sdk%2Fconnectors%2Fsql.py&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano#diff-c2luZ2VyX3Nkay9jb25uZWN0b3JzL3NxbC5weQ==) | 77.77% | [1 Missing and 1 partial :warning: ](https://app.codecov.io/gh/meltano/sdk/pull/2786?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano) |

<details><summary>Additional details and impacted files</summary>


```diff
@@                                   Coverage Diff                                   @@
##           edgarrmondragon/refactor/jsonschematosql-fromconfig    #2786      +/-   ##
=======================================================================================
- Coverage                                                91.03%   90.89%   -0.15%     
=======================================================================================
  Files                                                       62       62              
  Lines                                                     5174     5182       +8     
  Branches                                                   666      667       +1     
=======================================================================================
  Hits                                                      4710     4710              
- Misses                                                     326      333       +7     
- Partials                                                   138      139       +1     
```

</details>

[:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/meltano/sdk/pull/2786?dropdown=coverage&src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano).   
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano).

codspeed-hq[bot]: ## [CodSpeed Performance Report](https://codspeed.io/meltano/sdk/branches/edgarrmondragon%2Ffeat%2Fsinger-decimal)

### Merging #2786 will **not alter performance**

<sub>Comparing <code>edgarrmondragon/feat/singer-decimal</code> (ff67a40) with <code>edgarrmondragon/refactor/jsonschematosql-fromconfig</code> (de53f85)</sub>



### Summary

`✅ 6` untouched benchmarks  




s7clarke10: That's awesome, thanks once again @edgarrmondragon 🙌
edgarrmondragon: @sourcery-ai review

Patch:
diff --git a/docs/guides/sql-tap.md b/docs/guides/sql-tap.md
index 68b171f8..906d9d6c 100644
--- a/docs/guides/sql-tap.md
+++ b/docs/guides/sql-tap.md
@@ -80,3 +80,10 @@ class ConfiguredSQLToJSONSchema(SQLToJSONSchema):
 ```
 
 Then, you can use your custom type mapping in your connector as in the previous example.
+
+
+### SQL tap support for Singer Decimal string format
+
+Starting from version `0.45.0`, the Meltano Singer SDK supports the `x-singer.decimal` format for strings. You can configure the tap to use it with the `use_singer_decimal` setting. SQL Targets that support the `x-singer.decimal` format will create an appropriate numeric column in the target database.
+
+Read more about target support for `x-singer.decimal` in the [SQL target guide](./sql-target.md#sql-target-support-for-singer-decimal-string-format).
diff --git a/docs/guides/sql-target.md b/docs/guides/sql-target.md
index c2af5c01..17f5edaa 100644
--- a/docs/guides/sql-target.md
+++ b/docs/guides/sql-target.md
@@ -109,3 +109,9 @@ plugins:
 }
 ```
 ````
+
+### SQL target support for Singer Decimal string format
+
+Starting from version `0.45.0`, the Meltano Singer SDK supports the `x-singer.decimal` format for strings. If the source tap is configured to use this format, the SDK will automatically convert the string to a `DECIMAL` type in the target database.
+
+Read more about target support for `x-singer.decimal` in the [SQL tap guide](./sql-tap.md#sql-tap-support-for-singer-decimal-string-format).
diff --git a/singer_sdk/connectors/sql.py b/singer_sdk/connectors/sql.py
index a37a6a70..ba0d3246 100644
--- a/singer_sdk/connectors/sql.py
+++ b/singer_sdk/connectors/sql.py
@@ -126,10 +126,21 @@ class SQLToJSONSchema:
     .. versionchanged:: 0.43.0
        Added the :meth:`singer_sdk.connectors.sql.SQLToJSONSchema.from_config` class
        method.
+    .. versionchanged:: 0.45.0
+       Added support for the `use_singer_decimal` option.
     """
 
+    def __init__(self, *, use_singer_decimal: bool = False) -> None:
+        """Initialize the SQL to JSON Schema converter.
+
+        Args:
+            use_singer_decimal: Whether to represent numbers as `string` with
+                the `x-singer.decimal` format instead of as `number`.
+        """
+        self.use_singer_decimal = use_singer_decimal
+
     @classmethod
-    def from_config(cls: type[SQLToJSONSchema], config: dict) -> SQLToJSONSchema:  # noqa: ARG003
+    def from_config(cls: type[SQLToJSONSchema], config: dict) -> SQLToJSONSchema:
         """Create a new instance from a configuration dictionary.
 
         Override this to instantiate this converter with values from the tap's
@@ -148,11 +159,13 @@ class SQLToJSONSchema:
 
         Args:
             config: The configuration dictionary.
+            use_singer_decimal: Whether to represent numbers as `string` with
+                the `x-singer.decimal` format instead of as `number`.
 
         Returns:
             A new instance of the class.
         """
-        return cls()
+        return cls(use_singer_decimal=config.get("use_singer_decimal", False))
 
     @functools.singledispatchmethod
     def to_jsonschema(self, column_type: sa.types.TypeEngine) -> dict:  # noqa: ARG002, D102, PLR6301
@@ -195,12 +208,14 @@ class SQLToJSONSchema:
         return th.IntegerType.type_dict  # type: ignore[no-any-return]
 
     @to_jsonschema.register
-    def float_to_jsonschema(self, column_type: sa.types.Numeric) -> dict:  # noqa: ARG002, PLR6301
+    def float_to_jsonschema(self, column_type: sa.types.Numeric) -> dict:  # noqa: ARG002
         """Return a JSON Schema representation of a generic number type.
 
         Args:
             column_type (:column_type:`Numeric`): The column type.
         """
+        if self.use_singer_decimal:
+            return th.SingerDecimalType.type_dict  # type: ignore[no-any-return]
         return th.NumberType.type_dict  # type: ignore[no-any-return]
 
     @to_jsonschema.register
@@ -278,6 +293,7 @@ class JSONSchemaToSQL:
             "hostname": lambda _: sa.types.VARCHAR(253),  # RFC 1035
             "ipv4": lambda _: sa.types.VARCHAR(15),
             "ipv6": lambda _: sa.types.VARCHAR(45),
+            "x-singer.decimal": self._handle_singer_decimal,
         }
 
         self._sql_datatype_mapping: dict[str, JSONtoSQLHandler] = {}
@@ -331,6 +347,17 @@ class JSONSchemaToSQL:
             return handler()  # type: ignore[no-any-return]
         return handler(schema)
 
+    def _handle_singer_decimal(self, schema: dict) -> sa.types.TypeEngine:  # noqa: PLR6301
+        """Handle a x-singer.decimal format.
+
+        Args:
+            schema: The JSON Schema object.
+
+        Returns:
+            The appropriate SQLAlchemy type.
+        """
+        return sa.types.DECIMAL(schema.get("precision"), schema.get("scale"))
+
     @property
     def fallback_type(self) -> type[sa.types.TypeEngine]:
         """Return the fallback type.
diff --git a/singer_sdk/helpers/capabilities.py b/singer_sdk/helpers/capabilities.py
index 450df6c6..c1a2ea94 100644
--- a/singer_sdk/helpers/capabilities.py
+++ b/singer_sdk/helpers/capabilities.py
@@ -136,6 +136,18 @@ BATCH_CONFIG = PropertiesList(
         ),
     ),
 ).to_dict()
+SQL_TAP_USE_SINGER_DECIMAL = PropertiesList(
+    Property(
+        "use_singer_decimal",
+        BooleanType(),
+        title="Use Singer Decimal",
+        description=(
+            "Whether to use use strings with `x-singer.decimal` format for "
+            "decimals in the discovered schema. "
+            "This is useful to avoid precision loss when working with large numbers."
+        ),
+    ),
+).to_dict()
 TARGET_SCHEMA_CONFIG = PropertiesList(
     Property(
         "default_target_schema",
diff --git a/singer_sdk/tap_base.py b/singer_sdk/tap_base.py
index b6777bd5..b572ad85 100644
--- a/singer_sdk/tap_base.py
+++ b/singer_sdk/tap_base.py
@@ -24,6 +24,7 @@ from singer_sdk.helpers._state import write_stream_state
 from singer_sdk.helpers._util import dump_json, read_json_file
 from singer_sdk.helpers.capabilities import (
     BATCH_CONFIG,
+    SQL_TAP_USE_SINGER_DECIMAL,
     CapabilitiesEnum,
     PluginCapabilities,
     TapCapabilities,
@@ -675,6 +676,16 @@ class SQLTap(Tap):
         self._catalog_dict: dict | None = None
         super().__init__(*args, **kwargs)
 
+    @classmethod
+    def append_builtin_config(cls: type[SQLTap], config_jsonschema: dict) -> None:
+        """Appends built-in config to `config_jsonschema` if not already set.
+
+        Args:
+            config_jsonschema: [description]
+        """
+        merge_missing_config_jsonschema(SQL_TAP_USE_SINGER_DECIMAL, config_jsonschema)
+        super().append_builtin_config(config_jsonschema)
+
     @property
     def tap_connector(self) -> SQLConnector:
         """The connector object.
diff --git a/singer_sdk/typing.py b/singer_sdk/typing.py
index d3c62bb0..2a3a1472 100644
--- a/singer_sdk/typing.py
+++ b/singer_sdk/typing.py
@@ -448,6 +448,12 @@ class RegexType(StringType):
     string_format = "regex"
 
 
+class SingerDecimalType(StringType):
+    """Decimal type."""
+
+    string_format = "singer.decimal"
+
+
 class BooleanType(JSONTypeHelper[bool]):
     """Boolean type.
 


Test patch:
diff --git a/tests/core/test_connector_sql.py b/tests/core/test_connector_sql.py
index b32bae20..0c7fa51a 100644
--- a/tests/core/test_connector_sql.py
+++ b/tests/core/test_connector_sql.py
@@ -510,6 +510,14 @@ def test_custom_type_to_jsonschema():
     assert m.to_jsonschema(sa.types.BOOLEAN()) == {"type": ["boolean"]}
 
 
+def test_numeric_to_singer_decimal():
+    converter = SQLToJSONSchema(use_singer_decimal=True)
+    assert converter.to_jsonschema(sa.types.NUMERIC()) == {
+        "type": ["string"],
+        "format": "singer.decimal",
+    }
+
+
 class TestJSONSchemaToSQL:  # noqa: PLR0904
     @pytest.fixture
     def json_schema_to_sql(self) -> JSONSchemaToSQL:
@@ -682,7 +690,7 @@ class TestJSONSchemaToSQL:  # noqa: PLR0904
         assert isinstance(result, sa.types.VARCHAR)
 
     def test_custom_fallback(self):
-        json_schema_to_sql = JSONSchemaToSQL()
+        json_schema_to_sql = JSONSchemaToSQL(max_varchar_length=None)
         json_schema_to_sql.fallback_type = sa.types.CHAR
         jsonschema_type = {"cannot": "compute"}
         result = json_schema_to_sql.to_sql_type(jsonschema_type)
@@ -696,7 +704,7 @@ class TestJSONSchemaToSQL:  # noqa: PLR0904
 
                 return super().handle_raw_string(schema)
 
-        json_schema_to_sql = CustomJSONSchemaToSQL()
+        json_schema_to_sql = CustomJSONSchemaToSQL(max_varchar_length=None)
 
         vanilla = {"type": ["string"]}
         result = json_schema_to_sql.to_sql_type(vanilla)
@@ -717,6 +725,52 @@ class TestJSONSchemaToSQL:  # noqa: PLR0904
         result = json_schema_to_sql.to_sql_type(image_type)
         assert isinstance(result, sa.types.LargeBinary)
 
+    def test_singer_decimal(self):
+        json_schema_to_sql = JSONSchemaToSQL()
+        jsonschema_type = {"type": ["string"], "format": "x-singer.decimal"}
+        result = json_schema_to_sql.to_sql_type(jsonschema_type)
+        assert isinstance(result, sa.types.DECIMAL)
+
+    def test_singer_decimal_with_precision_scale(self):
+        json_schema_to_sql = JSONSchemaToSQL()
+        precision, scale = 12, 3
+        jsonschema_type = {
+            "type": ["string"],
+            "format": "x-singer.decimal",
+            "precision": precision,
+            "scale": scale,
+        }
+        result = json_schema_to_sql.to_sql_type(jsonschema_type)
+        assert isinstance(result, sa.types.DECIMAL)
+        assert result.precision == precision
+        assert result.scale == scale
+
+    def test_handle_singer_decimal_missing_precision(self):
+        json_schema_to_sql = JSONSchemaToSQL(max_varchar_length=None)
+        schema = {
+            "type": ["string"],
+            "format": "x-singer.decimal",
+            # 'precision' is missing
+            "scale": 2,
+        }
+        result = json_schema_to_sql.to_sql_type(schema)
+        assert isinstance(result, sa.types.DECIMAL)
+        assert result.precision is None
+        assert result.scale == 2
+
+    def test_handle_singer_decimal_missing_scale(self):
+        json_schema_to_sql = JSONSchemaToSQL(max_varchar_length=None)
+        schema = {
+            "type": ["string"],
+            "format": "x-singer.decimal",
+            "precision": 10,
+            # 'scale' is missing
+        }
+        result = json_schema_to_sql.to_sql_type(schema)
+        assert isinstance(result, sa.types.DECIMAL)
+        assert result.precision == 10
+        assert result.scale is None
+
     def test_annotation_sql_datatype(self):
         json_schema_to_sql = JSONSchemaToSQL()
         json_schema_to_sql.register_sql_datatype_handler("json", sa.types.JSON)