HiveSQL 数据操控、查询语言（DML、DQL）

HiveSQL 数据操控、查询语言（DML、DQL）

1 Load——加载数据

• 将数据load加载到表中时，hive不会进行如何转换，加载操作是将数据文件移动到与Hive表对应的位置的纯复制/移动操作。

• 语法

  LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
  
  LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [INPUTFORMAT 'inputformat' SERDE 'serde'] (3.0 or later)
  

  • filepath：表示代移动数据的路径，可以引用一个文件，也可以是一个目录
  • local：
    • 如果指定了local，load命令将在本地文件系统中查找文件路径。如果指定了相对路径，他将相当于用户的当前工作目录进行解释
    • 如果没有指定LOCAL关键字，如果filepath指向的是一个完整的URI，hive会直接使用这个URI。 否则如果没有指定schema或者authority，Hive会使用在hadoop配置文件中定义的schema 和 authority，即参数fs.default.name指定的（不出意外，都是HDFS）
    • **注意：**如果对HiveServer2服务运行此命令。这里的本地文件系统指的是HiveServer2服务所在机器的本地Linux文件系统，不是Hive客户端所在的本地文件系统
  • overwrite：如果使用了overwrite关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。

• Hive3.0及更高版本中，除了移动复制操作之外，还支持其他加载操作，因为Hive在内部的某些场合下会将加载重写为insert as select

  • 如果表是具有分区，load命令中没有指定分区，则将load转换为insert as select ，并假定最后一组为分区列。如果文件不符合预期的架构，就会报错

2. insert——插入数据

• insert + select：将查询结果插入到表中

  INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;
  
  INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;
  

  • insert overwrite 将覆盖表或分区中的如何现有的数据
  • 需要保证查询结果列的数目和需要插入数据表格的列数目一致
  • 如果查询出来的数据类型和插入表格对应的数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会是NULL

• multiple inserts：多次插入，核心是：一次扫描。多次插入。功能就是减少扫描的次数

• dynamic partition insert：动态分区插入

  • 动态分区插入指的是：分区的值是由后续的select查询语句的结果来动态确定的，根据查询结果来自动分区

  • 配置参数

    set hive.exec.dynamic.partition = true;
    set hive.exec.dynamic.partition.mode = nonstrict;
    

  • 动态插入语法例子

    -- 库下有一张student学生表
    select * from student;
    
    -- 注意：分区字段名不能和表中的字段名重复
    create table student_partition(Sno int,Sname string,Sex string,Sage int) partitioned by(Sdept string);
    
    -- 执行动态分区插入操作
    insert into table student_partition partition(Sdept)
    select Sno,Sname,Sex,Sage,Sdept from student;
    -- 其中，Sno,Sname,Sex,Sage作为表的字段内容插入表中
    -- Sdept作为分区字段值
    

• insert + directory：支持将select查询的结果导出文件存放在文件系统中

  • 语法

    --标准语法:
    INSERT OVERWRITE [LOCAL] DIRECTORY directory1
        [