Modified: phoenix/site/publish/language/index.html URL: http://svn.apache.org/viewvc/phoenix/site/publish/language/index.html?rev=1813354&r1=1813353&r2=1813354&view=diff ============================================================================== --- phoenix/site/publish/language/index.html (original) +++ phoenix/site/publish/language/index.html Wed Oct 25 20:48:50 2017 @@ -161,7 +161,1632 @@ <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="#declare_cursor">DECLARE CURSOR</a><br /> + + <a href="#open_cursor">OPEN CURSOR</a><br /> + + <a href="#fetch_next">FETCH NEXT</a><br /> + + <a href="#close">CLOSE</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 /> + + <a href="#create_schema">CREATE SCHEMA</a><br /> + + <a href="#use">USE</a><br /> + + <a href="#drop_schema">DROP SCHEMA</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="#declare_cursor" >DECLARE CURSOR</a><br /> + + <a href="#open_cursor" >OPEN CURSOR</a><br /> + + <a href="#fetch_next" >FETCH NEXT</a><br /> + + <a href="#close" >CLOSE</a><br /> + + <a href="#create_table" >CREATE TABLE</a><br /> + </td><td class="index"> + + <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 /> + </td><td class="index"> + + <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 /> + + <a href="#create_schema" >CREATE SCHEMA</a><br /> + + <a href="#use" >USE</a><br /> + + <a href="#drop_schema" >DROP SCHEMA</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="#serial_hint" >Serial 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="#serial_hint" >Serial 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 /> + </td><td class="index"> + + <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 /> + </td><td class="index"> + + <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 /> + </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>} ] +[ OFFSET {<a href="index.html#bind_parameter">bindParameter</a> | <a href="index.html#number">number</a>} [ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } {<a href="index.html#bind_parameter">bindParameter</a> | <a href="index.html#number">number</a>} { ROW | ROWS } ONLY ] +</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><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">OFFSET</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><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">ROW</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">ROWS</code></td><td class="le"></td></tr></table></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">FETCH</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">FIRST</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">NEXT</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#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><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">ROW</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">ROWS</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">ONLY</code></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>} ] +[ OFFSET {<a href="index.html#bind_parameter">bindParameter</a> | <a href="index.html#number">number</a>} [ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } {<a href="index.html#bind_parameter">bindParameter</a> | <a href="index.html#number">number</a>} { ROW | ROWS } ONLY ] +</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>(or <code>FETCH FIRST</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>(or <code>FETCH FIRST</code>) clause is executed after the <code>ORDER BY</code> clause to support top-N type queries. <code>OFFSET</code> clause skips that many rows before beginning to return rows. 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 * FROM TEST LIMIT 1000 OFFSET 100;<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> [,...] ) +[ON DUPLICATE KEY { IGNORE | UPDATE <a href="index.html#column_ref">columnRef</a> = <a href="index.html#operand">operand</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><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">ON DUPLICATE KEY</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">IGNORE</code></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">UPDATE <a href="index.html#column_ref">columnRef</a> = <a href="index.html#operand">operand</a></code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></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> [,...] ) +[ON DUPLICATE KEY { IGNORE | UPDATE <a href="index.html#column_ref">columnRef</a> = <a href="index.html#operand">operand</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>Use the <code>ON DUPLICATE KEY</code> clause (available in Phoenix 4.9) if you need the <code>UPSERT</code> to be atomic. Performance will be slower in this case as the row needs to be read on the server side when the commit is done. Use <code>IGNORE</code> if you do not want the <code>UPSERT</code> performed if the row already exists. Otherwise, with <code>UPDATE</code>, the expression will be evaluated and the result used to set the column, for example to perform an atomic increment. An <code>UPSERT</code> to the same row in the same commit batch will be processed in the order of execution.</p> +<p>Example:</p> +<p class="notranslate"> +UPSERT INTO TEST VALUES('foo','bar',3);<br />UPSERT INTO TEST(NAME,ID) VALUES('foo',123);<br />UPSERT INTO TEST(ID, COUNTER) VALUES(123, 0) ON DUPLICATE KEY UPDATE COUNTER = COUNTER + 1;<br />UPSERT INTO TEST(ID, MY_COL) VALUES(123, 0) ON DUPLICATE KEY IGNORE;</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="declare_cursor" class="notranslate">DECLARE CURSOR</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +DECLARE CURSOR <a href="index.html#name">cursorName</a> FOR <a href="index.html#select_statement">selectStatement</a> +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">DECLARE CURSOR <a href="index.html#name">cursorName</a> FOR <a href="index.html#select_statement">selectStatement</a></code></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +DECLARE CURSOR <a href="index.html#name">cursorName</a> FOR <a href="index.html#select_statement">selectStatement</a> +</pre> +syntax-end --> +<p>Creates a cursor for the select statement</p> +<p>Example:</p> +<p class="notranslate"> +DECLARE CURSOR TEST_CURSOR FOR SELECT * FROM TEST_TABLE</p> + +<h3 id="open_cursor" class="notranslate">OPEN CURSOR</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +OPEN CURSOR <a href="index.html#name">cursorName</a> +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">OPEN CURSOR <a href="index.html#name">cursorName</a></code></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +OPEN CURSOR <a href="index.html#name">cursorName</a> +</pre> +syntax-end --> +<p>Opens already declared cursor to perform <code>FETCH</code> operations</p> +<p>Example:</p> +<p class="notranslate"> +OPEN CURSOR TEST_CURSOR</p> + +<h3 id="fetch_next" class="notranslate">FETCH NEXT</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +FETCH NEXT [n ROWS] FROM <a href="index.html#name">cursorName</a> +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">FETCH NEXT</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">n ROWS</code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">FROM <a href="index.html#name">cursorName</a></code></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +FETCH NEXT [n ROWS] FROM <a href="index.html#name">cursorName</a> +</pre> +syntax-end --> +<p>Retrieves next or next n rows from already opened cursor</p> +<p>Example:</p> +<p class="notranslate"> +FETCH NEXT FROM TEST_CURSOR<br />FETCH NEXT 10 ROWS FROM TEST_CURSOR</p> + +<h3 id="close" class="notranslate">CLOSE</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +CLOSE <a href="index.html#name">cursorName</a> +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">CLOSE <a href="index.html#name">cursorName</a></code></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +CLOSE <a href="index.html#name">cursorName</a> +</pre> +syntax-end --> +<p>Closes an already open cursor</p> +<p>Example:</p> +<p class="notranslate"> +CLOSE TEST_CURSOR</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 [LOCAL] 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</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">LOCAL</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">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">expres sion</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 c lass="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="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"><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#o ptions">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="railroad"><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 [LOCAL] 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] [SET <a href="index.html#options">guidepostOptions</a>] +</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><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 cl ass="c">SET <a href="index.html#options">guidepostOptions</a></code></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] [SET <a href="index.html#options">guidepostOptions</a>] +</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. The statistics for a single index may also be updated by using its full index name for the tableRef. The default guidepost properties may be overridden by specifying their values after the <code>SET</code> keyword. Note that when a major compaction occurs, the default guidepost properties will be used again.</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<br />UPDATE STATISTICS my_table SET phoenix.stats.guidepost.width=50000000</p> + +<h3 id="create_schema" class="notranslate">CREATE SCHEMA</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +CREATE SCHEMA [IF NOT EXISTS] <a href="index.html#name">schemaName</a> +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">CREATE SCHEMA</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">schemaName</a></code></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +CREATE SCHEMA [IF NOT EXISTS] <a href="index.html#name">schemaName</a> +</pre> +syntax-end --> +<p>creates a schema and corresponding name-space in hbase. To enable namespace mapping, see https://phoenix.apache.org/tuning.html</p><p>User that execute this command should have admin permissions to create namespace in <code>HBase</code>.</p> +<p>Example:</p> +<p class="notranslate"> +CREATE SCHEMA IF NOT EXISTS my_schema<br />CREATE SCHEMA my_schema</p> + +<h3 id="use" class="notranslate">USE</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +USE { <a href="index.html#name">schemaName</a> | DEFAULT } +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">USE</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#name">schemaName</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">DEFAULT</code></td><td class="le"></td></tr></table></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +USE { <a href="index.html#name">schemaName</a> | DEFAULT } +</pre> +syntax-end --> +<p>Sets a default schema for the connection and is used as a target schema for all statements issued from the connection that do not specify schema name explicitly. <code>USE DEFAULT</code> unset the schema for the connection so that no schema will be used for the statements issued from the connection.</p><p>schemaName should already be existed for the <code>USE SCHEMA</code> statement to succeed. see <code>CREATE SCHEMA</code> for creating schema.</p> +<p>Example:</p> +<p class="notranslate"> +USE my_schema<br />USE DEFAULT</p> + +<h3 id="drop_schema" class="notranslate">DROP SCHEMA</h3> +<!-- railroad-start --> +<pre name="bnf" style="display: none"> +DROP SCHEMA [IF EXISTS] <a href="index.html#name">schemaName</a> +</pre> +<div name="railroad"> +<table class="railroad"><tr class="railroad"><td class="d"><code class="c">DROP SCHEMA</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">schemaName</a></code></td></tr></table> +</div> +<!-- railroad-end --> +<!-- syntax-start +<pre> +DROP SCHEMA [IF EXISTS] <a href="index.html#name">schemaName</a> +</pre> +syntax-end --> +<p>Drops a schema and corresponding name-space from hbase. To enable namespace mapping, see https://phoenix.apache.org/tuning.html</p><p>This statement succeed only when schema doesn't hold any tables.</p> +<p>Example:</p> +<p class="notranslate"> +DROP SCHEMA IF EXISTS my_schema<br />DROP SCHEMA my_schema</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> = <a href="index.html#value">value</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> +<!-- 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> } [,...] +</pre> +syntax-end -->
[... 745 lines stripped ...] Modified: phoenix/site/publish/tuning_guide.html URL: http://svn.apache.org/viewvc/phoenix/site/publish/tuning_guide.html?rev=1813354&r1=1813353&r2=1813354&view=diff ============================================================================== --- phoenix/site/publish/tuning_guide.html (original) +++ phoenix/site/publish/tuning_guide.html Wed Oct 25 20:48:50 2017 @@ -668,7 +668,7 @@ <div class="row"> <div class="span12"> <p class="pull-right"><a href="#">Back to top</a></p> - <p class="copyright">Copyright ©2013-2017 <a href="http://www.apache.org";>Apache Software Foundation</a>. All Rights Reserved.</p> + <p class="copyright">Copyright ©2017 <a href="http://www.apache.org";>Apache Software Foundation</a>. All Rights Reserved.</p> </div> </div> </div>
