Discussion:
ODBC querying issues- Can only see files in Drill Explorer, not with other client
Add Reply
Jack Ingoldsby
2017-06-15 22:42:47 UTC
Reply
Permalink
Raw Message
Hi,

I'm using Windows embedded to connect to S3, but am having querying using
ODBC

The ODBC connection works (connection string below)
CastAnyToVarchar=true;Catalog=s3citibike;Schema=default;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=local;NumberOfPrefetchBuffers=5;StringColumnLength=1024;ConvertToCast=false

Using Drill Explorer (direct to Drillbit), I can see the files in
s3citibike.default, and view the data (see attached image) but for some
reason I cannot see my files when using ODBC with another client such as
Excel.



I can query using sqline, for example the bellow returns the dataset

SELECT * FROM `s3citibike`.`default`.`./201307-citibike-tripdata.csv` LIMIT
100;

I'm kind of guessing I'm just not specifying the folder path correctly, but
I've been looking around for a while, tried Catalog = DRILL, schema =
s3citibike.default, no avail.


I'd try the drill-jdbc-all-1.10.0.jar JDBC driver for my client, but
understand it doesn't work with embedded Windows

Can anyone see where I'm going wrong?

Thanks and regards,
Jack
Jack Ingoldsby
2017-06-16 15:16:07 UTC
Reply
Permalink
Raw Message
Hi,
Sorry to bump this, but I just asked by one of my sales guys if I could get
this working for a customer meeting in a couple of hours where an inability
to query S3 via Apache Drill ODBC or JDBC is a dealbreaker.

If anyone has any thoughts would be greatly appreciated. Possibly an Amazon
token might find its way to you...
Regards,
Jack
Post by Jack Ingoldsby
Hi,
I'm using Windows embedded to connect to S3, but am having querying using
ODBC
The ODBC connection works (connection string below)
CastAnyToVarchar=true;Catalog=s3citibike;Schema=default;
HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=
local;NumberOfPrefetchBuffers=5;StringColumnLength=1024;
ConvertToCast=false
Using Drill Explorer (direct to Drillbit), I can see the files in
s3citibike.default, and view the data (see attached image) but for some
reason I cannot see my files when using ODBC with another client such as
Excel.
I can query using sqline, for example the bellow returns the dataset
SELECT * FROM `s3citibike`.`default`.`./201307-citibike-tripdata.csv`
LIMIT 100;
I'm kind of guessing I'm just not specifying the folder path correctly,
but I've been looking around for a while, tried Catalog = DRILL, schema =
s3citibike.default, no avail.
I'd try the drill-jdbc-all-1.10.0.jar JDBC driver for my client, but
understand it doesn't work with embedded Windows
Can anyone see where I'm going wrong?
Thanks and regards,
Jack
Andries Engelbrecht
2017-06-16 15:21:01 UTC
Reply
Permalink
Raw Message
With tools like Excel you will either have to figure out how to enter custom SQL, or if you want the data to be more visible to these tools you will have to create Drill Views and then reference these views from the tool via ODBC/JDBC. Properly define the column name and data types in the Views to make it easier for the end user/tool to process the data (this way you push the work to Drill).

--Andries


On 6/16/17, 8:16 AM, "Jack Ingoldsby" <***@gmail.com> wrote:

Hi,
Sorry to bump this, but I just asked by one of my sales guys if I could get
this working for a customer meeting in a couple of hours where an inability
to query S3 via Apache Drill ODBC or JDBC is a dealbreaker.

If anyone has any thoughts would be greatly appreciated. Possibly an Amazon
token might find its way to you...
Regards,
Jack
Post by Jack Ingoldsby
Hi,
I'm using Windows embedded to connect to S3, but am having querying using
ODBC
The ODBC connection works (connection string below)
CastAnyToVarchar=true;Catalog=s3citibike;Schema=default;
HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=
local;NumberOfPrefetchBuffers=5;StringColumnLength=1024;
ConvertToCast=false
Using Drill Explorer (direct to Drillbit), I can see the files in
s3citibike.default, and view the data (see attached image) but for some
reason I cannot see my files when using ODBC with another client such as
Excel.
I can query using sqline, for example the bellow returns the dataset
SELECT * FROM `s3citibike`.`default`.`./201307-citibike-tripdata.csv`
LIMIT 100;
I'm kind of guessing I'm just not specifying the folder path correctly,
but I've been looking around for a while, tried Catalog = DRILL, schema =
s3citibike.default, no avail.
I'd try the drill-jdbc-all-1.10.0.jar JDBC driver for my client, but
understand it doesn't work with embedded Windows
Can anyone see where I'm going wrong?
Thanks and regards,
Jack Ingoldsby
2017-06-16 17:11:58 UTC
Reply
Permalink
Raw Message
Hi,
Got it to work by adding view! Some issues still, but loaded the data.
Sales guy says he doesn't need it any more of course, but very glad to have
this functionality.
Ping me with your email address (and home country), will send you a small
token (gift card)
Best,
Jack
Post by Andries Engelbrecht
With tools like Excel you will either have to figure out how to enter
custom SQL, or if you want the data to be more visible to these tools you
will have to create Drill Views and then reference these views from the
tool via ODBC/JDBC. Properly define the column name and data types in the
Views to make it easier for the end user/tool to process the data (this way
you push the work to Drill).
--Andries
Hi,
Sorry to bump this, but I just asked by one of my sales guys if I could get
this working for a customer meeting in a couple of hours where an inability
to query S3 via Apache Drill ODBC or JDBC is a dealbreaker.
If anyone has any thoughts would be greatly appreciated. Possibly an Amazon
token might find its way to you...
Regards,
Jack
On Thu, Jun 15, 2017 at 6:42 PM, Jack Ingoldsby <
Post by Jack Ingoldsby
Hi,
I'm using Windows embedded to connect to S3, but am having querying
using
Post by Jack Ingoldsby
ODBC
The ODBC connection works (connection string below)
CastAnyToVarchar=true;Catalog=s3citibike;Schema=default;
HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=
local;NumberOfPrefetchBuffers=5;StringColumnLength=1024;
ConvertToCast=false
Using Drill Explorer (direct to Drillbit), I can see the files in
s3citibike.default, and view the data (see attached image) but for
some
Post by Jack Ingoldsby
reason I cannot see my files when using ODBC with another client
such as
Post by Jack Ingoldsby
Excel.
I can query using sqline, for example the bellow returns the dataset
SELECT * FROM `s3citibike`.`default`.`./
201307-citibike-tripdata.csv`
Post by Jack Ingoldsby
LIMIT 100;
I'm kind of guessing I'm just not specifying the folder path
correctly,
Post by Jack Ingoldsby
but I've been looking around for a while, tried Catalog = DRILL,
schema =
Post by Jack Ingoldsby
s3citibike.default, no avail.
I'd try the drill-jdbc-all-1.10.0.jar JDBC driver for my client, but
understand it doesn't work with embedded Windows
Can anyone see where I'm going wrong?
Thanks and regards,
Jack
Loading...