Hi, You are right, partition pruning works using simple queries (I will adjust our queries accordingly):
- select count(*) from dfs.tmp.`/analytics/processed/test/events` as t where dir0 = '2014'; I tried to run the following query: - select count(*) 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,'-')) and dir1 = dirInRange(cast('2015-04-10' as timestamp),cast('2015-07-11' as timestamp),COALESCE(dir0,'-'),COALESCE(dir1,'-')); This later query uses a UDF (dirInRange) that operates solely on static+directory information (see my earlier post on the same subject) but that does not work. Using a non-static-value for directory evaluation/planning does not work and can be quite limiting since periods can, for example, overlap years and months and then filters on dir0/dir1/dir2 combinations can become quite verbose (but works :). I understand the limitation and apologize for making a false claim but it would be great if partition pruning would work with functions like these. Best regards, -Stefan On Tue, Aug 4, 2015 at 4:56 AM, rahul challapalli < challapallira...@gmail.com> wrote: > As Jinfeng mentioned, directory based partition pruning should work. You > might also be interested in DRILL-3333 > <https://issues.apache.org/jira/browse/DRILL-3333> which allows you to > auto > partition data when using CTAS. > > - Rahul > > On Mon, Aug 3, 2015 at 5:03 PM, Jinfeng Ni <jinfengn...@gmail.com> wrote: > > > For the issues about partition pruning over directories, could you please > > provide more detail information? Drill should do partition pruning based > on > > directory. If it does not work the way you want, probably there is a bug > > in the code. We would appreciate if you can provide more detail, so that > > we could re-produce the problem and get it fix asap. > > > > Regards, > > > > Jinfeng > > > > > > On Mon, Aug 3, 2015 at 4:44 PM, Stefán Baxter <ste...@activitystream.com > > > > wrote: > > > > > Hi, > > > > > > I have been meaning to write a blog post regarding our Drill > experiments > > > but I though I might share some thoughts here first. > > > Hopefully some newbie can benefit from this and perhaps it sheds some > > light > > > on what drives newcomers in this community (or at least some part of > > them). > > > > > > A bit of a back story. I work for a small startup that has been > > developing > > > it's application for *two long* but we hope to lunch our product in > > > Q1/2016. > > > We have been using Druid as a real-time analytic store and, even though > > > Druid does extremely well what it does, we are now forced to replace it > > to > > > allow for functionality that we can not be without. > > > > > > We started to evaluate several solutions and we are now left with a > > handful > > > that we like. We have not had the time to evaluate all of them to > > extremes > > > but we feel comfortable with the general approach we have taken. > > > > > > There were many things for us to consider and I want to run through > them > > > here to seek advice from those here that know better. (I apologize for > > the > > > long read) > > > > > > *Initial questions we had:* > > > > > > - Do we want to operate our own (distributed) file system or rely on > > > something like S3? > > > - Are there other options? > > > > > > - Do we favor "bringing the computation to the data" or "bring the > > data > > > to the computation"? > > > - For us Drill+S3 is a "bring data to the computation" solution > while > > > Hadoop+Hive+MapReduce is a "bringing the computation to the data" > > > solution > > > > > > - How can we effectively merge streaming data with historical data > and > > > process both at the same time? > > > - Something that Druid does very well > > > > > > - What is the best way to merge data from different sources in a > > > polyglot data / format / sources environment? > > > > > > - How can we minimize the effect of large number of small tenants? > > > - Warming up tenant specific indexes in Elastic Search can, for > > example, > > > be costly (What a great solution though) > > > > > > - How can we evict long-tail data from fast storage and gradually > move > > > it into deep storage over time ? > > > - ... while avoiding a lengthy warm-up period > > > - ... while using deep storage also as backup even for fresh data > > > > > > - Can we cache aggregates or would we just be building a cache that > > > next-to-nothing would ever hit? > > > - aggregation for days, weeks, months with all the mostly used spins > > > - historical data does not change here so one level of complexity is > > > absent > > > > > > - Can we achieve sub-second results dealing with our tenant specific > > > data sets > > > - We will never be FB/Twitter but we still got some sizable chunks > of > > > data :) > > > > > > - Is there a stack available that already works like this? > > > - MapR / BDAS etc. etc. > > > > > > - Can we build this using "pure" Apache components? > > > - We would like to have the option of going without > > support/license-fees > > > but we also like the option of professional services with SLAs and > > paid > > > subscriptions > > > > > > - Is this all a premature optimization? > > > - aka. "let's just use Elastic Search for now" > > > > > > > > > *Storage format* > > > > > > We decided early on that the we liked Parquet < > > https://parquet.apache.org/ > > > > > > > enough to commit to it for our processed/historical data. > > > We know that ORC <https://orc.apache.org/> is also great but the we > > > believe > > > that Parquet will, with its support for nested structures, broad > appeal > > > and interoperability become a de-facto standard before long. > > > (We really like ORC as well :) ) > > > > > > Parquet has so many things going for it so I jump to what we see as > > current > > > cons: > > > > > > - Parquet is does not compress as well as ORC > > > - Seems to makes up for it in retrieval speed ( > > > > > > > > > https://developer.ibm.com/hadoop/blog/2014/09/19/big-sql-3-0-file-formats-usage-performance/ > > > ) > > > > > > - Parquet is still missing some things that can make it even better > > for > > > analytics > > > - Like: Bloom filters < > > https://issues.apache.org/jira/browse/PARQUET-41 > > > >, > > > HyperLogLog <https://issues.apache.org/jira/browse/PARQUET-42> > > > and indexing/sorting > > > > > > - Hive has some problems dealing with data in nested structures in > > > Parquet files (but that may have changed) > > > > > > - It's ineffective to gradually add data to parquet files (or so we > > > believe) so streaming data should be batched into Parquet files > > > > > > Our streaming data is mixed schema (partially schemed and partially > > schema > > > less) so using Avro <https://avro.apache.org/> or Protobuff > > > <https://developers.google.com/protocol-buffers/> is doable but a > tricky > > > while JSON is very doable but freakishly verbose. > > > Drill will, for example, allow us to join JSON data with Parquet data > and > > > in our tests we have been merging log information, in JSON format, with > > > historical/older data stored in Parqet. > > > Querying log files like this is sub-optimal, even though they are > stored > > on > > > PCIE-flash, so we still look for viable alternatives. > > > > > > Worst case scenario for us is to continue to work with this log > approach > > > but then using Avro. > > > > > > Relevant Drill points: > > > > > > - + Drill knows all the file formats we like to use (and then some) > > > - - Drill still misses RDBMs access (via JDBC) but that will show > up > > > soon enough (for us) > > > > > > > > > *Query engine* > > > > > > We like SQL. > > > > > > It's the key to so many systems, tools and resources that selecting a > > > proprietary query language is not an option (any more). > > > The query engines we know (of) in this space are: > > > > > > - Presto <https://prestodb.io/> > > > - Apache Drill <https://drill.apache.org/> > > > - Apache Spark <http://spark.apache.org/> > > > > > > They all have in common the ability to run distributed queries and > merge > > > data from different sources using different format, which is great! > > > Spark does, in addition to this, offer a range of capabilities > > > (ML/Graph/etc.) that are appealing to us. > > > > > > We have not decided yet what to do but Drill is very appealing to us > and > > we > > > have it running in the "production" system used for our dev. partners. > > > > > > Relevant Drill points: > > > > > > - + Drill does SQL well and is getting better at dealing with > > > evolving/dirty schema (aka. JSON curve balls) > > > - + Drill is getting better and better in window functions and > various > > > aggregations > > > - + Drill can be accessed via JDBC/ODBC and it has a Spark > connection > > as > > > well > > > - + Drill does User Defined Functions (Could be a bit more > accessible > > > but hey, they are there!) > > > > > > *Storage:* > > > > > > It's very appealing to use Hive <https://hive.apache.org/> and > > Hadoop/HDFS > > > <https://hadoop.apache.org/> to store our data and Hive can point to > > data > > > in S3 as well as in other storage locations. > > > Many solutions know how to use the Hive catalog and some optimization > > seems > > > to be available the utilizes it. > > > > > > Still we are not convinced that we want to build a hadoop > > > <https://hadoop.apache.org/> cluster (yeah, it's mostly prejudice) > > > We don't want to use S3 directly or be forced to live in the EC2 > > ecosystem > > > for it to make any operation sense. (yeah, mostly we are cheep) > > > > > > Ideally we would like something like this: > > > > > > - Use S3 (or any other cost effective alternative) as deep storage > > > > > > - Cache new and in-demand-data locally to minimize the S3 traffic as > > > much as possible (Large tired cache: t1:Flash, t2:SSD and t3:HDD) > > > > > > - We would like to use a file system that plays nice with the > Parquet > > > file format and dos the scans/reads required to effectively work > with > > > the > > > columnar nature of it. > > > we are still not sure if HDFS is the ideal match for parquet from > this > > > perspective but we think that both Gluster <http://www.gluster.org/ > > > > > and > > > Ceph <http://ceph.com/> could be (even though Ceph is a Blob/Object > > > store like S3 underneath) > > > > > > Relevant Drill Points (for file system based operations): > > > > > > - - All Drillbits (nodes) require access to the same data for > > > distribution/planning to work ("bring the data to the computation") > > > planner that knows about "single-node-data" and can plan for it > would > > be > > > ideal for alternatives like the one we have in mind > > > > > > - - Partition pruning does not work with directories. > > > Partitioning data based on time span into a sub-directory structure > > has > > > no benefits (Drill goes through them all, always) > > > > > > We were quite optimistic when we came across Tachyon > > > <http://tachyon-project.org/> and got it to work with Drill (see > earlier > > > email). > > > It seemed like the ideal bridge between Drill and S3 that would allow > us > > to > > > do multi-tiered caching and smart sharing of content between Drillbits > > > (nodes) without fetching it multiple times from S3. > > > But Tachyon currently has a single drawback that we can not accept. It > > > stored all files in S3 in a proprietary way that does not reflect the > > "file > > > system" that it fronts. > > > Also; Accessing Tachyon seems to be a API only exercise and NFS access, > > for > > > example, does not exist. (In that way Tachyon is wicket fast and slow > > (as > > > in dim) at the same time) > > > > > > *To oversimplify - the choice is now between the beaten path > > > (MapR/BDAS/etc.) or ... * > > > > > > *A)* the 12" mix - feat. file system only: > > > > > > - Query engine: *Drill* > > > - Streaming/log data format: *Avro* / *JSON* > > > - Historical Data Format: *Parquet* > > > - Extra dimensions / enrichment information: *RDBM* / *Cassandra* / > > > *Solr* / *More* > > > - File System: *S3 + Tachyon* / *Gluster* / *Ceph* > > > > > > *B)* the 6" mix - feat. Johnny Cash: > > > > > > - Query engine: *Drill / Presto / Spark / Spark + Drill* > > > - Streaming/log data format: *Avro* / *JSON* > > > - Historical Data Format: *Parquet* > > > - Extra dimensions / enrichment information: *RDBM* / *Cassandra* / > > > *Solr* / *More* > > > - File System: > > > *Hadoop + Hive * > > > > > > > > > *General Drill observations that are relevant to us (partly > redundant):* > > > > > > - REST API does only return JSON > > > - Uncompressed and incorrectly typed (count(*) returns a number in > > > quotes) > > > - the result set is always known and returning Avro/Protobuf could > be > > > beneficial > > > > > > - Schema discovery is still a bit immature / fragile / temperamental > > > - This is understandable and will change > > > > > > - Partition pruning does not work for directories > > > - See point above (Storage) > > > > > > - Schema information is not available for data in the file system > > > - This will likely change (at least the schema is created on query > > time) > > > > > > - Metadata queries are not available (data discovery queries) > > > - Discovering fields, cardinality and composition for data that > meets > > > certain criteria > > > - This is essential in building good UI for tenant specific data > (data > > > that varies between tenants) > > > > > > I will, in the near future, try to involve this into a more informative > > > blog post and share it for those starting down this path. > > > > > > There are is so much more going on in/around this space (Tez > > > <https://tez.apache.org/>, Flink <https://flink.apache.org/> ...) > > > > > > All input is welcomed. > > > > > > Best regards, > > > -Stefan Baxter > > > > > > PS. I'm a bit dyslexic and sometimes the little bastards sneak though. > > Just > > > laugh and shake your head when you come across them :) > > > > > >