Hive SQL - String을 Datetime로 변환, 두개의 Datetime사이 초수 구하기

회사에서의 업무가 PM업무이다 보니 실제로 직접 코딩을 하거나 DB를 쿼리 할 일이 갈수록 줄어든다ㅠㅜ

하지만 가끔은 직접 무언가를 하는 게 빠를 때가 있다. 스스로에게도 자극도 되는 것 같고ㅎㅎ

 

이번에 진행한 프로젝트의 결과로 만들어진 하둡 에코시스템((Hadoop-Ecosystem)에 포함된

Hive SQL 을 활용해서 직접 데이터 쿼리를 진행해 본다.

몇 년 전에 간단한 오라클 DB 프로시저 코드를 알티베이스 DB프로시저 코드로 포팅해본 이후 너무 오랜만이다.

역시 다 리셋됐다ㅠㅜ 또 리셋되기 전에 기록해 둔다.

 

String으로 저장된 Datetime 값으로 초수 계산하면 발생되는 오류

작업하려는 내용은 DB에 저장된 시작시간과 종료시간으로  접속 시간(초수)을 계산하는 게 목표다.

그런데 DB에는 String형태로 데이터가 들어 있었다.

String데이터를 단순히 뺄셈으로 연산해버리면 통계가 완전히 틀어진다.

예를 들어 종료시간이 '20220717160228' 이고 시작시간이 '20220717160158' 라면 30초가 계산돼야 하지만

단순 뺄셈을 해버리면 70초로 계산된다;;;;

처음에는 이런 오류가 있을 거라는 걸 제대로 고민해보지도 않고 그냥 쿼리를 했다가 통계치가 이상해서 한참을 헤맸다.

 

String을 Datetime으로 변환하기(unix_timestamp, from_unixtime)

그래서 단순 계산이 아니라 날짜로 변환해서 계산을 해보기로 했다.

Hive SQL함수 중에 String으로 저장된 날짜+시간 데이터를 바로 Datetime 형태로 변환하는 함수는 없는 것 같다.

 

그래서 방법은

1. unix_timestamp함수로 String 데이터를 Timestamp로 변환을 먼저 하고
2. from_unixtime함수로 변환된 Timestamp 값을 Datetime으로 변환하면 된다.

SELECT from_unixtime(unix_timestamp('20220717160202', 'yyyyMMddHHmmss'),"yyyy-MM-dd HH:mm:ss");

결과 > 2022-07-17 16:02:02

SELECT from_unixtime(unix_timestamp('20220717160148', 'yyyyMMddHHmmss'),"yyyy-MM-dd HH:mm:ss");

결과 > 2022-07-17 16:01:48

 

이렇게 String 데이터가 Datetime 데이터로 변환이 됐으니 두 개의 Datetime을 연산해봤다.

그런데;;;; 으잉???? 오류가 발생된다ㅠ

SELECT from_unixtime(unix_timestamp('20220717160202', 'yyyyMMddHHmmss'),"yyyy-MM-dd HH:mm:ss")-from_unixtime(unix_timestamp('20220717160148', 'yyyyMMddHHmmss'),"yyyy-MM-dd HH:mm:ss");

결과 > NULL

열심히 찾아보니 날짜 Hive에는 두 개의 Datetime 사이의 일수를 계산해주는 Datediff 함수는 있는데

초수를 계산해주는 함수는 없는 것 같다ㅠ

 

두 개의 Datetime 사이 초수 계산하기

계산을 하려는 대상은 초수다.

예전 앱 개발 등을 할 때의 기억을 더듬어 보니 이럴 때 가장 정확한 방법은 역시 Timestamp로 변환해서 계산하는 게 가장 정확했다.

마침 이미 Timestamp로 변환하는 건 알아냈으니 계산만 하면 된다. 그리고 정확한 결과를 얻을 수 있었다ㅎㅎ

SELECT unix_timestamp('20220717160202', 'yyyyMMddHHmmss');

결과 > 1658041322

SELECT unix_timestamp('20220717160148', 'yyyyMMddHHmmss'),

결과 > 1658041308

SELECT unix_timestamp('20220717160202', 'yyyyMMddHHmmss')-unix_timestamp('20220717160148', 'yyyyMMddHHmmss')

결과 > 14