Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Extract hive table structure

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Extract hive table structure
# 1  
Old 01-17-2018
Extract hive table structure
Hi,

I need to extract only the create table structure with columns alone.
for eg
Code:
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
Code:
create table hive_table(id number,age number);

Code which i am using

Code:
#!/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  
Old 01-17-2018
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
Code:
grep "^create" file

get you?
# 3  
Old 01-17-2018
Hi,

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

Code:
#!/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

Code:
create table hive_table(id number,age number);

because show create will give me table properties like below

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

# 4  
Old 01-17-2018
Perhaps you should pipe the "create table" command to grep "create table " or to sed as follows
Code:
#!/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  
Old 01-17-2018
Hi,

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

Code:
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
Code:
CREATE TABLE `test`(
  `id` string COMMENT '',
  `age` string COMMENT '',
  `city` string COMMENT '')

# 6  
Old 01-18-2018
Replace the sed line by this sed line
Code:
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 ")"
This User Gave Thanks to MadeInGermany For This Post:
rohit_shinez
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Shell Script to read hive table and send email

HI Team, I am working on reading hive table and send email in email body using shell script, can you please help on fixing the errors: I have 6 columns in my hive table and trying to send the email in the mail body. below script: #!/bin/bash hive -e 'SELECT count(*) from db.table' >... (4 Replies)
Discussion started by: Mi4304
4 Replies

2. Shell Programming and Scripting

JSON structure to table form in awk, bash

Hello guys, I want to parse a JSON file in order to get the data in a table form. My JSON file is like this: { "document":{ "page": }, { "column": } ] }, { ... (6 Replies)
Discussion started by: Gescad
6 Replies

3. Shell Programming and Scripting

Extract table from file

I need to sort out table in a file to a format below: Input: this is a test example Cat Bee Dat 1 2 3 more Example date data Bet Cla Blaa Dat A 6 T data.. Output: this is a test (10 Replies)
Discussion started by: aydj
10 Replies

4. Shell Programming and Scripting

How to export hive table data to a file on local UNIX?

Hi All , I am stuck on the below situation.I have a table called "test" which are created on hive.I need to export the data from hive to a file(test.txt) on local unix system.I have tried the below command ,but its giving the exception . hive -e "select * from test " > /home/user/test.txt ; ... (1 Reply)
Discussion started by: STCET22
1 Replies

5. UNIX for Dummies Questions & Answers

Extract table from an HTML file

I want to extract a table from an HTML file. the table starts with <table class="tableinfo" and ends with next closing table tag </table> how can I do this with awk/sed... ---------- Post updated at 04:34 PM ---------- Previous update was at 04:28 PM ---------- also I want to... (4 Replies)
Discussion started by: koutroul
4 Replies

6. UNIX and Linux Applications

Help in copying table structure to another table with constraints in Oracle

hi, i need to copy one table with data into another table, right now am using create table table1 as select * from table2 i want the constraints of table1 to be copied to table2 also , can anyone give me some solution to copy the constraints also, now am using oracle 10.2.0.3.0... (1 Reply)
Discussion started by: senkerth
1 Replies

7. Shell Programming and Scripting

Extract files from tar ball without directory structure

Hi, I have tar filw which has multiple directories which contain files. When i extract using tar -xf the directory structure also get extracted. I require only files and not directory structures as there will be overhead of moving the files again. So i searched here and got a solution but... (4 Replies)
Discussion started by: chetan.c
4 Replies

8. Shell Programming and Scripting

Extract table name from DDL

How can I extract table name from the different DDL statement like ALTER TABLE CREATE TABLE etc Basically I have to parse thr the any of the DDL statement and verify if that DDL statement is implemented by DBA or not. how can i do this efficiently in Kornshell scripting. (2 Replies)
Discussion started by: gayathree
2 Replies

9. Shell Programming and Scripting

How to create a C structure using table description.

There is table 'DEPT' in the database with the following desciption: Name Null? Type ------- -------- ------------------------ DEPTNO NOT NULL NUMBER(2) DNAME NULL VARCHAR2(14) LOC NULL VARCHAR2(13) Using shell script, I need to create a structure for the... (2 Replies)
Discussion started by: ehari
2 Replies

10. Shell Programming and Scripting

Extract Table from PDF

Hi Guys! I want to extract table from PDF in HTML. Can we do this using Shell script....??. Please provide me your suggestions. Any help will be highly appreciated. Thanks! (2 Replies)
Discussion started by: parshant_bvcoe
2 Replies
Login or Register to Ask a Question