-
Notifications
You must be signed in to change notification settings - Fork 16
/
Copy pathpostgresql.html
141 lines (128 loc) · 7.63 KB
/
postgresql.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>5.13. PostgreSQL Connector — Presto 0.199 Documentation</title>
<link rel="stylesheet" href="../_static/presto.css" type="text/css" />
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '0.199',
COLLAPSE_INDEX: false,
FILE_SUFFIX: '.html',
HAS_SOURCE: true
};
</script>
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<link rel="index" title="Index" href="../genindex.html" />
<link rel="search" title="Search" href="../search.html" />
<link rel="top" title="Presto 0.199 Documentation" href="../index.html" />
<link rel="up" title="5. Connectors" href="../connector.html" />
<link rel="next" title="5.14. Redis Connector" href="redis.html" />
<link rel="prev" title="5.12. MySQL Connector" href="mysql.html" />
</head>
<body role="document">
<div class="header">
<h1 class="heading"><a href="../index.html">
<span>Presto 0.199 Documentation</span></a></h1>
<h2 class="heading"><span>5.13. PostgreSQL Connector</span></h2>
</div>
<div class="topnav">
<p class="nav">
<span class="left">
« <a href="mysql.html">5.12. MySQL Connector</a>
</span>
<span class="right">
<a href="redis.html">5.14. Redis Connector</a> »
</span>
</p>
</div>
<div class="content">
<div class="section" id="postgresql-connector">
<h1>5.13. PostgreSQL Connector</h1>
<p>The PostgreSQL connector allows querying and creating tables in an
external PostgreSQL database. This can be used to join data between
different systems like PostgreSQL and Hive, or between two different
PostgreSQL instances.</p>
<div class="section" id="configuration">
<h2>Configuration</h2>
<p>To configure the PostgreSQL connector, create a catalog properties file
in <code class="docutils literal"><span class="pre">etc/catalog</span></code> named, for example, <code class="docutils literal"><span class="pre">postgresql.properties</span></code>, to
mount the PostgreSQL connector as the <code class="docutils literal"><span class="pre">postgresql</span></code> catalog.
Create the file with the following contents, replacing the
connection properties as appropriate for your setup:</p>
<div class="highlight-none"><div class="highlight"><pre><span></span>connector.name=postgresql
connection-url=jdbc:postgresql://example.net:5432/database
connection-user=root
connection-password=secret
</pre></div>
</div>
<div class="section" id="multiple-postgresql-databases-or-servers">
<h3>Multiple PostgreSQL Databases or Servers</h3>
<p>The PostgreSQL connector can only access a single database within
a PostgreSQL server. Thus, if you have multiple PostgreSQL databases,
or want to connect to multiple PostgreSQL servers, you must configure
multiple instances of the PostgreSQL connector.</p>
<p>To add another catalog, simply add another properties file to <code class="docutils literal"><span class="pre">etc/catalog</span></code>
with a different name (making sure it ends in <code class="docutils literal"><span class="pre">.properties</span></code>). For example,
if you name the property file <code class="docutils literal"><span class="pre">sales.properties</span></code>, Presto will create a
catalog named <code class="docutils literal"><span class="pre">sales</span></code> using the configured connector.</p>
</div>
</div>
<div class="section" id="querying-postgresql">
<h2>Querying PostgreSQL</h2>
<p>The PostgreSQL connector provides a schema for every PostgreSQL schema.
You can see the available PostgreSQL schemas by running <code class="docutils literal"><span class="pre">SHOW</span> <span class="pre">SCHEMAS</span></code>:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SHOW</span> <span class="n">SCHEMAS</span> <span class="k">FROM</span> <span class="n">postgresql</span><span class="p">;</span>
</pre></div>
</div>
<p>If you have a PostgreSQL schema named <code class="docutils literal"><span class="pre">web</span></code>, you can view the tables
in this schema by running <code class="docutils literal"><span class="pre">SHOW</span> <span class="pre">TABLES</span></code>:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SHOW</span> <span class="n">TABLES</span> <span class="k">FROM</span> <span class="n">postgresql</span><span class="p">.</span><span class="n">web</span><span class="p">;</span>
</pre></div>
</div>
<p>You can see a list of the columns in the <code class="docutils literal"><span class="pre">clicks</span></code> table in the <code class="docutils literal"><span class="pre">web</span></code> database
using either of the following:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">DESCRIBE</span> <span class="n">postgresql</span><span class="p">.</span><span class="n">web</span><span class="p">.</span><span class="n">clicks</span><span class="p">;</span>
<span class="k">SHOW</span> <span class="n">COLUMNS</span> <span class="k">FROM</span> <span class="n">postgresql</span><span class="p">.</span><span class="n">web</span><span class="p">.</span><span class="n">clicks</span><span class="p">;</span>
</pre></div>
</div>
<p>Finally, you can access the <code class="docutils literal"><span class="pre">clicks</span></code> table in the <code class="docutils literal"><span class="pre">web</span></code> schema:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">postgresql</span><span class="p">.</span><span class="n">web</span><span class="p">.</span><span class="n">clicks</span><span class="p">;</span>
</pre></div>
</div>
<p>If you used a different name for your catalog properties file, use
that catalog name instead of <code class="docutils literal"><span class="pre">postgresql</span></code> in the above examples.</p>
</div>
<div class="section" id="postgresql-connector-limitations">
<h2>PostgreSQL Connector Limitations</h2>
<p>The following SQL statements are not yet supported:</p>
<ul class="simple">
<li><a class="reference internal" href="../sql/delete.html"><span class="doc">DELETE</span></a></li>
<li><a class="reference internal" href="../sql/alter-table.html"><span class="doc">ALTER TABLE</span></a></li>
<li><a class="reference internal" href="../sql/create-table.html"><span class="doc">CREATE TABLE</span></a> (<a class="reference internal" href="../sql/create-table-as.html"><span class="doc">CREATE TABLE AS</span></a> is supported)</li>
<li><a class="reference internal" href="../sql/grant.html"><span class="doc">GRANT</span></a></li>
<li><a class="reference internal" href="../sql/revoke.html"><span class="doc">REVOKE</span></a></li>
<li><a class="reference internal" href="../sql/show-grants.html"><span class="doc">SHOW GRANTS</span></a></li>
</ul>
</div>
</div>
</div>
<div class="bottomnav">
<p class="nav">
<span class="left">
« <a href="mysql.html">5.12. MySQL Connector</a>
</span>
<span class="right">
<a href="redis.html">5.14. Redis Connector</a> »
</span>
</p>
</div>
<div class="footer" role="contentinfo">
</div>
</body>
</html>