sql与HiveQL一些区别:
IN
SQL中可以使用IN操作符来规定多个值:
SELECT * FROM Persons WHERE LastName IN ('Adams','Carter');HiveQL目前是不支持IN操作符的,需要通过转换为多个OR连接的条件:
SELECT * FROM Persons WHERE LastName = 'Adams' OR LastName = 'Carter';INNER JOIN
SQL中对两表内联可以写成:
SELECT a.col, b.col FROM t1 a, t2 b WHERE a.id=b.id;但这在HiveQL中是不支持的,需转为JOIN关键字的写法,如:
SELECT a.col, b.col FROM t1 a JOIN t2 b ON a.id=b.id;
IS [NOT] NULL
SQL中null代表空值, 值得警惕的是, 在HiveQL中String类型的字段若是空(empty)字符串, 即长度为0, 那么对它进行IS NULL的判断结果是False.
Create TableCREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name data_type [COMMENT col_comment], ...)] [COMMENT table_comment] [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] [ROW FORMAT row_format] [STORED AS file_format] [LOCATION hdfs_path]
外部表的使用场景,当多个系统需要使用表中的数据时,当某个系统删除了表时,外部表相应的数据不会被删除,有效的保证了数据的安全性