1.创建表时array类型的数据列需要写成ARRAY<STRING>
,如下图:
2.听说hive没有主键的概念?
3.hive建表时设置存储格式:
create table database.tablename (
id int,
name string,
age int
birthday timestamp
) comment 'tablename' row format delimited fields terminated by '\t' lines terminated by '\n';
<!-- 文件导入数据或导出数据时,'\t'为列分隔符,'\n'为行分割符 -->
- hive本地文件数据导入表命令:
load data local inpath '../a.txt' [overwrite] into table tablename
5.hive 不支持select * from tablename group by columnname
,
需要用join on连接。
select * from tablename t1 join (
select columnname from tablename group by columnname having ...
) t2 on t1.columnname=t2.columnname;
6.hive没有exists关键字,需要用left semi join
代替,join子句中右边的表只能在on子句中设置过滤条件,在where子句、select子句或其他地方过滤都不行。
select * from tablename left semi join (
select * from tablename
) t2 on t2.columnname = value;