Hive中的DML语句的详细梳理

最新推荐文章于 2023-08-05 13:21:35 发布

原创最新推荐文章于 2023-08-05 13:21:35 发布 · 463 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hive #SQL #DML #数据库

数据仓库Hive学习总结专栏收录该内容

25 篇文章

订阅专栏

本文详细梳理了Hive中的DML语句，包括如何插入数据到数据表、保存到本地磁盘，使用select查询，以及Export和Import操作。对于数据库管理和数据处理的初学者，是理解Hive数据操作的重要参考。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DML:Data Manipulation Language 数据操纵语言

1、插入数据到数据表

1、建表后插入数据
a)语法：
    LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename 
        [PARTITION (partcol1=val1, partcol2=val2 ...)]

b)注意：
    不加local剪切hdfs上的数据到hive数据目录，加local复制linux本地的数据到hive数据目录


c)示例：
    creat table emp (id int,name string) row format delimited 
    fields terminated by '\t' lines terminated by '\n';
    load  data [local]  inpath '/user/hive/warehouse/test1.db/test' into table emp;
    导入hdfs数据是剪切，导入本地Linux数据是复制。

d)非DML方式：
    第1种方式：
        creat table emp (id int,name string)row format delimited 
            fields terminated by '\t' lines terminated by '\n'; 
            location hdfs_path;
        hdfs dfs -put /tmp/emp /user/hive/warehouse/test1.db/test；

    第2种方式：
        creat table emp (id int,name string) row format delimited
            fields terminated by '\t' lines terminated by '\n';
        hdfs dfs -put /tmp/emp /user/hive/warehouse/test1.db/test；
        msck repair table emp;强制刷新元信息


2、建表时插入数据（单级插入）
a)语法：
    INSERT (OVERWRITE|INTO) TABLE tablename1 
        [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]]
         select_statement1 FROM from_statement;
   #####overwrite是覆盖，into是追加。into的时候不要带if not exits。没有表怎么追加啊

b)示例：
    insert overwrite table emp2  select id ,name from emp;
    注意：
        表emp2结构和select子句查出来的列数量和类型及顺序都要一致，否则报错或出现业务上的问题。


3、建表时插入数据（多级插入）

语法：
    FROM from_statement

    INSERT (OVERWRITE|INTO) TABLE tablename1 [PARTITION (partcol1=val1 ...) 
        [IF NOT EXISTS]] select_statement1

    [INSERT (OVERWRITE|INTO) TABLE tablename1 [PARTITION (partcol1=val1 ...) 
        [IF NOT EXISTS]] select_statement2....];
    
示例：
    from emp1
        insert overwrite table emp2  select name, id
        insert into table emp3 select name, age;


注意：
    当要从一个表中查询多个数据插入新表时，如果单级插入，每次执行SQL语句都要执行一个MR程序。 
    每个MR程序中的shuffle和资源调度都十分消耗性能和浪费时间。
    而使用多级插入，只会有一个MR程序，可以提升效率，节省时间。
    必须会。

3、插入数据到本地磁盘

1、单级插入

语法：
    INSERT OVERWRITE [LOCAL] DIRECTORY directory1
        [ROW FORMAT row_format] [STORED AS file_format] 
        SELECT ... FROM ...

注意：
    overwrite：覆盖。所以写路径的时候一定不要写根目录，否则后果很严重。
    local:写local数据导入本地目录，不写则导入hdfs目录。
    #####：目录可以不存在，hive会自动创建，当然，hive要有权限才行。

    


2、多级插入

语法：
    FROM from_statement
        INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
        [INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

    from：from子句的数据来源刻是一个子查询，但子查询的表必须有一个别名
            from (select *from emp) tmp

4、select相关

1、union all:
    select * from emp where id=1 union all select * from emp2;
    分组聚合会产生数据倾斜，而 union all 常用于处理数据倾斜问题

2、case....when....then...end:
    select ename, salary,
        case
            when salary > 1 and salary <= 1000then'LOWER'
            when salary > 1000 and salary <= 2000then'MIDDLE'
            when salary > 2000 and salary <= 4000then'HIGH'
            else'HIGHEST'
        end
    from emp;

3、between...and:
    select * from emp where sal between 1000 and 2000;    #####左闭右闭

4、group by:
    select id ,name,avg(sal) from emp group by id ,name having sal>1000;
    #####出现在select后的字段要么出现在group by后面，要么是在聚合函数内
    #####group by中做过滤只能用having

5、Export 和Import

1、应用场景
    EXPORT命令将表或分区的数据以及元数据导出到指定的输出位置。
    然后可以将此输出位置移动到不同的Hadoop或Hive实例。

2、导出数据
语法：
    EXPORT TABLE tablename [PARTITION (part_column="value"[, ...])]
        TO 'export_target_path' [ FOR replication('eventid') ]

示例：
    export table department to 'hdfs_exports_location/department';

3、导入数据
语法：
    IMPORT [[EXTERNAL] TABLE new_or_original_tablename 
        [PARTITION (part_column="value"[, ...])]]
        FROM 'source_path'
        [LOCATION 'import_target_path']

示例：
    import from 'hdfs_exports_location/department';

import/export更多用法参考官网。

DML官网参考。