forked from docs/mapreduce-service
proposalbot
d31d5b10fe
Reviewed-by: Kacur, Michal <michal.kacur@t-systems.com> Co-authored-by: proposalbot <proposalbot@otc-service.com> Co-committed-by: proposalbot <proposalbot@otc-service.com>
27 lines
13 KiB
ReStructuredText
27 lines
13 KiB
ReStructuredText
:original_name: mrs_01_2330.html
|
|
|
|
.. _mrs_01_2330:
|
|
|
|
Hive SQL
|
|
========
|
|
|
|
Hive SQL supports all features of Hive-3.1.0. For details, see https://cwiki.apache.org/confluence/display/hive/languagemanual.
|
|
|
|
:ref:`Table 1 <mrs_01_2330__en-us_topic_0000001173470502_tab1>` describes the extended Hive statements provided by .
|
|
|
|
.. _mrs_01_2330__en-us_topic_0000001173470502_tab1:
|
|
|
|
.. table:: **Table 1** Extended Hive statements
|
|
|
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| Extended Syntax | Syntax Description | Syntax Example | Example Description |
|
|
+====================================================================================================================================================================================================================================================================================================================================================================================================================================+=================================================================================================================================================================================================+====================================================================================================================================================================================================================================================================================================+===========================================================================================================================================================================================================================+
|
|
| CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name (col_name data_type [COMMENT col_comment], ...) [ROW FORMAT row_format] [STORED AS file_format] \| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...) ] ...... [TBLPROPERTIES ("groupId"=" group1 ","locatorId"="locator1")] ...; | The statement is used to create a Hive table and specify locators on which table data files locate. For details, see :ref:`Using HDFS Colocation to Store Hive Tables <mrs_01_0953>`. | CREATE TABLE tab1 (id INT, name STRING) row format delimited fields terminated by '\\t' stored as RCFILE TBLPROPERTIES("groupId"=" group1 ","locatorId"="locator1"); | The statement is used to create table **tab1** and specify locator1 on which the table data of **tab1** locates. |
|
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name (col_name data_type [COMMENT col_comment], ...) [ROW FORMAT row_format] [STORED AS file_format] \| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...) ] ... [TBLPROPERTIES (**'column.encode.columns'**\ ='col_name1,col_name2'\| '**column.encode.indices**'='col_id1,col_id2', **'column.encode.classname'**\ ='encode_classname')]...; | The statement is used to create a hive table and specify the table encryption column and encryption algorithm. For details, see :ref:`Using the Hive Column Encryption Function <mrs_01_0954>`. | create table encode_test(id INT, name STRING, phone STRING, address STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ('column.encode.indices'='2,3', 'column.encode.classname'='org.apache.hadoop.hive.serde2.SMS4Rewriter') STORED AS TEXTFILE; | The statement is used to create table **encode_test** and specify that column 2 and column 3 will be encrypted using the **org.apache.hadoop.hive.serde2.SMS4Rewriter** encryption algorithm class during data insertion. |
|
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| REMOVE TABLE *hbase_tablename* [WHERE where_condition]; | The statement is used to delete data that meets criteria from the Hive on HBase table. For details, see :ref:`Deleting Single-Row Records from Hive on HBase <mrs_01_0956>`. | remove table hbase_table1 where id = 1; | The statement is used to delete data that meets the criterion of "id = 1" from the table. |
|
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name (col_name data_type [COMMENT col_comment], ...) [ROW FORMAT row_format] **STORED AS inputformat 'org.apache.hadoop.hive.contrib.fileformat.SpecifiedDelimiterInputFormat'** outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'; | The statement is used to create a hive table and specify that the table supports customized row delimiters. For details, see :ref:`Customizing Row Separators <mrs_01_0955>`. | create table blu(time string, num string, msg string) row format delimited fields terminated by ',' **stored as inputformat 'org.apache.hadoop.hive.contrib.fileformat.SpecifiedDelimiterInputFormat'** outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'; | The statement is used to create table **blu** and set **inputformat** to **SpecifiedDelimiterInputFormat** so that the query row delimiter can be specified during the query. |
|
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|