Extract hive table structure

Shell Programming and Scripting
1

Hi,

I need to extract only the create table structure with columns alone.
for eg

hive_table

show create table hive_table:
create table hive_table(id number,age number)
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION  'hdfs:/path/'

I need only below

create table hive_table(id number,age number);

Code which i am using


#!/bin/bash
rm -f tableNames.txt
rm -f HiveTableDDL.txt
hive -e "use $1; show tables;" > tableNames.txt  
wait
cat ae_table.txt |while read LINE
   do
   hive -e "use $1;show create table $LINE" >>table.hql
   echo  -e ";\n" >> table.hql
   done
rm -f tableNames.txt
echo "Table DDL generated"
2

Not sure what you're targeting at. What be your ultimate result? Your "need" has a semicolon that does not exist in the ?input file?
In your code, the file ae_table.txt is neither created nor written to, so you're depending on what is in there from any time before. To tell what is happening in your script, esp. with $1 positional parameter, you need to show how that script is invoked.

Just off the cuff, how far would

grep "^create" file

get you?

3

Hi,

i am trying to extract the tables from the database named as schema and get the create table structure

#!/bin/bash
rm -f tableNames.txt
rm -f HiveTableDDL.txt
hive -e "use $1; show tables;" > tableNames.txt  
wait
cat tableNames.txt |while read LINE
   do
   hive -e "use $1;show create table $LINE" >>table.hql
   echo  -e ";\n" >> table.hql
   done
rm -f tableNames.txt
echo "Table DDL generated"

sh table.sh schema

I would require to extract only till below

create table hive_table(id number,age number);

because show create will give me table properties like below

create table hive_table(id number,age number)
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION  'hdfs:/path/'
4

Perhaps you should pipe the "create table" command to grep "create table " or to sed as follows

#!/bin/bash
rm -f tableNames.txt
rm -f HiveTableDDL.txt
hive -e "use $1; show tables;" > tableNames.txt  
wait
while read LINE
do
   hive -e "use $1;show create table $LINE" |
     sed -n '1,/create table /p'
   printf  ";\n\n" 
done < tableNames.txt >> table.hql
rm -f tableNames.txt
echo "Table DDL generated"
5

Hi,

Thanks for the suggestion, but still i am unable to achive, this is by show create table structure

CREATE TABLE `test`(
  `id` string COMMENT '',
  `age` string COMMENT '',
  `city` string COMMENT '')
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
WITH SERDEPROPERTIES (
  'path'='hdfs://local/')
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  'hdfs://local/'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='false',
  'EXTERNAL'='FALSE',
  'numFiles'='14',
  'numRows'='-1',
  'rawDataSize'='-1',
  'spark.sql.sources.provider'='orc',
  'spark.sql.sources.schema.numParts'='9',
'spark.sql.sources.schema.part.8'='....":{}}]}',
  'totalSize'='12',
  'transient_lastDdlTime'='12')

i would need

CREATE TABLE `test`(
  `id` string COMMENT '',
  `age` string COMMENT '',
  `city` string COMMENT '')
6

Replace the sed line by this sed line

sed -n -e '/^ *CREATE TABLE /{' -e ':L' -e '/) *$/{' -e 'p;d' -e '}' -e 'N; bL'

It prints the block between "CREATE TABLE " and the closing ")"

1 Like