-
Notifications
You must be signed in to change notification settings - Fork 16
/
Copy pathsqlserver.html
180 lines (167 loc) · 9.63 KB
/
sqlserver.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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
<!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.16. SQL Server 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.17. System Connector" href="system.html" />
<link rel="prev" title="5.15. Redshift Connector" href="redshift.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.16. SQL Server Connector</span></h2>
</div>
<div class="topnav">
<p class="nav">
<span class="left">
« <a href="redshift.html">5.15. Redshift Connector</a>
</span>
<span class="right">
<a href="system.html">5.17. System Connector</a> »
</span>
</p>
</div>
<div class="content">
<div class="section" id="sql-server-connector">
<h1>5.16. SQL Server Connector</h1>
<p>The SQL Server connector allows querying and creating tables in an
external SQL Server database. This can be used to join data between
different systems like SQL Server and Hive, or between two different
SQL Server instances.</p>
<div class="section" id="configuration">
<h2>Configuration</h2>
<p>To configure the SQL Server 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">sqlserver.properties</span></code>, to
mount the SQL Server connector as the <code class="docutils literal"><span class="pre">sqlserver</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=sqlserver
connection-url=jdbc:sqlserver://[serverName[\instanceName][:portNumber]]
connection-user=root
connection-password=secret
</pre></div>
</div>
<div class="section" id="multiple-sql-server-databases-or-servers">
<h3>Multiple SQL Server Databases or Servers</h3>
<p>The SQL Server connector can only access a single database within
a SQL Server server. Thus, if you have multiple SQL Server databases,
or want to connect to multiple instances of the SQL Server, you must configure
multiple catalogs, one for each instance.</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-sql-server">
<h2>Querying SQL Server</h2>
<p>The SQL Server connector provides access to all schemas visible to the specified user in the configured database.
For the following examples, assume the SQL Server catalog is <code class="docutils literal"><span class="pre">sqlserver</span></code>.</p>
<p>You can see the available 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">sqlserver</span><span class="p">;</span>
</pre></div>
</div>
<p>If you have a 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">sqlserver</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">sqlserver</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">sqlserver</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 query 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">sqlserver</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">sqlserver</span></code> in the above examples.</p>
</div>
<div class="section" id="sql-server-connector-limitations">
<h2>SQL Server Connector Limitations</h2>
<p>Presto supports connecting to SQL Server 2016, SQL Server 2014, SQL Server 2012
and Azure SQL Database.</p>
<p>Presto supports the following SQL Server data types.
The following table shows the mappings between SQL Server and Presto data types.</p>
<table border="1" class="docutils">
<colgroup>
<col width="51%" />
<col width="49%" />
</colgroup>
<thead valign="bottom">
<tr class="row-odd"><th class="head">SQL Server Type</th>
<th class="head">Presto Type</th>
</tr>
</thead>
<tbody valign="top">
<tr class="row-even"><td><code class="docutils literal"><span class="pre">bigint</span></code></td>
<td><code class="docutils literal"><span class="pre">bigint</span></code></td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">smallint</span></code></td>
<td><code class="docutils literal"><span class="pre">smallint</span></code></td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">int</span></code></td>
<td><code class="docutils literal"><span class="pre">integer</span></code></td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">float</span></code></td>
<td><code class="docutils literal"><span class="pre">double</span></code></td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">char(n)</span></code></td>
<td><code class="docutils literal"><span class="pre">char(n)</span></code></td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">varchar(n)</span></code></td>
<td><code class="docutils literal"><span class="pre">varchar(n)</span></code></td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">date</span></code></td>
<td><code class="docutils literal"><span class="pre">date</span></code></td>
</tr>
</tbody>
</table>
<p>Complete list of <a class="reference external" href="https://msdn.microsoft.com/en-us/library/ms187752.aspx">SQL Server data types</a>.</p>
<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>
</ul>
</div>
</div>
</div>
<div class="bottomnav">
<p class="nav">
<span class="left">
« <a href="redshift.html">5.15. Redshift Connector</a>
</span>
<span class="right">
<a href="system.html">5.17. System Connector</a> »
</span>
</p>
</div>
<div class="footer" role="contentinfo">
</div>
</body>
</html>