hive插入多条数据sql_Hive的SQL操作

1、分区表

1)创建分区表

hive> create table dept_partitions()

> partition by()

> row format

> delimited fields

> terminated by '';

例：

hive> create table dept_partitions(deptno int, dept string, loc string)

> partitioned by(day string)

> row format

> delimited fields

> terminated by '\t';

hive> load data local inpath '/root/dept.txt' into table dept_partitions

> partition(day='0228');

2)查询

全查询

hive> select * from dept_partitions;

注意：此时查看的是整个分区表中的数据

单分区查询

hive> select * from dept_partitions where day = ‘0228’;

注意：此时查看的是指定分区中的数据

联合查询

hive> select * from dept_partitions where day = ‘0228’ union select * from dept_partitions where day = ‘0302’;

添加单个分区

hive> alter table dept_partitions add partition(day = ‘0303’);

注意：如果想一次添加多个的话 空格分割即可

hive> alter table dept_partitions add partition(day = ‘0304’) partition(day = ‘0305’);

查看分区

hive> show partitions dept_partitions;

删除分区

hive> alter table dept_partitions drop partition(day=’0305’);

分区表在hdfs中分目录文件夹

hive> dfs -mkdir -p /user/hive/warehouse/dept_partitions/day=0305;

hive> dfs -put /root/dept.txt /user/hive/warehouse/dept_partitions/day=0305;<