[Bug] [Sink] Bug Hive insert error org.apache.spark.sql.execution.QueryExecutionException: Parquet column cannot be converted in file hdfs:/db/xx/dt_mon=xxxx/qwxsadas12321321.parquet. Column: [xxx ], Expected: decimal(12,2), Found: FIXED_LEN_BYTE_ARRAY about seatunnel HOT 9 OPEN

Please provide the schema of oracle table and hive table.

from seatunnel.

hive table below，Oracle same cloumns
-- drop table if exists ghydata.ods_webcsmsgl_ecsms_visit_info_mi;
CREATE TABLE IF NOT EXISTS ghydata.ods_webcsmsgl_ecsms_visit_info_mi
(
isvisit string COMMENT '',
clwz string COMMENT '',
content string COMMENT '',
plan string COMMENT ' ',
cuser string COMMENT '创建',
cdate timestamp COMMENT '时间',
zhanbi decimal(12, 2) COMMENT ' ',
entertime timestamp COMMENT '进',
leavetime timestamp COMMENT '离'
)
COMMENT 'SFA-'
PARTITIONED BY (dt_mon string COMMENT '月快照增量表')
STORED AS PARQUET;

from seatunnel.

It's better to provide the definition of the "zhanbi" field in the Oracle table. It seems like there might be an issue with the type conversion of the Oracle connector.

from seatunnel.

But it works well on seatunnel1.5.7,so i don't what is the reason that cause this

Oralce below:
Column Name # Type Type Mod Not Null Default Comment
ZHANBI 11 NUMBER(12,2) [NULL] false [NULL] 占比

from seatunnel.

Because the scale of the field ZHANBI is 2, the field type will set to Decimal(38, 18).

❯ parq ./T_836191201227964417_6d287c425d_0_1_0.parquet -s                                                                       18:45:25

 # Schema 
 <pyarrow._parquet.ParquetSchema object at 0x7ff161507b40>
required group field_id=-1 SeaTunnelRecord {
  optional fixed_len_byte_array(16) field_id=-1 f (Decimal(precision=38, scale=18));
}

You can use sql cast the field as decimal(12, 2). Like this:

source {
  Jdbc {
    result_table_name = tbl
    driver = oracle.jdbc.driver.OracleDriver
    url = "jdbc:oracle:thin:@localhost:49161/xe"
    user = xxxxx
    password = xxx
    query = "select F from tbl"
    properties {
       database.oracle.jdbc.timezoneAsRegion = "false"
    }
  }
}

transform {
  sql {
    source_table_name = tbl
    result_table_name = t_tbl
    query = "select cast(F as decimal(12,2)) as F1 from tbl"
  }
}

sink {
  LocalFile {
    source_table_name = t_tbl
    path = "/tmp/hive/warehouse/test3"
    file_format_type = "parquet"
 }
}

from seatunnel.

This issue has been solved at #5872. If you consider upgrading, you can also use the latest version 2.3.5, so you don't need to use sql transform.

from seatunnel.

Great, the problem is solved, which means that I can directly use decimal(38,18) or decimal(38,0) to process any precision numbers in the future.

from seatunnel.

@hailin0 please close this issue.

from seatunnel.

This issue has been automatically marked as stale because it has not had recent activity for 30 days. It will be closed in next 7 days if no further activity occurs.

from seatunnel.