You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Stefán Baxter <st...@activitystream.com> on 2015/07/25 18:18:23 UTC
Continued experiments
Hi,
I'm still working on our evaluation and now focusing on directory based
queries and mixing directory and parquet based partitions.
This is also a continued trip down the UDF rabbit hole :) (some pun
intended)
I continue to come across things that surprise me and I would like to share
them with both the developers, that might want to address some of them, and
other newcomers that might benefit from them.
The UDF code referenced here van be found at (
https://github.com/acmeguy/asdrill) and the documents and the directory
structure used in these examples are included in the tiny attachment.
I will try to keep this as brief as possible.
*What you need to know is:*
- there are 33 records in the 19 files in a mixed directory structure
- see zip for details
- each record contains a date that is valid within that directory
structure
- the dirInRange function is a UDF that takes a date range and directory
information to determine if a directory contains target data for the
provided date range
- see github for details
- the dirInRange function should be able to accept all null values or
missing parameters for everything other than the time range starts parameter
- the dirInRange function returns a string with a number that represents
the number of parameters used (function variant)
- has no other purpose/function at this point
- will return the value of the last dirN paramater that is not null
(dir0, dir1 or dir2)
*Observations*
1. The UDF function (dirInRange) is not called if dir0, dir1 or dir2 are
missing (missing is not the same as null here)
- select occurred_at, dir0 dYear, dir1 dMonth, dir2 dDay from
dfs.tmp.`/analytics/processed/test/events` as t order by occurred_at;
- return 33 records
- select occurred_at, dir0 dYear, dir1 dMonth, dir2 dDay from
dfs.tmp.`/analytics/processed/test/events` as t where
dirInRange(cast('2015-04-10' as timestamp),cast('2015-07-11' as
timestamp),COALESCE(dir0,'-'),COALESCE(dir1,'-'),COALESCE(dir2,'-')) > '0'
order by occurred_at;
- returns 33 record (Coalesce handles the missing values are replaces
them with '-')
- select occurred_at, dir0 dYear, dir1 dMonth, dir2 dDay from
dfs.tmp.`/analytics/processed/test/events` as t where
dirInRange(cast('2015-04-10' as timestamp),cast('2015-07-11' as
timestamp),dir0,dir1,dir2) > '0' order by occurred_at;
- returns 13 records (only those in the deepest directories
where dir0, dir1, dir2 are all set
- select occurred_at, dir0 dYear, dir1 dMonth, dir2 dDay,
dirInRange(cast('2015-04-10' as timestamp),cast('2015-07-11' as
timestamp),dir0,dir1,dir2) inRange from
dfs.tmp.`/analytics/processed/test/events` as t order by occurred_at;
- return 33 records but 20 of the records will have inRange set to null
(the UDF never returns null so it's being ignored completely)
- *Lesson:* It's not enough to use Nullable*Holder in UDF and have all
permutations covered
- Drill will not call the function and fails silently, evaluating the
outcome of the function to null, if any of the dirN parameters are not
initialized
2. System.out.print out is the way to get information from within the
Drillbit
- It would be good to know which Drillbit instance, if many, is
responsible for the println
- I don't know how to get the parent drillbit injected into the UDF
3. If directories have numeric names then Drill starts to insist they are
all numeric (in the where condition) event though dirInRange always returns
a varchar.
- select occurred_at, dir0 dYear, dir1 dMonth, dir2 dDay from
dfs.tmp.`/analytics/processed/test/events` as t where dir0 =
dirInRange(cast('2015-04-10' as timestamp),cast('2015-07-11' as
timestamp),COALESCE(dir0,'-'),COALESCE(dir1,'-'),COALESCE(dir2,'-')) order
by occurred_at;
"2011,2012" is the name of the directory (same happens with directories
("Q1" and "W1" etc.)
java.lang.RuntimeException: java.sql.SQLException: SYSTEM ERROR:
NumberFormatException: 2011,2012
Fragment 0:0
[Error Id: 0c3e1370-ccc5-4288-b6c9-ea0ef4884f1e on localhost:31010]
This seems to fail on the other side where Drill thinks that the outcome
of the dirInRange function is numeric and that the "=" expression is a
numerical one.
- this runs though: select occurred_at, dir0 dYear, dir1 dMonth, dir2
dDay from dfs.tmp.`/analytics/processed/test/events` as t where dir0 =
trim(dirInRange(cast('2015-04-10' as timestamp),cast('2015-07-11' as
timestamp),COALESCE(dir2,'-'),COALESCE(dir2,'-'),COALESCE(dir2,'-'))) order
by occurred_at;
- here the trim() function takes care of making sure that the UDF
returns a string (I think) even though the return type of the UDF is always
a varcharholder.
4. Directories do not fail and all files and all their records are evaluated
- select occurred_at, dir0 dYear, dir1 dMonth, dir2 dDay from
dfs.tmp.`/analytics/processed/test/events` as t where dir0 =
trim(dirInRange(cast('2015-04-10' as timestamp),cast('2015-07-11' as
timestamp),COALESCE(dir2,'-'),COALESCE(dir2,'-'),COALESCE(dir2,'-'))) order
by occurred_at;
- correctly returns no records (return value of dirInRange never matches
dir0)
- This still evaluates* all the records in all the files in all the
directories*
- no partition pruning is available for selecting from directories
- massive performance/optimization gain to be had
5. File name is not available
- It might be good to have dynamic filename variable available (just as
the directory variables)
Point number 4 bugs the living daylights out of me and I hope to share
something constructive on that soon.
There are 3+ Jira items in there somewhere and I will create what I believe
to be correct tasks but please feel free to correct them for me.
Regards,
-Stefan
Re: Continued experiments
Posted by Stefán Baxter <st...@activitystream.com>.
#3 is incorrectly labelled and should be called "3. since it seems unclear
what type UDFs return then Drill will, under some circumstances, assume
arithmetic comparison (=, <>, etc.) rather than string comparison even
though the UDF returns VarChar
On Sat, Jul 25, 2015 at 4:18 PM, Stefán Baxter <st...@activitystream.com>
wrote:
> Hi,
>
> I'm still working on our evaluation and now focusing on directory based
> queries and mixing directory and parquet based partitions.
> This is also a continued trip down the UDF rabbit hole :) (some pun
> intended)
>
> I continue to come across things that surprise me and I would like to
> share them with both the developers, that might want to address some of
> them, and other newcomers that might benefit from them.
>
> The UDF code referenced here van be found at (
> https://github.com/acmeguy/asdrill) and the documents and the directory
> structure used in these examples are included in the tiny attachment.
>
> I will try to keep this as brief as possible.
>
> *What you need to know is:*
>
> - there are 33 records in the 19 files in a mixed directory structure
> - see zip for details
>
> - each record contains a date that is valid within that directory
> structure
>
> - the dirInRange function is a UDF that takes a date range and
> directory information to determine if a directory contains target data for
> the provided date range
> - see github for details
>
> - the dirInRange function should be able to accept all null values or
> missing parameters for everything other than the time range starts parameter
>
> - the dirInRange function returns a string with a number that
> represents the number of parameters used (function variant)
> - has no other purpose/function at this point
> - will return the value of the last dirN paramater that is not null
> (dir0, dir1 or dir2)
>
> *Observations*
>
> 1. The UDF function (dirInRange) is not called if dir0, dir1 or dir2 are
> missing (missing is not the same as null here)
>
> - select occurred_at, dir0 dYear, dir1 dMonth, dir2 dDay from
> dfs.tmp.`/analytics/processed/test/events` as t order by occurred_at;
> - return 33 records
>
> - select occurred_at, dir0 dYear, dir1 dMonth, dir2 dDay from
> dfs.tmp.`/analytics/processed/test/events` as t where
> dirInRange(cast('2015-04-10' as timestamp),cast('2015-07-11' as
> timestamp),COALESCE(dir0,'-'),COALESCE(dir1,'-'),COALESCE(dir2,'-')) > '0'
> order by occurred_at;
> - returns 33 record (Coalesce handles the missing values are replaces
> them with '-')
>
> - select occurred_at, dir0 dYear, dir1 dMonth, dir2 dDay from
> dfs.tmp.`/analytics/processed/test/events` as t where
> dirInRange(cast('2015-04-10' as timestamp),cast('2015-07-11' as
> timestamp),dir0,dir1,dir2) > '0' order by occurred_at;
> - returns 13 records (only those in the deepest directories
> where dir0, dir1, dir2 are all set
>
> - select occurred_at, dir0 dYear, dir1 dMonth, dir2 dDay,
> dirInRange(cast('2015-04-10' as timestamp),cast('2015-07-11' as
> timestamp),dir0,dir1,dir2) inRange from
> dfs.tmp.`/analytics/processed/test/events` as t order by occurred_at;
> - return 33 records but 20 of the records will have inRange set to
> null (the UDF never returns null so it's being ignored completely)
>
> - *Lesson:* It's not enough to use Nullable*Holder in UDF and have all
> permutations covered
> - Drill will not call the function and fails silently, evaluating the
> outcome of the function to null, if any of the dirN parameters are not
> initialized
>
> 2. System.out.print out is the way to get information from within the
> Drillbit
>
> - It would be good to know which Drillbit instance, if many, is
> responsible for the println
> - I don't know how to get the parent drillbit injected into the UDF
>
> 3. If directories have numeric names then Drill starts to insist they are
> all numeric (in the where condition) event though dirInRange always returns
> a varchar.
>
> - select occurred_at, dir0 dYear, dir1 dMonth, dir2 dDay from
> dfs.tmp.`/analytics/processed/test/events` as t where dir0 =
> dirInRange(cast('2015-04-10' as timestamp),cast('2015-07-11' as
> timestamp),COALESCE(dir0,'-'),COALESCE(dir1,'-'),COALESCE(dir2,'-')) order
> by occurred_at;
> "2011,2012" is the name of the directory (same happens with
> directories ("Q1" and "W1" etc.)
> java.lang.RuntimeException: java.sql.SQLException: SYSTEM ERROR:
> NumberFormatException: 2011,2012
> Fragment 0:0
> [Error Id: 0c3e1370-ccc5-4288-b6c9-ea0ef4884f1e on localhost:31010]
>
> This seems to fail on the other side where Drill thinks that the
> outcome of the dirInRange function is numeric and that the "=" expression
> is a numerical one.
>
> - this runs though: select occurred_at, dir0 dYear, dir1 dMonth, dir2
> dDay from dfs.tmp.`/analytics/processed/test/events` as t where dir0 =
> trim(dirInRange(cast('2015-04-10' as timestamp),cast('2015-07-11' as
> timestamp),COALESCE(dir2,'-'),COALESCE(dir2,'-'),COALESCE(dir2,'-'))) order
> by occurred_at;
> - here the trim() function takes care of making sure that the UDF
> returns a string (I think) even though the return type of the UDF is always
> a varcharholder.
>
> 4. Directories do not fail and all files and all their records are
> evaluated
>
> - select occurred_at, dir0 dYear, dir1 dMonth, dir2 dDay from
> dfs.tmp.`/analytics/processed/test/events` as t where dir0 =
> trim(dirInRange(cast('2015-04-10' as timestamp),cast('2015-07-11' as
> timestamp),COALESCE(dir2,'-'),COALESCE(dir2,'-'),COALESCE(dir2,'-'))) order
> by occurred_at;
> - correctly returns no records (return value of dirInRange never
> matches dir0)
>
> - This still evaluates* all the records in all the files in all the
> directories*
> - no partition pruning is available for selecting from directories
> - massive performance/optimization gain to be had
>
> 5. File name is not available
>
> - It might be good to have dynamic filename variable available (just
> as the directory variables)
>
>
> Point number 4 bugs the living daylights out of me and I hope to share
> something constructive on that soon.
>
> There are 3+ Jira items in there somewhere and I will create what I
> believe to be correct tasks but please feel free to correct them for me.
>
> Regards,
> -Stefan
>
>
>