Author: moon
Date: Mon Aug 24 03:06:23 2015
New Revision: 1697303
URL: http://svn.apache.org/r1697303
Log:
Add PostgreSQL Interpreters Documentation to gh-pages
https://issues.apache.org/jira/browse/ZEPPELIN-252
Added:
incubator/zeppelin/site/docs/interpreter/postgresql.html
Modified:
incubator/zeppelin/site/docs/index.html
Modified: incubator/zeppelin/site/docs/index.html
URL:
http://svn.apache.org/viewvc/incubator/zeppelin/site/docs/index.html?rev=1697303&r1=1697302&r2=1697303&view=diff
==============================================================================
--- incubator/zeppelin/site/docs/index.html (original)
+++ incubator/zeppelin/site/docs/index.html Mon Aug 24 03:06:23 2015
@@ -148,6 +148,10 @@
+
+
+
+
@@ -241,6 +245,10 @@
+
+
+
+
<li><a href="/download.html">Download</a></li>
@@ -317,6 +325,8 @@
<li><a href="../docs/pleasecontribute.html">sh</a></li>
<li><a href="./interpreter/spark.html">spark</a></li>
<li><a href="../docs/pleasecontribute.html">tajo</a></li>
+<li><a href="../docs/pleasecontribute.html">geode</a></li>
+<li><a href="./interpreter/postgresql.html">postgresql, hawq</a></li>
</ul>
<h3>Display System</h3>
Added: incubator/zeppelin/site/docs/interpreter/postgresql.html
URL:
http://svn.apache.org/viewvc/incubator/zeppelin/site/docs/interpreter/postgresql.html?rev=1697303&view=auto
==============================================================================
--- incubator/zeppelin/site/docs/interpreter/postgresql.html (added)
+++ incubator/zeppelin/site/docs/interpreter/postgresql.html Mon Aug 24
03:06:23 2015
@@ -0,0 +1,475 @@
+
+<!DOCTYPE html>
+<html lang="en">
+ <head>
+ <meta charset="utf-8">
+ <title>PostgreSQL and HAWQ Interpreter</title>
+ <meta name="description" content="">
+ <meta name="author" content="The Apache Software Foundation">
+
+ <!-- Enable responsive viewport -->
+ <meta name="viewport" content="width=device-width, initial-scale=1.0">
+
+ <!-- Le HTML5 shim, for IE6-8 support of HTML elements -->
+ <!--[if lt IE 9]>
+ <script
src="http://html5shim.googlecode.com/svn/trunk/html5.js";></script>
+ <![endif]-->
+
+ <!-- Le styles -->
+ <link href="/assets/themes/zeppelin/bootstrap/css/bootstrap.css"
rel="stylesheet">
+ <link href="/assets/themes/zeppelin/css/style.css?body=1" rel="stylesheet"
type="text/css">
+ <link href="/assets/themes/zeppelin/css/syntax.css" rel="stylesheet"
type="text/css" media="screen" />
+ <!-- Le fav and touch icons -->
+ <!-- Update these with your own images
+ <link rel="shortcut icon" href="images/favicon.ico">
+ <link rel="apple-touch-icon" href="images/apple-touch-icon.png">
+ <link rel="apple-touch-icon" sizes="72x72"
href="images/apple-touch-icon-72x72.png">
+ <link rel="apple-touch-icon" sizes="114x114"
href="images/apple-touch-icon-114x114.png">
+ -->
+
+ <!-- atom & rss feed -->
+ <link href="/atom.xml" type="application/atom+xml" rel="alternate"
title="Sitewide ATOM Feed">
+ <link href="/rss.xml" type="application/rss+xml" rel="alternate"
title="Sitewide RSS Feed">
+ </head>
+
+ <body>
+
+ <div class="navbar navbar-inverse navbar-fixed-top" role="navigation">
+ <div class="container">
+ <div class="navbar-header">
+ <button type="button" class="navbar-toggle" data-toggle="collapse"
data-target=".navbar-collapse">
+ <span class="sr-only">Toggle navigation</span>
+ <span class="icon-bar"></span>
+ <span class="icon-bar"></span>
+ <span class="icon-bar"></span>
+ </button>
+ <a class="navbar-brand" href="/">
+ <img src="/assets/themes/zeppelin/img/zeppelin_logo.png"
width="50" alt="I'm zeppelin">
+ Apache Zeppelin <small>(incubating)</small>
+ </a>
+ </div>
+ <nav class="navbar-collapse collapse" role="navigation">
+ <ul class="nav navbar-nav">
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ </ul>
+ <ul class="nav navbar-nav navbar-right">
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li><a href="/community.html">Community</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li><a href="/docs/index.html">Docs</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li><a href="/download.html">Download</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li><a
href="https://github.com/apache/incubator-zeppelin";>GitHub</a></li>
+ <li id="apache">
+ <a href="#" data-toggle="dropdown"
class="dropdown-toggle">Apache<b class="caret"></b></a>
+ <ul class="dropdown-menu">
+ <li><a
href="http://www.apache.org/foundation/how-it-works.html";>Apache Software
Foundation</a></li>
+ <li><a href="http://www.apache.org/licenses/";>Apache
License</a></li>
+ <li><a
href="http://www.apache.org/foundation/sponsorship.html";>Sponsorship</a></li>
+ <li><a
href="http://www.apache.org/foundation/thanks.html";>Thanks</a></li>
+ </ul>
+ </li>
+ </ul>
+ </nav><!--/.navbar-collapse -->
+ </div>
+ </div>
+
+
+
+
+ <div class="container">
+
+<!--<div class="hero-unit PostgreSQL and HAWQ Interpreter">
+ <h1></h1>
+</div>
+-->
+
+<div class="row">
+ <div class="col-md-12">
+ <h2>PostgreSQL, HAWQ Interpreter for Apache Zeppelin</h2>
+
+<p><br/>
+<table class="table-configuration">
+ <tr>
+ <th>Name</th>
+ <th>Class</th>
+ <th>Description</th>
+ </tr>
+ <tr>
+ <td>%psql.sql</td>
+ <td>PostgreSqlInterpreter</td>
+ <td>Provides SQL environment for Postgresql, HAWQ and Greenplum</td>
+ </tr>
+</table></p>
+
+<p><br/>
+<a href="https://www.youtube.com/watch?v=wqXXQhJ5Uk8";><img align="right"
src="http://img.youtube.com/vi/wqXXQhJ5Uk8/0.jpg"; alt="zeppelin-view"
hspace="10" width="250"></img></a></p>
+
+<p>This interpreter seamlessly supports the following SQL data processing
engines:</p>
+
+<ul>
+<li><a href="http://www.postgresql.org/";>PostgreSQL</a> - OSS,
Object-relational database management system (ORDBMS) </li>
+<li><a href="http://pivotal.io/big-data/pivotal-hawq";>Apache HAWQ</a> -
Powerful <a href="https://wiki.apache.org/incubator/HAWQProposal";>Open
Source</a> SQL-On-Hadoop engine. </li>
+<li><a
href="http://pivotal.io/big-data/pivotal-greenplum-database";>Greenplum</a> -
MPP database built on open source PostgreSQL.</li>
+</ul>
+
+<p>This <a href="https://www.youtube.com/watch?v=wqXXQhJ5Uk8";>Video
Tutorial</a> illustrates some of the features provided by the <code>Postgresql
Interpreter</code>.</p>
+
+<h3>Create Interpreter</h3>
+
+<p>By default Zeppelin creates one <code>PSQL</code> instance. You can remove
it or create new instances. </p>
+
+<p>Multiple PSQL instances can be created, each configured to the same or
different backend databases. But over time a <code>Notebook</code> can have
only one PSQL interpreter instance <code>bound</code>. That means you <em>can
not</em> connect to different databases in the same <code>Notebook</code>. This
is a known Zeppelin limitation. </p>
+
+<p>To create new PSQL instance open the <code>Interprter</code> section and
click the <code>+Create</code> button. Pick a <code>Name</code> of your choice
and from the <code>Interpreter</code> drop-down select <code>psql</code>. Then
follow the configuration instructions and <code>Save</code> the new instance.
</p>
+
+<blockquote>
+<p>Note: The <code>Name</code> of the instance is used only to distinct the
instances while binding them to the <code>Notebook</code>. The
<code>Name</code> is irrelevant inside the <code>Notebook</code>. In the
<code>Notebook</code> you must use <code>%psql.sql</code> tag. </p>
+</blockquote>
+
+<h3>Bind to Notebook</h3>
+
+<p>In the <code>Notebook</code> click on the <code>settings</code> icon in the
top right corner. The select/deselect the interpreters to be bound with the
<code>Notebook</code>.</p>
+
+<h3>Configuration</h3>
+
+<p>You can modify the configuration of the PSQL from the
<code>Interpreter</code> section. The PSQL interpreter expenses the following
properties:</p>
+
+<p><table class="table-configuration">
+ <tr>
+ <th>Property Name</th>
+ <th>Description</th>
+ <th>Default Value</th>
+ </tr>
+ <tr>
+ <td>postgresql.url</td>
+ <td>JDBC URL to connect to </td>
+ <td>jdbc:postgresql://localhost:5432</td>
+ </tr>
+ <tr>
+ <td>postgresql.user</td>
+ <td>JDBC user name</td>
+ <td>gpadmin</td>
+ </tr>
+ <tr>
+ <td>postgresql.password</td>
+ <td>JDBC password</td>
+ <td></td>
+ </tr>
+ <tr>
+ <td>postgresql.driver.name</td>
+ <td>JDBC driver name. In this version the driver name is fixed and should
not be changed</td>
+ <td>org.postgresql.Driver</td>
+ </tr>
+ <tr>
+ <td>postgresql.max.result</td>
+ <td>Max number of SQL result to display to prevent the browser
overload</td>
+ <td>1000</td>
+ </tr><br>
+ </table></p>
+
+<h3>How to use</h3>
+<div class="highlight"><pre><code class="text language-text"
data-lang="text">Tip: Use (CTRL + .) for SQL auto-completion.
+</code></pre></div>
+<h4>DDL and SQL commands</h4>
+
+<p>Start the paragraphs with the full <code>%psql.sql</code> prefix tag! The
short notation: <code>%psql</code> would still be able run the queries but the
syntax highlighting and the auto-completions will be disabled. </p>
+
+<p>You can use the standard CREATE / DROP / INSERT commands to create or
modify the data model:</p>
+<div class="highlight"><pre><code class="sql language-sql"
data-lang="sql"><span class="o">%</span><span class="n">psql</span><span
class="p">.</span><span class="k">sql</span>
+<span class="k">drop</span> <span class="k">table</span> <span
class="n">if</span> <span class="k">exists</span> <span
class="n">mytable</span><span class="p">;</span>
+<span class="k">create</span> <span class="k">table</span> <span
class="n">mytable</span> <span class="p">(</span><span class="n">i</span> <span
class="nb">int</span><span class="p">);</span>
+<span class="k">insert</span> <span class="k">into</span> <span
class="n">mytable</span> <span class="k">select</span> <span
class="n">generate_series</span><span class="p">(</span><span
class="mi">1</span><span class="p">,</span> <span class="mi">100</span><span
class="p">);</span>
+</code></pre></div>
+<p>Then in a separate paragraph run the query.</p>
+<div class="highlight"><pre><code class="sql language-sql"
data-lang="sql"><span class="o">%</span><span class="n">psql</span><span
class="p">.</span><span class="k">sql</span>
+<span class="k">select</span> <span class="o">*</span> <span
class="k">from</span> <span class="n">mytable</span><span class="p">;</span>
+</code></pre></div>
+<blockquote>
+<p>Note: You can have multiple queries in the same paragraph but only the
result from the first is displayed. [<a
href="https://issues.apache.org/jira/browse/ZEPPELIN-178";>1</a>], [<a
href="https://issues.apache.org/jira/browse/ZEPPELIN-212";>2</a>].</p>
+</blockquote>
+
+<p>For example, this will execute both queries but only the count result will
be displayed. If you revert the order of the queries the mytable content will
be shown instead.</p>
+<div class="highlight"><pre><code class="sql language-sql"
data-lang="sql"><span class="o">%</span><span class="n">psql</span><span
class="p">.</span><span class="k">sql</span>
+<span class="k">select</span> <span class="k">count</span><span
class="p">(</span><span class="o">*</span><span class="p">)</span> <span
class="k">from</span> <span class="n">mytable</span><span class="p">;</span>
+<span class="k">select</span> <span class="o">*</span> <span
class="k">from</span> <span class="n">mytable</span><span class="p">;</span>
+</code></pre></div>
+<h4>PSQL command line tools</h4>
+
+<p>Use the Shell Interpreter (<code>%sh</code>) to access the command line <a
href="http://www.postgresql.org/docs/9.4/static/app-psql.html";>PSQL</a>
interactively:</p>
+<div class="highlight"><pre><code class="bash language-bash"
data-lang="bash">%sh
+psql -h phd3.localdomain -U gpadmin -p 5432 <span class="s"><<EOF</span>
+<span class="s"> \dn </span>
+<span class="s"> \q</span>
+<span class="s">EOF</span>
+</code></pre></div>
+<p>This will produce output like this:</p>
+<div class="highlight"><pre><code class="text language-text" data-lang="text">
Name | Owner
+--------------------+---------
+ hawq_toolkit | gpadmin
+ information_schema | gpadmin
+ madlib | gpadmin
+ pg_catalog | gpadmin
+ pg_toast | gpadmin
+ public | gpadmin
+ retail_demo | gpadmin
+</code></pre></div>
+<h4>Apply Zeppelin Dynamic Forms</h4>
+
+<p>You can leverage <a
href="https://zeppelin.incubator.apache.org/docs/manual/dynamicform.html";>Zepplein
Dynamic Form</a> inside your queries. You can use both the <code>text
input</code> and <code>select form</code> parametrization features</p>
+<div class="highlight"><pre><code class="sql language-sql"
data-lang="sql"><span class="o">%</span><span class="n">psql</span><span
class="p">.</span><span class="k">sql</span>
+<span class="k">SELECT</span> <span class="err">${</span><span
class="n">group_by</span><span class="err">}</span><span class="p">,</span>
<span class="k">count</span><span class="p">(</span><span
class="o">*</span><span class="p">)</span> <span class="k">as</span> <span
class="k">count</span>
+<span class="k">FROM</span> <span class="n">retail_demo</span><span
class="p">.</span><span class="n">order_lineitems_pxf</span>
+<span class="k">GROUP</span> <span class="k">BY</span> <span
class="err">${</span><span class="n">group_by</span><span
class="o">=</span><span class="n">product_id</span><span
class="p">,</span><span class="n">product_id</span><span
class="o">|</span><span class="n">product_name</span><span
class="o">|</span><span class="n">customer_id</span><span
class="o">|</span><span class="n">store_id</span><span class="err">}</span>
+<span class="k">ORDER</span> <span class="k">BY</span> <span
class="k">count</span> <span class="err">${</span><span
class="k">order</span><span class="o">=</span><span class="k">DESC</span><span
class="p">,</span><span class="k">DESC</span><span class="o">|</span><span
class="k">ASC</span><span class="err">}</span>
+<span class="k">LIMIT</span> <span class="err">${</span><span
class="k">limit</span><span class="o">=</span><span class="mi">10</span><span
class="err">}</span><span class="p">;</span>
+</code></pre></div>
+<h4>Example HAWQ PXF/HDFS Tables</h4>
+
+<p>Create HAWQ external table that read data from tab-separated-value data in
HDFS.</p>
+<div class="highlight"><pre><code class="sql language-sql"
data-lang="sql"><span class="o">%</span><span class="n">psql</span><span
class="p">.</span><span class="k">sql</span>
+<span class="k">CREATE</span> <span class="k">EXTERNAL</span> <span
class="k">TABLE</span> <span class="n">retail_demo</span><span
class="p">.</span><span class="n">payment_methods_pxf</span> <span
class="p">(</span>
+ <span class="n">payment_method_id</span> <span
class="nb">smallint</span><span class="p">,</span>
+ <span class="n">payment_method_code</span> <span class="nb">character</span>
<span class="nb">varying</span><span class="p">(</span><span
class="mi">20</span><span class="p">)</span>
+<span class="p">)</span> <span class="k">LOCATION</span> <span
class="p">(</span><span
class="s1">'pxf://${NAME_NODE_HOST}:50070/retail_demo/payment_methods.tsv.gz?profile=HdfsTextSimple'</span><span
class="p">)</span> <span class="n">FORMAT</span> <span
class="s1">'TEXT'</span> <span class="p">(</span><span
class="k">DELIMITER</span> <span class="o">=</span> <span
class="n">E</span><span class="s1">'\t'</span><span class="p">);</span>
+</code></pre></div>
+<p>And retrieve content</p>
+<div class="highlight"><pre><code class="sql language-sql"
data-lang="sql"><span class="o">%</span><span class="n">psql</span><span
class="p">.</span><span class="k">sql</span>
+<span class="n">seelect</span> <span class="o">*</span> <span
class="k">from</span> <span class="n">retail_demo</span><span
class="p">.</span><span class="n">payment_methods_pxf</span>
+</code></pre></div>
+<h3>Auto-completion</h3>
+
+<p>The PSQL Interpreter provides a basic auto-completion functionality. On
<code>(Ctrl+.)</code> it list the most relevant suggesntions in a pop-up
window. In addition to the SQL keyword the interpter provides suggestions for
the Schema, Table, Column names as well. </p>
+
+ </div>
+</div>
+
+
+ <hr>
+ <footer>
+ <!-- <p>© 2015 The Apache Software Foundation</p>-->
+ </footer>
+ </div>
+
+
+ <script src="https://code.jquery.com/jquery-1.10.2.min.js";></script>
+
+ <script
src="/assets/themes/zeppelin/bootstrap/js/bootstrap.min.js"></script>
+ </body>
+</html>
+
![http://img.youtube.com/vi/wqXXQhJ5Uk8/0.jpg"](https://img.youtube.com/vi/wqXXQhJ5Uk8/0.jpg"){kind=link}