I am reading the docs on postgresql tap.. and want...
# singer-tap-development
e
I am reading the docs on postgresql tap.. and wanting to understand how to only ADD data.. so was looking into the replication method.. unsure if this is the correct area I need to read.. but the links to the sub-sections seem to be broken https://meltano.com/docs/integration.html#log-based-incremental-replication redirects to https://docs.meltano.com/#log-based-incremental-replication
a
Thanks for letting us know... @amanda.folson / @taylor fyi regarding the docs issue.
@emcp - If by just adding data you mean "append only" (and if duplicates are okay for your use case), you can override "key properties" for those streams and set them to null or an empty set. Since merge behaviors in targets is driven by the defined primary keys (aka "key properties"), you can force the target to not merge by keeping key properties undefined. Does this help at all? Am I properly understanding your use case?
Also - the above mentioned approach can work in combination with any replication method: FULL_TABLE, INCREMENTAL, and LOG_BASED. The difference is just that rather than merging the result, the target will purely append whatever records are sent over.
e
Thanks AJ, I will look into this and try to play around with the postgresql tap target in a dev environment . I am still looking where those links were supposed to go otherwise I would offer a PR
so.. I actually don't mind if a data with the same primary key overlaps and is ignored or overwrites
I guess I was concerned more for the Transform step.. since it appears to totally wipe out the prior data.. and re run the transform on the entirety of the Loads result (which is okay for me)
s
@aaronsteers How would I add the key-properties config, and is there a way to set it for the whole tap?
a
Do you mean as a user of a tap in Meltano? Meltano provides a "metadata" override capability as described here: https://docs.meltano.com/concepts/plugins#metadata-extra This would allow you to override
key-properties
per each stream name.
s
Will check it today! Thank you
a
@visch had a helpful reply there so I appended a bit more explanation based on that input (regarding wildcards in the stream name). Thanks, @visch.
s
I set key-properties to either
[id, updated_at]
or to
[]
in the tap, but the target still wants to merge on “ID” which is the tables actual primary key 😕
a
@sam_werbalowsky - Can you try on a fresh table? (Perhaps by renaming the current one?) If the target is inferring from the table schema instead of from the stream's
key-properties
, this should work around that.
s
Yeah I can definitely do that when back online! I did delete the target table in snowflake and try again...Though maybe it is something on the tap side, so I can try that on monday
Yeah, still no luck on this one, set up a new Snowflake db and schema to attempt it and still not working. It seems to always want to create the primary key on the column from the database rather than the metadata. This is what the output with
--dump=catalog
looks like for the given table.
Copy code
"tap_stream_id": "myschema-mytable",
      "schema": {
        "type": "object",
        "properties": {
          "id": {
            "type": [
              "string"
            ]
          },
          "user_id": {
            "type": [
              "null",
              "string"
            ]
          }
        },
        "definitions": {
          "sdc_recursive_integer_array": {
            "type": [
              "null",
              "integer",
              "array"
            ],
            "items": {
              "$ref": "#/definitions/sdc_recursive_integer_array"
            }
          },
          "sdc_recursive_number_array": {
            "type": [
              "null",
              "number",
              "array"
            ],
            "items": {
              "$ref": "#/definitions/sdc_recursive_number_array"
            }
          },
          "sdc_recursive_string_array": {
            "type": [
              "null",
              "string",
              "array"
            ],
            "items": {
              "$ref": "#/definitions/sdc_recursive_string_array"
            }
          },
          "sdc_recursive_boolean_array": {
            "type": [
              "null",
              "boolean",
              "array"
            ],
            "items": {
              "$ref": "#/definitions/sdc_recursive_boolean_array"
            }
          },
          "sdc_recursive_timestamp_array": {
            "type": [
              "null",
              "string",
              "array"
            ],
            "format": "date-time",
            "items": {
              "$ref": "#/definitions/sdc_recursive_timestamp_array"
            }
          },
          "sdc_recursive_object_array": {
            "type": [
              "null",
              "object",
              "array"
            ],
            "items": {
              "$ref": "#/definitions/sdc_recursive_object_array"
            }
          }
        }
      },
      "selected": true,
      "table_key_properties": [
        "id",
        "updated_at"
      ],
      "key_properties": [
        "id",
        "updated_at"
      ],
      "replication_method": "INCREMENTAL",
      "replication_key": "updated_at",
      "properties": [
        "id",
        "updated_at"
      ]
    },