Re: Exception While Querying Decimal Fields in Apache Drill

[Vova Vysotskyi]

Thanks for the stack trace, it helped to find the root cause of this
problem.
Decimal values in parquet table are stored using BINARY primitive type, but
currently, Drill does not support decimals stored as binary.

The good news is that it will be fixed in DRILL-6094.

Kind regards,
Volodymyr Vysotskyi


пн, 30 квіт. 2018 о 11:44 Peter Edike 
пише:

>
>
> Hi
>
> Here is the stacktrace on the server side
>
> error_type: SYSTEM
> message: "SYSTEM ERROR: ClassCastException:
> org.apache.drill.exec.vector.NullableDecimal28SparseVector cannot be cast
> to org.apache.drill.exec.vector.VariableWidthVector\n\nFragment
> 2:8\n\n[Error Id: b144b650-99c3-4305-880f-d3a6794a2eab on
> BGDTEST3.INTERSWITCH.COM:31010]"
> exception {
>   exception_class:
> "org.apache.drill.common.exceptions.DrillRuntimeException"
>   message: "Error in parquet record reader.\nMessage: Failure in
> setting up reader\nParquet Metadata: ParquetMetaData{FileMetaData{schema:
> message postillion_data_schema_generator.postillion_super_switch_schema
> {\n  optional int64 post_tran_id;\n  optional int64 post_tran_cust_id;\n
> optional int32 settle_entity_id;\n  optional int32 batch_nr;\n  optional
> int64 prev_post_tran_id;\n  optional int64 next_post_tran_id;\n  optional
> binary sink_node_name (UTF8);\n  optional binary tran_postilion_originated
> (DECIMAL(1,0));\n  optional binary tran_completed (DECIMAL(1,0));\n
> optional binary message_type (UTF8);\n  optional binary tran_type
> (UTF8);\n  optional int64 tran_nr;\n  optional binary system_trace_audit_nr
> (UTF8);\n  optional binary rsp_code_req (UTF8);\n  optional binary
> rsp_code_rsp (UTF8);\n  optional binary abort_rsp_code (UTF8);\n  optional
> binary auth_id_rsp (UTF8);\n  optional binary auth_type (DECIMAL(1,0));\n
> optional binary auth_reason (DECIMAL(1,0));\n  optional binary
> retention_data (UTF8);\n  optional binary acquiring_inst_id_code (UTF8);\n
> optional binary message_reason_code (UTF8);\n  optional binary sponsor_bank
> (UTF8);\n  optional binary retrieval_reference_nr (UTF8);\n  optional int64
> datetime_tran_gmt (TIMESTAMP_MILLIS);\n  optional int64 datetime_tran_local
> (TIMESTAMP_MILLIS);\n  optional int64 datetime_req (TIMESTAMP_MILLIS);\n
> optional int64 datetime_rsp (TIMESTAMP_MILLIS);\n  optional int64
> realtime_business_date (TIMESTAMP_MILLIS);\n  optional int64
> recon_business_date (TIMESTAMP_MILLIS);\n  optional binary
> from_account_type (UTF8);\n  optional binary to_account_type (UTF8);\n
> optional binary from_account_id (UTF8);\n  optional binary to_account_id
> (UTF8);\n  optional binary tran_amount_req (DECIMAL(16,0));\n  optional
> binary tran_amount_rsp (DECIMAL(16,0));\n  optional binary
> settle_amount_impact (DECIMAL(16,0));\n  optional binary tran_cash_req
> (DECIMAL(16,0));\n  optional binary tran_cash_rsp (DECIMAL(16,0));\n
> optional binary tran_currency_code (UTF8);\n  optional binary
> tran_tran_fee_req (DECIMAL(16,0));\n  optional binary tran_tran_fee_rsp
> (DECIMAL(16,0));\n  optional binary tran_tran_fee_currency_code (UTF8);\n
> optional binary tran_proc_fee_req (DECIMAL(16,0));\n  optional binary
> tran_proc_fee_rsp (DECIMAL(16,0));\n  optional binary
> tran_proc_fee_currency_code (UTF8);\n  optional binary settle_amount_req
> (DECIMAL(16,0));\n  optional binary settle_amount_rsp (DECIMAL(16,0));\n
> optional binary settle_cash_req (DECIMAL(16,0));\n  optional binary
> settle_cash_rsp (DECIMAL(16,0));\n  optional binary settle_tran_fee_req
> (DECIMAL(16,0));\n  optional binary settle_tran_fee_rsp (DECIMAL(16,0));\n
> optional binary settle_proc_fee_req (DECIMAL(16,0));\n  optional binary
> settle_proc_fee_rsp (DECIMAL(16,0));\n  optional binary
> settle_currency_code (UTF8);\n  optional binary icc_data_req (UTF8);\n
> optional binary icc_data_rsp (UTF8);\n  optional binary pos_entry_mode
> (UTF8);\n  optional binary pos_condition_code (UTF8);\n  optional binary
> additional_rsp_data (UTF8);\n  optional binary structured_data_req
> (UTF8);\n  optional binary structured_data_rsp (UTF8);\n  optional binary
> tran_reversed (UTF8);\n  optional binary prev_tran_approved
> (DECIMAL(1,0));\n  optional binary issuer_network_id (UTF8);\n  optional
> binary acquirer_network_id (UTF8);\n  optional binary extended_tran_type
> (UTF8);\n  optional binary ucaf_data (UTF8);\n  optional binary
> from_account_type_qualifier (UTF8);\n  optional binary
> to_account_type_qualifier (UTF8);\n  optional binary bank_details
> (UTF8);\n  optional binary payee (UTF8);\n  optional binary
> card_verification_result (UTF8);\n  optional int32 online_system_id;\n
> optional int32 participant_id;\n  optional int32 opp_participant_id;\n
> optional binary receiving_inst_id_code (UTF8);\n  optional int32
> routing_type;\n  optional binary pt_pos_operating_environment (UTF8);\n
> optional binary pt_pos_card_input_mode (UTF8);\n  optional binary
> pt_pos_cardholder_auth_method (UTF8);\n  optional binary
> 

RE: Exception While Querying Decimal Fields in Apache Drill

[Peter Edike]

Hi

Here is the stacktrace on the server side

error_type: SYSTEM
message: "SYSTEM ERROR: ClassCastException: 
org.apache.drill.exec.vector.NullableDecimal28SparseVector cannot be cast to 
org.apache.drill.exec.vector.VariableWidthVector\n\nFragment 2:8\n\n[Error Id: 
b144b650-99c3-4305-880f-d3a6794a2eab on BGDTEST3.INTERSWITCH.COM:31010]"
exception {
  exception_class: 
"org.apache.drill.common.exceptions.DrillRuntimeException"
  message: "Error in parquet record reader.\nMessage: Failure in setting up 
reader\nParquet Metadata: ParquetMetaData{FileMetaData{schema: message 
postillion_data_schema_generator.postillion_super_switch_schema {\n  optional 
int64 post_tran_id;\n  optional int64 post_tran_cust_id;\n  optional int32 
settle_entity_id;\n  optional int32 batch_nr;\n  optional int64 
prev_post_tran_id;\n  optional int64 next_post_tran_id;\n  optional binary 
sink_node_name (UTF8);\n  optional binary tran_postilion_originated 
(DECIMAL(1,0));\n  optional binary tran_completed (DECIMAL(1,0));\n  optional 
binary message_type (UTF8);\n  optional binary tran_type (UTF8);\n  optional 
int64 tran_nr;\n  optional binary system_trace_audit_nr (UTF8);\n  optional 
binary rsp_code_req (UTF8);\n  optional binary rsp_code_rsp (UTF8);\n  optional 
binary abort_rsp_code (UTF8);\n  optional binary auth_id_rsp (UTF8);\n  
optional binary auth_type (DECIMAL(1,0));\n  optional binary auth_reason 
(DECIMAL(1,0));\n  optional binary retention_data (UTF8);\n  optional binary 
acquiring_inst_id_code (UTF8);\n  optional binary message_reason_code (UTF8);\n 
 optional binary sponsor_bank (UTF8);\n  optional binary retrieval_reference_nr 
(UTF8);\n  optional int64 datetime_tran_gmt (TIMESTAMP_MILLIS);\n  optional 
int64 datetime_tran_local (TIMESTAMP_MILLIS);\n  optional int64 datetime_req 
(TIMESTAMP_MILLIS);\n  optional int64 datetime_rsp (TIMESTAMP_MILLIS);\n  
optional int64 realtime_business_date (TIMESTAMP_MILLIS);\n  optional int64 
recon_business_date (TIMESTAMP_MILLIS);\n  optional binary from_account_type 
(UTF8);\n  optional binary to_account_type (UTF8);\n  optional binary 
from_account_id (UTF8);\n  optional binary to_account_id (UTF8);\n  optional 
binary tran_amount_req (DECIMAL(16,0));\n  optional binary tran_amount_rsp 
(DECIMAL(16,0));\n  optional binary settle_amount_impact (DECIMAL(16,0));\n  
optional binary tran_cash_req (DECIMAL(16,0));\n  optional binary tran_cash_rsp 
(DECIMAL(16,0));\n  optional binary tran_currency_code (UTF8);\n  optional 
binary tran_tran_fee_req (DECIMAL(16,0));\n  optional binary tran_tran_fee_rsp 
(DECIMAL(16,0));\n  optional binary tran_tran_fee_currency_code (UTF8);\n  
optional binary tran_proc_fee_req (DECIMAL(16,0));\n  optional binary 
tran_proc_fee_rsp (DECIMAL(16,0));\n  optional binary 
tran_proc_fee_currency_code (UTF8);\n  optional binary settle_amount_req 
(DECIMAL(16,0));\n  optional binary settle_amount_rsp (DECIMAL(16,0));\n  
optional binary settle_cash_req (DECIMAL(16,0));\n  optional binary 
settle_cash_rsp (DECIMAL(16,0));\n  optional binary settle_tran_fee_req 
(DECIMAL(16,0));\n  optional binary settle_tran_fee_rsp (DECIMAL(16,0));\n  
optional binary settle_proc_fee_req (DECIMAL(16,0));\n  optional binary 
settle_proc_fee_rsp (DECIMAL(16,0));\n  optional binary settle_currency_code 
(UTF8);\n  optional binary icc_data_req (UTF8);\n  optional binary icc_data_rsp 
(UTF8);\n  optional binary pos_entry_mode (UTF8);\n  optional binary 
pos_condition_code (UTF8);\n  optional binary additional_rsp_data (UTF8);\n  
optional binary structured_data_req (UTF8);\n  optional binary 
structured_data_rsp (UTF8);\n  optional binary tran_reversed (UTF8);\n  
optional binary prev_tran_approved (DECIMAL(1,0));\n  optional binary 
issuer_network_id (UTF8);\n  optional binary acquirer_network_id (UTF8);\n  
optional binary extended_tran_type (UTF8);\n  optional binary ucaf_data 
(UTF8);\n  optional binary from_account_type_qualifier (UTF8);\n  optional 
binary to_account_type_qualifier (UTF8);\n  optional binary bank_details 
(UTF8);\n  optional binary payee (UTF8);\n  optional binary 
card_verification_result (UTF8);\n  optional int32 online_system_id;\n  
optional int32 participant_id;\n  optional int32 opp_participant_id;\n  
optional binary receiving_inst_id_code (UTF8);\n  optional int32 
routing_type;\n  optional binary pt_pos_operating_environment (UTF8);\n  
optional binary pt_pos_card_input_mode (UTF8);\n  optional binary 
pt_pos_cardholder_auth_method (UTF8);\n  optional binary 
pt_pos_pin_capture_ability (UTF8);\n  optional binary pt_pos_terminal_operator 
(UTF8);\n  optional binary source_node_key (UTF8);\n  optional int32 
proc_online_system_id;\n  optional int32 from_account_id_cs;\n  optional int32 
to_account_id_cs;\n  optional binary pos_geographic_data (UTF8);\n  optional 
binary payer_account_id (UTF8);\n  optional binary cvv_available_at_auth 
(UTF8);\n  optional binary cvv2_available_at_auth (UTF8);\n  optional int64 
Expr1;\n  

Re: Exception While Querying Decimal Fields in Apache Drill

[Vova Vysotskyi]

This part of the stack trace connected with the client side, could
you please share a stack trace that corresponds to the error on the server
side?

Kind regards,
Volodymyr Vysotskyi


пн, 30 квіт. 2018 о 10:44 Peter Edike <peter.ed...@interswitchgroup.com>
пише:

> Hi,
>
> Stacktrace as requested
>
> java.sql.SQLException: [MapR][DrillJDBCDriver](500165) Query execution
> error. Details: SYSTEM ERROR: ClassCastException:
> org.apache.drill.exec.vector.NullableDecimal28SparseVector cannot be cast
> to org.apache.drill.exec.vector.VariableWidthVector
> Fragment 2:18
> [Error Id: dba9df08-fb1d-4bd2-93e6-d08fb6f79ff1 on
> BGDTEST3.INTERSWITCH.COM:31010].
> at
> com.mapr.drill.drill.dataengine.DRQryResultListener.checkAndThrowException(Unknown
> Source)
> at
> com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Unknown
> Source)
> at
> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchData(Unknown
> Source)
> at
> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unknown
> Source)
> at
> com.mapr.drill.drill.dataengine.DRJDBCQueryExecutor.execute(Unknown Source)
> at com.mapr.drill.jdbc.common.SStatement.executeNoParams(Unknown
> Source)
> at com.mapr.drill.jdbc.common.SStatement.execute(Unknown Source)
> at
> org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
> at
> org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
> at
> org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.java:581)
> at
> org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.java:692)
> at
> org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:97)
> at
> org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:498)
> at org.apache.zeppelin.scheduler.Job.run(Job.java:175)
> at
> org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(ParallelScheduler.java:162)
> at
> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> Caused by: com.mapr.drill.support.exceptions.GeneralException:
> [MapR][DrillJDBCDriver](500165) Query execution error. Details: SYSTEM
> ERROR: ClassCastException:
> org.apache.drill.exec.vector.NullableDecimal28SparseVector cannot be cast
> to org.apache.drill.exec.vector.VariableWidthVector
> Fragment 2:18
> [Error Id: dba9df08-fb1d-4bd2-93e6-d08fb6f79ff1 on
> BGDTEST3.INTERSWITCH.COM:31010].
> ... 21 more
>
>
> This message has been marked as CONFIDENTIAL on Monday, April 30, 2018 @
> 8:44:22 AM
>
> -Original Message-
> From: Vova Vysotskyi <vvo...@gmail.com>
> Sent: Friday, April 27, 2018 5:29 PM
> To: user@drill.apache.org
> Subject: Re: Exception While Querying Decimal Fields in Apache Drill
>
> Hi Peter,
>
> Could you please also share a stacktrace?
>
> Does the specified table
> contain pan, terminal_id, source_node_name, tran_completed, tran_reversed
> and tran_type columns? If it contains them, which types do they have?
>
> Kind regards,
> Volodymyr Vysotskyi
>
>
> пт, 27 квіт. 2018 о 17:47 Peter Edike <peter.ed...@interswitchgroup.com>
> пише:
>
> >
> >
> > Drill Version  1.12.0
> >
> >
> >
> > planner.enable_decimal_data_type is set to true on the system.
> >
> >
> >
> > --peter
> >
> >
> >
> >
> >
> > -Original Message-
> >
> > From: Andries Engelbrecht <aengelbre...@mapr.com>
> >
> > Sent: Friday, April 27, 2018 3:41 PM
> >
> > To: user@drill.apache.org
> >
> > Subject: Re: Exception While Querying Decimal Fields in Apache Drill
> >
> >
> >
> > What version of Drill are you using?
> >
> > Also is planner.enable_decimal_data_type set to true on the system?
> >
> >
> >
> > --Andries
> >
> >
> >
> > On 4/27/18, 7:24 AM, "Peter Edike" <peter.ed...@interswitchgroup

RE: Exception While Querying Decimal Fields in Apache Drill

[Peter Edike]

Hi, 

Stacktrace as requested

java.sql.SQLException: [MapR][DrillJDBCDriver](500165) Query execution error. 
Details: SYSTEM ERROR: ClassCastException: 
org.apache.drill.exec.vector.NullableDecimal28SparseVector cannot be cast to 
org.apache.drill.exec.vector.VariableWidthVector
Fragment 2:18
[Error Id: dba9df08-fb1d-4bd2-93e6-d08fb6f79ff1 on 
BGDTEST3.INTERSWITCH.COM:31010].
at 
com.mapr.drill.drill.dataengine.DRQryResultListener.checkAndThrowException(Unknown
 Source)
at 
com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Unknown Source)
at 
com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchData(Unknown 
Source)
at 
com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unknown Source)
at com.mapr.drill.drill.dataengine.DRJDBCQueryExecutor.execute(Unknown 
Source)
at com.mapr.drill.jdbc.common.SStatement.executeNoParams(Unknown Source)
at com.mapr.drill.jdbc.common.SStatement.execute(Unknown Source)
at 
org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
at 
org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
at 
org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.java:581)
at 
org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.java:692)
at 
org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:97)
at 
org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:498)
at org.apache.zeppelin.scheduler.Job.run(Job.java:175)
at 
org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(ParallelScheduler.java:162)
at 
java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at 
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
at 
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at 
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at 
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
Caused by: com.mapr.drill.support.exceptions.GeneralException: 
[MapR][DrillJDBCDriver](500165) Query execution error. Details: SYSTEM ERROR: 
ClassCastException: org.apache.drill.exec.vector.NullableDecimal28SparseVector 
cannot be cast to org.apache.drill.exec.vector.VariableWidthVector
Fragment 2:18
[Error Id: dba9df08-fb1d-4bd2-93e6-d08fb6f79ff1 on 
BGDTEST3.INTERSWITCH.COM:31010].
... 21 more


This message has been marked as CONFIDENTIAL on Monday, April 30, 2018 @ 
8:44:22 AM

-Original Message-
From: Vova Vysotskyi <vvo...@gmail.com> 
Sent: Friday, April 27, 2018 5:29 PM
To: user@drill.apache.org
Subject: Re: Exception While Querying Decimal Fields in Apache Drill

Hi Peter,

Could you please also share a stacktrace?

Does the specified table
contain pan, terminal_id, source_node_name, tran_completed, tran_reversed and 
tran_type columns? If it contains them, which types do they have?

Kind regards,
Volodymyr Vysotskyi


пт, 27 квіт. 2018 о 17:47 Peter Edike <peter.ed...@interswitchgroup.com>
пише:

>
>
> Drill Version  1.12.0
>
>
>
> planner.enable_decimal_data_type is set to true on the system.
>
>
>
> --peter
>
>
>
>
>
> -Original Message-
>
> From: Andries Engelbrecht <aengelbre...@mapr.com>
>
> Sent: Friday, April 27, 2018 3:41 PM
>
> To: user@drill.apache.org
>
> Subject: Re: Exception While Querying Decimal Fields in Apache Drill
>
>
>
> What version of Drill are you using?
>
> Also is planner.enable_decimal_data_type set to true on the system?
>
>
>
> --Andries
>
>
>
> On 4/27/18, 7:24 AM, "Peter Edike" <peter.ed...@interswitchgroup.com>
> wrote:
>
>
>
> Tried That, It did not work. Still Fails with the exception. Let 
> me even add that even if the query is a simple select from 
> statement, as long any of the fields is decimal type, the statement 
> will fail with the stated exception
>
>
>
> Please Help a lot depends on this
>
>
>
> Best regards,
>
> Peter Edike
>
>
>
> This message has been marked as CONFIDENTIAL on Friday, April 27, 
> 2018 @ 3:24:36 PM
>
>
>
> -----Original Message-
>
> From: Andries Engelbrecht <aengelbre...@mapr.com>
>
> Sent: Friday, April 27, 2018 3:07 PM
>
> To: user@drill.apache.org
>
> Subject: Re: Exception While Querying Decimal Fields in Apache 
> Drill
>
>
>
> Pe

Re: Exception While Querying Decimal Fields in Apache Drill

[Vova Vysotskyi]

Hi Peter,

Could you please also share a stacktrace?

Does the specified table
contain pan, terminal_id, source_node_name, tran_completed, tran_reversed
and tran_type columns? If it contains them, which types do they have?

Kind regards,
Volodymyr Vysotskyi


пт, 27 квіт. 2018 о 17:47 Peter Edike <peter.ed...@interswitchgroup.com>
пише:

>
>
> Drill Version  1.12.0
>
>
>
> planner.enable_decimal_data_type is set to true on the system.
>
>
>
> --peter
>
>
>
>
>
> -Original Message-
>
> From: Andries Engelbrecht <aengelbre...@mapr.com>
>
> Sent: Friday, April 27, 2018 3:41 PM
>
> To: user@drill.apache.org
>
> Subject: Re: Exception While Querying Decimal Fields in Apache Drill
>
>
>
> What version of Drill are you using?
>
> Also is planner.enable_decimal_data_type set to true on the system?
>
>
>
> --Andries
>
>
>
> On 4/27/18, 7:24 AM, "Peter Edike" <peter.ed...@interswitchgroup.com>
> wrote:
>
>
>
> Tried That, It did not work. Still Fails with the exception. Let me
> even add that even if the query is a simple select from statement, as
> long any of the fields is decimal type, the statement will fail with the
> stated exception
>
>
>
> Please Help a lot depends on this
>
>
>
> Best regards,
>
> Peter Edike
>
>
>
> This message has been marked as CONFIDENTIAL on Friday, April 27, 2018
> @ 3:24:36 PM
>
>
>
>     -----Original Message-
>
> From: Andries Engelbrecht <aengelbre...@mapr.com>
>
> Sent: Friday, April 27, 2018 3:07 PM
>
> To: user@drill.apache.org
>
> Subject: Re: Exception While Querying Decimal Fields in Apache Drill
>
>
>
> Perhaps try to convert the predicate and select operations involving
> the decimal types to float or similar.
>
>
>
> i.e tran_completed = 1.0   and ((cast(SETTLE_AMOUNT_IMPACT as double)
> *(-1.0))/100.0)
>
>
>
> Alternatively you may have to cast the decimals as float, but that
> will be more cumbersome.
>
>
>
> --Andries
>
>
>
> On 4/27/18, 5:18 AM, "Peter Edike" <peter.ed...@interswitchgroup.com>
> wrote:
>
>
>
> I am trying to run the following query in apache drill, I am
> querying data stored in parquet files using the following query
>
>
>
>
>
> select pan, count(*) as number_of_transactions ,
>
> terminal_id,SUM((cast(SETTLE_AMOUNT_IMPACT as double) *-1)/100) AS
> settle_amount_impact
>
>
>
>
>
> from
> dfs.`/iswdata/storage/products/superswitch/parquet/transactions`
>
>
>
> where pan like '506126%' and terminal_id like '1%' and
>
> sink_node_name like ('SWTDB%')
>
>and source_node_name not like ('SWTDBLsrc')
>
> and tran_completed=1
>
> and tran_reversed = 0
>
> and tran_postilion_originated = 1
>
> AND tran_type = '01'
>
> --and pan like '506126%0011'
>
> group by pan,terminal_id
>
>
>
> The Schema for the data I am querying is as follows
>
>
>
>
>
> post_tran_id LONG
>
>
>
>  post_tran_cust_id :LONG
>
>
>
>  settle_entity_id :INTEGER
>
>
>
>  batch_nr : INTEGER
>
>
>
>  prev_post_tran_id : LONG
>
>
>
>  next_post_tran_id : LONG
>
>
>
>  sink_node_name : STRING
>
>
>
>  tran_postilion_originated : DECIMAL
>
>
>
>  tran_completed : DECIMAL
>
>
>
>  tran_amount_req : DECIMAL
>
>
>
>  tran_amount_rsp : DECIMAL
>
>
>
>  settle_amount_impact : DECIMAL
>
>
>
>  tran_cash_req : DECIMAL
>
>
>
>  tran_cash_rsp : DECIMAL
>
>
>
> tran_currency_code : STRING
>
>
>
> tran_tran_fee_req : DECIMAL
>
>
>
> tran_tran_fee_rsp : DECIMAL
>
>
>
> tran_tran_fee_currency_code : STRING
>
>
>
> tran_proc_fee_req : DECIMAL
>
>
>
> tran_proc_fee_rsp : DECIMAL
>
>
>
> tran_proc_fee_currency_code : STRING
>
>
>
> settle_amount_req : DECIMAL
>
>
>
> settle_amount_rsp : DECIMAL
>
>
>
> settle_cash_req : DECIMAL
>
>
>
> settle_cash_rsp : DECIMAL
>
>
>
> settle_tran_fee_req : DECIMAL
>
>
>
> settle_tran_fee_rsp : DECIMAL
>
>
>
> settle_proc_fee_req : DECIMAL
>
>
>
> settle_proc_fee_rsp : DECIMAL
>
>
>
> settle_currency_code : STRING
>
>
>
> However When I run the query against the dataset, I get the
> following exception
>
>
>
>
>
> SYSTEM ERROR: ClassCastException:
> org.apache.drill.exec.vector.NullableDecimal28SparseVector cannot be cast
> to org.apache.drill.exec.vector.VariableWidthVector
>
>
>
>
>
> More so, the same error occurs when I include a decimal field in
> the select clause. Please, is there something I am missing or doing wrong,
> Any pointer will be deeply appreciated
>
>
>
> Kind Regards
>
>
>
> Peter
>
> 
>
>
>
>
>
>
>
>
>
>
>

Re: Exception While Querying Decimal Fields in Apache Drill

[Andries Engelbrecht]

What version of Drill are you using?
Also is planner.enable_decimal_data_type set to true on the system?

--Andries

On 4/27/18, 7:24 AM, "Peter Edike" <peter.ed...@interswitchgroup.com> wrote:

Tried That, It did not work. Still Fails with the exception. Let me even 
add that even if the query is a simple select from statement, as long any 
of the fields is decimal type, the statement will fail with the stated exception

Please Help a lot depends on this

Best regards,
Peter Edike

This message has been marked as CONFIDENTIAL on Friday, April 27, 2018 @ 
3:24:36 PM

-Original Message-
From: Andries Engelbrecht <aengelbre...@mapr.com> 
Sent: Friday, April 27, 2018 3:07 PM
To: user@drill.apache.org
Subject: Re: Exception While Querying Decimal Fields in Apache Drill

Perhaps try to convert the predicate and select operations involving the 
decimal types to float or similar.

i.e tran_completed = 1.0   and ((cast(SETTLE_AMOUNT_IMPACT as double) 
*(-1.0))/100.0)

Alternatively you may have to cast the decimals as float, but that will be 
more cumbersome.

--Andries

On 4/27/18, 5:18 AM, "Peter Edike" <peter.ed...@interswitchgroup.com> wrote:

I am trying to run the following query in apache drill, I am querying 
data stored in parquet files using the following query


select pan, count(*) as number_of_transactions ,
terminal_id,SUM((cast(SETTLE_AMOUNT_IMPACT as double) *-1)/100) AS 
settle_amount_impact


from dfs.`/iswdata/storage/products/superswitch/parquet/transactions`

where pan like '506126%' and terminal_id like '1%' and
sink_node_name like ('SWTDB%')
and source_node_name not like ('SWTDBLsrc')
and tran_completed=1
and tran_reversed = 0
and tran_postilion_originated = 1
AND tran_type = '01'
--and pan like '506126%0011'
group by pan,terminal_id

The Schema for the data I am querying is as follows


post_tran_id LONG

 post_tran_cust_id :LONG

 settle_entity_id :INTEGER

 batch_nr : INTEGER

 prev_post_tran_id : LONG

 next_post_tran_id : LONG

 sink_node_name : STRING

 tran_postilion_originated : DECIMAL

 tran_completed : DECIMAL

 tran_amount_req : DECIMAL

 tran_amount_rsp : DECIMAL

 settle_amount_impact : DECIMAL

 tran_cash_req : DECIMAL

 tran_cash_rsp : DECIMAL

tran_currency_code : STRING

tran_tran_fee_req : DECIMAL

tran_tran_fee_rsp : DECIMAL

tran_tran_fee_currency_code : STRING

tran_proc_fee_req : DECIMAL

tran_proc_fee_rsp : DECIMAL

tran_proc_fee_currency_code : STRING

settle_amount_req : DECIMAL

settle_amount_rsp : DECIMAL

settle_cash_req : DECIMAL

settle_cash_rsp : DECIMAL

settle_tran_fee_req : DECIMAL

settle_tran_fee_rsp : DECIMAL

settle_proc_fee_req : DECIMAL

settle_proc_fee_rsp : DECIMAL

settle_currency_code : STRING

However When I run the query against the dataset, I get the following 
exception


SYSTEM ERROR: ClassCastException: 
org.apache.drill.exec.vector.NullableDecimal28SparseVector cannot be cast to 
org.apache.drill.exec.vector.VariableWidthVector


More so, the same error occurs when I include a decimal field in the 
select clause. Please, is there something I am missing or doing wrong, Any 
pointer will be deeply appreciated

Kind Regards

Peter

RE: Exception While Querying Decimal Fields in Apache Drill

[Peter Edike]

Tried That, It did not work. Still Fails with the exception. Let me even add 
that even if the query is a simple select from statement, as long any of 
the fields is decimal type, the statement will fail with the stated exception

Please Help a lot depends on this

Best regards,
Peter Edike

This message has been marked as CONFIDENTIAL on Friday, April 27, 2018 @ 
3:24:36 PM

-Original Message-
From: Andries Engelbrecht <aengelbre...@mapr.com> 
Sent: Friday, April 27, 2018 3:07 PM
To: user@drill.apache.org
Subject: Re: Exception While Querying Decimal Fields in Apache Drill

Perhaps try to convert the predicate and select operations involving the 
decimal types to float or similar.

i.e tran_completed = 1.0   and ((cast(SETTLE_AMOUNT_IMPACT as double) 
*(-1.0))/100.0)

Alternatively you may have to cast the decimals as float, but that will be more 
cumbersome.

--Andries

On 4/27/18, 5:18 AM, "Peter Edike" <peter.ed...@interswitchgroup.com> wrote:

I am trying to run the following query in apache drill, I am querying data 
stored in parquet files using the following query


select pan, count(*) as number_of_transactions ,
terminal_id,SUM((cast(SETTLE_AMOUNT_IMPACT as double) *-1)/100) AS 
settle_amount_impact


from dfs.`/iswdata/storage/products/superswitch/parquet/transactions`

where pan like '506126%' and terminal_id like '1%' and
sink_node_name like ('SWTDB%')
and source_node_name not like ('SWTDBLsrc')
and tran_completed=1
and tran_reversed = 0
and tran_postilion_originated = 1
AND tran_type = '01'
--and pan like '506126%0011'
group by pan,terminal_id

The Schema for the data I am querying is as follows


post_tran_id LONG

 post_tran_cust_id :LONG

 settle_entity_id :INTEGER

 batch_nr : INTEGER

 prev_post_tran_id : LONG

 next_post_tran_id : LONG

 sink_node_name : STRING

 tran_postilion_originated : DECIMAL

 tran_completed : DECIMAL

 tran_amount_req : DECIMAL

 tran_amount_rsp : DECIMAL

 settle_amount_impact : DECIMAL

 tran_cash_req : DECIMAL

 tran_cash_rsp : DECIMAL

tran_currency_code : STRING

tran_tran_fee_req : DECIMAL

tran_tran_fee_rsp : DECIMAL

tran_tran_fee_currency_code : STRING

tran_proc_fee_req : DECIMAL

tran_proc_fee_rsp : DECIMAL

tran_proc_fee_currency_code : STRING

settle_amount_req : DECIMAL

settle_amount_rsp : DECIMAL

settle_cash_req : DECIMAL

settle_cash_rsp : DECIMAL

settle_tran_fee_req : DECIMAL

settle_tran_fee_rsp : DECIMAL

settle_proc_fee_req : DECIMAL

settle_proc_fee_rsp : DECIMAL

settle_currency_code : STRING

However When I run the query against the dataset, I get the following 
exception


SYSTEM ERROR: ClassCastException: 
org.apache.drill.exec.vector.NullableDecimal28SparseVector cannot be cast to 
org.apache.drill.exec.vector.VariableWidthVector


More so, the same error occurs when I include a decimal field in the select 
clause. Please, is there something I am missing or doing wrong, Any pointer 
will be deeply appreciated

Kind Regards

Peter

Re: Exception While Querying Decimal Fields in Apache Drill

[Andries Engelbrecht]

Perhaps try to convert the predicate and select operations involving the 
decimal types to float or similar.

i.e tran_completed = 1.0   and ((cast(SETTLE_AMOUNT_IMPACT as double) 
*(-1.0))/100.0)

Alternatively you may have to cast the decimals as float, but that will be more 
cumbersome.

--Andries

On 4/27/18, 5:18 AM, "Peter Edike"  wrote:

I am trying to run the following query in apache drill, I am querying data 
stored in parquet files using the following query


select pan, count(*) as number_of_transactions ,
terminal_id,SUM((cast(SETTLE_AMOUNT_IMPACT as double) *-1)/100) AS 
settle_amount_impact


from dfs.`/iswdata/storage/products/superswitch/parquet/transactions`

where pan like '506126%' and terminal_id like '1%' and
sink_node_name like ('SWTDB%')
and source_node_name not like ('SWTDBLsrc')
and tran_completed=1
and tran_reversed = 0
and tran_postilion_originated = 1
AND tran_type = '01'
--and pan like '506126%0011'
group by pan,terminal_id

The Schema for the data I am querying is as follows


post_tran_id LONG

 post_tran_cust_id :LONG

 settle_entity_id :INTEGER

 batch_nr : INTEGER

 prev_post_tran_id : LONG

 next_post_tran_id : LONG

 sink_node_name : STRING

 tran_postilion_originated : DECIMAL

 tran_completed : DECIMAL

 tran_amount_req : DECIMAL

 tran_amount_rsp : DECIMAL

 settle_amount_impact : DECIMAL

 tran_cash_req : DECIMAL

 tran_cash_rsp : DECIMAL

tran_currency_code : STRING

tran_tran_fee_req : DECIMAL

tran_tran_fee_rsp : DECIMAL

tran_tran_fee_currency_code : STRING

tran_proc_fee_req : DECIMAL

tran_proc_fee_rsp : DECIMAL

tran_proc_fee_currency_code : STRING

settle_amount_req : DECIMAL

settle_amount_rsp : DECIMAL

settle_cash_req : DECIMAL

settle_cash_rsp : DECIMAL

settle_tran_fee_req : DECIMAL

settle_tran_fee_rsp : DECIMAL

settle_proc_fee_req : DECIMAL

settle_proc_fee_rsp : DECIMAL

settle_currency_code : STRING

However When I run the query against the dataset, I get the following 
exception


SYSTEM ERROR: ClassCastException: 
org.apache.drill.exec.vector.NullableDecimal28SparseVector cannot be cast to 
org.apache.drill.exec.vector.VariableWidthVector


More so, the same error occurs when I include a decimal field in the select 
clause. Please, is there something I am missing or doing wrong, Any pointer 
will be deeply appreciated

Kind Regards

Peter