Hive Data Import

You can import data to hive tables in multiple ways.
1. Import from an external table
You can create an external table on hive, specify the hdfs path when creating the table, copy the data to the specified hdfs path, and insert the data into the external table at the same time.
For example:

Edit file test.txt
$ Cat test.txt
1 hello
2 world
3 test
4 case
Fields are separated by '\ t'

Start hive:
$ Hive

Create an external table:
Hive> create external table mytest (num INT, name STRING)
> COMMENT 'this is a Test'
> Row format delimited fields terminated by '\ t'
> STORED AS TEXTFILE
> LOCATION '/data/test ';
OK
Time taken: 0.714 seconds
Hive> show tables;
OK
Mytest
Partition_test
Partition_test_input
Test
Time taken: 0.07 seconds
Hive> desc mytest;
OK
Num int
Name string
Time taken: 0.121 seconds |

Copy Data to hdfs:
$ Hadoop fs-put test.txt/data/test

View hive table data:
Hive> select * from mytest;
OK
1 hello
2 world
3 test
4 case
Time taken: 0.375 seconds
Hive> select num from mytest;
Total MapReduce jobs = 1
Launching Job 1 out of 1
......
Total MapReduce CPU Time Spent: 510 msec
OK
1
2
3
4
Time taken: 27.157 seconds

This method is often used when hdfs has some historical data, and we need to perform some hive operations on the data. This method avoids data copy overhead.

2. import data locally
The data is not on hdfs, And the hive table is directly imported from the local machine.

File/home/work/test.txt is the same as above

Table creation:
Hive> create table MYTEST2 (num INT, name STRING)
> COMMENT 'this is a test2'
> Row format delimited fields terminated by '\ t'
> Stored as textfile;
OK
Time taken: 0.077 seconds

Import data to the table:
Hive> load data local inpath '/home/work/test.txt' into table MYTEST2;
Copying data from file:/home/work/test.txt
Copying file:/home/work/test.txt
Loading data to table default. mytest2
OK
Time taken: 0.24 seconds

View data:
Hive> select * from MYTEST2;
OK
1 hello
2 world
3 test
4 case
Time taken: 0.11 seconds
The local data imported in this way can be a file, a folder or a wildcard. Note that, for a folder, the folder cannot contain subdirectories. Likewise, wildcards can only be used with wildcard files.

3. Import from hdfs
The test.txt file has been imported to/data/test.
You can use the following command to directly import data to the hive table:
Hive> create table MYTEST3 (num INT, name STRING)
> COMMENT "this is a test3"
> Row format delimited fields terminated by '\ t'
> Stored as textfile;
OK
Time taken: 4.735 seconds
Hive> load data inpath'/data/test/test.txt 'into table MYTEST3;
Loading data to table default. mytest3
OK
Time taken: 0.337 seconds
Hive> select * from MYTEST3;
OK
1 hello
2 world
3 test
4 case
Time taken: 0.227 seconds

4. import data from other tables:
Hive> create external table MYTEST4 (num INT );
OK
Time taken: 0.091 seconds
Hive> FROM MYTEST3 test3
> Insert overwrite table MYTEST4
> Select test3.num where name = 'World ';
Total MapReduce jobs = 2
Launching Job 1 out of 2
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201207230024_0002, Tracking URL = http: // localhost: 50030/jobdetails. jsp? Jobid = job_201207230024_0002
Kill Command =/home/work/hadoop/hadoop-1.0.3/libexec/../bin/hadoop job-Dmapred. job. tracker = localhost: 9001-kill job_201207230024_0002
Hadoop job information for Stage-1: number of mappers: 1; number of concurrent CERs: 0
18:59:02, 365 Stage-1 map = 0%, reduce = 0%
18:59:08, 417 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.62 sec
18:59:09, 435 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.62 sec
18:59:10, 445 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.62 sec
18:59:11, 455 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.62 sec
18:59:12, 470 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.62 sec
18:59:13, 489 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.62 sec
18:59:14, 508 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 0.62 sec
MapReduce Total cumulative CPU time: 620 msec
Ended Job = job_201207230024_0002
Ended Job =-174856900, job is filtered out (removed at runtime ).
Moving data to: hdfs: // localhost: 9000/tmp/hive-work/hive_2012-07-23_18-58-44_166_189728317691010041/-ext-10000
Loading data to table default. mytest4
Deleted hdfs: // localhost: 9000/user/hive/warehouse/mytest4
Table default. mytest4 stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 2, raw_data_size: 0]
1 Rows loaded to mytest4
MapReduce Jobs Launched:
Job 0: Map: 1 Accumulative CPU: 0.62 sec HDFS Read: 242 HDFS Write: 2 SUCESS
Total MapReduce CPU Time Spent: 620 msec
OK
Time taken: 30.663 seconds
Hive> select * from mytest4;
OK
2
Time taken: 0.103 seconds
Author: yfkiss