Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich.

# Extrahieren von Daten aus Ihrem AWS Glue Datenkatalog für Amazon Chime SDK-Anrufanalysen
<a name="ca-data-model-queries"></a>

Verwenden Sie diese Beispielabfragen, um die Daten in Ihrem Glue-Datenkatalog für Amazon Chime SDK Call Analytics zu extrahieren und zu organisieren. 

**Anmerkung**  
Informationen zum Herstellen einer Verbindung mit Amazon Athena und zum Abfragen Ihres Glue-Datenkatalogs finden Sie unter Herstellen einer [Verbindung zu Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/connect-with-odbc.html) mit ODBC.

Erweitern Sie jeden Abschnitt nach Bedarf.

## Extrahieren von Werten aus Metadaten (STRING-Datentyp) in der Tabelle call\_analytics\_metadata
<a name="qry-insights-metadata"></a>

`call_analytics_metadata`hat das Feld in einem JSON-Zeichenkettenformat. `metadata` Verwenden Sie die [Funktion json\_extract\_scalar in Athena, um die Elemente](https://docs.aws.amazon.com/athena/latest/ug/extracting-data-from-JSON.html) in dieser Zeichenfolge abzufragen.

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

## Abfragen von SIPrecMetadata-Aktualisierungen in der Tabelle call\_analytics\_metadata
<a name="qry-insights-siprec-metadata"></a>

Das Feld enthält das `call_analytics_metadata` Metadatenfeld in einem JSON-String-Format. `metadata`hat ein anderes verschachteltes Objekt namens`oneTimeMetadata`. Dieses Objekt enthält SIPrec-Metadaten im ursprünglichen XML- und transformierten JSON-Format. Verwenden Sie die `json_extract_scalar` Funktion in Athena, um die Elemente in dieser Zeichenfolge abzufragen.

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

## Extrahieren von Werten aus Metadaten (STRING-Datentyp) in der Tabelle call\_analytics\_recording\_metadata
<a name="qry-recording-metadata"></a>

`call_analytics_recording_metadata`hat das Metadatenfeld in einem JSON-String-Format. Verwenden Sie die [Funktion json\_extract\_scalar in Athena, um die Elemente](https://docs.aws.amazon.com/athena/latest/ug/extracting-data-from-JSON.html) in dieser Zeichenfolge abzufragen.

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

## Extrahieren von Werten aus Details (STRUCT-Datentyp) in der Tabelle voice\_analytics\_status
<a name="qry-va-status"></a>

`voice_analytics_status`hat ein Detailfeld im Datentyp. `struct` Das folgende Beispiel zeigt, wie ein `struct` Datentypfeld abgefragt wird:

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

## Zusammenführen der Tabellen voice\_analytics\_status und call\_analytics\_metadata
<a name="qry-join-va-meta"></a>

Die folgende Beispielabfrage verknüpft und: `call_analytics_metadata` `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')
```

## Extrahieren von Transkripten aus der Tabelle transcribe\_call\_analytics\_post\_call
<a name="qry-transcribe-ca-post-call"></a>

transcribe\_call\_analytics\_post\_call hat ein Transkriptfeld im Strukturformat mit verschachtelten Arrays. Verwenden Sie die folgende Abfrage, um die Verschachtelung der Arrays aufzuheben:

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

## Verknüpfen der Tabellen transcribe\_call\_analytics\_post\_call und call\_analytics\_metadata
<a name="qry-va-status"></a>

Die folgende Abfrage verbindet transcribe\_call\_analytics\_post\_call und 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
```

## Abfragen von Medienobjekt-URLs für die Aufzeichnung von Anrufen mit Sprachverbesserung
<a name="qry-voice-enhancement-call-recording"></a>

Die folgende Beispielabfrage verknüpft die `Voice enhancement call recording` 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"
```