Hi Andries Thank you for your suggestion.
I tried creating a view instead of temporary table. And it goes well, you know. Thank you. -- Miura, Masahide > -----Original Message----- > From: Andries Engelbrecht [mailto:aengelbre...@maprtech.com] > Sent: Tuesday, December 22, 2015 1:35 AM > To: user@drill.apache.org > Subject: Re: Join with empty table > > Hi Stefan, > > You bring up a good point in regards to "rules of Drill", some of it is > captured in the Drill doc site and various blogs. Perhaps a good option > for someone to write a book as Drill is a very powerful tool and the > functionality is evolving rapidly. This looks interesting, but seems to > be out on the horizon a bit > http://shop.oreilly.com/product/0636920029021.do > <http://shop.oreilly.com/product/0636920029021.do> > > However in the case of pointing to non existent directories/files it > seems to be a pretty good assumption that the query should fail with a > message that the data source does not exists, vs where you point to an > empty directory/file that does exists. Similar to a RDBMS where you try > to query a non existent table vs querying a table than exists with no > rows in it. > > There are a number of JIRAs to pick up more meta data from file formats, > specifically parquet. However to be fair the file/metadata should exist > first. Pending on the use case it can be a good idea to tie down the data > types/schema with a View, especially if the underlying data structure > is still empty. > > --Andries > > > > On Dec 18, 2015, at 9:36 AM, Stefán Baxter <ste...@activitystream.com> > wrote: > > > > Hi Andries, > > > > Where does it say that the query for an non existing file is > > unions/joins should fail? > > > > I ask because I'm interested in the "basic rules of Drill". > > > > Rgards, > > -Stefán > > > > On Fri, Dec 18, 2015 at 4:37 PM, Andries Engelbrecht < > > aengelbre...@maprtech.com> wrote: > > > >> 1) When the table/file doesn't exist it should fail. > >> > >> 2) If the file is empty Drill is trying to discover a schema, but > >> unable to do so since there are no records. Perhaps try to create a > >> View on these "temp" tables so you can declare dat types used of > >> joins. This way at least you give Drill a data type for the JOIN even > if the file has no data. > >> > >> --Andries > >> > >>> On Dec 17, 2015, at 9:39 PM, <masahide.mi...@brother.co.jp> < > >> masahide.mi...@brother.co.jp> wrote: > >>> > >>> Hi, > >>> > >>> Is any good way to avoid error on join query with empty table? > >>> I've come across 2 types of error as bellow. > >>> > >>> Sample SQL: > >>> select A.*, B.* > >>> from cp.`tpch/nation.parquet` as A > >>> join dfs.tmp.`dummy` as B > >>> on A.n_nationkey = B.n_nationkey > >>> > >>> 1) When the table(file) doesn't exist. > >>> > >>> org.apache.drill.common.exceptions.UserRemoteException: > >>> VALIDATION > >> ERROR > >>> Table 'TABLENAME' not found > >>> > >>> 2) When the table(file) exists, but it has no records. > >>> ex) No recode file is created as csv with header and filled > >> properly header text. > >>> such as: "n_nationkey","iso_code" > >>> > >>> org.apache.drill.common.exceptions.UserRemoteException: > SYSTEM > >> ERROR: DrillRuntimeException: > >>> Join only supports implicit casts between 1. Numeric > >>> data > >> 2. Varchar, Varbinary data 3. Date, Timestamp data Left type: > >> VARCHAR, Right type: INT > >>> > >>> Surely, I know these errors should occur in those case. > >>> Though, we expect null values like RDB-SQL join with empty tables. > >>> > >>> I hope your great idea. > >>> > >>> p.s. : for your information. > >>> Actually, we are going to query to drill in batch process. > >>> And, we predict some tables can be empty and it's ok. > >>> For example, temporary table is so. > >>> We wouldn't like to suspend batch in such situation. > >>> > >>> -- > >>> Miura, Masahide > >>> > >> > >>
