Presto PostgreSQL Gateway

Presto query engine provides the gateway, who can talk PostgreSQL protocol. That means you can connect and issue queries to Presto, ad if it’s a PostgreSQL.

Untitled-3
WARNING (FEATURE OBSOLETION PLANNED): Due to a couple of fundamental architectural challenges, this feature is planned to deprecate in Q1 2017. Any new customers are not recommended to use this feature. We will be enabling similar functionality via a direct presto JDBC driver in Q3 2016. Please contact support@treasuredata.com.

Table of Contents

Connect to the Gateway

Please use the following information to connect Presto PostgreSQL gateway.

  • Host: pggw.treasuredata.com
  • Port: 5439
  • Protocol Version: 2
  • SSL: Enabled

Also there are two ways to authenticate your self: by email + password, or apikey.

Authentication 1: Email + Password Authentication

  • User: YOUR_EMAIL
  • Pass: YOUR_PASSWORD
Untitled-3
The PostgreSQL protocol limits the length of the 'username' to 32 bytes/characters.
Therefore email addresses longer than 32 characters will not work: please use the API key based authentication method instead if you are affected by this problem.

Authentication 2: API Key Authentication

  • User: TD1 (Fixed String)
  • Password: YOUR_API_KEY

Example: Connect with psql Command

Here’s an example command to connect with psql command.

$ psql -h pggw.treasuredata.com -p 5439 -U "YOUR_EMAIL_IS_HERE" -W -d testdb
Password for user XXX: YOUR_PASSWORD_IS_HERE

testdb=# \t
www_access

testdb=# SELECT COUNT(1) FROM testdb.www_access;
5000

Example: Connect from Ruby Program

Here’s an example Ruby program to connect with Presto PostgreSQL gateway.

#!/usr/bin/env ruby
require 'pg'
require 'benchmark'
q = ARGV[0]
puts Benchmark.measure {
  conn = PGconn.connect(
    :host=>"pggw.treasuredata.com",
    :port=>5439,
    :dbname=>"testdb",
    :user=>"YOUR_EMAIL_IS_HERE",
    :password=>'YOUR_PASSWORD_IS_HERE')
  res = conn.exec(q)
  res.each{ |row| p row }
}

Last modified: Dec 23 2016 04:08:01 UTC

If this article is incorrect or outdated, or omits critical information, let us know. For all other issues, access our support channels.