Hive学习（二）---hive的表操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
###hive的基本操作
1.查看数据库：show databases;
2.创建数据库：create database db1;
3.创建数据库的标准写法：create database if not exists db2;
4.创建数据库指定的hdfs路径：create database db3 location '/hive_db';
5.查看数据库结构：desc database db1;
6.添加描述信息：alter database db1 set dbproperties('datais='cwx');
7.查看拓展属性：desc database extended db1;
8.筛选查询的数据库：show databases like 'db*';
9.删除数据库：drop database db1;
10.删除数据库标准写法：drop database if exists db1;
11.创建表：create table db1(id int,name string) row format delimited fields terminated by "t";
12.加载数据：load data local inpath '/root/test.txt' into table emp;
13.查询并保存到一歌表中：create table if not exists tb11 as select * from tb1 where id='1';
14.查询表结构：desc formatted tb1;
15.创建外部表：create external table if not exists tb2(id int ,name string) row format delimited fields terminated  by 't';
###分区表
1.创建分区表：create table part_tb(depno int,dept string,loc string )partitioned by(day string) row format delimited fields terminated by 't';
2.加载数据
load data local path '/root/dept.txt' into table part_tb partition(day='1111');
3.添加分区：alter table part_tb add partition(day='1122');
4.单分区查询：select * from part_tb where day='1111';
5.全分区查询：select *from part_tb;
6.查询表结构:desc formatted part_tb;
7.删除单个分区：alter table part_tb drop partition(day='1122');
###修改表
1.修改表名：alter table tb1 rename to  tb1new;
2.添加列：alter table tb add columns(phone string isstu int);
3.更新列：alter table tb change column phone phonenow int;
###DML数据操作
1加载hdfs中的数据：load data  inpath '/hdfs.txt' into table tmp;
2.覆盖原有的数据：load data inpath '/hdfs.txt' overwrite into table tb;
3.insert into table part_tb partition (month='2222') values(1,'swdsw','dwdw','dwdw');