insert 研究
create table if not exists vn09jj5.test0_order ( store_id int , channel string , gmv decimal(6,2) , qty int ) row format delimited fields terminated by ',' lines terminated by '\n' stored as orc ;
hdfs dfs -ls /user/hive/userdbs/vn09jj5.db/test0_order/
hdfs dfs -ls /user/hive/userdbs/vn09jj5.db/test0_order/*
hdfs dfs -du -h /user/hive/userdbs/vn09jj5.db/test0_order/
hdfs dfs -du -h /user/hive/userdbs/vn09jj5.db/test0_order/*
insert into vn09jj5.test0_order values (1094,'SNG' ,10.00,1); insert into vn09jj5.test0_order values (1094,'WMDJ',20.00,2); insert into vn09jj5.test0_order values (1094,'JDDJ',30.00,3);
插入数据
insert into vn09jj5.test0_order values (1094,'SNG' ,10.00,1);
select store_id,channel,gmv,qty from vn09jj5.test0_order ; +-----------+----------+--------+------+ | store_id | channel | gmv | qty | +-----------+----------+--------+------+ | 1094 | SNG | 10.00 | 1 | +-----------+----------+--------+------+
hdfs dfs -ls /user/hive/userdbs/vn09jj5.db/test0_order/ Found 1 items drwxr-x--- - hive hadoop 0 2020-05-20 02:07 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000001_0000001_0000
hdfs dfs -ls /user/hive/userdbs/vn09jj5.db/test0_order/* Found 1 items -rw-r----- 3 hive hadoop 489 2020-05-20 02:07 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000001_0000001_0000/000000_0
hdfs dfs -du -h /user/hive/userdbs/vn09jj5.db/test0_order/
489 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000001_0000001_0000
hdfs dfs -du -h /user/hive/userdbs/vn09jj5.db/test0_order/*
489 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000001_0000001_0000/000000_0
插入第二条数据
insert into vn09jj5.test0_order values (1094,'WMDJ',20.00,2);
select store_id,channel,gmv,qty from vn09jj5.test0_order ; +-----------+----------+--------+------+ | store_id | channel | gmv | qty | +-----------+----------+--------+------+ | 1094 | SNG | 10.00 | 1 | | 1094 | WMDJ | 20.00 | 2 | +-----------+----------+--------+------+
hdfs dfs -ls /user/hive/userdbs/vn09jj5.db/test0_order/ Found 2 items drwxr-x--- - hive hadoop 0 2020-05-20 02:07 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000001_0000001_0000 drwxr-x--- - hive hadoop 0 2020-05-20 02:14 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000002_0000002_0000
hdfs dfs -ls /user/hive/userdbs/vn09jj5.db/test0_order/* Found 1 items -rw-r----- 3 hive hadoop 489 2020-05-20 02:07 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000001_0000001_0000/000000_0 Found 1 items -rw-r----- 3 hive hadoop 493 2020-05-20 02:14 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000002_0000002_0000/000000_0
hdfs dfs -du -h /user/hive/userdbs/vn09jj5.db/test0_order/ 489 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000001_0000001_0000 493 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000002_0000002_0000
hdfs dfs -du -h /user/hive/userdbs/vn09jj5.db/test0_order/* 489 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000001_0000001_0000/000000_0 493 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000002_0000002_0000/000000_0
插入第三条数据
insert into vn09jj5.test0_order values (1094,'JDDJ',30.00,3);
select store_id,channel,gmv,qty from vn09jj5.test0_order ; +-----------+----------+--------+------+ | store_id | channel | gmv | qty | +-----------+----------+--------+------+ | 1094 | JDDJ | 30.00 | 3 | | 1094 | WMDJ | 20.00 | 2 | | 1094 | SNG | 10.00 | 1 | +-----------+----------+--------+------+
hdfs dfs -ls /user/hive/userdbs/vn09jj5.db/test0_order/ Found 3 items drwxr-x--- - hive hadoop 0 2020-05-20 02:07 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000001_0000001_0000 drwxr-x--- - hive hadoop 0 2020-05-20 02:14 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000002_0000002_0000 drwxr-x--- - hive hadoop 0 2020-05-20 02:20 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000003_0000003_0000
hdfs dfs -ls /user/hive/userdbs/vn09jj5.db/test0_order/* Found 1 items -rw-r----- 3 hive hadoop 489 2020-05-20 02:07 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000001_0000001_0000/000000_0 Found 1 items -rw-r----- 3 hive hadoop 493 2020-05-20 02:14 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000002_0000002_0000/000000_0 Found 1 items -rw-r----- 3 hive hadoop 493 2020-05-20 02:20 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000003_0000003_0000/000000_0
hdfs dfs -du -h /user/hive/userdbs/vn09jj5.db/test0_order/ 489 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000001_0000001_0000 493 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000002_0000002_0000 493 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000003_0000003_0000
hdfs dfs -du -h /user/hive/userdbs/vn09jj5.db/test0_order/* 489 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000001_0000001_0000/000000_0 493 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000002_0000002_0000/000000_0 493 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000003_0000003_0000/000000_0
清空表
truncate table vn09jj5.test0_order ;
select store_id,channel,gmv,qty from vn09jj5.test0_order ; +-----------+----------+------+------+ | store_id | channel | gmv | qty | +-----------+----------+------+------+ +-----------+----------+------+------+
hdfs dfs -ls /user/hive/userdbs/vn09jj5.db/test0_order/* ls: `/user/hive/userdbs/vn09jj5.db/test0_order/*': No such file or directory
hdfs dfs -du -h /user/hive/userdbs/vn09jj5.db/test0_order/* du: `/user/hive/userdbs/vn09jj5.db/test0_order/*': No such file or directory
一次性插入多条数据
insert into vn09jj5.test0_order values (1094,'SNG' ,20.00,2), (1094,'WMDJ',40.00,4), (1094,'JDDJ',60.00,6);
select store_id,channel,gmv,qty from vn09jj5.test0_order ; +-----------+----------+--------+------+ | store_id | channel | gmv | qty | +-----------+----------+--------+------+ | 1094 | SNG | 20.00 | 2 | | 1094 | WMDJ | 40.00 | 4 | | 1094 | JDDJ | 60.00 | 6 | +-----------+----------+--------+------+
hdfs dfs -ls /user/hive/userdbs/vn09jj5.db/test0_order/* Found 1 items -rw-r----- 3 hive hadoop 523 2020-05-20 02:40 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000005_0000005_0000/000000_0
hdfs dfs -du -h /user/hive/userdbs/vn09jj5.db/test0_order/*
523 1.5 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000005_0000005_0000/000000_0
truncate table vn09jj5.test0_order ;
插入数据再覆盖数据
添加一条数据
insert into vn09jj5.test0_order values (1094,'SNG' ,10.00,1);
select store_id,channel,gmv,qty from vn09jj5.test0_order ; +-----------+----------+--------+------+ | store_id | channel | gmv | qty | +-----------+----------+--------+------+ | 1094 | SNG | 10.00 | 1 | +-----------+----------+--------+------+
hdfs dfs -ls /user/hive/userdbs/vn09jj5.db/test0_order/* Found 1 items -rw-r----- 3 hive hadoop 489 2020-05-20 02:54 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000007_0000007_0000/000000_0
hdfs dfs -du -h /user/hive/userdbs/vn09jj5.db/test0_order/*
489 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000007_0000007_0000/000000_0
insert into vn09jj5.test0_order values (1094,'WMDJ',20.00,2);
select store_id,channel,gmv,qty from vn09jj5.test0_order ; +-----------+----------+--------+------+ | store_id | channel | gmv | qty | +-----------+----------+--------+------+ | 1094 | SNG | 10.00 | 1 | | 1094 | WMDJ | 20.00 | 2 | +-----------+----------+--------+------+
hdfs dfs -ls /user/hive/userdbs/vn09jj5.db/test0_order/* Found 1 items -rw-r----- 3 hive hadoop 489 2020-05-20 02:54 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000007_0000007_0000/000000_0 Found 1 items -rw-r----- 3 hive hadoop 493 2020-05-20 02:56 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000008_0000008_0000/000000_0
hdfs dfs -du -h /user/hive/userdbs/vn09jj5.db/test0_order/* 489 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000007_0000007_0000/000000_0 493 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000008_0000008_0000/000000_0
insert into vn09jj5.test0_order values (1094,'JDDJ',30.00,3);
select store_id,channel,gmv,qty from vn09jj5.test0_order ; +-----------+----------+--------+------+ | store_id | channel | gmv | qty | +-----------+----------+--------+------+ | 1094 | JDDJ | 30.00 | 3 | | 1094 | WMDJ | 20.00 | 2 | | 1094 | SNG | 10.00 | 1 | +-----------+----------+--------+------+
hdfs dfs -ls /user/hive/userdbs/vn09jj5.db/test0_order/* Found 1 items -rw-r----- 3 hive hadoop 489 2020-05-20 02:54 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000007_0000007_0000/000000_0 Found 1 items -rw-r----- 3 hive hadoop 493 2020-05-20 02:56 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000008_0000008_0000/000000_0 Found 1 items -rw-r----- 3 hive hadoop 493 2020-05-20 02:57 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000009_0000009_0000/000000_0
hdfs dfs -du -h /user/hive/userdbs/vn09jj5.db/test0_order/* 489 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000007_0000007_0000/000000_0 493 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000008_0000008_0000/000000_0 493 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000009_0000009_0000/000000_0
覆盖数据
insert overwrite table vn09jj5.test0_order values (1094,'WFS',100.00,10);
select store_id,channel,gmv,qty from vn09jj5.test0_order ; +-----------+----------+---------+------+ | store_id | channel | gmv | qty | +-----------+----------+---------+------+ | 1094 | WFS | 100.00 | 10 | +-----------+----------+---------+------+
hdfs dfs -ls /user/hive/userdbs/vn09jj5.db/test0_order/* Found 1 items -rw-r----- 3 hive hadoop 490 2020-05-20 03:00 /user/hive/userdbs/vn09jj5.db/test0_order/base_0000010/000000_0 Found 1 items -rw-r----- 3 hive hadoop 489 2020-05-20 02:54 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000007_0000007_0000/000000_0 Found 1 items -rw-r----- 3 hive hadoop 493 2020-05-20 02:56 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000008_0000008_0000/000000_0 Found 1 items -rw-r----- 3 hive hadoop 493 2020-05-20 02:57 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000009_0000009_0000/000000_0
hdfs dfs -du -h /user/hive/userdbs/vn09jj5.db/test0_order/* 490 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/base_0000010/000000_0 489 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000007_0000007_0000/000000_0 493 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000008_0000008_0000/000000_0 493 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000009_0000009_0000/000000_0
insert into table vn09jj5.test0_order values (1094,'OFFLINE' ,200.00,20); select store_id,channel,gmv,qty from vn09jj5.test0_order ;
select store_id,channel,gmv,qty from vn09jj5.test0_order ; +-----------+----------+---------+------+ | store_id | channel | gmv | qty | +-----------+----------+---------+------+ | 1094 | OFFLINE | 200.00 | 20 | | 1094 | WFS | 100.00 | 10 | +-----------+----------+---------+------+
hdfs dfs -du -h /user/hive/userdbs/vn09jj5.db/test0_order/*
490 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/base_0000010/000000_0 489 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000007_0000007_0000/000000_0 493 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000008_0000008_0000/000000_0 493 1.4 K /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000009_0000009_0000/000000_0
hdfs dfs -count /user/hive/userdbs/vn09jj5.db/test0_order/
6 5 2476 /user/hive/userdbs/vn09jj5.db/test0_order
hdfs dfs -count /user/hive/userdbs/vn09jj5.db/test0_order/* 1 1 490 /user/hive/userdbs/vn09jj5.db/test0_order/base_0000010 1 1 489 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000007_0000007_0000 1 1 493 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000008_0000008_0000 1 1 493 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000009_0000009_0000 1 1 511 /user/hive/userdbs/vn09jj5.db/test0_order/delta_0000011_0000011_0000
浙公网安备 33010602011771号