EXPORT HIVE Database

[#1]

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'

[#2 · Accepted solution]

@rbalam

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

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+ImportExport#LanguageManualImportExp...

[#3]

@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?

[#4]

@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.

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

[#5]

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...

[#6]

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

[#7]

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.

[#8]

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

[#9]

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