Added: tajo/site/docs/0.11.0/sql_language/joins.html URL: http://svn.apache.org/viewvc/tajo/site/docs/0.11.0/sql_language/joins.html?rev=1710773&view=auto ============================================================================== --- tajo/site/docs/0.11.0/sql_language/joins.html (added) +++ tajo/site/docs/0.11.0/sql_language/joins.html Tue Oct 27 11:04:33 2015 @@ -0,0 +1,400 @@ + + +<!DOCTYPE html> +<!--[if IE 8]><html class="no-js lt-ie9" lang="en" > <![endif]--> +<!--[if gt IE 8]><!--> <html class="no-js" lang="en" > <!--<![endif]--> +<head> + <meta charset="utf-8"> + <meta name="viewport" content="width=device-width, initial-scale=1.0"> + + <title>Joins — Apache Tajo 0.11.0 documentation</title> + + + + + + + <link href='https://fonts.googleapis.com/css?family=Lato:400,700|Roboto+Slab:400,700|Inconsolata:400,700' rel='stylesheet' type='text/css'> + + + + + + + + + + <link rel="stylesheet" href="../_static/css/theme.css" type="text/css" /> + + + + <link rel="top" title="Apache Tajo 0.11.0 documentation" href="../index.html"/> + <link rel="up" title="SQL Language" href="../sql_language.html"/> + <link rel="next" title="SQL Expressions" href="sql_expression.html"/> + <link rel="prev" title="Queries" href="queries.html"/> + + + <script src="https://cdnjs.cloudflare.com/ajax/libs/modernizr/2.6.2/modernizr.min.js";></script> + +</head> + +<body class="wy-body-for-nav" role="document"> + + <div class="wy-grid-for-nav"> + + + <nav data-toggle="wy-nav-shift" class="wy-nav-side"> + <div class="wy-side-nav-search"> + <a href="../index.html" class="fa fa-home"> Apache Tajo</a> + <div role="search"> + <form id ="rtd-search-form" class="wy-form" action="../search.html" method="get"> + <input type="text" name="q" placeholder="Search docs" /> + <input type="hidden" name="check_keywords" value="yes" /> + <input type="hidden" name="area" value="default" /> + </form> +</div> + </div> + + <div class="wy-menu wy-menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation"> + + + <ul class="current"> +<li class="toctree-l1"><a class="reference internal" href="../introduction.html">Introduction</a></li> +<li class="toctree-l1"><a class="reference internal" href="../getting_started.html">Getting Started</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../getting_started.html#prerequisites">Prerequisites</a></li> +<li class="toctree-l2"><a class="reference internal" href="../getting_started.html#dowload-and-unpack-the-source-code">Dowload and unpack the source code</a></li> +<li class="toctree-l2"><a class="reference internal" href="../getting_started.html#build-source-code">Build source code</a></li> +<li class="toctree-l2"><a class="reference internal" href="../getting_started.html#setting-up-a-local-tajo-cluster">Setting up a local Tajo cluster</a></li> +<li class="toctree-l2"><a class="reference internal" href="../getting_started.html#first-query-execution">First query execution</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../configuration.html">Configuration</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../configuration/preliminary.html">Preliminary</a></li> +<li class="toctree-l2"><a class="reference internal" href="../configuration/cluster_setup.html">Cluster Setup</a></li> +<li class="toctree-l2"><a class="reference internal" href="../configuration/tajo_master_configuration.html">Tajo Master Configuration</a></li> +<li class="toctree-l2"><a class="reference internal" href="../configuration/worker_configuration.html">Worker Configuration</a></li> +<li class="toctree-l2"><a class="reference internal" href="../configuration/catalog_configuration.html">Catalog Configuration</a></li> +<li class="toctree-l2"><a class="reference internal" href="../configuration/ha_configuration.html">High Availability for TajoMaster</a></li> +<li class="toctree-l2"><a class="reference internal" href="../configuration/service_config_defaults.html">Cluster Service Configuration Defaults</a></li> +<li class="toctree-l2"><a class="reference internal" href="../configuration/tajo-site-xml.html">The tajo-site.xml File</a></li> +<li class="toctree-l2"><a class="reference internal" href="../configuration/catalog-site-xml.html">The catalog-site.xml File</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../tsql.html">Tajo Shell (TSQL)</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../tsql/meta_command.html">Meta Commands</a></li> +<li class="toctree-l2"><a class="reference internal" href="../tsql/dfs_command.html">Executing HDFS commands</a></li> +<li class="toctree-l2"><a class="reference internal" href="../tsql/variables.html">Session Variables</a></li> +<li class="toctree-l2"><a class="reference internal" href="../tsql/admin_command.html">Administration Commands</a></li> +<li class="toctree-l2"><a class="reference internal" href="../tsql/intro.html">Introducing to TSQL</a></li> +<li class="toctree-l2"><a class="reference internal" href="../tsql/single_command.html">Executing a single command</a></li> +<li class="toctree-l2"><a class="reference internal" href="../tsql/execute_file.html">Executing Queries from Files</a></li> +<li class="toctree-l2"><a class="reference internal" href="../tsql/background_command.html">Executing as background process</a></li> +</ul> +</li> +<li class="toctree-l1 current"><a class="reference internal" href="../sql_language.html">SQL Language</a><ul class="current"> +<li class="toctree-l2"><a class="reference internal" href="data_model.html">Data Model</a></li> +<li class="toctree-l2"><a class="reference internal" href="ddl.html">Data Definition Language</a></li> +<li class="toctree-l2"><a class="reference internal" href="insert.html">INSERT (OVERWRITE) INTO</a></li> +<li class="toctree-l2"><a class="reference internal" href="alter_table.html">ALTER TABLE</a></li> +<li class="toctree-l2"><a class="reference internal" href="queries.html">Queries</a></li> +<li class="toctree-l2 current"><a class="current reference internal" href="">Joins</a></li> +<li class="toctree-l2"><a class="reference internal" href="sql_expression.html">SQL Expressions</a></li> +<li class="toctree-l2"><a class="reference internal" href="predicates.html">Predicates</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../time_zone.html">Time Zone</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../time_zone.html#server-cluster-time-zone">Server Cluster Time Zone</a></li> +<li class="toctree-l2"><a class="reference internal" href="../time_zone.html#table-time-zone">Table Time Zone</a></li> +<li class="toctree-l2"><a class="reference internal" href="../time_zone.html#client-time-zone">Client Time Zone</a></li> +<li class="toctree-l2"><a class="reference internal" href="../time_zone.html#time-zone-id">Time Zone ID</a></li> +<li class="toctree-l2"><a class="reference internal" href="../time_zone.html#examples-of-time-zone">Examples of Time Zone</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../functions.html">Functions</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../functions.html#built-in-scalar-functions">Built-in Scalar Functions</a></li> +<li class="toctree-l2"><a class="reference internal" href="../functions.html#built-in-aggregation-functions">Built-in Aggregation Functions</a></li> +<li class="toctree-l2"><a class="reference internal" href="../functions.html#built-in-window-functions">Built-in Window Functions</a></li> +<li class="toctree-l2"><a class="reference internal" href="../functions.html#user-defined-functions">User-defined Functions</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../table_management.html">Table Management</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../table_management/table_overview.html">Overview of Tajo Tables</a></li> +<li class="toctree-l2"><a class="reference internal" href="../table_management/tablespaces.html">Tablespaces</a></li> +<li class="toctree-l2"><a class="reference internal" href="../table_management/file_formats.html">File Formats</a></li> +<li class="toctree-l2"><a class="reference internal" href="../table_management/compression.html">Compression</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../table_partitioning.html">Table Partitioning</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../partitioning/intro_to_partitioning.html">Introduction to Partitioning</a></li> +<li class="toctree-l2"><a class="reference internal" href="../partitioning/column_partitioning.html">Column Partitioning</a></li> +<li class="toctree-l2"><a class="reference internal" href="../partitioning/range_partitioning.html">Range Partitioning</a></li> +<li class="toctree-l2"><a class="reference internal" href="../partitioning/hash_partitioning.html">Hash Partitioning</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../storage_plugins.html">Storage Plugin</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../storage_plugins/overview.html">Storage Plugin Overview</a></li> +<li class="toctree-l2"><a class="reference internal" href="../storage_plugins/postgresql.html">PostgreSQL Storage Handler</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../index_overview.html">Index (Experimental Feature)</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../index/types.html">Index Types</a></li> +<li class="toctree-l2"><a class="reference internal" href="../index/how_to_use.html">How to use index?</a></li> +<li class="toctree-l2"><a class="reference internal" href="../index/future_work.html">Future Works</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../backup_and_restore.html">Backup and Restore</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../backup_and_restore/catalog.html">Backup and Restore Catalog</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../hive_integration.html">Hive Integration</a></li> +<li class="toctree-l1"><a class="reference internal" href="../hbase_integration.html">HBase Integration</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../hbase_integration.html#create-table">CREATE TABLE</a></li> +<li class="toctree-l2"><a class="reference internal" href="../hbase_integration.html#drop-table">DROP TABLE</a></li> +<li class="toctree-l2"><a class="reference internal" href="../hbase_integration.html#insert-overwrite-into">INSERT (OVERWRITE) INTO</a></li> +<li class="toctree-l2"><a class="reference internal" href="../hbase_integration.html#usage">Usage</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../swift_integration.html">OpenStack Swift Integration</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../swift_integration.html#swift-configuration">Swift configuration</a></li> +<li class="toctree-l2"><a class="reference internal" href="../swift_integration.html#hadoop-configurations">Hadoop configurations</a></li> +<li class="toctree-l2"><a class="reference internal" href="../swift_integration.html#tajo-configuration">Tajo configuration</a></li> +<li class="toctree-l2"><a class="reference internal" href="../swift_integration.html#querying-on-swift">Querying on Swift</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../jdbc_driver.html">Tajo JDBC Driver</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../jdbc_driver.html#how-to-get-jdbc-driver">How to get JDBC driver</a></li> +<li class="toctree-l2"><a class="reference internal" href="../jdbc_driver.html#setting-the-classpath">Setting the CLASSPATH</a></li> +<li class="toctree-l2"><a class="reference internal" href="../jdbc_driver.html#connecting-to-the-tajo-cluster-instance">Connecting to the Tajo cluster instance</a></li> +<li class="toctree-l2"><a class="reference internal" href="../jdbc_driver.html#connection-parameters">Connection Parameters</a></li> +<li class="toctree-l2"><a class="reference internal" href="../jdbc_driver.html#an-example-jdbc-client">An Example JDBC Client</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../tajo_client_api.html">Tajo Client API</a></li> +<li class="toctree-l1"><a class="reference internal" href="../faq.html">FAQ</a></li> +</ul> + + + </div> + + </nav> + + <section data-toggle="wy-nav-shift" class="wy-nav-content-wrap"> + + + <nav class="wy-nav-top" role="navigation" aria-label="top navigation"> + <i data-toggle="wy-nav-top" class="fa fa-bars"></i> + <a href="../index.html">Apache Tajo</a> + </nav> + + + + <div class="wy-nav-content"> + <div class="rst-content"> + <div role="navigation" aria-label="breadcrumbs navigation"> + <ul class="wy-breadcrumbs"> + <li><a href="../index.html">Docs</a> »</li> + + <li><a href="../sql_language.html">SQL Language</a> »</li> + + <li>Joins</li> + <li class="wy-breadcrumbs-aside"> + + <a href="../_sources/sql_language/joins.txt" rel="nofollow"> View page source</a> + + </li> + </ul> + <hr/> +</div> + <div role="main"> + + <div class="section" id="joins"> +<h1>Joins<a class="headerlink" href="#joins" title="Permalink to this headline">¶</a></h1> +<div class="section" id="overview"> +<h2>Overview<a class="headerlink" href="#overview" title="Permalink to this headline">¶</a></h2> +<p>In Tajo, a single query can accesses multiple rows of the same or different relations at one time. This query is called <em>join</em>. +Currently, Tajo supports cross, inner, and outer joins.</p> +<p>A join query can involve multiple relations in the <code class="docutils literal"><span class="pre">FROM</span></code> clause according to the following rule.</p> +<div class="highlight-sql"><div class="highlight"><pre><span class="k">FROM</span> <span class="n">joined_table</span> <span class="p">[,</span> <span class="n">joined_table</span> <span class="p">[,</span> <span class="p">...]</span> <span class="p">]</span> +</pre></div> +</div> +<p>, where <code class="docutils literal"><span class="pre">joined_table</span></code> is:</p> +<div class="highlight-sql"><div class="highlight"><pre><span class="n">table_reference</span> <span class="n">join_type</span> <span class="n">table_reference</span> <span class="p">[</span> <span class="k">ON</span> <span class="n">join_condition</span> <span class="p">]</span> +</pre></div> +</div> +<p><code class="docutils literal"><span class="pre">join_type</span></code> can be one of the followings.</p> +<div class="highlight-sql"><div class="highlight"><pre>CROSS JOIN +[ NATURAL ] [ INNER ] JOIN +{ LEFT | RIGHT | FULL } OUTER JOIN +</pre></div> +</div> +<p><code class="docutils literal"><span class="pre">join_condition</span></code> can be specified in the <code class="docutils literal"><span class="pre">WHERE</span></code> clause as well as the <code class="docutils literal"><span class="pre">ON</span></code> clause.</p> +<p>For more information, please refer to <a class="reference internal" href="predicates.html"><em>Predicates</em></a>.</p> +<div class="admonition note"> +<p class="first admonition-title">Note</p> +<p class="last">Currently, Tajo cannot natively support non-equality conditions. It means that inner joins with non-equality conditions will be executed with cross joins. Outer joins with non-equality conditions cannot be executed yet.</p> +</div> +</div> +<div class="section" id="examples"> +<h2>Examples<a class="headerlink" href="#examples" title="Permalink to this headline">¶</a></h2> +<ul class="simple"> +<li>For inner and outer joins, only equality conditions are allowed as follows. For inner joins, implicit join notation is allowed.</li> +</ul> +<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">a</span><span class="p">.</span><span class="o">*</span> <span class="k">FROM</span> <span class="n">a</span><span class="p">,</span> <span class="n">b</span> <span class="k">WHERE</span> <span class="n">a</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">id</span> + +<span class="k">SELECT</span> <span class="n">a</span><span class="p">.</span><span class="o">*</span> <span class="k">FROM</span> <span class="n">a</span> <span class="k">JOIN</span> <span class="n">b</span> <span class="k">ON</span> <span class="p">(</span><span class="n">a</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">id</span><span class="p">)</span> + +<span class="k">SELECT</span> <span class="n">a</span><span class="p">.</span><span class="o">*</span> <span class="k">FROM</span> <span class="n">a</span> <span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">b</span> <span class="k">ON</span> <span class="p">(</span><span class="n">a</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">id</span> <span class="k">AND</span> <span class="n">a</span><span class="p">.</span><span class="k">type</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="k">type</span><span class="p">)</span> +</pre></div> +</div> +<p>However, the following query will be executed with CROSS join, thereby taking a very long time.</p> +<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">a</span><span class="p">.</span><span class="o">*</span> <span class="k">FROM</span> <span class="n">a</span> <span class="k">JOIN</span> <span class="n">b</span> <span class="k">ON</span> <span class="p">(</span><span class="n">a</span><span class="p">.</span><span class="n">id</span> <span class="o"><></span> <span class="n">b</span><span class="p">.</span><span class="n">id</span><span class="p">)</span> +</pre></div> +</div> +<p>In addition, the following query is not allowed.</p> +<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">a</span><span class="p">.</span><span class="o">*</span> <span class="k">FROM</span> <span class="n">a</span> <span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">b</span> <span class="k">ON</span> <span class="p">(</span><span class="n">a</span><span class="p">.</span><span class="n">id</span> <span class="o">></span> <span class="n">b</span><span class="p">.</span><span class="n">id</span><span class="p">)</span> +</pre></div> +</div> +<ul class="simple"> +<li>You can join more than 2 tables in a query with multiple join types.</li> +</ul> +<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">a</span><span class="p">.</span><span class="o">*</span> <span class="k">FROM</span> <span class="n">a</span><span class="p">,</span> <span class="n">b</span><span class="p">,</span> <span class="k">c</span> <span class="k">WHERE</span> <span class="n">a</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">id</span> <span class="k">AND</span> <span class="n">b</span><span class="p">.</span><span class="n">id2</span> <span class="o">=</span> <span class="k">c</span><span class="p">.</span><span class="n">id2</span> + +<span class="k">SELECT</span> <span class="n">a</span><span class="p">.</span><span class="o">*</span> <span class="k">FROM</span> <span class="n">a</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">b</span> <span class="k">ON</span> <span class="n">a</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">id</span> <span class="k">FULL</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="k">c</span> <span class="k">ON</span> <span class="n">b</span><span class="p">.</span><span class="n">id2</span> <span class="o">=</span> <span class="k">c</span><span class="p">.</span><span class="n">id2</span> +</pre></div> +</div> +<p>When a query involves three or more tables, there may be a lot of possible join orders. Tajo automatically finds the best join order regardless of the input order. For example, suppose that relation <code class="docutils literal"><span class="pre">b</span></code> is larger than relation <code class="docutils literal"><span class="pre">a</span></code>, and in turn, the relation <code class="docutils literal"><span class="pre">c</span></code> is larger than relation <code class="docutils literal"><span class="pre">b</span></code>. The query</p> +<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">a</span><span class="p">.</span><span class="o">*</span> <span class="k">FROM</span> <span class="k">c</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">b</span> <span class="k">ON</span> <span class="n">b</span><span class="p">.</span><span class="n">id2</span> <span class="o">=</span> <span class="k">c</span><span class="p">.</span><span class="n">id2</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">a</span> <span class="k">ON</span> <span class="n">a</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">id</span> +</pre></div> +</div> +<p>is rewritten to</p> +<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">a</span><span class="p">.</span><span class="o">*</span> <span class="k">FROM</span> <span class="n">a</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">b</span> <span class="k">ON</span> <span class="n">a</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">id</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="k">c</span> <span class="k">ON</span> <span class="n">b</span><span class="p">.</span><span class="n">id2</span> <span class="o">=</span> <span class="k">c</span><span class="p">.</span><span class="n">id2</span> +</pre></div> +</div> +<p>because early join of small relations accelerates the query speed.</p> +<ul class="simple"> +<li>Tajo also supports natural join. When relations have a common column name, they are joined with an equality condition on that column even though it is not explicitly declared in the query. For example,</li> +</ul> +<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">a</span><span class="p">.</span><span class="o">*</span> <span class="k">FROM</span> <span class="n">a</span> <span class="k">JOIN</span> <span class="n">b</span> +</pre></div> +</div> +<p>is rewritten to</p> +<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">a</span><span class="p">.</span><span class="o">*</span> <span class="k">FROM</span> <span class="n">a</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">b</span> <span class="k">ON</span> <span class="n">a</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">id</span> +</pre></div> +</div> +</div> +<div class="section" id="join-optimization"> +<h2>Join Optimization<a class="headerlink" href="#join-optimization" title="Permalink to this headline">¶</a></h2> +<p>Join is one of the most expensive operations in relational world. +Tajo adopts several optimization techniques to improve its join performance.</p> +<div class="section" id="join-ordering"> +<h3>Join ordering<a class="headerlink" href="#join-ordering" title="Permalink to this headline">¶</a></h3> +<p>Join ordering is one of the important techniques for join performance improvement. +Basically, joining multiple relations is left-associative. However, query performance can be significantly changed according to which order is chosen for the join execution.</p> +<p>To find the best join order, Tajo’s cost-based optimizer considers join conditions, join types, and the size of input relations. +In addition, it considers the computation cost of consecutive joins so that the shape of query plan forms a bushy tree.</p> +<p>For example, suppose that there are 4 relations <code class="docutils literal"><span class="pre">a</span></code> (10), <code class="docutils literal"><span class="pre">b</span></code> (20), <code class="docutils literal"><span class="pre">c</span></code> (30), and <code class="docutils literal"><span class="pre">d</span></code> (40) where the numbers within brackets represent the relation size. +The following query</p> +<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> + <span class="o">*</span> +<span class="k">FROM</span> + <span class="n">a</span><span class="p">,</span> <span class="n">b</span><span class="p">,</span> <span class="k">c</span><span class="p">,</span> <span class="n">d</span> +<span class="k">WHERE</span> + <span class="n">a</span><span class="p">.</span><span class="n">id1</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">id1</span> <span class="k">AND</span> + <span class="n">a</span><span class="p">.</span><span class="n">id4</span> <span class="o">=</span> <span class="n">d</span><span class="p">.</span><span class="n">id4</span> <span class="k">AND</span> + <span class="n">b</span><span class="p">.</span><span class="n">id2</span> <span class="o">=</span> <span class="k">c</span><span class="p">.</span><span class="n">id2</span> <span class="k">AND</span> + <span class="k">c</span><span class="p">.</span><span class="n">id3</span> <span class="o">=</span> <span class="n">d</span><span class="p">.</span><span class="n">id3</span> +</pre></div> +</div> +<p>is rewritten into</p> +<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> + <span class="o">*</span> +<span class="k">FROM</span> + <span class="p">(</span><span class="n">a</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">d</span> <span class="k">ON</span> <span class="n">a</span><span class="p">.</span><span class="n">id4</span> <span class="o">=</span> <span class="n">d</span><span class="p">.</span><span class="n">id4</span><span class="p">)</span> + <span class="k">INNER</span> <span class="k">JOIN</span> + <span class="p">(</span><span class="n">b</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="k">c</span> <span class="k">ON</span> <span class="n">b</span><span class="p">.</span><span class="n">id2</span> <span class="o">=</span> <span class="k">c</span><span class="p">.</span><span class="n">id2</span><span class="p">)</span> + <span class="k">ON</span> <span class="n">a</span><span class="p">.</span><span class="n">id1</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">id1</span> <span class="k">AND</span> <span class="k">c</span><span class="p">.</span><span class="n">id3</span> <span class="o">=</span> <span class="n">d</span><span class="p">.</span><span class="n">id3</span> +</pre></div> +</div> +</div> +<div class="section" id="broadcast-join"> +<h3>Broadcast join<a class="headerlink" href="#broadcast-join" title="Permalink to this headline">¶</a></h3> +<p>In Tajo, a join query is executed in two stages. The first stage is responsible for scanning input data and performing local join, while the second stage is responsible for performing global join and returning the result. +To perform join globally in the second stage, intermediate result of the first stage is exchanged according to join keys, i.e., <em>shuffled</em>, among Tajo workers. +Here, the cost of shuffle is expensive especially when the input relation size is very small.</p> +<p>Broadcast join is a good solution to handle this problem. In broadcast join, the small relations are replicated to every worker who participates in the join computation. +Thus, they can perform join without expensive data shuffle.</p> +<p>Tajo provides a session variable for broadcast join configuration. (For more detailed information of session variables, please refer to <a class="reference internal" href="../tsql/variables.html"><em>Session Variables</em></a>.)</p> +<ul class="simple"> +<li><code class="docutils literal"><span class="pre">DIST_QUERY_BROADCAST_JOIN_THRESHOLD</span></code> is a threshold for broadcast join. Only the relations who are larger than this value can be broadcasted.</li> +</ul> +<p>You can also apply this configuration system widely by setting <code class="docutils literal"><span class="pre">tajo.dist-query.join.broadcast.threshold-bytes</span></code> in <code class="docutils literal"><span class="pre">${TAJO_HOME}/conf/tajo-site.xml</span></code>.</p> +</div> +</div> +</div> + + + </div> + <footer> + + <div class="rst-footer-buttons" role="navigation" aria-label="footer navigation"> + + <a href="sql_expression.html" class="btn btn-neutral float-right" title="SQL Expressions"/>Next <span class="fa fa-arrow-circle-right"></span></a> + + + <a href="queries.html" class="btn btn-neutral" title="Queries"><span class="fa fa-arrow-circle-left"></span> Previous</a> + + </div> + + + <hr/> + + <div role="contentinfo"> + <p> + © Copyright 2014, Apache Tajo Team. + </p> + </div> + + <a href="https://github.com/snide/sphinx_rtd_theme";>Sphinx theme</a> provided by <a href="https://readthedocs.org";>Read the Docs</a> +</footer> + </div> + </div> + + </section> + + </div> + + + + + + <script type="text/javascript"> + var DOCUMENTATION_OPTIONS = { + URL_ROOT:'../', + VERSION:'0.11.0', + 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> + + + + + + <script type="text/javascript" src="../_static/js/theme.js"></script> + + + + + <script type="text/javascript"> + jQuery(function () { + SphinxRtdTheme.StickyNav.enable(); + }); + </script> + + +</body> +</html> \ No newline at end of file
Added: tajo/site/docs/0.11.0/sql_language/predicates.html URL: http://svn.apache.org/viewvc/tajo/site/docs/0.11.0/sql_language/predicates.html?rev=1710773&view=auto ============================================================================== --- tajo/site/docs/0.11.0/sql_language/predicates.html (added) +++ tajo/site/docs/0.11.0/sql_language/predicates.html Tue Oct 27 11:04:33 2015 @@ -0,0 +1,435 @@ + + +<!DOCTYPE html> +<!--[if IE 8]><html class="no-js lt-ie9" lang="en" > <![endif]--> +<!--[if gt IE 8]><!--> <html class="no-js" lang="en" > <!--<![endif]--> +<head> + <meta charset="utf-8"> + <meta name="viewport" content="width=device-width, initial-scale=1.0"> + + <title>Predicates — Apache Tajo 0.11.0 documentation</title> + + + + + + + <link href='https://fonts.googleapis.com/css?family=Lato:400,700|Roboto+Slab:400,700|Inconsolata:400,700' rel='stylesheet' type='text/css'> + + + + + + + + + + <link rel="stylesheet" href="../_static/css/theme.css" type="text/css" /> + + + + <link rel="top" title="Apache Tajo 0.11.0 documentation" href="../index.html"/> + <link rel="up" title="SQL Language" href="../sql_language.html"/> + <link rel="next" title="Time Zone" href="../time_zone.html"/> + <link rel="prev" title="SQL Expressions" href="sql_expression.html"/> + + + <script src="https://cdnjs.cloudflare.com/ajax/libs/modernizr/2.6.2/modernizr.min.js";></script> + +</head> + +<body class="wy-body-for-nav" role="document"> + + <div class="wy-grid-for-nav"> + + + <nav data-toggle="wy-nav-shift" class="wy-nav-side"> + <div class="wy-side-nav-search"> + <a href="../index.html" class="fa fa-home"> Apache Tajo</a> + <div role="search"> + <form id ="rtd-search-form" class="wy-form" action="../search.html" method="get"> + <input type="text" name="q" placeholder="Search docs" /> + <input type="hidden" name="check_keywords" value="yes" /> + <input type="hidden" name="area" value="default" /> + </form> +</div> + </div> + + <div class="wy-menu wy-menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation"> + + + <ul class="current"> +<li class="toctree-l1"><a class="reference internal" href="../introduction.html">Introduction</a></li> +<li class="toctree-l1"><a class="reference internal" href="../getting_started.html">Getting Started</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../getting_started.html#prerequisites">Prerequisites</a></li> +<li class="toctree-l2"><a class="reference internal" href="../getting_started.html#dowload-and-unpack-the-source-code">Dowload and unpack the source code</a></li> +<li class="toctree-l2"><a class="reference internal" href="../getting_started.html#build-source-code">Build source code</a></li> +<li class="toctree-l2"><a class="reference internal" href="../getting_started.html#setting-up-a-local-tajo-cluster">Setting up a local Tajo cluster</a></li> +<li class="toctree-l2"><a class="reference internal" href="../getting_started.html#first-query-execution">First query execution</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../configuration.html">Configuration</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../configuration/preliminary.html">Preliminary</a></li> +<li class="toctree-l2"><a class="reference internal" href="../configuration/cluster_setup.html">Cluster Setup</a></li> +<li class="toctree-l2"><a class="reference internal" href="../configuration/tajo_master_configuration.html">Tajo Master Configuration</a></li> +<li class="toctree-l2"><a class="reference internal" href="../configuration/worker_configuration.html">Worker Configuration</a></li> +<li class="toctree-l2"><a class="reference internal" href="../configuration/catalog_configuration.html">Catalog Configuration</a></li> +<li class="toctree-l2"><a class="reference internal" href="../configuration/ha_configuration.html">High Availability for TajoMaster</a></li> +<li class="toctree-l2"><a class="reference internal" href="../configuration/service_config_defaults.html">Cluster Service Configuration Defaults</a></li> +<li class="toctree-l2"><a class="reference internal" href="../configuration/tajo-site-xml.html">The tajo-site.xml File</a></li> +<li class="toctree-l2"><a class="reference internal" href="../configuration/catalog-site-xml.html">The catalog-site.xml File</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../tsql.html">Tajo Shell (TSQL)</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../tsql/meta_command.html">Meta Commands</a></li> +<li class="toctree-l2"><a class="reference internal" href="../tsql/dfs_command.html">Executing HDFS commands</a></li> +<li class="toctree-l2"><a class="reference internal" href="../tsql/variables.html">Session Variables</a></li> +<li class="toctree-l2"><a class="reference internal" href="../tsql/admin_command.html">Administration Commands</a></li> +<li class="toctree-l2"><a class="reference internal" href="../tsql/intro.html">Introducing to TSQL</a></li> +<li class="toctree-l2"><a class="reference internal" href="../tsql/single_command.html">Executing a single command</a></li> +<li class="toctree-l2"><a class="reference internal" href="../tsql/execute_file.html">Executing Queries from Files</a></li> +<li class="toctree-l2"><a class="reference internal" href="../tsql/background_command.html">Executing as background process</a></li> +</ul> +</li> +<li class="toctree-l1 current"><a class="reference internal" href="../sql_language.html">SQL Language</a><ul class="current"> +<li class="toctree-l2"><a class="reference internal" href="data_model.html">Data Model</a></li> +<li class="toctree-l2"><a class="reference internal" href="ddl.html">Data Definition Language</a></li> +<li class="toctree-l2"><a class="reference internal" href="insert.html">INSERT (OVERWRITE) INTO</a></li> +<li class="toctree-l2"><a class="reference internal" href="alter_table.html">ALTER TABLE</a></li> +<li class="toctree-l2"><a class="reference internal" href="queries.html">Queries</a></li> +<li class="toctree-l2"><a class="reference internal" href="joins.html">Joins</a></li> +<li class="toctree-l2"><a class="reference internal" href="sql_expression.html">SQL Expressions</a></li> +<li class="toctree-l2 current"><a class="current reference internal" href="">Predicates</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../time_zone.html">Time Zone</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../time_zone.html#server-cluster-time-zone">Server Cluster Time Zone</a></li> +<li class="toctree-l2"><a class="reference internal" href="../time_zone.html#table-time-zone">Table Time Zone</a></li> +<li class="toctree-l2"><a class="reference internal" href="../time_zone.html#client-time-zone">Client Time Zone</a></li> +<li class="toctree-l2"><a class="reference internal" href="../time_zone.html#time-zone-id">Time Zone ID</a></li> +<li class="toctree-l2"><a class="reference internal" href="../time_zone.html#examples-of-time-zone">Examples of Time Zone</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../functions.html">Functions</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../functions.html#built-in-scalar-functions">Built-in Scalar Functions</a></li> +<li class="toctree-l2"><a class="reference internal" href="../functions.html#built-in-aggregation-functions">Built-in Aggregation Functions</a></li> +<li class="toctree-l2"><a class="reference internal" href="../functions.html#built-in-window-functions">Built-in Window Functions</a></li> +<li class="toctree-l2"><a class="reference internal" href="../functions.html#user-defined-functions">User-defined Functions</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../table_management.html">Table Management</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../table_management/table_overview.html">Overview of Tajo Tables</a></li> +<li class="toctree-l2"><a class="reference internal" href="../table_management/tablespaces.html">Tablespaces</a></li> +<li class="toctree-l2"><a class="reference internal" href="../table_management/file_formats.html">File Formats</a></li> +<li class="toctree-l2"><a class="reference internal" href="../table_management/compression.html">Compression</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../table_partitioning.html">Table Partitioning</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../partitioning/intro_to_partitioning.html">Introduction to Partitioning</a></li> +<li class="toctree-l2"><a class="reference internal" href="../partitioning/column_partitioning.html">Column Partitioning</a></li> +<li class="toctree-l2"><a class="reference internal" href="../partitioning/range_partitioning.html">Range Partitioning</a></li> +<li class="toctree-l2"><a class="reference internal" href="../partitioning/hash_partitioning.html">Hash Partitioning</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../storage_plugins.html">Storage Plugin</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../storage_plugins/overview.html">Storage Plugin Overview</a></li> +<li class="toctree-l2"><a class="reference internal" href="../storage_plugins/postgresql.html">PostgreSQL Storage Handler</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../index_overview.html">Index (Experimental Feature)</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../index/types.html">Index Types</a></li> +<li class="toctree-l2"><a class="reference internal" href="../index/how_to_use.html">How to use index?</a></li> +<li class="toctree-l2"><a class="reference internal" href="../index/future_work.html">Future Works</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../backup_and_restore.html">Backup and Restore</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../backup_and_restore/catalog.html">Backup and Restore Catalog</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../hive_integration.html">Hive Integration</a></li> +<li class="toctree-l1"><a class="reference internal" href="../hbase_integration.html">HBase Integration</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../hbase_integration.html#create-table">CREATE TABLE</a></li> +<li class="toctree-l2"><a class="reference internal" href="../hbase_integration.html#drop-table">DROP TABLE</a></li> +<li class="toctree-l2"><a class="reference internal" href="../hbase_integration.html#insert-overwrite-into">INSERT (OVERWRITE) INTO</a></li> +<li class="toctree-l2"><a class="reference internal" href="../hbase_integration.html#usage">Usage</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../swift_integration.html">OpenStack Swift Integration</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../swift_integration.html#swift-configuration">Swift configuration</a></li> +<li class="toctree-l2"><a class="reference internal" href="../swift_integration.html#hadoop-configurations">Hadoop configurations</a></li> +<li class="toctree-l2"><a class="reference internal" href="../swift_integration.html#tajo-configuration">Tajo configuration</a></li> +<li class="toctree-l2"><a class="reference internal" href="../swift_integration.html#querying-on-swift">Querying on Swift</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../jdbc_driver.html">Tajo JDBC Driver</a><ul> +<li class="toctree-l2"><a class="reference internal" href="../jdbc_driver.html#how-to-get-jdbc-driver">How to get JDBC driver</a></li> +<li class="toctree-l2"><a class="reference internal" href="../jdbc_driver.html#setting-the-classpath">Setting the CLASSPATH</a></li> +<li class="toctree-l2"><a class="reference internal" href="../jdbc_driver.html#connecting-to-the-tajo-cluster-instance">Connecting to the Tajo cluster instance</a></li> +<li class="toctree-l2"><a class="reference internal" href="../jdbc_driver.html#connection-parameters">Connection Parameters</a></li> +<li class="toctree-l2"><a class="reference internal" href="../jdbc_driver.html#an-example-jdbc-client">An Example JDBC Client</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../tajo_client_api.html">Tajo Client API</a></li> +<li class="toctree-l1"><a class="reference internal" href="../faq.html">FAQ</a></li> +</ul> + + + </div> + + </nav> + + <section data-toggle="wy-nav-shift" class="wy-nav-content-wrap"> + + + <nav class="wy-nav-top" role="navigation" aria-label="top navigation"> + <i data-toggle="wy-nav-top" class="fa fa-bars"></i> + <a href="../index.html">Apache Tajo</a> + </nav> + + + + <div class="wy-nav-content"> + <div class="rst-content"> + <div role="navigation" aria-label="breadcrumbs navigation"> + <ul class="wy-breadcrumbs"> + <li><a href="../index.html">Docs</a> »</li> + + <li><a href="../sql_language.html">SQL Language</a> »</li> + + <li>Predicates</li> + <li class="wy-breadcrumbs-aside"> + + <a href="../_sources/sql_language/predicates.txt" rel="nofollow"> View page source</a> + + </li> + </ul> + <hr/> +</div> + <div role="main"> + + <div class="section" id="predicates"> +<h1>Predicates<a class="headerlink" href="#predicates" title="Permalink to this headline">¶</a></h1> +<div class="section" id="in-predicate"> +<h2>IN Predicate<a class="headerlink" href="#in-predicate" title="Permalink to this headline">¶</a></h2> +<p>IN predicate provides row and array comparison.</p> +<p><em>Synopsis</em></p> +<div class="highlight-sql"><div class="highlight"><pre><span class="n">column_reference</span> <span class="k">IN</span> <span class="p">(</span><span class="n">val1</span><span class="p">,</span> <span class="n">val2</span><span class="p">,</span> <span class="p">...,</span> <span class="n">valN</span><span class="p">)</span> +<span class="n">column_reference</span> <span class="k">NOT</span> <span class="k">IN</span> <span class="p">(</span><span class="n">val1</span><span class="p">,</span> <span class="n">val2</span><span class="p">,</span> <span class="p">...,</span> <span class="n">valN</span><span class="p">)</span> +</pre></div> +</div> +<p>Examples are as follows:</p> +<div class="highlight-sql"><div class="highlight"><pre><span class="c1">-- this statement filters lists down all the records where col1 value is 1, 2 or 3:</span> +<span class="k">SELECT</span> <span class="n">col1</span><span class="p">,</span> <span class="n">col2</span> <span class="k">FROM</span> <span class="n">table1</span> <span class="k">WHERE</span> <span class="n">col1</span> <span class="k">IN</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">);</span> + +<span class="c1">-- this statement filters lists down all the records where col1 value is neither 1, 2 nor 3:</span> +<span class="k">SELECT</span> <span class="n">col1</span><span class="p">,</span> <span class="n">col2</span> <span class="k">FROM</span> <span class="n">table1</span> <span class="k">WHERE</span> <span class="n">col1</span> <span class="k">NOT</span> <span class="k">IN</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">);</span> +</pre></div> +</div> +<p>You can use ‘IN clause’ on text data domain as follows:</p> +<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">col1</span><span class="p">,</span> <span class="n">col2</span> <span class="k">FROM</span> <span class="n">table1</span> <span class="k">WHERE</span> <span class="n">col2</span> <span class="k">IN</span> <span class="p">(</span><span class="s1">'tajo'</span><span class="p">,</span> <span class="s1">'hadoop'</span><span class="p">);</span> + +<span class="k">SELECT</span> <span class="n">col1</span><span class="p">,</span> <span class="n">col2</span> <span class="k">FROM</span> <span class="n">table1</span> <span class="k">WHERE</span> <span class="n">col2</span> <span class="k">NOT</span> <span class="k">IN</span> <span class="p">(</span><span class="s1">'tajo'</span><span class="p">,</span> <span class="s1">'hadoop'</span><span class="p">);</span> +</pre></div> +</div> +</div> +<div class="section" id="string-pattern-matching-predicates"> +<h2>String Pattern Matching Predicates<a class="headerlink" href="#string-pattern-matching-predicates" title="Permalink to this headline">¶</a></h2> +<div class="section" id="like"> +<h3>LIKE<a class="headerlink" href="#like" title="Permalink to this headline">¶</a></h3> +<p>LIKE operator returns true or false depending on whether its pattern matches the given string. An underscore (_) in pattern matches any single character. A percent sign (%) matches any sequence of zero or more characters.</p> +<p><em>Synopsis</em></p> +<div class="highlight-sql"><div class="highlight"><pre><span class="n">string</span> <span class="k">LIKE</span> <span class="n">pattern</span> +<span class="n">string</span> <span class="k">NOT</span> <span class="k">LIKE</span> <span class="n">pattern</span> +</pre></div> +</div> +</div> +<div class="section" id="ilike"> +<h3>ILIKE<a class="headerlink" href="#ilike" title="Permalink to this headline">¶</a></h3> +<p>ILIKE is the same to LIKE, but it is a case insensitive operator. It is not in the SQL standard. We borrow this operator from PostgreSQL.</p> +<p><em>Synopsis</em></p> +<div class="highlight-sql"><div class="highlight"><pre><span class="n">string</span> <span class="k">ILIKE</span> <span class="n">pattern</span> +<span class="n">string</span> <span class="k">NOT</span> <span class="k">ILIKE</span> <span class="n">pattern</span> +</pre></div> +</div> +</div> +<div class="section" id="similar-to"> +<h3>SIMILAR TO<a class="headerlink" href="#similar-to" title="Permalink to this headline">¶</a></h3> +<p><em>Synopsis</em></p> +<div class="highlight-sql"><div class="highlight"><pre><span class="n">string</span> <span class="k">SIMILAR</span> <span class="k">TO</span> <span class="n">pattern</span> +<span class="n">string</span> <span class="k">NOT</span> <span class="k">SIMILAR</span> <span class="k">TO</span> <span class="n">pattern</span> +</pre></div> +</div> +<p>It returns true or false depending on whether its pattern matches the given string. Also like LIKE, <code class="docutils literal"><span class="pre">SIMILAR</span> <span class="pre">TO</span></code> uses <code class="docutils literal"><span class="pre">_</span></code> and <code class="docutils literal"><span class="pre">%</span></code> as metacharacters denoting any single character and any string, respectively.</p> +<p>In addition to these metacharacters borrowed from LIKE, ‘SIMILAR TO’ supports more powerful pattern-matching metacharacters borrowed from regular expressions:</p> +<table border="1" class="docutils"> +<colgroup> +<col width="21%" /> +<col width="79%" /> +</colgroup> +<thead valign="bottom"> +<tr class="row-odd"><th class="head">metacharacter</th> +<th class="head">description</th> +</tr> +</thead> +<tbody valign="top"> +<tr class="row-even"><td>&#124;</td> +<td>denotes alternation (either of two alternatives).</td> +</tr> +<tr class="row-odd"><td><ul class="first last simple"> +<li></li> +</ul> +</td> +<td>denotes repetition of the previous item zero or more times.</td> +</tr> +<tr class="row-even"><td><ul class="first last simple"> +<li></li> +</ul> +</td> +<td>denotes repetition of the previous item one or more times.</td> +</tr> +<tr class="row-odd"><td>?</td> +<td>denotes repetition of the previous item zero or one time.</td> +</tr> +<tr class="row-even"><td>{m}</td> +<td>denotes repetition of the previous item exactly m times.</td> +</tr> +<tr class="row-odd"><td>{m,}</td> +<td>denotes repetition of the previous item m or more times.</td> +</tr> +<tr class="row-even"><td>{m,n}</td> +<td>denotes repetition of the previous item at least m and not more than n times.</td> +</tr> +<tr class="row-odd"><td>[]</td> +<td>A bracket expression specifies a character class, just as in POSIX regular expressions.</td> +</tr> +<tr class="row-even"><td>()</td> +<td>Parentheses can be used to group items into a single logical item.</td> +</tr> +</tbody> +</table> +<p>Note that <cite>.`</cite> is not used as a metacharacter in <code class="docutils literal"><span class="pre">SIMILAR</span> <span class="pre">TO</span></code> operator.</p> +</div> +<div class="section" id="regular-expressions"> +<h3>Regular expressions<a class="headerlink" href="#regular-expressions" title="Permalink to this headline">¶</a></h3> +<p>Regular expressions provide a very powerful means for string pattern matching. In the current Tajo, regular expressions are based on Java-style regular expressions instead of POSIX regular expression. The main difference between java-style one and POSIX’s one is character class.</p> +<p><em>Synopsis</em></p> +<div class="highlight-sql"><div class="highlight"><pre><span class="n">string</span> <span class="o">~</span> <span class="n">pattern</span> +<span class="n">string</span> <span class="o">!~</span> <span class="n">pattern</span> + +<span class="n">string</span> <span class="o">~*</span> <span class="n">pattern</span> +<span class="n">string</span> <span class="o">!~*</span> <span class="n">pattern</span> +</pre></div> +</div> +<table border="1" class="docutils"> +<colgroup> +<col width="9%" /> +<col width="91%" /> +</colgroup> +<thead valign="bottom"> +<tr class="row-odd"><th class="head">operator</th> +<th class="head">Description</th> +</tr> +</thead> +<tbody valign="top"> +<tr class="row-even"><td>~</td> +<td>It returns true if a given regular expression is matched to string. Otherwise, it returns false.</td> +</tr> +<tr class="row-odd"><td>!~</td> +<td>It returns false if a given regular expression is matched to string. Otherwise, it returns true.</td> +</tr> +<tr class="row-even"><td>~*</td> +<td>It is the same to ‘~’, but it is case insensitive.</td> +</tr> +<tr class="row-odd"><td>!~*</td> +<td>It is the same to ‘!~’, but it is case insensitive.</td> +</tr> +</tbody> +</table> +<p>Here are examples:</p> +<div class="highlight-sql"><div class="highlight"><pre>'abc' ~ '.*c' true +'abc' ~ 'c' false +'aaabc' ~ '([a-z]){3}bc true +'abc' ~* '.*C' true +'abc' !~* 'B.*' true +</pre></div> +</div> +<p>Regular expressions operator is not in the SQL standard. We borrow this operator from PostgreSQL.</p> +<p><em>Synopsis for REGEXP and RLIKE operators</em></p> +<div class="highlight-sql"><div class="highlight"><pre><span class="n">string</span> <span class="n">REGEXP</span> <span class="n">pattern</span> +<span class="n">string</span> <span class="k">NOT</span> <span class="n">REGEXP</span> <span class="n">pattern</span> + +<span class="n">string</span> <span class="n">RLIKE</span> <span class="n">pattern</span> +<span class="n">string</span> <span class="k">NOT</span> <span class="n">RLIKE</span> <span class="n">pattern</span> +</pre></div> +</div> +<p>But, they do not support case-insensitive operators.</p> +</div> +</div> +</div> + + + </div> + <footer> + + <div class="rst-footer-buttons" role="navigation" aria-label="footer navigation"> + + <a href="../time_zone.html" class="btn btn-neutral float-right" title="Time Zone"/>Next <span class="fa fa-arrow-circle-right"></span></a> + + + <a href="sql_expression.html" class="btn btn-neutral" title="SQL Expressions"><span class="fa fa-arrow-circle-left"></span> Previous</a> + + </div> + + + <hr/> + + <div role="contentinfo"> + <p> + © Copyright 2014, Apache Tajo Team. + </p> + </div> + + <a href="https://github.com/snide/sphinx_rtd_theme";>Sphinx theme</a> provided by <a href="https://readthedocs.org";>Read the Docs</a> +</footer> + </div> + </div> + + </section> + + </div> + + + + + + <script type="text/javascript"> + var DOCUMENTATION_OPTIONS = { + URL_ROOT:'../', + VERSION:'0.11.0', + 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> + + + + + + <script type="text/javascript" src="../_static/js/theme.js"></script> + + + + + <script type="text/javascript"> + jQuery(function () { + SphinxRtdTheme.StickyNav.enable(); + }); + </script> + + +</body> +</html> \ No newline at end of file
