HiveQL： 数据操作

hive (default)> load data local inpath "/home/hadoop/workspace/student.txt"
              > overwrite into table student1;

hadoop@dblab-VirtualBox:~/workspace$ cat stu.txt
1	michael	male	china
2	ming	male	china1
3	haha	female	china
4	huahua	female	china1

hive (default)> create table stu(
              > id int,
              > name string,
              > sex string,
              > country string)
              > row format delimited fields terminated by '\t';

hive (default)> load data local inpath '/home/hadoop/workspace/stu.txt'
              > into table stu;

hive (default)> create table employee(
              > name string,
              > country string)
              > row format delimited fields terminated by '\t';

hive (default)> from stu s
              > insert overwrite table employee
              > select s.name, s.country where s.id%2=1;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = hadoop_20210408224138_1df23614-7945-40c0-9a4d-df88e4f58ea1
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Job running in-process (local Hadoop)
2021-04-08 22:41:40,081 Stage-1 map = 100%,  reduce = 0%
Ended Job = job_local1437521177_0001
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to directory hdfs://localhost:9000/user/hive/warehouse/employee/.hive-staging_hive_2021-04-08_22-41-38_345_1863326332876590299-1/-ext-10000
Loading data to table default.employee
MapReduce Jobs Launched: 
Stage-Stage-1:  HDFS Read: 83 HDFS Write: 180 SUCCESS
Total MapReduce CPU Time Spent: 0 msec

hive (default)> select * from employee;
OK
michael	china
haha	china

hive (default)> from stu s
              > insert into table employee
              > select s.name, s.country where s.sex='female'
              > insert into table employee1
              > select s.name, s.country where s.sex='male';
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = hadoop_20210408230623_bc69bccf-348e-467d-b88e-498664f27017
Total jobs = 5
Launching Job 1 out of 5
Number of reduce tasks is set to 0 since there's no reduce operator
Job running in-process (local Hadoop)
2021-04-08 23:06:24,405 Stage-2 map = 100%,  reduce = 0%
Ended Job = job_local2065691620_0003
Stage-5 is selected by condition resolver.
Stage-4 is filtered out by condition resolver.
Stage-6 is filtered out by condition resolver.
Stage-11 is selected by condition resolver.
Stage-10 is filtered out by condition resolver.
Stage-12 is filtered out by condition resolver.
Moving data to directory hdfs://localhost:9000/user/hive/warehouse/employee/.hive-staging_hive_2021-04-08_23-06-23_001_7974131043339100692-1/-ext-10000
Moving data to directory hdfs://localhost:9000/user/hive/warehouse/employee1/.hive-staging_hive_2021-04-08_23-06-23_001_7974131043339100692-1/-ext-10002
Loading data to table default.employee
Loading data to table default.employee1
MapReduce Jobs Launched: 
Stage-Stage-2:  HDFS Read: 470 HDFS Write: 474 SUCCESS
Total MapReduce CPU Time Spent: 0 msec

hive (default)> select * from employee;
ming	china1
huahua	china1
haha	china
huahua	china1

hive (default)> select * from employee1;
michael	china
ming	china1

hive (default)> from stu s
              > insert overwrite table employee2
              > partition (country, sex)
              > select s.id, s.name, s.country, s.sex;

hive (default)> select * from employee2;
OK
3	haha	china	female
1	michael	china	male
4	huahua	china1	female
2	ming	china1	male

hive (default)> create table employee3
              > as select id, name from stu
              > where country='china';

hive (default)> select * from employee3;
1	michael
3	haha

hive (default)> from stu s
              > insert overwrite local directory '/tmp/employee'
              > select s.id, s.name, s.sex
              > where country='china';

hive (default)> ! ls /tmp/employee -r;
000000_0

hive (default)> ! cat /tmp/employee/000000_0;
1michaelmale
3hahafemale