PostgreSQL Bulk Loader

Description

The PostgreSQL bulk loader is a transform in which we will stream data from Hop to the psql command using "COPY DATA FROM STDIN" into the database.

Options

Option	Description
Transform name	Name of the transform.
Connection	Name of the database connection on which the target table resides.
Target schema	The name of the Schema for the table to write data to. This is important for data sources that allow for table names with dots '.' in it.
Target table	Name of the target table.
psql path	Full path to the psql utility.
Load action	Insert, Truncate. Insert inserts, truncate first truncates the table.
Fields to load	This table contains a list of fields to load data from, properties include: Table field: Table field to be loaded in the PostgreSQL table; Stream field: Field to be taken from the incoming rows; Date mask: Either "Pass through, "Date" or "DateTime", determines how date/timestamps will be loaded in PostgreSQL.

Option

Description

Transform name

Name of the transform.

Connection

Name of the database connection on which the target table resides.

Target schema

The name of the Schema for the table to write data to. This is important for data sources that allow for table names with dots '.' in it.

Target table

Name of the target table.

psql path

Full path to the psql utility.

Load action

Insert, Truncate. Insert inserts, truncate first truncates the table.

Fields to load

This table contains a list of fields to load data from, properties include:

Table field: Table field to be loaded in the PostgreSQL table;
Stream field: Field to be taken from the incoming rows;
Date mask: Either "Pass through, "Date" or "DateTime", determines how date/timestamps will be loaded in PostgreSQL.

Metadata Injection Support

All fields of this transform support metadata injection. You can use this transform with Metadata Injection to pass metadata to your pipeline at runtime.

Set Up Authentication

"psql" doesn’t allow you to specify the password. Here is a part of the connection options:

 Connection options:
  -h HOSTNAME     database server host or socket directory (default: "/var/run/postgresql")
  -p PORT         database server port (default: "5432")
  -U NAME         database user name
  -W              prompt for password (should happen automatically)

As you can see there is no way to specify a password for the database. It will always prompt for a password on the console no matter what.

To overcome this you need to set up trusted authentication on the PostgreSQL server.

To make this happen, change the pg_hba.conf file (on my box this is /etc/postgresql/8.2/main/pg_hba.conf) and add a line like this:

host    all         all         192.168.1.0/24        trust

This basically means that everyone from the 192.168.1.0 network (mask 255.255.255.0) can log into postgres on all databases with any username. If you are running Hop on the same server, change it to localhost:

host    all         all         127.0.0.1/32        trust

This is much safer of-course. Make sure you don’t invite any strangers onto your PostgreSQL database!