Позвольте мне попытаться объяснить несколько проблем, которые я вижу на переднем плане.
- Похоже, что ваш желаемый результат ожидает некоторых данных, которые являются частью местоположения файла пути, устройства и датчика, однако они не определены как часть определения вашей таблицы, а только столбцы в определении таблицы или виртуальные столбцы будут доступны.
- Несколько небольших файлов могут повлиять на производительность ваших запросов (но это не повлияет на желаемый результат)
- Разделы Hive используются для повышения производительности запросов, избегая сканирования всех данных. Разделы указывают на папки, в этом случае вы пытаетесь получить доступ к определенным файлам
- Ваш желаемый результат - это в основном разрушение 1 записи в нескольких записях, это не должно обрабатываться при определении таблицы, может быть выполнено с помощью вашего оператора select
- Разделы Hive имеют соглашение об именах
partitionname=partitionvalue, это не обязательно, но полезно, если вы хотите заранее использовать команды для автоматического добавления разделов на основе структуры ваших папок.
Вот как я бы решил вашу проблему, если вы будете в основном запрашивать по датчику или устройству.
Измените структуру ваших данных
Структура вашей папки в идеале должна быть от
farm_iot/sensor_data/farm/farm0001/sensor01/1541252701443
в farm_iot / sensor_data / farm / device = farm0001 / sensor = sensor01 / 1541252701443
Измените определение таблицы
Определение вашей таблицы должно содержать местоположения ваших разделов, чтобы иметь возможность выбрать его без регулярного выражения и воспользоваться его улучшением производительности (я предполагаю, что общий запрос будет фильтровать по устройству или датчику. Кроме того, вам нужно добавить все свои столбцы json которые являются частью вашего файла
CREATE EXTERNAL TABLE IF NOT EXISTS farm.sensor_data (
temperature double,
preassure double,
humidity double
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '1'
) LOCATION 's3://farm-iot/sensor_data/farm/'
PARTITIONED BY (device string, sensor string)
TBLPROPERTIES ('has_encrypted_data'='false')
Запрос ваших данных
Нам не хватает метки времени, которая по сути является частью вашего имени файла с входом json. Мы можем включить имя файла во время оператора выбора, используя виртуальный столбец INPUT__FILE__NAME, как показано ниже.
select device, sensor, temperature, preassure, humidity, INPUT__FILE__NAME as mytimestamp from farm.sensor_data
Если вам нужны предварительные условия, температура, влажность и разные строки, я бы порекомендовал создать массив из этих трех и взорвать его, должно быть намного эффективнее запускать 3 запроса с использованием UNION ALL для добавления результатов
Добавление нового раздела
Если вы следуете соглашению Hive, вы можете воспользоваться командой таблица ремонта msck для автоматического добавления новых разделов при добавлении новых устройств / датчиков. В худшем случае, если вы хотите сохранить структуру папок, вы можете добавить разделы следующим образом
ALTER TABLE test ADD PARTITION (device='farm0001', sensor='sensor01') location 's3://farm_iot/sensor_data/farm/farm0001/sensor01'
ВНИМАНИЕ: новые разделы не будут добавляться автоматически, их всегда нужно добавлять
Я постарался добавить как можно больше деталей. Если что-то непонятно, дайте мне знать.
РЕДАКТИРОВАТЬ: Если ваши запросы будут в основном основаны на временных рядах (например, диапазоне дат), я бы рекомендовал добавить раздел на дневном уровне (не меньше этого), чтобы улучшить производительность ваших запросов. Итак, ваше определение таблицы будет выглядеть как
CREATE EXTERNAL TABLE IF NOT EXISTS farm.sensor_data (
temperature double,
preassure double,
humidity double
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '1'
) LOCATION 's3://farm-iot/sensor_data/farm/'
PARTITIONED BY (dt=long, device string, sensor string)
TBLPROPERTIES ('has_encrypted_data'='false')
И ваша структура папок будет выглядеть как
farm_iot / sensor_data / farm / dt = 20191204 / device = farm0001 / sensor = sensor01 / 1541252701443
В качестве пояснения, вам не нужно изменять таблицу для каждого нового раздела, только добавьте эти разделы в таблицу, по сути, это то, как Hive узнает, что был создан новый раздел. Если вы решите использовать разделы, это единственный способ, если вы этого не сделаете (это повлияет на производительность), есть другие альтернативы, чтобы заставить его работать
РЕДАКТИРОВАТЬ2:
Если вы хотите сохранить структуру данных как есть и не использовать разделы, можно получить ожидаемые результаты, как показано ниже.
CREATE EXTERNAL TABLE IF NOT EXISTS yourdb.sensordata (
temperature double,
pressure double,
humidity double
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '1'
)
LOCATION 's3://farm-iot/sensor_data/farm/'
TBLPROPERTIES ('has_encrypted_data'='false');
SET hive.mapred.supports.subdirectories=TRUE;
SET mapred.input.dir.recursive=TRUE;
select * from yourdb.sensordata;
select
split(input__file__name, "/")[size(split(input__file__name, "/")) - 1] as ts,
split(input__file__name, "/")[size(split(input__file__name, "/")) - 3] as device,
split(input__file__name, "/")[size(split(input__file__name, "/")) - 2] as sensor,
'temperature' as data_point,
temperature as value
from yourdb.sensordata
union all
select
split(input__file__name, "/")[size(split(input__file__name, "/")) - 1] as ts,
split(input__file__name, "/")[size(split(input__file__name, "/")) - 3] as device,
split(input__file__name, "/")[size(split(input__file__name, "/")) - 2] as sensor,
'pressure' as data_point,
pressure as value
from yourdb.sensordata
union all
select
split(input__file__name, "/")[size(split(input__file__name, "/")) - 1] as ts,
split(input__file__name, "/")[size(split(input__file__name, "/")) - 3] as device,
split(input__file__name, "/")[size(split(input__file__name, "/")) - 2] as sensor,
'humidity' as data_point,
humidity as value
from yourdb.sensordata;
Как видите, я получаю большую часть информации из пути к файлу, однако необходимо установить некоторые флаги, чтобы сообщить Hive о рекурсивном чтении папок.
ts,device,sensor,_data_point,value
1541252701443,farm0001,sensor01,temperature,14.78
1541252701443,farm0001,sensor01,pressure,961.7
1541252701443,farm0001,sensor01,humidity,68.32
person
hlagos
schedule
04.12.2018
