Help Center> MapReduce Service> Developer Guide (2.x and Earlier)> Hive Application Development> Hive APIs> HiveQL
View PDF

HiveQL

HiveQL supports all features of MRS Hive and the corresponding open-source Hive version. For details, see https://cwiki.apache.org/confluence/display/hive/languagemanual. Table 1 lists the mapping between MRS Hive versions and open-source Hive versions.

Table 1 Mapping between MRS Hive and open-source versions

MRS Version

Open-source Hive version

MRS 1.8.x or earlier

1.2.1

MRS 1.9.x

2.3.3

MRS 2.x

3.1.1

Table 2 lists the extended Hive statements provided by MRS (supported only in MRS 1.8.x and earlier versions).

Table 2 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.

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.

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.

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.

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.

Parent topic: Hive APIs