Hive SQL基础

Hive SQL基础

1. 创建表

   create [external] table [if not exists] test(
   	id int [comment 'id primary key']
           name
   )
   [row format delimited,
   fields terminated by '指定分隔符'];
   

   1. 添加external，则创建的是外部表

   2. 内部表（MANAGED_TABLE）：默认创建内部表，托管表，管理表

      1. 分区表
      2. 分桶表
      3. 删除内部表时，会删除数据本身和元数据信息

   3. 外部表（EXTERNAL_TABLE）：关联表，需要搭配location

      1. 分区表
      2. 分桶表
      3. 数据不是hive拥有和管理的，而只管理元数据生命周期，可以访问实际数据

   4. 区别

      

      1. 内部表适合数据临时处理转换等操作，及对数据进行清洗、统计分析、计算等
      2. 外部表适用于数据的长期存储、备份和共享
      3. Hive对内部表有绝对控制权
      4. 删除内部表时，会从Metastore中删除表元数据，还会从HDFS中删除其所有数据和文件
      5. 删除外部表时，只会从MetaStore中删除表的元数据，并保持HDFS位置中的实际数据不变

   5. 实操

      create external table test_hivedb1(
      	id int,
           name string
      )
      row format delimited
      fields terminated by ',';
      
      -- 查看
      describe formatted test_hivedb1;
      
      -- 快速创建  -copy
      create table tb_computer_copy as select * from tb_computer;
      create table tb_phone_copy like tb_phone;
      

   6. 数据类型

      1. 原生数据类型
         1. int
         2. float
         3. double
         4. char
         5. varchar
         6. string
         7. timestamp
         8. data
      2. 复杂类型
         1. array
         2. map
         3. strcut
         4. union

2. 删除表

   1. 内部表：可以删除表和表数据
   2. 外部表
   drop table 表名;
   
   
   -- 清空表数据
   truncate table 表名;
   

3. 修改表

   1、添加一列
   	alter table 表名 add columns(字段名 数据类型)
   2、修改字段名
   	alter table 表名 change 原字段 新字段名 类型;
   3、修改表名
   	alter table 表名 rename to 新表名
   4、修改表位置路径
   	alter table 表名 set location  '路径'；
   

4. 操作表数据

   -- 插入表数据
   insert into 表名 values(1,"lyt","woman");
   
   -- 修改表数据(不支持修改)
   Attempt to do update or delete using transaction manager that does not support these operations.
   
   -- 查看表数据
   select * from 表名;
   

5. 上传文件映射表：通过HDFS网页上传到对应目录

6. 外部表操作

   1. 创建表
      

      -- 创建库
      create database tb_test_stu; 
      -- 创建外部表
      create external table outer_stu(
      	id int,
              name string,
              gender string
      )
      comment "external_stu_table"
      row format delimited fields terminated by ","
      location "/hello"
      tblproperties ("create_time"="2023-08-30");
      -- 查看创建表语句、
      show create table outer_stu;
      
      -- 设定路径、注释、属性
      alter table outer_stu replace columns (字段名 类型，字段名 类型，...);    --注意：所有的字段都要写
      
      -- 修改表属性
      alter table outer_stu set tblproperties (属性名=值，属性名=值，......);
      alter table outer_stu set tblproperties ("create_time"="2023-09-01");
      
      -- 查看
      sleect * from outer_stu;
      

7. 内部表与外部表转换

   1. 当仅临时使用数据时，使用外部表，建议使用内部表

   2. 不建议修改表的存储路径地址，因此修改表路径仅作了解

   3. 相互切换

      1. 修改表属性

         alter table outer_stu set tblproperties("EXTERNAL"="TRUE")  # TURE为外部表，FALSE为内部表
         # external和true必须为大写
         
         -- 实例
         -- 创建内部表
         create table inner_stu(
         	id int,
                 name string,
                 gender string
         )
         comment "external_stu_table"
         row format delimited fields terminated by ","
         location "/hello"
         tblproperties ("create_time"="2023-08-30");
         
         -- -- 查看table type
         desc formatted inner_stu;
         
         --  切换
          alter table inner_stu set tblproperties ("EXTERNAL"="TRUE");
          alter table inner_stu set tblproperties ("EXTERNAL"="FALSE");
         

8. 数据导入导出

   1. 数据导入
      1. HDFS页面导入
      2. insert into table(字段名,字段名,…) values(值, 值, …)
      3. 将已有数据的表导入数据到新表
         1. insert into [table] 表名新 select * from 已有表名；（追加数据）
         2. insert overwrite table 新表名 select * from 已有表名;（数据覆盖）
      4. hdfs dfs -put /本地路径 /HDFS中的路径 （复制上传）
      5. 使用load语法加载数据到HDFS（直接移动源文件）
   2. 数据导出
      1. 通过HDFS页面导出
      2. 使用hdfs dfs -get 方式导出
      3. 使用类SQL命令导出数据
         1. insert overwrite [local] directory “存放目录路径名” select 语句;
         2. 加local表示导出到Linux本地
      4. shell命令导出
         1. hive -e 执行语句 > 文件名