메뉴 건너뛰기

Cloudera, BigData, Semantic IoT, Hadoop, NoSQL

Cloudera CDH/CDP 및 Hadoop EcoSystem, Semantic IoT등의 개발/운영 기술을 정리합니다. gooper@gooper.com로 문의 주세요.


hive Hive Query Examples from test code (2 of 2)

총관리자 2014.03.26 11:06 조회 수 : 13505

Hive query for adding jar or script files

set jar=${system:build.ivy.lib.dir}/default/derby-${system:derby.version}.jar;

add file ${hiveconf:jar}; -- 추가
list file;  -- 리스트
delete file ${hiveconf:jar}; -- 삭제


Hive example for creating table using RegexSerDe

CREATE TABLE serde_regex(
  host STRING,
  identity STRING,
  user STRING,
  time STRING,
  request STRING,
  status STRING,
  size STRING,
  referer STRING,
  agent STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (-|\[[^\]]*\]) ([^ "]*|"[^"]*") (-|[0-9]*) (-|[0-9]*)(?: ([^ "]*|"[^"]*") ([^ "]*|"[^"]*"))?",
  "output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s"
)
STORED AS TEXTFILE;

아파치 로그 파일

../data/files/apache.access.log

127.0.0.1 - frank [10/Oct/2000:13:55:36 -0700] "GET /apache_pb.gif HTTP/1.0" 200 2326

아파치 로그 파일2

../data/files/apache.access.2.log

127.0.0.1 - - [26/May/2009:00:00:00 +0000] "GET /someurl/?track=Blabla(Main) HTTP/1.1" 200 5864 - "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/525.19 (KHTML, like Gecko) Chrome/1.0.154.65 Safari/525.19"


Hive query which contains “transform” using specific script.

set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
CREATE TABLE dest1(key INT, value STRING);

ADD FILE src/test/scripts/testgrep;

FROM (
  FROM src
  SELECT TRANSFORM(src.key, src.value)
         USING 'testgrep' AS (tkey, tvalue)
  CLUSTER BY tkey
) tmap
INSERT OVERWRITE TABLE dest1 SELECT tmap.tkey, tmap.tvalue;

SELECT dest1.* FROM dest1;

src/test/scripts/testgrep

#!/bin/bash
egrep '10.*'

exit 0;


Hive tablesamples example

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Sampling

-- TABLESAMPLES
CREATE TABLE bucketized_src (key INT, value STRING)
CLUSTERED BY (key) SORTED BY (key) INTO 1 BUCKETS;

INSERT OVERWRITE TABLE bucketized_src
SELECT key, value FROM src WHERE key=66;

SELECT key FROM bucketized_src TABLESAMPLE(BUCKET 1 out of 1);


Hive query for creating table using specific delimiters

create table impressions (imp string, msg string)
row format delimited
fields terminated by 't'
lines terminated by 'n'
stored as textfile;


기본 파티션 이름 설정

create table default_partition_name (key int, value string) partitioned by (ds string);

set hive.exec.default.partition.name='some_other_default_partition_name';

alter table default_partition_name add partition(ds='__HIVE_DEFAULT_PARTITION__');

show partitions default_partition_name;


문자열 상수 처리

SELECT 'face''book', 'face' 'book', 'face'
                                'book',
   "face""book", "face" "book", "face"
                                "book",
   'face' 'bo' 'ok', 'face'"book",
   "face"'book', 'facebook' FROM src LIMIT 1;

결과

facebook    facebook    facebook    facebook    facebook    facebook    facebook    facebook    facebook    facebook


Hive table lock examples

CREATE TABLE tstsrc (col1 STRING) STORED AS TEXTFILE;

SHOW LOCKS;
SHOW LOCKS tstsrc;
SHOW LOCKS tstsrc extended;

LOCK TABLE tstsrc shared;
UNLOCK TABLE tstsrc;


Hive partition lock examples

LOCK TABLE tstsrcpart PARTITION(ds='2008-04-08', hr='11') EXCLUSIVE;

SHOW LOCKS tstsrcpart PARTITION(ds='2008-04-08', hr='11') extended;

UNLOCK TABLE tstsrcpart PARTITION(ds='2008-04-08', hr='11');  


Hive virtual column

0.8.0 부터 INPUT_FILENAME, BLOCKOFFSETINSIDE_FILE 두 개 가상 컬럼 지원함

  • INPUT_FILE_NAME는 맵퍼 테스크의 파일 이름
  • BLOCK_OFFSETINSIDE_FILE는 현재 글로벌 파일 포지션

블락이 압축된 파일인 경운 현재 블락의 파일 오프셋은 현재 블락의 첫번째 바이트의 파일 오프셋이다.

select INPUT__FILE__NAME, key, BLOCK__OFFSET__INSIDE__FILE from src;


로컬 디렉토리에 결과 쓰기

FROM src INSERT OVERWRITE DIRECTORY '../build/contrib/hive/ql/test/data/warehouse/dest4.out' SELECT src.value WHERE src.key >= 300

dfs -cat ../build/contrib/hive/ql/test/data/warehouse/dest4.out/*;


Hive example for comparison of timestamp values

select cast('2011-05-06 07:08:09' as timestamp) > 
  cast('2011-05-06 07:08:09' as timestamp) from src limit 1;


Hive type casting

SELECT IF(false, 1, cast(2 as smallint)) + 3 FROM src LIMIT 1;


Show table properties in Hive

show tblproperties tmpfoo;
show tblproperties tmpfoo("bar");


Display functions in Hive CLI

SHOW FUNCTIONS;

SHOW FUNCTIONS '^c.*';

SHOW FUNCTIONS '.*e$';

SHOW FUNCTIONS 'log.*';

SHOW FUNCTIONS '.*date.*';

SHOW FUNCTIONS '***';


Show colums in Hive

CREATE TABLE shcol_test(KEY STRING, VALUE STRING) PARTITIONED BY(ds STRING) STORED AS TEXTFILE;

SHOW COLUMNS from shcol_test;


Reset hive settings

set hive.skewjoin.key;
set hive.skewjoin.mapjoin.min.split;
set hive.skewjoin.key=300000;
set hive.skewjoin.mapjoin.min.split=256000000;
set hive.skewjoin.key;
set hive.skewjoin.mapjoin.min.split;

reset;

set hive.skewjoin.key;
set hive.skewjoin.mapjoin.min.split;


Print column header in Hive CLI

set hive.cli.print.header=true;


프로그래스 heartbeat 간격

set hive.heartbeat.interval=5; 


DDL 관련 출력 포맷을 json으로 변경

set hive.ddl.output.format=json;

desc extended table_name;

set hive.ddl.output.format=text; -- 기본값
번호 제목 날짜 조회 수
47 Ubuntu 16.04 LTS에 Hive 2.1.1설치하면서 "Version information not found in metastore"발생하는 오류원인및 조치사항 2017.05.03 3088
46 Hive MetaStore Server기동시 Could not create "increment"/"table" value-generation container SEQUENCE_TABLE since autoCreate flags do not allow it. 오류발생시 조치사항 2017.05.03 3112
45 [hive] hive.tbls테이블의 owner컬럼값은 hadoop.security.auth_to_local에 의해서 filtering된다. 2022.04.14 3249
44 CDH 5.4.4 버전에서 hive on tez (0.7.0)설치하기 2016.01.14 3370
43 Caused by: java.net.URISyntaxException: Relative path in absolute URI: ${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D오류발생시 조치사항 2016.06.03 3429
42 hive에서 insert overwrite directory.. 로 하면 default column구분자는 'SOH'혹은 't'가 됨 2014.05.20 3590
41 hive기동시 Caused by: java.net.URISyntaxException: Relative path in absolute URI: ${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D 오류 발생시 조치사항 2016.09.25 3631
40 Hive JDBC Connection과 유형별 에러및 필요한 jar파일 2021.05.24 3724
39 impala session type별 표시되는 정보로 구분하는 방법 2021.05.25 3872
38 lateral view 예제 2014.09.18 3903
37 beeline으로 접근시 "User: gooper is not allowed to impersonate anonymous (state=08S01,code=0)"가 발생하면서 "No current connection"이 발생하는 경우 조치 2018.04.15 4036
36 schema설정없이 hive를 최초에 실행했을때 발생하는 오류메세지및 처리방법 2016.09.25 4055
35 hive 0.13.1 설치 + meta정보는 postgresql 9.3에 저장 2015.04.30 4065
34 hive metastore ERD file 2018.09.20 4090
33 hive metastore db중 TBLS, TABLE_PARAMS테이블 설명 2021.10.22 4160
32 AIX 7.1에서 hive실행시 "hive: line 86: readlink: command not found" 오류가 발생시 임시 조치사항 2016.09.25 4195
31 [sentry]role부여후 테이블명이 변경되어 오류가 발생할때 조치방법 2018.10.16 4198
30 json 값 다루기 2014.04.17 4217
29 dual table만들기 2014.05.16 4243
28 external partition table생성및 data확인 2014.04.03 4278
위로