By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,739 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

How to create hive comparable table statement using Python

P: 3
I/p:SQL server file is input
I/p:Hive create statements
I tried with this code but unable to reach some portion.please help and am new to Python
Eg create statement

create external table db.emp
(
Name string,
I'd int,
Fname string,
.....,

)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '~'


LOCATION 'usr/bin/...';


#!/bin/sh
import string
from itertools import izip

infile = r"ip1.txt"
outfile = "op1.txt"
delete_list = ["NULL,","NULL",chr(91),chr(93),chr(41),chr(40),chr (44)]
datatypes = ["varchar","datetime","char","numeric"]
fin = open(infile)
fout = open(outfile, "w+")
for line in fin:
for word in delete_list:
line = line.replace(word, "")
out0= line.strip(' \t\n\r')
out1= out0.split(' ')
for word in datatypes:
if 'varchar 'in out1[1]:
line = line.replace(word, "string")
elif 'numeric' in out1[1]:
line = line.replace(word, "int")
elif 'char' in out1[1]:
line = line.replace(word, "string")
elif 'datetime' in out1[1]:
line = line.replace(word, "timestamp")
else:
line = line.replace(word,"string")
fout.write(line)
fin.close()
fout.close()
f1 = open("op1.txt","r")
temp = open("ip2.txt","w")
temp1 = open("ip3.txt","w")
f2 = open("op2.txt", "w")
for line in f1:
out0 = line.strip(' \t\n\r')
out1 = out0.split(' ')
x = out1[1]
print >>temp,x
y = out1[0]
print >>temp1,y
f1.close()
temp.close()
temp1.close()
with open("ip2.txt") as f3:
z = ''.join(i for i in f3.read() if not i.isdigit())
with open("op2.txt", "w")as f2:
f2.write(z)
with open ('op3.txt','w')as res:

with open('ip3.txt')as f1:

with open('op2.txt')as f2:
for line1,line2 in zip(f1,f2):
res.write("{0} {1}\n".format(line1.rstrip(),line2.rstrip()))

f2.close()
res.close()
May 10 '17 #1

✓ answered by manyatha

Thank you it's help full.but currently am getting data as file,so there I need to convert column names and data types I have done.but finally create statement unable to insert last few line.if any suggestions it should be appreciated.

Share this Question
Share on Google+
2 Replies


Expert 100+
P: 613
An SQLite tutorial is at http://zetcode.com/db/sqlitepythontutorial/ It uses a smaller subset of SQL commands than officially available. But these commands will work for any SQL interface if you just want to create a database and maintain the records in it. Note that if you use another SQL (not SQLite), you have to have Python bindings for it installed. So if you use PostgreSQL for example, you would have to install one of the Python bindings from https://wiki.postgresql.org/wiki/Python
May 10 '17 #2

P: 3
Thank you it's help full.but currently am getting data as file,so there I need to convert column names and data types I have done.but finally create statement unable to insert last few line.if any suggestions it should be appreciated.
May 11 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.