0

I can us PYHIVE to connect to PRESTO and select data back just fine. I am trying to use PYHIVE to run "insert into x select from y" on presto and it is not running. I am sure I am missing something simple. 

from pyhive import presto
import requests
from requests.auth import HTTPBasicAuth
import pandas as pd

req_kw = {'auth': HTTPBasicAuth(user, pw),'verify':False}

conn = presto.connect(host=ht,port=prt,protocol='https',catalog='hive',username=user,requests_kwargs=req_kw)

cursor  = conn.cursor()

query='select count(1) from dim.date_dim '
cursor.execute(query)
print(cursor.fetchall())

query='insert into flowersc.date_dim select * from dim.date_dim'
cursor.execute(query)

query='select count(1) from flowersc.date_dim '
cursor.execute(query)
print(cursor.fetchall())

no errors occur

but the results show no data loaded

[(16624,)] [(0,)]

Any help is greatly appreciated.

Clark
  • 1
  • 1

1 Answers1

0

You need to check (fetch) result in

query='insert into flowersc.date_dim select * from dim.date_dim'
cursor.execute(query).next() # added .next()

This is needed due to a change in Presto in May 2018 (https://github.com/prestosql/presto/commit/568449b8d058ed8281cc5277bb53902fd044cad7). But it's also a good practise to verify query results, i.e. check that your INSERT statement succeeds.

Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82
  • Thanks for the response but it is not failing with an error. – Clark Sep 30 '19 at 13:39
  • from pyhive import presto import requests from requests.auth import HTTPBasicAuth import pandas as pd req_kw = {'auth': HTTPBasicAuth(user, pw),'verify':False} conn = presto.connect(host=ht,port=prt,protocol='https',catalog='hive',username=user,requests_kwargs=req_kw) cursor = conn.cursor() query='select count(1) from dim.date_dim ' cursor.execute(query) print(cursor.fetchall()) query='insert into flowersc.date_dim select * from dim.date_dim' cursor.execute(query).fetchall() query='select count(1) from flowersc.date_dim ' cursor.execute(query) print(cursor.fetchall()) – Clark Sep 30 '19 at 13:39
  • --------------------------------------------------------------------------- AttributeError Traceback (most recent call last) in 11 12 query='insert into flowersc.date_dim select * from dim.date_dim' ---> 13 cursor.execute(query).fetchall() 14 15 query='select count(1) from flowersc.date_dim ' AttributeError: 'NoneType' object has no attribute 'fetchall' – Clark Sep 30 '19 at 13:40
  • I figured it out. I used the next() method. – Clark Sep 30 '19 at 13:55