Oracle/Hive/Impala SQL比较（3-4）

3 DML

3.1 Load Files

操作

Oracle

Hive

Impala

LOAD

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

同hive

3.2 Insert

操作

Oracle

Hive

Impala

INSERT

INSERT [ hint ]

   { single_table_insert | multi_table_insert } ;

single_table_insert ::=

insert_into_clause
{ values_clause [ returning_clause ]
| subquery 

} [ error_logging_clause ]

multi_table_insert ::=

{ ALL

  { insert_into_clause [ values_clause ] [error_logging_clause] }...

| conditional_insert_clause

} subquery

Oracle单表的values和subquery插入和其他两种差不多，多表插入是其他两种没有的

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;

INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;

INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]

hive的INSERT VALUES从0.14.0开始才有

[with_clause]

INSERT { INTO | OVERWRITE } [TABLE] table_name

  [(column_list)]

  [ PARTITION (partition_clause)]

{

    [hint_clause] select_statement

  | VALUES (value [, value ...]) [, (value [, value ...]) ...]

}

基本和hive一致，除了头部可以带with子句

3.3 Update

功能

Oracle

Hive

Impala

UPDATE

UPDATE [ hint ]

   { dml_table_expression_clause

   | ONLY (dml_table_expression_clause)

   } [ t_alias ]

 update_set_clause

   [ where_clause ]

   [ returning_clause ]

 [error_logging_clause] ;

建议参考官方手册

UPDATE tablename SET column = value [, column = value ...] [WHERE expression]

从0.14.0开始才有，存在一些限制，比如赋的值必须来自于表达式而不是子查询，不支持分区、分桶的列等

3.4 Delete

功能

Oracle

Hive

Impala

DELETE

DELETE [ hint ]

   [ FROM ]

   { dml_table_expression_clause

   | ONLY (dml_table_expression_clause)

   } [ t_alias ]

     [ where_clause ]

     [ returning_clause ]

 [error_logging_clause];

与UPDATE高度相似，建议参考官方手册

DELETE FROM tablename [WHERE expression];

从0.14.0开始才有，值得注意的是hive的DELETE（UPDATE也是）只要语句成功执行就自动提交

3.5 其他

功能

Oracle

Hive

Impala

MERGE

MERGE [ hint ]

   INTO [ schema. ] { table | view } [ t_alias ]

   USING { [ schema. ] { table | view }

         | subquery 

         } [ t_alias ]

   ON ( condition )

   [ merge_update_clause ]

   [ merge_insert_clause ]

   [ error_logging_clause ];

Oracle提供的将一个表上的多个INSERT,UPDATE,DELETE操作合并在一起的简便语法，非常实用，建议参考官方手册

     其他一些不常用的或者独有的就不列了。

4 Queries

4.1基本SELECT

功能

Oracle

Hive

Impala

SELECT … WHERE

语法略，WHERE子句的写法和hive是相同的

建议参考官方手册

[WITH CommonTableExpression (, CommonTableExpression)*]  

SELECT [ALL | DISTINCT] select_expr, select_expr, ...

  FROM table_reference

  [WHERE where_condition]

  [GROUP BY col_list]

  [ORDER BY col_list]

  [CLUSTER BY col_list

    | [DISTRIBUTE BY col_list] [SORT BY col_list]

  ]

 [LIMIT number]

WHERE子句的写法没有大区别

基本等同与hive，CLUSTER BY, DISTRIBUTE BY, SORT BY没有

DISTINCT/ALL/UNIQUE

SELECT后可接DISTINCT，UNIQUE（等同DISTINCT），ALL（全部返回，默认）三种关键字

有DISTINCT，ALL

只有DISTINCT

GROUP BY/HAVING

group_by_clause ::=

GROUP BY

   { expr

   | rollup_cube_clause

   | grouping_sets_clause

   }

     [, { expr

        | rollup_cube_clause

        | grouping_sets_clause

        }

     ]...

   [ HAVING condition ]

Oracle中ROLLUP, CUBE, GROUPING SETS这些BI中常用的功能

GROUP BY groupByExpression (, groupByExpression)*

也支持ROLLUP, CUBE, GROUPING SETS，语法和Oracle有细微差别

只支持最基本的用法

LIMIT

无（只能通过rownum限制结果集行数）

有

有

4.2 Sort

功能

Oracle

Hive

Impala

ORDER BY

order_by_clause ::=

ORDER [ SIBLINGS ] BY

{ expr | position | c_alias }

[ ASC | DESC ]

[ NULLS FIRST | NULLS LAST ]

  [, { expr | position | c_alias }

     [ ASC | DESC ]

     [ NULLS FIRST | NULLS LAST ]

  ]...

SIBLINGS关键字仅当使用了CONNECT BY时才有用；

另一个特点是，Oracle可以按表达式/列序号/列名三种之一排序，而其他两种都只支持列名

ORDER BY colName [ASC | DESC] (',' colName [ASC | DESC])*

hive很常规

ORDER BY col_ref [, col_ref ...] [ASC | DESC] [NULLS FIRST | NULLS LAST]

NULLS FIRST | NULLS LAST用于指定null排在结果集的头部还是尾部

SORT BY

仅hive支持，和ORDER BY区别是，它只对每个reducer内的数据排序，因此最后汇总后的数据可能不是严格排序的

4.3 Join

     Join可以说是差别最大的语法之一，因为对效率的影响最大，所以三种库都有不同的折衷和妥协。强烈建议看官方手册。

功能

Oracle

Hive

Impala

JOIN

join_clause ::=

table_reference

  { inner_cross_join_clause | outer_join_clause }...

inner_cross_join_clause::=

{ [ INNER ] JOIN table_reference

    { ON condition

    | USING (column [, column ]...)

    }

| { CROSS

  | NATURAL [ INNER ]

  }

  JOIN table_reference

}

outer_join_clause::=

  [ query_partition_clause ] [ NATURAL ]

outer_join_type JOIN table_reference

  [ query_partition_clause ]

  [ ON condition

  | USING ( column [, column ]...)

  ]

值得注意的点：

1，只有它支持非等值连接，即condition可以是任意条件表达式

2，只有它支持自然连接

3，支持USING指定同名列的语法

4，不支持半连接，必须用EXISTS替代

5，不支持反连接，必须用NOT IN替代

table_reference JOIN table_factor [join_condition]

| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition

| table_reference LEFT SEMI JOIN table_reference join_condition

| table_reference CROSS JOIN table_reference [join_condition]

值得注意的点：

1，hive仅支持等值连接，因为非等值连接用map/reduce范式无法实现

2，和Oracle相比直接支持半连接

3，反连接还是不支持

4，USING语法不支持

5，可用/*+ STREAMTABLE(a) */ 指示流表，hive连接的时候，默认会将最右端的表放进流中用来驱动，其余表全部读进内存中，因此内存容易爆。解决办法是将最大的表写在最右，或者用这个hint来指定最大的表为流表

6，可用/*+ MAPJOIN(b) */ 指示map端连接。这个也有三种用法：一表特小，可使用；所有表join的列都是分桶的，可使用；所有表join的列都是分桶且排序的，可使用（称为sort-merge join）

SELECT select_list FROM 

table_or_subquery1 [INNER] JOIN table_or_subquery2 | 

table_or_subquery1 {LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]} JOIN table_or_subquery2 |

 table_or_subquery1 {LEFT | RIGHT} SEMI JOIN table_or_subquery2 |

 table_or_subquery1 {LEFT | RIGHT} ANTI JOIN table_or_subquery2 |

    [ ON col1 = col2 [AND col3 = col4 ...] |

      USING (col1 [, col2 ...]) ]

  [other_join_clause ...]

[ WHERE where_clauses ];

值得注意的点：

1，这种语法只支持等值连接

2，支持USING指定同名列的语法

3，支持半连接

4，支持反连接

5，不支持自然连接

＝/>=/<=...  (+)

Oracle支持这种在where中写条件进行连接的方式（支持等值与非等值），并用(+)指示外连接，虽然用的人很多，但并不推荐

SELECT select_list FROM

 table_or_subquery1, table_or_subquery2 [, table_or_subquery3 ...]

  [other_join_clause ...]

WHERE

    col1 = col2 [AND col3 = col4 …];

SELECT select_list FROM 

table_or_subquery1 CROSS JOIN table_or_subquery2

  [other_join_clause ...]

[ WHERE where_clauses ];

Impala也支持where中连接，但第一种写法只支持等值连接，如果要使用非等值连接只能用CROSS JOIN。另外也不支持外连接

4.4 集合操作

功能

Oracle

Hive

Impala

UNION

写UNION就是去重的，没有DISTINCT写法

写UNION就是不去重

select_statement UNION [ALL | DISTINCT] select_statement ...

hive1.2.0后才支持UNION DISTINCT

同hive

INTERSECT/MINUS

支持

4.5 Subqueries

功能

Oracle

Hive

Impala

子查询

用途很广：

1，INSERT或CREATE TABLE语句中定义行集

2，CREATE VIEW或CREATE METERIALIZED VIEW语句中定义行集

3，UPDATE语句中定义要赋的一个或多个值

4，WHERE段、HAVING段、或SELECT/UPDATE/DELETE语句的START WITH段中提供条件

5，通过包一个查询来提供要操作的表，比如SELECT语句的FROM段，或INSERT/UPDATE/DELETE语句的表名处

SELECT ... FROM (subquery) name ...

SELECT ... FROM (subquery) AS name ...

hive只在FROM段中支持子查询

SELECT select_list FROM table_ref [, table_ref ...]

WHERE value comparison_operator (scalar_select_statement)

WHERE value [NOT] IN (select_statement)

WHERE [NOT] EXISTS (correlated_select_statement)

WHERE NOT EXISTS (correlated_select_statement)

支持FROM段和WHERE段中的子查询

4.6 其他

功能

Oracle

Hive

Impala

dual

有

有

REGEX

SELECT后的列名支持正则表达式

     还有很多暂时没想到。