Hive入门(三)：HQL数据操作——数据装载与保存

时间：2022-02-03 02:47:42

主要内容：
1.load装载数据；
2.insert查询插入数据：单个查询，多个查询，分区动态插入；
3.查询创建表并插入数据；
4.导出数据：直接拷贝，insert 导出。

装载数据

本地有数据文件products.dat，内容如下。

F-000212,Dali milk,2.0,food,China
F-002839,Ice cream,12.0,food,China
F-000233,Banana milk,5.0,food,China
E-001283,Water watch,399,electronics,China
E-230004,S007 Phone,1999,electronic,China

通过load转载数据

非分区表load导入数据

创建一张非分区表。

hive (mydb)> create table products(id string, name string, price int, type string, locality string)
           > row format delimited
           > fields terminated by ',';

然后通过load将products.dat导入表。
local表示数据在本地。如果数据保存在hdfs上，则无需local关键字。（可选）
overwrite表示覆盖重写。如果表目录下存在文件，则会被删除。如果不使用overwrite，则只会将新文件据添加至原目录下。（可选）
/path/products.dat数据文件的路径。通常这里的路径为一个目录，如/path，而不是指向具体的单个文件。如果这里是本地路径，那么数据被上传到到HDFS上来。而如果这是HDFS路径，则数据只是被转移到新的路径下。

hive (mydb)> load data local inpath '/path/products.dat'
           > overwrite into table products;

分区表load导入数据

观察products.dat数据，如果经常需要查询来自某个国家的某种类型的产品，则可以将后面的两个字段（type，locality）作为分区字段。创建一张分区表part_products如下。

hive (mydb)> create table part_products(id string, name string, price int)
           > partitioned by (type string, locality string)
           > row format delimited
           > fields terminated by ',';

倘若现在要向（type=food,locality=China）的分区加载数据。我们有一份products-china-food.dat文件，符合分区字段的要求。

F-000212,Dali milk,2.0,food,China
F-002839,Ice cream,12.0,food,China
F-000233,Banana milk,5.0,food,China

通过load data加载products-china-food.dat到part_products表的分区目录下。

hive (mydb)> load data local inpath '/path/products-china-food.dat'
           > overwrite into table part_products
           > partition (locality='China',type='food');

通过insert查询插入数据

可以通过查询其他表中符合条件的数据，将查询结果插入目的表中。

单个查询插入

如下，将products表中来自中国的电子产品数据插入part_products表的(type=’electronics’, locality=’China’)分区中。
注意，这里使用的是insert into，写新数据前，不会删除旧的数据，即使新数据与旧数据相同，也不会对旧数据产生任何影响。into如果替换成overwrite，则会删掉掉旧数据，再写入新数据。

hive> insert into table part_products
    > partition (type='electronics', locality='China')
    > select p.id, p.name, p.price from products p where p.locality='China' and p.type='electronics'; 
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 = stack_20180522152637_2c78c33b-c29c-4cdf-a19c-e6e25727b1f1
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)
2018-05-22 15:26:43,366 Stage-1 map = 100%,  reduce = 0%
Ended Job = job_local671673519_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://master:9000/user/hive/warehouse/mydb.db/part_products/type=electronics/locality=China/.hive-staging_hive_2018-05-22_15-26-37_705_3105165785025736504-1/-ext-10000
Loading data to table mydb.part_products partition (type=electronics, locality=China)
MapReduce Jobs Launched: 
Stage-Stage-1:  HDFS Read: 192 HDFS Write: 156 SUCCESS
Total MapReduce CPU Time Spent: 0 msec
OK
Time taken: 6.218 seconds

插入数据的操作由一个本地Hadoop MR任务执行。执行完成后，查看分区表part_products，发现数据已经成功插入。

hive> select * from part_products;
OK
E-001283    Water watch 399 electronics China
E-230004    S007 Phone  1999    electronics China
F-000212    Dali milk   2   food    China
F-002839    Ice cream   12  food    China
E-000233    Banana milk 5   food    China

最初执行插入语句时，出现了错误。后来在hive_env.sh中注释掉了有关jar包的配置即可成功执行。

Job Submission failed with exception 'java.io.FileNotFoundException(File does not exist: hdfs://master:9000/home/stack/spark-2.0.0-bin-2.6.0/jars/activation-1.1.1.jar)'
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask. File does not exist: hdfs://master:9000/home/stack/spark-2.0.0-bin-2.6.0/jars/activation-1.1.1.jar

多个查询插入

如果使用上述语句插入数据，则对每一个分区，都要扫描一遍原表products，效率非常低。因此，提供了另一种写法。扫描一遍products，即可将相应的数据插入不同分区。

hive> from products p
    > insert overwrite table part_products
    >   partition (type='electronics', locality='China')
    >   select p.id, p.name, p.price  where p.locality='China' and p.type='electronics'
    > insert overwrite table part_products
    >   partition (type='food', locality='China')
    >   select p.id, p.name, p.price  where p.locality='China' and p.type='food';

动态分区插入

如果每次插入分区数据都要手动设定分区类型，操作实在非常麻烦。动态分区可以解决这个问题。为了方便演示，首先创建一张分区表part_id_price_prodcts。该分区表只包含2个普通字段和一个分区字段。

hive> create table part_id_price_prodcts (id string, price string)
    > partitioned by (type string);

然后，查询products表中的数据，插入part_id_price_prodcts分区表中。查询时，仅选择了三个字段。Hive根据最后的字段p.type来确定分区字段type的值。

hive> insert into table part_id_price_products
    > partition (type)
    > select p.id,p.price,p.type from products p;

执行完成后，查看分区表的分区情况。可见两个分区已经被创建成功。

hive> show partitions part_id_price_products;
OK
type=__HIVE_DEFAULT_PARTITION__
type=electronics
type=food

注意：在动态插入分区前，需设置如下参数。

hive> set hive.exec.dynamic.partition=true;
hive> set hive.exec.dynamic.partition.mode=nonstrict;

可通过set 参数名的形式查看参数是否正确。

hive> set hive.exec.dynamic.partition;
hive.exec.dynamic.partition=true

单个查询创建表并插入数据

查询已有的内部表中数据，将这些数据保存为一张新表。比如如下，查询所有的来自中国的产品保存成一张新表。因为这张表全是中国的产品，因此选择字段时不再包含locality。

hive> create table china_products
    > as select id, name, price, type
    > from products
    > where locality='China';

该语句执行完成后，创建了新表china_products，并且包含数据。可以通过select * from china_products产看表中数据。
注意，这种方式只适用于内部表，不适用于外部表。

导出数据

直接拷贝数据文件

将表中数据导出。其实Hive表的数据就保存在表目录下。如果保存格式便是所需格式，则直接将表目录下文件下载到指定目录即可。

hive> dfs -get /user/hive/warehouse/mydb.db/china_products/* /path/products_data/

在/path/products_data目录下，有一个000000_0文件，文件个数与MR任务并行度一致。打开该文件，内容如下：

F-000212^ADali milk^A2^Afood F-002839^AIce cream^A12^Afood F-000233^ABanana milk^A5^Afood E-001283^AWater watch^A399^Aelectronics E-230004^AS007 Phone^A1999^Aelectronics

因为没有设定文件保存格式，按默认模仿保存。字段间以^A分隔。

单个查询导出数据

通过单个查询，将查询结果导出到指定目录。
如下，查询china_products表中food类型的商品，将查询结果写入本地目录下。如果无local关键字，则指定路径为hdfs路径。

hive> insert overwrite local directory '../testdata/china_food_products'
    > select id,name,price
    > from china_products cp
    > where cp.type='food';

多个查询导出数据

在一次扫描表时，设定多种查询条件，将各自符合的查询结果导出到不同目录下。比如，将china_products表中数据按type的类型，分别保存到不同目录下。

hive> from china_products cp
    > insert overwrite local directory '../testdata/china_food_products'
    > select id,name,price where type='food'
    > insert overwrite local directory '../testdata/china_electronics_products'
    > select id,name,price where type='electronics';
标签：

相关文章