Modified: phoenix/site/publish/language/index.html URL: http://svn.apache.org/viewvc/phoenix/site/publish/language/index.html?rev=1710289&r1=1710288&r2=1710289&view=diff ============================================================================== --- phoenix/site/publish/language/index.html (original) +++ phoenix/site/publish/language/index.html Fri Oct 23 22:31:39 2015 @@ -142,7 +142,1448 @@ <div class="body-content"> <div class="page-header"> <h1>Grammar</h1> -</div>update_here +</div> + +<!-- } --> +<h3 id="grammar">Commands</h3> +<!-- syntax-start +<p class="notranslate"> + + <a href="#select">SELECT</a><br /> + + <a href="#upsert_values">UPSERT VALUES</a><br /> + + <a href="#upsert_select">UPSERT SELECT</a><br /> + + <a href="#delete">DELETE</a><br /> + + <a href="#create_table">CREATE TABLE</a><br /> + + <a href="#drop_table">DROP TABLE</a><br /> + + <a href="#create_function">CREATE FUNCTION</a><br /> + + <a href="#drop_function">DROP FUNCTION</a><br /> + + <a href="#create_view">CREATE VIEW</a><br /> + + <a href="#drop_view">DROP VIEW</a><br /> + + <a href="#create_sequence">CREATE SEQUENCE</a><br /> + + <a href="#drop_sequence">DROP SEQUENCE</a><br /> + + <a href="#alter">ALTER</a><br /> + + <a href="#create_index">CREATE INDEX</a><br /> + + <a href="#drop_index">DROP INDEX</a><br /> + + <a href="#alter_index">ALTER INDEX</a><br /> + + <a href="#explain">EXPLAIN</a><br /> + + <a href="#update_statistics">UPDATE STATISTICS</a><br /> +</p> +syntax-end --> +<!-- railroad-start --> +<table class="notranslate index"> + <tr> + <td class="index"> + + <a href="#select" >SELECT</a><br /> + + <a href="#upsert_values" >UPSERT VALUES</a><br /> + + <a href="#upsert_select" >UPSERT SELECT</a><br /> + + <a href="#delete" >DELETE</a><br /> + + <a href="#create_table" >CREATE TABLE</a><br /> + + <a href="#drop_table" >DROP TABLE</a><br /> + </td><td class="index"> + + <a href="#create_function" >CREATE FUNCTION</a><br /> + + <a href="#drop_function" >DROP FUNCTION</a><br /> + + <a href="#create_view" >CREATE VIEW</a><br /> + + <a href="#drop_view" >DROP VIEW</a><br /> + + <a href="#create_sequence" >CREATE SEQUENCE</a><br /> + + <a href="#drop_sequence" >DROP SEQUENCE</a><br /> + </td><td class="index"> + + <a href="#alter" >ALTER</a><br /> + + <a href="#create_index" >CREATE INDEX</a><br /> + + <a href="#drop_index" >DROP INDEX</a><br /> + + <a href="#alter_index" >ALTER INDEX</a><br /> + + <a href="#explain" >EXPLAIN</a><br /> + + <a href="#update_statistics" >UPDATE STATISTICS</a><br /> + </td> + </tr> +</table> +<!-- railroad-end --> + +<h3>Other Grammar</h3> +<!-- syntax-start +<p class="notranslate"> + + <a href="#constraint" >Constraint</a><br /> + + <a href="#options" >Options</a><br /> + + <a href="#hint" >Hint</a><br /> + + <a href="#scan_hint" >Scan Hint</a><br /> + + <a href="#cache_hint" >Cache Hint</a><br /> + + <a href="#index_hint" >Index Hint</a><br /> + + <a href="#small_hint" >Small Hint</a><br /> + + <a href="#seek_to_column_hint" >Seek To Column Hint</a><br /> + + <a href="#join_hint" >Join Hint</a><br /> + + <a href="#column_def" >Column Def</a><br /> + + <a href="#table_ref" >Table Ref</a><br /> + + <a href="#sequence_ref" >Sequence Ref</a><br /> + + <a href="#column_ref" >Column Ref</a><br /> + + <a href="#select_expression" >Select Expression</a><br /> + + <a href="#select_statement" >Select Statement</a><br /> + + <a href="#split_point" >Split Point</a><br /> + + <a href="#table_spec" >Table Spec</a><br /> + + <a href="#aliased_table_ref" >Aliased Table Ref</a><br /> + + <a href="#join_type" >Join Type</a><br /> + + <a href="#func_argument" >Func Argument</a><br /> + + <a href="#class_name" >Class Name</a><br /> + + <a href="#jar_path" >Jar Path</a><br /> + + <a href="#order" >Order</a><br /> + + <a href="#expression" >Expression</a><br /> + + <a href="#and_condition" >And Condition</a><br /> + + <a href="#boolean_condition" >Boolean Condition</a><br /> + + <a href="#condition" >Condition</a><br /> + + <a href="#rhs_operand" >RHS Operand</a><br /> + + <a href="#operand" >Operand</a><br /> + + <a href="#summand" >Summand</a><br /> + + <a href="#factor" >Factor</a><br /> + + <a href="#term" >Term</a><br /> + + <a href="#array_constructor" >Array Constructor</a><br /> + + <a href="#sequence" >Sequence</a><br /> + + <a href="#cast" >Cast</a><br /> + + <a href="#row_value_constructor" >Row Value Constructor</a><br /> + + <a href="#bind_parameter" >Bind Parameter</a><br /> + + <a href="#value" >Value</a><br /> + + <a href="#case" >Case</a><br /> + + <a href="#case_when" >Case When</a><br /> + + <a href="#name" >Name</a><br /> + + <a href="#quoted_name" >Quoted Name</a><br /> + + <a href="#alias" >Alias</a><br /> + + <a href="#null" >Null</a><br /> + + <a href="#data_type" >Data Type</a><br /> + + <a href="#sql_data_type" >SQL Data Type</a><br /> + + <a href="#hbase_data_type" >HBase Data Type</a><br /> + + <a href="#string" >String</a><br /> + + <a href="#boolean" >Boolean</a><br /> + + <a href="#numeric" >Numeric</a><br /> + + <a href="#int" >Int</a><br /> + + <a href="#long" >Long</a><br /> + + <a href="#decimal" >Decimal</a><br /> + + <a href="#number" >Number</a><br /> + + <a href="#comments" >Comments</a><br /> +</p> +syntax-end --> +<!-- railroad-start --> +<table class="notranslate index"> + <tr> + <td class="index"> + + <a href="#constraint" >Constraint</a><br /> + + <a href="#options" >Options</a><br /> + + <a href="#hint" >Hint</a><br /> + + <a href="#scan_hint" >Scan Hint</a><br /> + + <a href="#cache_hint" >Cache Hint</a><br /> + + <a href="#index_hint" >Index Hint</a><br /> + + <a href="#small_hint" >Small Hint</a><br /> + + <a href="#seek_to_column_hint" >Seek To Column Hint</a><br /> + + <a href="#join_hint" >Join Hint</a><br /> + + <a href="#column_def" >Column Def</a><br /> + + <a href="#table_ref" >Table Ref</a><br /> + + <a href="#sequence_ref" >Sequence Ref</a><br /> + + <a href="#column_ref" >Column Ref</a><br /> + + <a href="#select_expression" >Select Expression</a><br /> + + <a href="#select_statement" >Select Statement</a><br /> + + <a href="#split_point" >Split Point</a><br /> + + <a href="#table_spec" >Table Spec</a><br /> + + <a href="#aliased_table_ref" >Aliased Table Ref</a><br /> + + <a href="#join_type" >Join Type</a><br /> + </td><td class="index"> + + <a href="#func_argument" >Func Argument</a><br /> + + <a href="#class_name" >Class Name</a><br /> + + <a href="#jar_path" >Jar Path</a><br /> + + <a href="#order" >Order</a><br /> + + <a href="#expression" >Expression</a><br /> + + <a href="#and_condition" >And Condition</a><br /> + + <a href="#boolean_condition" >Boolean Condition</a><br /> + + <a href="#condition" >Condition</a><br /> + + <a href="#rhs_operand" >RHS Operand</a><br /> + + <a href="#operand" >Operand</a><br /> + + <a href="#summand" >Summand</a><br /> + + <a href="#factor" >Factor</a><br /> + + <a href="#term" >Term</a><br /> + + <a href="#array_constructor" >Array Constructor</a><br /> + + <a href="#sequence" >Sequence</a><br /> + + <a href="#cast" >Cast</a><br /> + + <a href="#row_value_constructor" >Row Value Constructor</a><br /> + + <a href="#bind_parameter" >Bind Parameter</a><br /> + + <a href="#value" >Value</a><br /> + </td><td class="index"> + + <a href="#case" >Case</a><br /> + + <a href="#case_when" >Case When</a><br /> + + <a href="#name" >Name</a><br /> + + <a href="#quoted_name" >Quoted Name</a><br /> + + <a href="#alias" >Alias</a><br /> + + <a href="#null" >Null</a><br /> + + <a href="#data_type" >Data Type</a><br /> + + <a href="#sql_data_type" >SQL Data Type</a><br /> + + <a href="#hbase_data_type" >HBase Data Type</a><br /> + + <a href="#string" >String</a><br /> + + <a href="#boolean" >Boolean</a><br /> + + <a href="#numeric" >Numeric</a><br /> + + <a href="#int" >Int</a><br /> + + <a href="#long" >Long</a><br /> + + <a href="#decimal" >Decimal</a><br /> + + <a href="#number" >Number</a><br /> + + <a href="#comments" >Comments</a><br /> + </td> + </tr> +</table> +<!-- railroad-end --> + + +<h3 id="select" class="notranslate">SELECT</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +<a href="index.html#select_statement">selectStatement</a> [ { UNION ALL <a href="index.html#select_statement">selectStatement</a> [...] } ] +[ ORDER BY <a href="index.html#order">order</a> [,...] ] [ LIMIT {<a href="index.html#bind_parameter">bindParameter</a> | <a href="index.html#number">number</a>} ] +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#select_statement">selectStatement</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">UNION ALL <a href="index.html#select_statement">selectStatement</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">...</code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="ra ilroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">ORDER BY <a href="index.html#order">order</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">LIMIT</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#bind_parameter">bindParameter</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls">< /td><td class="d"><code class="c"><a href="index.html#number">number</a></code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +<a href="index.html#select_statement">selectStatement</a> [ { UNION ALL <a href="index.html#select_statement">selectStatement</a> [...] } ] +[ ORDER BY <a href="index.html#order">order</a> [,...] ] [ LIMIT {<a href="index.html#bind_parameter">bindParameter</a> | <a href="index.html#number">number</a>} ] +</pre> +syntax-end --> +<p>Selects data from one or more tables. <code>UNION ALL</code> combines rows from multiple select statements. <code>ORDER BY</code> sorts the result based on the given expressions. <code>LIMIT</code> limits the number of rows returned by the query with no limit applied if unspecified or specified as null or less than zero. The <code>LIMIT</code> clause is executed after the <code>ORDER BY</code> clause to support top-N type queries. An optional hint may be used to override decisions made by the query optimizer.</p> +<p>Example:</p> +<p class="notranslate"> +SELECT * FROM TEST LIMIT 1000;<br />SELECT full_name FROM SALES_PERSON WHERE ranking >= 5.0<br /> UNION ALL SELECT reviewer_name FROM CUSTOMER_REVIEW WHERE score >= 8.0</p> + +<h3 id="upsert_values" class="notranslate">UPSERT VALUES</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +UPSERT INTO <a href="index.html#name">tableName</a> [( { <a href="index.html#column_ref">columnRef</a> | <a href="index.html#column_def">columnDef</a> } [,...] )] VALUES ( <a href="index.html#term">constantTerm</a> [,...] ) +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">UPSERT INTO <a href="index.html#name">tableName</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">(</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#column_ref">columnRef</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#column_def">columnDef</a></code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le "></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">VALUES ( <a href="index.html#term">constantTerm</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +UPSERT INTO <a href="index.html#name">tableName</a> [( { <a href="index.html#column_ref">columnRef</a> | <a href="index.html#column_def">columnDef</a> } [,...] )] VALUES ( <a href="index.html#term">constantTerm</a> [,...] ) +</pre> +syntax-end --> +<p>Inserts if not present and updates otherwise the value in the table. The list of columns is optional and if not present, the values will map to the column in the order they are declared in the schema. The values must evaluate to constants.</p> +<p>Example:</p> +<p class="notranslate"> +UPSERT INTO TEST VALUES('foo','bar',3);<br />UPSERT INTO TEST(NAME,ID) VALUES('foo',123);</p> + +<h3 id="upsert_select" class="notranslate">UPSERT SELECT</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +UPSERT [/*+ <a href="index.html#hint">hint</a> */] INTO <a href="index.html#name">tableName</a> [( { <a href="index.html#column_ref">columnRef</a> | <a href="index.html#column_def">columnDef</a> } [,...] )] <a href="index.html#select">select</a> +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">UPSERT</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">/ * + <a href="index.html#hint">hint</a> * /</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">INTO <a href="index.html#name">tableName</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">(</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#column_ref">columnRef</a></code></td><td class ="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#column_def">columnDef</a></code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#select">select</a></code></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +UPSERT [/*+ <a href="index.html#hint">hint</a> */] INTO <a href="index.html#name">tableName</a> [( { <a href="index.html#column_ref">columnRef</a> | <a href="index.html#column_def">columnDef</a> } [,...] )] <a href="index.html#select">select</a> +</pre> +syntax-end --> +<p>Inserts if not present and updates otherwise rows in the table based on the results of running another query. The values are set based on their matching position between the source and target tables. The list of columns is optional and if not present will map to the column in the order they are declared in the schema. If auto commit is on, and both a) the target table matches the source table, and b) the select performs no aggregation, then the population of the target table will be done completely on the server-side (with constraint violations logged, but otherwise ignored). Otherwise, data is buffered on the client and, if auto commit is on, committed in row batches as specified by the UpsertBatchSize connection property (or the phoenix.mutate.upsertBatchSize <code>HBase</code> config property which defaults to 10000 rows)</p> +<p>Example:</p> +<p class="notranslate"> +UPSERT INTO test.targetTable(col1, col2) SELECT col3, col4 FROM test.sourceTable WHERE col5 < 100<br />UPSERT INTO foo SELECT * FROM bar;</p> + +<h3 id="delete" class="notranslate">DELETE</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +DELETE [/*+ <a href="index.html#hint">hint</a> */] FROM <a href="index.html#name">tableName</a> [ WHERE <a href="index.html#expression">expression</a> ] +[ ORDER BY <a href="index.html#order">order</a> [,...] ] [ LIMIT {<a href="index.html#bind_parameter">bindParameter</a> | <a href="index.html#number">number</a>} ] +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">DELETE</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">/ * + <a href="index.html#hint">hint</a> * /</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">FROM <a href="index.html#name">tableName</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">WHERE <a href="index.html#expression">expression</a></code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">ORDER BY <a href="index.html#order">order</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">LIMIT</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#bind_parameter">bindParameter</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#number">number</a></code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +DELETE [/*+ <a href="index.html#hint">hint</a> */] FROM <a href="index.html#name">tableName</a> [ WHERE <a href="index.html#expression">expression</a> ] +[ ORDER BY <a href="index.html#order">order</a> [,...] ] [ LIMIT {<a href="index.html#bind_parameter">bindParameter</a> | <a href="index.html#number">number</a>} ] +</pre> +syntax-end --> +<p>Deletes the rows selected by the where clause. If auto commit is on, the deletion is performed completely server-side.</p> +<p>Example:</p> +<p class="notranslate"> +DELETE FROM TEST;<br />DELETE FROM TEST WHERE ID=123;<br />DELETE FROM TEST WHERE NAME LIKE 'foo%';</p> + +<h3 id="create_table" class="notranslate">CREATE TABLE</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +CREATE TABLE [IF NOT EXISTS] <a href="index.html#table_ref">tableRef</a> +( <a href="index.html#column_def">columnDef</a> [,...] [<a href="index.html#constraint">constraint</a>] ) +[<a href="index.html#options">tableOptions</a>] [ SPLIT ON ( <a href="index.html#split_point">splitPoint</a> [,...] ) ] +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">CREATE TABLE</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">IF NOT EXISTS</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#table_ref">tableRef</a></code></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="d"><code class="c">( <a href="index.html#column_def">columnDef</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td cl ass="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#constraint">constraint</a></code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#options">tableOptions</a></code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">SPLIT ON ( <a href="index.html#split_point">splitPoint</a></code></td><td class="d"><table class="railroa d"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +CREATE TABLE [IF NOT EXISTS] <a href="index.html#table_ref">tableRef</a> +( <a href="index.html#column_def">columnDef</a> [,...] [<a href="index.html#constraint">constraint</a>] ) +[<a href="index.html#options">tableOptions</a>] [ SPLIT ON ( <a href="index.html#split_point">splitPoint</a> [,...] ) ] +</pre> +syntax-end --> +<p>Creates a new table. The <code>HBase</code> table and any column families referenced are created if they don't already exist. All table, column family and column names are uppercased unless they are double quoted in which case they are case sensitive. Column families that exist in the <code>HBase</code> table but are not listed are ignored. At create time, to improve query performance, an empty key value is added to the first column family of any existing rows or the default column family if no column families are explicitly defined. Upserts will also add this empty key value. This improves query performance by having a key value column we can guarantee always being there and thus minimizing the amount of data that must be projected and subsequently returned back to the client. <code>HBase</code> table and column configuration options may be passed through as key/value pairs to configure the <code>HBase</code> table as desired. Note that when using the <code>IF NOT EXISTS</co de> clause, if a table already exists, then no change will be made to it. Additionally, no validation is done to check whether the existing table metadata matches the proposed table metadata. so it's better to use <code>DROP TABLE</code> followed by <code>CREATE TABLE</code> is the table metadata may be changing.</p> +<p>Example:</p> +<p class="notranslate"> +CREATE TABLE my_schema.my_table ( id BIGINT not null primary key, date)<br />CREATE TABLE my_table ( id INTEGER not null primary key desc, date DATE not null,<br /> m.db_utilization DECIMAL, i.db_utilization)<br /> m.DATA_BLOCK_ENCODING='DIFF'<br />CREATE TABLE stats.prod_metrics ( host char(50) not null, created_date date not null,<br /> txn_count bigint CONSTRAINT pk PRIMARY KEY (host, created_date) )<br />CREATE TABLE IF NOT EXISTS "my_case_sensitive_table"<br /> ( "id" char(10) not null primary key, "value" integer)<br /> DATA_BLOCK_ENCODING='NONE',VERSIONS=5,MAX_FILESIZE=2000000 split on (?, ?, ?)<br />CREATE TABLE IF NOT EXISTS my_schema.my_table (<br /> org_id CHAR(15), entity_id CHAR(15), payload binary(1000),<br /> CONSTRAINT pk PRIMARY KEY (org_id, entity_id) )<br />&nb sp; TTL=86400</p> + +<h3 id="drop_table" class="notranslate">DROP TABLE</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +DROP TABLE [IF EXISTS] <a href="index.html#table_ref">tableRef</a> [CASCADE] +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">DROP TABLE</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">IF EXISTS</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#table_ref">tableRef</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">CASCADE</code></td><td class="le"></td></tr></table></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +DROP TABLE [IF EXISTS] <a href="index.html#table_ref">tableRef</a> [CASCADE] +</pre> +syntax-end --> +<p>Drops a table. The optional <code>CASCADE</code> keyword causes any views on the table to be dropped as well. When dropping a table, by default the underlying <code>HBase</code> data and index tables are dropped. The phoenix.schema.dropMetaData may be used to override this and keep the <code>HBase</code> table for point-in-time queries.</p> +<p>Example:</p> +<p class="notranslate"> +DROP TABLE my_schema.my_table;<br />DROP TABLE IF EXISTS my_table;<br />DROP TABLE my_schema.my_table CASCADE;</p> + +<h3 id="create_function" class="notranslate">CREATE FUNCTION</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +CREATE [TEMPORARY] FUNCTION <a href="index.html#name">funcName</a> +([<a href="index.html#func_argument">funcArgument</a>[,...]]) +RETURNS <a href="index.html#data_type">dataType</a> AS <a href="index.html#class_name">className</a> [USING JAR <a href="index.html#jar_path">jarPath</a>] +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">CREATE</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">TEMPORARY</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">FUNCTION <a href="index.html#name">funcName</a></code></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="d"><code class="c">(</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#func_argument">funcArgument</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></t r><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="d"><code class="c">RETURNS <a href="index.html#data_type">dataType</a> AS <a href="index.html#class_name">className</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">USING JAR <a href="index.html#jar_path">jarPath</a></code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +CREATE [TEMPORARY] FUNCTION <a href="index.html#name">funcName</a> +([<a href="index.html#func_argument">funcArgument</a>[,...]]) +RETURNS <a href="index.html#data_type">dataType</a> AS <a href="index.html#class_name">className</a> [USING JAR <a href="index.html#jar_path">jarPath</a>] +</pre> +syntax-end --> +<p>Creates a new function. The function name is uppercased unless they are double quoted in which case they are case sensitive. The function accepts zero or more arguments. The class name and jar path should be in single quotes. The jar path is optional and if not specified then the class name will be loaded from the jars present in directory configured for hbase.dynamic.jars.dir.</p> +<p>Example:</p> +<p class="notranslate"> +CREATE FUNCTION my_reverse(varchar) returns varchar as 'com.mypackage.MyReverseFunction' using jar 'hdfs:/localhost:8080/hbase/lib/myjar.jar'<br />CREATE FUNCTION my_reverse(varchar) returns varchar as 'com.mypackage.MyReverseFunction'<br />CREATE FUNCTION my_increment(integer, integer constant defaultvalue='10') returns integer as 'com.mypackage.MyIncrementFunction' using jar '/hbase/lib/myincrement.jar'<br />CREATE TEMPORARY FUNCTION my_reverse(varchar) returns varchar as 'com.mypackage.MyReverseFunction' using jar 'hdfs:/localhost:8080/hbase/lib/myjar.jar'</p> + +<h3 id="drop_function" class="notranslate">DROP FUNCTION</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +DROP FUNCTION [IF EXISTS] <a href="index.html#name">funcName</a> +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">DROP FUNCTION</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">IF EXISTS</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#name">funcName</a></code></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +DROP FUNCTION [IF EXISTS] <a href="index.html#name">funcName</a> +</pre> +syntax-end --> +<p>Drops a function.</p> +<p>Example:</p> +<p class="notranslate"> +DROP FUNCTION IF EXISTS my_reverse<br />DROP FUNCTION my_reverse</p> + +<h3 id="create_view" class="notranslate">CREATE VIEW</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +CREATE VIEW [IF NOT EXISTS] <a href="index.html#table_ref">newTableRef</a> +[ ( <a href="index.html#column_def">columnDef</a> [,...] ) ] +[AS SELECT * FROM <a href="index.html#table_ref">existingTableRef</a> [WHERE <a href="index.html#expression">expression</a>] ] +[<a href="index.html#options">tableOptions</a>] +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">CREATE VIEW</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">IF NOT EXISTS</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#table_ref">newTableRef</a></code></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">( <a href="index.html#column_def">columnDef</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad "><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table></td><td class="le"></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">AS SELECT * FROM <a href="index.html#table_ref">existingTableRef</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">WHERE <a href="index.html#expression">expression</a></code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table><br /><table class="railroad"><tr c lass="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#options">tableOptions</a></code></td><td class="le"></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +CREATE VIEW [IF NOT EXISTS] <a href="index.html#table_ref">newTableRef</a> +[ ( <a href="index.html#column_def">columnDef</a> [,...] ) ] +[AS SELECT * FROM <a href="index.html#table_ref">existingTableRef</a> [WHERE <a href="index.html#expression">expression</a>] ] +[<a href="index.html#options">tableOptions</a>] +</pre> +syntax-end --> +<p>Creates a new view over an existing <code>HBase</code> or Phoenix table. As expected, the <code>WHERE</code> expression is always automatically applied to any query run against the view. As with <code>CREATE TABLE</code>, the table, column family, and column names are uppercased unless they are double quoted. The newTableRef may refer directly to an <code>HBase</code> table, in which case, the table, column family, and column names must match the existing metadata exactly or an exception will occur. When a view is mapped directly to an <code>HBase</code> table, no empty key value will be added to rows and the view will be read-only. A view will be updatable (i.e. referenceable in a <code>DML</code> statement such as <code>UPSERT</code> or <code>DELETE</code>) if its <code>WHERE</code> clause expression contains only simple equality expressions separated by <code>ANDs</code>. Updatable views are not required to set the columns which appear in the equality expressions, as the equal ity expressions define the default values for those columns. If they are set, then they must match the value used in the <code>WHERE</code> clause, or an error will occur. All columns from the existingTableRef are included as columns in the new view as are columns defined in the columnDef list. An <code>ALTER VIEW</code> statement may be issued against a view to remove or add columns, however, no changes may be made to the primary key constraint. In addition, columns referenced in the <code>WHERE</code> clause are not allowed to be removed. Once a view is created for a table, that table may no longer altered or dropped until all of its views have been dropped.</p> +<p>Example:</p> +<p class="notranslate"> +CREATE VIEW "my_hbase_table"<br /> ( k VARCHAR primary key, "v" UNSIGNED_LONG) default_column_family='a';<br />CREATE VIEW my_view ( new_col SMALLINT )<br /> AS SELECT * FROM my_table WHERE k = 100;<br />CREATE VIEW my_view_on_view<br /> AS SELECT * FROM my_view WHERE new_col > 70;</p> + +<h3 id="drop_view" class="notranslate">DROP VIEW</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +DROP VIEW [IF EXISTS] <a href="index.html#table_ref">tableRef</a> [CASCADE] +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">DROP VIEW</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">IF EXISTS</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#table_ref">tableRef</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">CASCADE</code></td><td class="le"></td></tr></table></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +DROP VIEW [IF EXISTS] <a href="index.html#table_ref">tableRef</a> [CASCADE] +</pre> +syntax-end --> +<p>Drops a view. The optional <code>CASCADE</code> keyword causes any views derived from the view to be dropped as well. When dropping a view, the actual table data is not affected. However, index data for the view will be deleted.</p> +<p>Example:</p> +<p class="notranslate"> +DROP VIEW my_view<br />DROP VIEW IF EXISTS my_schema.my_view<br />DROP VIEW IF EXISTS my_schema.my_view CASCADE</p> + +<h3 id="create_sequence" class="notranslate">CREATE SEQUENCE</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +CREATE SEQUENCE [IF NOT EXISTS] <a href="index.html#sequence_ref">sequenceRef</a> +[START [WITH] {<a href="index.html#number">number</a> | <a href="index.html#bind_parameter">bindParameter</a>}] [INCREMENT [BY] {<a href="index.html#number">number</a> | <a href="index.html#bind_parameter">bindParameter</a>}] +[MINVALUE {<a href="index.html#number">number</a> | <a href="index.html#bind_parameter">bindParameter</a>}] [MAXVALUE {<a href="index.html#number">number</a> | <a href="index.html#bind_parameter">bindParameter</a>}] [CYCLE] +[CACHE {<a href="index.html#number">number</a> | <a href="index.html#bind_parameter">bindParameter</a>}] +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">CREATE SEQUENCE</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">IF NOT EXISTS</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#sequence_ref">sequenceRef</a></code></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">START</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td> </tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">WITH</code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#number">number</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#bind_parameter">bindParameter</a></code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">INCREMENT</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">BY</code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#number">number</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#bind_parameter">bindParameter</a></code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">MINVALUE</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#number">number</a></code></td><t d class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#bind_parameter">bindParameter</a></code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">MAXVALUE</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#number">number</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#bind_parameter">bindParameter</a></code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="ra ilroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">CYCLE</code></td><td class="le"></td></tr></table></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">CACHE</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#number">number</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#bind_parameter">bindParameter</a></code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +CREATE SEQUENCE [IF NOT EXISTS] <a href="index.html#sequence_ref">sequenceRef</a> +[START [WITH] {<a href="index.html#number">number</a> | <a href="index.html#bind_parameter">bindParameter</a>}] [INCREMENT [BY] {<a href="index.html#number">number</a> | <a href="index.html#bind_parameter">bindParameter</a>}] +[MINVALUE {<a href="index.html#number">number</a> | <a href="index.html#bind_parameter">bindParameter</a>}] [MAXVALUE {<a href="index.html#number">number</a> | <a href="index.html#bind_parameter">bindParameter</a>}] [CYCLE] +[CACHE {<a href="index.html#number">number</a> | <a href="index.html#bind_parameter">bindParameter</a>}] +</pre> +syntax-end --> +<p>Creates a monotonically increasing sequence. <code>START</code> controls the initial sequence value while <code>INCREMENT</code> controls by how much the sequence is incremented after each call to <code>NEXT VALUE FOR</code>. By default, the sequence will start with 1 and be incremented by 1. Specify <code>CYCLE</code> to indicate that the sequence should continue to generate values after reaching either its <code>MINVALUE</code> or <code>MAXVALUE</code>. After an ascending sequence reaches its <code>MAXVALUE</code>, it generates its <code>MINVALUE</code>. After a descending sequence reaches its <code>MINVALUE</code>, it generates its <code>MAXVALUE</code>. <code>CACHE</code> controls how many sequence values will be reserved from the server, cached on the client, and doled out as need by subsequent <code>NEXT VALUE FOR</code> calls for that client connection to the cluster to save on <code>RPC</code> calls. If not specified, the phoenix.sequence.cacheSize config parameter defaul ting to 100 will be used for the <code>CACHE</code> value.</p> +<p>Example:</p> +<p class="notranslate"> +CREATE SEQUENCE my_sequence;<br />CREATE SEQUENCE my_sequence START WITH -1000<br />CREATE SEQUENCE my_sequence INCREMENT BY 10<br />CREATE SEQUENCE my_schema.my_sequence START 0 CACHE 10</p> + +<h3 id="drop_sequence" class="notranslate">DROP SEQUENCE</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +DROP SEQUENCE [IF EXISTS] <a href="index.html#sequence_ref">sequenceRef</a> +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">DROP SEQUENCE</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">IF EXISTS</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#sequence_ref">sequenceRef</a></code></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +DROP SEQUENCE [IF EXISTS] <a href="index.html#sequence_ref">sequenceRef</a> +</pre> +syntax-end --> +<p>Drops a sequence.</p> +<p>Example:</p> +<p class="notranslate"> +DROP SEQUENCE my_sequence<br />DROP SEQUENCE IF EXISTS my_schema.my_sequence</p> + +<h3 id="alter" class="notranslate">ALTER</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +ALTER {TABLE | VIEW} <a href="index.html#table_ref">tableRef</a> { { ADD [IF NOT EXISTS] <a href="index.html#column_def">columnDef</a> [,...] [<a href="index.html#options">options</a>] } | { DROP COLUMN [IF EXISTS] <a href="index.html#column_ref">columnRef</a> [,...] } | { SET <a href="index.html#options">options</a> } } +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">ALTER</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">TABLE</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">VIEW</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#table_ref">tableRef</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">ADD</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">IF NOT EXISTS</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code c lass="c"><a href="index.html#column_def">columnDef</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#options">options</a></code></td><td class="le"></td></tr></table></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">DROP COLUMN</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">IF EXISTS</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#column_ref">columnRef</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">SET <a href="index.html#options">options</a></code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +ALTER {TABLE | VIEW} <a href="index.html#table_ref">tableRef</a> { { ADD [IF NOT EXISTS] <a href="index.html#column_def">columnDef</a> [,...] [<a href="index.html#options">options</a>] } | { DROP COLUMN [IF EXISTS] <a href="index.html#column_ref">columnRef</a> [,...] } | { SET <a href="index.html#options">options</a> } } +</pre> +syntax-end --> +<p>Alters an existing table by adding or removing columns or updating table options. When a column is dropped from a table, the data in that column is deleted as well. <code>PK</code> columns may not be dropped, and only nullable <code>PK</code> columns may be added. For a view, the data is not affected when a column is dropped. Note that creating or dropping columns only affects subsequent queries and data modifications. Snapshot queries that are connected at an earlier timestamp will still use the prior schema that was in place when the data was written.</p> +<p>Example:</p> +<p class="notranslate"> +ALTER TABLE my_schema.my_table ADD d.dept_id char(10) VERSIONS=10<br />ALTER TABLE my_table ADD dept_name char(50), parent_id char(15) null primary key<br />ALTER TABLE my_table DROP COLUMN d.dept_id, parent_id;<br />ALTER VIEW my_view DROP COLUMN new_col;<br />ALTER TABLE my_table SET IMMUTABLE_ROWS=true,DISABLE_WAL=true;</p> + +<h3 id="create_index" class="notranslate">CREATE INDEX</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +CREATE INDEX [IF NOT EXISTS] <a href="index.html#name">indexName</a> +ON <a href="index.html#table_ref">tableRef</a> ( <a href="index.html#expression">expression</a> [ASC | DESC] [,...] ) +[ INCLUDE ( <a href="index.html#column_ref">columnRef</a> [,...] ) ] +[ ASYNC ] +[<a href="index.html#options">indexOptions</a>] [ SPLIT ON ( <a href="index.html#split_point">splitPoint</a> [,...] ) ] +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">CREATE INDEX</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">IF NOT EXISTS</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#name">indexName</a></code></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="d"><code class="c">ON <a href="index.html#table_ref">tableRef</a> ( <a href="index.html#expression">expression</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class=" c">ASC</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">DESC</code></td><td class="le"></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">INCLUDE ( <a href="index.html#column_ref">columnRef</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="l s"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table></td><td class="le"></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">ASYNC</code></td><td class="le"></td></tr></table><br /><table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#options">indexOptions</a></code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="rai lroad"><td class="d"><code class="c">SPLIT ON ( <a href="index.html#split_point">splitPoint</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +CREATE INDEX [IF NOT EXISTS] <a href="index.html#name">indexName</a> +ON <a href="index.html#table_ref">tableRef</a> ( <a href="index.html#expression">expression</a> [ASC | DESC] [,...] ) +[ INCLUDE ( <a href="index.html#column_ref">columnRef</a> [,...] ) ] +[ ASYNC ] +[<a href="index.html#options">indexOptions</a>] [ SPLIT ON ( <a href="index.html#split_point">splitPoint</a> [,...] ) ] +</pre> +syntax-end --> +<p>Creates a new secondary index on a table or view. The index will be automatically kept in sync with the table as the data changes. At query time, the optimizer will use the index if it contains all columns referenced in the query and produces the most efficient execution plan. If a table has rows that are write-once and append-only, then the table may set the <code>IMMUTABLE_ROWS</code> property to true (either up-front in the <code>CREATE TABLE</code> statement or afterwards in an <code>ALTER TABLE</code> statement). This reduces the overhead at write time to maintain the index. Otherwise, if this property is not set on the table, then incremental index maintenance will be performed on the server side when the data changes. As of the 4.3 release, functional indexes are supported which allow arbitrary expressions rather than solely column names to be indexed. As of the 4.4.0 release, you can specify the <code>ASYNC</code> keyword to create the index using a map reduce job.</p> +<p>Example:</p> +<p class="notranslate"> +CREATE INDEX my_idx ON sales.opportunity(last_updated_date DESC)<br />CREATE INDEX my_idx ON log.event(created_date DESC) INCLUDE (name, payload) SALT_BUCKETS=10<br />CREATE INDEX IF NOT EXISTS my_comp_idx ON server_metrics ( gc_time DESC, created_date DESC )<br /> DATA_BLOCK_ENCODING='NONE',VERSIONS=?,MAX_FILESIZE=2000000 split on (?, ?, ?)<br />CREATE INDEX my_idx ON sales.opportunity(UPPER(contact_name))</p> + +<h3 id="drop_index" class="notranslate">DROP INDEX</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +DROP INDEX [IF EXISTS] <a href="index.html#name">indexName</a> ON <a href="index.html#table_ref">tableRef</a> +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">DROP INDEX</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">IF EXISTS</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#name">indexName</a> ON <a href="index.html#table_ref">tableRef</a></code></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +DROP INDEX [IF EXISTS] <a href="index.html#name">indexName</a> ON <a href="index.html#table_ref">tableRef</a> +</pre> +syntax-end --> +<p>Drops an index from a table. When dropping an index, the data in the index is deleted. Note that since metadata is versioned, snapshot queries connecting at an earlier time stamp may still use the index, as the <code>HBase</code> table backing the index is not deleted.</p> +<p>Example:</p> +<p class="notranslate"> +DROP INDEX my_idx ON sales.opportunity<br />DROP INDEX IF EXISTS my_idx ON server_metrics</p> + +<h3 id="alter_index" class="notranslate">ALTER INDEX</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +ALTER INDEX [IF EXISTS] <a href="index.html#name">indexName</a> ON <a href="index.html#table_ref">tableRef</a> { DISABLE | REBUILD | UNUSABLE | USABLE } +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">ALTER INDEX</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">IF EXISTS</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#name">indexName</a> ON <a href="index.html#table_ref">tableRef</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">DISABLE</code></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c">REBUILD</code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c">UNUSABLE</code></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class= "d"><code class="c">USABLE</code></td><td class="le"></td></tr></table></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +ALTER INDEX [IF EXISTS] <a href="index.html#name">indexName</a> ON <a href="index.html#table_ref">tableRef</a> { DISABLE | REBUILD | UNUSABLE | USABLE } +</pre> +syntax-end --> +<p>Alters the state of an existing index. <code>DISABLE</code> will cause the no further index maintenance to be performed on the index and it will no longer be considered for use in queries. <code>REBUILD</code> will completely rebuild the index and upon completion will enable the index to be used in queries again. <code>UNUSABLE</code> will cause the index to no longer be considered for use in queries, however index maintenance will continue to be performed. <code>USABLE</code> will cause the index to again be considered for use in queries. Note that a disabled index must be rebuild and cannot be set as <code>USABLE</code>.</p> +<p>Example:</p> +<p class="notranslate"> +ALTER INDEX my_idx ON sales.opportunity DISABLE<br />ALTER INDEX IF EXISTS my_idx ON server_metrics REBUILD</p> + +<h3 id="explain" class="notranslate">EXPLAIN</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +EXPLAIN {<a href="index.html#select">select</a>|<a href="index.html#upsert_select">upsertSelect</a>|<a href="index.html#delete">delete</a>} +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">EXPLAIN</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#select">select</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="index.html#upsert_select">upsertSelect</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#delete">delete</a></code></td><td class="le"></td></tr></table></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +EXPLAIN {<a href="index.html#select">select</a>|<a href="index.html#upsert_select">upsertSelect</a>|<a href="index.html#delete">delete</a>} +</pre> +syntax-end --> +<p>Computes the logical steps necessary to execute the given command. Each step is represented as a string in a single column result set row.</p> +<p>Example:</p> +<p class="notranslate"> +EXPLAIN SELECT NAME, COUNT(*) FROM TEST GROUP BY NAME HAVING COUNT(*) > 2;<br />EXPLAIN SELECT entity_id FROM CORE.CUSTOM_ENTITY_DATA WHERE organization_id='00D300000000XHP' AND SUBSTR(entity_id,1,3) = '002' AND created_date < CURRENT_DATE()-1;</p> + +<h3 id="update_statistics" class="notranslate">UPDATE STATISTICS</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +UPDATE STATISTICS <a href="index.html#table_ref">tableRef</a> [ALL | INDEX | COLUMNS] +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">UPDATE STATISTICS <a href="index.html#table_ref">tableRef</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">ALL</code></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c">INDEX</code></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">COLUMNS</code></td><td class="le"></td></tr></table></td><td class="le"></td></tr></table></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +UPDATE STATISTICS <a href="index.html#table_ref">tableRef</a> [ALL | INDEX | COLUMNS] +</pre> +syntax-end --> +<p>Updates the statistics on the table and by default all of its associated index tables. To only update the table, use the <code>COLUMNS</code> option and to only update the <code>INDEX</code>, use the <code>INDEX</code> option. Note that the statistics for a single index may also be updated by using its full index name for the tableRef.</p> +<p>Example:</p> +<p class="notranslate"> +UPDATE STATISTICS my_table<br />UPDATE STATISTICS my_schema.my_table INDEX<br />UPDATE STATISTICS my_index<br />UPDATE STATISTICS my_table COLUMNS</p> + + +<h3 id="constraint" class="notranslate">Constraint</h3> +<!-- railroad-start --> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">CONSTRAINT <a href="index.html#name">constraintName</a> PRIMARY KEY ( <a href="index.html#name">columnName</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">ASC</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">DESC</code></td><td class="le"></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">ROW_TIMESTAMP</code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railro ad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> +<!-- railroad-end --> +<!-- syntax-start +<pre> +CONSTRAINT <a href="index.html#name">constraintName</a> PRIMARY KEY (<a href="index.html#name">columnName</a> [ASC | DESC] [ROW_TIMESTAMP] [,...]) +</pre> +syntax-end --> +<p>Defines a multi-part primary key constraint. Each column may be declared to be sorted in ascending or descending ordering. The default is ascending. One primary key column can also be designated as <code>ROW_TIMESTAMP</code> provided it is of one of the types: <code>BIGINT, UNSIGNED_LONG, DATE, TIME</code> and <code>TIMESTAMP</code>.</p> +<p>Example:</p> +<p class="notranslate">CONSTRAINT my_pk PRIMARY KEY (host,created_date)<br />CONSTRAINT my_pk PRIMARY KEY (host ASC,created_date DESC)<br />CONSTRAINT my_pk PRIMARY KEY (host ASC,created_date DESC ROW_TIMESTAMP)</p> + +<h3 id="options" class="notranslate">Options</h3> +<!-- railroad-start --> +<table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#name">familyName</a> .</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#name">name</a> =</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#value">value</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#bind_parameter">bindParameter</a></code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad" ><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td >class="le"></td></tr></table></td></tr></table> +<!-- railroad-end --> +<!-- syntax-start +<pre> +{ [<a href="index.html#name">familyName</a> .] <a href="index.html#name">name</a>= {<a href="index.html#value">value</a> | <a href="index.html#bind_parameter">bindParameter</a>}} [,...] +</pre> +syntax-end --> +<p>Sets a built-in Phoenix table property or an <code>HBase</code> table or column descriptor metadata attribute. The name is case insensitive. Built-in Phoenix table options include <code>SALT_BUCKETS, DISABLE_WAL, IMMUTABLE_ROWS, MULTI_TENANT, DEFAULT_COLUMN_FAMILY</code>, and <code>STORE_NULLS</code>. If the name is a known <code>HColumnDescriptor</code> attribute, then the value is applied to the specified column family or, if omitted, to all column families. Otherwise, the <code>HBase</code> metadata attribute value is applied to the <code>HTableDescriptor</code>. Note that no validation is performed on the property name or value, so unknown or misspelled options will end up as adhoc metadata attributes values on the <code>HBase</code> table.</p><p>The <code>SALT_BUCKETS</code> numeric property causes an extra byte to be transparently prepended to every row key to ensure an evenly distributed read and write load across all region servers. This is especially useful when your row key is always monotonically increasing and causing hot spotting on a single region server. However, even if it's not, it often improves performance by ensuring an even distribution of data across your cluster. The byte is determined by hashing the row key and modding it with the <code>SALT_BUCKETS</code> value. The value may be from 0 to 256, with 0 being a special means of turning salting off for an index in which the data table is salted (since by default an index has the same number of salt buckets as its data table). If split points are not defined for the table, the table will automatically be pre-split at each possible salt bucket value. For more information, see http://phoenix.incubator.apache.org/salted.html</p><p>The <code>DISABLE_WAL</code> boolean option when true causes <code>HBase</code> not to write data to the write-ahead-log, thus making updates faster at the expense of potentially losing data in the event of a region server failure. This option is useful when updating a table which is not the source-of-truth and thus making the lose of data acceptable.</p><p>The <code>IMMUTABLE_ROWS</code> boolean option when true declares that your table has rows which are write-once, append-only (i.e. the same row is never updated or deleted). With this option set, indexes added to the table are managed completely on the client-side, with no need to perform incremental index maintenance., thus improving performance. Deletes are not allowed (beyond dropping the entire table) and updates are expected to never update an existing row (failure to follow this will result in invalid indexes). For more information, see http://phoenix.incubator.apache.org/secondary_indexing.html</p><p>The <code>MULTI_TENANT</code> boolean option when true enables views to be created over the table across different tenants. This option is useful to share the same physical <code>HBase</code> table across many different tenants. For more information, see http://phoenix.incuba tor.apache.org/multi-tenancy.html</p><p>The <code>DEFAULT_COLUMN_FAMILY</code> string option determines the column family used used when none is specified. The value is case sensitive. If this option is not present, a column family name of '0' is used.</p><p>The <code>STORE_NULLS</code> boolean option (available as of Phoenix 4.3) determines whether or not null values should be explicitly stored in <code>HBase</code>. This option is generally only useful if a table is configured to store multiple versions in order to facilitate doing flashback queries (i.e. queries to look at the state of a record in the past).</p> +<p>Example:</p> +<p class="notranslate">IMMUTABLE_ROWS=true<br />DEFAULT_COLUMN_FAMILY='a'<br />SALT_BUCKETS=10<br />DATA_BLOCK_ENCODING='NONE',a.VERSIONS=10<br />MAX_FILESIZE=2000000000,MEMSTORE_FLUSHSIZE=80000000</p> + +<h3 id="hint" class="notranslate">Hint</h3> +<!-- railroad-start --> +<table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#scan_hint">scanHint</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="index.html#index_hint">indexHint</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="index.html#cache_hint">cacheHint</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="index.html#small_hint">smallHint</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="index.html#join_hint">joinHint</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#seek_to_column_hint">seekToColumnHint</a></code></td><td class ="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">, ...</code></td><td class="le"></td></tr></table></td></tr></table> +<!-- railroad-end --> +<!-- syntax-start +<pre> +{ <a href="index.html#scan_hint">scanHint</a> | <a href="index.html#index_hint">indexHint</a> | <a href="index.html#cache_hint">cacheHint</a> | <a href="index.html#small_hint">smallHint</a> | <a href="index.html#join_hint">joinHint</a> | <a href="index.html#seek_to_column_hint">seekToColumnHint</a> } [,...] +</pre> +syntax-end --> +<p>An advanced features that overrides default query processing behavior for decisions such as whether to use a range scan versus skip scan and an index versus no index. Note that strict parsing is not done on hints. If hints are misspelled or invalid, they are silently ignored.</p> +<p>Example:</p> +<p class="notranslate">SKIP_SCAN,NO_INDEX<br />NO_CACHE<br />INDEX(employee emp_name_idx emp_start_date_idx)<br />SMALL</p> + +<h3 id="scan_hint" class="notranslate">Scan Hint</h3> +<!-- railroad-start --> +<table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">SKIP_SCAN</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">RANGE_SCAN</code></td><td class="le"></td></tr></table> +<!-- railroad-end --> +<!-- syntax-start +<pre> +SKIP_SCAN | RANGE_SCAN +</pre> +syntax-end --> +<p>Use the <code>SKIP_SCAN</code> hint to force a skip scan to be performed on the query when it otherwise would not be. This option may improve performance if a query does not include the leading primary key column, but does include other, very selective primary key columns.</p><p>Use the <code>RANGE_SCAN</code> hint to force a range scan to be performed on the query. This option may improve performance if a query filters on a range for non selective leading primary key column along with other primary key columns</p> +<p>Example:</p> +<p class="notranslate">SKIP_SCAN<br />RANGE_SCAN</p> + +<h3 id="cache_hint" class="notranslate">Cache Hint</h3> +<!-- railroad-start --> +<code class="c">NO_CACHE</code> +<!-- railroad-end --> +<!-- syntax-start +<pre> +NO_CACHE +</pre> +syntax-end --> +<p>Use the <code>NO_CACHE</code> hint to prevent the results of the query from populating the <code>HBase</code> block cache. This is useful in situation where you're doing a full table scan and know that it's unlikely that the rows being returned will be queried again.</p> +<p>Example:</p> +<p class="notranslate">NO_CACHE</p> + +<h3 id="index_hint" class="notranslate">Index Hint</h3> +<!-- railroad-start --> +<table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">INDEX</code></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c">NO_INDEX</code></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c">USE_INDEX_OVER_DATA_TABLE</code></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">USE_DATA_OVER_INDEX_TABLE</code></td><td class="le"></td></tr></table> +<!-- railroad-end --> +<!-- syntax-start +<pre> +INDEX | NO_INDEX | USE_INDEX_OVER_DATA_TABLE | USE_DATA_OVER_INDEX_TABLE +</pre> +syntax-end --> +<p>Use the <code>INDEX</code>(<table_name> <index_name>...) to suggest which index to use for a given query. Double quotes may be used to surround a table_name and/or index_name to make them case sensitive. As of the 4.3 release, this will force an index to be used, even if it doesn't contain all referenced columns, by joining back to the data table to retrieve any columns not contained by the index.</p><p>Use the <code>NO_INDEX</code> hint to force the data table to be used for a query.</p><p>Use the <code>USE_INDEX_OVER_DATA_TABLE</code> hint to act as a tiebreaker for choosing the index table over the data table when all other criteria are equal. Note that this is the default optimizer decision.</p><p>Use the <code>USE_DATA_OVER_INDEX_TABLE</code> hint to act as a tiebreaker for choosing the data table over the index table when all other criteria are equal.</p> +<p>Example:</p> +<p class="notranslate">INDEX(employee emp_name_idx emp_start_date_idx)<br />NO_INDEX<br />USE_INDEX_OVER_DATA_TABLE<br />USE_DATA_OVER_INDEX_TABLE</p> + +<h3 id="small_hint" class="notranslate">Small Hint</h3> +<!-- railroad-start --> +<code class="c">SMALL</code> +<!-- railroad-end --> +<!-- syntax-start +<pre> +SMALL +</pre> +syntax-end --> +<p>Use the <code>SMALL</code> hint to reduce the number of <code>RPCs</code> done between the client and server when a query is executed. Generally, if the query is a point lookup or returns data that is likely in a single data block (64 KB by default), performance may improve when using this hint.</p> +<p>Example:</p> +<p class="notranslate">SMALL</p> + +<h3 id="seek_to_column_hint" class="notranslate">Seek To Column Hint</h3> +<!-- railroad-start --> +<table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">SEEK_TO_COLUMN</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">NO_SEEK_TO_COLUMN</code></td><td class="le"></td></tr></table> +<!-- railroad-end --> +<!-- syntax-start +<pre> +SEEK_TO_COLUMN | NO_SEEK_TO_COLUMN +</pre> +syntax-end --> +<p>Use the <code>SEEK_TO_COLUMN</code> hint to force the server to seek to navigate between columns instead of doing a next. If there are many versions of the same column value or if there are many columns between the columns that are projected, then this may be more efficient.</p><p>Use the <code>NO_SEEK_TO_COLUMN</code> hint to force the server to do a next to navigate between columns instead of a seek. If there are few versions of the same column value or if the columns that are projected are adjacent to each other, then this may be more efficient.</p> +<p>Example:</p> +<p class="notranslate">SEEK_TO_COLUMN<br />NO_SEEK_TO_COLUMN</p> + +<h3 id="join_hint" class="notranslate">Join Hint</h3> +<!-- railroad-start --> +<code class="c">NO_STAR_JOIN</code> +<!-- railroad-end --> +<!-- syntax-start +<pre> +NO_STAR_JOIN +</pre> +syntax-end --> +<p>Use the <code>NO_STAR_JOIN</code> hint to prevent the optimizer from using the star join query to broadcast the results of the querying one common table to all region servers. This is useful when the results of the querying the one common table is too large and would likely be substantially filtered when joined against one or more of the other joined tables.</p> +<p>Example:</p> +<p class="notranslate">NO_STAR_JOIN</p> + +<h3 id="column_def" class="notranslate">Column Def</h3> +<!-- railroad-start --> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#column_ref">columnRef</a> <a href="index.html#data_type">dataType</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">NOT</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">NULL</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td clas s="d"><code class="c">PRIMARY KEY</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">ASC</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">DESC</code></td><td class="le"></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">ROW_TIMESTAMP</code></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td></tr></table> +<!-- railroad-end --> +<!-- syntax-start +<pre> +<a href="index.html#column_ref">columnRef</a> <a href="index.html#data_type">dataType</a> [[NOT] NULL] [PRIMARY KEY [ASC | DESC] [ROW_TIMESTAMP]] +</pre> +syntax-end --> +<p>Define a new primary key column. The column name is case insensitive by default and case sensitive if double quoted. The sort order of a primary key may be ascending (<code>ASC</code>) or descending. The default is ascending. Additionally, if the column is the only column that forms the primary key, then it can be designated as <code>ROW_TIMESTAMP</code> column provided its data type is one of these: <code>BIGINT, UNSIGNED_LONG, DATE, TIME</code> and <code>TIMESTAMP</code>.</p> +<p>Example:</p> +<p class="notranslate">id char(15) not null primary key<br />key integer null<br />m.response_time bigint<br /><br />created_date date not null primary key row_timestamp<br />key integer null<br />m.response_time bigint</p> + +<h3 id="table_ref" class="notranslate">Table Ref</h3> +<!-- railroad-start --> +<table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#name">schemaName</a> .</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#name">tableName</a></code></td></tr></table> +<!-- railroad-end --> +<!-- syntax-start +<pre> +[<a href="index.html#name">schemaName</a> .] <a href="index.html#name">tableName</a> +</pre> +syntax-end --> +<p>References a table or view with an optional schema name qualifier</p> +<p>Example:</p> +<p class="notranslate">Sales.Contact<br />HR.Employee<br />Department</p> + +<h3 id="sequence_ref" class="notranslate">Sequence Ref</h3> +<!-- railroad-start --> +<table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#name">schemaName</a> .</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#name">sequenceName</a></code></td></tr></table> +<!-- railroad-end --> +<!-- syntax-start +<pre> +[<a href="index.html#name">schemaName</a> .] <a href="index.html#name">sequenceName</a> +</pre> +syntax-end --> +<p>References a sequence with an optional schema name qualifier</p> +<p>Example:</p> +<p class="notranslate">my_id_generator<br />my_seq_schema.id_generator</p> + +<h3 id="column_ref" class="notranslate">Column Ref</h3> +<!-- railroad-start --> +<table class="railroad"><tr class="railroad"><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#name">familyName</a> .</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c"><a href="index.html#name">columnName</a></code></td></tr></table> +<!-- railroad-end --> +<!-- syntax-start +<pre> +[<a href="index.html#name">familyName</a> .] <a href="index.html#name">columnName</a> +</pre> +syntax-end --> +<p>References a column with an optional family name qualifier</p> +<p>Example:</p> +<p class="notranslate">e.salary<br />dept_name</p> +
[... 574 lines stripped ...]
