Dear Community,
I have uploaded the menifest file with prefix path to the s3 bucket folder. but the thing is when I upload it and check the data set it does not show some records that are present in the json files. I am trying to visualize data of contact lens that are stored in s3 bucket path analysis. I don’t know why quicksight dataset is not able to detect all json file records.
Is there a way we can solve it?
Here is the record data:
Thanks and Regards
Hi @musama
Thank you for your question.
Please can you check the link below for JSON format for your manifest file
Regards,
Demola
Yes I have added the json format in menifest file. Additionally is there a way we can define schema for our files so that quicksight extract data accordingly?
Hi @musama,
Have you tried to edit your dataset?
Please find more information on the link below.
Regards,
Demola
Yes @demola I have tried the edit option for data set that is coming from s3. but the thing is s3 json files schema is not being detected correctly and some records come missing in the dataset as shown in the image above.
Can you help me regarding that?
Regards
Hi @musama - Can you please share sample JSON data so that we can guide you.
Regards - Sanjeeb
@Sanjeeb2022 I have following kind of json data which value is not being read in the dataset:
first record is being stored but the following json records are not being read in quicksight.
{
“Version”: “CHAT-2022-11-30”,
“AccountId”: “747926693930”,
“Channel”: “CHAT”,
“JobStatus”: “COMPLETED”,
“LanguageCode”: “en-US”,
“CustomerMetadata”: {
“InputS3Uri”: “s3://new-bucket-for-metrics/connect/ob/queuesHR/2023/06/22/e485d68-d21a20acf046_20230622T12:12_UTC.json”,
“ContactId”: “e485d619-6f90-”,
“InstanceId”: “84ecefa9-18d04”
},
“ChatTranscriptVersion”: “2019-08-26”,
“ContentMetadata”: {
“Output”: “Raw”
},
“Transcript”: [
{
“AbsoluteTime”: “2023-06-22T12:12:32.136Z”,
“ContentType”: “application/vnd.amazonaws.connect.event.participant.joined”,
“Id”: “e061f4d2-a3b3-421d-b0fc-aab666388505”,
“Type”: “EVENT”,
“ParticipantId”: “9b0e502a-c3b4-453e-b12c-c125bedefe75”,
“DisplayName”: “+923457560837”,
“ParticipantRole”: “CUSTOMER”
},
{
“AbsoluteTime”: “2023-06-22T12:12:34.035Z”,
“Content”: “Welcome to Octave Lab”,
“ContentType”: “text/plain”,
“Id”: “c935aaad-265e-4eea-a3e3-6dd9a6071208”,
“Type”: “MESSAGE”,
“ParticipantId”: “34f70ace-1eb9-435e-925b-56345be7235b”,
“DisplayName”: “SYSTEM_MESSAGE”,
“ParticipantRole”: “SYSTEM”
},
{
“AbsoluteTime”: “2023-06-22T12:12:40.922Z”,
“ContentType”: “application/vnd.amazonaws.connect.event.participant.joined”,
“Id”: “c72fa95b-c81f-4e93-ab0a-682f6f0d1ff2”,
“Type”: “EVENT”,
“ParticipantId”: “958d6d51-1b3d-46de-8fe5-20925a026e39”,
“DisplayName”: “usama”,
“ParticipantRole”: “AGENT”
},
{
“AbsoluteTime”: “2023-06-22T12:12:41.128Z”,
“Content”: “Hi”,
“ContentType”: “text/markdown”,
“Id”: “51584da3-f9dd-4f77-b5cd-72193f6db0fb”,
“Type”: “MESSAGE”,
“ParticipantId”: “9b0e502a-c3b4-453e-b12c-c125bedefe75”,
“DisplayName”: “+923457560837”,
“ParticipantRole”: “CUSTOMER”
},
{
“AbsoluteTime”: “2023-06-22T12:12:43.677Z”,
“Content”: “es”,
“ContentType”: “text/markdown”,
“Id”: “c2c62715-7e6a-4f00-83b5-77d11a9d75e4”,
“Type”: “MESSAGE”,
“ParticipantId”: “958d6d51-1b3d-46de-8fe5-20925a026e39”,
“DisplayName”: “usama”,
“ParticipantRole”: “AGENT”
},
{
“AbsoluteTime”: “2023-06-22T12:12:44.754Z”,
“Content”: “No”,
“ContentType”: “text/markdown”,
“Id”: “4adc6a62-78fa-4da1-81ce-0587f9446a92”,
“Type”: “MESSAGE”,
“ParticipantId”: “9b0e502a-c3b4-453e-b12c-c125bedefe75”,
“DisplayName”: “+923457560837”,
“ParticipantRole”: “CUSTOMER”
},
{
“AbsoluteTime”: “2023-06-22T12:12:48.258Z”,
“Content”: “yes”,
“ContentType”: “text/markdown”,
“Id”: “3fc75973-7bf1-4ef6-a25a-caa532cdadc2”,
“Type”: “MESSAGE”,
“ParticipantId”: “958d6d51-1b3d-46de-8fe5-20925a026e39”,
“DisplayName”: “usama”,
“ParticipantRole”: “AGENT”
},
{
“AbsoluteTime”: “2023-06-22T12:12:50.824Z”,
“Content”: “Ues”,
“ContentType”: “text/markdown”,
“Id”: “d69c8338-e5f2-4b2b-a44a-52af8235f813”,
“Type”: “MESSAGE”,
“ParticipantId”: “9b0e502a-c3b4-453e-b12c-c125bedefe75”,
“DisplayName”: “+923457560837”,
“ParticipantRole”: “CUSTOMER”
},
{
“AbsoluteTime”: “2023-06-22T12:12:52.915Z”,
“Content”: “Yes”,
“ContentType”: “text/markdown”,
“Id”: “47c1b26b-a8ae-421a-ae00-69d04be34998”,
“Type”: “MESSAGE”,
“ParticipantId”: “9b0e502a-c3b4-453e-b12c-c125bedefe75”,
“DisplayName”: “+923457560837”,
“ParticipantRole”: “CUSTOMER”
},
{
“AbsoluteTime”: “2023-06-22T12:12:55.163Z”,
“Content”: “okay”,
“ContentType”: “text/markdown”,
“Id”: “6861c8d9-8cfe-47ed-844d-2636b223f4cd”,
“Type”: “MESSAGE”,
“ParticipantId”: “958d6d51-1b3d-46de-8fe5-20925a026e39”,
“DisplayName”: “usama”,
“ParticipantRole”: “AGENT”
},
{
“AbsoluteTime”: “2023-06-22T12:12:57.192Z”,
“Content”: “bye”,
“ContentType”: “text/markdown”,
“Id”: “789f883d-0809-4cd2-979e-f26a42e28561”,
“Type”: “MESSAGE”,
“ParticipantId”: “958d6d51-1b3d-46de-8fe5-20925a026e39”,
“DisplayName”: “usama”,
“ParticipantRole”: “AGENT”
},
{
“AbsoluteTime”: “2023-06-22T12:13:00.127Z”,
“Content”: “Bye”,
“ContentType”: “text/markdown”,
“Id”: “81a5385f-c77b-4263-8039-1e64375d28d3”,
“Type”: “MESSAGE”,
“ParticipantId”: “9b0e502a-c3b4-453e-b12c-c125bedefe75”,
“DisplayName”: “+923457560837”,
“ParticipantRole”: “CUSTOMER”
},
{
“AbsoluteTime”: “2023-06-22T12:13:02.032Z”,
“ContentType”: “application/vnd.amazonaws.connect.event.participant.left”,
“Id”: “fbd7db51-15ca-4e05-88d3-a3cdfc773af2”,
“Type”: “EVENT”,
“ParticipantId”: “958d6d51-1b3d-46de-8fe5-20925a026e39”,
“DisplayName”: “usama”,
“ParticipantRole”: “AGENT”
},
{
“AbsoluteTime”: “2023-06-22T12:13:02.713Z”,
“ContentType”: “application/vnd.amazonaws.connect.event.chat.ended”,
“Id”: “f0e67ba0-3205-4c04-a82d-dd57c24a6b9f”,
“Type”: “EVENT”
}
],
“Categories”: {
“MatchedCategories”: [
],
"MatchedDetails": {
}
},
“Participants”: [
{
“ParticipantId”: “9b0e502a-c3b4-453e-b12c-c125bedefe75”,
“ParticipantRole”: “CUSTOMER”
},
{
“ParticipantId”: “34f70ace-1eb9-435e-925b-56345be7235b”,
“ParticipantRole”: “SYSTEM”
},
{
“ParticipantId”: “958d6d51-1b3d-46de-8fe5-20925a026e39”,
“ParticipantRole”: “AGENT”
}
],
“ConversationCharacteristics”: {
“ContactSummary”: {
“SummaryItemsDetected”: [
{
“IssuesDetected”: [
],
"OutcomesDetected": [
],
"ActionItemsDetected": [
]
}
]
},
"ResponseTime": {
"AgentGreetingTimeMillis": 2755,
"DetailsByParticipantRole": {
"AGENT": {
"Average": {
"ValueMillis": 2767
},
"Maximum": {
"ValueMillis": 3504,
"TranscriptItems": [
{
"Id": "3fc75973-7bf1-4ef6-a25a-caa532cdadc2"
}
]
}
},
"CUSTOMER": {
"Average": {
"ValueMillis": 2193
},
"Maximum": {
"ValueMillis": 2935,
"TranscriptItems": [
{
"Id": "81a5385f-c77b-4263-8039-1e64375d28d3"
}
]
}
}
}
},
"Sentiment": {
"OverallSentiment": {
"DetailsByInteraction": {
"DetailsByParticipantRole": {
"CUSTOMER": {
"WithAgent": 0
}
}
},
"DetailsByParticipantRole": {
"AGENT": 0,
"CUSTOMER": 0,
"SYSTEM": 0
}
},
"SentimentShift": {
"DetailsByInteraction": {
"DetailsByParticipantRole": {
"CUSTOMER": {
"WithAgent": {
"BeginScore": 0,
"EndScore": 0
}
}
}
},
"DetailsByParticipantRole": {
"AGENT": {
"BeginScore": 0,
"EndScore": 0
},
"CUSTOMER": {
"BeginScore": 0,
"EndScore": 0
},
"SYSTEM": {
"BeginScore": 0,
"EndScore": 0
}
}
},
"DetailsByTranscriptItemGroup": [
{
"ParticipantRole": "CUSTOMER",
"ProgressiveScore": 0,
"Sentiment": "NEUTRAL",
"TranscriptItems": [
{
"Id": "51584da3-f9dd-4f77-b5cd-72193f6db0fb"
}
]
},
{
"ParticipantRole": "CUSTOMER",
"ProgressiveScore": 0,
"Sentiment": "NEUTRAL",
"TranscriptItems": [
{
"Id": "4adc6a62-78fa-4da1-81ce-0587f9446a92"
}
]
},
{
"ParticipantRole": "CUSTOMER",
"ProgressiveScore": 0,
"Sentiment": "NEUTRAL",
"TranscriptItems": [
{
"Id": "d69c8338-e5f2-4b2b-a44a-52af8235f813"
}
]
},
{
"ParticipantRole": "CUSTOMER",
"ProgressiveScore": 0,
"Sentiment": "NEUTRAL",
"TranscriptItems": [
{
"Id": "47c1b26b-a8ae-421a-ae00-69d04be34998"
}
]
},
{
"ParticipantRole": "CUSTOMER",
"ProgressiveScore": 0,
"Sentiment": "NEUTRAL",
"TranscriptItems": [
{
"Id": "81a5385f-c77b-4263-8039-1e64375d28d3"
}
]
},
{
"ParticipantRole": "AGENT",
"ProgressiveScore": 0,
"Sentiment": "NEUTRAL",
"TranscriptItems": [
{
"Id": "c2c62715-7e6a-4f00-83b5-77d11a9d75e4"
}
]
},
{
"ParticipantRole": "AGENT",
"ProgressiveScore": 0,
"Sentiment": "NEUTRAL",
"TranscriptItems": [
{
"Id": "3fc75973-7bf1-4ef6-a25a-caa532cdadc2"
}
]
},
{
"ParticipantRole": "AGENT",
"ProgressiveScore": 0,
"Sentiment": "NEUTRAL",
"TranscriptItems": [
{
"Id": "6861c8d9-8cfe-47ed-844d-2636b223f4cd"
},
{
"Id": "789f883d-0809-4cd2-979e-f26a42e28561"
}
]
}
]
}
}
}
Hi @musama - For JSON data, the best way to dump the data in S3 and create Athena table and parse the JSON data and used Athena as the source . Please see the link below - Analyze and visualize nested JSON data with Amazon Athena and Amazon QuickSight | AWS Big Data Blog
Regards - Sanjeeb
@Sanjeeb2022 Thanks for your input. Going with that approach. Your help is appreciated.
Regards