serde配置单元表中的二进制摄取十进制

omqzjyyz  于 2021-06-24  发布在  Hive
关注(0)|答案(1)|浏览(543)

我正在检查是否可以用avro-serde更改配置单元中十进制字段的精度和比例,所以我编写了下面的代码。

  1. create database test_avro;
  2. use test_avro_table;
  3. create external table test_table(
  4. name string,
  5. salary decimal(17,2),
  6. country string
  7. )
  8. row format delimited
  9. fields terminated by ","
  10. STORED AS textfile;
  11. LOAD DATA LOCAL INPATH '/home/appsdesdssu/data/CACS_POC/data/' INTO TABLE
  12. test_table;
  13. create external table test_table_avro
  14. ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
  15. STORED AS INPUTFORMAT
  16. 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
  17. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
  18. tblproperties ('avro.schema.literal'='{
  19. "name": "my_record",
  20. "type": "record",
  21. "fields": [
  22. {"name":"name", "type":"string"},
  23. {"name":"salary","type": "bytes","logicalType": "decimal","precision":
  24. 17,"scale": 2},
  25. {"name":"country", "type":"string"}
  26. ]}');
  27. insert overwrite table test_table_avro select * from test_table;

在这里,我得到错误的说法

  1. FAILED: UDFArgumentException Only string, char, varchar or binary data can be cast into binary data types.

数据文件:

  1. steve,976475632987465.257,USA
  2. rogers,349643905318384.137,mexico
  3. groot,534563663653653.896,titan

如果我漏了什么,请告诉我。

wtzytmuj

wtzytmuj1#

到目前为止,配置单元还不支持十进制到二进制的版本。所以我们必须先把它转换成字符串,然后再转换成二进制

  1. insert overwrite table test_table_avro select * from test_table;

需要更改为

  1. insert overwrite table test_table_avro select name,cast(cast(salary as string) as binary),country from test_table;

相关问题