Connecting dremio with python using jdbc

1

I am trying to connect dremio with python in zeppelin notebook and I have given the connection string below but getting parser error .

%jdbc(dremio)
import jaydebeapi

conn = jaydebeapi.connect(“com.dremio.jdbc.Driver”,
“jdbc:dremio:direct=xx.xx.xx.xx:31010”,
[“username”, “password123”],
‘/usr/hdp/current/zeppelin-server/lib/dremio-jdbc-driver-3.1.9-201904051346520183-a35b753.jar’,)
curs = conn.cursor()
curs.execute(“show databases”)
curs.fetchall()

error:java.sql.SQLException: PARSE ERROR: Failure parsing the query.

SQL Query import jaydebeapi

conn = jaydebeapi.connect(“com.dremio.jdbc.Driver”,
“jdbc:dremio:direct=xx.xx.xx.xx:31010”,
[“username”, “password123”],
‘/usr/hdp/current/zeppelin-server/lib/dremio-jdbc-driver-3.1.9-201904051346520183-a35b753.jar’,)
curs = conn.cursor()
curs.execute(“show databases”)
curs.fetchall()
startLine 1
startColumn 1
endLine 1
endColumn 6
SQL Query import jaydebeapi

2

@abhba,

The PARSE ERROR implies that there’s something wrong with the SQL, but I can’t see anything that jumps out.

Have you tried any other query that you know works in the Dremio UI?

3

Hi Ben,
Thanks for getting back to me.Yeah I tried but I don’t know why it didn’t work for me .Are you using Zeppelin notebook for dremio ??

Regards,

Abhay Singh

4

@abhba you should be able to see some failed queries in Dremio UI with possibly more details. Could you please check and share with us?

5

Hi Laurent,
same error is there in the UI job section .

6

@abhba,
That you see the job in Dremio at least means you’re connecting to Dremio :slightly_smiling_face:

If you open up the profile for the job, you’ll see both the SQL that was submitted and (hopefully) a more detailed error message.

Can you share the profile?

7

Yeah sure ben .Tomorrow I will do that .

8

Hi ben,
this the complete error .

PARSE ERROR: Failure parsing the query.

SQL Query import jaydebeapi

conn = jaydebeapi.connect(“com.dremio.jdbc.Driver”,
“jdbc:dremio:direct=xx.xx.xx.xxx:31010”,
[“user”, “pass123”],
‘/usr/hdp/current/zeppelin-server/lib/dremio-jdbc-driver-3.1.9-201904051346520183-a35b753.jar’,)
curs = conn.cursor()
curs.execute(‘SELECT * FROM “Mongo_Db”.mydb.“customer”’)
curs.fetchall()
startLine 1
startColumn 1
endLine 1
endColumn 6
SQL Query import jaydebeapi

conn = jaydebeapi.connect(“com.dremio.jdbc.Driver”,
“jdbc:dremio:direct=xx.xx.xx.xxx:31010”,
[“user”, “pss123”],
‘/usr/hdp/current/zeppelin-server/lib/dremio-jdbc-driver-3.1.9-201904051346520183-a35b753.jar’,)
curs = conn.cursor()
curs.execute(‘SELECT * FROM “Mongo_Db”.mydb.“customer”’)
curs.fetchall()

(org.apache.calcite.sql.parser.SqlParseException) Encountered “import” at line 1, column 1.
Was expecting one of:
“SHOW” …
“DESCRIBE” …
“USE” …
“CREATE” …
“DROP” …
“ALTER” …
“REFRESH” …
“LOAD” …
“COMPACT” …
“SET” …
“RESET” …
“WITH” …
“+” …
“-” …
“NOT” …
“EXISTS” …
<UNSIGNED_INTEGER_LITERAL> …
<DECIMAL_NUMERIC_LITERAL> …
<APPROX_NUMERIC_LITERAL> …
<BINARY_STRING_LITERAL> …
<PREFIXED_STRING_LITERAL> …
<QUOTED_STRING> …
<UNICODE_STRING_LITERAL> …
“TRUE” …
“FALSE” …
“UNKNOWN” …
“NULL” …
<LBRACE_D> …
<LBRACE_T> …
<LBRACE_TS> …
“DATE” …
“TIME” …
“TIMESTAMP” …
“INTERVAL” …
“CONTAINS(” …
“?” …
“CAST” …
“EXTRACT” …
“POSITION” …
“CONVERT” …
“TRANSLATE” …
“OVERLAY” …
“FLOOR” …
“CEIL” …
“CEILING” …
“SUBSTRING” …
“TRIM” …
“CLASSIFIER” …
“MATCH_NUMBER” …
“RUNNING” …
“PREV” …
“NEXT” …
<LBRACE_FN> …
“MULTISET” …
“ARRAY” …
“PERIOD” …
“SPECIFIC” …
…
<QUOTED_IDENTIFIER> …
<BACK_QUOTED_IDENTIFIER> …
<BRACKET_QUOTED_IDENTIFIER> …
<UNICODE_QUOTED_IDENTIFIER> …
“ABS” …
“AVG” …
“CARDINALITY” …
“CHAR_LENGTH” …
“CHARACTER_LENGTH” …
“COALESCE” …
“COLLECT” …
“COVAR_POP” …
“COVAR_SAMP” …
“CUME_DIST” …
“COUNT” …
“CURRENT_DATE” …
“CURRENT_TIME” …
“CURRENT_TIMESTAMP” …
“DENSE_RANK” …
“ELEMENT” …
“EXP” …
“FIRST_VALUE” …
“FUSION” …
“GROUPING” …
“HOUR” …
“LAG” …
“LEAD” …
“LAST_VALUE” …
“LN” …
“LOCALTIME” …
“LOCALTIMESTAMP” …
“LOWER” …
“MAX” …
“MIN” …
“MINUTE” …
“MOD” …
“MONTH” …
“NTILE” …
“NULLIF” …
“OCTET_LENGTH” …
“PERCENT_RANK” …
“POWER” …
“RANK” …
“REGR_SXX” …
“REGR_SYY” …
“ROW_NUMBER” …
“SECOND” …
“SQRT” …
“STDDEV_POP” …
“STDDEV_SAMP” …
“SUM” …
“UPPER” …
“TRUNCATE” …
“USER” …
“VAR_POP” …
“VAR_SAMP” …
“YEAR” …
“CURRENT_CATALOG” …
“CURRENT_DEFAULT_TRANSFORM_GROUP” …
“CURRENT_PATH” …
“CURRENT_ROLE” …
“CURRENT_SCHEMA” …
“CURRENT_USER” …
“SESSION_USER” …
“SYSTEM_USER” …
“NEW” …
“CASE” …
“CURRENT” …
“CURSOR” …
“ROW” …
“(” …
“SELECT” …
“VALUES” …
“TABLE” …
“EXPLAIN” …
“INSERT” …
“UPSERT” …
“DELETE” …
“UPDATE” …
“MERGE” …
“CALL” …

com.dremio.exec.planner.sql.parser.impl.ParserImpl.convertException():355
com.dremio.exec.planner.sql.parser.impl.ParserImpl.normalizeException():136
org.apache.calcite.sql.parser.SqlParser.parseQuery():138
org.apache.calcite.sql.parser.SqlParser.parseStmt():163
com.dremio.exec.planner.sql.SqlConverter.parse():180
com.dremio.exec.planner.sql.handlers.commands.CommandCreator.getSqlCommand():249
com.dremio.exec.planner.sql.handlers.commands.CommandCreator.toCommand():205
com.dremio.exec.work.foreman.AttemptManager.run():282
java.util.concurrent.ThreadPoolExecutor.runWorker():1149
java.util.concurrent.ThreadPoolExecutor$Worker.run():624
java.lang.Thread.run():748

Caused By (com.dremio.exec.planner.sql.parser.impl.ParseException) Encountered “import” at line 1, column 1.
Was expecting one of:
“SHOW” …
“DESCRIBE” …
“USE” …
“CREATE” …
“DROP” …
“ALTER” …
“REFRESH” …
“LOAD” …
“COMPACT” …
“SET” …
“RESET” …
“WITH” …
“+” …
“-” …
“NOT” …
“EXISTS” …
<UNSIGNED_INTEGER_LITERAL> …
<DECIMAL_NUMERIC_LITERAL> …
<APPROX_NUMERIC_LITERAL> …
<BINARY_STRING_LITERAL> …
<PREFIXED_STRING_LITERAL> …
<QUOTED_STRING> …
<UNICODE_STRING_LITERAL> …
“TRUE” …
“FALSE” …
“UNKNOWN” …
“NULL” …
<LBRACE_D> …
<LBRACE_T> …
<LBRACE_TS> …
“DATE” …
“TIME” …
“TIMESTAMP” …
“INTERVAL” …
“CONTAINS(” …
“?” …
“CAST” …
“EXTRACT” …
“POSITION” …
“CONVERT” …
“TRANSLATE” …
“OVERLAY” …
“FLOOR” …
“CEIL” …
“CEILING” …
“SUBSTRING” …
“TRIM” …
“CLASSIFIER” …
“MATCH_NUMBER” …
“RUNNING” …
“PREV” …
“NEXT” …
<LBRACE_FN> …
“MULTISET” …
“ARRAY” …
“PERIOD” …
“SPECIFIC” …
…
<QUOTED_IDENTIFIER> …
<BACK_QUOTED_IDENTIFIER> …
<BRACKET_QUOTED_IDENTIFIER> …
<UNICODE_QUOTED_IDENTIFIER> …
“ABS” …
“AVG” …
“CARDINALITY” …
“CHAR_LENGTH” …
“CHARACTER_LENGTH” …
“COALESCE” …
“COLLECT” …
“COVAR_POP” …
“COVAR_SAMP” …
“CUME_DIST” …
“COUNT” …
“CURRENT_DATE” …
“CURRENT_TIME” …
“CURRENT_TIMESTAMP” …
“DENSE_RANK” …
“ELEMENT” …
“EXP” …
“FIRST_VALUE” …
“FUSION” …
“GROUPING” …
“HOUR” …
“LAG” …
“LEAD” …
“LAST_VALUE” …
“LN” …
“LOCALTIME” …
“LOCALTIMESTAMP” …
“LOWER” …
“MAX” …
“MIN” …
“MINUTE” …
“MOD” …
“MONTH” …
“NTILE” …
“NULLIF” …
“OCTET_LENGTH” …
“PERCENT_RANK” …
“POWER” …
“RANK” …
“REGR_SXX” …
“REGR_SYY” …
“ROW_NUMBER” …
“SECOND” …
“SQRT” …
“STDDEV_POP” …
“STDDEV_SAMP” …
“SUM” …
“UPPER” …
“TRUNCATE” …
“USER” …
“VAR_POP” …
“VAR_SAMP” …
“YEAR” …
“CURRENT_CATALOG” …
“CURRENT_DEFAULT_TRANSFORM_GROUP” …
“CURRENT_PATH” …
“CURRENT_ROLE” …
“CURRENT_SCHEMA” …
“CURRENT_USER” …
“SESSION_USER” …
“SYSTEM_USER” …
“NEW” …
“CASE” …
“CURRENT” …
“CURSOR” …
“ROW” …
“(” …
“SELECT” …
“VALUES” …
“TABLE” …
“EXPLAIN” …
“INSERT” …
“UPSERT” …
“DELETE” …
“UPDATE” …
“MERGE” …
“CALL” …

com.dremio.exec.planner.sql.parser.impl.ParserImpl.generateParseException():26589
com.dremio.exec.planner.sql.parser.impl.ParserImpl.jj_consume_token():26400
com.dremio.exec.planner.sql.parser.impl.ParserImpl.SqlStmt():891
com.dremio.exec.planner.sql.parser.impl.ParserImpl.SqlStmtEof():903
com.dremio.exec.planner.sql.parser.impl.ParserImpl.parseSqlStmtEof():192
com.dremio.exec.planner.sql.parser.impl.ParserWithCompoundIdConverter.parseSqlStmtEof():59
org.apache.calcite.sql.parser.SqlParser.parseQuery():131
org.apache.calcite.sql.parser.SqlParser.parseStmt():163
com.dremio.exec.planner.sql.SqlConverter.parse():180
com.dremio.exec.planner.sql.handlers.commands.CommandCreator.getSqlCommand():249
com.dremio.exec.planner.sql.handlers.commands.CommandCreator.toCommand():205
com.dremio.exec.work.foreman.AttemptManager.run():282
java.util.concurrent.ThreadPoolExecutor.runWorker():1149
java.util.concurrent.ThreadPoolExecutor$Worker.run():624
java.lang.Thread.run():748
9

fbb7e22a-05b2-4ebc-88ee-1a580b68e1a9.zip (4.9 KB)

this is the profile of the job .Which I ran

10

If you look at your profile in the UI, you will see that the query looks like a lot like your python script instead of just the SHOW DATABASES statement, so my guess is that your zeppelin script should look like:

%jdbc(dremio)
SHOW DATABASES
11

Hi Laurent,
you are correct I can run the queries like this in zeppelin but I want the python to connect with dremio so that I can use python libraries to query dremio .

12

Not an expert at Zeppelin, but in that case, shouldn’t you start your script with %python instead of %jdbc(dremio) according to https://zeppelin.apache.org/docs/0.8.0/interpreter/python.html?

13

Correct Laurent .But for that you need to add a python interpreter and In my case I m using JDBC interpreter to query dremio not Python interpreter .So I don’t think it is needed .

14

Like I said, not an expert using Zeppelin, but if you use %jdbc(...), the remaining of the script should be a SQL statement. If you use %python, then the remaining of the script should be some python code. I don’t think you can have %jdbc with python code.

As far as I can tell, the JDBC driver works as expected, so I would suggest to contact the Zeppelin community (https://zeppelin.apache.org/community.html) to get more guidance on your use-case.