Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

# Extraction des données de votre catalogue de AWS Glue données pour l'analyse des appels du SDK Amazon Chime
<a name="ca-data-model-queries"></a>

Utilisez ces exemples de requêtes pour extraire et organiser les données de votre catalogue de données Glue pour l'analyse des appels du SDK Amazon Chime. 

**Note**  
Pour plus d'informations sur la connexion à Amazon Athena et l'interrogation de votre catalogue de données Glue, consultez la section [Connexion à Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/connect-with-odbc.html) avec ODBC.

Développez chaque section selon vos besoins.

## Extraction de valeurs à partir de métadonnées (type de données STRING) dans la table call\$1analytics\$1metadata
<a name="qry-insights-metadata"></a>

`call_analytics_metadata`contient le `metadata` champ au format de chaîne JSON. Utilisez la [fonction json\$1extract\$1scalar](https://docs.aws.amazon.com/athena/latest/ug/extracting-data-from-JSON.html) dans Athena pour interroger les éléments de cette chaîne.

```
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"
```

## Interrogation des SIPRECMetadata mises à jour dans la table call\$1analytics\$1metadata
<a name="qry-insights-siprec-metadata"></a>

Le `call_analytics_metadata` champ de métadonnées du champ est au format de chaîne JSON. `metadata`possède un autre objet imbriqué appelé`oneTimeMetadata`, cet objet contient des SIPRec métadonnées au format XML d'origine et au format JSON transformé. Utilisez la `json_extract_scalar` fonction d'Athena pour interroger les éléments de cette chaîne.

```
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";
```

## Extraction de valeurs à partir de métadonnées (type de données STRING) dans la table call\$1analytics\$1recording\$1metadata
<a name="qry-recording-metadata"></a>

`call_analytics_recording_metadata`possède le champ de métadonnées au format de chaîne JSON. Utilisez la [fonction json\$1extract\$1scalar](https://docs.aws.amazon.com/athena/latest/ug/extracting-data-from-JSON.html) dans Athena pour interroger les éléments de cette chaîne.

```
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"
```

## Extraction de valeurs à partir du détail (type de données STRUCT) dans la table voice\$1analytics\$1status
<a name="qry-va-status"></a>

`voice_analytics_status`comporte un champ de détails dans le type de `struct` données. L'exemple suivant montre comment interroger un champ de type de `struct` données :

```
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"
```

## Joindre les tables voice\$1analytics\$1status et call\$1analytics\$1metadata
<a name="qry-join-va-meta"></a>

L'exemple de requête suivant joint `call_analytics_metadata` et `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')
```

## Extraction des transcriptions de la table transcribe\$1call\$1analytics\$1post\$1call
<a name="qry-transcribe-ca-post-call"></a>

transcribe\$1call\$1analytics\$1post\$1call possède un champ de transcription au format structure avec des tableaux imbriqués. Utilisez la requête suivante pour dé-imbriquer les tableaux :

```
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)
```

## Joindre les tables transcribe\$1call\$1analytics\$1post\$1call et call\$1analytics\$1metadata
<a name="qry-va-status"></a>

La requête suivante joint transcribe\$1call\$1analytics\$1post\$1call et call\$1analytics\$1metadata :

```
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
```

## Interrogation d'un objet multimédia URLs pour l'enregistrement d'appels avec amélioration vocale
<a name="qry-voice-enhancement-call-recording"></a>

L'exemple de requête suivant joint `Voice enhancement call recording` l'URL :

```
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"
```