Hive DML语法

我会带着你远行 2024-04-18 16:51 70阅读 0赞

# DML语法 #

#### 1. 向Hive中加载数据 ####

几种常用的方法---------------------------------------------------------

###### 1.1 加载到普通表 ######

1.  可以将本地文本文件内容批量加载到Hive表中，要求文本文件中的格式和Hive表的定义一致，包括：字段个数、字段顺序、列分隔符都要一致。  
    这里的user\_info表的表定义是以\\t作为列分隔符，所以准备好数据后，将文本文件拷贝到hive客户端机器上后，执行加载命令。

load data local inpath '/home/hadoop/userinfodata.txt' overwrite into table user_info;

*  local关键字表示源数据文件在本地，源文件可以在HDFS上，如果在HDFS上，则去掉local，inpath后面的路径是类似”hdfs://namenode:9000/user/datapath”这样的HDFS上文件的路径。  
    overwrite关键字表示如果hive表中存在数据，就会覆盖掉原有的数据。如果省略overwrite，则默认是追加数据。  
    加载完成数据后，在HDFS上就会看到加载的数据文件。

1.  创建表的时候，使用location指定表所对应的目录即可。

create table if not exists db1.user_info2(
    id int comment "this is userid",
    name string
    )
    row format delimited fields terminated by ' '
    lines terminated by '\n'
    stored as textfile --存储格式
    location '/user/hive/warehouse/db1.db/user_info'  --只能是hdfs中的目录，直接为表加载数据
    ;

1.  直接将hdfs中数据使用命令上传到表所对应的目录即可。

hadoop fs -put userinfodata /user/hive/warehouse/db1.db/user_info2;

###### 1.2 加载到分区表 ######

load data local inpath '/home/hadoop/actionlog.txt' overwrite into table user_action_log 
    PARTITION (dt='2019-09-09');

在严格模式下必须指定分区，也可以直接put上去，没有问题  
partition 是指定这批数据放入分区2019-09-09中。

###### 1.3 加载到分桶表 ######

\------先创建普通临时表

create table user_leads_tmp
    (
    leads_id string,
    user_id string,
    user_id string,
    user_phone string,
    user_name string,
    create_time string
    )
    row format delimited fields terminated by ',' 
    stored as textfile;

\------数据载入临时表

load data local inpath '/home/hadoop/lead.txt' overwrite into table user_leads_tmp;

\------导入分桶表

set hive.enforce.bucketing = true;
    insert overwrite table user_leads select * from  user_leads_tmp;
    set hive.enforce.bucketing = true;

这个配置非常关键，为true就是设置为启用分桶。

###### 1.4 导出数据 ######

–导出数据，是将hive表中的数据导出到本地文件中。

insert overwrite local directory '/home/hadoop/user_info.bak2016-08-22 '
    select * from user_info;

去掉local关键字，也可以导出到HDFS上。

###### 1.5 插入数据 ######

insert select 语句

上一节分桶表数据导入，用到从user\_leads\_tmp表向user\_leads表中导入数据，用到了insert数据。

insert overwrite table user_leads select * from  user_leads_tmp;

这里是将查询结果导入到表中，overwrite关键字是覆盖目标表中的原来数据。如果缺省，就是追加数据。  
如果是插入数据的表是分区表，那么就如下所示：

insert overwrite table user_leads PARTITION (dt='2017-05-26') 
    select * from  user_leads_tmp;

**一次遍历多次插入**

from user_action_log
    insert overwrite table log1 select companyid,originalstring  where companyid='100006'
    insert overwrite table log2 select companyid,originalstring  where companyid='10002'

每次hive查询，都会将数据集整个遍历一遍。当查询结果会插入多个表中时，可以采用以上语法，将一次遍历写入多个表，以达到提高效率的目的。

###### 1.6 复制表 ######

复制表是将源表的结构和数据复制并创建为一个新表，复制过程中，可以对数据进行筛选，列可以进行删减。

create table user_leads_bak
        row format delimited fields terminated by '\t'
        stored as textfile
        as
        select leads_id,user_id,'2019-08-22' as bakdate
        from user_leads
        where create_time<'2019-08-22';

上面这个例子是对user\_leads表进行复制备份，复制时筛选了2019-08-22以前的数据，减少几个列，并添加了一个bakdate列。

###### 1.7 克隆表 ######

克隆表时会克隆源表的所有元数据信息，但是不会复制源表的数据。

–克隆表user\_leads，创建新表user\_leads\_like

create table user_leads_like like  user_leads;

###### 1.8 备份表 ######

备份是将表的元数据和数据都导出到HDFS上。

export table user_action_log partition (dt='2016-08-19')
    to '/user/hive/action_log.export'

这个例子是将user\_action\_log表中的一个分区，备份到HDFS上，to后面的路径是HDFS上的路径。

###### 1.9 还原表 ######

将备份在HDFS上的文件，还原到user\_action\_log\_like表中。

import table user_action_log_like from '/user/hive/action_log.export';