Hi Francis, I don’t know if you’ve tried it by there are two other python modules: drillpy and pydrill which use Drill’s RESTFul interface and I’ve found them to work a lot better. — C
> On Mar 20, 2018, at 17:16, Francis McGregor-Macdonald <fran...@mc-mac.com> > wrote: > > Hi Kunal, > > I did find: > https://github.com/baztian/jaydebeapi/commit/a1f8d3c3b4621570065d968b4b734bae3f0eaf79 > which suggests that this may be already being worked on. I'm not sure if it > would cover this use case though. I posted a question on the commit which > hopefully gets an answer. > > > > On Tue, Mar 20, 2018 at 5:45 PM, Kunal Khatua <kunalkha...@gmail.com> wrote: > >> Francis >> >> I'm certain this is the result of JayDeBeApi using the preparedStatement >> command. (DRILL-5316. See the comments in the JIRA) >> >> I was thinking of creating a fork and using the standard >> Connection.getStatement() API instead, before compiling. However, I'm >> currently on a time crunch and my Python skills are a bit rusty. Hoping >> someone in the community can step forward and take a crack at this. >> >> ~ KK >> >> >> On 3/19/2018 7:30:49 PM, Francis McGregor-Macdonald <fran...@mc-mac.com> >> wrote: >> Thanks Kunal and Charles, >> >> I rebuilt the script / environment inside a container to see if I could >> replicate and I have the same result. >> >> The container is running on an EC2 "next to" the cluster. >> >> Charles was there any additional configuration you had done? >> >> I have in the Dockerfile: >> ... >> conda install -c conda-forge jpype1 -q && \ >> conda install pip -q && \ >> pip install jaydebeapi -q && \ >> ... >> >> I am only loading the single jar into the container, I additionally get: >> "SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder"." ... >> when >> running the script. >> >> I can see suggestions this might be related to "Prepared Statements" but >> can't find anything definitive. >> >> >> >> On Mon, Mar 19, 2018 at 6:16 PM, Kunal Khatua wrote: >> >>> This error looks familiar and might be because of the Python library >>> wrapping a select * around the original query. >>> >>> Using the JDBC driver directly doesn’t seem to show this problem. Drill >>> 1.13.0 is out now. Could you give a try with that and confirm if the >>> behavior is the same? >>> >>> -----Original Message----- >>> From: Charles Givre >>> Sent: Sunday, March 18, 2018 9:10 PM >>> To: user@drill.apache.org >>> Subject: Re: QSQL via jdbc (python3 and JayDeBeApi) wraps with SELECT ... >>> LIMIT 0 >>> >>> Hi Francis, >>> >>> >>> The code below worked for me. Also, I don’t know if it matters, but did >>> you mean to create two cursors? >>> — C >>> >>> import jaydebeapi >>> import pandas as pd >>> >>> #Create the connection object >>> conn = jaydebeapi.connect("org.apache.drill.jdbc.Driver", >>> "jdbc:drill:drillbit=localhost:31010", >>> ["admin", "password"], >>> "/usr/local/share/drill/jars/ >>> jdbc-driver/drill-jdbc-all-1.12.0.jar",) >>> >>> #Create the Cursor Object >>> curs = conn.cursor() >>> >>> #Execute the query >>> curs.execute("SELECT * FROM cp.`employee.json` LIMIT 20") >>> >>> #Get the results >>> curs.fetchall() >>> >>> #Read query results into a Pandas DataFrame df = pd.read_sql("SELECT * >>> FROM cp.`employee.json` LIMIT 20", conn) >>> >>> >>> >>> >>> >>>> On Mar 18, 2018, at 23:41, Francis McGregor-Macdonald >>> fran...@mc-mac.com> wrote: >>>> >>>> Hi all, >>>> >>>> I am attempting to send a query from python3 via JayDeBeApi and am >>>> encountering the issue that the SQL is enclosed in a SELECT * FROM >>>> $myquery LIMIT 0 >>>> >>>> With: >>>> conn = jaydebeapi.connect("org.apache.drill.jdbc.Driver", >>>> "jdbc:drill:drillbit=$mycluster:$myport", >>>> ["$username", "$password"], >>>> "/tmp/drill-jdbc-all-1.12.0.jar") >>>> curs = conn.cursor() >>>> curs = conn.cursor() >>>> curs.execute('SHOW DATABASES') >>>> >>>> ... the query hits Drill as: >>>> SELECT * FROM (SHOW DATABASES) LIMIT 0 >>>> >>>> A select * from mytable limit 100 also has the same issue. >>>> >>>> Drill is version 1.12 >>>> >>>> This also occurs with other queries. I found >>>> https://urldefense.proofpoint.com/v2/url?u=https-3A__issues.apache.org >>>> _jira_browse_DRILL-2D5136&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=-cT6otg6 >>>> lpT_XkmYy7yg3A&m=P8xoBFS297Ln7VimEBQXJDYYFIdoiHRELssI6Cnf4IM&s=6S6F2Zp >>>> p92kNVbtDDuGR29X21JjbNNnn6FZWibbd-gk&e= which looks similar and lists >>>> "Client - ODBC" (not JDBC) >>>> >>>> Has anyone else encountered this? >>> >>> >>
