Create hive external table using shell script

Technical Discussions Hive
#1

Hi,

I’m trying to create a shell script to create hive external table for which all my table_names are in table.txt file and the locations are in paths.txt file.

How can I loop over to pass the table name and location values as parameters for create table command from the table.txt and paths.txt files

Here schema for all the tables is same to me.

Thanks
Jayanthi

1 Like
#2

Hi @jayanthireddy

Could you please provide one example how you wanted to try out

Do you wanted to create the external tables with the columns and then place the Location for multiple tables in a single go.

I’m just giving you an hint,please check if the below approach could help you

#Create a sh script

hive_execute.sh
#!/bin/sh

PROP_FILE=/home/username/run.properties
db_name=$(grep -i ‘dbName’ $PROP_FILE | cut -f2 -d’=’)
table_name=$(grep -i ‘tblName’ $PROP_FILE | cut -f2 -d’=’)
echo "connecting to database - " $db_name
echo "using table - " $table_name
hive -hiveconf DB_NAME=$db_name -hiveconf TABLE_NAME=$table_name -f /home/username/create.hql

run.properties file will have your dbname,tablename,hive conf settings

create.hql will have your create statement with the parameter

sample query below

—Creating the table with required columns
DROP TABLE IF EXISTS ${hiveconf:DB_NAME}.${hiveconf:TABLE_NAME} PURGE;
CREATE TABLE IF NOT EXISTS ${hiveconf:DB_NAME}.${hiveconf:TABLE_NAME}
(Text STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
LOCATION ‘/hdfspath/username/${hiveconf:TABLE_NAME}/’
;

let me know if this helps you :slight_smile:

Thanks
Shanmukh

2 Likes