Статьи

Использование новых функций в Drill 1.2 — функции ANSI SQL Analytic / Window

Январь 25, 2019

Сегодня мы очень рады объявить о последней версии Apache Drill, 1.2,) в составе дистрибутива MapR. Пакеты Drill 1.2 для MapR можно скачать http://doc.mapr.com/display/MapR/Apache+Drill+on+MapR
Вы можете поэкспериментировать с выпуском, используя песочницу MapR и различные практические руководства по адресу https://www.mapr.com/products/mapr-sandbox-hadoop/download-sandbox-drill.

Apache Drill набирает значительную популярность среди пользователей и поддерживает сообщество с момента его первой доступности бета-версии (Sep’14). Многочисленные клиенты развернули и используют Drill в производстве, и они находят Drill чрезвычайно ценным в своих средах с различными вариантами использования, такими как исследование данных, запросы Adhoc / BI на озере данных Hadoop и аналитика данных JSON.

Общедоступная версия Drill 1.0 была выпущена в мае 15 года, а затем Drill 1.1 в начале июля 15 года. Каждый из этих выпусков добавляет существенные новые функции к интерактивным функциям самообслуживания Drill, позволяющим исследовать данные и выполнять запросы SQL, и делает их готовыми к масштабированию и управляемости. Drill 1.2 расширяет фундамент и поднимает планку благодаря расширенной поддержке SQL, более глубокой интеграции Hive и улучшениям производительности. Drill 1.2 содержит более 250 исправлений ошибок и несколько новых улучшений, включая следующие.

  • Новые функции ANSI SQL Analytic / Window — Lead / Lag, First_Value / Last_Value, NTile
  • Оптимизированные возможности чтения в таблицах Hive
  • Поддержка нескольких версий Hive
  • Кэширование метаданных для улучшения производительности запросов на больших # паркетных файлах
  • Улучшено нажатие клавиш в таблицах HBase / MapR-DB
  • Безопасность веб-интерфейса Drill
  • Команда удаления таблицы
  • Улучшения обработки памяти

В этой записи блога я хотел бы кратко представить новые аналитические возможности, добавленные в Drill, а именно: ANSI SQL-совместимые аналитические и оконные функции, и как с ними начать. Оконные функции SQL в Drill включают поддержку предложений PARTITION BY и OVER, разнообразные агрегированные оконные функции для Sum, Max, Min, Count, Avg и аналитические функции, такие как First_Value, Last_Value, Lead, Lag, NTile, Row_Number, Rank. Оконные функции очень универсальны и позволяют пользователям сокращать объединения, подзапросы, явные курсоры, которые должны быть написаны и подогнаны естественным образом для решения различных вариантов использования без больших усилий по написанию кода.

В моих предыдущих статьях « Превращая необработанные данные в реальное понимание и работая с высокодинамичными наборами данных» , я продемонстрировал различные возможности запросов в Drill, используя демонстрационный набор данных бизнес-обзоров от Yelp. Этот пост продолжает использовать тот же набор данных для демонстрации аналитических / оконных функций.

Во-первых, давайте запустим Drill во встроенном режиме (также можно использовать распределенный режим)

1
2
3
4
5
6
NRentachintala-MAC:bin nrentachintala$ ./drill-embedded
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
Oct 19, 2015 9:20:03 AM org.glassfish.jersey.server.ApplicationHandler initialize
INFO: Initiating Jersey application, version Jersey: 2.8 2014-04-29 01:25:26...
apache drill 1.2.0
"a drill in the hand is better than two in the bush"

Перечислите доступные схемы в Drill.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
0: jdbc:drill:zk=local> show schemas;
+---------------------+
|     SCHEMA_NAME     |
+---------------------+
| INFORMATION_SCHEMA  |
| cp.default          |
| dfs.default         |
| dfs.root            |
| dfs.tmp             |
| dfs.yelp            |
| sys                 |
+---------------------+
 
7 rows selected (1.755 seconds)

Переключитесь на использование рабочей области, в которую загружаются данные Yelp.

1
2
3
4
5
6
7
8
0: jdbc:drill:zk=local> use dfs.yelp;
+-------+---------------------------------------+
|  ok   |                summary                |
+-------+---------------------------------------+
| true  | Default schema changed to [dfs.yelp]  |
+-------+---------------------------------------+
 
1 row selected (0.129 seconds)

Давайте начнем с изучения одного из наборов данных, доступных в наборе данных Yelp — бизнес-информации

1
2
3
4
5
6
7
8
9
0: jdbc:drill:zk=local> select * from `business.json` limit 1;
+-------------+--------------+-------+------+------------+------+--------------+------+-----------+-------+-------+----------+------------+------+---------------+
| business_id | full_address | hours | open | categories | city | review_count | name | longitude | state | stars | latitude | attributes | type | neighborhoods |
+-------------+--------------+-------+------+------------+------+--------------+------+-----------+-------+-------+----------+------------+------+---------------+
| vcNAWiLM4dR7D2nwwJ7nCA | 4840 E Indian School Rd
Ste 101
Phoenix, AZ 85018 | {"Tuesday":{"close":"17:00","open":"08:00"},"Friday":{"close":"17:00","open":"08:00"},"Monday":{"close":"17:00","open":"08:00"},"Wednesday":{"close":"17:00","open":"08:00"},"Thursday":{"close":"17:00","open":"08:00"},"Sunday":{},"Saturday":{}} | true | ["Doctors","Health & Medical"] | Phoenix | 7 | Eric Goldberg, MD | -111.983758 | AZ | 3.5 | 33.499313 | {"By Appointment Only":true,"Good For":{},"Ambience":{},"Parking":{},"Music":{},"Hair Types Specialized In":{},"Payment Types":{},"Dietary Restrictions":{}} | business | [] |
+-------------+--------------+-------+------+------------+------+--------------+------+-----------+-------+-------+----------+------------+------+---------------+
1 row selected (0.514 seconds)

Теперь давайте рассмотрим использование нескольких оконных функций Drill.

Во-первых, просто найдите лучшие компании Yelp на основе # отзывов в каждом городе вместе с номером строки.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
0: jdbc:drill:zk=local> SELECT name, city, review_count,row_number()
. . . . . . . . . . . > OVER (PARTITION BY city ORDER BY review_count DESC) as rownum
. . . . . . . . . . . > FROM `business.json` limit 15; 
 
+----------------------------------------+------------+---------------+---------+
|                  name                  |    city    | review_count  | rownum  |
+----------------------------------------+------------+---------------+---------+
| Cupz N' Crepes                         | Ahwatukee  | 124           | 1       |
| My Wine Cellar                         | Ahwatukee  | 98            | 2       |
| Kathy's Alterations                    | Ahwatukee  | 12            | 3       |
| McDonald's                             | Ahwatukee  | 7             | 4       |
| U-Haul                                 | Ahwatukee  | 5             | 5       |
| Hi-Health                              | Ahwatukee  | 4             | 6       |
| Healthy and Clean Living Environments  | Ahwatukee  | 4             | 7       |
| Active Kids Pediatrics                 | Ahwatukee  | 4             | 8       |
| Roberto's Authentic Mexican Food       | Anthem     | 117           | 1       |
| Q to U BBQ                             | Anthem     | 74            | 2       |
| Outlets At Anthem                      | Anthem     | 64            | 3       |
| Dara Thai                              | Anthem     | 56            | 4       |
| Cafe Provence                          | Anthem     | 53            | 5       |
| Shanghai Club                          | Anthem     | 50            | 6       |
| Two Brothers Kitchen                   | Anthem     | 43            | 7       |
+----------------------------------------+------------+---------------+---------+
15 rows selected (0.67 seconds)

Посмотрите, как количество # отзывов по каждому бизнесу сравнивается со средним количеством # отзывов по всему бизнесу в городе.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
0: jdbc:drill:zk=local> SELECT name, city,review_count,
. . . . . . . . . . . > Avg(review_count) OVER (PARTITION BY City) AS city_reviews_avg
. . . . . . . . . . . > FROM `business.json`limit 15;
+----------------------------------------+------------+---------------+---------------------+
|                  name                  |    city    | review_count  |  city_reviews_avg   |
+----------------------------------------+------------+---------------+---------------------+
| Hi-Health                              | Ahwatukee  | 4             | 32.25               |
| My Wine Cellar                         | Ahwatukee  | 98            | 32.25               |
| U-Haul                                 | Ahwatukee  | 5             | 32.25               |
| Cupz N' Crepes                         | Ahwatukee  | 124           | 32.25               |
| McDonald's                             | Ahwatukee  | 7             | 32.25               |
| Kathy's Alterations                    | Ahwatukee  | 12            | 32.25               |
| Healthy and Clean Living Environments  | Ahwatukee  | 4             | 32.25               |
| Active Kids Pediatrics                 | Ahwatukee  | 4             | 32.25               |
| Anthem Community Center                | Anthem     | 4             | 14.492063492063492  |
| Scrapbooks To Remember                 | Anthem     | 4             | 14.492063492063492  |
| Hungry Howie's Pizza                   | Anthem     | 7             | 14.492063492063492  |
| Pinata Nueva                           | Anthem     | 3             | 14.492063492063492  |
| Starbucks Coffee Company               | Anthem     | 13            | 14.492063492063492  |
| Pizza Hut                              | Anthem     | 6             | 14.492063492063492  |
| Rays Pizza                             | Anthem     | 19            | 14.492063492063492  |
+----------------------------------------+------------+---------------+---------------------+
15 rows selected (0.395 seconds)

Проверьте, как #reviews для каждого бизнеса способствуют общему количеству отзывов для всех предприятий в городе.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
0: jdbc:drill:zk=local> SELECT name, city,review_count,
. . . . . . . . . . . > Sum(review_count) OVER (PARTITION BY City) AS city_reviews_sum
. . . . . . . . . . . > FROM `business.json`limit 15;
+----------------------------------------+------------+---------------+-------------------+
|                  name                  |    city    | review_count  | city_reviews_sum  |
+----------------------------------------+------------+---------------+-------------------+
| Hi-Health                              | Ahwatukee  | 4             | 258               |
| My Wine Cellar                         | Ahwatukee  | 98            | 258               |
| U-Haul                                 | Ahwatukee  | 5             | 258               |
| Cupz N' Crepes                         | Ahwatukee  | 124           | 258               |
| McDonald's                             | Ahwatukee  | 7             | 258               |
| Kathy's Alterations                    | Ahwatukee  | 12            | 258               |
| Healthy and Clean Living Environments  | Ahwatukee  | 4             | 258               |
| Active Kids Pediatrics                 | Ahwatukee  | 4             | 258               |
| Anthem Community Center                | Anthem     | 4             | 913               |
| Scrapbooks To Remember                 | Anthem     | 4             | 913               |
| Hungry Howie's Pizza                   | Anthem     | 7             | 913               |
| Pinata Nueva                           | Anthem     | 3             | 913               |
| Starbucks Coffee Company               | Anthem     | 13            | 913               |
| Pizza Hut                              | Anthem     | 6             | 913               |
| Rays Pizza                             | Anthem     | 19            | 913               |
+----------------------------------------+------------+---------------+-------------------+
15 rows selected (0.543 seconds)

Теперь. Давайте попробуем немного сложный запрос. Перечислите 10 лучших городов и их компании с самым высоким рейтингом с точки зрения количества отзывов. В этих запросах могут использоваться функции окна детализации, такие как rank, dens_rank.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
. . . . . . . . . . . > WITH X
. . . . . . . . . . . > AS
. . . . . . . . . . . > (SELECT name, city, review_count,
. . . . . . . . . . . > RANK()
. . . . . . . . . . . > OVER (PARTITION BY city
. . . . . . . . . . . > ORDER BY review_count DESC) AS review_rank
. . . . . . . . . . . > FROM `business.json`)
. . . . . . . . . . . > SELECT X.name, X.city, X.review_count
. . . . . . . . . . . > FROM X
. . . . . . . . . . . > WHERE X.review_rank =1 ORDER BY review_count DESC LIMIT 10;
+-------------------------------------------+-------------+---------------+
|                   name                    |    city     | review_count  |
+-------------------------------------------+-------------+---------------+
| Mon Ami Gabi                              | Las Vegas   | 4084          |
| Studio B                                  | Henderson   | 1336          |
| Phoenix Sky Harbor International Airport  | Phoenix     | 1325          |
| Four Peaks Brewing Co                     | Tempe       | 1110          |
| The Mission                               | Scottsdale  | 783           |
| Joe's Farm Grill                          | Gilbert     | 770           |
| The Old Fashioned                         | Madison     | 619           |
| Cornish Pasty Company                     | Mesa        | 578           |
| SanTan Brewing Company                    | Chandler    | 469           |
| Yard House                                | Glendale    | 321           |
+-------------------------------------------+-------------+---------------+
10 rows selected (0.49 seconds)

Сравните #reviews для каждого бизнеса с верхним и нижним количеством отзывов в городе.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
0: jdbc:drill:zk=local> SELECT name, city, review_count,
. . . . . . . . . . . > FIRST_VALUE(review_count)
. . . . . . . . . . . > OVER(PARTITION BY city ORDER BY review_count DESC) AS top_review_count,
. . . . . . . . . . . > LAST_VALUE(review_count)
. . . . . . . . . . . > OVER(PARTITION BY city ORDER BY review_cout DESC) AS bottom_review_count
. . . . . . . . . . . > FROM `business.json` limit 15;
 
+----------------------------------------+------------+---------------+-------------------+----------------------+
|                  name                  |    city    | review_count  | top_review_count  | bottom_review_count  |
+----------------------------------------+------------+---------------+-------------------+----------------------+
| My Wine Cellar                         | Ahwatukee  | 98            | 124               | 12                   |
| McDonald's                             | Ahwatukee  | 7             | 124               | 12                   |
| U-Haul                                 | Ahwatukee  | 5             | 124               | 12                   |
| Hi-Health                              | Ahwatukee  | 4             | 124               | 12                   |
| Healthy and Clean Living Environments  | Ahwatukee  | 4             | 124               | 12                   |
| Active Kids Pediatrics                 | Ahwatukee  | 4             | 124               | 12                   |
| Cupz N' Crepes                         | Ahwatukee  | 124           | 124               | 12                   |
| Kathy's Alterations                    | Ahwatukee  | 12            | 124               | 12                   |
| Q to U BBQ                             | Anthem     | 74            | 117               | 117                  |
| Dara Thai                              | Anthem     | 56            | 117               | 117                  |
| Cafe Provence                          | Anthem     | 53            | 117               | 117                  |
| Shanghai Club                          | Anthem     | 50            | 117               | 117                  |
| Two Brothers Kitchen                   | Anthem     | 43            | 117               | 117                  |
| The Tennessee Grill                    | Anthem     | 32            | 117               | 117                  |
| Dollyrockers Boutique and Salon        | Anthem     | 30            | 117               | 117                  |
+----------------------------------------+------------+---------------+-------------------+----------------------+
15 rows selected (0.516 seconds)

Сравните #reviews с #reviews для предыдущего и следующего бизнеса

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
0: jdbc:drill:zk=local> SELECT city, review_count, name,
. . . . . . . . . . . > LAG(review_count, 1) OVER(PARTITION BY city ORDER BY review_count DESC)
. . . . . . . . . . . > AS preceding_count,
. . . . . . . . . . . > LEAD(review_count, 1) OVER(PARTITION BY city ORDER BY review_count DESC)
. . . . . . . . . . . > AS following_count
. . . . . . . . . . . > FROM `business.json` limit 15;
+------------+---------------+----------------------------------------+------------------+------------------+
|    city    | review_count  |                  name                  | preceding_count  | following_count  |
+------------+---------------+----------------------------------------+------------------+------------------+
| Ahwatukee  | 124           | Cupz N' Crepes                         | null             | 98               |
| Ahwatukee  | 98            | My Wine Cellar                         | 124              | 12               |
| Ahwatukee  | 12            | Kathy's Alterations                    | 98               | 7                |
| Ahwatukee  | 7             | McDonald's                             | 12               | 5                |
| Ahwatukee  | 5             | U-Haul                                 | 7                | 4                |
| Ahwatukee  | 4             | Hi-Health                              | 5                | 4                |
| Ahwatukee  | 4             | Healthy and Clean Living Environments  | 4                | 4                |
| Ahwatukee  | 4             | Active Kids Pediatrics                 | 4                | null             |
| Anthem     | 117           | Roberto's Authentic Mexican Food       | null             | 74               |
| Anthem     | 74            | Q to U BBQ                             | 117              | 64               |
| Anthem     | 64            | Outlets At Anthem                      | 74               | 56               |
| Anthem     | 56            | Dara Thai                              | 64               | 53               |
| Anthem     | 53            | Cafe Provence                          | 56               | 50               |
| Anthem     | 50            | Shanghai Club                          | 53               | 43               |
| Anthem     | 43            | Two Brothers Kitchen                   | 50               | 32               |
+------------+---------------+----------------------------------------+------------------+------------------+
15 rows selected (0.518 seconds)

Более подробную информацию и документацию по функциям Window и другим функциям Drill 1.2 можно найти в документах Drill и в документах MapR . Поздравляем сообщество Drill с очередной ключевой вехой и ждем большего.

Ссылка: Использование новых функций Drill 1.2 — функции ANSI SQL Analytic / Window от нашего партнера JCG Чейза Хули в блоге Mapr .
33
Share: