Text data type column not loading data for the values with hyphen and special charcter

Question

Hi ,

I have an external table with col1 data type text , when i load the table with the script create external table boa_external.test ( col1 text ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ( 'escapeChar'='\u0B83', 'quoteChar'='"', 'separatorChar'=',') STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://' TABLE PROPERTIES('skip.header.line.count'='1');

The values ending with <sup>1*†‡§</sup> is not loaded fully and looks as blank
The values afer - are not loaded and the whole values remains blank

would appreiciate if you could throw some light on it and would be of great help

Answer

Can you please confirm where are you trying to create the table ? If you are trying to create table in Athena with text datatype, Athena doesn't support text data type. For list of supported data types in Athena, please refer [1]. I tested it on my end and received below error

FAILED: ParseException line 1:34 cannot recognize input near 'text' ')' 'ROW' in column type
This query ran against the "default" database, unless qualified by the query. Please post the error message on our forum  or contact customer support

From the error it looks like the table DDL is unable to handle the data with special characters. If you are creating table with AWS Data Exchange, I would recommend please open a support ticket with our AWS Data Exchange queue and please provide some sample data for us to test and dive deep into the issue

REFERENCES:

[1] https://docs.aws.amazon.com/athena/latest/ug/data-types.html

Text data type column not loading data for the values with hyphen and special charcter

관련 콘텐츠