Learning Singer and Meltano

[Andreas.Motl]

Introduction

We recently had the pleasure to look into what the lovely people of the Singer and Meltano communities are conceiving.

• https://www.singer.io/
• https://meltano.com/

This article outlines a few struggles we encountered when diving in “bottom-up”, by just going ahead and sitting down to conceive corresponding data source and sink adapters for CrateDB.

• GitHub - crate-workbench/meltano-tap-cratedb: A Singer tap / Meltano extractor for CrateDB, built with the Meltano SDK, and based on the Meltano PostgreSQL tap.
• GitHub - crate-workbench/meltano-target-cratedb: A Singer target / Meltano loader for CrateDB, built with the Meltano SDK, and based on the Meltano PostgreSQL target.

It probably was the wrong approach, and we should have dedicated more time reading the documentation in one way or another. Still, we hope you enjoy our reports.

Singer specification

The Singer specification was started in 2016 by Stitch Data. It specified a data transfer format that would allow any number of data systems, called taps, to send data to any data destinations, called targets. Airbyte was incorporated in 2020 and created their own specification that was heavily inspired by Singer. There are differences, but the core of each specification is sending new-line delimited JSON data from STDOUT of a tap to STDIN of a target.

– Advanced Topics | Meltano Documentation

The canonical places to inspect the original Singer specification for Composable Open Source ETL are on the Singer GitHub, and the Meltano documentation pages.

• getting-started/docs/SPEC.md at master · singer-io/getting-started · GitHub
• https://hub.meltano.com/singer/spec/

Singer wire and file format

The tap_countries.singer file is perfect for demonstrating the capabilites of the wire format. It is made of SCHEMA, RECORD, and STATE messages, in this case wrapping two streams of data, called continents and countries. In the context of running streams in and out of databases, they correspond to database tables.

{"type": "SCHEMA", "stream": "continents", "schema": {"properties": {"code": {"type": ["null", "string"]}, "name": {"type": ["null", "string"]}}, "type": "object"}, "key_properties": ["code"]}
{"type": "RECORD", "stream": "continents", "record": {"code": "AF", "name": "Africa"}}
{"type": "RECORD", "stream": "continents", "record": {"code": "AN", "name": "Antarctica"}}
{"type": "RECORD", "stream": "continents", "record": {"code": "AS", "name": "Asia"}}
{"type": "RECORD", "stream": "continents", "record": {"code": "EU", "name": "Europe"}}
{"type": "RECORD", "stream": "continents", "record": {"code": "NA", "name": "North America"}}
{"type": "RECORD", "stream": "continents", "record": {"code": "OC", "name": "Oceania"}}
{"type": "RECORD", "stream": "continents", "record": {"code": "SA", "name": "South America"}}
{"type": "STATE", "value": {"bookmarks": {"continents": {}}}}
{"type": "SCHEMA", "stream": "countries", "schema": {"properties": {"code": {"type": ["string", "null"]}, "name": {"type": ["string", "null"]}, "native": {"type": ["string", "null"]}, "phone": {"type": ["string", "null"]}, "capital": {"type": ["string", "null"]}, "currency": {"type": ["string", "null"]}, "emoji": {"type": ["string", "null"]}, "continent": {"properties": {"code": {"type": ["string", "null"]}, "name": {"type": ["string", "null"]}}, "type": ["object", "null"]}, "languages": {"items": {"properties": {"code": {"type": ["string", "null"]}, "name": {"type": ["string", "null"]}}, "type": "object"}, "type": ["array", "null"]}}, "type": "object"}, "key_properties": ["code"]}
{"type": "RECORD", "stream": "countries", "record": {"code": "AD", "name": "Andorra", "native": "Andorra", "phone": "376", "continent": {"code": "EU", "name": "Europe"}, "capital": "Andorra la Vella", "currency": "EUR", "languages": [{"code": "ca", "name": "Catalan"}], "emoji": "\ud83c\udde6\ud83c\udde9"}}
{"type": "RECORD", "stream": "countries", "record": {"code": "AE", "name": "United Arab Emirates", "native": "\u062f\u0648\u0644\u0629 \u0627\u0644\u0625\u0645\u0627\u0631\u0627\u062a \u0627\u0644\u0639\u0631\u0628\u064a\u0629 \u0627\u0644\u0645\u062a\u062d\u062f\u0629", "phone": "971", "continent": {"code": "AS", "name": "Asia"}, "capital": "Abu Dhabi", "currency": "AED", "languages": [{"code": "ar", "name": "Arabic"}], "emoji": "\ud83c\udde6\ud83c\uddea"}}
{"type": "RECORD", "stream": "countries", "record": {"code": "AF", "name": "Afghanistan", "native": "\u0627\u0641\u063a\u0627\u0646\u0633\u062a\u0627\u0646", "phone": "93", "continent": {"code": "AS", "name": "Asia"}, "capital": "Kabul", "currency": "AFN", "languages": [{"code": "ps", "name": "Pashto"}, {"code": "uz", "name": "Uzbek"}, {"code": "tk", "name": "Turkmen"}], "emoji": "\ud83c\udde6\ud83c\uddeb"}}
{"type": "RECORD", "stream": "countries", "record": {"code": "AG", "name": "Antigua and Barbuda", "native": "Antigua and Barbuda", "phone": "1268", "continent": {"code": "NA", "name": "North America"}, "capital": "Saint John's", "currency": "XCD", "languages": [{"code": "en", "name": "English"}], "emoji": "\ud83c\udde6\ud83c\uddec"}}
{"type": "STATE", "value": {"bookmarks": {"continents": {}, "countries": {}}}}

In a full-qualified notation, table schema name and table name are separated by a dash - character. So, when addressing a database table called sys.summits, the corresponding stream name would be sys-summits.

Singer vs. Meltano

Meltano as a framework fills many gaps and makes Singer convenient to actually use. It is impossible to outline all details and every difference, so we will focus on the “naming things” aspects for now.

Both ecosystems use different names for the same elements. That may be confusing at first, but it is easy to learn: For the notion of data source vs. data sink, common to all pipeline systems in one way or another, Singer uses tap vs. target, while Meltano uses extractor vs. loader. Essentially, they are the same things under different names.

Ecosystem	Data source	Data sink
Singer	Tap	Target
Meltano	Extractor	Loader

In Singer jargon, you tap data from a source, and send it to a target. In Meltano jargon, you extract data from a source, and then load it into the target system.

[Andreas.Motl]

Creating a database tap

Hi there,

while making tap-cratedb work, we’ve encountered some struggles. The most probable reason is that we didn’t work much with Singer/Meltano before (as a plugin developer), and that we didn’t follow the upstream documentation closely enough, effectively boiling down to plain ignorance ;]. Still, we wanted to write down where we have been running into obstacles, to have the chance to improve the documentation.

Please find the report and corresponding thoughts below. Saying all that, after you know how things are connected, we want to emphasize the Singer ecosystem is amazing, and the Meltano layer adds the right convenience abstractions to consume it better. Thank you all who contribute to this ecosystem to make it better each day.

With kind regards,
Andreas.

Selecting streams and entities on taps

We had problems to discover the corresponding mechanisms around “stream/entity selection” as a newcomer developer. In the context of database taps, they are used to select a subset of tables and columns when tapping/extracting data.

Specifically, when developing the tap, even when using tap-postgres as a blueprint, we found it hard to connect the dots regarding entity selection rules and the select extra of Meltano fame, because they naturally don’t have any representation within the Singer SDK. This may revolve around topics discussed at Letting tap developers unselect certain streams in the default catalog · meltano/sdk · Discussion #1652 · GitHub, but also naturally, those are topics which are being discussed by the system architects, and we only have been able to discover them much later when starting off not knowing anything about those.

As newcomer developers, while having been so amazed that the SQLAlchemy-based reflection machinery thoroughly discovered and reflected all the system tables of CrateDB, all of them have been deselected, and we haven’t been able to figure out how to select them: First, following the Singer way, we figured that using a catalog would be the right way, and found ourselves on editing deeply nested JSON structures, trying to get data flowing out of the built-in sys.summits table of CrateDB.

Eventually, we discovered the entity/stream selection service added to the baseline Singer implementations on behalf of Meltano, but still it did not work: All the streams have been deselected, and you can imagine our frustration because, well, you see the light at the end of the tunnel, but there seems to be an invisible barrier grid between you and the tunnel exit.

In a tinge of despair, we already started to work around it somehow by mimicking the select: attribute into our tap implementation, hooking it into settings.select_beta, only to finally discover that we needed to add the capabilities: section to the Meltano project definition, to make the select: thing work without futher ado.

You can imagine our relief at this point to finally have discovered one of the holy grails of Meltano. So, it all boils down to us being too silly or lazy to read the documentation properly. Still, we wanted to share that story with you, and we hope you can appreciate it.

Just revisiting the documentation about Creating a Custom Extractor, also admitting we didn’t follow it close enough, we think we also got confused within learning about the difference of config: vs. settings:, and that select: is a completely different thing, not mentioned on that page at all. Maybe it is special for database taps?

Well, it is all right there:

• Plugins
• Plugin extras
• How to use: Plugin extras
• Capabilities
• Custom Plugins

However, we don’t know why, but we haven’t been able to connect the dots between those concepts sitting on top of Singer. One reason might have been that we studied the documentation wrongly, not intensively enough, and that we were trying to discover something about “Meltano Services” (apparently the underlying machinery for those plugins/extras), which we had picked up from the code base.

So, we think everything is excellently documented from a user’s perspective, and it’s only us who took the wrong “bottom up” approach.

[Andreas.Motl]

Defining schema overrides on taps

Within Singer, schema definitions are emitted by the data source components, which is natural. Within Meltano, there is a subsystem to define and resolve schema override rules, in order to adjust the data schema according to your downstream needs.

Hi again,

while working on feat: Add support for `pgvector` [NAIVE] by amotl · Pull Request #251 · MeltanoLabs/target-postgres · GitHub, we have been struggling to define schema override rules. For development purposes, to easily load example data for testing purposes, we selected tap-singer-jsonl ^[1].

Within meltano.yml, we defined a schema override rule on the tap-singer-jsonl element.

schema:
  my_table:
    value:
      type: "foo"

After running meltano invoke like:

meltano --log-level=debug invoke tap-singer-jsonl

We can see it gets propagated into the generated Singer catalog file .meltano/run/tap-singer-jsonl/tap.properties.json:

{
  "streams": [
    {
      "tap_stream_id": "array_number",
      "key_properties": [
        "id"
      ],
      "schema": {
        "properties": {
          "id": {
            "type": "integer"
          },
          "value": {
            "type": "foo"
          }
        },
        "type": "object",
        "required": [
          "id"
        ]
      },
      "metadata": {},
      "selected": true
    }
  ]
}

NB: metadata has been stripped, as it is of no interest here.

However, it looks like it does not have any influence on the output of the tap at all. The schema override rule is not being taken into consideration. Bummer.

With kind regards,
Andreas.

Please note we also needed to define the capabilities attribute in the tap-singer-jsonl section of meltano.yml.

capabilities:
  - catalog
  - discover

Otherwise, invoking meltano invoke would yield such a warning, and will not propagate the schema override rule into the tap.properties.json file at all.

[warning  ] A catalog file was found, but it will be ignored as the extractor does not advertise the `catalog` or `properties` capability

This is most probably the issue here: tap-singer-jsonl just isn’t ready for this kind of manipulation / behaviour override, effectively not implementing what it needs to fulfil the catalog capability contract?

NB: The original source code says it only provides the discover capability. Bummer.

In response to that, we have been successful to switch to tap-spreadsheets-anywhere for reading from a JSONL file, and apply a schema override rule like outlined above. Apparently, it works because this tap provides both the catalog and discover capabilities? Excellent!

---

1. Which probably was the wrong choice. ↩︎