321/connector/googlesheets.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>6.5. Google Sheets Connector &#8212; Presto 321 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:     '321',
        COLLAPSE_INDEX: false,
        FILE_SUFFIX: '.html',
        HAS_SOURCE:  true,
        SOURCELINK_SUFFIX: '.txt'
      };
    </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="next" title="6.6. Hive Connector" href="hive.html" />
    <link rel="prev" title="6.4. Elasticsearch Connector" href="elasticsearch.html" /> 
    <script async src="https://www.googletagmanager.com/gtag/js?id=UA-133457846-1"></script>
    <script>
      window.dataLayer = window.dataLayer || [];
      function gtag(){dataLayer.push(arguments);}
      gtag('js', new Date());
      gtag('config', 'UA-133457846-1');
    </script>
  </head>
  <body>
<div class="header">
    <h1 class="heading"><a href="../index.html">
        <span>Presto 321 Documentation</span></a></h1>
    <h2 class="heading"><span>6.5. Google Sheets Connector</span></h2>
</div>
<div class="topnav">
    
<p class="nav">
    <span class="left">
        &laquo; <a href="elasticsearch.html">6.4. Elasticsearch Connector</a>
    </span>
    <span class="right">
        <a href="hive.html">6.6. Hive Connector</a> &raquo;
    </span>
</p>

</div>
<div class="content">
    
  <div class="section" id="google-sheets-connector">
<h1>6.5. Google Sheets Connector</h1>
<p>The Google Sheets connector allows reading spreadsheets as tables in Presto.</p>
<div class="section" id="configuration">
<h2>Configuration</h2>
<p>Create <code class="docutils literal"><span class="pre">etc/catalog/sheets.properties</span></code>
to mount the Google Sheets connector as the <code class="docutils literal"><span class="pre">sheets</span></code> catalog,
replacing the properties as appropriate:</p>
<div class="highlight-none"><div class="highlight"><pre><span></span>connector.name=gsheets
credentials-path=/path/to/google-sheets-credentials.json
metadata-sheet-id=exampleId
</pre></div>
</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="34%" />
<col width="66%" />
</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">credentials-path</span></code></td>
<td>Path to the Google API JSON key file</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">metadata-sheet-id</span></code></td>
<td>Sheet ID of the spreadsheet that contains the table mapping</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">sheets-data-max-cache-size</span></code></td>
<td>Maximum number of spreadsheets to cache (defaults to <code class="docutils literal"><span class="pre">1000</span></code>)</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">sheets-data-expire-after-write</span></code></td>
<td>How long to cache spreadsheet data or metadata (defaults to <code class="docutils literal"><span class="pre">5m</span></code>)</td>
</tr>
</tbody>
</table>
</div>
<div class="section" id="credentials">
<h2>Credentials</h2>
<p>The connector requires credentials in order to access the Google Sheets API.</p>
<ol class="arabic simple">
<li>Open the <a class="reference external" href="https://console.developers.google.com/apis/library/sheets.googleapis.com">Google Sheets API</a>
page and click the <em>Enable</em> button. This will take you to the API manager page.</li>
<li>Select a project using the drop down menu at the top of the page.
Create a new project if you do not already have one.</li>
<li>Choose <em>Credentials</em> in the left panel.</li>
<li>Click <em>Manage service accounts</em>, then create a service account for the connector.
On the <em>Create key</em> step, create and download a key in JSON format.</li>
</ol>
<p>The key file will need to be available on the Presto coordinator and workers.
Set the <code class="docutils literal"><span class="pre">credentials-path</span></code> configuration property to point to this file
(the exact name of the file does not matter – it can be named anything).</p>
</div>
<div class="section" id="metadata-sheet">
<h2>Metadata Sheet</h2>
<p>The metadata sheet is used to map table names to sheet IDs.
Create a new metadata sheet. The first row must be a header row
containing the following columns in this order:</p>
<ul class="simple">
<li>Table Name</li>
<li>Sheet ID</li>
<li>Owner</li>
<li>Notes</li>
</ul>
<p>See this <a class="reference external" href="https://docs.google.com/spreadsheets/d/1Es4HhWALUQjoa-bQh4a8B5HROz7dpGMfq_HbfoaW5LM">example sheet</a>
as a reference.</p>
<p>The metadata sheet must be shared with the service account user
(the one for which the key credentials file was created). Click the <em>Share</em>
button to share the sheet with the email address of the service account.</p>
<p>Set the <code class="docutils literal"><span class="pre">metadata-sheet-id</span></code> configuration property to the ID of this sheet.</p>
</div>
<div class="section" id="querying-sheets">
<h2>Querying Sheets</h2>
<p>The service account user must have access to the sheet in order for Presto
to query it. Click the <em>Share</em> button to share the sheet with the email
address of the service account.</p>
<p>The sheet needs to be mapped to a Presto table name. Specify a table name
(column A) and the sheet ID (column B) in the metadata sheet. To refer
to a specific tab in the sheet, add the tab name after the sheet ID, separated
with <code class="docutils literal"><span class="pre">#</span></code>.</p>
</div>
<div class="section" id="api-usage-limits">
<h2>API Usage Limits</h2>
<p>The Google Sheets API has <a class="reference external" href="https://developers.google.com/sheets/api/limits">usage limits</a>
that may impact the usage of this connector. Increasing the cache duration and/or size
may prevent the limit from being reached. Running queries on the <code class="docutils literal"><span class="pre">information_schema.columns</span></code>
table without a schema and table name filter may lead to hitting the limit, as this requires
fetching the sheet data for every table (unless it is already cached).</p>
</div>
</div>


</div>
<div class="bottomnav">
    
<p class="nav">
    <span class="left">
        &laquo; <a href="elasticsearch.html">6.4. Elasticsearch Connector</a>
    </span>
    <span class="right">
        <a href="hive.html">6.6. Hive Connector</a> &raquo;
    </span>
</p>

</div>

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