321/connector/phoenix.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.18. Phoenix 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.19. PostgreSQL Connector" href="postgresql.html" />
    <link rel="prev" title="6.17. MySQL Connector" href="mysql.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.18. Phoenix Connector</span></h2>
</div>
<div class="topnav">
    
<p class="nav">
    <span class="left">
        &laquo; <a href="mysql.html">6.17. MySQL Connector</a>
    </span>
    <span class="right">
        <a href="postgresql.html">6.19. PostgreSQL Connector</a> &raquo;
    </span>
</p>

</div>
<div class="content">
    
  <div class="section" id="phoenix-connector">
<h1>6.18. Phoenix Connector</h1>
<p>The Phoenix connector allows querying data stored in HBase using Apache Phoenix.</p>
<div class="section" id="compatibility">
<h2>Compatibility</h2>
<p>The Phoenix connector is compatible with all Phoenix versions starting from 4.14.1.</p>
</div>
<div class="section" id="configuration">
<h2>Configuration</h2>
<p>To configure the Phoenix connector, create a catalog properties file
<code class="docutils literal"><span class="pre">etc/catalog/phoenix.properties</span></code> with the following contents,
replacing <code class="docutils literal"><span class="pre">host1,host2,host3</span></code> with a comma-separated list of the ZooKeeper
nodes used for discovery of the HBase cluster:</p>
<div class="highlight-none"><div class="highlight"><pre><span></span>connector.name=phoenix
phoenix.connection-url=jdbc:phoenix:host1,host2,host3:2181:/hbase
phoenix.config.resources=/path/to/hbase-site.xml
</pre></div>
</div>
<p>The optional paths to Hadoop resource files such as <code class="docutils literal"><span class="pre">hbase-site.xml</span></code> are used
to load custom Phoenix client connection properties.</p>
</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="35%" />
<col width="7%" />
<col width="58%" />
</colgroup>
<thead valign="bottom">
<tr class="row-odd"><th class="head">Property Name</th>
<th class="head">Required</th>
<th class="head">Description</th>
</tr>
</thead>
<tbody valign="top">
<tr class="row-even"><td><code class="docutils literal"><span class="pre">phoenix.connection-url</span></code></td>
<td>Yes</td>
<td><code class="docutils literal"><span class="pre">jdbc:phoenix[:zk_quorum][:zk_port][:zk_hbase_path]</span></code>.
The <code class="docutils literal"><span class="pre">zk_quorum</span></code> is a comma separated list of ZooKeeper servers.
The <code class="docutils literal"><span class="pre">zk_port</span></code> is the ZooKeeper port. The <code class="docutils literal"><span class="pre">zk_hbase_path</span></code> is the HBase
root znode path, that is configurable using <code class="docutils literal"><span class="pre">hbase-site.xml</span></code>.  By
default the location is <code class="docutils literal"><span class="pre">/hbase</span></code></td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">phoenix.config.resources</span></code></td>
<td>No</td>
<td>Comma-separated list of configuration files (e.g. <code class="docutils literal"><span class="pre">hbase-site.xml</span></code>) to use for
connection properties.  These files must exist on the machines running Presto.</td>
</tr>
</tbody>
</table>
</div>
<div class="section" id="querying-phoenix-tables">
<h2>Querying Phoenix Tables</h2>
<p>The default empty schema in Phoenix maps to a schema named <code class="docutils literal"><span class="pre">default</span></code> in Presto.
You can see the available Phoenix 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">phoenix</span><span class="p">;</span>
</pre></div>
</div>
<p>If you have a Phoenix 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">phoenix</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> schema
using either of the following:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">DESCRIBE</span> <span class="n">phoenix</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">phoenix</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">phoenix</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">phoenix</span></code> in the above examples.</p>
</div>
<div class="section" id="data-types">
<h2>Data types</h2>
<p>The data type mappings are as follows:</p>
<table border="1" class="docutils">
<colgroup>
<col width="67%" />
<col width="33%" />
</colgroup>
<thead valign="bottom">
<tr class="row-odd"><th class="head">Phoenix</th>
<th class="head">Presto</th>
</tr>
</thead>
<tbody valign="top">
<tr class="row-even"><td><code class="docutils literal"><span class="pre">BOOLEAN</span></code></td>
<td>(same)</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">BIGINT</span></code></td>
<td>(same)</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">INTEGER</span></code></td>
<td>(same)</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">SMALLINT</span></code></td>
<td>(same)</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">TINYINT</span></code></td>
<td>(same)</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">DOUBLE</span></code></td>
<td>(same)</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">FLOAT</span></code></td>
<td><code class="docutils literal"><span class="pre">REAL</span></code></td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">DECIMAL</span></code></td>
<td>(same)</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">BINARY</span></code></td>
<td><code class="docutils literal"><span class="pre">VARBINARY</span></code></td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">VARBINARY</span></code></td>
<td>(same)</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">DATE</span></code></td>
<td>(same)</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">TIME</span></code></td>
<td>(same)</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">VARCHAR</span></code></td>
<td>(same)</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">CHAR</span></code></td>
<td>(same)</td>
</tr>
</tbody>
</table>
<p>The Phoenix fixed length <code class="docutils literal"><span class="pre">BINARY</span></code> data type is mapped to the Presto
variable length <code class="docutils literal"><span class="pre">VARBINARY</span></code> data type. There is no way to create a
Phoenix table in Presto that uses the <code class="docutils literal"><span class="pre">BINARY</span></code> data type, as Presto
does not have an equivalent type.</p>
</div>
<div class="section" id="table-properties-phoenix">
<h2>Table Properties - Phoenix</h2>
<p>Table property usage 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">myschema</span><span class="p">.</span><span class="n">scientists</span> <span class="p">(</span>
  <span class="n">recordkey</span> <span class="nb">VARCHAR</span><span class="p">,</span>
  <span class="n">birthday</span> <span class="nb">DATE</span><span class="p">,</span>
  <span class="n">name</span> <span class="nb">VARCHAR</span><span class="p">,</span>
  <span class="n">age</span> <span class="nb">BIGINT</span>
<span class="p">)</span>
<span class="k">WITH</span> <span class="p">(</span>
  <span class="n">rowkeys</span> <span class="o">=</span> <span class="s1">&#39;recordkey,birthday&#39;</span><span class="p">,</span>
  <span class="n">salt_buckets</span> <span class="o">=</span> <span class="mi">10</span>
<span class="p">);</span>
</pre></div>
</div>
<p>The following are supported Phoenix table properties from <a class="reference external" href="https://phoenix.apache.org/language/index.html#options">https://phoenix.apache.org/language/index.html#options</a></p>
<table border="1" class="docutils">
<colgroup>
<col width="18%" />
<col width="10%" />
<col width="72%" />
</colgroup>
<thead valign="bottom">
<tr class="row-odd"><th class="head">Property Name</th>
<th class="head">Default Value</th>
<th class="head">Description</th>
</tr>
</thead>
<tbody valign="top">
<tr class="row-even"><td><code class="docutils literal"><span class="pre">rowkeys</span></code></td>
<td><code class="docutils literal"><span class="pre">ROWKEY</span></code></td>
<td>Comma-separated list of primary key columns.  See further description below</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">split_on</span></code></td>
<td>(none)</td>
<td>List of keys to presplit the table on.
See <a class="reference external" href="https://phoenix.apache.org/language/index.html#split_point">Split Point</a>.</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">salt_buckets</span></code></td>
<td>(none)</td>
<td>Number of salt buckets for this table.</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">disable_wal</span></code></td>
<td>false</td>
<td>Whether to disable WAL writes in HBase for this table.</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">immutable_rows</span></code></td>
<td>false</td>
<td>Declares whether this table has rows which are write-once, append-only.</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">default_column_family</span></code></td>
<td><code class="docutils literal"><span class="pre">0</span></code></td>
<td>Default column family name to use for this table.</td>
</tr>
</tbody>
</table>
<div class="section" id="rowkeys">
<h3><code class="docutils literal"><span class="pre">rowkeys</span></code></h3>
<p>This is a comma-separated list of columns to be used as the table’s primary key. If not specified, a <code class="docutils literal"><span class="pre">BIGINT</span></code> primary key column named <code class="docutils literal"><span class="pre">ROWKEY</span></code> is generated
, as well as a sequence with the same name as the table suffixed with <code class="docutils literal"><span class="pre">_seq</span></code> (i.e. <code class="docutils literal"><span class="pre">&lt;schema&gt;.&lt;table&gt;_seq</span></code>)
, which is used to automatically populate the <code class="docutils literal"><span class="pre">ROWKEY</span></code> for each row during insertion.</p>
</div>
</div>
<div class="section" id="table-properties-hbase">
<h2>Table Properties - HBase</h2>
<p>The following are the supported HBase table properties that are passed through by Phoenix during table creation.
Use them in the the same way as above: in the <code class="docutils literal"><span class="pre">WITH</span></code> clause of the <code class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code> statement.</p>
<table border="1" class="docutils">
<colgroup>
<col width="18%" />
<col width="10%" />
<col width="72%" />
</colgroup>
<thead valign="bottom">
<tr class="row-odd"><th class="head">Property Name</th>
<th class="head">Default Value</th>
<th class="head">Description</th>
</tr>
</thead>
<tbody valign="top">
<tr class="row-even"><td><code class="docutils literal"><span class="pre">versions</span></code></td>
<td><code class="docutils literal"><span class="pre">1</span></code></td>
<td>The maximum number of versions of each cell to keep.</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">min_versions</span></code></td>
<td><code class="docutils literal"><span class="pre">0</span></code></td>
<td>The minimum number of cell versions to keep.</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">compression</span></code></td>
<td><code class="docutils literal"><span class="pre">NONE</span></code></td>
<td>Compression algorithm to use.  Valid values are <code class="docutils literal"><span class="pre">NONE</span></code> (default), <code class="docutils literal"><span class="pre">SNAPPY</span></code>, <code class="docutils literal"><span class="pre">LZO</span></code>, <code class="docutils literal"><span class="pre">LZ4</span></code>, or <code class="docutils literal"><span class="pre">GZ</span></code>.</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">ttl</span></code></td>
<td><code class="docutils literal"><span class="pre">FOREVER</span></code></td>
<td>Time To Live for each cell.</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">bloomfilter</span></code></td>
<td><code class="docutils literal"><span class="pre">ROW</span></code></td>
<td>Bloomfilter to use. Valid values are <code class="docutils literal"><span class="pre">NONE</span></code>, <code class="docutils literal"><span class="pre">ROW</span></code> (default), or <code class="docutils literal"><span class="pre">ROWCOL</span></code>.</td>
</tr>
</tbody>
</table>
</div>
</div>


</div>
<div class="bottomnav">
    
<p class="nav">
    <span class="left">
        &laquo; <a href="mysql.html">6.17. MySQL Connector</a>
    </span>
    <span class="right">
        <a href="postgresql.html">6.19. PostgreSQL Connector</a> &raquo;
    </span>
</p>

</div>

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