How to set up a database connection in Apache Hop
What this shows
Hop keeps database connections outside the pipelines that use them. You define one, give it a name, and from then on pipelines refer to the name. This tutorial sets up the connection the other database tutorials in this series use.
Reference documentationThe complete list of Relational database connection options is documented in the Apache Hop manual, which is the authoritative reference.hop.apache.org →Transcript
Full transcript
Hop keeps database connections outside the pipelines that use them. You define one, give it a name, and from then on pipelines refer to the name. This tutorial sets up the connection the other database tutorials in this series use.
This connection is called tutorial. The type is PostgreSQL, and picking that is what decided the three fields underneath it: a host, a port and a database name are what PostgreSQL asks for. Pick a different type and you are asked for different things. The host here reads putki-tutorial-db rather than localhost, because this database runs in a container and that is the name it answers to on the container network. The port is five four three two, the standard one. The database is also called tutorial, which is a coincidence of naming rather than a rule: the connection's name and the database's name have nothing to do with each other.
Installed driver reads org dot postgresql dot Driver, version forty-two point seven point four. That line is worth looking at first when a connection refuses to work, because Hop ships the dialect but not always the driver. If it says no driver installed, the fix is hop driver install postgresql, and no amount of correcting the host name will help. The password shows as dots here and is stored encrypted in the connection file, which is obfuscation rather than security - anyone with the file can recover it.
The name is the only part of this that other files mention. Hop writes the connection to a JSON file under the project's metadata folder, in a directory called rdbms, one file per connection named after the connection. A pipeline stores the word tutorial and nothing else, which is why moving a pipeline between machines does not move a password with it. The Hop manual's advice at this point is worth following: put variables in these fields rather than the literal values you see here, and let each environment supply its own. The connection editor will generate those variables for you from what you have already typed.
The Advanced tab handles the ways databases disagree with each other. Identifier case is the usual one: PostgreSQL folds unquoted names to lower case and several others fold to upper, so a pipeline that worked against one can fail against the next. Forcing a case here settles it in one place. The preferred schema is set to library on this connection, which is where the books tables live. With it set, a Table Input can say books rather than library dot books.
The Options tab is empty on this connection and will stay empty for most. Anything the driver understands and this dialog has no field for goes here as a parameter and a value, and gets appended to the connection URL. Time zones, SSL modes, statement timeouts. What is available is a question for the driver's documentation rather than Hop's.