Re: Performance tuning

Leandro, I ran into a similar situation while building this demo: https://github.com/cjmatta/DrillPandasReddit/blob/master/Reddit%20Drill%20Pandas.ipynb I don't think Drill splits single JSON files the way it does for delimited one-record-per-line files, so that would explain why you're seeing

Drill impersonation not respecting secondary group on view

Drill doesn’t seem to be respecting secondary group ownership with regards to a view, this view is owned by the analysts group: [mapr@ip-172-16-1-219 data]$ ls -l users_ssns.view.drill -rwxr-x--- 1 mapr analysts 1020 May 3 13:54 users_ssns.view.drill My user is a member of the analysts group

Support for UDTFs?

Are there plans to support UDTFs in Drill? I recall some discussions on a hangout last year about re-implementing the flatten function within a UDTF framework so that this framework could be made available to the community. Also Jacques mentions here that he'd look into putting together a

Case-sensitive view names?

I’m experiencing case-sensitive view names, the drill documentation here: https://drill.apache.org/docs/lexical-structure/#case-sensitivity doesn't explicitly state weather view names are case-insensitive: 0: jdbc:drill:> select state, count(1) from `yelp/yelp_academic_dataset_business.json`

Strange error with hive null data

I have some Hive data loaded with sqoop that looks like this: 0: jdbc:drill:> select curr_fiscal_week, load_dts from hive.dim.`YEAR_DATE_DIM`; +---++ | curr_fiscal_week |load_dts| +---++ | 12.0

Re: Strange error with hive null data

the first column have a type of int? In that case what might be > failing here is your string literal 'null' failing to be implicitly cast to > match the column. > > Jason Altekruse > Software Engineer at Dremio > Apache Drill Committer > > On Fri, Mar 18, 2016 at 6:22 PM, C

Re: extractHeader in session variable?

2016 at 2:51 PM, Jason Altekruse <altekruseja...@gmail.com> wrote: > You need to specify the delimiter, it doesn't seem to default to comma as > the field delimiter. > > On Mon, Feb 29, 2016 at 11:46 AM, Christopher Matta <cma...@mapr.com> > wrote: > > > Actuall

Re: extractHeader in session variable?

quot;,"Clear","230","2012-01-01 08:54:00"] | | ["4:54 AM","37.9","34.0","86","30.11","10.0","SW","4.6","-","N/A","","Clear","230","201

Re: extractHeader in session variable?

t; > E.g.: > > SELECT * FROM TABLE(dfs.`/path/to/CO.dat`(type => 'text', extractHeader => > true)); > > -- > Jacques Nadeau > CTO and Co-Founder, Dremio > > On Fri, Feb 26, 2016 at 12:38 PM, Christopher Matta <cma...@mapr.com> > wrote: > > > Is it pos

extractHeader in session variable?

Is it possible to set the extractHeader option for CSV/TSV in a session variable? Doing it on the format type is just too broad sometimes and I'd like to be able to set it based on the files I'm querying. Chris Matta cma...@mapr.com 215-701-3146

Re: Sqlline Tricks

/stackoverflow.com/questions/3980668/how-to-get-a-password-from-a-shell-script-without-echoing > > Thus, I went that route for more portability. > > On Thu, Feb 4, 2016 at 8:54 AM, Christopher Matta <cma...@mapr.com> wrote: > > > Looks good. You can streamline the no echo of

Re: Sqlline Tricks

specify user and password. > > > > An attacker could see the file name on the [ps ax] output, but they > > wouldn't be able to read the props file. If that file is deleted shortly > > after starting sqlline, even better. > > > > So you can write the user and passw

Re: Sqlline Tricks

The only way I know of getting sqlline to ask for a password is to run the !connect command after starting the shell: $ /opt/mapr/drill/drill-1.4.0/bin/sqlline apache drill 1.4.0 "a drill in the hand is better than two in the bush" sqlline> !connect jdbc:drill: scan complete in 427ms Enter

Re: Sqlline Tricks

auth string, I may open a JIRA on > that, > > I > > > think it would be a helpful feature. Right now my work around is to use > > the > > > bash script to echo out what they need to type to get into drill :) > > > > > > > > > > >

Re: Sqlline Tricks

; >>> Ya, Andries, that's effectively what I did with my script, I passed > the > > >> url > > >>> with -u, but without the username and password provided on the > command > > >>> line, I get an auth error. If there truly is no way to ask for > >

Convert ISO 8601 string to timestamp

What’s the best way to convert this ISO 8601 timestamp to a drill timestamp type? Tried this: 0: jdbc:drill:> select to_timestamp('2016-01-08T15:00:15-05:00') from sys.version; Error: SYSTEM ERROR: DrillRuntimeException: Failure while materializing expression in constant expression evaluator

Changing Map schema in an embeded list

I have some POS log data that I’m converting from XML which has a nested list of items/actions added during the transaction. The issue I’m having is that when I try and flatten the list Drill complains: 0: jdbc:drill:> select

Re: JSON File, Total numbers Record: 1

The apache mailing lists don't support image attachments, it would be better to attach text anyway since it's searchable. Chris Matta cma...@mapr.com 215-701-3146 On Wed, Jan 13, 2016 at 9:55 AM, Paolo Spanevello wrote: > Hi, > > thanks again Jason for your support. > >

Re: SQL Lookup table - how to mimic?

Are you asking about a simple JOIN? Below I have a simple list of country_data.csv that I’m joining to an iso dataset: 0: jdbc:drill:> select * from maprfs.cmatta.`iso_3166_2_countries.csv` limit 10; +-+ | columns | +-+ | ["Sort Order","Common Name","Formal Name","Type","Sub

Re: Sqlline Options

Hi Yogesh, You could achieve this by writing a wrapper script: #!/bin/env bash VARIABLE="testvalue" SQL="SELECT '${VARIABLE}' from sys.version;" sqlline -u jdbc:drill: -n cmatta -p <<< $SQL ​ Chris Matta cma...@mapr.com 215-701-3146 On Sat, Dec 26, 2015 at 10:38 PM, Yogesh Keshetty <

Re: A field reference identifier must not have the form of a qualified name

Seems like Drill is explicitly checking for a period in the key and failing (from FieldReference.java ): private void checkSimpleString(CharSequence value) { if

Query elasticsearch data via Hive?

Hi, I can query data stored in Elasticsearch using Hive and the elasticsearch-hadoop code, but when trying to query that data using drill (via the hive storage plugin) I get a NullPointerException. Is there any way to use the work the ES team did

Re: Querying k/v pairs in a CSV column

ws selected (0.876 seconds) 0: jdbc:drill:> If you could come up with a regexp and string formatting to turn your var1=test var2=test var3=test column into valid JSON you could use CONVERT_FROM as Tomer suggested. ​ Chris Matta cma...@mapr.com 215-701-3146 On Sat, Nov 21, 2015 at 6:38 AM, Ch

Re: Querying k/v pairs in a CSV column

Along the same lines I have a data set that's delimited by pipe with the last column further delimited by commas. It would be great to be able to flatten that last column. On Wednesday, October 7, 2015, Tugdual Grall wrote: > Hi, > > Can you clarify how to use our own format

Re: CTAS over empty file throws NPE

I've run across this issue as well, in my opinion the CTAS should complete without complaint as you can create views on empty tables in RDBMSs. Chris Matta cma...@mapr.com 215-701-3146 On Thu, Oct 22, 2015 at 1:25 PM, Neeraja Rentachintala < nrentachint...@maprtech.com> wrote: > Hsuan > Is

Re: MapR Drill 1.2 Package

Good question, I'd like to know as well. I know that there were some JDBC fixes added to the official Drill 1.2 release, are those going to get back ported into the MapR release? Chris Matta cma...@mapr.com 215-701-3146 On Tue, Oct 20, 2015 at 2:36 PM, John Omernik wrote: >

ODBC Installers should have the versions in the filenames

I end up with multiple ODBC installers in my downloads directory and it's hard to figure out which version each is. Why isn't the release version in the installer file name? Chris Matta cma...@mapr.com 215-701-3146

Re: Drill to S3 set up and core-site.xml

I believe that’s referring to the Hadoop core-site.xml file, if that’s the case then the documentation should be updated to specify it. ​ Chris Matta cma...@mapr.com 215-701-3146 On Wed, Sep 30, 2015 at 5:45 PM, Dan Blondowski < dan.blondow...@dhigroupinc.com> wrote: > Hi. > Trying to set up

Re: FLATTEN doesn't quite give me what I need

Have you tried using the flatten query as a sub-query? Like: SELECT x.`household`, x.`minutes`, x.`LDItem`.`upc`, x.`LDItem`.`item_flags` from (SELECT household, minutes, FLATTEN(T.lditem_array.LDItem) as `LDItem` FROM `dfs`.`root`.`./S/Beddo/spark/pig-xpdb` T limit 8) as x; ​ Chris Matta

Re: NullPointers in type conversions

it might only apply > with varchar. We were primarily focused on issues with lots of case > statements when querying text files when it was added. > > - Jason > > On Wed, Sep 23, 2015 at 1:49 PM, Christopher Matta <cma...@mapr.com> > wrote: > > > This system settin

Re: Querying local filesystem directory: How do I access the filename?

Seems like this was brought up in DRILL-3425 and identified as not something that was in the design spec. Chris Matta cma...@mapr.com 215-701-3146 On Thu, Sep 24, 2015 at 2:57 PM, Tim Harper wrote: > So, if the files are

Re: NullPointers in type conversions

-3146 On Tue, Sep 22, 2015 at 2:33 PM, Christopher Matta <cma...@mapr.com> wrote: > Was the result I saw expected? Seems like I got back an empty string and > not a NULL. > > Chris Matta > cma...@mapr.com > 215-701-3146 > > On Tue, Sep 22, 2015 at 1:48 PM, Sudhees

Re: NullPointers in type conversions

ate a sub-task for improving the experience right now > to at least tell users when they set it at the session level that it is not > allowed. > > > > On Wed, Sep 23, 2015 at 6:01 AM, Christopher Matta <cma...@mapr.com> > wrote: > > > Just bumping this, I’m sure o

Re: NullPointers in type conversions

n provide will be useful the dev who > picks up the JIRA. > > On Thu, Sep 10, 2015 at 12:49 PM, Christopher Matta <cma...@mapr.com> > wrote: > > > Does changing the query to something like this work?: > > > > SELECT CAST(CASE WHEN table.

Re: NullPointers in type conversions

Does changing the query to something like this work?: SELECT CAST(CASE WHEN table.cf.myColumn IS NOT NULL THEN table.cf.myColumn ELSE NULL END as INTEGER) ... ​ Chris Matta cma...@mapr.com 215-701-3146 On Thu, Sep 10, 2015 at 12:49 PM, Nathaniel Auvil wrote: > i

Support for Hive CHAR type

It doesn’t look like Drill 1.1.0 supports the Hive CHAR type? In Hive: create table development.foo ( bad CHAR(10) ); And then in sqlline: > use `hive.development`; > select * from foo; Error: PARSE ERROR: Unsupported Hive data type CHAR. Following Hive data types are supported in Drill

Re: Support for Hive CHAR type

regard to using Hive don't > jump out at you. We need to spell it out using a Hive example, right? > > Kristine Hahn > Sr. Technical Writer > 415-497-8107 @krishahn skype:krishahn > > > On Fri, Sep 4, 2015 at 7:20 AM, Christopher Matta <cma...@mapr.com > <javascript:;

Re: xml files with Drill

People have been asking for this functionality since Drill existed, if it were as straightforward as everyone says I'd think we'd have it working by now. On Friday, September 4, 2015, Jim Scott wrote: > Drill does not support XML. > > I have talked with folks who have

Re: Support for Hive CHAR type

According to this page: http://kristinehahn.github.io/drill/docs/hive-to-drill-data-type-mapping/ it looks like Drill should convert CHAR to VARCHAR, but it's not. On Friday, September 4, 2015, Christopher Matta <cma...@mapr.com> wrote: > Does this mean there aren't plans to support

Re: Rrounding timestamps to nearest period interval

I did this last week with a query on the Drill profiles, this query will break down the number of queries in 5 minute increments: select x.`timestamp`, x.`user`, count(1) from ( select t.`user`, to_timestamp(((cast(t.`start` as bigint)/1000) - MOD((cast(t.`start` as bigint)/1000),

Re: Querying parquet files

You might also want to check out the new partitioned Parquet creation that was launched with 1.1.0: https://drill.apache.org/docs/partition-by-clause/ This would increase your read speed if your queries tend to use predicates. Chris Matta cma...@mapr.com 215-701-3146 On Tue, Jul 7, 2015 at

Re: Querying Drill profiles (JSON)

It would seem that user is a protected word that Drill uses to return your username. Try specifying user without backticks on any dataset: 0: jdbc:drill:zk=sen11:5181,sen12:5181 select user from sys.version . . . . . . . . . . . . . . . . . . . ; +-+ | user | +-+ | cmatta |

Re: JSON/Join/Dynamic schema : java.lang.IllegalStateException: Failure while reading vector.

Just to clarify, you run the *exact same query* once and it works, then you remove say the “cool” field from orders/222.json and put it in orders/111.json and the next time the same query returns that error? ​ Chris Matta cma...@mapr.com 215-701-3146 On Mon, Jun 22, 2015 at 9:59 AM, Tugdual

Re: JSON/Join/Dynamic schema : java.lang.IllegalStateException: Failure while reading vector.

Matta cma...@mapr.com 215-701-3146 On Mon, Jun 22, 2015 at 10:13 AM, Tugdual Grall tugd...@gmail.com wrote: Yes. On Mon, Jun 22, 2015 at 4:12 PM, Christopher Matta cma...@mapr.com wrote: Just to clarify, you run the *exact same query* once and it works, then you remove say the “cool” field

Re: Exception thrown when float field has integer precedent

Wait, what’s wrong with doing it this way? 0: jdbc:drill:zk=sen11:5181,sen12:5181 select cast(`float` as float), cast (`int` as int) from `floatint.json`; +-+-+ | EXPR$0 | EXPR$1 | +-+-+ | 1.0 | 1 | | 0.1 | 1 | +-+-+ ​ Chris

Re: Query Failed: An Error Occurred

Yea, leave the default string out when querying the hive default configuration. ​ Chris Matta cma...@mapr.com 215-701-3146 On Wed, Jun 17, 2015 at 9:33 AM, Leon Clayton lclay...@maprtech.com wrote: Just done this on the sandbox and it works fine. 0: jdbc:drill: select count(1) from

Re: timestamp string to epoch time

to_timestamp(1432912733), unix_timestamp(to_timestamp(1432912733)) from `sys`.`version`; Error: SYSTEM ERROR: java.lang.IllegalArgumentException: Invalid format: 2015-05-29 15:18:53.000 is malformed at .000” —Andries On Jun 15, 2015, at 7:18 AM, Christopher Matta cma...@mapr.com wrote

timestamp string to epoch time

Is there a way to convert a timestamp string to unix time? Chris Matta cma...@mapr.com 215-701-3146

Adding an interval column to a date

Just to illustrate, I have this data: 0: jdbc:drill:zk=sen11:5181,sen12:5181 select * from `datemath.json`; +--+-+ | datetime | offset | +--+-+ | 2015-06-11 17:12:20 | 5 | +--+-+ 1 row selected

Re: from_unixtime in drill explorer/ODBC

The function from_unixtime() is actually a Hive UDF and drill doesn’t yet know how to deal with the 2 extra bytes returned from it. It’s better to use the Drill function to_timestamp(): select to_timestamp(1432912733) from `sys`.`version` Chris mattacma...@mapr.com 215-701-3146 On Tue, Jun 9,

Re: Drill authentication with pyodbc

PM, Christopher Matta cma...@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 cma...@mapr.com 215-701-3146 On Mon, Jun 8, 2015 at 10:24 PM, Matt

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 =

Re: Drill authentication with pyodbc

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

convert unix timestamp

. Technical Writer 415-497-8107 @krishahn On Fri, May 29, 2015 at 11:40 AM, Christopher Matta cma...@mapr.com javascript:_e(%7B%7D,'cvml','cma...@mapr.com'); wrote: I need to be able to convert a unix timestamp to a drill timestamp. I can use the from_unixtime function return what seems like

Drill and Spark integration

Spark integration with Drill is mentioned in this http://drill.apache.org/blog/2014/12/16/whats-coming-in-2015/ blog post, however I can’t find a JIRA for this feature on either the Drill or Spark trackers. What’s the status on this? Is there a timeframe? Is anyone working on it? --Chris ​

Convert UTC to specific timezone?

I have a column that’s UTC, how can I convert this column to a specific timezone? select to_timestamp(columns[13], '-MM-dd HH:mm:ss') as `datetime_utc` from `weather/2012` limit 10; +--+ | datetime_utc | +--+ | 2012-02-01 05:54:00.0 | | 2012-02-01 06:54:00.0 | |

Trying to wrap my head around using WHERE with flatten

I’m using the yelp academic data set https://www.yelp.com/academic_dataset for my testing purposes. Say I want to list all businesses in the “Pets” category, this query doesn’t work: 0: jdbc:drill:zk=sen11:5181,sen12:5181 select `name`, flatten(`categories`) from

Re: Drill Adjunct Data Warehouse

The potential for a JDBC storage plugin has come up in discussions a lot lately and would be a very positive addition to the project. I would love to know if there's been any work on this, or if not how something like this could get bootstrapped. Chris Matta cma...@mapr.com 215-701-3146 On Fri,

Re: Filter out empty arrays in JSON

, Jan 21, 2015 at 1:57 PM, Christopher Matta cma...@mapr.com wrote: Trying that locally did not work for me (drill 0.7.0): 0: jdbc:drill:zk=local select `id`, `name`, `members` from `Downloads/test.json` where repeated_count(`members`) 0; Query failed: Query stopped., Failure

Re: COUNT DISTINCT with HAVING causing a failure

COUNT(distinct column2) FROM HAVING COUNT(distinct column2) 0 Aman On Wed, Dec 10, 2014 at 12:29 PM, Christopher Matta cma...@mapr.com wrote: Tableau is using the following query to get the distinct count of a measure: SELECT COUNT(DISTINCT `custview`.`age`) AS `ctd_age_ok