With verbose mode on the following is the error message. I guess this confirms it is a date conversion but there isn't any help as to which file or which expression (in this statement there is only one TO_DATE function so it is obvious).
org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: IllegalArgumentException: Invalid format: " " Fragment 1:0 [Error Id: 22129e51-acbd-41f2-84f5-721541d9843b on ubuntu:31010] (java.lang.IllegalArgumentException) Invalid format: " " org.joda.time.format.DateTimeFormatter.parseDateTime():899 org.joda.time.DateMidnight.parse():152 org.apache.drill.exec.test.generated.ProjectorGen367.doEval():76 org.apache.drill.exec.test.generated.ProjectorGen367.projectRecords():67 org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.doWork():199 org.apache.drill.exec.record.AbstractSingleRecordBatch.innerNext():93 org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext():135 org.apache.drill.exec.record.AbstractRecordBatch.next():162 org.apache.drill.exec.record.AbstractRecordBatch.next():119 org.apache.drill.exec.record.AbstractRecordBatch.next():109 org.apache.drill.exec.record.AbstractSingleRecordBatch.innerNext():51 org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext():135 org.apache.drill.exec.record.AbstractRecordBatch.next():162 org.apache.drill.exec.record.AbstractRecordBatch.next():119 org.apache.drill.exec.record.AbstractRecordBatch.next():109 org.apache.drill.exec.physical.impl.WriterRecordBatch.innerNext():91 org.apache.drill.exec.record.AbstractRecordBatch.next():162 org.apache.drill.exec.physical.impl.BaseRootExec.next():104 org.apache.drill.exec.physical.impl.SingleSenderCreator$SingleSenderRootExec.innerNext():92 org.apache.drill.exec.physical.impl.BaseRootExec.next():94 org.apache.drill.exec.work.fragment.FragmentExecutor$1.run():232 org.apache.drill.exec.work.fragment.FragmentExecutor$1.run():226 java.security.AccessController.doPrivileged():-2 javax.security.auth.Subject.doAs():422 org.apache.hadoop.security.UserGroupInformation.doAs():1657 org.apache.drill.exec.work.fragment.FragmentExecutor.run():226 org.apache.drill.common.SelfCleaningRunnable.run():38 java.util.concurrent.ThreadPoolExecutor.runWorker():1149 java.util.concurrent.ThreadPoolExecutor$Worker.run():624 java.lang.Thread.run():748 Dan Holmes | Revenue Analytics, Inc. Direct: 770.859.1255 www.revenueanalytics.com -----Original Message----- From: Dan Holmes [mailto:[email protected]] Sent: Thursday, July 27, 2017 9:11 AM To: [email protected] Subject: How much more information can error messages provide? I am querying a 6GB data set in a directory. There are 34 files in the directory. Here is the statement. CREATE TABLE dfs.root.`twm/sales_p` AS SELECT to_date(columns[0], 'MM/dd/yy') transaction_date , CAST(columns[1] as INT) store_key , CAST(columns[2] AS BIGINT) item_code , columns[3] position_name , CAST(CASE WHEN columns[4] = 'NULL' THEN 0 ELSE columns[4] END as INT) quantity_per_sku , CAST(columns[5] as INT) sales_quantity, CAST(columns[6] as float) sales_dollars, columns[7] channel FROM dfs.root.`sales` I get the following error. I was able to determine that columns[2] needed to be a BIGINT based on an error and that columns[4] sometimes had the word "NULL" in it. In the case of NULL I had to run a statement across every column I was converting to INT (col1 = NULL or col2 = NULL ...) to figure out which one it was. Now I have a similar problem with the TO_DATE() function. Can the error message include the filename and record number? Or at least the value of what it found and which expression failed? Those kinds of clues would go a long way towards making this type of detective work significantly faster. The larger the dataset the faster it would be. org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: IllegalArgumentException: Invalid format: " " Fragment 1:0 [Error Id: 3557e893-e848-4503-bf6a-73110fbd6207 on ubuntu:31010] v1.10 Dan Holmes | Architect | Revenue Analytics, Inc. 300 Galleria Parkway, Suite 1900 | Atlanta, Georgia 30339 Direct: 770.859.1255 Cell: 404.617.3444 www.revenueanalytics.com<https://webmail.revenueanalytics.com/owa/redir.aspx?SURL=RqmyOJRm3r383jV2nPQLyg9BvjWZqMX4-tL3BHj81WfaslMWau_SCGgAdAB0AHAAOgAvAC8AdwB3AHcALgByAGUAdgBlAG4AdQBlAGEAbgBhAGwAeQB0AGkAYwBzAC4AYwBvAG0A&URL=http%3a%2f%2fwww.revenueanalytics.com> LinkedIn<https://webmail.revenueanalytics.com/owa/redir.aspx?SURL=SrcaeiXxVTCDhl49ibCO7CHhTsNynunc_8gSjHDaikXaslMWau_SCGgAdAB0AHAAcwA6AC8ALwB3AHcAdwAuAGwAaQBuAGsAZQBkAGkAbgAuAGMAbwBtAC8AYwBvAG0AcABhAG4AeQAvAHIAZQB2AGUAbgB1AGUALQBhAG4AYQBsAHkAdABpAGMAcwAtAGkAbgBjAC0A&URL=https%3a%2f%2fwww.linkedin.com%2fcompany%2frevenue-analytics-inc-> | Twitter<https://webmail.revenueanalytics.com/owa/redir.aspx?SURL=cdePsMV8TCGx8O_Rugbj-maE9C9DVT373vSJwbUc23faslMWau_SCGgAdAB0AHAAcwA6AC8ALwB0AHcAaQB0AHQAZQByAC4AYwBvAG0ALwBSAGUAdgBfAEEAbgBhAGwAeQB0AGkAYwBzAA..&URL=https%3a%2f%2ftwitter.com%2fRev_Analytics>
