На этапе передачи больших данных в процессе ETL (извлечение-передача-загрузка) данные строки json необходимо преобразовать и «сгладить».
Персональный тест! Супер простой в использовании Hive встроенный json аналитическая функция Подробное введение в статье get_json_object и json_tuple Как работает функция json Строку можно эффективно проанализировать, но единственным недостатком является то, что ни одна функция не может ее проанализировать. json Массив, может анализировать только один json нить.
Сегодняшний обмен познакомит вас Hive Обычно используется в json множествоаналитическая функцияи подробные инструкции по использованию。
Например: в Hive есть таблица test_json. Содержимое поля json_data в таблице следующее:
json_data |
---|
[{"user_id":"1","name":"Сяо Линь","age":16},{"user_id":"2","name":"Сяо Лю","age":18} ,{"user_id":"3","name":"Сяо Мин","возраст":20}] |
На основе приведенных выше данных json_data теперь необходимо проанализировать приведенные выше строковые данные json и получить следующие структурные данные:
user_id | name | age |
---|---|---|
1 | Сяолинь | 16 |
2 | Сяо Лю | 18 |
3 | Сяо Мин | 20 |
Прежде чем анализировать, давайте сначала поймем, как использовать следующие две функции.
грамматика
explode(Array|Map)
иллюстрировать
Функция взорвать() получает на вход данные типа массива или карты, а затем выводит элементы массива или карты в виде каждой строки.
То есть разделение сложного массива или структуры карты в одном столбце Hive на несколько строк для отображения, также известное как функция «столбец-строка».
Пример
SQL-запрос для проверки массива:
select explode(array('user_id','name','age'));
Результат выполнения:
SQL-запрос для проверки карты:
select explode(map('user_id',1,'name','rocky','age',18));
Результат выполнения:
грамматика
regexp_replace(str A, str B, str C)
иллюстрировать
грамматиказначение:преобразовать строку A Сопоставление регулярных выражений в B Замените деталь на C。
Примечание. Если в строке A есть специальные символы, в регулярном выражении B необходимо использовать escape-символы.
Пример
SQL-заявление:
select regexp_replace('hello world!', '\\ |\\!', '');
Результат выполнения:
учиться explode функция с regexp_replace После правил использования функций, теперь завершим вышеизложенную подготовку. проанализировать требование, представленное в данных.
Первый шаг анализа: разбить массив json на несколько строк
SQL-заявление:
SELECT explode(split(
regexp_replace(
regexp_replace(
'[
{"user_id":"1","name":"Сяолинь","age":16},
{"user_id":"2","name":"Сяо Лю","age":18},
{"user_id":"3","name":"Сяо Мин","age":20}
]',
'\\[|\\]' , ''), Удалите квадратные скобки с обеих сторон массива json.
'\\}\\,\\{' , '\\}\\;\\{'), Замените запятые между элементами массива json точками с запятой.
'\\;') Используйте точку с запятой в качестве разделителя (функция разделения использует точку с запятой в качестве разделителя)
);
Результат выполнения:
Второй этап анализа: ключ массива JSON в поле столбца
SQL-заявление:
select json_tuple(json, 'user_id', 'name', 'age')
from (select explode(split(
regexp_replace(
regexp_replace(
'[
{"user_id":"1","name":"Сяолинь","age":16},
{"user_id":"2","name":"Сяо Лю","age":18},
{"user_id":"3","name":"Сяо Мин","age":20}
]',
'\\[|\\]' , ''),
'\\}\\,\\{' , '\\}\\;\\{'),
'\\;')
)as json) tmp;
Результат выполнения:
Например:
В Улье есть один data_json стол, стол goods_id и str_data Содержимое полей следующее:
goods_id | str_data |
---|---|
5,7,9 | [{"source":"taobao","sold":100,"remain":1000},{"source":"jd","sold":200,"remain":2000},{"source":"meituan","sold":300,"remain":3000}] |
На основании вышеизложенного goods_id и str_data данные, теперь необходимо преобразовать приведенные выше json Строковые данные анализируются в следующие структурированные данные:
goods_id | sold |
---|---|
5 | 100 |
5 | 200 |
5 | 300 |
7 | 100 |
7 | 200 |
7 | 300 |
9 | 100 |
9 | 200 |
9 | 300 |
Прежде чем анализировать, давайте сначала поймем, как использовать следующие две функции.
иллюстрировать
lateral view Используется для split, explode ждать UDTF При совместном использовании он может разделить столбец данных на несколько строк данных, по которым разделенные данные могут быть агрегированы.
Латеральный просмотр сначала вызывает UDTF для каждой строки исходной таблицы. Латеральный просмотр объединяет результаты для создания виртуальной таблицы, поддерживающей таблицы псевдонимов.
Пример
Например:Hive Один из них page_ads Таблица, структура данных таблицы следующая:
page_name | ads_id |
---|---|
home_page | [1,2,3] |
front_page | [2,6] |
page_name представляет имя страницы,ads_id представляет собой идентификатор рекламы, а несколько идентификаторов разделяются запятыми.
Требование: подсчитайте, сколько раз все рекламные идентификаторы появляются на всех страницах.
Первый этап анализа: разделение рекламного идентификатора
РасколотьSQL-заявление:
SELECT page_name, ads_id
FROM page_ads LATERAL VIEW explode(ads_id) adTable AS adid;
Результаты разделения:
page_name | ads_id |
---|---|
home_page | 1 |
home_page | 2 |
home_page | 3 |
front_page | 2 |
front_page | 6 |
Второй этап анализа: агрегированная статистика
Агрегированная статистика SQL-заявления:
SELECT adid, count(1)
FROM page_ads LATERAL VIEW explode(ads_id) adTable AS adid
GROUP BY adid;
Статистические результаты:
adid | count(1) |
---|---|
1 | 1 |
2 | 2 |
3 | 1 |
6 | 1 |
Инструкция sql для анализа таблицы data_json выглядит следующим образом:
select goods_id,get_json_object(sale_json,'$.sold') as sold
from data_json
LATERAL VIEW explode(split(goods_id,','))goods as goods_id
LATERAL VIEW explode(split(
regexp_replace(
regexp_replace(json_str , '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;')) sales as sale_json;
Уведомление:
Приведенное выше утверждение является результатом декартова произведения 3*3, поэтому этот метод подходит для ситуаций, когда объем данных не очень велик.
Результаты выполнения следующие:
goods_id | sold |
---|---|
5 | 100 |
5 | 200 |
5 | 300 |
7 | 100 |
7 | 200 |
7 | 300 |
9 | 100 |
9 | 200 |
9 | 300 |