3.1 data import
3.1.1 loading data into the table (Load)
(1) Grammar
load data [local] inpath 'path of data' [overwrite] into table
student [partition (partcol1=val1,...)];
load data: indicates loading data
Local: indicates loading data from local to hive table; Otherwise, load data from HDFS to hive table
inpath: indicates the path to load data
Overwrite: it means to overwrite the existing data in the table, otherwise it means to append
into table: indicates which table to load
student: represents a specific table
Partition: indicates to upload to the specified partition
(2) Case
1) . create a table
create table student_xjl(id string,name string) row format delimited fields terminated by '\t';
2) . load local files into hive
load data local inpath '/opt/module/hive/datas/student.txt' into table default.student;
3) . load the HDFS file into hive
Upload files to HDFS
dfs -put /opt/module/hive/data/student.txt /user/atguigu/hive;
Load data on HDFS
load data inpath '/user/atguigu/hive/student.txt' into table default.student;
3) . load data to overwrite the existing data in the table
Upload files to HDFS
dfs -put /opt/module/data/student.txt /user/atguigu/hive;
Loading data overwrites the existing data in the table
load data inpath '/user/atguigu/hive/student.txt' overwrite into table default.student;
3.1.2 inserting data into a table through a query statement (Insert)
(1) Create a table
create table student_xjl(id int, name string) row format delimited fields terminated by '\t'
(2) Insert basic data
insert into table student_xjl values (1,'zhangsan'),(2,'lisi'),(3,'wangwu');
Insert into: insert into a table or partition by appending data. The original data will not be deleted
insert overwrite: it will overwrite the existing data in the table
Note: insert does not support inserting partial fields
3.1.3 create tables and load data in query statements (As Select)
Create a table based on the query results (the query results are added to the newly created table)
create table if not exists student1_xjl as select id,name from student_xjl;
3.1.4 specify the loading data path through Location when creating a table
(1) Upload data to hdfs
hive (default)> dfs -mkdir /student; hive (default)> dfs -put /opt/module/datas/student.txt /student;
(2) Create a table and specify the location on hdfs
create external table if not exists student5( id int, name string) row format delimited fields terminated by '\t' location '/student;
(3) Query data
hive (default)> select * from student5;
3.1.5 Import data into the specified Hive table
Note: export first, and then import the data.
import table student2 from '/user/hive/warehouse/export/student';
3.2 data export
3.2.1 Insert export
(1) Export query results to local
insert overwrite local directory '/opt/module/hive/data/export/student' select * from student;
(2) Format and export query results to local
insert overwrite local directory '/opt/module/hive/data/export/student1' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;
(3) Export the query results to HDFS (no local)
insert overwrite directory '/opt/module/hive/data/export/student1' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;
3.2.2 exporting Hadoop commands to local
3.2.3 Hive Shell command export
Basic syntax: (hive -f/-e execute statement or script > file)
3.2.4 Export to HDFS
export and import are mainly used for Hive table migration between two Hadoop platform clusters.
3.2.5 clear data in the table (Truncate)
Note: Truncate can only delete management tables, not data in external tables
truncate table student;