Сравнение дат в Hive

Я работаю с Hive, и у меня есть таблица, структурированная следующим образом:

CREATE TABLE t1 (
  id INT,
  created TIMESTAMP,
  some_value BIGINT
);

Мне нужно найти каждую строку в t1 возрастом менее 180 дней. Следующий запрос не дает строк, хотя в таблице есть данные, соответствующие предикату поиска.

select * 
from t1 
where created > date_sub(from_unixtime(unix_timestamp()), 180);

Как правильно выполнить сравнение дат в Hive?


timestamp hive hiveql ddl
person Jeremiah Peschka    schedule 28.12.2012    source источник

Ответы (5)


arrow_upward
14
arrow_downward

Как насчет:

where unix_timestamp() - created < 180 * 24 * 60 * 60

Математика даты обычно проще всего, если вы можете просто сделать это с фактическими значениями метки времени.

Или вы хотите, чтобы он отключался только на целые дни? Тогда я думаю, что проблема в том, как вы конвертируете туда и обратно между целыми числами и строками. Пытаться:

where created > unix_timestamp(date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),180),'yyyy-MM-dd')

Проходя через каждую UDF:

  1. unix_timestamp() возвращает int: текущее время в секундах с начала эпохи
  2. from_unixtime(,'yyyy-MM-dd') преобразуется в строку заданного формата, например. '2012-12-28'
  3. date_sub(,180) вычитает 180 дней из этой строки и возвращает новую строку в том же формате.
  4. unix_timestamp(,'yyyy-MM-dd') преобразует эту строку обратно в int

Если все это становится слишком сложным, вы всегда можете написать UDF, чтобы сделать это самостоятельно.

person Joe K    schedule 28.12.2012
comment
Фантастическое объяснение решения, которое я использовал. Один вопрос - получится ли в этом случае сравнение между TIMESTAMP/STRING и INT? - person Jeremiah Peschka; 28.12.2012
comment
Далее - сравнение TIMESTAMP/STRING и INT работает. Спасибо за эту информацию, это упрощает запрос. - person Jeremiah Peschka; 28.12.2012

arrow_upward
6
arrow_downward

В качестве альтернативы вы также можете использовать datediff. Тогда предложение where будет
в случае временной метки String (формат jdbc):

datediff(from_unixtime(unix_timestamp()), created) < 180;

в случае эпохи Unix:

datediff(from_unixtime(unix_timestamp()), from_unixtime(created)) < 180;
person Lorand Bendig    schedule 29.12.2012

arrow_upward
3
arrow_downward

Я думаю, возможно, это ошибка Hive, связанная с типом timestamp. Я пытался использовать его недавно и получил неправильные результаты. Если я изменю вашу схему, чтобы использовать строку вместо временной метки и указать значения в

гггг-ММ-дд ЧЧ:мм:сс

формат, то у меня сработал запрос на выборку.

Согласно документации, Hive должен иметь возможность преобразовывать BIGINT, представляющий секунды эпохи, в метку времени, и все существующие пользовательские функции даты и времени работают с типом данных метки времени.

с помощью этого простого запроса:

выберите from_unixtime(unix_timestamp()), cast(unix_timestamp() as timestamp) из test_tt limit 1;

Я ожидал бы, что оба поля будут одинаковыми, но я получаю:

2012-12-29 00:47:43 1970-01-16 16:52:22.063

Я наблюдаю и другие странности.

person libjack    schedule 29.12.2012
comment
Похоже, об этом сообщается в HIVE-3454, но этого не произошло. была ли проделана какая-либо работа, чтобы исправить это, поскольку сопровождающие Hive хотят обеспечить обратную совместимость с нарушенным поведением. - person Jeremiah Peschka; 29.12.2012
comment
@JeremiahPeschka Спасибо, плюс есть обходной путь ... Я должен искать там и другие мои проблемы с отметками времени. - person libjack; 29.12.2012

arrow_upward
3
arrow_downward

TIMESTAMP в миллисекундах
unix_timestamp в секундах
Вам нужно умножить RHS на 1000.

where created > 1000 * date_sub(from_unixtime(unix_timestamp()), 180);
person user1089933    schedule 27.10.2013

arrow_upward
1
arrow_downward

После просмотра этого и ссылки на Разница дат менее 15 минут в Hive Я придумал решение. Хотя я не уверен, почему Hive не выполняет эффективное сравнение дат в виде строк (они должны сортироваться и сравниваться лексикографически), работает следующее решение:

FROM (
    SELECT  id, value,
            unix_timestamp(created) c_ts, 
            unix_timestamp(date_sub(from_unixtime(unix_timestamp()), 180), 'yyyy-MM-dd') c180_ts
    FROM    t1
) x
JOIN t1 t ON x.id = t.id
SELECT  to_date(t.Created), 
        x.id, AVG(COALESCE(x.HighestPrice, 0)), AVG(COALESCE(x.LowestPrice, 0))
WHERE   unix_timestamp(t.Created) > x.c180_ts
GROUP BY to_date(t.Created), x.id ;
person Jeremiah Peschka    schedule 28.12.2012