Re: Problem Creating Views In Apache Drill
Hi Gautam I have been able to narrow down the troublesome column to settlement_date. I still see the same error when I run the select query instead. The complete stacktrace of the errors is as follows org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: SchemaChangeException: Failure while trying to materialize incoming schema. Errors: Error in expression at index -1. Error: Missing function implementation: [castTINYINT(VARCHAR-OPTIONAL)]. Full expression: --UNKNOWN EXPRESSION--.. Fragment 0:0 [Error Id: cbff001e-1ffa-43f7-a368-8ff2cbe44e69 on bdnode3.interswitch.com:31010] (org.apache.drill.exec.exception.SchemaChangeException) Failure while trying to materialize incoming schema. Errors: Error in expression at index -1. Error: Missing function implementation: [castTINYINT(VARCHAR-OPTIONAL)]. Full expression: --UNKNOWN EXPRESSION--.. org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.setupNewSchemaFromInput():424 org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.setupNewSchema():498 org.apache.drill.exec.record.AbstractSingleRecordBatch.innerNext():82 org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext():134 org.apache.drill.exec.record.AbstractRecordBatch.next():164 org.apache.drill.exec.physical.impl.BaseRootExec.next():105 org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext():83 org.apache.drill.exec.physical.impl.BaseRootExec.next():95 org.apache.drill.exec.work.fragment.FragmentExecutor$1.run():233 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():1633 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 Many Thanks Peter From: Gautam Parai Sent: Friday, January 11, 2019 9:19 PM To: user@drill.apache.org Subject: Re: Problem Creating Views In Apache Drill Hi Peter, Do you see the same error when you run the query instead? SELECT id, settlement, settlement_type, to_timestamp(to_char(settlement_date, '#')) as settlement_date ,transaction_id, transaction_type, settlement_institution, settlement_institution_bank_domain_id, domain_i,product_group_id, channel_id FROM `dfs`.`***`.`/tbl_settlement` Could you narrow down on the column which is causing this error? Also, can you please set alter session set `exec.errors.verbose` = true and provide the stack trace from the console. Thanks, Gautam On Fri, Jan 11, 2019 at 3:22 AM Peter Edike < peter.ed...@interswitchgroup.com> wrote: > Hello Everyone, > > > I have a set of parquet files which where created as a result of a batch > ETL job (using Sqoop 1). > > > I can query the fields normally without any kind of formatting and all is > well... > > > However, when i try to create a view with the following code > > > CREATE VIEW `dfs.asra`.`vw_tbl_settlement` AS > SELECT id, settlement, settlement_type, > to_timestamp(to_char(settlement_date, '#')) as settlement_date > ,transaction_id, transaction_type, settlement_institution, > settlement_institution_bank_domain_id, domain_i,product_group_id, > channel_id FROM `dfs`.`***`.`/tbl_settlement` > > I end up with the following exception > > org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: > SchemaChangeException: Failure while trying to materialize incoming schema. > Errors: Error in expression at index -1. Error: Missing function > implementation: [castTINYINT(VARCHAR-OPTIONAL)]. Full expression: --UNKNOWN > EXPRESSION--.. Fragment 0:0 [Error Id: 55c13096-085a-4d0c-9eca-5c7bbc2d1b8f > on bdnode3.interswitch.com:31010]? > > Please What am I doing wrong > > Kind Regards > > >
Re: Problem Creating Views In Apache Drill
Hi Khurram I am currently Running Apache Drill 1.13 Many Thanks From: Khurram Faraaz Sent: Friday, January 11, 2019 11:19 PM To: user@drill.apache.org Subject: Re: Problem Creating Views In Apache Drill Hi Peter, What version of Drill are you on ? Thanks, Khurram On Fri, Jan 11, 2019 at 12:20 PM Gautam Parai wrote: > Hi Peter, > > Do you see the same error when you run the query instead? > > SELECT id, settlement, settlement_type, > to_timestamp(to_char(settlement_date, '#')) as settlement_date > ,transaction_id, transaction_type, settlement_institution, > settlement_institution_bank_domain_id, domain_i,product_group_id, > channel_id FROM `dfs`.`***`.`/tbl_settlement` > > Could you narrow down on the column which is causing this error? Also, can > you please set alter session set `exec.errors.verbose` = true and provide > the stack trace from the console. > > Thanks, > Gautam > > On Fri, Jan 11, 2019 at 3:22 AM Peter Edike < > peter.ed...@interswitchgroup.com> wrote: > > > Hello Everyone, > > > > > > I have a set of parquet files which where created as a result of a batch > > ETL job (using Sqoop 1). > > > > > > I can query the fields normally without any kind of formatting and all is > > well... > > > > > > However, when i try to create a view with the following code > > > > > > CREATE VIEW `dfs.asra`.`vw_tbl_settlement` AS > > SELECT id, settlement, settlement_type, > > to_timestamp(to_char(settlement_date, '#')) as settlement_date > > ,transaction_id, transaction_type, settlement_institution, > > settlement_institution_bank_domain_id, domain_i,product_group_id, > > channel_id FROM `dfs`.`***`.`/tbl_settlement` > > > > I end up with the following exception > > > > org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: > > SchemaChangeException: Failure while trying to materialize incoming > schema. > > Errors: Error in expression at index -1. Error: Missing function > > implementation: [castTINYINT(VARCHAR-OPTIONAL)]. Full expression: > --UNKNOWN > > EXPRESSION--.. Fragment 0:0 [Error Id: > 55c13096-085a-4d0c-9eca-5c7bbc2d1b8f > > on bdnode3.interswitch.com:31010]? > > > > Please What am I doing wrong > > > > Kind Regards > > > > > > >
Re: Problem Creating Views In Apache Drill
Hi Peter, The function to_char(settlement_date, '#') is operating on a date column, so the second argument should be a date formatter, something like: to_char(settlement_date, 'mm-dd-').Can you try that ? Aman On Fri, Jan 11, 2019 at 2:19 PM Khurram Faraaz wrote: > Hi Peter, > > What version of Drill are you on ? > > Thanks, > Khurram > > On Fri, Jan 11, 2019 at 12:20 PM Gautam Parai wrote: > > > Hi Peter, > > > > Do you see the same error when you run the query instead? > > > > SELECT id, settlement, settlement_type, > > to_timestamp(to_char(settlement_date, '#')) as settlement_date > > ,transaction_id, transaction_type, settlement_institution, > > settlement_institution_bank_domain_id, domain_i,product_group_id, > > channel_id FROM `dfs`.`***`.`/tbl_settlement` > > > > Could you narrow down on the column which is causing this error? Also, > can > > you please set alter session set `exec.errors.verbose` = true and provide > > the stack trace from the console. > > > > Thanks, > > Gautam > > > > On Fri, Jan 11, 2019 at 3:22 AM Peter Edike < > > peter.ed...@interswitchgroup.com> wrote: > > > > > Hello Everyone, > > > > > > > > > I have a set of parquet files which where created as a result of a > batch > > > ETL job (using Sqoop 1). > > > > > > > > > I can query the fields normally without any kind of formatting and all > is > > > well... > > > > > > > > > However, when i try to create a view with the following code > > > > > > > > > CREATE VIEW `dfs.asra`.`vw_tbl_settlement` AS > > > SELECT id, settlement, settlement_type, > > > to_timestamp(to_char(settlement_date, '#')) as settlement_date > > > ,transaction_id, transaction_type, settlement_institution, > > > settlement_institution_bank_domain_id, domain_i,product_group_id, > > > channel_id FROM `dfs`.`***`.`/tbl_settlement` > > > > > > I end up with the following exception > > > > > > org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: > > > SchemaChangeException: Failure while trying to materialize incoming > > schema. > > > Errors: Error in expression at index -1. Error: Missing function > > > implementation: [castTINYINT(VARCHAR-OPTIONAL)]. Full expression: > > --UNKNOWN > > > EXPRESSION--.. Fragment 0:0 [Error Id: > > 55c13096-085a-4d0c-9eca-5c7bbc2d1b8f > > > on bdnode3.interswitch.com:31010]? > > > > > > Please What am I doing wrong > > > > > > Kind Regards > > > > > > > > > > > >
Re: Problem Creating Views In Apache Drill
Hi Peter, What version of Drill are you on ? Thanks, Khurram On Fri, Jan 11, 2019 at 12:20 PM Gautam Parai wrote: > Hi Peter, > > Do you see the same error when you run the query instead? > > SELECT id, settlement, settlement_type, > to_timestamp(to_char(settlement_date, '#')) as settlement_date > ,transaction_id, transaction_type, settlement_institution, > settlement_institution_bank_domain_id, domain_i,product_group_id, > channel_id FROM `dfs`.`***`.`/tbl_settlement` > > Could you narrow down on the column which is causing this error? Also, can > you please set alter session set `exec.errors.verbose` = true and provide > the stack trace from the console. > > Thanks, > Gautam > > On Fri, Jan 11, 2019 at 3:22 AM Peter Edike < > peter.ed...@interswitchgroup.com> wrote: > > > Hello Everyone, > > > > > > I have a set of parquet files which where created as a result of a batch > > ETL job (using Sqoop 1). > > > > > > I can query the fields normally without any kind of formatting and all is > > well... > > > > > > However, when i try to create a view with the following code > > > > > > CREATE VIEW `dfs.asra`.`vw_tbl_settlement` AS > > SELECT id, settlement, settlement_type, > > to_timestamp(to_char(settlement_date, '#')) as settlement_date > > ,transaction_id, transaction_type, settlement_institution, > > settlement_institution_bank_domain_id, domain_i,product_group_id, > > channel_id FROM `dfs`.`***`.`/tbl_settlement` > > > > I end up with the following exception > > > > org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: > > SchemaChangeException: Failure while trying to materialize incoming > schema. > > Errors: Error in expression at index -1. Error: Missing function > > implementation: [castTINYINT(VARCHAR-OPTIONAL)]. Full expression: > --UNKNOWN > > EXPRESSION--.. Fragment 0:0 [Error Id: > 55c13096-085a-4d0c-9eca-5c7bbc2d1b8f > > on bdnode3.interswitch.com:31010]? > > > > Please What am I doing wrong > > > > Kind Regards > > > > > > >
Re: Problem Creating Views In Apache Drill
Hi Peter, Do you see the same error when you run the query instead? SELECT id, settlement, settlement_type, to_timestamp(to_char(settlement_date, '#')) as settlement_date ,transaction_id, transaction_type, settlement_institution, settlement_institution_bank_domain_id, domain_i,product_group_id, channel_id FROM `dfs`.`***`.`/tbl_settlement` Could you narrow down on the column which is causing this error? Also, can you please set alter session set `exec.errors.verbose` = true and provide the stack trace from the console. Thanks, Gautam On Fri, Jan 11, 2019 at 3:22 AM Peter Edike < peter.ed...@interswitchgroup.com> wrote: > Hello Everyone, > > > I have a set of parquet files which where created as a result of a batch > ETL job (using Sqoop 1). > > > I can query the fields normally without any kind of formatting and all is > well... > > > However, when i try to create a view with the following code > > > CREATE VIEW `dfs.asra`.`vw_tbl_settlement` AS > SELECT id, settlement, settlement_type, > to_timestamp(to_char(settlement_date, '#')) as settlement_date > ,transaction_id, transaction_type, settlement_institution, > settlement_institution_bank_domain_id, domain_i,product_group_id, > channel_id FROM `dfs`.`***`.`/tbl_settlement` > > I end up with the following exception > > org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: > SchemaChangeException: Failure while trying to materialize incoming schema. > Errors: Error in expression at index -1. Error: Missing function > implementation: [castTINYINT(VARCHAR-OPTIONAL)]. Full expression: --UNKNOWN > EXPRESSION--.. Fragment 0:0 [Error Id: 55c13096-085a-4d0c-9eca-5c7bbc2d1b8f > on bdnode3.interswitch.com:31010]? > > Please What am I doing wrong > > Kind Regards > > >
Problem Creating Views In Apache Drill
Hello Everyone, I have a set of parquet files which where created as a result of a batch ETL job (using Sqoop 1). I can query the fields normally without any kind of formatting and all is well... However, when i try to create a view with the following code CREATE VIEW `dfs.asra`.`vw_tbl_settlement` AS SELECT id, settlement, settlement_type, to_timestamp(to_char(settlement_date, '#')) as settlement_date ,transaction_id, transaction_type, settlement_institution, settlement_institution_bank_domain_id, domain_i,product_group_id, channel_id FROM `dfs`.`***`.`/tbl_settlement` I end up with the following exception org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: SchemaChangeException: Failure while trying to materialize incoming schema. Errors: Error in expression at index -1. Error: Missing function implementation: [castTINYINT(VARCHAR-OPTIONAL)]. Full expression: --UNKNOWN EXPRESSION--.. Fragment 0:0 [Error Id: 55c13096-085a-4d0c-9eca-5c7bbc2d1b8f on bdnode3.interswitch.com:31010]? Please What am I doing wrong Kind Regards
