A Singer postgres target, for use with Singer streams generated by Singer taps.

• Creates SQL tables for Singer streams
• Denests objects flattening them into the parent object's table
• Denests rows into separate tables
• Adds columns and sub-tables as new fields are added to the stream JSON Schema
• Full stream replication via record version and ACTIVATE_VERSION messages.

Create a config file with postgres connection information and target postgres schema.

{
  "postgres_host": "locahost",
  "postgres_port": 5432,
  "postgres_database": "my_analytics",
  "postgres_username": "myuser",
  "postgres_password": "1234",
  "postgres_schema": "mytapname"
}

Run target-postgres against a Singer stream.

	tap-something | target-postgres --config config.json

It ignores "STATE" type Singer messages.

• Requires a JSON Schema for every stream.
• Only string, string with date-time format, integer, number, boolean, object, and array types with or without null are supported. Arrays can have any of the other types listed, including objects as types within items.
  • Example of JSON Schema types that work
    • ['number']
    • ['string']
    • ['string', 'null']
  • Exmaple of JSON Schema types that DO NOT work
    • ['string', 'integer']
    • ['integer', 'number']
    • ['any']
    • ['null']
• Types cannot change
• JSON Schema combinations such as anyOf and allOf are not supported.
• JSON Schema $ref is partially supported:
  • NOTE: The following limitations are known to NOT fail gracefully
  • Presently you cannot have any circular or recursive $refs
  • $refs must be present within the schema:
    • URI's do not work
    • if the $ref is broken, the behaviour is considered unexpected

Singer.io requires offical taps and targets to collect anonymous usage data. This data is only used in aggregate to report on individual tap/targets, as well as the Singer community at-large. IP addresses are recorded to detect unique tap/targets users but not shared with third-parties.

To disable anonymous data collection set disable_collection to true in the configuration JSON file.

target-postgres utilizes setup.py for package management, and PyTest for testing.

If you have Docker and Docker Compose installed, you can easily run the following to get a local env setup quickly.

$ docker-compose up -d --build
$ docker logs -tf target-postgres_target-postgres_1 # You container names might differ

As soon as you see INFO: Dev environment ready. you can shell into the container and start running test commands:

$ docker exec -it target-postgres_target-postgres_1 sh # Your container names might differ

See the PyTest commands below!

To run the tests, you will need a PostgreSQL server running.

NOTE: Testing assumes that you've exposed the traditional port 5432.

Make sure to set the following env vars for PyTest:

$ EXPORT POSTGRES_HOST='<your-host-name>' # Most likely 'localhost'
$ EXPORT POSTGRES_DB='<your-db-name>'     # We use 'target_postgres_test'
$ EXPORT POSTGRES_USER='<your-user-name'  # Probably just 'postgres', make sure this user has no auth

To run tests, try:

$ python setup.py pytest

Target Postgres is sponsored by Data Mill (Data Mill Services, LLC) datamill.co.

Data Mill helps organizations utilize modern data infrastructure and data science to power analytics, products, and services.

Copyright Data Mill Services, LLC 2018