본문으로 건너뛰기

DNS 리졸버 쿼리 로그를 Data Query 서비스를 통해 조회

Object Storage에 저장된 DNS 리졸버 쿼리 로그를 Data Catalog와 Data Query를 활용해 SQL 기반으로 조회하는 방법을 안내합니다.

기본 정보

시나리오 소개​

이 튜토리얼에서는 VPC 내 DNS 리졸버의 쿼리 로그를 Object Storage에 저장한 뒤, Data Catalog와 Data Query를 활용해 SQL 기반으로 로그를 조회하는 방법을 단계별로 안내합니다.

주요 내용은 다음과 같습니다.

  • DNS 리졸버 쿼리 로그를 Object Storage 버킷에 저장 설정
  • Data Catalog에서 로그 버킷과 연결된 데이터베이스 생성
  • Data Query에서 테이블 생성, 파티션 동기화 및 로그 데이터 조회
  • (부록) 활용 쿼리 예시

시작하기 전에​

DNS 리졸버 쿼리 로그를 Data Query로 조회하려면 Object Storage 버킷이 먼저 생성되어 있어야 합니다. Object Storage 버킷 생성 가이드를 참고하여 버킷을 준비한 후 아래 단계를 진행하세요.

시작하기​

Step 1. DNS 리졸버 쿼리 로그 저장 활성화​

DNS 리졸버 쿼리 로깅 설정 가이드를 참고하여 VPC 내 DNS 리졸버에 쿼리 로깅을 설정합니다.

Step 2. Data Catalog 설정​

Data Catalog 서비스에서 카탈로그와, 로그가 저장되고 있는 버킷과 연결된 데이터베이스를 생성합니다.

  1. 카카오클라우드 콘솔 > Analytics > Data Catalog 메뉴로 이동합니다.

  2. 카탈로그 메뉴에서 [카탈로그 생성] 버튼을 클릭합니다.

  3. 카탈로그 생성 팝업창에서 아래 정보를 입력한 후, [생성] 버튼을 클릭합니다.

    구분설정값
    유형Standard 카탈로그
    이름카탈로그 이름 (예: catalog)
    VPC 설정카탈로그에 해당하는 네트워크 선택 (VPC-서브넷당 1개만 생성 가능)

  4. 생성한 카탈로그(catalog)를 클릭하여 데이터베이스 목록에서 [데이터베이스 생성] 버튼을 클릭합니다.

  5. 데이터베이스 생성 팝업창에서 아래 정보를 입력한 후, [생성] 버튼을 클릭합니다.

    구분설정값
    유형Standard 데이터베이스
    카탈로그위에서 생성한 catalog 선택
    이름데이터베이스 이름 (예: dns_query)
    경로s3 연결: 체크 / 버킷 이름: Object Storage 버킷 이름 입력 / 디렉터리: KCLogs/kr-central-2
    속성 (선택)-
    설명 (선택)-
    참고

    Object Storage에 DNS 리졸버 쿼리 로그는 지정된 버킷에 아래와 같은 형식으로 적재됩니다.

    {bucket-name}/KCLogs/{region-name}/{year=yyyy/month=mm/day=dd}/{az-name}_{project-id}_{vpc-id}_{logger-id}_{start-time}_{end-time}.log.gz

Step 3. Data Query 조회​

Data Catalog에서 생성한 데이터베이스를 통해 테이블을 만들고 파티션을 동기화한 뒤, 로그 데이터를 조회합니다.

  1. 카카오클라우드 콘솔 > Analytics > Data Query > 쿼리 편집기 메뉴로 이동합니다.

  2. 아래와 같이 데이터 원본과 데이터베이스를 선택합니다.

    구분설정값
    데이터 원본Data Catalog에서 생성한 카탈로그 catalog 선택
    데이터베이스Data Catalog에서 생성한 데이터베이스 dns_query 선택

  3. 우측의 쿼리 편집기에서 테이블을 생성합니다.

    테이블 생성
    CREATE TABLE ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME} (
        availability_zone varchar,
        project_id varchar,
        query_class varchar,
        query_name varchar,
        record_type varchar,
        region varchar,
        response_code varchar,
        response_count varchar,
        response_protocol varchar,
        response_size varchar,
        response_time varchar,
        timestamp varchar,
        version varchar,
        vpc_id varchar,
        year varchar,
        month varchar,
        day varchar
    )
    WITH (
        format = 'JSON',
        external_location = 's3a://${BUCKET_NAME}/KCLogs/kr-central-2', -- 현재 kr-central-2 리전만 지원
        partitioned_by = ARRAY['year', 'month', 'day']
    );
    환경변수설명
    CATALOG_NAME🖌︎ Data Catalog에서 생성한 카탈로그 이름
    DATABASE_NAME🖌︎ Data Catalog에서 생성한 데이터베이스 이름
    TABLE_NAME🖌︎ 테이블 이름
    BUCKET_NAME🖌︎ 버킷 이름

  4. 새로운 쿼리 탭을 열어 파티션 동기화 쿼리를 실행합니다. 테이블 생성 쿼리와 같은 탭에서 실행하면 파티션이 정상적으로 인식되지 않을 수 있습니다.

    파티션 동기화
    CALL system.sync_partition_metadata(
        schema_name => '${DATABASE_NAME}',
        table_name => '${TABLE_NAME}',
        mode => 'FULL',
        case_sensitive => true
    );
    환경변수설명
    DATABASE_NAME🖌︎ Data Catalog에서 생성한 데이터베이스 이름
    TABLE_NAME🖌︎ 테이블 이름

  5. 파티션 동기화 성공 후, 파티션 조회를 진행합니다.

    파티션 조회
    SELECT *
    FROM ${CATALOG_NAME}.${DATABASE_NAME}."${TABLE_NAME}$partitions";
    환경변수설명
    CATALOG_NAME🖌︎ Data Catalog에서 생성한 카탈로그 이름
    DATABASE_NAME🖌︎ Data Catalog에서 생성한 데이터베이스 이름
    TABLE_NAME🖌︎ 테이블 이름

    쿼리 결과로 year, month, day별 파티션이 조회되면 정상입니다.

부록​

부록 1. 쿼리 예시​

1. 특정 날짜 전체 로그 조회
SELECT *
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE year = '${YEAR}' AND month = '${MONTH}' AND day = '${DAY}';
환경변수설명
CATALOG_NAME🖌︎ Data Catalog에서 생성한 카탈로그 이름
DATABASE_NAME🖌︎ Data Catalog에서 생성한 데이터베이스 이름
TABLE_NAME🖌︎ 테이블 이름
YEAR🖌︎ 연도, 예: 2026
MONTH🖌︎ 월, 예: 04
DAY🖌︎ 일, 예: 30
2. query_name별 DNS 쿼리 수 - 상위 10개
SELECT query_name, COUNT(*) AS query_count
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE year = '${YEAR}' AND month = '${MONTH}' AND day = '${DAY}'
GROUP BY query_name
ORDER BY query_count DESC
LIMIT 10;
환경변수설명
CATALOG_NAME🖌︎ Data Catalog에서 생성한 카탈로그 이름
DATABASE_NAME🖌︎ Data Catalog에서 생성한 데이터베이스 이름
TABLE_NAME🖌︎ 테이블 이름
YEAR🖌︎ 연도, 예: 2026
MONTH🖌︎ 월, 예: 04
DAY🖌︎ 일, 예: 30
3. 시간대별 DNS 쿼리 실패 수
SELECT
    SUBSTR("timestamp", 12, 2) AS hour,
    COUNT(*) AS failed_query_count
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE year = '${YEAR}'
    AND month = '${MONTH}'
    AND day = '${DAY}'
    AND response_code != 'NOERROR'
GROUP BY SUBSTR("timestamp", 12, 2)
ORDER BY failed_query_count DESC
LIMIT 10;
환경변수설명
CATALOG_NAME🖌︎ Data Catalog에서 생성한 카탈로그 이름
DATABASE_NAME🖌︎ Data Catalog에서 생성한 데이터베이스 이름
TABLE_NAME🖌︎ 테이블 이름
YEAR🖌︎ 연도, 예: 2026
MONTH🖌︎ 월, 예: 04
DAY🖌︎ 일, 예: 30
4. 실패가 많은 도메인 TOP 5
SELECT
    query_name,
    COUNT(*) AS failed_count
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE year = '${YEAR}'
    AND month = '${MONTH}'
    AND day = '${DAY}'
    AND response_code != 'NOERROR'
GROUP BY query_name
ORDER BY failed_count DESC
LIMIT 5;
환경변수설명
CATALOG_NAME🖌︎ Data Catalog에서 생성한 카탈로그 이름
DATABASE_NAME🖌︎ Data Catalog에서 생성한 데이터베이스 이름
TABLE_NAME🖌︎ 테이블 이름
YEAR🖌︎ 연도, 예: 2026
MONTH🖌︎ 월, 예: 04
DAY🖌︎ 일, 예: 30
5. 응답 시간이 오래 걸린 쿼리 TOP 10 (오류 제외)
SELECT
    query_name,
    response_time,
    response_code,
    response_size,
    response_protocol
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE year = '${YEAR}'
    AND month = '${MONTH}'
    AND day = '${DAY}'
    AND regexp_like(response_time, '^[0-9]+(\.[0-9]+)?$')
ORDER BY CAST(response_time AS DOUBLE) DESC
LIMIT 10;
환경변수설명
CATALOG_NAME🖌︎ Data Catalog에서 생성한 카탈로그 이름
DATABASE_NAME🖌︎ Data Catalog에서 생성한 데이터베이스 이름
TABLE_NAME🖌︎ 테이블 이름
YEAR🖌︎ 연도, 예: 2026
MONTH🖌︎ 월, 예: 04
DAY🖌︎ 일, 예: 30
6. 특정 query_name을 비정상적으로 많이 요청한 VPC
-- HAVING COUNT(*) > 1000은 기준값이므로 환경에 맞게 조정 필요
SELECT
    vpc_id,
    query_name,
    COUNT(*) AS query_count
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE year = '${YEAR}'
    AND month = '${MONTH}'
    AND day = '${DAY}'
GROUP BY vpc_id, query_name
HAVING COUNT(*) > 1000
ORDER BY query_count DESC
LIMIT 10;
환경변수설명
CATALOG_NAME🖌︎ Data Catalog에서 생성한 카탈로그 이름
DATABASE_NAME🖌︎ Data Catalog에서 생성한 데이터베이스 이름
TABLE_NAME🖌︎ 테이블 이름
YEAR🖌︎ 연도, 예: 2026
MONTH🖌︎ 월, 예: 04
DAY🖌︎ 일, 예: 30
7. 시간대별 DNS 쿼리 사용량 (1일 기준)
SELECT
    SUBSTR("timestamp", 12, 2) AS hour,
    COUNT(*) AS query_count
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE year = '${YEAR}'
    AND month = '${MONTH}'
    AND day = '${DAY}'
GROUP BY SUBSTR("timestamp", 12, 2)
ORDER BY hour;
환경변수설명
CATALOG_NAME🖌︎ Data Catalog에서 생성한 카탈로그 이름
DATABASE_NAME🖌︎ Data Catalog에서 생성한 데이터베이스 이름
TABLE_NAME🖌︎ 테이블 이름
YEAR🖌︎ 연도, 예: 2026
MONTH🖌︎ 월, 예: 04
DAY🖌︎ 일, 예: 30
8. 일별 DNS 쿼리 사용량 (월간 기준)
SELECT
    day,
    COUNT(*) AS query_count
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE year = '${YEAR}'
    AND month = '${MONTH}'
GROUP BY day
ORDER BY day;
환경변수설명
CATALOG_NAME🖌︎ Data Catalog에서 생성한 카탈로그 이름
DATABASE_NAME🖌︎ Data Catalog에서 생성한 데이터베이스 이름
TABLE_NAME🖌︎ 테이블 이름
YEAR🖌︎ 연도, 예: 2026
MONTH🖌︎ 월, 예: 04
9. 응답 코드별 요청 분포
SELECT
    response_code,
    COUNT(*) AS request_count
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE year = '${YEAR}'
    AND month = '${MONTH}'
    AND day = '${DAY}'
GROUP BY response_code
ORDER BY request_count DESC;
환경변수설명
CATALOG_NAME🖌︎ Data Catalog에서 생성한 카탈로그 이름
DATABASE_NAME🖌︎ Data Catalog에서 생성한 데이터베이스 이름
TABLE_NAME🖌︎ 테이블 이름
YEAR🖌︎ 연도, 예: 2026
MONTH🖌︎ 월, 예: 04
DAY🖌︎ 일, 예: 30
10. 의심스러운 쿼리 패턴 탐지
-- query_name에 DROP, DELETE, UNION 키워드가 포함된 의심 쿼리 집계
SELECT
    query_name,
    COUNT(*) AS occurrence
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE year = '${YEAR}'
    AND month = '${MONTH}'
    AND day = '${DAY}'
    AND (query_name LIKE '%DROP%' OR query_name LIKE '%DELETE%' OR query_name LIKE '%UNION%')
GROUP BY query_name
ORDER BY occurrence DESC;

-- record_type이 TXT이고 비정상적으로 긴 query_name 확인 (기준값 100자는 환경에 맞게 조정 필요)
SELECT
    query_name,
    response_size,
    response_code,
    LENGTH(query_name) AS query_length
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE year = '${YEAR}'
    AND month = '${MONTH}'
    AND day = '${DAY}'
    AND record_type = 'TXT'
    AND LENGTH(query_name) > 100
ORDER BY query_length DESC
LIMIT 20;
환경변수설명
CATALOG_NAME🖌︎ Data Catalog에서 생성한 카탈로그 이름
DATABASE_NAME🖌︎ Data Catalog에서 생성한 데이터베이스 이름
TABLE_NAME🖌︎ 테이블 이름
YEAR🖌︎ 연도, 예: 2026
MONTH🖌︎ 월, 예: 04
DAY🖌︎ 일, 예: 30
11. 쿼리 유형(record_type) 분포
SELECT
    record_type,
    COUNT(*) AS query_count
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE year = '${YEAR}'
    AND month = '${MONTH}'
    AND day = '${DAY}'
GROUP BY record_type
ORDER BY query_count DESC;
환경변수설명
CATALOG_NAME🖌︎ Data Catalog에서 생성한 카탈로그 이름
DATABASE_NAME🖌︎ Data Catalog에서 생성한 데이터베이스 이름
TABLE_NAME🖌︎ 테이블 이름
YEAR🖌︎ 연도, 예: 2026
MONTH🖌︎ 월, 예: 04
DAY🖌︎ 일, 예: 30