321/connector/hive-gcs-tutorial.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.7. Hive Connector GCS Tutorial &#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.8. Hive Security Configuration" href="hive-security.html" />
    <link rel="prev" title="6.6. Hive Connector" href="hive.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.7. Hive Connector GCS Tutorial</span></h2>
</div>
<div class="topnav">
    
<p class="nav">
    <span class="left">
        &laquo; <a href="hive.html">6.6. Hive Connector</a>
    </span>
    <span class="right">
        <a href="hive-security.html">6.8. Hive Security Configuration</a> &raquo;
    </span>
</p>

</div>
<div class="content">
    
  <div class="section" id="hive-connector-gcs-tutorial">
<h1>6.7. Hive Connector GCS Tutorial</h1>
<div class="section" id="preliminary-steps">
<h2>Preliminary Steps</h2>
<div class="section" id="ensure-access-to-gcs">
<h3>Ensure Access to GCS</h3>
<p>Access to Cloud Storage data is possible thanks to
<a class="reference external" href="https://cloud.google.com/dataproc/docs/concepts/connectors/cloud-storage">Hadoop Cloud Storage connector</a>.</p>
<p>If your data is publicly available, you do not need to do anything here.
However, in most cases data is not publicly available, and the Presto cluster needs to have access to it.
This is typically achieved by creating a service account which has permissions to access your data.
You can do this on the
<a class="reference external" href="https://console.cloud.google.com/projectselector2/iam-admin/serviceaccounts">service accounts page in GCP</a>.
Once you create a service account, create a key for it and download the key in JSON format.</p>
</div>
<div class="section" id="hive-connector-configuration">
<h3>Hive Connector configuration</h3>
<p>Another requirement is that you have enabled and configured a Hive connector in Presto.
The connector uses Hive metastore for data discovery and is not limited to data residing on HDFS.</p>
<p><strong>Configuring Hive Connector</strong></p>
<ul>
<li><p class="first">URL to Hive metastore:</p>
<blockquote>
<div><ul>
<li><p class="first">New Hive metastore on GCP:</p>
<blockquote>
<div><p>If your Presto nodes are provisioned by GCP, your Hive metastore should also be on GCP
to minimize latency and costs. The simplest way to create a new Hive metastore on GCP
is to create a small Cloud DataProc cluster (1 master, 0 workers), accessible from
your Presto cluster. Follow the steps for existing Hive metastore after finishing this step.</p>
</div></blockquote>
</li>
<li><p class="first">Existing Hive metastore:</p>
<blockquote>
<div><p>To use an existing Hive metastore with a Presto cluster, you need to set the
<code class="docutils literal"><span class="pre">hive.metastore.uri</span></code> property in your Hive catalog properties file to
<code class="docutils literal"><span class="pre">thrift://${METASTORE_ADDRESS}:${METASTORE_THRIFT_PORT}</span></code>.
If the metastore uses authentication, please refer to <a class="reference internal" href="hive-security.html"><span class="doc">Hive Security Configuration</span></a>.</p>
</div></blockquote>
</li>
</ul>
</div></blockquote>
</li>
<li><p class="first">GCS access:</p>
<blockquote>
<div><p>Here are example values for all GCS configuration properties which can be set in Hive
catalog properties file:</p>
<div class="highlight-properties"><div class="highlight"><pre><span></span><span class="c"># JSON key file used to access Google Cloud Storage</span>
<span class="na">hive.gcs.json-key-file-path</span><span class="o">=</span><span class="s">/path/to/gcs_keyfile.json</span>

<span class="c"># Use client-provided OAuth token to access Google Cloud Storage</span>
<span class="na">hive.gcs.use-access-token</span><span class="o">=</span><span class="s">false</span>
</pre></div>
</div>
</div></blockquote>
</li>
</ul>
</div>
<div class="section" id="hive-metastore-configuration">
<h3>Hive Metastore configuration</h3>
<p>If your Hive metastore uses StorageBasedAuthorization it will also need to access GCS
to perform POSIX permission checks.
Configuring GCS access for Hive is outside the scope of this tutorial, but there
are some excellent guides online:</p>
<ul class="simple">
<li><a class="reference external" href="https://cloud.google.com/dataproc/docs/concepts/connectors/install-storage-connector">Google: Installing the Cloud Storage connector</a></li>
<li><a class="reference external" href="https://docs.hortonworks.com/HDPDocuments/HDP3/HDP-3.1.0/bk_cloud-data-access/content/gcp-get-started.html">HortonWorks: Working with Google Cloud Storage</a></li>
<li><a class="reference external" href="https://www.cloudera.com/documentation/enterprise/latest/topics/admin_gcs_config.html">Cloudera: Configuring Google Cloud Storage Connectivity</a></li>
</ul>
<p>GCS access is typically configured in <code class="docutils literal"><span class="pre">core-site.xml</span></code>, to be used by all components using Apache Hadoop.</p>
<p>GCS connector for Hadoop provides an implementation of a Hadoop FileSystem.
Unfortunately GCS IAM permissions don’t map to POSIX permissions required by Hadoop FileSystem,
so the GCS connector presents fake POSIX file permissions.</p>
<p>When Hive metastore accesses GCS, it will by default see fake POSIX permissions equal to <code class="docutils literal"><span class="pre">0700</span></code>.
If Presto and Hive metastore are running as different user accounts, this will cause Hive metastore
to deny Presto data access.
There are two possible solutions to this problem:</p>
<ul class="simple">
<li>Run Presto service and Hive service as the same user.</li>
<li>Make sure Hive GCS configuration includes a <code class="docutils literal"><span class="pre">fs.gs.reported.permissions</span></code> property
with a value of <code class="docutils literal"><span class="pre">777</span></code>.</li>
</ul>
</div>
</div>
<div class="section" id="accessing-gcs-data-from-presto-for-the-first-time">
<h2>Accessing GCS Data From Presto for the First Time</h2>
<div class="section" id="accessing-data-already-mapped-in-the-hive-metastore">
<h3>Accessing Data Already Mapped in the Hive metastore</h3>
<p>If you migrate to Presto from Hive, chances are that your GCS data is already mapped to
SQL tables in the metastore.
In that case, you should be able to query it.</p>
</div>
<div class="section" id="accessing-data-not-yet-mapped-in-the-hive-metastore">
<h3>Accessing Data Not Yet Mapped in the Hive metastore</h3>
<p>To access GCS data that is not yet mapped in the Hive metastore you need to provide the
schema of the data, the file format, and the data location.
For example, if you have ORC or Parquet files in an GCS bucket <code class="docutils literal"><span class="pre">my_bucket</span></code>, you will
need to execute a query:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="c1">-- select schema in which the table will be defined, must already exist</span>
<span class="n">USE</span> <span class="n">hive</span><span class="p">.</span><span class="k">default</span><span class="p">;</span>

<span class="c1">-- create table</span>
<span class="k">CREATE</span> <span class="k">TABLE</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">custkey</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="mi">1</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="n">orderpriority</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">15</span><span class="p">),</span>
     <span class="n">clerk</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">15</span><span class="p">),</span>
     <span class="n">shippriority</span> <span class="nb">integer</span><span class="p">,</span>
     <span class="k">comment</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">79</span><span class="p">)</span>
<span class="p">)</span> <span class="k">WITH</span> <span class="p">(</span>
     <span class="n">external_location</span> <span class="o">=</span> <span class="s1">&#39;gs://my_bucket/path/to/folder&#39;</span><span class="p">,</span>
     <span class="n">format</span> <span class="o">=</span> <span class="s1">&#39;ORC&#39;</span> <span class="c1">-- or &#39;PARQUET&#39;</span>
<span class="p">);</span>
</pre></div>
</div>
<p>Now you should be able to query the newly mapped table:</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">orders</span><span class="p">;</span>
</pre></div>
</div>
</div>
</div>
<div class="section" id="writing-gcs-data-with-presto">
<h2>Writing GCS Data with Presto</h2>
<div class="section" id="prerequisites">
<h3>Prerequisites</h3>
<p>Before you attempt to write data to GCS, make sure you have configured everything
necessary to read data from GCS.</p>
</div>
<div class="section" id="create-export-schema">
<h3>Create Export Schema</h3>
<p>If Hive metastore contains schema(s) mapped to GCS locations, you can use them to
export data to GCS.
If you don’t want to use existing schemas (or there are no appropriate schemas in
the Hive metastore), you need to create a new one:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">SCHEMA</span> <span class="n">hive</span><span class="p">.</span><span class="n">gcs_export</span> <span class="k">WITH</span> <span class="p">(</span><span class="k">location</span> <span class="o">=</span> <span class="s1">&#39;gs://my_bucket/some/path&#39;</span><span class="p">);</span>
</pre></div>
</div>
</div>
<div class="section" id="export-data-to-gcs">
<h3>Export Data to GCS</h3>
<p>Once you have a schema pointing to a location where you want to export the data, you can issue
the export using a <code class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TABLE</span> <span class="pre">AS</span></code> statement and select your desired file format. The data
will be written to one or more files within the <code class="docutils literal"><span class="pre">gs://my_bucket/some/path/my_table</span></code> namespace.
Example:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">hive</span><span class="p">.</span><span class="n">gcs_export</span><span class="p">.</span><span class="n">orders_export</span>
<span class="k">WITH</span> <span class="p">(</span><span class="n">format</span> <span class="o">=</span> <span class="s1">&#39;ORC&#39;</span><span class="p">)</span>
<span class="k">AS</span> <span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">tpch</span><span class="p">.</span><span class="n">sf1</span><span class="p">.</span><span class="n">orders</span><span class="p">;</span>
</pre></div>
</div>
</div>
</div>
</div>


</div>
<div class="bottomnav">
    
<p class="nav">
    <span class="left">
        &laquo; <a href="hive.html">6.6. Hive Connector</a>
    </span>
    <span class="right">
        <a href="hive-security.html">6.8. Hive Security Configuration</a> &raquo;
    </span>
</p>

</div>

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