0.192/connector/mongodb.html

<!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.11. MongoDB Connector &#8212; Presto 0.192 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.192',
        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.192 Documentation" href="../index.html" />
    <link rel="up" title="5. Connectors" href="../connector.html" />
    <link rel="next" title="5.12. MySQL Connector" href="mysql.html" />
    <link rel="prev" title="5.10. Memory Connector" href="memory.html" /> 
  </head>
  <body role="document">
<div class="header">
    <h1 class="heading"><a href="../index.html">
        <span>Presto 0.192 Documentation</span></a></h1>
    <h2 class="heading"><span>5.11. MongoDB Connector</span></h2>
</div>
<div class="topnav">
    
<p class="nav">
    <span class="left">
        &laquo; <a href="memory.html">5.10. Memory Connector</a>
    </span>
    <span class="right">
        <a href="mysql.html">5.12. MySQL Connector</a> &raquo;
    </span>
</p>

</div>
<div class="content">
    
  <div class="section" id="mongodb-connector">
<h1>5.11. MongoDB Connector</h1>
<p>This connector allows the use of MongoDB collections as tables in Presto.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">MongoDB 2.6+ is supported although it is highly recommend to use 3.0 or later.</p>
</div>
<div class="section" id="configuration">
<h2>Configuration</h2>
<p>To configure the MongoDB connector, create a catalog properties file
<code class="docutils literal"><span class="pre">etc/catalog/mongodb.properties</span></code> with the following contents,
replacing the properties as appropriate:</p>
<div class="highlight-none"><div class="highlight"><pre><span></span>connector.name=mongodb
mongodb.seeds=host1,host:port
</pre></div>
</div>
<div class="section" id="multiple-mongodb-clusters">
<h3>Multiple MongoDB Clusters</h3>
<p>You can have as many catalogs as you need, so if you have additional
MongoDB clusters, 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="configuration-properties">
<h2>Configuration Properties</h2>
<p>The following configuration properties are available:</p>
<table border="1" class="docutils">
<colgroup>
<col width="37%" />
<col width="63%" />
</colgroup>
<thead valign="bottom">
<tr class="row-odd"><th class="head">Property Name</th>
<th class="head">Description</th>
</tr>
</thead>
<tbody valign="top">
<tr class="row-even"><td><code class="docutils literal"><span class="pre">mongodb.seeds</span></code></td>
<td>List of all mongod servers</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">mongodb.schema-collection</span></code></td>
<td>A collection which contains schema information</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">mongodb.credentials</span></code></td>
<td>List of credentials</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">mongodb.min-connections-per-host</span></code></td>
<td>The minimum size of the connection pool per host</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">mongodb.connections-per-host</span></code></td>
<td>The maximum size of the connection pool per host</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">mongodb.max-wait-time</span></code></td>
<td>The maximum wait time</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">mongodb.connection-timeout</span></code></td>
<td>The socket connect timeout</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">mongodb.socket-timeout</span></code></td>
<td>The socket timeout</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">mongodb.socket-keep-alive</span></code></td>
<td>Whether keep-alive is enabled on each socket</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">mongodb.ssl.enabled</span></code></td>
<td>Use TLS/SSL for connections to mongod/mongos</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">mongodb.read-preference</span></code></td>
<td>The read preference</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">mongodb.write-concern</span></code></td>
<td>The write concern</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">mongodb.required-replica-set</span></code></td>
<td>The required replica set name</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">mongodb.cursor-batch-size</span></code></td>
<td>The number of elements to return in a batch</td>
</tr>
</tbody>
</table>
<div class="section" id="mongodb-seeds">
<h3><code class="docutils literal"><span class="pre">mongodb.seeds</span></code></h3>
<p>Comma-separated list of <code class="docutils literal"><span class="pre">hostname[:port]</span></code> all mongod servers in the same replica set or a list of mongos servers in the same sharded cluster. If port is not specified, port 27017 will be used.</p>
<p>This property is required; there is no default and at least one seed must be defined.</p>
</div>
<div class="section" id="mongodb-schema-collection">
<h3><code class="docutils literal"><span class="pre">mongodb.schema-collection</span></code></h3>
<p>As the MongoDB is a document database, there&#8217;s no fixed schema information in the system. So a special collection in each MongoDB database should defines the schema of all tables. Please refer the <a class="reference internal" href="#table-definition-label"><span class="std std-ref">Table Definition</span></a> section for the details.</p>
<p>At startup, this connector tries guessing fields&#8217; types, but it might not be correct for your collection. In that case, you need to modify it manually. <code class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code> and <code class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TABLE</span> <span class="pre">AS</span> <span class="pre">SELECT</span></code> will create an entry for you.</p>
<p>This property is optional; the default is <code class="docutils literal"><span class="pre">_schema</span></code>.</p>
</div>
<div class="section" id="mongodb-credentials">
<h3><code class="docutils literal"><span class="pre">mongodb.credentials</span></code></h3>
<p>A comma separated list of <code class="docutils literal"><span class="pre">username:password&#64;collection</span></code> credentials</p>
<p>This property is optional; no default value.</p>
</div>
<div class="section" id="mongodb-min-connections-per-host">
<h3><code class="docutils literal"><span class="pre">mongodb.min-connections-per-host</span></code></h3>
<p>The minimum number of connections per host for this MongoClient instance. Those connections will be kept in a pool when idle, and the pool will ensure over time that it contains at least this minimum number.</p>
<p>This property is optional; the default is <code class="docutils literal"><span class="pre">0</span></code>.</p>
</div>
<div class="section" id="mongodb-connections-per-host">
<h3><code class="docutils literal"><span class="pre">mongodb.connections-per-host</span></code></h3>
<p>The maximum number of connections allowed per host for this MongoClient instance. Those connections will be kept in a pool when idle. Once the pool is exhausted, any operation requiring a connection will block waiting for an available connection.</p>
<p>This property is optional; the default is <code class="docutils literal"><span class="pre">100</span></code>.</p>
</div>
<div class="section" id="mongodb-max-wait-time">
<h3><code class="docutils literal"><span class="pre">mongodb.max-wait-time</span></code></h3>
<p>The maximum wait time in milliseconds that a thread may wait for a connection to become available.
A value of <code class="docutils literal"><span class="pre">0</span></code> means that it will not wait. A negative value means to wait indefinitely for a connection to become available.</p>
<p>This property is optional; the default is <code class="docutils literal"><span class="pre">120000</span></code>.</p>
</div>
<div class="section" id="mongodb-connection-timeout">
<h3><code class="docutils literal"><span class="pre">mongodb.connection-timeout</span></code></h3>
<p>The connection timeout in milliseconds. A value of <code class="docutils literal"><span class="pre">0</span></code> means no timeout. It is used solely when establishing a new connection.</p>
<p>This property is optional; the default is <code class="docutils literal"><span class="pre">10000</span></code>.</p>
</div>
<div class="section" id="mongodb-socket-timeout">
<h3><code class="docutils literal"><span class="pre">mongodb.socket-timeout</span></code></h3>
<p>The socket timeout in milliseconds. It is used for I/O socket read and write operations.</p>
<p>This property is optional; the default is <code class="docutils literal"><span class="pre">0</span></code> and means no timeout.</p>
</div>
<div class="section" id="mongodb-socket-keep-alive">
<h3><code class="docutils literal"><span class="pre">mongodb.socket-keep-alive</span></code></h3>
<p>This flag controls the socket keep alive feature that keeps a connection alive through firewalls.</p>
<p>This property is optional; the default is <code class="docutils literal"><span class="pre">false</span></code>.</p>
</div>
<div class="section" id="mongodb-ssl-enabled">
<h3><code class="docutils literal"><span class="pre">mongodb.ssl.enabled</span></code></h3>
<p>This flag enables SSL connections to MongoDB servers.</p>
<p>This property is optional; the default is <code class="docutils literal"><span class="pre">false</span></code>.</p>
</div>
<div class="section" id="mongodb-read-preference">
<h3><code class="docutils literal"><span class="pre">mongodb.read-preference</span></code></h3>
<p>The read preference to use for queries, map-reduce, aggregation, and count.
The available values are <code class="docutils literal"><span class="pre">PRIMARY</span></code>, <code class="docutils literal"><span class="pre">PRIMARY_PREFERRED</span></code>, <code class="docutils literal"><span class="pre">SECONDARY</span></code>, <code class="docutils literal"><span class="pre">SECONDARY_PREFERRED</span></code> and <code class="docutils literal"><span class="pre">NEAREST</span></code>.</p>
<p>This property is optional; the default is <code class="docutils literal"><span class="pre">PRIMARY</span></code>.</p>
</div>
<div class="section" id="mongodb-write-concern">
<h3><code class="docutils literal"><span class="pre">mongodb.write-concern</span></code></h3>
<p>The write concern to use. The available values are
<code class="docutils literal"><span class="pre">ACKNOWLEDGED</span></code>, <code class="docutils literal"><span class="pre">FSYNC_SAFE</span></code>, <code class="docutils literal"><span class="pre">FSYNCED</span></code>, <code class="docutils literal"><span class="pre">JOURNAL_SAFEY</span></code>, <code class="docutils literal"><span class="pre">JOURNALED</span></code>, <code class="docutils literal"><span class="pre">MAJORITY</span></code>,
<code class="docutils literal"><span class="pre">NORMAL</span></code>, <code class="docutils literal"><span class="pre">REPLICA_ACKNOWLEDGED</span></code>, <code class="docutils literal"><span class="pre">REPLICAS_SAFE</span></code> and <code class="docutils literal"><span class="pre">UNACKNOWLEDGED</span></code>.</p>
<p>This property is optional; the default is <code class="docutils literal"><span class="pre">ACKNOWLEDGED</span></code>.</p>
</div>
<div class="section" id="mongodb-required-replica-set">
<h3><code class="docutils literal"><span class="pre">mongodb.required-replica-set</span></code></h3>
<p>The required replica set name. With this option set, the MongoClient instance will</p>
<ol class="arabic simple">
<li>Connect in replica set mode, and discover all members of the set based on the given servers</li>
<li>Make sure that the set name reported by all members matches the required set name.</li>
<li>Refuse to service any requests if any member of the seed list is not part of a replica set with the required name.</li>
</ol>
<p>This property is optional; no default value.</p>
</div>
<div class="section" id="mongodb-cursor-batch-size">
<h3><code class="docutils literal"><span class="pre">mongodb.cursor-batch-size</span></code></h3>
<p>Limits the number of elements returned in one batch. A cursor typically fetches a batch of result objects and stores them locally.
If batchSize is 0, Driver&#8217;s default will be used.
If batchSize is positive, it represents the size of each batch of objects retrieved. It can be adjusted to optimize performance and limit data transfer.
If batchSize is negative, it will limit of number objects returned, that fit within the max batch size limit (usually 4MB), and cursor will be closed. For example if batchSize is -10, then the server will return a maximum of 10 documents and as many as can fit in 4MB, then close the cursor.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Do not use a batch size of <code class="docutils literal"><span class="pre">1</span></code>.</p>
</div>
<p>This property is optional; the default is <code class="docutils literal"><span class="pre">0</span></code>.</p>
</div>
</div>
<div class="section" id="table-definition">
<span id="table-definition-label"></span><h2>Table Definition</h2>
<p>MongoDB maintains table definitions on the special collection where <code class="docutils literal"><span class="pre">mongodb.schema-collection</span></code> configuration value specifies.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">There&#8217;s no way for the plugin to detect a collection is deleted.
You need to delete the entry by <code class="docutils literal"><span class="pre">db.getCollection(&quot;_schema&quot;).remove(</span> <span class="pre">{</span> <span class="pre">table:</span> <span class="pre">deleted_table_name</span> <span class="pre">})</span></code> in the Mongo Shell.
Or drop a collection by running <code class="docutils literal"><span class="pre">DROP</span> <span class="pre">TABLE</span> <span class="pre">table_name</span></code> using Presto.</p>
</div>
<p>A schema collection consists of a MongoDB document for a table.</p>
<div class="highlight-none"><div class="highlight"><pre><span></span>{
    &quot;table&quot;: ...,
    &quot;fields&quot;: [
          { &quot;name&quot; : ...,
            &quot;type&quot; : &quot;varchar|bigint|boolean|double|date|array&lt;bigint&gt;|...&quot;,
            &quot;hidden&quot; : false },
            ...
        ]
    }
}
</pre></div>
</div>
<table border="1" class="docutils">
<colgroup>
<col width="12%" />
<col width="7%" />
<col width="11%" />
<col width="71%" />
</colgroup>
<thead valign="bottom">
<tr class="row-odd"><th class="head">Field</th>
<th class="head">Required</th>
<th class="head">Type</th>
<th class="head">Description</th>
</tr>
</thead>
<tbody valign="top">
<tr class="row-even"><td><code class="docutils literal"><span class="pre">table</span></code></td>
<td>required</td>
<td>string</td>
<td>Presto table name</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">fields</span></code></td>
<td>required</td>
<td>array</td>
<td>A list of field definitions. Each field definition creates a new column in the Presto table.</td>
</tr>
</tbody>
</table>
<p>Each field definition:</p>
<div class="highlight-none"><div class="highlight"><pre><span></span>{
    &quot;name&quot;: ...,
    &quot;type&quot;: ...,
    &quot;hidden&quot;: ...
}
</pre></div>
</div>
<table border="1" class="docutils">
<colgroup>
<col width="12%" />
<col width="7%" />
<col width="7%" />
<col width="73%" />
</colgroup>
<thead valign="bottom">
<tr class="row-odd"><th class="head">Field</th>
<th class="head">Required</th>
<th class="head">Type</th>
<th class="head">Description</th>
</tr>
</thead>
<tbody valign="top">
<tr class="row-even"><td><code class="docutils literal"><span class="pre">name</span></code></td>
<td>required</td>
<td>string</td>
<td>Name of the column in the Presto table.</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">type</span></code></td>
<td>required</td>
<td>string</td>
<td>Presto type of the column.</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">hidden</span></code></td>
<td>optional</td>
<td>boolean</td>
<td>Hides the column from <code class="docutils literal"><span class="pre">DESCRIBE</span> <span class="pre">&lt;table</span> <span class="pre">name&gt;</span></code> and <code class="docutils literal"><span class="pre">SELECT</span> <span class="pre">*</span></code>. Defaults to <code class="docutils literal"><span class="pre">false</span></code>.</td>
</tr>
</tbody>
</table>
<p>There is no limit on field descriptions for either key or message.</p>
</div>
<div class="section" id="objectid">
<h2>ObjectId</h2>
<p>MongoDB collection has the special field <code class="docutils literal"><span class="pre">_id</span></code>. The connector tries to follow the same rules for this special field, so there will be hidden field <code class="docutils literal"><span class="pre">_id</span></code>.</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="k">IF</span> <span class="k">NOT</span> <span class="k">EXISTS</span> <span class="n">orders</span> <span class="p">(</span>
    <span class="n">orderkey</span> <span class="nb">bigint</span><span class="p">,</span>
    <span class="n">orderstatus</span> <span class="nb">varchar</span><span class="p">,</span>
    <span class="n">totalprice</span> <span class="n">double</span><span class="p">,</span>
    <span class="n">orderdate</span> <span class="nb">date</span>
<span class="p">);</span>

<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">orders</span> <span class="k">VALUES</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s1">&#39;bad&#39;</span><span class="p">,</span> <span class="mi">50</span><span class="p">.</span><span class="mi">0</span><span class="p">,</span> <span class="k">current_date</span><span class="p">);</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">orders</span> <span class="k">VALUES</span><span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="s1">&#39;good&#39;</span><span class="p">,</span> <span class="mi">100</span><span class="p">.</span><span class="mi">0</span><span class="p">,</span> <span class="k">current_date</span><span class="p">);</span>
<span class="k">SELECT</span> <span class="n">_id</span><span class="p">,</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">orders3</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span>                 _id                 | orderkey | orderstatus | totalprice | orderdate
-------------------------------------+----------+-------------+------------+------------
 55 b1 51 63 38 64 d6 43 8c 61 a9 ce |        1 | bad         |       50.0 | 2015-07-23
 55 b1 51 67 38 64 d6 43 8c 61 a9 cf |        2 | good        |      100.0 | 2015-07-23
(2 rows)
</pre></div>
</div>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">_id</span><span class="p">,</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">orders3</span> <span class="k">WHERE</span> <span class="n">_id</span> <span class="o">=</span> <span class="n">ObjectId</span><span class="p">(</span><span class="s1">&#39;55b151633864d6438c61a9ce&#39;</span><span class="p">);</span>
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span>                 _id                 | orderkey | orderstatus | totalprice | orderdate
-------------------------------------+----------+-------------+------------+------------
 55 b1 51 63 38 64 d6 43 8c 61 a9 ce |        1 | bad         |       50.0 | 2015-07-23
(1 row)
</pre></div>
</div>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Unfortunately, there is no way to represent <code class="docutils literal"><span class="pre">_id</span></code> fields more fancy like <code class="docutils literal"><span class="pre">55b151633864d6438c61a9ce</span></code>.</p>
</div>
</div>
</div>


</div>
<div class="bottomnav">
    
<p class="nav">
    <span class="left">
        &laquo; <a href="memory.html">5.10. Memory Connector</a>
    </span>
    <span class="right">
        <a href="mysql.html">5.12. MySQL Connector</a> &raquo;
    </span>
</p>

</div>

    <div class="footer" role="contentinfo">
    </div>
  </body>
</html>