question Re: EXPORT HIVE Database in Archives of Support Questions (Read Only)

EXPORT HIVE Database

rbalam — Thu, 10 Dec 2015 00:07:44 GMT

I see the following option to export one table at a time but do we have a way to do this for all the tables on a particular hive db?

EXPORT TABLE tablename [PARTITION (part_column="value"[, ...])] TO 'export_target_path'

Re: EXPORT HIVE Database

nsabharwal — Thu, 10 Dec 2015 00:13:27 GMT

@rbalam

I don't think there is an option to export database.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+ImportExport#LanguageManualImportExport-ExportSyntax

Re: EXPORT HIVE Database

rbalam — Thu, 10 Dec 2015 00:27:41 GMT

@Neeraj Sabharwal

Thanks for your quick response on this but do we have other options if we need to export the whole hive db to another cluster?

Re: EXPORT HIVE Database

bwilson — Thu, 10 Dec 2015 00:30:48 GMT

I am unaware of an option to export a whole database but you could implement something quickly via a bash script. The rough pseudo-code would be:

#!/bin/bash
tables = hive -e "use database_name;show tables;"
for table in tables
do
    hive -e "EXPORT $table ..."
done

Re: EXPORT HIVE Database

nsabharwal — Thu, 10 Dec 2015 00:31:53 GMT

@rbalam please see this https://community.hortonworks.com/questions/4496/how-to-migrate-hive-data-over-to-new-cluster.html

This can be a productive workaround. You can get list of tables and then using vi or any editor create a script to export all tables as seperate command.

mysql -u hive -p -e " select concat( 'show create table ' , TBL_NAME,';') from TBLS" hive > file.sql
remove header in file.sql
hive -f /tmp/file.sql

Re: EXPORT HIVE Database

bwilson — Thu, 10 Dec 2015 00:32:09 GMT

Falcon can be used to mirror/replicate Hive tables to another cluster:

http://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.3.2/bk_data_governance/content/section_mirroring_data_falcon.html

Re: EXPORT HIVE Database

abajwa — Thu, 10 Dec 2015 01:07:26 GMT

If you are just looking for the data, another option could be to use the Hive view from Ambari dashboard: it has an export option.

Re: EXPORT HIVE Database

francois__tizie — Thu, 21 Sep 2017 17:17:36 GMT

Hi all,

Thank you Brandon for posting your idea.

I have used the framework and built an KSH script to export successfully my Hive databases.

I share herewith the content of what I have conceived, just have to replace the key words and use the right user to execute and backup your databases:

#!/bin/ksh
# Author: Francois Tizie
# 2017/09/19
# This program exports all tables from the given Hive database
##############################################################
EXPORT_DIR=/path-to-hdfs/project_name/HIVE_DB_DUMP/
HiveTables=$(hive -e "use your_database_name;show tables;" 2>/dev/null | egrep -v "WARN|^$|^Logging|^OK|^Time\ taken")
hdfs dfs -mkdir -p /path-to-hdfs/project_name/HIVE_DB_DUMP/ 2>/dev/null
for Table in $HiveTables
do
    hive -e "EXPORT TABLE your_database_name.$Table TO '/path-to-hdfs/project_name/HIVE_DB_DUMP/your_database_name.$Table';"
done

Re: EXPORT HIVE Database

stellium — Thu, 21 Sep 2017 18:27:14 GMT

A learning I had was that when you export a table, use complete HDFS URI... In some cases, I found that it helped executing the command which otherwise was failed