从sql server导入，数据类型未正确转换

bejyjqdl  于 2021-05-29  发布在  Hadoop
关注(0)|答案(1)|浏览(531)

从sql server导入，数据类型未正确转换堆栈：已使用ambari 2.1安装hdp-2.3.2.0-2950
目标：
以avro格式将表从sqlserver导入hdfs
创建包含所有数据的外部配置单元avro（serde）表
创建外部配置单元orc表并插入orc select*from avro表
放下avro表，在orc表上执行测试
其中一张表：

ECU_DTC_ID          int
DTC_CDE             nchar(20)
ECU_NAME            nvarchar(15)
ECU_FAMILY_NAME     nvarchar(15)
DTC_DESC            nvarchar(MAX)
INSERTED_BY         nvarchar(64)
INSERTION_DATE      datetime
DTC_CDE_DECIMAL     int

当我执行普通的sqoop导入时，datetime被转换为long、nchar和nvarchar-to-string。生成的avsc文件如图所示，当我创建一个hive avro表时，它不包括生成的avro文件，因此留下一个空表：

{
  "type" : "record",
  "name" : "DimECUDTCCode",
  "doc" : "Sqoop import of DimECUDTCCode",
  "fields" : [ {
    "name" : "ECU_DTC_ID",
    "type" : [ "null", "int" ],
    "default" : null,
    "columnName" : "ECU_DTC_ID",
    "sqlType" : "4"
  }, {
    "name" : "DTC_CDE",
    "type" : [ "null", "string" ],
    "default" : null,
    "columnName" : "DTC_CDE",
    "sqlType" : "-15"
  }, {
    "name" : "ECU_NAME",
    "type" : [ "null", "string" ],
    "default" : null,
    "columnName" : "ECU_NAME",
    "sqlType" : "-9"
  }, {
    "name" : "ECU_FAMILY_NAME",
    "type" : [ "null", "string" ],
    "default" : null,
    "columnName" : "ECU_FAMILY_NAME",
    "sqlType" : "-9"
  }, {
    "name" : "DTC_DESC",
    "type" : [ "null", "string" ],
    "default" : null,
    "columnName" : "DTC_DESC",
    "sqlType" : "-9"
  }, {
    "name" : "INSERTED_BY",
    "type" : [ "null", "string" ],
    "default" : null,
    "columnName" : "INSERTED_BY",
    "sqlType" : "-9"
  }, {
    "name" : "INSERTION_DATE",
    "type" : [ "null", "long" ],
    "default" : null,
    "columnName" : "INSERTION_DATE",
    "sqlType" : "93"
  }, {
    "name" : "DTC_CDE_DECIMAL",
    "type" : [ "null", "int" ],
    "default" : null,
    "columnName" : "DTC_CDE_DECIMAL",
    "sqlType" : "4"
  } ],
  "tableName" : "DimECUDTCCode"

我决定包括--Map列java:

sqoop import --connect 'jdbc:sqlserver://somedbserver;database=somedb' --username someusername--password somepassword --as-avrodatafile --num-mappers 8 --table DimECUDTCCode --map-column-java DTC_CDE=string,ECU_NAME=string,ECU_FAMILY_NAME=string,DTC_DESC=string,INSERTED_BY=string,INSERTION_DATE=timestamp --warehouse-dir /dataload/tohdfs/reio/odpdw/may2016 --verbose

但我得到以下错误：

16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_CDE to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_NAME to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_FAMILY_NAME to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_DESC to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTED_BY to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTION_DATE to timestamp
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_CDE to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_NAME to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_FAMILY_NAME to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_DESC to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTED_BY to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTION_DATE to timestamp
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_CDE to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_NAME to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_FAMILY_NAME to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_DESC to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTED_BY to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTION_DATE to timestamp
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_CDE to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_NAME to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_FAMILY_NAME to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_DESC to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTED_BY to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTION_DATE to timestamp
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_CDE to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_NAME to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_FAMILY_NAME to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_DESC to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTED_BY to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTION_DATE to timestamp
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_CDE to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_NAME to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_FAMILY_NAME to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_DESC to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTED_BY to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTION_DATE to timestamp
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_CDE to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_NAME to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_FAMILY_NAME to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_DESC to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTED_BY to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTION_DATE to timestamp
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_CDE to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_NAME to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_FAMILY_NAME to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_DESC to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTED_BY to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTION_DATE to timestamp
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_CDE to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_NAME to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_FAMILY_NAME to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_DESC to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTED_BY to string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTION_DATE to timestamp
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_CDE to string
16/05/12 09:43:12 ERROR orm.ClassWriter: No ResultSet method for Java type string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_NAME to string
16/05/12 09:43:12 ERROR orm.ClassWriter: No ResultSet method for Java type string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column ECU_FAMILY_NAME to string
16/05/12 09:43:12 ERROR orm.ClassWriter: No ResultSet method for Java type string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_DESC to string
16/05/12 09:43:12 ERROR orm.ClassWriter: No ResultSet method for Java type string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTED_BY to string
16/05/12 09:43:12 ERROR orm.ClassWriter: No ResultSet method for Java type string
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column INSERTION_DATE to timestamp
16/05/12 09:43:12 ERROR orm.ClassWriter: No ResultSet method for Java type timestamp
16/05/12 09:43:12 INFO orm.ClassWriter: Overriding type of column DTC_CDE to string
16/05/12 09:43:12 ERROR tool.ImportTool: Imported Failed: No ResultSet method for Java type string
[sqoop@l1038lab root]$

我错过了什么？

hadoopHivesqoopavro

1条答案

按热度按时间
sycxhyv7

sycxhyv71#

事实证明 STRING 以及 String 以及 string 被sqoop区别对待。正确的方法是 String .

赞(0）分享  回复(0）举报  2021-05-29
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com