I managed to get a real sql statement from Caravel to Drill ! It failed,
but at least it wasn't a blob of Unicode text. I had to switch UTF-32 to
UTF-16 in mapr.drillodbc.ini. I now do this directly in my dockerfile for
caraveldrill https://github.com/JohnOmernik/caraveldrill
Now I need to look at the SQL Alchemy side of things. Drill didn't like
some of the queries that got sent when I did test connections etc. So now
to figure out how to handle that in the dialect...
(Example of Queries Drill didn't like that Caravel/SQLAlchemy Sent when
adding data connections)
SELECT 'test plain returns' AS anon_1
SELECT 'x' AS some_label
SELECT 'test unicode returns' AS anon_1
SELECT 'x' AS some_label
On Sun, May 15, 2016 at 2:30 PM, John Omernik <j...@omernik.com> wrote:
> I managed to get a skeleton drill dialect to connect to Drill. That said,
> when I "skeletonized" the access Dialect I likely removed to much, in that
> when I tested the connection from Caravel to Drill, this is the error I got
> (note the query was attempted and I saw it in my drill profiles!!)
>
> Basically I used the URL drill+pyodbc:///?%CONNSTRING% wtih %CONNSTRING
> being the uq variable in my pyodbc_example.py that I outputted in my github
> above. That worked except that I needed to set
>
> {
> "metadata_params": {},
> "engine_params": {"connect_args": {"autocommit":1}}
> }
>
> in the Caravel data source page as well as the URL to make it work.
>
> Anywho, I think I am out of my depth at this point, I got it to connect
> and submit "something" to drill, but that's about it... if anyone needs
> help getting to where I got to take it from there, I'd be happy to help.
>
> John
>
>
>
> DBAPIError: (pyodbc.Error) ('HY000', '[HY000] [MapR][Drill] (1040) Drill
> failed to execute the query:
> \x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\n[30027]Query
> execution error. Details:[ \nPARSE ERROR: Lexical error at line 1, column
> 1. Encountered: "\\ufffd" (65533), after : ""\n\nSQL Query
> \x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\x1a\n^\n\n\n[Error
> Id: 9e67eab5-3a39-4527-ab22-763e950dcd86 on
> hadoopmapr5.brewingintel.com:20001]\n] (1040) (SQLExecDirectW)')
>
> On Sun, May 15, 2016 at 10:24 AM, John Omernik <j...@omernik.com> wrote:
>
>> Based on some of the SQL Alchemy documentation, they recommend using the
>> pyodbc-access dialect as a good starting point. (Fairly simple, easy to
>> understand how things play out)
>>
>> https://bitbucket.org/zzzeek/sqlalchemy-access
>>
>> I am looking through the code, but my time to dedicate to this is limited
>> do to an implementation at work that is finally happening. I will continue
>> to post updates as come across them.
>>
>> As a side, I created a git with a simple dev env for Caravel and pyodbc
>> built in a docker container. This includes the MapR ODBC driver, the unix
>> ODBC, and pyodbc. (And has an example python script showing the connection
>> to drill works).
>>
>> Basically, it's everything except the SQL Alchemy Dialect work, Caravel
>> is working with the test data. (And it's persistent, so when you shut down
>> the container, it actually persists the setup work in caravel, so the next
>> time you start, you can just work again). It's not production ready, but
>> it allows people to play around with things, and shows off using python
>> with Drill via pyodbc, using caravel, and allows folks who may know sql
>> alchemy a starting off point.
>>
>> https://github.com/JohnOmernik/caraveldrill
>>
>>
>> John
>>
>>
>>
>>
>> On Sun, May 15, 2016 at 12:34 AM, Ted Dunning <ted.dunn...@gmail.com>
>> wrote:
>>
>>> On Sat, May 14, 2016 at 6:31 PM, John Omernik <j...@omernik.com> wrote:
>>>
>>> > Thoughts on approaching writing a dialect for Drill for SQL alchemy?
>>> > Anyone here done that before?
>>> >
>>>
>>> Nobody has. It is moderately involved, but doesn't look complicated ...
>>> just lengthy.
>>>
>>> Neeraja is coordinating efforts on this.
>>>
>>
>>
>
