I m struggling a bit to get a mongodb gt postgres EL working Meltano #announcements

I'm struggling a bit to get a mongodb -> postgr...

nate_atkins

01/07/2021, 5:59 PM

I'm struggling a bit to get a mongodb -> postgres EL working and could really use some tips from the community. If I run just the tap-mongodb portion with

meltano invoke tap-mongodb > output/b.jsonl

I can see that the tap generates a schema of type object which makes sense.

Copy code

{
    "type": "STATE",
    "value": {
        "bookmarks": {"junk-stuff": {"last_replication_method": "FULL_TABLE"}},
        "currently_syncing": "junk-stuff",
    },
}
{
    "type": "SCHEMA",
    "stream": "stuff",
    "schema": {"type": "object"},
    "key_properties": ["_id"],
}
{
    "type": "STATE",
    "value": {
        "bookmarks": {
            "junk-stuff": {
                "last_replication_method": "FULL_TABLE",
                "version": 1610041761516,
            }
        },
        "currently_syncing": "junk-stuff",
    },
}
{"type": "ACTIVATE_VERSION", "stream": "stuff", "version": 1610041761516}
{
    "type": "RECORD",
    "stream": "stuff",
    "record": {
        "_id": "5ff64e1191e2891d8b6ba683",
        "id": 4711,
        "tasks": [
            {"created_on": "2021-01-06T16:56:01.058005", "value": 657},
            {"created_on": "2021-01-06T16:56:01.058005", "value": 353},
            {"created_on": "2021-01-06T16:56:01.058005", "value": 839},
            {"created_on": "2021-01-06T16:56:01.058005", "value": 214},
            {"created_on": "2021-01-06T16:56:01.058005", "value": 905},
            {"created_on": "2021-01-06T16:56:01.058005", "value": 852},
            {"created_on": "2021-01-06T16:56:01.058005", "value": 47},
            {"created_on": "2021-01-06T16:56:01.058005", "value": 858},
        ],
        "updated_on": "2021-01-06T16:57:39.025265",
    },
    "version": 1610041761516,
    "time_extracted": "2021-01-07T17:49:21.517186Z",
}
{"type": "ACTIVATE_VERSION", "stream": "stuff", "version": 1610041762219}
{
    "type": "STATE",
    "value": {
        "bookmarks": {
            "junk-stuff": {
                "last_replication_method": "FULL_TABLE",
                "version": 1610041762219,
                "initial_full_table_complete": true,
            }
        },
        "currently_syncing": null,
    },
}

When I run the full EL

meltano elt tap-mongodb target-postgres

I get the following error. ```meltano | Running extract & load... meltano | No state was found, complete import. tap-mongodb | INFO Connected to MongoDB host: localhost, version: 4.2.11 tap-mongodb | INFO Starting full table sync for junk-stuff tap-mongodb | INFO Querying junk-stuff with: tap-mongodb | Find Parameters: {'$lte': ObjectId('5ff64e7891e2891d8b6c5551')} target-postgres | Traceback (most recent call last): target-postgres | File "/home/nate/projects/wemlo-data-engineering/experiment/ELT-meltano/demo-project/.meltano/loaders/target-postgres/venv/bin/target-postgres", line 10, in <module> target-postgres | sys.exit(main()) target-postgres | File "/home/nate/projects/wemlo-data-engineering/experiment/ELT-meltano/demo-project/.meltano/loaders/target-postgres/venv/lib/python3.7/site-packages/target_postgres/__init__.py", line 188, in main target-postgres | state = persist_lines(config, input) target-postgres | File "/home/nate/projects/wemlo-data-engineering/experiment/ELT-meltano/demo-project/.meltano/loaders/target-postgres/venv/lib/python3.7/site-packages/target_postgres/__init__.py", line 150, in persist_lines target-postgres | stream_to_sync[stream] = DbSync(config, o) target-postgres | File "/home/nate/projects/wemlo-data-engineering/experiment/ELT-meltano/demo-project/.meltano/loaders/target-postgres/venv/lib/python3.7/site-packages/target_postgres/db_sync.py", line 121, in init target-postgres | self.flatten_schema = flatten_schema(stream_schema_message['schema']) target-postgres | File "/home/nate/projects/wemlo-data-engineering/experiment/ELT-meltano/demo-project/.meltano/loaders/target-postgres/venv/lib/python3.7/site-packages/target_postgre…

douwe_maan

01/07/2021, 6:08 PM

@nate_atkins The problem appears to be that

target-postgres

requires the

SCHEMA

message to define all of the record's properties so that it can create the appropriate tables, which

tap-mongodb

isn't doing (for understandable reasons). To work around this, you can explicitly specify the schema for each stream in

meltano.yml

using https://meltano.com/docs/plugins.html#schema-extra

douwe_maan

01/07/2021, 6:11 PM

In your case that would look something like:

Copy code

schema:
  stuff:
    _id:
      type: string
    id:
      type: integer
    tasks:
      type: array
      items:
        type: object
        properties:
          created_on:
            type: string
            format: date-time
          value:
            type: integer
    updated_on:
      type: string
      format: date-time

douwe_maan

01/07/2021, 6:11 PM

Then if you run

meltano invoke tap-mongodb

, you should see a more complete

SCHEMA

message.

douwe_maan

01/07/2021, 6:12 PM

You'll want to do this for every stream, of course.

nate_atkins

01/07/2021, 6:12 PM

Thanks, I'll give it a try.

nate_atkins

01/07/2021, 6:37 PM

No luck. Did I get it in the right place? meltano.yml

Copy code

version: 1
send_anonymous_usage_stats: true
project_id: 02ab68b4-8b76-48e2-9c30-234e8aec9d30
plugins:
  extractors:
  - name: tap-mongodb
    variant: singer-io
    pip_url: tap-mongodb
    metadata:
      '*':
        replication-method: FULL_TABLE
    schema:
      stuff:
        _id:
          type: string
        id:
          type: integer
        tasks:
          type: array
          items:
            type: object
            properties:
              created_on:
                type: string
                format: date-time
              value:
                type: integer
        updated_on:
          type: string
          format: date-time
  loaders:
  - name: target-jsonl
    variant: andyh1203
    pip_url: target-jsonl
  - name: target-postgres
    variant: meltano
    pip_url: git+<https://github.com/meltano/target-postgres.git>

tap.properties.json

Copy code

{
  "streams": [
    {
      "table_name": "stuff",
      "stream": "stuff",
      "metadata": [
        {
          "breadcrumb": [],
          "metadata": {
            "table-key-properties": [
              "_id"
            ],
            "database-name": "junk",
            "row-count": 4995,
            "is-view": false,
            "valid-replication-keys": [
              "_id"
            ],
            "selected": true,
            "replication-method": "FULL_TABLE"
          }
        }
      ],
      "tap_stream_id": "junk-stuff",
      "schema": {
        "type": "object"
      },
      "selected": true,
      "replication_method": "FULL_TABLE"
    }
  ]
}

tap output

Copy code

{
  "type": "SCHEMA",
  "stream": "stuff",
  "schema": {
    "type": "object"
  },
  "key_properties": [
    "_id"
  ]
}

douwe_maan

01/07/2021, 6:38 PM

@nate_atkins Ah, try using

junk-stuff

meltano.yml

instead of

stuff

— the identifier needs to match the stream's

tap_stream_id

, not its

stream

property!

douwe_maan

01/07/2021, 6:39 PM

(There's really no great reason why those are different in the first place, but so be it!)

nate_atkins

01/07/2021, 6:39 PM

I tried that before and just now. Still no luck.

nate_atkins

01/07/2021, 6:40 PM

doooo! got it.

junk-stuff

not

junk_stuff

douwe_maan

01/07/2021, 6:40 PM

😄 Does it work now?

nate_atkins

01/07/2021, 6:42 PM

need to sort out my docker-compose file to get the right postgres database running.

nate_atkins

01/07/2021, 6:57 PM

👍 thanks a bunch. I knew it had something to do with that I just couldn't see where to make the change. Now to work out a schema that works across 100+ collections. I already have all the DBT to unpack nested mess. I might do some refactoring to handle some of it here and some there. Cool, I can see where that puts it into the tap.properties.json file. One think I've noticed there is that it actually creates what appears to be duplicate entries for each stream. When the EL runs it generates to rows of data as a result. I can go in and delete the row out of the catalog, but something seem fishy there.

douwe_maan

01/07/2021, 7:01 PM

One think I've noticed there is that it actually creates what appears to be duplicate entries for each stream.

Hmm, that sounds like a bug! Can you please file an issue with some more details/examples?

nate_atkins

01/07/2021, 7:03 PM

This repository? https://github.com/singer-io/tap-mongodb

nate_atkins

01/07/2021, 7:17 PM

I got it in the meltano repository where your other mongodb issues are.

douwe_maan

01/07/2021, 8:51 PM

@nate_atkins Ah, does tap-mongodb always create two entries for each stream in the catalog and the actual sync output? Then it's an issue for https://github.com/singer-io/tap-mongodb. If it only does so when you set

schema

meltano.yml

, it's a Meltano issue

douwe_maan

01/07/2021, 11:31 PM

I've responded in the issue: https://gitlab.com/meltano/meltano/-/issues/2499#note_480693050

douwe_maan

01/12/2021, 11:34 PM

@nate_atkins I've also created a new issue about tap-mongodb requiring the

schema

to be specified explicitly: https://gitlab.com/meltano/meltano/-/issues/2517

13 Views

Open in Slack

Previous Next