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 <[email protected]> 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 <[email protected]> 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 <[email protected]> >> wrote: >> >>> On Sat, May 14, 2016 at 6:31 PM, John Omernik <[email protected]> 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. >>> >> >> >
