언어 꿀Tip/Hadoop Hive (5) 썸네일형 리스트형 [hive/impala] 정규표현식 이용한 숫자 추출하기 select nm ,regexp_extract(nm,'[0-9]+', 0) as number from table [Hive] 04. 날짜 다루기 (string -> date) string 형태로 된 날짜값을 날짜type으로 변경하기!! select from_unixtime(unix_timestamp(string_col, 'yyyymmdd'), 'yyyy-mm-dd') # 2020-01-01 ,from_unixtime(unix_timestamp(string_col, 'yyyymmdd'), 'yyyymmdd') # 20200101 from table ; [Impala] 03. 테이블 Create 생성 + partition 추가 create table Schema.tablename ( col1 string, col2 decimal(38,2) ) partitioned by (partition_col col_type) stored as parquet; insert into Schema.tablename partition(partition_col) select col1 ,col2 ,partition_col from table ; [Hive] 02.현재 날짜 다루기 select from_unixtime(unix_timestamp()) ,from_unixtime(unix_timestamp(), 'yyyymmdd') ,from_unixtime(unix_timestamp(), 'yyyy-mm-dd') ,cast(from_unixtime(unix_timestamp(), 'yyyy-mm-dd') as date); [결과] [impala] 01. 현재 날짜 다루기, string to date format select now() as today_timestamp ,to_date(now()) as today_date ,from_timestamp(to_date(now()) , 'yyyyMMdd') as dt ,from_timestamp(now(), 'yyyyMMdd') as dtt; select dt --'20210531' ,from_unixtime(unix_timestamp(dt, 'yyyyMMdd'), 'yyyy-MM-dd') as dt_date -'2021-05-31' 이전 1 다음
