Using bash to send hive script a variable number of fields

Question

I'm automating a data pipeline by using a bash script to move csvs to HDFS and build external Hive tables on them. Currently, this only works when the format of the table is predefined in an .hql file. But I want to be able to read the headers from the CSV and send them as arguments to Hive. So currently I do this inside a loop through the files:

# bash
hive -S -hiveconf VAR1=$target_db -hiveconf VAR2=$filename -hiveconf VAR3=$target_folder/$filename -f create_tables.hql

Which is sent to this...

-- hive
CREATE DATABASE IF NOT EXISTS ${hiveconf:VAR1};         
CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:VAR1}.${hiveconf:VAR2}(  
  individual_pkey INT,      
  response CHAR(1)        
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','   
LOCATION '/${hiveconf:VAR3}'

I want the hive script to look more like this...

CREATE DATABASE IF NOT EXISTS ${hiveconf:VAR1};         
CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:VAR1}.${hiveconf:VAR2}(  
  ${hiveconf:ROW1} ${hiveconf:TYPE1},
  ...              ...
  ${hiveconf:ROW_N} ${hiveconf:TYPE_N}

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','   
LOCATION '/${hiveconf:VAR3}'

Is it possible to send it some kind of array that it would parse? Is this feasible or advisable?

data princess · Accepted Answer · 2018-01-24 18:00:06Z

1

I eventually figured out a way around this.

You can't really write an HQL script that takes in a variable number of fields. You can, however, write a bash script that generates an HQL script of variable length. I've implemented this for my team, but the general idea is to write out how you want the HQL to look as a string in bash, then use something like Rscript to read in and identify the data types of your CSV. Store the data types as an array along with the CSV headers and then loop through those arrays, writing the information to the HQL.

answered Jan 24, 2018 at 18:00

data princess

1,1583 gold badges26 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using bash to send hive script a variable number of fields

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related