hive 数据导入导出

Dear 丶 2023-10-11 12:29 13阅读 0赞

# 1，数据源端 #

*  hive表数据导出到本地目录 - - -> scp 本地目录到远程主机

hive> select * from t;
    OK
    t.id	t.name
    1	a
    2	a2
    3	b
    Time taken: 0.451 seconds, Fetched: 3 row(s)
    
    hive> insert overwrite local directory '/root/t.data' select * from t;
    Query ID = root_20190923154949_f55579fa-091e-47c5-b774-4dcf3d4b77dc
    Total jobs = 1
    Launching Job 1 out of 1
    Number of reduce tasks is set to 0 since there's no reduce operator
    Starting Job = job_1568012065562_0275, Tracking URL = http://test1:8998/proxy/application_1568012065562_0275/
    Kill Command = /opt/cloudera/parcels/CDH-5.12.0-1.cdh5.12.0.p0.29/lib/hadoop/bin/hadoop job  -kill job_1568012065562_0275
    Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
    2019-09-23 15:49:59,590 Stage-1 map = 0%,  reduce = 0%
    2019-09-23 15:50:04,875 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.15 sec
    MapReduce Total cumulative CPU time: 2 seconds 150 msec
    Ended Job = job_1568012065562_0275
    Copying data to local directory /root/t.data
    MapReduce Jobs Launched: 
    Stage-Stage-1: Map: 1   Cumulative CPU: 2.15 sec   HDFS Read: 2862 HDFS Write: 13 SUCCESS
    Total MapReduce CPU Time Spent: 2 seconds 150 msec
    OK
    t.id	t.name
    Time taken: 14.71 seconds
    
    hive> show create table t;
    OK
    createtab_stmt
    CREATE TABLE `t`(
      `id` int, 
      `name` string)
    
    [root@test1 ~]# scp -r t.data/ cdh1:/
    Warning: Permanently added 'cdh1,192.168.58.151' (RSA) to the list of known hosts.
    .000000_0.crc                                                                                               100%   12     0.0KB/s   00:00    
    000000_0                                                                                                    100%   13     0.0KB/s   00:00

# 2, 数据接收端 #

*  创建相同字段类型的hive表- - -> 从本地目录load数据到表中

hive > CREATE TABLE `t_local`(
        >   `id` int, 
        >   `name` string) ;
    OK
    Time taken: 0.706 seconds
    
    hive> load data local inpath '/t.data' into table t_local;
    Loading data to table default.t_local
    Table default.t_local stats: [numFiles=1, totalSize=13]
    OK
    Time taken: 0.885 seconds
    
    hive> select * from t_local;
    OK
    1	a
    2	a2
    3	b
    Time taken: 0.559 seconds, Fetched: 3 row(s)