Create table in HIVE using column names from file

Create table in HIVE using column names from file

Questions : Create table in HIVE using column names from file


I currently have some HIVE code that creates in4codes_csv a table where I declare all the column names in4codes_csv and type. Then I use “load data inpath” to in4codes_csv load a CSV into that table so I can then in4codes_csv join to my database tables. The problem in4codes_csv lies in that sometimes the CSV columns maybe in4codes_csv in a different order. I can not control in4codes_csv that as it is sent to me from a different in4codes_csv source. I am wondering if there is a way to in4codes_csv create the temp table I do daily without in4codes_csv declaring the column names and just allow it in4codes_csv to read from the CSV. This would allow me in4codes_csv to not have to manually review the file in4codes_csv every morning to check the columns are in in4codes_csv the right order?

Total Answers 1

Answers 1 : of Create table in HIVE using column names from file

Because the column order keeps changing, in4codes_hive as a first step you could use a shell in4codes_hive script to read the header columns and in4codes_hive generate a create table script for the in4codes_hive temp table. Thereafter,execute the in4codes_hive generated create table string and load in4codes_hive the file into the temp table. From temp in4codes_hive table, you can then load it to the in4codes_hive target table.

A sample bash script to give you an in4codes_hive idea.

#!/bin/bash #File check if [ -s "$1" ] then echo "File $1 exists and is non-empty" else echo "File $1 does not exist or is empty" fi create_tbl_cmd="create table tblname (" #Get fields from file header fields=`head -1 $1` #add string as the datatype for each field fields="${fields//,/ string,} string)" create_table_cmd="$create_tbl_cmd$fields" #echo $create_table_cmd #Execute the $create_table_cmd using Beeline or Hive command line invoking the necessary command #Execute the Load into temp table #Execute the Insert from temp table to target table  

Execute the bash script above with the in4codes_hive csv file argument as

bash filename.csv