메뉴 건너뛰기

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 조회 수 : 11466

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 [CDP7.1.7, Hive Replication]Hive Replication진행중 "The following columns have types incompatible with the existing columns in their respective positions " 오류 2023.12.27 114
46 [hive] hive.tbls테이블의 owner컬럼값은 hadoop.security.auth_to_local에 의해서 filtering된다. 2022.04.14 167
45 hive의 메타정보 테이블을 MariaDB로 사용하는 경우 table comment나 column comment에 한글 입력시 깨지는 경우 utf8로 바꾸는 방법. 2023.03.10 262
44 CDH 5.4.4 버전에서 hive on tez (0.7.0)설치하기 2016.01.14 298
43 impala session type별 표시되는 정보로 구분하는 방법 2021.05.25 417
42 [sentry]role부여후 테이블명이 변경되어 오류가 발생할때 조치방법 2018.10.16 427
41 beeline으로 접근시 "User: gooper is not allowed to impersonate anonymous (state=08S01,code=0)"가 발생하면서 "No current connection"이 발생하는 경우 조치 2018.04.15 488
40 AIX 7.1에서 hive실행시 "hive: line 86: readlink: command not found" 오류가 발생시 임시 조치사항 2016.09.25 519
39 Ubuntu 16.04 LTS에 Hive 2.1.1설치하면서 "Version information not found in metastore"발생하는 오류원인및 조치사항 2017.05.03 567
38 hive 0.13.1 설치 + meta정보는 postgresql 9.3에 저장 2015.04.30 604
37 Hive MetaStore Server기동시 Could not create "increment"/"table" value-generation container SEQUENCE_TABLE since autoCreate flags do not allow it. 오류발생시 조치사항 2017.05.03 606
36 hive metastore db중 TBLS, TABLE_PARAMS테이블 설명 2021.10.22 610
35 hive기동시 Caused by: java.net.URISyntaxException: Relative path in absolute URI: ${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D 오류 발생시 조치사항 2016.09.25 615
34 hive metadata(hive, impala, kudu 정보가 있음) 테이블에서 db, table, owner, location를 조회하는 쿼리 2020.02.07 620
33 Tracking URL = N/A 가발생하는 경우 - 환경설정값을 잘못설정하는 경우에 발생함 2015.06.17 694
32 lateral view 예제 2014.09.18 775
31 beeline실행시 User: root is not allowed to impersonate오류 발생시 조치사항 2016.06.03 852
30 hive metastore ERD file 2018.09.20 876
29 hive테이블의 물리적인 위치인 HDFS에 여러개의 데이터 파일이 존재할때 한개의 파일로 merge하여 동일한 테이블에 입력하는 방법 2019.05.23 895
28 hive query에서 mapreduce돌리지 않고 select하는 방법 2014.05.23 897
위로