Пример вычислений Hive

В данной статье мы рассмотрим обработку криминальной статистики, основанную на реальных отчетах о происшествиях Бостонского управления полиции. Набор данных предоставлен Analyze Boston. Актуальный dataset доступен на странице Kaggle.

Набор данных

Набор данных (Dataset) содержит записи с 14 июня 2015 по 3 сентября 2018 года. Каждая строка представляет запись о преступлении, включая вид преступления, дату, время и место.

Программа принимает следующие файлы на входе:

  • crime.csv — основной файл с записями о преступлениях;

  • offense_codes.csv — файл, содержащий коды преступлений.

 

В результате выполнения программы мы получим следующую информацию:

  • наиболее частые виды преступлений;

  • распределение преступлений по районам;

  • частота совершения преступления по видам.

Подготовка

Перед началом работы выполните следующие шаги:

  1. На машине, где установлен Hive-сервер, создайте директории для файлов с данными:

    $ hdfs dfs -mkdir /user/user1/crime
    $ hdfs dfs -mkdir /user/user1/offense_codes
  2. Скопируйте файлы с данными в созданные директории:

    $ hdfs dfs -put ~/crime.csv /user/user1/crime
    $ hdfs dfs -put ~/offense_codes.csv /user/user1/offense_codes

Выполнение вычислений

  1. Создайте таблицу offense_codes:

    CREATE EXTERNAL TABLE offense_codes (
      CODE int,
      NAME string
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    LOCATION '/user/user1/offense_codes' tblproperties ("skip.header.line.count"="1");
  2. Создайте таблицу crime:

    CREATE EXTERNAL TABLE crime (
        INCIDENT_NUMBER string,
        OFFENSE_CODE int,
        OFFENSE_CODE_GROUP string,
        OFFENSE_DESCRIPTION string,
        DISTRICT string,
        REPORTING_AREA string,
        SHOOTING string,
        OCCURRED_ON_DATE string,
        YEAR int,
        MONTH int,
        DAY_OF_WEEK string,
        HOUR int,
        UCR_PART string,
        STREET string,
        Lat DECIMAL,
        Long DECIMAL,
        Location string
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    LOCATION '/user/user1/crime' tblproperties ("skip.header.line.count"="1");
    РЕКОМЕНДАЦИЯ
    Не забудьте изменить путь, где хранятся ваши таблицы и файлы с данными. В данном примере используется путь /user/user1/.
  3. Запустите вычисления с помощью следующего SQL-скрипта:

    with filtered_crimes as (
        select *
        from crime cr
        where cr.district is not null
    ),
         crimes_w_codes as (
             select cr.*,
                    oc.*,
                    trim(case
                        when instr(oc.name, '-') != 0 then
                            substr(oc.name, 1, instr(oc.name, '-') - 1)
                        else oc.name end) as crime_type
             from filtered_crimes cr
                      join offense_codes oc
                           on cr.offense_code = oc.code
         ),
         crimes_district_analytics as (
             select cr.district, count(incident_number) as crimes_total, avg(cr.lat) as lat, avg(cr.long) as lng
             from filtered_crimes cr
             group by cr.district
         ),
         crimes_by_district_by_month as (
             select cr.district, cr.month, count(cr.incident_number) as crimes_cnt
             from filtered_crimes cr
             group by cr.district, cr.month
         ),
         crimes_district_median as (
             select cr.district, percentile(cr.crimes_cnt,0.5) as crimes_monthly
             from crimes_by_district_by_month cr
             group by cr.district
         ),
         crimes_by_district_by_crime_type as (
             select cr.district, cr.crime_type, count(cr.INCIDENT_NUMBER) as crimes_cnt
             from crimes_w_codes cr
             group by cr.district, cr.crime_type
         ),
         crimes_sort as (
             select pre.district, collect_list(pre.crime_type) as frequent_crime_types
             from (
                      select cr.district,
                             cr.crime_type,
                             row_number() over (partition by cr.district order by cr.crimes_cnt desc) as rn
                      from crimes_by_district_by_crime_type cr) pre
             where pre.rn <= 3
             group by pre.district
         )
    select cda.district, cda.crimes_total, cdm.crimes_monthly, cs.frequent_crime_types, cda.lat, cda.lng
    from crimes_district_analytics cda
             join crimes_district_median cdm on cda.district = cdm.district
             join crimes_sort cs on cda.district = cs.district;

Где:

  • crimes_total — общее количество преступлений, совершенных в указанном районе.

  • crimes_monthly — усредненное количество преступлений в месяц, совершенных в указанном районе.

  • frequent_crime_types — три наиболее частых вида преступлений в указанном районе за всю историю наблюдений. Значения разделены запятой и пробелом () и упорядочены по нисходящей от наиболее частых к редким.

  • crime_type — первая часть поля NAME из таблицы offense_codes.csv, отсеченная разделителем -. Например, если поле NAME содержит BURGLARY - COMMERICAL - ATTEMPT, то crime_type определяется как BURGLARY.

  • lat — широта координат района, средняя по всем преступлениям.

  • lng — долгота координат района, средняя по всем преступлениям.

Результат выполнения скрипта имеет следующий вид:

No Stats for default@crime, Columns: district, incident_number, lat, long
No Stats for default@crime, Columns: month
No Stats for default@crime, Columns: offense_code
No Stats for default@offense_codes, Columns: code, name
Query ID = user1_20211112125439_22d654c1-caeb-47f8-adc9-8e2028602653
Total jobs = 1
Launching Job 1 out of 1
Status: Running (Executing on YARN cluster with App id application_1636705252031_0039)

----------------------------------------------------------------------------------------------
        VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED
----------------------------------------------------------------------------------------------
Map 1 .......... container     SUCCEEDED      1          1        0        0       0       0
Reducer 2 ...... container     SUCCEEDED      3          3        0        0       0       0
Map 4 .......... container     SUCCEEDED      1          1        0        0       0       0
Reducer 5 ...... container     SUCCEEDED      3          3        0        0       0       0
Map 10 ......... container     SUCCEEDED      1          1        0        0       0       0
Map 6 .......... container     SUCCEEDED      1          1        0        0       0       0
Reducer 7 ...... container     SUCCEEDED      3          3        0        0       0       0
Reducer 8 ...... container     SUCCEEDED      2          2        0        0       0       0
Reducer 9 ...... container     SUCCEEDED      3          3        0        0       0       0
Reducer 3 ...... container     SUCCEEDED      3          3        0        0       0       0
----------------------------------------------------------------------------------------------
VERTICES: 10/10  [==========================>>] 100%  ELAPSED TIME: 9,21 s
----------------------------------------------------------------------------------------------
OK
cda.district    cda.crimes_total    cdm.crimes_monthly  cs.frequent_crime_types cda.lat cda.lng
 ETC"   1522    369.5   ["\"WEAPON","FIREARM/WEAPON"]   NULL    41.9364
 ETC. " 1514    382.0   ["\"DRUGS"] NULL    41.8329
A7  13151   1062.5  ["SICK/INJURED/MEDICAL","INVESTIGATE PERSON","VANDALISM"]   41.9898 -70.9834
B2  48669   3876.0  ["VERBAL DISPUTE","SICK/INJURED/MEDICAL","INVESTIGATE PERSON"]  41.9943 -70.9907
C11 41460   3212.5  ["SICK/INJURED/MEDICAL","INVESTIGATE PERSON","VERBAL DISPUTE"]  41.9925 -70.9877
D4  41020   3221.5  ["PROPERTY","INVESTIGATE PERSON","SICK/INJURED/MEDICAL"]    41.9977 -70.9963
E5  12966   1022.5  ["SICK/INJURED/MEDICAL","INVESTIGATE PERSON","PROPERTY"]    41.9178 -70.8662
 BRUSH  525 525.0   ["\"FIRE REPORT"]   NULL    NULL
 BUILDING   1269    1269.0  ["\"FIRE REPORT"]   NULL    NULL
A15 6378    491.5   ["INVESTIGATE PERSON","VANDALISM","SICK/INJURED/MEDICAL"]   41.8010 -70.6760
    1693    107.0   ["M/V ACCIDENT","M/V","INVESTIGATE PROPERTY"]   24.8485 -43.0789
 ETC."  2597    680.0   ["\"DRUGS"] NULL    41.8254
 FEET   1   1.0 ["\"A&B HANDS"] NULL    NULL
 NON-NEGLIGIENT MANSLAUGHTER"   161 40.0    ["\"MURDER","MURDER NON"]   NULL    41.7190
A1  34834   2698.5  ["PROPERTY","SICK/INJURED/MEDICAL","WARRANT ARREST"]    41.9741 -70.9578
B3  34618   2696.0  ["VERBAL DISPUTE","INVESTIGATE PERSON","MISSING PERSON"]    41.9961 -70.9936
C6  22903   1826.5  ["SICK/INJURED/MEDICAL","INVESTIGATE PERSON","TOWED MOTOR VEHICLE"] 41.8786 -70.8023
D14 19785   1581.0  ["TOWED MOTOR VEHICLE","SICK/INJURED/MEDICAL","INVESTIGATE PERSON"] 41.9931 -70.9888
E13 17010   1323.0  ["SICK/INJURED/MEDICAL","INVESTIGATE PERSON","TOWED MOTOR VEHICLE"] 41.9947 -70.9913
E18 16997   1289.5  ["SICK/INJURED/MEDICAL","INVESTIGATE PERSON","VERBAL DISPUTE"]  42.0000 -71.0000
Time taken: 12.691 seconds, Fetched: 20 row(s)
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней