current position:Home>Hive DML

Hive DML

2022-01-27 00:21:16 JOEL-T99

2. DML

DML(Data Manipulation Language): Database operation language

2.1 Data import

Prepare a table :

create table if not exists stu01
(
    id   int,
    name string
)
    row format delimited fields terminated by '\t';

Prepare a copy of the data students.txt

1001	student01
1002	student02
1003	student03
1004	student04
1005	student05
1006	student06
1007	student07
1008	student08
1009	student09

2.1.1 Loading data into a table (load)

Grammatical structure

load data [local] inpath 'data_path' [overwrite] into table <table_name> [partition (part_coll=val, ...)]; 

local: Indicates that data is loaded locally to Hive surface , Otherwise, from HDFS Load data to Hive surface

overwrite: Indicates that the existing data in the table is overwritten , Otherwise, it means additional

partition: Indicates that it is uploaded to the specified partition

Load server local files to Hive

load data local inpath '/root/students.txt' into table stu01;

image-20211216153054156

load HDFS File to Hive

dfs -put /root/students.txt /;  --  First upload the file to HDFS
load data inpath '/students.txt' into table stu01;

image-20211216153643597

Load data , And cover the original data

image-20211216154154642

2.1.2 Insert data into the table by query criteria (insert)

Grammatical structure

insert into/overwrite table <table_name> values(value1[, value2, ..]);
insert into/overwrite table <table_name> select ...;

Basic insert data

insert into stu01 values (10010, 'JOEL');

image-20211216155308626

Insert data according to the query results

insert overwrite table stu01 select * from students where id = 1001;

image-20211216155819134

2.1.3 Create table and load data in query statement (as select)

Grammatical structure

create table [if not exists] stu01 as select ...;

Create a table based on the query results

create table if not exists stu01 as select * from students where id = 1001;

image-20211216160308703

2.1.4 Passed while creating the table location Specify the loading data path

Grammatical structure

create external table if not exists <table_name> (
	col_name1 col_vaule1;
    col_name2 col_vaule2
)
	row format delimited [...]
	location <HDFS_path>

Specify... When creating a table HDFS Data path

dfs -put /root/students.txt /students;  --  Upload files to HDFS, Remove the file suffix 
create table if not exists stu01(
    id   int,
    name string
)
    row format delimited fields terminated by '\t'
    stored as textfile
    location '/students';

image-20211216161809158

2.2 Export data

2.2.1 insert export

Export the results of the query to local

insert overwrite local directory '/root/stu' select * from stu01;

image-20211216163812281

Format the results of the query to local

insert overwrite local directory '/root/stu'
    row format delimited fields terminated by '\t'
    stored as textfile select *from stu01;

image-20211216164551418

Export the results of the query to HDFS

insert overwrite directory '/stu' 
    row format delimited fields terminated by '\t' 
    stored as textfile select * from stu01;

image-20211216164219008

2.2.2 Hive Shell Command export

 echo `hive -e 'select * from hive_test01.stu01;'`

image-20211216165746042

2.2.3 export Export to HDFS

export table stu01 to '/stu';

image-20211216170340736

image-20211216170418790

image-20211216170520160

export and import It is mainly used for Two Hadoop Between platforms Hive Migration !

 


️ END ️

copyright notice
author[JOEL-T99],Please bring the original link to reprint, thank you.
https://en.cdmana.com/2022/01/202201270021142958.html

Random recommended