AWS Glue issue with double quote and commas

Question

I have this CSV file:

reference,address
V7T452F4H9,"12410 W 62TH ST, AA D"

The following options are being used in the table definition

ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
WITH SERDEPROPERTIES ( 
  'quoteChar'='\"', 
  'separatorChar'=',')

but it still won't recognize the double quotes in the data, and that comma in the double quote fiel is messing up the data. When I run the Athena query, the result looks like this

reference     address
V7T452F4H9    "12410 W 62TH ST

How do I fix this issue?

In case it saves someone else a bunch of searching, this is how I fixed it for my issue: https://stackoverflow.com/questions/57498330/specify-a-serde-serialization-lib-with-aws-glue-crawler Including the first comment to delete fields before re-running — Mike Karp, Jul 11 '22 at 02:05

score 12 · Answer 1 · edited Jul 16 '23 at 21:15

I do this to solve:

1 - Create a Crawler that doesn't overwrite the target table properties, I used boto3 for this but it can be created in AWS console too, by doing this (change the xxx-var):

import boto3

client = boto3.client('glue')

response = client.create_crawler(
    Name='xxx-Crawler-Name',
    Role='xxx-Put-here-your-rol',
    DatabaseName='xxx-databaseName',
    Description='xxx-Crawler description if u need it',
    Targets={
        'S3Targets': [
            {
                'Path': 's3://xxx-Path-to-s3/',
                'Exclusions': [
                ]
            },
        ]
    },
    SchemaChangePolicy={
        'UpdateBehavior': 'LOG',
        'DeleteBehavior': 'LOG'
    },
    Configuration='{ \
        "Version": 1.0, \
        "CrawlerOutput": { \
            "Partitions": {"AddOrUpdateBehavior": "InheritFromTable" \
            }, \
            "Tables": {"AddOrUpdateBehavior": "MergeNewColumns" } \
        }  \
    }'
)

# run the crawler
response = client.start_crawler(
    Name='xxx-Crawler-Name'
)

2 - Edit the serialization lib, I do this in AWS Console like say this post (https://docs.aws.amazon.com/athena/latest/ug/glue-best-practices.html#schema-csv-quotes) just change this:

3 - Run Crawler again. Run the crawler as always do:

4 - That's it, your 2nd run should not change any data in the table, it's just for testing that it's works ¯\(ツ)/¯.

Another useful link I came across --> https://docs.aws.amazon.com/athena/latest/ug/csv-serde.html — Hasani Blackwell, Jan 27 '21 at 15:29

score 11 · Accepted Answer · answered May 17 '18 at 05:01

Look like you also need to add escapeChar. AWS Athena docs shows this example:

CREATE EXTERNAL TABLE myopencsvtable (
   col1 string,
   col2 string,
   col3 string,
   col4 string
)
ROW FORMAT SERDE 
'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   'separatorChar' = ',',
   'quoteChar' = '\"',
   'escapeChar' = '\\'
   )
STORED AS TEXTFILE
LOCATION 's3://location/of/csv/';

AWS Glue issue with double quote and commas

2 Answers2

Linked

Related