The following query works fine:
LOAD DATA FROM S3 's3://{s3_bucket_name}/{s3_key}'
REPLACE
INTO TABLE {schema_name}.stg_tickets
CHARACTER SET utf8mb4
FIELDS
TERMINATED BY ','
ENCLOSED BY '"'
IGNORE 1 LINES
(id, url, @created_at, @updated_at, @external_id, @type, subject,
description, priority, status, recipient, requester_id,
submitter_id, assignee_id, @organization_id, @group_id,
collaborator_ids, follower_ids, @forum_topic_id, @problem_id,
@has_incidents, @is_public, @due_at, tags, sharing_agreement_ids,
fields, followup_ids, ticket_form_id, @allow_channelback,
generated_timestamp, via_channel, @via_source_to_name,
@via_source_to_address, @via_source_rel, satisfaction_rating_score,
@metric_set_id, @metric_set_url, @metric_set_created_at,
@metric_set_updated_at, metric_set_group_stations,
metric_set_assignee_stations, metric_set_reopens, metric_set_replies,
@metric_set_assignee_updated_at, @metric_set_requester_updated_at,
@metric_set_status_updated_at, @metric_set_initially_assigned_at,
@metric_set_assigned_at, @metric_set_solved_at, @metric_set_latest_comment_added_at,
@metric_set_reply_time_in_minutes_calendar, @metric_set_reply_time_in_minutes_business,
@metric_set_first_resolution_time_in_minutes_calendar,
@metric_set_first_resolution_time_in_minutes_business,
@metric_set_full_resolution_time_in_minutes_calendar,
@metric_set_full_resolution_time_in_minutes_business,
@metric_set_agent_wait_time_in_minutes_calendar,
@metric_set_agent_wait_time_in_minutes_business,
@metric_set_requester_wait_time_in_minutes_calendar,
@metric_set_requester_wait_time_in_minutes_business,
@metric_set_on_hold_time_in_minutes_calendar,
@metric_set_on_hold_time_in_minutes_business)
SET db_creation_date = current_timestamp, db_modify_date = current_timestamp,
has_incidents = case when @has_incidents = 'True' then 1 else 0 end,
is_public = case when @is_public = 'True' then 1 else 0 end,
allow_channelback = case when @allow_channelback = 'True' then 1 else 0 end,
type = NULLIF(@type, ''),
external_id = NULLIF(@external_id, ''),
forum_topic_id = NULLIF(@eforum_topic_id, ''),
problem_id = NULLIF(@problem_id, ''),
organization_id = NULLIF(@organization_id, ''),
metric_set_reply_time_in_minutes_calendar = NULLIF(@metric_set_reply_time_in_minutes_calendar, ''),
metric_set_reply_time_in_minutes_business = NULLIF(@metric_set_reply_time_in_minutes_business, ''),
metric_set_first_resolution_time_in_minutes_calendar = NULLIF(@metric_set_first_resolution_time_in_minutes_calendar, ''),
metric_set_first_resolution_time_in_minutes_business = NULLIF(@metric_set_first_resolution_time_in_minutes_business, ''),
metric_set_full_resolution_time_in_minutes_calendar = NULLIF(@metric_set_full_resolution_time_in_minutes_calendar, ''),
metric_set_full_resolution_time_in_minutes_business = NULLIF(@metric_set_full_resolution_time_in_minutes_business, ''),
metric_set_agent_wait_time_in_minutes_calendar = NULLIF(@metric_set_agent_wait_time_in_minutes_calendar, ''),
metric_set_agent_wait_time_in_minutes_business = NULLIF(@metric_set_agent_wait_time_in_minutes_business, ''),
metric_set_requester_wait_time_in_minutes_calendar = NULLIF(@metric_set_requester_wait_time_in_minutes_calendar, ''),
metric_set_requester_wait_time_in_minutes_business = NULLIF(@metric_set_requester_wait_time_in_minutes_business, ''),
metric_set_on_hold_time_in_minutes_calendar = NULLIF(@metric_set_on_hold_time_in_minutes_calendar, ''),
metric_set_on_hold_time_in_minutes_business = NULLIF(@metric_set_on_hold_time_in_minutes_business, ''),
via_source_to_name = NULLIF(@via_source_to_name, ''),
via_source_to_address = NULLIF(@via_source_to_address, ''),
via_source_rel = NULLIF(@via_source_rel, ''),
metric_set_id = NULLIF(@metric_set_id, ''),
metric_set_url = NULLIF(@metric_set_url, ''),
problem_id = NULLIF(@problem_id, ''),
created_at = NULLIF(@created_at, ''),
updated_at = NULLIF(@updated_at, ''),
due_at = NULLIF(@due_at, ''),
metric_set_created_at = NULLIF(@metric_set_created_at, ''),
metric_set_updated_at = NULLIF(@metric_set_updated_at, ''),
metric_set_assignee_updated_at = NULLIF(@metric_set_assignee_updated_at, ''),
metric_set_requester_updated_at = NULLIF(@metric_set_requester_updated_at, ''),
metric_set_status_updated_at = NULLIF(@metric_set_status_updated_at, ''),
metric_set_initially_assigned_at = NULLIF(@metric_set_initially_assigned_at, ''),
metric_set_assigned_at = NULLIF(@metric_set_assigned_at, ''),
metric_set_solved_at = NULLIF(@metric_set_solved_at, ''),
metric_set_latest_comment_added_at = NULLIF(@metric_set_latest_comment_added_at, '')
but as soon as I add “ESCAPED BY”,
FIELDS
TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\'
IGNORE 1 LINES
it gives me this error
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'True' then 1 else 0 end,
is_public = case when @is_public = ' at line 36
even though I am not changing anything in this part of the code. What am I missing out on? I need to handle special characters while loading data into MySQL.