0

I have a .sql file that has a create table query:

<<<<< some text >>>>>
CREATE EXTERNAL TABLE table_name
(
    key1                   int ,
    key2                varchar(256),
    key3                   int ,
    key4            varchar(64),
    key5                   int ,
)
<<<<<<< some text >>>>>>>>

Now I want just the column names in a string separated by commas and enclosed in parentheses. That is, for the above table, I want:

( key1, key2, key3, key4, key5 )

From what I thought, it is getting very very complicated. I want to script this because I have 150 such files and few of them have about 300 columns. Not a great idea to write it by hand. Can someone suggest an easier method? Thanks!

  • I'd query the table for this using SQL, and then munge the output as necessary. – Faheem Mitha May 14 '17 at 14:43
0

This should work

Old_IFS=$IFS
IFS=$'\n'

file=`cat file.sql` > /dev/null

for line in $file
do

    temp=`echo "$line" | awk '{print $1}'`


    if [[ "$temp" == "create" ]] || [[ "$temp" == "" ]]
    then
        echo ""

    elif [[ "$temp" == "(" ]]
    then
        printf "$temp"
    elif [[ "$temp" == ")" ]]
    then
        printf "$temp\n"
    else
        printf "$temp,"
    fi
done

IFS=$Old_IFS


exit 0

Create a script with this code and substitute "file.sql" with your file and you should get what you need

Of course I don't know the entire structure of your file, but add clauses in the if statement if there are other things you want to avoid

The variable temp will store the first instance of each line, based on that you can choose whether to keep or not lines

0
sed -Ee '
   /^CREATE EXTERNAL TABLE/!d
   s/.*//
   :loop
      $!N
      s/(\n\()\n\s*(\S+).*\s.*\S.*/\2, \1/
   /\n\)$/!bloop
   s/(.*), \n\(\n\)$/( \1 )/
' filename.sql

Results

( key1, key2, key3, key4, key5 )

Explanation

  • The pattern space has two lines at any given time.
  • We preserve the first field and delete everything from the 2nd line.
  • Do this till we hit the lone ) line.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.