Hive Table DDL using Unix shell script

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,

edited Nov 14 '18 at 14:13

Kaushik Nayak

19.1k41331

asked Nov 14 '18 at 11:12

Sumit D

245

Please check my answer and accept/upvote it if it worked for you so that it would also help others seeking answers.Please read : stackoverflow.com/help/someone-answers

– Kaushik Nayak
Dec 19 '18 at 15:39

add a comment |

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,

edited Nov 14 '18 at 14:13

Kaushik Nayak

19.1k41331

asked Nov 14 '18 at 11:12

Sumit D

245

Please check my answer and accept/upvote it if it worked for you so that it would also help others seeking answers.Please read : stackoverflow.com/help/someone-answers

– Kaushik Nayak
Dec 19 '18 at 15:39

add a comment |

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,

edited Nov 14 '18 at 14:13

Kaushik Nayak

19.1k41331

asked Nov 14 '18 at 11:12

Sumit D

245

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,

unix hive

edited Nov 14 '18 at 14:13

Kaushik Nayak

19.1k41331

asked Nov 14 '18 at 11:12

Sumit D

245

edited Nov 14 '18 at 14:13

Kaushik Nayak

19.1k41331

asked Nov 14 '18 at 11:12

Sumit D

245

edited Nov 14 '18 at 14:13

Kaushik Nayak

19.1k41331

edited Nov 14 '18 at 14:13

Kaushik Nayak

19.1k41331

edited Nov 14 '18 at 14:13

Kaushik Nayak

19.1k41331

asked Nov 14 '18 at 11:12

Sumit D

245

asked Nov 14 '18 at 11:12

Sumit D

245

asked Nov 14 '18 at 11:12

Sumit D

245

Please check my answer and accept/upvote it if it worked for you so that it would also help others seeking answers.Please read : stackoverflow.com/help/someone-answers

– Kaushik Nayak
Dec 19 '18 at 15:39

add a comment |

Please check my answer and accept/upvote it if it worked for you so that it would also help others seeking answers.Please read : stackoverflow.com/help/someone-answers

– Kaushik Nayak
Dec 19 '18 at 15:39

Please check my answer and accept/upvote it if it worked for you so that it would also help others seeking answers.Please read : stackoverflow.com/help/someone-answers

– Kaushik Nayak
Dec 19 '18 at 15:39

add a comment |

1 Answer
1

active

oldest

votes

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 '18 at 10:16

answered Nov 14 '18 at 14:13

Kaushik Nayak

19.1k41331

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

– Sumit D
Nov 16 '18 at 9:58

1

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

– Kaushik Nayak
Nov 16 '18 at 10:17

Thanks a lot!!!!

– Sumit D
Nov 16 '18 at 11:09

add a comment |

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53298877%2fhive-table-ddl-using-unix-shell-script%23new-answer', 'question_page');

);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

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 '18 at 10:16

answered Nov 14 '18 at 14:13

Kaushik Nayak

19.1k41331

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

– Sumit D
Nov 16 '18 at 9:58

1

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

– Kaushik Nayak
Nov 16 '18 at 10:17

Thanks a lot!!!!

– Sumit D
Nov 16 '18 at 11:09

add a comment |

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 '18 at 10:16

answered Nov 14 '18 at 14:13

Kaushik Nayak

19.1k41331

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

– Sumit D
Nov 16 '18 at 9:58

1

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

– Kaushik Nayak
Nov 16 '18 at 10:17

Thanks a lot!!!!

– Sumit D
Nov 16 '18 at 11:09

add a comment |

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 '18 at 10:16

answered Nov 14 '18 at 14:13

Kaushik Nayak

19.1k41331

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 '18 at 10:16

answered Nov 14 '18 at 14:13

Kaushik Nayak

19.1k41331

edited Nov 16 '18 at 10:16

answered Nov 14 '18 at 14:13

Kaushik Nayak

19.1k41331

answered Nov 14 '18 at 14:13

Kaushik Nayak

19.1k41331

answered Nov 14 '18 at 14:13

Kaushik Nayak

19.1k41331

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

– Sumit D
Nov 16 '18 at 9:58

1

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

– Kaushik Nayak
Nov 16 '18 at 10:17

Thanks a lot!!!!

– Sumit D
Nov 16 '18 at 11:09

add a comment |

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

– Sumit D
Nov 16 '18 at 9:58

1

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

– Kaushik Nayak
Nov 16 '18 at 10:17

Thanks a lot!!!!

– Sumit D
Nov 16 '18 at 11:09

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

– Sumit D
Nov 16 '18 at 9:58

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

– Kaushik Nayak
Nov 16 '18 at 10:17

Thanks a lot!!!!

– Sumit D
Nov 16 '18 at 11:09

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Odtnhj