Hive table creation and Data Import and Export

Table creation:

Create external table tabtext (imsi string,
MDN string,
Meid string,
Nai string,
Destinationip string,
Destinationport string,
Sourceip string,
Sourceport string,
Pdsnip string,
Pcfip string,
Haip string,
Userzoneid string,
BSID string,
Subnet string,
Serviceoption string,
ProtocolId string,
Servicetype string,
Starttime string,
Endtime string,
Duration string,
Inputoctets string,
Outputoctets string,
Inputpacket string,
Outputpacket string,
Sessionid string,
Recordclosecause string,
Useragent string,
Destinationurl string,
Domainname string,
Host string,
Contentlen string,
Contenttype string,
Iflink string,
Refer string,
Httpaction string,
Httpstatus string,
Respdelay string,
Behaviortarget string)
Row format delimited
Fields terminated by '| ';

Load data inpath'/user/vendorultrapower/CK/car.txt 'into Table tabtext;
Load data inpath'/user/vendorultrapower/CK/car.txt 'into Table tabtext;

Set mapreduce. Job. queuename = root. Vendor. ven3;

Create external table unmatch (URL string );

Load data local inpath '/home/vendorultrapower/CK/notnatch.txt' into Table unmatch;

 

 

Data import and export:

 

1.
Several common data import methods of hive
Here we will introduce four types:
(1) import data from the local file system to the hive table;
(2) import data from HDFS to hive tables;
(3) query the corresponding data from other tables and import the data to the hive table;
(4) When creating a table, query corresponding records from other tables and insert them to the created table.

1. import data from the local file system to the hive table
1.1
[[Email protected] hive-0.9.0-bin] $ bin/hive
Create an ha table
Hive> Create Table ha (ID int, name string)
> Row format delimited
> Fields terminated by '\ t'
> Stored as textfile;

The [row format delimited] keyword is used to set the column delimiter supported by the created table when loading data.
The [stored as file_format] keyword is used to set the data type for loading data. The default value is textfile. If the file data is plain text, [stored as textfile] is used. then, the data is directly copied from the local machine to HDFS, and hive can directly identify the data.

1.2
Text in the operating system
[[Email protected] ~] $ Cat haha.txt
101 Zs
102 ls
103 ww

1.3 import data
Hive> load data local inpath '/home/hadoop/haha.txt' into table ha;
Hive> select * From ha;

*****
Unlike the relational database we are familiar with, hive does not support directly providing a set of records in the insert statement, that is, hive does not support insert .... Values statements.
*****

--------------------------------------------------
2.
Import data from HDFS to hive tables;

2.1
[[Email protected] hadoop-0.20.2-cdh3u5] $ bin/hadoop FS-mkdir ABC

[[Email protected] ~] $ Cat hehe.txt
1001 AA
1002 bb
1003 CC

[[Email protected] hadoop-0.20.2-cdh3u5] $ bin/hadoop FS-Put/home/hadoop/hehe.txt ABC /.
(Upload to HDFS)

2.2
Hive> Create Table he (ID int, name string)
> Row format delimited
> Fields terminated by '\ t'
> Stored as textfile;

Import
Hive> load data inpath'/user/hadoop/ABC/hehe.txt 'into table he;

---------------------------------------------------------
3. query the corresponding data from another table and import it to the hive table.

3.1
Hive> select * from he;
OK
1001 AA
1002 bb
1003 CC

Hive> Create Table Heihei (ID int, name string)
> Row format delimited
> Fields terminated by '\ t'
> Stored as textfile;

3.2
Hive> insert into Table Heihei select * from he;

Or
Hive> insert overwrite table Heihei select * From ha;
(Insert overwrite overwrites data)

--------------------------------------------------
4. When creating a table, you can query the corresponding records from other tables and insert them to the created table.
Hive> Create Table Gaga as select * from he;

 

========================================================== ======================================
Export data
(1) export to the local file system;
(2) Export to HDFS;
(3) export to another hive table.

1. Export to the local file system;
Hive> insert overwrite local directory '/home/hadoop/he1' select * from he;

[[Email protected] ~] $ CD he1 (he1 is the directory with the file)
[[Email protected] he1] $ cat protected 00_0
(No column separation is found)

You can add partitions in the following way.
Hive> insert overwrite local directory '/home/hadoop/he1' select ID, Concat (' \ t', name) from he;

******
Unlike importing data to hive, insert into cannot be used to export data.
******

---------------------------------------------------------
2. Export to HDFS.
Hive> insert overwrite directory '/user/hadoop/abc' select * from he;
(/User/hadoop/ABC is the directory under HDFS)

[[Email protected] hadoop-0.20.2-cdh3u5] $ bin/hadoop FS-ls ABC
[[Email protected] hadoop-0.20.2-cdh3u5] $ bin/hadoop FS-cat ABC/12700_0

-------------------------------------------------------------
3. export to another hive table
Hive> insert into Table he12 select * from he;

 

Hive table creation and Data Import and Export