0

Using Cloudera Data Science Workbench, python 2 session i am calling a .sql file :

Changing tables names for security:
DROP TABLE IF EXISTS database1.table1 ;

CREATE TABLE IF NOT EXISTS database1.table1 ;
AS Select tb.column1
FROM database2.table2 as tb lIMIT 10

The queries execute fine in HUE but i get the following error when I call them from pyhive

Pyhs2Exception:
 "Error while compiling statement: FAILED: ParseException line 1:84 missing EOF at ';'

My guess is i have have to execute two separate statements when using pyhive, is there a way to combine sql statements when executing one cursor?

here is the cursor part from python, it works fine calling each of the statements if i run them alone" the drop or create statements":

cursor = conn.cursor()
cursor.execute((open("sandbox/test2_table_create.sql").read()))
Anshul Goyal
  • 73,278
  • 37
  • 149
  • 186
Brian DS
  • 51
  • 9
  • sounds like the sql is bad. The error you're getting is usually associated with the parser not finding the end of the file where it expected. In this case it sounds like the SQL is indicating that the last command was reached but python is detecting that there are more commands in the file. – MCBama Dec 06 '17 at 17:14
  • that is what is happening. can you include two commands in one file? – Brian DS Dec 06 '17 at 17:41
  • Sure. It looks to me like you have a semi-colon out of place or possibly a comment that isn't a comment? "Changing tables names for security:" looks like a comment line but I don't see anything making it a comment which will make the parser think it's a command. The entire second command looks weird to me as well since usually `AS` commands come after a `SELECT` call not before. – MCBama Dec 06 '17 at 17:49
  • The SQL runs fine in HUE because HUE takes the ; semicolon as the end of the command. Sounds like I may have to iterate thru the file and execute each command separately? – Brian DS Dec 06 '17 at 17:53
  • You shouldn't have to. But I'll admit to not having much experience using sql in python. In fact I tend to avoid it because of stuff like this xD. So hopefully someone more experienced in that area will be able to help you more. – MCBama Dec 06 '17 at 17:56

1 Answers1

0

It believe there is not a way to execute multiple SQL commands in one execute statement using Impyla.

Brian DS
  • 51
  • 9