NYPD 신고 데이터
NYPD 신고 데이터 (NYPD complaint data)
NYC Open Data 팀이 제공하는 뉴욕시 경찰국(NYPD)에 신고된 모든 주요 범죄(중죄, 경범죄, 위반) 데이터를 다루는 가이드입니다. TSV 파일을 clickhouse-local로 조사하고 스키마를 설계한 뒤, 전처리해 ClickHouse로 스트리밍하고 분석합니다.
출처: 문서
본문
탭 구분 값(TSV) 파일은 흔하며 파일 첫 줄에 필드 헤더를 포함할 수 있습니다. ClickHouse는 TSV를 수집할 수 있고, 수집 없이도 TSV를 쿼리할 수 있습니다. 이 가이드는 두 경우 모두를 다룹니다. CSV 파일을 쿼리하거나 수집해야 한다면 같은 기법이 동작하며, 형식 인자에서 TSV를 CSV로 바꾸기만 하면 됩니다.
이 가이드를 진행하면서 다음을 수행합니다:
- 조사하기: TSV 파일의 구조와 내용을 쿼리합니다.
- 대상 ClickHouse 스키마 결정: 적절한 데이터 타입을 선택하고 기존 데이터를 그 타입에 매핑합니다.
- ClickHouse 테이블 생성.
- 데이터 전처리 및 스트리밍을 ClickHouse로 수행합니다.
- ClickHouse에서 몇 가지 쿼리 실행.
이 가이드에 사용된 데이터셋은 NYC Open Data 팀에서 제공하며, "뉴욕시 경찰국(NYPD)에 신고된 모든 유효한 중죄, 경범죄, 위반 범죄"에 대한 데이터를 포함합니다. 작성 시점에 데이터 파일은 166MB이지만 정기적으로 갱신됩니다.
출처: data.cityofnewyork.us 이용 약관: https://www1.nyc.gov/home/terms-of-use.page
전제 조건 (Prerequisites)
- NYPD Complaint Data Current (Year To Date) 페이지를 방문해 Export 버튼을 클릭하고 TSV for Excel을 선택해 데이터셋을 내려받으세요.
- ClickHouse 서버와 클라이언트 설치
이 가이드에 설명된 명령에 대한 참고
이 가이드에는 두 가지 유형의 명령이 있습니다:
- 일부 명령은 TSV 파일을 쿼리하는 것으로, 커맨드 프롬프트에서 실행됩니다.
- 나머지 명령은 ClickHouse를 쿼리하는 것으로,
clickhouse-client또는 Play UI에서 실행됩니다.
이 가이드의 예제는 TSV 파일을 ${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv로 저장했다고 가정합니다. 필요하면 명령을 조정하세요.
TSV 파일 익히기 (Familiarize yourself with the TSV file)
ClickHouse 데이터베이스 작업을 시작하기 전에 데이터에 익숙해지세요.
소스 TSV 파일의 필드 살펴보기
이것은 TSV 파일을 쿼리하는 명령의 예시이지만, 아직 실행하지 마세요.
Query
clickhouse-local --query \
"describe file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')"
샘플 응답
CMPLNT_NUM Nullable(Float64)
ADDR_PCT_CD Nullable(Float64)
BORO_NM Nullable(String)
CMPLNT_FR_DT Nullable(String)
CMPLNT_FR_TM Nullable(String)
대부분의 경우 위 명령은 입력 데이터의 어떤 필드가 숫자이고 어떤 것이 문자열이고 어떤 것이 튜플인지 알려줍니다. 항상 그런 것은 아닙니다. ClickHouse는 수십억 개의 레코드를 포함한 데이터셋에 정기적으로 사용되므로, 스키마 추론을 위해 수십억 행을 파싱하는 것을 피하기 위해 검사하는 기본 행 수(100)가 있습니다. 아래 응답은 데이터셋이 매년 여러 번 갱신되므로 보이는 것과 다를 수 있습니다. 데이터 사전(Data Dictionary)을 보면 CMPLNT_NUM이 숫자가 아닌 텍스트로 지정되어 있음을 알 수 있습니다. 추론을 위한 기본 100행을 설정 SETTINGS input_format_max_rows_to_read_for_schema_inference=2000으로 재정의하면 내용을 더 잘 파악할 수 있습니다. 참고: 22.5 버전부터 스키마 추론을 위한 기본값은 이제 25,000행이므로 구버전이거나 25,000행 이상을 샘플링해야 하는 경우에만 설정을 변경하세요.
커맨드 프롬프트에서 이 명령을 실행하세요. 내려받은 TSV 파일의 데이터를 쿼리하기 위해 clickhouse-local을 사용할 것입니다.
Query
clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"describe file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')"
Response
CMPLNT_NUM Nullable(String)
ADDR_PCT_CD Nullable(Float64)
BORO_NM Nullable(String)
CMPLNT_FR_DT Nullable(String)
CMPLNT_FR_TM Nullable(String)
CMPLNT_TO_DT Nullable(String)
CMPLNT_TO_TM Nullable(String)
CRM_ATPT_CPTD_CD Nullable(String)
HADEVELOPT Nullable(String)
HOUSING_PSA Nullable(Float64)
JURISDICTION_CODE Nullable(Float64)
JURIS_DESC Nullable(String)
KY_CD Nullable(Float64)
LAW_CAT_CD Nullable(String)
LOC_OF_OCCUR_DESC Nullable(String)
OFNS_DESC Nullable(String)
PARKS_NM Nullable(String)
PATROL_BORO Nullable(String)
PD_CD Nullable(Float64)
PD_DESC Nullable(String)
PREM_TYP_DESC Nullable(String)
RPT_DT Nullable(String)
STATION_NAME Nullable(String)
SUSP_AGE_GROUP Nullable(String)
SUSP_RACE Nullable(String)
SUSP_SEX Nullable(String)
TRANSIT_DISTRICT Nullable(Float64)
VIC_AGE_GROUP Nullable(String)
VIC_RACE Nullable(String)
VIC_SEX Nullable(String)
X_COORD_CD Nullable(Float64)
Y_COORD_CD Nullable(Float64)
Latitude Nullable(Float64)
Longitude Nullable(Float64)
Lat_Lon Tuple(Nullable(Float64), Nullable(Float64))
New Georeferenced Column Nullable(String)
이 시점에서 TSV 파일의 컬럼이 데이터셋 웹 페이지의 Columns in this Dataset 섹션에 지정된 이름과 타입과 일치하는지 확인해야 합니다. 데이터 타입은 그다지 구체적이지 않으며, 모든 숫자 필드는 Nullable(Float64)로, 다른 모든 필드는 Nullable(String)로 설정됩니다. 데이터를 저장할 ClickHouse 테이블을 만들 때 더 적절하고 성능 좋은 타입을 지정할 수 있습니다.
적절한 스키마 결정 (Determine the proper schema)
필드에 어떤 타입을 사용해야 하는지 알아내려면 데이터가 어떻게 생겼는지 아는 것이 필요합니다. 예를 들어 JURISDICTION_CODE 필드는 숫자입니다: UInt8이어야 할까요, Enum이어야 할까요, 아니면 Float64가 적절할까요?
Query
clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"select JURISDICTION_CODE, count() FROM
file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
GROUP BY JURISDICTION_CODE
ORDER BY JURISDICTION_CODE
FORMAT PrettyCompact"
Response
┌─JURISDICTION_CODE─┬─count()─┐
│ 0 │ 188875 │
│ 1 │ 4799 │
│ 2 │ 13833 │
│ 3 │ 656 │
│ 4 │ 51 │
│ 6 │ 5 │
│ 7 │ 2 │
│ 9 │ 13 │
│ 11 │ 14 │
│ 12 │ 5 │
│ 13 │ 2 │
│ 14 │ 70 │
│ 15 │ 20 │
│ 72 │ 159 │
│ 87 │ 9 │
│ 88 │ 75 │
│ 97 │ 405 │
└───────────────────┴─────────┘
쿼리 응답은 JURISDICTION_CODE가 UInt8에 잘 맞는다는 것을 보여줍니다.
마찬가지로 일부 String 필드를 살펴보고 DateTime이나 LowCardinality(String) 필드에 적합한지 확인하세요.
예를 들어 PARKS_NM 필드는 "해당되는 경우 발생한 NYC 공원, 놀이터 또는 녹지의 이름(주립 공원은 포함되지 않음)"으로 설명됩니다. 뉴욕시의 공원 이름은 LowCardinality(String)의 좋은 후보일 수 있습니다:
Query
clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"select count(distinct PARKS_NM) FROM
file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
FORMAT PrettyCompact"
Response
┌─uniqExact(PARKS_NM)─┐
│ 319 │
└─────────────────────┘
공원 이름 몇 개를 살펴보세요:
Query
clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"select distinct PARKS_NM FROM
file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
LIMIT 10
FORMAT PrettyCompact"
Response
┌─PARKS_NM───────────────────┐
│ (null) │
│ ASSER LEVY PARK │
│ JAMES J WALKER PARK │
│ BELT PARKWAY/SHORE PARKWAY │
│ PROSPECT PARK │
│ MONTEFIORE SQUARE │
│ SUTTON PLACE PARK │
│ JOYCE KILMER PARK │
│ ALLEY ATHLETIC PLAYGROUND │
│ ASTORIA PARK │
└────────────────────────────┘
작성 시점에 사용된 데이터셋은 PARK_NM 컬럼에 수백 개의 고유 공원·놀이터만 있습니다. 이것은 LowCardinality(String) 필드에서 10,000개 미만의 고유 문자열을 유지하라는 LowCardinality 권장사항에 기반한 작은 수입니다.
DateTime 필드
데이터셋 웹 페이지의 Columns in this Dataset 섹션에 따르면 보고된 사건의 시작과 끝에 대한 날짜·시간 필드가 있습니다. CMPLNT_FR_DT와 CMPLT_TO_DT의 min·max를 보면 필드가 항상 채워지는지 여부를 알 수 있습니다:
Query
clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"select min(CMPLNT_FR_DT), max(CMPLNT_FR_DT) FROM
file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
FORMAT PrettyCompact"
Response
┌─min(CMPLNT_FR_DT)─┬─max(CMPLNT_FR_DT)─┐
│ 01/01/1973 │ 12/31/2021 │
└───────────────────┴───────────────────┘
Query
clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"select min(CMPLNT_TO_DT), max(CMPLNT_TO_DT) FROM
file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
FORMAT PrettyCompact"
Response
┌─min(CMPLNT_TO_DT)─┬─max(CMPLNT_TO_DT)─┐
│ │ 12/31/2021 │
└───────────────────┴───────────────────┘
Query
clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"select min(CMPLNT_FR_TM), max(CMPLNT_FR_TM) FROM
file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
FORMAT PrettyCompact"
Response
┌─min(CMPLNT_FR_TM)─┬─max(CMPLNT_FR_TM)─┐
│ 00:00:00 │ 23:59:00 │
└───────────────────┴───────────────────┘
Query
clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"select min(CMPLNT_TO_TM), max(CMPLNT_TO_TM) FROM
file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
FORMAT PrettyCompact"
Response
┌─min(CMPLNT_TO_TM)─┬─max(CMPLNT_TO_TM)─┐
│ (null) │ 23:59:00 │
└───────────────────┴───────────────────┘
계획 세우기 (Make a plan)
위 조사에 기반하여:
JURISDICTION_CODE는UInt8로 캐스팅해야 합니다.PARKS_NM은LowCardinality(String)으로 캐스팅해야 합니다.CMPLNT_FR_DT와CMPLNT_FR_TM은 항상 채워집니다(가능하면 기본 시간00:00:00으로).CMPLNT_TO_DT와CMPLNT_TO_TM은 비어 있을 수 있습니다.- 소스에서 날짜와 시간이 별도 필드에 저장됩니다.
- 날짜는
mm/dd/yyyy형식입니다. - 시간은
hh:mm:ss형식입니다. - 날짜와 시간을 DateTime 타입으로 연결할 수 있습니다.
- 1970년 1월 1일 이전의 날짜가 있으므로 64비트 DateTime이 필요합니다.
타입에 대한 변경은 훨씬 더 많이 있으며, 모두 같은 조사 단계를 따라 결정할 수 있습니다. 필드의 고유 문자열 수, 숫자의 min·max를 보고 결정하세요. 가이드 후반에 제공되는 테이블 스키마는 저카디널리티 문자열과 부호 없는 정수 필드가 많고 부동소수점 숫자는 거의 없습니다.
날짜와 시간 필드 연결 (Concatenate the date and time fields)
날짜·시간 필드 CMPLNT_FR_DT와 CMPLNT_FR_TM을 DateTime으로 캐스팅할 수 있는 단일 String으로 연결하려면 두 필드를 연결 연산자로 선택하세요: CMPLNT_FR_DT || ' ' || CMPLNT_FR_TM. CMPLNT_TO_DT와 CMPLNT_TO_TM 필드도 비슷하게 처리합니다.
Query
clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"select CMPLNT_FR_DT || ' ' || CMPLNT_FR_TM AS complaint_begin FROM
file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
LIMIT 10
FORMAT PrettyCompact"
Response
┌─complaint_begin─────┐
│ 07/29/2010 00:01:00 │
│ 12/01/2011 12:00:00 │
│ 04/01/2017 15:00:00 │
│ 03/26/2018 17:20:00 │
│ 01/01/2019 00:00:00 │
│ 06/14/2019 00:00:00 │
│ 11/29/2021 20:00:00 │
│ 12/04/2021 00:35:00 │
│ 12/05/2021 12:50:00 │
│ 12/07/2021 20:30:00 │
└─────────────────────┘
날짜·시간 String을 DateTime64 타입으로 변환 (Convert the date and time String to a DateTime64 type)
이 가이드 앞부분에서 TSV 파일에 1970년 1월 1일 이전의 날짜가 있음을 발견했으므로 날짜에 64비트 DateTime 타입이 필요합니다. 날짜도 MM/DD/YYYY에서 YYYY/MM/DD 형식으로 변환해야 합니다. 둘 다 parseDateTime64BestEffort()로 수행할 수 있습니다.
Query
clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"WITH (CMPLNT_FR_DT || ' ' || CMPLNT_FR_TM) AS CMPLNT_START,
(CMPLNT_TO_DT || ' ' || CMPLNT_TO_TM) AS CMPLNT_END
select parseDateTime64BestEffort(CMPLNT_START) AS complaint_begin,
parseDateTime64BestEffortOrNull(CMPLNT_END) AS complaint_end
FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
ORDER BY complaint_begin ASC
LIMIT 25
FORMAT PrettyCompact"
위 2·3행에는 이전 단계의 연결이 포함되고, 4·5행은 문자열을 DateTime64로 파싱합니다. 신고 종료 시간이 항상 존재한다는 보장이 없으므로 parseDateTime64BestEffortOrNull이 사용됩니다.
Response
┌─────────complaint_begin─┬───────────complaint_end─┐
│ 1925-01-01 10:00:00.000 │ 2021-02-12 09:30:00.000 │
│ 1925-01-01 11:37:00.000 │ 2022-01-16 11:49:00.000 │
│ 1925-01-01 15:00:00.000 │ 2021-12-31 00:00:00.000 │
│ 1925-01-01 15:00:00.000 │ 2022-02-02 22:00:00.000 │
│ 1925-01-01 19:00:00.000 │ 2022-04-14 05:00:00.000 │
│ 1955-09-01 19:55:00.000 │ 2022-08-01 00:45:00.000 │
│ 1972-03-17 11:40:00.000 │ 2022-03-17 11:43:00.000 │
│ 1972-05-23 22:00:00.000 │ 2022-05-24 09:00:00.000 │
│ 1972-05-30 23:37:00.000 │ 2022-05-30 23:50:00.000 │
│ 1972-07-04 02:17:00.000 │ ᴺᵁᴸᴸ │
│ 1973-01-01 00:00:00.000 │ ᴺᵁᴸᴸ │
│ 1975-01-01 00:00:00.000 │ ᴺᵁᴸᴸ │
│ 1976-11-05 00:01:00.000 │ 1988-10-05 23:59:00.000 │
│ 1977-01-01 00:00:00.000 │ 1977-01-01 23:59:00.000 │
│ 1977-12-20 00:01:00.000 │ ᴺᵁᴸᴸ │
│ 1981-01-01 00:01:00.000 │ ᴺᵁᴸᴸ │
│ 1981-08-14 00:00:00.000 │ 1987-08-13 23:59:00.000 │
│ 1983-01-07 00:00:00.000 │ 1990-01-06 00:00:00.000 │
│ 1984-01-01 00:01:00.000 │ 1984-12-31 23:59:00.000 │
│ 1985-01-01 12:00:00.000 │ 1987-12-31 15:00:00.000 │
│ 1985-01-11 09:00:00.000 │ 1985-12-31 12:00:00.000 │
│ 1986-03-16 00:05:00.000 │ 2022-03-16 00:45:00.000 │
│ 1987-01-07 00:00:00.000 │ 1987-01-09 00:00:00.000 │
│ 1988-04-03 18:30:00.000 │ 2022-08-03 09:45:00.000 │
│ 1988-07-29 12:00:00.000 │ 1990-07-27 22:00:00.000 │
└─────────────────────────┴─────────────────────────┘
위 1925로 표시된 날짜는 데이터의 오류에서 비롯됩니다. 원본 데이터에는 10191022 연도의 날짜가 있는 레코드가 몇 건 있는데, 20192022여야 합니다. 64비트 DateTime에서 가장 이른 날짜가므로 1925년 1월 1일로 저장되고 있습니다.
테이블 생성 (Create a table)
컬럼에 사용한 데이터 타입에 대한 위에서 내린 결정은 아래 테이블 스키마에 반영됩니다. 또한 테이블에 사용할 ORDER BY와 PRIMARY KEY를 결정해야 합니다. ORDER BY 또는 PRIMARY KEY 중 적어도 하나는 지정해야 합니다. ORDER BY에 포함할 컬럼을 결정하는 몇 가지 지침은 다음과 같으며, 더 많은 정보는 문서 끝의 Next Steps 섹션에 있습니다.
ORDER BY 및 PRIMARY KEY 절
ORDER BY튜플은 쿼리 필터에서 사용되는 필드를 포함해야 합니다- 디스크에서 압축을 최대화하려면
ORDER BY튜플은 카디널리티가 오름차순으로 정렬되어야 합니다 PRIMARY KEY튜플은 있으면ORDER BY튜플의 하위 집합이어야 합니다ORDER BY만 지정하면 같은 튜플이PRIMARY KEY로 사용됩니다- 기본 키 인덱스는 지정되면
PRIMARY KEY튜플로, 그렇지 않으면ORDER BY튜플로 생성됩니다 PRIMARY KEY인덱스는 주 메모리에 유지됩니다
데이터셋과 쿼리로 답할 수 있는 질문을 살펴보면 뉴욕시 다섯 자치구에서 시간에 따라 신고된 범죄 유형을 살펴보고 싶을 수 있습니다. 그러면 이 필드들이 ORDER BY에 포함될 수 있습니다:
| Column | Description (from the data dictionary) |
|---|---|
| OFNS_DESC | 키 코드에 대응하는 범죄 설명 |
| RPT_DT | 경찰에 사건이 신고된 날짜 |
| BORO_NM | 사건이 발생한 자치구의 이름 |
세 후보 컬럼의 카디널리티를 TSV 파일에서 쿼리합니다:
Query
clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"select formatReadableQuantity(uniq(OFNS_DESC)) as cardinality_OFNS_DESC,
formatReadableQuantity(uniq(RPT_DT)) as cardinality_RPT_DT,
formatReadableQuantity(uniq(BORO_NM)) as cardinality_BORO_NM
FROM
file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
FORMAT PrettyCompact"
Response
┌─cardinality_OFNS_DESC─┬─cardinality_RPT_DT─┬─cardinality_BORO_NM─┐
│ 60.00 │ 306.00 │ 6.00 │
└───────────────────────┴────────────────────┴─────────────────────┘
카디널리티 순으로 정렬하면 ORDER BY는 이렇게 됩니다:
ORDER BY ( BORO_NM, OFNS_DESC, RPT_DT )
아래 테이블은 더 읽기 쉬운 컬럼 이름을 사용하며, 위 이름들은 아래로 매핑됩니다:
ORDER BY ( borough, offense_description, date_reported )
데이터 타입 변경과 ORDER BY 튜플을 종합하면 이 테이블 구조가 나옵니다:
CREATE TABLE NYPD_Complaint (
complaint_number String,
precinct UInt8,
borough LowCardinality(String),
complaint_begin DateTime64(0,'America/New_York'),
complaint_end DateTime64(0,'America/New_York'),
was_crime_completed String,
housing_authority String,
housing_level_code UInt32,
jurisdiction_code UInt8,
jurisdiction LowCardinality(String),
offense_code UInt8,
offense_level LowCardinality(String),
location_descriptor LowCardinality(String),
offense_description LowCardinality(String),
park_name LowCardinality(String),
patrol_borough LowCardinality(String),
PD_CD UInt16,
PD_DESC String,
location_type LowCardinality(String),
date_reported Date,
transit_station LowCardinality(String),
suspect_age_group LowCardinality(String),
suspect_race LowCardinality(String),
suspect_sex LowCardinality(String),
transit_district UInt8,
victim_age_group LowCardinality(String),
victim_race LowCardinality(String),
victim_sex LowCardinality(String),
NY_x_coordinate UInt32,
NY_y_coordinate UInt32,
Latitude Float64,
Longitude Float64
) ENGINE = MergeTree
ORDER BY ( borough, offense_description, date_reported )
테이블의 기본 키 찾기
ClickHouse system 데이터베이스, 특히 system.table에는 방금 만든 테이블에 대한 모든 정보가 있습니다. 이 쿼리는 ORDER BY(정렬 키)와 PRIMARY KEY를 보여줍니다:
SELECT
partition_key,
sorting_key,
primary_key,
table
FROM system.tables
WHERE table = 'NYPD_Complaint'
FORMAT Vertical
Response
Query id: 6a5b10bf-9333-4090-b36e-c7f08b1d9e01
Row 1:
──────
partition_key:
sorting_key: borough, offense_description, date_reported
primary_key: borough, offense_description, date_reported
table: NYPD_Complaint
1 row in set. Elapsed: 0.001 sec.
데이터 전처리 및 임포트 (Preprocess and import data)
데이터 전처리에는 clickhouse-local 도구를, 업로드에는 clickhouse-client를 사용할 것입니다.
사용되는 clickhouse-local 인자
table='input'이 아래 clickhouse-local 인자에 나타납니다. clickhouse-local은 제공된 입력(cat ${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv)을 받아 테이블에 삽입합니다. 기본적으로 테이블 이름은 table입니다. 이 가이드에서는 데이터 흐름을 더 명확히 하기 위해 테이블 이름을 input으로 설정합니다. clickhouse-local의 마지막 인자는 테이블(FROM input)에서 선택하는 쿼리이며, 이것은 clickhouse-client로 파이프되어 NYPD_Complaint 테이블을 채웁니다.
cat ${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv \
| clickhouse-local --table='input' --input-format='TSVWithNames' \
--input_format_max_rows_to_read_for_schema_inference=2000 \
--query "
WITH (CMPLNT_FR_DT || ' ' || CMPLNT_FR_TM) AS CMPLNT_START,
(CMPLNT_TO_DT || ' ' || CMPLNT_TO_TM) AS CMPLNT_END
SELECT
CMPLNT_NUM AS complaint_number,
ADDR_PCT_CD AS precinct,
BORO_NM AS borough,
parseDateTime64BestEffort(CMPLNT_START) AS complaint_begin,
parseDateTime64BestEffortOrNull(CMPLNT_END) AS complaint_end,
CRM_ATPT_CPTD_CD AS was_crime_completed,
HADEVELOPT AS housing_authority_development,
HOUSING_PSA AS housing_level_code,
JURISDICTION_CODE AS jurisdiction_code,
JURIS_DESC AS jurisdiction,
KY_CD AS offense_code,
LAW_CAT_CD AS offense_level,
LOC_OF_OCCUR_DESC AS location_descriptor,
OFNS_DESC AS offense_description,
PARKS_NM AS park_name,
PATROL_BORO AS patrol_borough,
PD_CD,
PD_DESC,
PREM_TYP_DESC AS location_type,
toDate(parseDateTimeBestEffort(RPT_DT)) AS date_reported,
STATION_NAME AS transit_station,
SUSP_AGE_GROUP AS suspect_age_group,
SUSP_RACE AS suspect_race,
SUSP_SEX AS suspect_sex,
TRANSIT_DISTRICT AS transit_district,
VIC_AGE_GROUP AS victim_age_group,
VIC_RACE AS victim_race,
VIC_SEX AS victim_sex,
X_COORD_CD AS NY_x_coordinate,
Y_COORD_CD AS NY_y_coordinate,
Latitude,
Longitude
FROM input" \
| clickhouse-client --query='INSERT INTO NYPD_Complaint FORMAT TSV'
데이터 검증 (Validate the data)
데이터셋은 연 1회 이상 변경되므로 개수는 이 문서의 내용과 다를 수 있습니다.
Query
SELECT count()
FROM NYPD_Complaint
Response
┌─count()─┐
│ 208993 │
└─────────┘
1 row in set. Elapsed: 0.001 sec.
원본 TSV 파일의 크기와 테이블의 크기를 비교하면 ClickHouse의 데이터셋 크기는 원본 TSV 파일의 12%에 불과합니다:
Query
SELECT formatReadableSize(total_bytes)
FROM system.tables
WHERE name = 'NYPD_Complaint'
Response
┌─formatReadableSize(total_bytes)─┐
│ 8.63 MiB │
└─────────────────────────────────┘
몇 가지 쿼리 실행 (Run some queries)
Query 1. 월별 신고 수 비교
Query
SELECT
dateName('month', date_reported) AS month,
count() AS complaints,
bar(complaints, 0, 50000, 80)
FROM NYPD_Complaint
GROUP BY month
ORDER BY complaints DESC
Response
Query id: 7fbd4244-b32a-4acf-b1f3-c3aa198e74d9
┌─month─────┬─complaints─┬─bar(count(), 0, 50000, 80)───────────────────────────────┐
│ March │ 34536 │ ███████████████████████████████████████████████████████▎ │
│ May │ 34250 │ ██████████████████████████████████████████████████████▋ │
│ April │ 32541 │ ████████████████████████████████████████████████████ │
│ January │ 30806 │ █████████████████████████████████████████████████▎ │
│ February │ 28118 │ ████████████████████████████████████████████▊ │
│ November │ 7474 │ ███████████▊ │
│ December │ 7223 │ ███████████▌ │
│ October │ 7070 │ ███████████▎ │
│ September │ 6910 │ ███████████ │
│ August │ 6801 │ ██████████▊ │
│ June │ 6779 │ ██████████▋ │
│ July │ 6485 │ ██████████▍ │
└───────────┴────────────┴──────────────────────────────────────────────────────────┘
12 rows in set. Elapsed: 0.006 sec. Processed 208.99 thousand rows, 417.99 KB (37.48 million rows/s., 74.96 MB/s.)
Query 2. 자치구별 총 신고 수 비교
Query
SELECT
borough,
count() AS complaints,
bar(complaints, 0, 125000, 60)
FROM NYPD_Complaint
GROUP BY borough
ORDER BY complaints DESC
Response
Query id: 8cdcdfd4-908f-4be0-99e3-265722a2ab8d
┌─borough───────┬─complaints─┬─bar(count(), 0, 125000, 60)──┐
│ BROOKLYN │ 57947 │ ███████████████████████████▋ │
│ MANHATTAN │ 53025 │ █████████████████████████▍ │
│ QUEENS │ 44875 │ █████████████████████▌ │
│ BRONX │ 44260 │ █████████████████████▏ │
│ STATEN ISLAND │ 8503 │ ████ │
│ (null) │ 383 │ ▏ │
└───────────────┴────────────┴──────────────────────────────┘
6 rows in set. Elapsed: 0.008 sec. Processed 208.99 thousand rows, 209.43 KB (27.14 million rows/s., 27.20 MB/s.)
다음 단계 (Next steps)
ClickHouse 희소 기본 인덱스 실용 입문은 ClickHouse 인덱싱이 전통적인 관계형 데이터베이스와 어떤 차이가 있는지, ClickHouse가 희소 기본 인덱스를 어떻게 구축하고 사용하는지, 그리고 인덱싱 모범 사례를 다룹니다.