As traduções são geradas por tradução automática. Em caso de conflito entre o conteúdo da tradução e da versão original em inglês, a versão em inglês prevalecerá.
Consultas de exemplo
Use esses exemplos de consultas para extrair e organizar os dados em seu catálogo de dados do Glue de análise de chamadas do SDK do Amazon Chime.
nota
Para obter informações sobre como se conectar ao Amazon Athena e consultar seu catálogo de dados do Glue, consulte Conexão ao Amazon Athena com ODBC.
Expanda cada seção conforme necessário.
call_analytics_metadata
tem o campo metadata
em formato de string JSON. Use a função json_extract_scalar no Athena para consultar os elementos dessa string.
SELECT json_extract_scalar(metadata,'$.voiceConnectorId') AS "VoiceConnector ID", json_extract_scalar(metadata,'$.fromNumber') AS "From Number", json_extract_scalar(metadata,'$.toNumber') AS "To Number", json_extract_scalar(metadata,'$.callId') AS "Call ID", json_extract_scalar(metadata,'$.direction') AS Direction, json_extract_scalar(metadata,'$.transactionId') AS "Transaction ID" FROM "GlueDatabaseName"."call_analytics_metadata"
O campo call_analytics_metadata
tem o campo de metadados em formato de string JSON. metadata
tem outro objeto aninhado chamado oneTimeMetadata
, que contém metadados de SIPRec nos formatos XML original e JSON transformado. Use a função json_extract_scalar
no Athena para consultar os elementos dessa string.
SELECT json_extract_scalar(metadata,'$.voiceConnectorId') AS "VoiceConnector ID", json_extract_scalar(metadata,'$.fromNumber') AS "From Number", json_extract_scalar(metadata,'$.toNumber') AS "To Number", json_extract_scalar(metadata,'$.callId') AS "Call ID", json_extract_scalar(metadata,'$.direction') AS Direction, json_extract_scalar(metadata,'$.transactionId') AS "Transaction ID", json_extract_scalar(json_extract_scalar(metadata,'$.oneTimeMetadata'),'$.siprecMetadata') AS "siprec Metadata XML", json_extract_scalar(json_extract_scalar(metadata,'$.oneTimeMetadata'),'$.siprecMetadataJson') AS "Siprec Metadata JSON", json_extract_scalar(json_extract_scalar(metadata,'$.oneTimeMetadata'),'$.inviteHeaders') AS "Invite Headers" FROM "GlueDatabaseName"."call_analytics_metadata" WHERE callevent-type = "update";
call_analytics_recording_metadata
tem o campo de metadados em formato de string JSON. Use a função json_extract_scalar no Athena para consultar os elementos dessa string.
SELECT json_extract_scalar(metadata,'$.voiceConnectorId') AS "VoiceConnector ID", json_extract_scalar(metadata,'$.fromNumber') AS "From Number", json_extract_scalar(metadata,'$.toNumber') AS "To Number", json_extract_scalar(metadata,'$.callId') AS "Call ID", json_extract_scalar(metadata,'$.direction') AS Direction, json_extract_scalar(metadata,'$.transactionId') AS "Transaction ID" FROM "GlueDatabaseName"."call_analytics_recording_metadata" WHERE detail-subtype = "Recording"
voice_analytics_status
tem um campo de detalhes no tipo de dados struct
. O exemplo a seguir mostra como consultar um campo de tipo de dados struct
:
SELECT detail.transactionId AS "Transaction ID", detail.voiceConnectorId AS "VoiceConnector ID", detail.siprecmetadata AS "Siprec Metadata", detail.inviteheaders AS "Invite Headers", detail.streamStartTime AS "Stream Start Time" FROM "GlueDatabaseName"."voice_analytics_status"
A consulta de exemplo a seguir une call_analytics_metadata
e voice_analytics_status
:
SELECT a.detail.transactionId AS "Transaction ID", a.detail.voiceConnectorId AS "VoiceConnector ID", a.detail.siprecmetadata AS "Siprec Metadata", a.detail.inviteheaders AS "Invite Headers", a.detail.streamStartTime AS "Stream Start Time" json_extract_scalar(b.metadata,'$.fromNumber') AS "From Number", json_extract_scalar(b.metadata,'$.toNumber') AS "To Number", json_extract_scalar(b.metadata,'$.callId') AS "Call ID", json_extract_scalar(b.metadata,'$.direction') AS Direction FROM "GlueDatabaseName"."voice_analytics_status" a INNER JOIN "GlueDatabaseName"."call_analytics_metadata" b ON a.detail.transactionId = json_extract_scalar(b.metadata,'$.transactionId')
transcribe_call_analytics_post_call tem campo de transcrição em formato de struct com matrizes aninhadas. Use a consulta a seguir para desaninhar as matrizes:
SELECT jobstatus, languagecode, IF(CARDINALITY(m.transcript)=0 OR CARDINALITY(m.transcript) IS NULL, NULL, e.transcript.id) AS utteranceId, IF(CARDINALITY(m.transcript)=0 OR CARDINALITY(m.transcript) IS NULL, NULL, e.transcript.content) AS transcript, accountid, channel, sessionid, contentmetadata.output AS "Redaction" FROM "GlueDatabaseName"."transcribe_call_analytics_post_call" m CROSS JOIN UNNEST (IF(CARDINALITY(m.transcript)=0, ARRAY[NULL], transcript)) AS e(transcript)
A consulta a seguir une transcribe_call_analytics_post_call e call_analytics_metadata:
WITH metadata AS( SELECT from_iso8601_timestamp(time) AS "Timestamp", date_parse(date_format(from_iso8601_timestamp(time), '%m/%d/%Y %H:%i:%s') , '%m/%d/%Y %H:%i:%s') AS "DateTime", date_parse(date_format(from_iso8601_timestamp(time) , '%m/%d/%Y') , '%m/%d/%Y') AS "Date", date_format(from_iso8601_timestamp(time) , '%H:%i:%s') AS "Time", mediainsightspipelineid, json_extract_scalar(metadata,'$.toNumber') AS "To Number", json_extract_scalar(metadata,'$.voiceConnectorId') AS "VoiceConnector ID", json_extract_scalar(metadata,'$.fromNumber') AS "From Number", json_extract_scalar(metadata,'$.callId') AS "Call ID", json_extract_scalar(metadata,'$.direction') AS Direction, json_extract_scalar(metadata,'$.transactionId') AS "Transaction ID", REGEXP_REPLACE(REGEXP_EXTRACT(json_extract_scalar(metadata,'$.oneTimeMetadata.s3RecordingUrl'), '[^/]+(?=\.[^.]+$)'), '\.wav$', '') AS "SessionID" FROM "GlueDatabaseName"."call_analytics_metadata" ), transcript_events AS( SELECT jobstatus, languagecode, IF(CARDINALITY(m.transcript)=0 OR CARDINALITY(m.transcript) IS NULL, NULL, e.transcript.id) AS utteranceId, IF(CARDINALITY(m.transcript)=0 OR CARDINALITY(m.transcript) IS NULL, NULL, e.transcript.content) AS transcript, accountid, channel, sessionid, contentmetadata.output AS "Redaction" FROM "GlueDatabaseName"."transcribe_call_analytics_post_call" m CROSS JOIN UNNEST (IF(CARDINALITY(m.transcript)=0, ARRAY[NULL], transcript)) AS e(transcript) ) SELECT jobstatus, languagecode, a.utteranceId, transcript, accountid, channel, a.sessionid, "Redaction" "Timestamp", "DateTime", "Date", "Time", mediainsightspipelineid, "To Number", "VoiceConnector ID", "From Number", "Call ID", Direction, "Transaction ID" FROM "GlueDatabaseName"."transcribe_call_analytics_post_call" a LEFT JOIN metadata b ON a.sessionid = b.SessionID
A consulta de exemplo a seguir une o URL Voice enhancement call recording
:
SELECT json_extract_scalar(metadata,'$.voiceConnectorId') AS "VoiceConnector ID", json_extract_scalar(metadata,'$.fromNumber') AS "From Number", json_extract_scalar(metadata,'$.toNumber') AS "To Number", json_extract_scalar(metadata,'$.callId') AS "Call ID", json_extract_scalar(metadata,'$.direction') AS Direction, json_extract_scalar(metadata,'$.transactionId') AS "Transaction ID", s3MediaObjectConsoleUrl FROM {GlueDatabaseName}."call_analytics_recording_metadata" WHERE detail-subtype = "VoiceEnhancement"