Hive自身数据导出的方式

来源：互联网发布：淘宝p图用什么软件编辑：程序博客网时间：2024/06/06 02:17

根据导出的位置不一样，将这些方式分为三种：
1、导出到本地文件系统；
2、导出到HDFS中；
3、导出到Hive的另一个表中。
一、导出到本地文件系统及HDFS中

INSERT OVERWRITE LOCAL DIRECTORY '/home/spark/opt/data_dir/base_food' row format delimited fields terminated by '|'  SELECT a.* FROM base_food a;hive> insert  overwrite local directory '/home/spark/opt/data_dir/base_dh_stock'    > select * from base_dh_stock;    Time taken: 16.704 seconds    数据量：37857    hive>     > insert  overwrite directory '/alidata/base_dh_stock'    > select * from base_dh_stock;    Time taken: 15.986 seconds    数据量：37857    hive> insert  overwrite directory '/alidata/base_dh_stock'    > row format delimited    > fields terminated by '$'    > select * from base_dh_stock;Time taken: 15.749 secondshive> insert  overwrite local directory '/home/spark/opt/data_dir/base_dh_stock'    > row format delimited    > fields terminated by '$'    > COLLECTION ITEMS TERMINATED BY ','    > MAP KEYS TERMINATED BY ':'    > select * from base_dh_stock;    Time taken: 16.605 seconds

和导入数据到Hive不一样，不能用insert into来将数据导出

导出到本地文件系统一种更加便捷迅速的方式

hive -e "select * from 59store.base_dh_stock;" >> /home/spark/opt/data_dir/base_dh_stock.txtTime taken: 1.729 seconds, Fetched: 37857 row(s)[spark@Master data_dir]$ hive -e "select * from 59store.base_dh_stock;" >> /home/spark/opt/data_dir/base_dh_stock.txtTime taken: 247.971 seconds, Fetched: 15363485 row(s)

二、导出到Hive的另一个表中
从一张Hive表导到另一张Hive表：

insert overwrite table base_order_partition partition (order_month=201501)select * from base_order_all where order_mo=201501;

分区表的数据导入：

from base_order_allinsert overwrite table base_order_partition partition (order_month=201501)select * where order_mo=201501insert overwrite table base_order_partition partition (order_month=201502)select * where order_mo=201502insert overwrite table base_order_partition partition (order_month=201503)select * where order_mo=201503insert overwrite table base_order_partition partition (order_month=201504)select * where order_mo=201504insert overwrite table base_order_partition partition (order_month=201505)select * where order_mo=201505insert overwrite table base_order_partition partition (order_month=201506)select * where order_mo=201506insert overwrite table base_order_partition partition (order_month=201507)select * where order_mo=201507insert overwrite table base_order_partition partition (order_month=201508)select * where order_mo=201508insert overwrite table base_order_partition partition (order_month=201509)select * where order_mo=201509insert overwrite table base_order_partition partition (order_month=201510)select * where order_mo=201510insert overwrite table base_order_partition partition (order_month=201511)select * where order_mo=201511;

0 0