You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Christopher Matta <cm...@mapr.com> on 2015/06/09 04:20:48 UTC
Drill authentication with pyodbc
Does anyone know what the expected key names are for userid and password
for an ODBC connection? I was using pyodbc to connect to Drill pre-1.0 but
now with authentication enabled I haven’t figured out how to do it.
Relevant errors:
conn = pyodbc.connect('Driver=/opt/mapr/drillodbc/lib/universal/libmaprdrillodbc.dylib;ConnectionType=Zookeeper;ZKQuorum=hosta:5181,hostb:5181,hostc:5181;ZKClusterID=cluster-drillbits;Catalog=DRILL;AuthenticationType=BasicAuthentication;AdvancedProperties=CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=utc;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5;uid=cmatta;pwd=xxxx',
autocommit=True)
cursor = conn.cursor()
cursor.execute('select * from maprfs.cmatta.tweets_view limit 10;')
--- output ---
---------------------------------------------------------------------------
Error Traceback (most recent call last)
<ipython-input-8-7c757991ba47> in <module>()
1 conn = pyodbc.connect('Driver=/opt/mapr/drillodbc/lib/universal/libmaprdrillodbc.dylib;ConnectionType=Zookeeper;ZKQuorum=hosta:5181,hostb:5181,hostc:5181;ZKClusterID=cluster-drillbits;Catalog=DRILL;AuthenticationType=BasicAuthentication;AdvancedProperties=CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=utc;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5;uid=cmatta;pwd=xxxx',
autocommit=True)
2 cursor = conn.cursor()
----> 3 cursor.execute('select * from maprfs.cmatta.tweets_view limit 10;')
Error: ('HY000', '[HY000] [MapR][Drill] (1040) Drill failed to execute
the query: select * from maprfs.cmatta.tweets_view limit
10;\n[30027]Query execution error. Details:[ \nSYSTEM ERROR:
java.lang.IllegalArgumentException: A valid userName is
expected\n\n\n[Error Id: 2f26217c-ddac-4195-9bfb-03c519a95e56 on
se-node12.se.lab:31010]\n] (1040) (SQLExecDirectW)')
I’ve also tried replacing uid with user, username, userName etc… with no
luck.
Chris Mattacmatta@mapr.com
215-701-3146
Re: Drill authentication with pyodbc
Posted by Kristine Hahn <kh...@maprtech.com>.
Case-sensitive I think, try all caps.
On Monday, June 8, 2015, Kristine Hahn <kh...@maprtech.com> wrote:
> Set the UID and PWD properties in the connection string.
> http://tshiran.github.io/drill/docs/odbc-configuration-reference/ has a
> first draft of the docs that include a description of the properties. Some
> of these docs, esp Linux and windows installation, are incomplete.
>
> On Monday, June 8, 2015, Christopher Matta <cmatta@mapr.com
> <javascript:_e(%7B%7D,'cvml','cmatta@mapr.com');>> wrote:
>
>> Does anyone know what the expected key names are for userid and password
>> for an ODBC connection? I was using pyodbc to connect to Drill pre-1.0 but
>> now with authentication enabled I haven’t figured out how to do it.
>>
>> Relevant errors:
>>
>> conn =
>> pyodbc.connect('Driver=/opt/mapr/drillodbc/lib/universal/libmaprdrillodbc.dylib;ConnectionType=Zookeeper;ZKQuorum=hosta:5181,hostb:5181,hostc:5181;ZKClusterID=cluster-drillbits;Catalog=DRILL;AuthenticationType=BasicAuthentication;AdvancedProperties=CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=utc;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5;uid=cmatta;pwd=xxxx',
>> autocommit=True)
>> cursor = conn.cursor()
>> cursor.execute('select * from maprfs.cmatta.tweets_view limit 10;')
>>
>> --- output ---
>>
>> ---------------------------------------------------------------------------
>> Error Traceback (most recent call last)
>> <ipython-input-8-7c757991ba47> in <module>()
>> 1 conn =
>> pyodbc.connect('Driver=/opt/mapr/drillodbc/lib/universal/libmaprdrillodbc.dylib;ConnectionType=Zookeeper;ZKQuorum=hosta:5181,hostb:5181,hostc:5181;ZKClusterID=cluster-drillbits;Catalog=DRILL;AuthenticationType=BasicAuthentication;AdvancedProperties=CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=utc;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5;uid=cmatta;pwd=xxxx',
>> autocommit=True)
>> 2 cursor = conn.cursor()
>> ----> 3 cursor.execute('select * from maprfs.cmatta.tweets_view limit
>> 10;')
>>
>> Error: ('HY000', '[HY000] [MapR][Drill] (1040) Drill failed to execute
>> the query: select * from maprfs.cmatta.tweets_view limit
>> 10;\n[30027]Query execution error. Details:[ \nSYSTEM ERROR:
>> java.lang.IllegalArgumentException: A valid userName is
>> expected\n\n\n[Error Id: 2f26217c-ddac-4195-9bfb-03c519a95e56 on
>> se-node12.se.lab:31010]\n] (1040) (SQLExecDirectW)')
>>
>> I’ve also tried replacing uid with user, username, userName etc… with no
>> luck.
>>
>> Chris Mattacmatta@mapr.com
>> 215-701-3146
>>
>>
>
>
> --
> Kristine Hahn
> Sr. Technical Writer
> 415-497-8107 @krishahn
>
>
>
--
Kristine Hahn
Sr. Technical Writer
415-497-8107 @krishahn
Re: Drill authentication with pyodbc
Posted by Kristine Hahn <kh...@maprtech.com>.
Set the UID and PWD properties in the connection string.
http://tshiran.github.io/drill/docs/odbc-configuration-reference/ has a
first draft of the docs that include a description of the properties. Some
of these docs, esp Linux and windows installation, are incomplete.
On Monday, June 8, 2015, Christopher Matta <cm...@mapr.com> wrote:
> Does anyone know what the expected key names are for userid and password
> for an ODBC connection? I was using pyodbc to connect to Drill pre-1.0 but
> now with authentication enabled I haven’t figured out how to do it.
>
> Relevant errors:
>
> conn =
> pyodbc.connect('Driver=/opt/mapr/drillodbc/lib/universal/libmaprdrillodbc.dylib;ConnectionType=Zookeeper;ZKQuorum=hosta:5181,hostb:5181,hostc:5181;ZKClusterID=cluster-drillbits;Catalog=DRILL;AuthenticationType=BasicAuthentication;AdvancedProperties=CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=utc;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5;uid=cmatta;pwd=xxxx',
> autocommit=True)
> cursor = conn.cursor()
> cursor.execute('select * from maprfs.cmatta.tweets_view limit 10;')
>
> --- output ---
> ---------------------------------------------------------------------------
> Error Traceback (most recent call last)
> <ipython-input-8-7c757991ba47> in <module>()
> 1 conn =
> pyodbc.connect('Driver=/opt/mapr/drillodbc/lib/universal/libmaprdrillodbc.dylib;ConnectionType=Zookeeper;ZKQuorum=hosta:5181,hostb:5181,hostc:5181;ZKClusterID=cluster-drillbits;Catalog=DRILL;AuthenticationType=BasicAuthentication;AdvancedProperties=CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=utc;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5;uid=cmatta;pwd=xxxx',
> autocommit=True)
> 2 cursor = conn.cursor()
> ----> 3 cursor.execute('select * from maprfs.cmatta.tweets_view limit 10;')
>
> Error: ('HY000', '[HY000] [MapR][Drill] (1040) Drill failed to execute
> the query: select * from maprfs.cmatta.tweets_view limit
> 10;\n[30027]Query execution error. Details:[ \nSYSTEM ERROR:
> java.lang.IllegalArgumentException: A valid userName is
> expected\n\n\n[Error Id: 2f26217c-ddac-4195-9bfb-03c519a95e56 on
> se-node12.se.lab:31010]\n] (1040) (SQLExecDirectW)')
>
> I’ve also tried replacing uid with user, username, userName etc… with no
> luck.
>
> Chris Mattacmatta@mapr.com <javascript:;>
> 215-701-3146
>
>
--
Kristine Hahn
Sr. Technical Writer
415-497-8107 @krishahn
Re: Drill authentication with pyodbc
Posted by Christopher Matta <cm...@mapr.com>.
My fault, I had a greedy regular expression that was replacing the space in
the value “Basic Authentication” with nothing.
It’s working now.
Some people, when confronted with a problem, think “I know, I’ll use
regular expressions.” Now they have two problems.
Thanks guys.
Chris Mattacmatta@mapr.com
215-701-3146
On Mon, Jun 8, 2015 at 11:32 PM, Jim Bates <jb...@maprtech.com> wrote:
I use a DNS in a connection string like this for my pyodbc in my
> notebooks...
>
> MY_DSN = "DSN=Drill 1.0 sandbox;UID=mapr;PWD=mapr"
> conn = pyodbc.connect(MY_DSN, autocommit=True)
>
> On Mon, Jun 8, 2015 at 9:29 PM, Christopher Matta <cm...@mapr.com> wrote:
>
> > That's actually my notebook, which I'm trying to update to use with Drill
> > authentication, yes I'm using the DSN (the first argument in the connect
> > function).
> >
> > Chris Matta
> > cmatta@mapr.com
> > 215-701-3146
> >
> > On Mon, Jun 8, 2015 at 10:24 PM, Matt <bs...@gmail.com> wrote:
> >
> > > Does using a DSN as per this notebook help?
> > >
> > >
> > >
> >
> http://nbviewer.ipython.org/github/cjmatta/drill_ipython_notebook/blob/master/Twitter%20Drill%20Pandas.ipynb
> > >
> > > https://github.com/cjmatta/drill_ipython_notebook
> > >
> > >
> > >
> > > On 8 Jun 2015, at 22:20, Christopher Matta wrote:
> > >
> > > Does anyone know what the expected key names are for userid and
> password
> > >> for an ODBC connection? I was using pyodbc to connect to Drill pre-1.0
> > but
> > >> now with authentication enabled I haven’t figured out how to do it.
> > >>
> > >> Relevant errors:
> > >>
> > >> conn =
> > >>
> >
> pyodbc.connect('Driver=/opt/mapr/drillodbc/lib/universal/libmaprdrillodbc.dylib;ConnectionType=Zookeeper;ZKQuorum=hosta:5181,hostb:5181,hostc:5181;ZKClusterID=cluster-drillbits;Catalog=DRILL;AuthenticationType=BasicAuthentication;AdvancedProperties=CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=utc;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5;uid=cmatta;pwd=xxxx',
> > >> autocommit=True)
> > >> cursor = conn.cursor()
> > >> cursor.execute('select * from maprfs.cmatta.tweets_view limit 10;')
> > >>
> > >> --- output ---
> > >>
> > >>
> >
> ---------------------------------------------------------------------------
> > >> Error Traceback (most recent call last)
> > >> <ipython-input-8-7c757991ba47> in <module>()
> > >> 1 conn =
> > >>
> >
> pyodbc.connect('Driver=/opt/mapr/drillodbc/lib/universal/libmaprdrillodbc.dylib;ConnectionType=Zookeeper;ZKQuorum=hosta:5181,hostb:5181,hostc:5181;ZKClusterID=cluster-drillbits;Catalog=DRILL;AuthenticationType=BasicAuthentication;AdvancedProperties=CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=utc;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5;uid=cmatta;pwd=xxxx',
> > >> autocommit=True)
> > >> 2 cursor = conn.cursor()
> > >> ----> 3 cursor.execute('select * from maprfs.cmatta.tweets_view limit
> > >> 10;')
> > >>
> > >> Error: ('HY000', '[HY000] [MapR][Drill] (1040) Drill failed to execute
> > >> the query: select * from maprfs.cmatta.tweets_view limit
> > >> 10;\n[30027]Query execution error. Details:[ \nSYSTEM ERROR:
> > >> java.lang.IllegalArgumentException: A valid userName is
> > >> expected\n\n\n[Error Id: 2f26217c-ddac-4195-9bfb-03c519a95e56 on
> > >> se-node12.se.lab:31010]\n] (1040) (SQLExecDirectW)')
> > >>
> > >> I’ve also tried replacing uid with user, username, userName etc… with
> no
> > >> luck.
> > >>
> > >> Chris Mattacmatta@mapr.com
> > >> 215-701-3146
> > >>
> > >>
> > >
> >
>
Re: Drill authentication with pyodbc
Posted by Jim Bates <jb...@maprtech.com>.
I use a DNS in a connection string like this for my pyodbc in my
notebooks...
MY_DSN = "DSN=Drill 1.0 sandbox;UID=mapr;PWD=mapr"
conn = pyodbc.connect(MY_DSN, autocommit=True)
On Mon, Jun 8, 2015 at 9:29 PM, Christopher Matta <cm...@mapr.com> wrote:
> That's actually my notebook, which I'm trying to update to use with Drill
> authentication, yes I'm using the DSN (the first argument in the connect
> function).
>
> Chris Matta
> cmatta@mapr.com
> 215-701-3146
>
> On Mon, Jun 8, 2015 at 10:24 PM, Matt <bs...@gmail.com> wrote:
>
> > Does using a DSN as per this notebook help?
> >
> >
> >
> http://nbviewer.ipython.org/github/cjmatta/drill_ipython_notebook/blob/master/Twitter%20Drill%20Pandas.ipynb
> >
> > https://github.com/cjmatta/drill_ipython_notebook
> >
> >
> >
> > On 8 Jun 2015, at 22:20, Christopher Matta wrote:
> >
> > Does anyone know what the expected key names are for userid and password
> >> for an ODBC connection? I was using pyodbc to connect to Drill pre-1.0
> but
> >> now with authentication enabled I haven’t figured out how to do it.
> >>
> >> Relevant errors:
> >>
> >> conn =
> >>
> pyodbc.connect('Driver=/opt/mapr/drillodbc/lib/universal/libmaprdrillodbc.dylib;ConnectionType=Zookeeper;ZKQuorum=hosta:5181,hostb:5181,hostc:5181;ZKClusterID=cluster-drillbits;Catalog=DRILL;AuthenticationType=BasicAuthentication;AdvancedProperties=CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=utc;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5;uid=cmatta;pwd=xxxx',
> >> autocommit=True)
> >> cursor = conn.cursor()
> >> cursor.execute('select * from maprfs.cmatta.tweets_view limit 10;')
> >>
> >> --- output ---
> >>
> >>
> ---------------------------------------------------------------------------
> >> Error Traceback (most recent call last)
> >> <ipython-input-8-7c757991ba47> in <module>()
> >> 1 conn =
> >>
> pyodbc.connect('Driver=/opt/mapr/drillodbc/lib/universal/libmaprdrillodbc.dylib;ConnectionType=Zookeeper;ZKQuorum=hosta:5181,hostb:5181,hostc:5181;ZKClusterID=cluster-drillbits;Catalog=DRILL;AuthenticationType=BasicAuthentication;AdvancedProperties=CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=utc;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5;uid=cmatta;pwd=xxxx',
> >> autocommit=True)
> >> 2 cursor = conn.cursor()
> >> ----> 3 cursor.execute('select * from maprfs.cmatta.tweets_view limit
> >> 10;')
> >>
> >> Error: ('HY000', '[HY000] [MapR][Drill] (1040) Drill failed to execute
> >> the query: select * from maprfs.cmatta.tweets_view limit
> >> 10;\n[30027]Query execution error. Details:[ \nSYSTEM ERROR:
> >> java.lang.IllegalArgumentException: A valid userName is
> >> expected\n\n\n[Error Id: 2f26217c-ddac-4195-9bfb-03c519a95e56 on
> >> se-node12.se.lab:31010]\n] (1040) (SQLExecDirectW)')
> >>
> >> I’ve also tried replacing uid with user, username, userName etc… with no
> >> luck.
> >>
> >> Chris Mattacmatta@mapr.com
> >> 215-701-3146
> >>
> >>
> >
>
Re: Drill authentication with pyodbc
Posted by Christopher Matta <cm...@mapr.com>.
That's actually my notebook, which I'm trying to update to use with Drill
authentication, yes I'm using the DSN (the first argument in the connect
function).
Chris Matta
cmatta@mapr.com
215-701-3146
On Mon, Jun 8, 2015 at 10:24 PM, Matt <bs...@gmail.com> wrote:
> Does using a DSN as per this notebook help?
>
>
> http://nbviewer.ipython.org/github/cjmatta/drill_ipython_notebook/blob/master/Twitter%20Drill%20Pandas.ipynb
>
> https://github.com/cjmatta/drill_ipython_notebook
>
>
>
> On 8 Jun 2015, at 22:20, Christopher Matta wrote:
>
> Does anyone know what the expected key names are for userid and password
>> for an ODBC connection? I was using pyodbc to connect to Drill pre-1.0 but
>> now with authentication enabled I haven’t figured out how to do it.
>>
>> Relevant errors:
>>
>> conn =
>> pyodbc.connect('Driver=/opt/mapr/drillodbc/lib/universal/libmaprdrillodbc.dylib;ConnectionType=Zookeeper;ZKQuorum=hosta:5181,hostb:5181,hostc:5181;ZKClusterID=cluster-drillbits;Catalog=DRILL;AuthenticationType=BasicAuthentication;AdvancedProperties=CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=utc;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5;uid=cmatta;pwd=xxxx',
>> autocommit=True)
>> cursor = conn.cursor()
>> cursor.execute('select * from maprfs.cmatta.tweets_view limit 10;')
>>
>> --- output ---
>>
>> ---------------------------------------------------------------------------
>> Error Traceback (most recent call last)
>> <ipython-input-8-7c757991ba47> in <module>()
>> 1 conn =
>> pyodbc.connect('Driver=/opt/mapr/drillodbc/lib/universal/libmaprdrillodbc.dylib;ConnectionType=Zookeeper;ZKQuorum=hosta:5181,hostb:5181,hostc:5181;ZKClusterID=cluster-drillbits;Catalog=DRILL;AuthenticationType=BasicAuthentication;AdvancedProperties=CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=utc;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5;uid=cmatta;pwd=xxxx',
>> autocommit=True)
>> 2 cursor = conn.cursor()
>> ----> 3 cursor.execute('select * from maprfs.cmatta.tweets_view limit
>> 10;')
>>
>> Error: ('HY000', '[HY000] [MapR][Drill] (1040) Drill failed to execute
>> the query: select * from maprfs.cmatta.tweets_view limit
>> 10;\n[30027]Query execution error. Details:[ \nSYSTEM ERROR:
>> java.lang.IllegalArgumentException: A valid userName is
>> expected\n\n\n[Error Id: 2f26217c-ddac-4195-9bfb-03c519a95e56 on
>> se-node12.se.lab:31010]\n] (1040) (SQLExecDirectW)')
>>
>> I’ve also tried replacing uid with user, username, userName etc… with no
>> luck.
>>
>> Chris Mattacmatta@mapr.com
>> 215-701-3146
>>
>>
>
Re: Drill authentication with pyodbc
Posted by Matt <bs...@gmail.com>.
Does using a DSN as per this notebook help?
http://nbviewer.ipython.org/github/cjmatta/drill_ipython_notebook/blob/master/Twitter%20Drill%20Pandas.ipynb
https://github.com/cjmatta/drill_ipython_notebook
On 8 Jun 2015, at 22:20, Christopher Matta wrote:
> Does anyone know what the expected key names are for userid and
> password
> for an ODBC connection? I was using pyodbc to connect to Drill pre-1.0
> but
> now with authentication enabled I haven’t figured out how to do it.
>
> Relevant errors:
>
> conn =
> pyodbc.connect('Driver=/opt/mapr/drillodbc/lib/universal/libmaprdrillodbc.dylib;ConnectionType=Zookeeper;ZKQuorum=hosta:5181,hostb:5181,hostc:5181;ZKClusterID=cluster-drillbits;Catalog=DRILL;AuthenticationType=BasicAuthentication;AdvancedProperties=CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=utc;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5;uid=cmatta;pwd=xxxx',
> autocommit=True)
> cursor = conn.cursor()
> cursor.execute('select * from maprfs.cmatta.tweets_view limit 10;')
>
> --- output ---
> ---------------------------------------------------------------------------
> Error Traceback (most recent call last)
> <ipython-input-8-7c757991ba47> in <module>()
> 1 conn =
> pyodbc.connect('Driver=/opt/mapr/drillodbc/lib/universal/libmaprdrillodbc.dylib;ConnectionType=Zookeeper;ZKQuorum=hosta:5181,hostb:5181,hostc:5181;ZKClusterID=cluster-drillbits;Catalog=DRILL;AuthenticationType=BasicAuthentication;AdvancedProperties=CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=utc;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5;uid=cmatta;pwd=xxxx',
> autocommit=True)
> 2 cursor = conn.cursor()
> ----> 3 cursor.execute('select * from maprfs.cmatta.tweets_view limit
> 10;')
>
> Error: ('HY000', '[HY000] [MapR][Drill] (1040) Drill failed to execute
> the query: select * from maprfs.cmatta.tweets_view limit
> 10;\n[30027]Query execution error. Details:[ \nSYSTEM ERROR:
> java.lang.IllegalArgumentException: A valid userName is
> expected\n\n\n[Error Id: 2f26217c-ddac-4195-9bfb-03c519a95e56 on
> se-node12.se.lab:31010]\n] (1040) (SQLExecDirectW)')
>
> I’ve also tried replacing uid with user, username, userName etc…
> with no
> luck.
>
> Chris Mattacmatta@mapr.com
> 215-701-3146
>