Example 2: Dashboard Application

If you’re a data scientist, you need to keep track of a range of metrics every hour/day/month and make them accessible via visualizations. Using this “write-to-PostgreSQL” feature, you can streamline the process and focus on your queries and your visualizations of the query results.

You can limit the access to your database by using a list of static IPs. Please contact support@treasuredata.com if you need it.

Result Output URL Format

Format

The result output target is represented by URL with the following format:

port is the port number through which the Postgres server is accessible. “:” is optional and assumed to be 5432 by default;

database is the name of the destination database;

table is the name of a table within the above mentioned database. It may not exist at the moment the query output is execute, in which case a table with the specified name will be created for the user.

Options

Result output to Postgres supports various options that can be specified as optional URL parameters. The options are compatible with each other and can be combined. Where applicable, the default behavior is indicated.

ssl option

ssl option determines whether to use SSL or not for connecting to the Postgres server.

ssl=false (default)

schema option

Controls the schema the target table is located. If not specified default schema is to be used. The default schema depends on user’s “search_path” setting but it is usually “public”.

postgresql://user:password@host/database/table?schema=target_schema

Update mode option

Controls the various ways of modifying the database data. All 4 supported modes are atomic because they use a temporary table to store the incoming data before attempting to modify the destination table:

Append

Replace

Truncate

Update

mode=append (default)

The append mode is the default which is used when no mode option is provided in the URL. In this mode the query results are appended to the table. If the table does not exist, it will be created.

Because mode=append is the default behavior, these two URLs are equivalent:

mode=replace

The replace mode consists of replacing the entire content of an existing table with the result output of the query. If the table does not exist yet, a new table will be created. The replace mode achieves atomicity (so that a consumer of the table always has consistent data) by performing the following three steps in a single transaction:

Create a temporary table;

Write to the temporary table;

Replace the existing table with the temporary table using ALTER TABLE RENAME.

Example:

postgresql://user:password@host/database/table?mode=replace

mode=truncate

With the truncate mode the system first truncates the existing table, then inserts the query results. If the table does not exist yet, a new table will be created.

Example:

postgresql://user:password@host/database/table?mode=truncate

Unlike replace, the truncate mode retains the indexes of the table.

mode=update

In the update mode a row is inserted unless it would cause a duplicate value in the columns specified in the “unique” parameter: in such case an update is performed instead. Please note that the “unique” parameter is required when using the update mode.

method=copy

When the copy method is used the data is first stored in a temporary file on the server, then written to Postgres using a COPY transaction. This method is faster than INSERT, so it is useful when handling a large amount of data.

Example:

postgresql://user:password@host/database/table?method=copy

Usage

CLI

To output the result of a single query to a Postgres server add the --result option to the td query command. After the job is finished, the results will be written into your database: