Hello Daniel, Your understanding is perfectly correct. Drill does support translation of SQL to native SQL provided rules exists for that. One could write a push down rule for handling LIMIT based on DB type and by that get it pushed down. (Not being a core developer so I don’t want to describe how, since I am bound to get it wrong :)) So check with the team if LIMIT should be pushed down based on existing rules (my guess is no), and possibly open a JIRA for its inclusion. The simplest way to simulate the behavior is to do a limited view in the DB which is basically selecting with limit and use that as your source table. Not pretty but very straightforward.
Best regards, Magnus > 3 dec 2015 kl. 11:39 skrev Daniel Garcia <[email protected]>: > > Hello Magnus, > > Thanks for your answer. > > I understand the issue regarding non-standard SQL for LIMIT and OFFSET > clauses. But I thought that apache drill, somehow, was able to translate this > into native SQL (In MySQL case, the LIMIT clause is exactly the same). > > Anyway, any idea to achieve the same?? The table is very big and I cannot > afford to retrieve all rows. > > Regards, > > > Daniel García > Continuous Improvement Team > > > > Albasanz 12 / 4th Floor / 28037 Madrid > > T +34 917 542 966 > > E [email protected] <mailto:[email protected]> > W www.eurotaxglass.com <http://www.eurotaxglass.com/> > > -----Mensaje original----- > De: Magnus Pierre [mailto:[email protected] <mailto:[email protected]>] > Enviado el: jueves, 3 de diciembre de 2015 10:50 > Para: [email protected] <mailto:[email protected]> > Asunto: Re: LIMIT clause not being sent to MySQL server > > Hello Daniel, > > Filters and joins are pushed down i.e. filters on column data. Limit is a > special keyword not always supported by a database (different syntaxes for > quite many databases) and as such I don’t think the push down rules works for > that. > > Regards, > Magnus > > > >> 3 dec 2015 kl. 10:00 skrev Daniel Garcia <[email protected]>: >> >> Hi, >> >> I’m trying to obtain just one row from a table stored in MySQL by using the >> following query: >> >> select * from my.schema.table limit 1; >> >> But I’m not getting just one row but all instead. I mean: apache drill only >> shows one line, but it retrieves all of them from the source. >> >> Looking at the query being executed in MySQL server I can see that the >> “limit 1” part is not sent. >> Can apache drill filter data in the source rather than extract all rows in >> memory and then filter them? >> >> I’m using: >> jdk build 1.7.0_79-b15 >> apache drill 1.3 >> mysql-connector-java 5.1.37 >> windows 10 >> >> Regards, >> >> Daniel García >> Continuous Improvement Team >> >> >> >> Albasanz 12 / 4th Floor / 28037 Madrid >> >> T +34 917 542 966 >> >> E [email protected] <mailto:[email protected]> >> <mailto:[email protected] >> <mailto:[email protected]>> >> W www.eurotaxglass.com <http://www.eurotaxglass.com/> >> <http://www.eurotaxglass.com/ <http://www.eurotaxglass.com/>> >> >> >> >> Disclaimer >> >> The information contained in this communication from the sender is >> confidential. It is intended solely for use by the recipient and others >> authorized to receive it. If you are not the recipient, you are hereby >> notified that any disclosure, copying, distribution or taking action in >> relation of the contents of this information is strictly prohibited and may >> be unlawful. >> >> This email has been scanned for viruses and malware, and may have been >> automatically archived by Mimecast Ltd, an innovator in Software as a >> Service (SaaS) for business. Providing a safer and more useful place for >> your human generated data. Specializing in; Security, archiving and >> compliance. To find out more Click Here <http://www.mimecast.com/products/ >> <http://www.mimecast.com/products/>>. >> > > Disclaimer > > The information contained in this communication from the sender is > confidential. It is intended solely for use by the recipient and others > authorized to receive it. If you are not the recipient, you are hereby > notified that any disclosure, copying, distribution or taking action in > relation of the contents of this information is strictly prohibited and may > be unlawful. > > This email has been scanned for viruses and malware, and may have been > automatically archived by Mimecast Ltd, an innovator in Software as a Service > (SaaS) for business. Providing a safer and more useful place for your human > generated data. Specializing in; Security, archiving and compliance. To find > out more visit the Mimecast website.
