Extract table columns from a create table query

Question

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!

Answer 1

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

Answer 2

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.