Hive Table DDL using Unix shell script

Question

I am a novice in Unix and need some help. I have a excel file in the below format.

Table,Column,Datatype,Inputformat

TableA,col1,int,TEXTFILE

TableA,col2,string,TEXTFILE

TableA,col3,float,TEXTFILE

TableA,col4,int,TEXTFILE

TableB,col1,string,TEXTFILE

TableB,col2,int,TEXTFILE

TableB,col3,int,TEXTFILE

Likewise I have records for 100 tables.

I need to create a ddl statement for hive table creation for all those 100 tables using unix.

e.g:

create table TableA(col1 int ,col2 string,col3 float,col4 int) STORED AS TEXTFILE;

create table TableB(col1 string ,col2 int,col3 int) STORED AS TEXTFILE;

Can you please help me with the approach.

Thanks,

Kaushik Nayak · Accepted Answer · 2018-11-16 10:16:49Z

1

You may prepare an awk script

awk  -F ',' '{ 
                 a[$1] = a[$1] " " $2 " " $3 ","; #read the column/dtype into array
                 b[$1] = $4 ;                     #read the file format
         }END{
              for (i in a ) #loop through the concatenated string
              { gsub(/,$/, ")" ,a[i] );           #replace last comma with ")"
                      print "CREATE TABLE " i " (" a[i] " STORED AS " b[i] ;
                  } 
                }' filename

edited Nov 16, 2018 at 10:16

answered Nov 14, 2018 at 14:13

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

2 Comments

Sumit D Over a year ago

NayakWhat if I want to give the Inputformat dynamically? e.g: Table,Column,Datatype,Inputformat TableA,col1,int,TEXTFILE TableA,col2,string,TEXTFILE TableA,col3,float,TEXTFILE TableA,col4,int,TEXTFILE TableB,col1,string,SEQUENCEFILE TableB,col2,int,SEQUENCEFILE TableB,col3,int,SEQUENCEFILE

Kaushik Nayak Over a year ago

@SumitD : I've edited the script to handle that now.

Collectives™ on Stack Overflow

Hive Table DDL using Unix shell script

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related