Hive 基本操作

╰半橙微兮° 2023-06-19 06:56 22阅读 0赞

> hive最主要做查询 不涉及删除修改 默认不支持删除修改，默认不支持事务，并不完全支持标准sql

#### 一.HQL操作初体验 ####

1.创建表

# 创建表 row format delimited fields terminated by ',' 指定了字段的分隔符为逗号，所以load数据的时候，load的文本也要为逗号，否则加载后为NULL。hive只支持单个字符的分隔符，hive默认的分隔符是\001
    hive> CREATE TABLE student(classNo string, stuNo string, score int) row format delimited fields terminated by ',';

2.将数据load到表中  
在本地文件系统创建一个如下的文本文件：/root/student.txt

C01,N0101,82
    C01,N0102,59
    C01,N0103,65
    C02,N0201,81
    C02,N0202,82
    C02,N0203,79
    C03,N0301,56
    C03,N0302,92
    C03,N0306,72

# 将student.txt文件复制到hive的warehouse目录中，这个目录由hive.metastore.warehouse.dir配置项设置，默认值为/usr/local/apache-hive-2.3.6/warehouse . Overwrite选项将导致Hive事先删除student目录下所有的文件, 并将文件内容映射到表中
    hive> load data local inpath '/root/student.txt'overwrite into table student;
    
    # 也可以从外部直接将文件put进去
    # hadoop fs -put /root/student.txt /usr/local/apache-hive-2.3.6/warehouse/student

3.查询表中的数据

select * from student;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NvY2t0YWlsX3B5_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NvY2t0YWlsX3B5_size_16_color_FFFFFF_t_70 1]

#### 二.内部表和外部表 ####

**内部表**(managed table): 数据默认保存在hive.metastore.warehouse.dir配置项设置的路径;删除时,直接删除元数据(metabata)及存储数据;修改表会将修改直接同步给元数据.  
**外部表**(external table):数据由HDFS管理,存在hdfs任意位置;删除时,仅删除元数据;对表结构和分区进行修改,则需要修复(MSCK REPAIR TABLE table\_name;)

# 创建一个外部表
    hive> CREATE EXTERNAL TABLE student2 (classNo string, stuNo string, score int) row format delimited fields terminated by ',' location '/tmp/student';

# 装载数据
    hadoop fs -put /root/student.txt /tmp/student

![在这里插入图片描述][20191205194119889.png]

#### 三.分区表 ####

# 员工数据
    tom,4300
    jerry,12000
    mike,13000
    jake,11000
    rob,10000

#  创建一张员工表employee   设置partitioned分区为date2
    hive> create table employee (name string,salary bigint) partitioned by (date2 string) row format delimited fields terminated by ',' lines terminated by '\n' stored as textfile;
    
    # 添加分区
    hive> alter table employee add if not exists partition(date2='2018-12-01');
    hive> alter table employee add if not exists partition(date2='2018-12-02');
    hive> alter table employee add if not exists partition(date2='2018-12-03');
    
    # 加载数据到分区
    hive> load data local inpath '/root/employee.txt' into table employee partition(date2='2018-12-01');
    hive> load data local inpath '/root/employee.txt' into table employee partition(date2='2018-12-02');
    hive> load data local inpath '/root/employee.txt' into table employee partition(date2='2018-12-03');
    
    # 查看表的分区
    hive> show partitions employee;
    # 根据分区查找相应的数据
    hive> select * from employee where date2='2018-12-01';

参看数据在hdfs中的情况  
hadoop fs -ls /usr/local/apache-hive-2.3.6/warehouse/employee  
![在这里插入图片描述][20191205195529508.png]

##### 如果重复加载同名文件，不会报错，会自动创建一个\*\_copy\_1.txt #####

load data local inpath '/home/hadoop/tmp/employee.txt' into table employee partition(date1='2018-12-01');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NvY2t0YWlsX3B5_size_16_color_FFFFFF_t_70 2]

# 查看表在hdfs中的情况
    hadoop fs -ls /usr/local/apache-hive-2.3.6/warehouse/employee/date2=2018-12-01

![在这里插入图片描述][20191205195923550.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NvY2t0YWlsX3B5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191205083156220.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NvY2t0YWlsX3B5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NvY2t0YWlsX3B5_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2019120508325414.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NvY2t0YWlsX3B5,size_16,color_FFFFFF,t_70
[20191205194119889.png]: https://img-blog.csdnimg.cn/20191205194119889.png
[20191205195529508.png]: https://img-blog.csdnimg.cn/20191205195529508.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NvY2t0YWlsX3B5_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191205195818534.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NvY2t0YWlsX3B5,size_16,color_FFFFFF,t_70
[20191205195923550.png]: https://img-blog.csdnimg.cn/20191205195923550.png