I actually solved this with advice from Leon Clayton at MapR by creating a new workspace for views alone and keeping it separate from the data. This obviously makes sense but it would be nice if Drill could be aware of any created views (to be able to ignore them) in case someone accidently creates the view in the same area as their data causing erroneous results.
-----Original Message----- From: England, Michael (IT/UK) Sent: 22 September 2015 10:14 To: [email protected] Subject: RE: Drill view schema returned in results set I have noticed that Drill keeps the view definition in the same directory as the data. Should this not be ignored by default instead of having to specify what files to query? -----Original Message----- From: England, Michael (IT/UK) Sent: 22 September 2015 09:53 To: [email protected] Subject: Drill view schema returned in results set Hi, I was trying to run a SUM() function on a view but was getting the following error: ERROR [HY000] [MapR][Drill] (1040) Drill failed to execute the query: select sum(x) from `mike`.`view`.`test_view ` [30027]Query execution error. Details:[ SYSTEM ERROR: CompileException: Line 33, Column 177: Unknown variable or type "logger" Fragment 0:0 [Error Id: 68c7c5b8-8875-4834-8977-c514a08ca8b0 on x.mydomain.com:31010] ] I ran a distinct on the column to check all of the data types in the column were an integer but saw that one row contained no data. I orginally thought that it was my data causing the issue, but on further analysis I saw the view schema being returned in the first column which seems to be causing the issue: select * from `mike`.`view`.`myview` where isnumeric(x) = 0 { "name" : "test_view ", "sql" : "SELECT `columns`[0] AS `x`, `columns`[1] AS `x1`, `columns`[2] AS `x2`, `columns`[3] AS `x3`, `columns`[4] AS `x4`, `columns`[5] AS `x5`, `columns`[6] AS `severity`, `columns`[7] AS `x6`\nFROM `mike`.`view`.`.`", "fields" : [ { "name" : "x", "type" : "ANY", "isNullable" : true }, { "name" : "x1", "type" : "ANY", "isNullable" : true }, { "name" : "x2", "type" : "ANY", "isNullable" : true }, { "name" : "x3", "type" : "ANY", "isNullable" : true }, { "name" : "x4", "type" : "ANY", "isNullable" : true }, { "name" : "x5", "type" : "ANY", "isNullable" : true }, { "name" : "x6", "type" : "ANY", "isNullable" : true }, { "name" : "x7", "type" : "ANY", "isNullable" : true } ], "workspaceSchemaPath" : [ ] } Is this expected behaviour or does it look like a bug. Has anyone come across this before? Thanks, Michael This e-mail (including any attachments) is private and confidential, may contain proprietary or privileged information and is intended for the named recipient(s) only. Unintended recipients are strictly prohibited from taking action on the basis of information in this e-mail and must contact the sender immediately, delete this e-mail (and all attachments) and destroy any hard copies. Nomura will not accept responsibility or liability for the accuracy or completeness of, or the presence of any virus or disabling code in, this e-mail. If verification is sought please request a hard copy. Any reference to the terms of executed transactions should be treated as preliminary only and subject to formal written confirmation by Nomura. Nomura reserves the right to retain, monitor and intercept e-mail communications through its networks (subject to and in accordance with applicable laws). No confidentiality or privilege is waived or lost by Nomura by any mistransmission of this e-mail. Any reference to "Nomura" is a reference to any entity in the Nomura Holdings, Inc. group. Please read our Electronic Communications Legal Notice which forms part of this e-mail: http://www.Nomura.com/email_disclaimer.htm This e-mail (including any attachments) is private and confidential, may contain proprietary or privileged information and is intended for the named recipient(s) only. Unintended recipients are strictly prohibited from taking action on the basis of information in this e-mail and must contact the sender immediately, delete this e-mail (and all attachments) and destroy any hard copies. Nomura will not accept responsibility or liability for the accuracy or completeness of, or the presence of any virus or disabling code in, this e-mail. If verification is sought please request a hard copy. Any reference to the terms of executed transactions should be treated as preliminary only and subject to formal written confirmation by Nomura. Nomura reserves the right to retain, monitor and intercept e-mail communications through its networks (subject to and in accordance with applicable laws). No confidentiality or privilege is waived or lost by Nomura by any mistransmission of this e-mail. Any reference to "Nomura" is a reference to any entity in the Nomura Holdings, Inc. group. Please read our Electronic Communications Legal Notice which forms part of this e-mail: http://www.Nomura.com/email_disclaimer.htm This e-mail (including any attachments) is private and confidential, may contain proprietary or privileged information and is intended for the named recipient(s) only. Unintended recipients are strictly prohibited from taking action on the basis of information in this e-mail and must contact the sender immediately, delete this e-mail (and all attachments) and destroy any hard copies. Nomura will not accept responsibility or liability for the accuracy or completeness of, or the presence of any virus or disabling code in, this e-mail. If verification is sought please request a hard copy. Any reference to the terms of executed transactions should be treated as preliminary only and subject to formal written confirmation by Nomura. Nomura reserves the right to retain, monitor and intercept e-mail communications through its networks (subject to and in accordance with applicable laws). No confidentiality or privilege is waived or lost by Nomura by any mistransmission of this e-mail. Any reference to "Nomura" is a reference to any entity in the Nomura Holdings, Inc. group. Please read our Electronic Communications Legal Notice which forms part of this e-mail: http://www.Nomura.com/email_disclaimer.htm
