기계 번역으로 제공되는 번역입니다. 제공된 번역과 원본 영어의 내용이 상충하는 경우에는 영어 버전이 우선합니다.

# Amazon Chime SDK 통화 분석을 위해 데이터 카탈로그에서 AWS Glue 데이터 추출
<a name="ca-data-model-queries"></a>

이 샘플 쿼리를 사용하여 Amazon Chime SDK 통화 분석 Glue 데이터 카탈로그에서 데이터를 추출하고 구성할 수 있습니다.

**참고**  
Amazon Athena에 연결하고 Glue 데이터 카탈로그를 쿼리하는 방법에 대한 자세한 내용은 [ODBC로 Amazon Athena에 연결](https://docs.aws.amazon.com/athena/latest/ug/connect-with-odbc.html)을 참조하세요.

필요에 따라 각 섹션을 열어보세요.

## call\$1analytics\$1metadata 테이블의 메타데이터(STRING 데이터 유형)에서 값 추출
<a name="qry-insights-metadata"></a>

`call_analytics_metadata`에는 JSON 문자열 형식의 `metadata` 필드가 있습니다. 이 문자열의 요소를 쿼리하려면 Athena의 [json\$1extract\$1scalar 함수](https://docs.aws.amazon.com/athena/latest/ug/extracting-data-from-JSON.html)를 사용하세요.

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

## call\$1analytics\$1metadata 테이블에서 SIPRECMetadata 업데이트 쿼리
<a name="qry-insights-siprec-metadata"></a>

`call_analytics_metadata` 필드에는 JSON 문자열 형식의 메타데이터 필드가 있습니다. `metadata`에는 `oneTimeMetadata`라는 또 다른 중첩된 객체가 있습니다. 이 객체에는 원본 XML 및 변환된 JSON 형식의 SIPRec 메타데이터가 포함되어 있습니다. 이 문자열의 요소를 쿼리하려면 Athena의 `json_extract_scalar` 함수를 사용하세요.

```
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\$1analytics\$1recording\$1metadata 테이블의 메타데이터(문자열 데이터 유형)에서 값 추출
<a name="qry-recording-metadata"></a>

`call_analytics_recording_metadata`에는 JSON 문자열 형식의 메타데이터 필드가 있습니다. 이 문자열의 요소를 쿼리하려면 Athena의 [json\$1extract\$1scalar 함수](https://docs.aws.amazon.com/athena/latest/ug/extracting-data-from-JSON.html)를 사용하세요.

```
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\$1analytics\$1status 테이블의 세부 정보(구조체 데이터 유형)에서 값 추출
<a name="qry-va-status"></a>

`voice_analytics_status`에는 `struct` 데이터 유형에 세부 정보 필드가 있습니다. 다음 예에서는 `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"
```

## voice\$1analytics\$1status 및 call\$1analytics\$1metadata tables 조인
<a name="qry-join-va-meta"></a>

다음 예제 쿼리는`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')
```

## transcribe\$1call\$1analytics\$1post\$1call 테이블에서 대화 내용 추출하기
<a name="qry-transcribe-ca-post-call"></a>

transcribe\$1call\$1analytics\$1post\$1call에는 중첩된 배열이 있는 구조체 형식의 트랜스크립트 필드가 있습니다. 다음 쿼리를 사용하여 배열의 중첩을 해제하세요.

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

## transcribe\$1call\$1analytics\$1post\$1call 및 call\$1analytics\$1metadata 테이블 조인
<a name="qry-va-status"></a>

다음 쿼리는 transcribe\$1call\$1analytics\$1post\$1call 및 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
```

## 음성 향상 통화 레코딩을 위한 미디어 개체 URL 쿼리
<a name="qry-voice-enhancement-call-recording"></a>

다음 예제 쿼리는 `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"
```