'SQL'에 해당되는 글 3건

  1. 2011.02.23 Hive : DDL, DML, SQL Operations
  2. 2011.02.23 Hive : Installation and Configuration
  3. 2010.06.22 SQL query for pagination

DDL Operation

하이브 테이블을 만들고 결과를 보여준다.

hive> CREATE TABLE pokes (foo INt, bar STRING);

두 개의 컬럼이 있는 pokes 테읍ㄹ을 생성한다. 첫 번째는 정수, 두 번째는 문자열이다.

hive> CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);

두개의 컬럼을 가지고 한 개의 파티션 컬럼을 가지는 invites 테이블은 생성한다. 파티션 걸럼은 가상 컬럼이다. 이것은 데이타 자체의 일부분이 아니다. 특별한 데이터 셋이 로드되는 파티션으로 부터 유래한다.
디폴트로 테이블들은 text input format이고 구분자는 ^A(crt-a)라고 가정한다.

hive> SHOW TABLES;

테이블의 목록을 보여준다.

hive> SHOW TABLES ‘.*s’;

‘s’로 끝나는 테이블의 모든 목록을 보여준다. 이 패턴 매칭은 Java regular expressions을 따른다.

hive> DESCRIBE invites;

컬럼의 목록을 보여준다.

테이블 변경에서 테이블 이름은 변경될 수 있다. 그리고 추가 컬럼도 drop 될 수 있다.

hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);
hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT ‘a comment’);
hive> ALTER TABLE events RENAME TO 3koobecaf;

테이블 drop

hive> DROP TABLE pokes;

 

Metadata Store

메타데이터는 javax.jdo.option.ConnectionURL 의 이름의 하이브 설정 변수에 의해 디스크 스토리의 위치 결정되는  임베디드 더비 데이터베이스이다.  초기 값은 ./metasore_db 이다.

메타스토어는  JPOX를 지원하는 어떤 데이타베이스에도 저장될 수 있다. 위치와 RDBMS의 타입은 javax.jdo.option.ConnectionURL 과 javax.jdo.option.ConnectionDriverName 두 변수에 의해서 조정된다.
지원하는 데이터베이스들의 좀 더 자세항 사항은 JDO(또는 JPOX) 문서를 참조해라 테이터베이스 스키마는  src/contrib/hive/metasore/src/model에 있는 JDO 메타데이터 어노테이션 파일 package.jdo 에 정의 되어 있다.

향후에는 메타스토어 자체가 stand-alone 서버가 될 것이다.

DML Operation

flat 파일의 데이타를 Hive로 로드한다.

hive> LOAD DATA LOCAL INPATH ‘./examples/files/kv1.txt’ OVERWRITE INTO TABLE pokes;

ctrl-a 로 나눠진 두 컬럼 포함하는 파일을 pkes 테이블로 로드한다.  ‘local’ 이라고 명시하는 것은 입력파일이 로컬 파일 시스템에 있다는 것이다. ‘local’을 빼면 HDFS 에 있는 파일을 찾는다. ‘overwrite’ 키워드는 테이블의 기존의 데이타는 삭제됨을 명시한다. ‘overwrite’ 키워드가 빠지면 데이터 파일은 기존 데이터 셋에 추가된다.

알아야 할 점

  • 로드 커맨드에 수행되는 스키마에 대한 데이터 검증(verification)은 없다
  • 만약 HDFS에 파일이 있다면 그것은 Hive-controlled 파일 시스템 네임스페이스로 이동한다.
    하이브 디렉토리의 루트는 hive-default.xml 파일에 hive.metastore.warehouse.dir 옵션에 의해 지정된다. 하이브에서 테이블을 생성하기 전에 이 디렉토리를 만들 것을 사용자에게 충고한다
hive> LOAD DATA LOCAL INPATH ‘./examples/files/kv2.txt’ OVERWRITE INTO TABLE invites PARTITION (ds=’2008-08-15)’;
hive> LOAD DATA LOCAL INPATH ‘./.examples/files/kv3.txt’ OVERWRiTE INTO TABLE invites PARTITION  (ds=’2008-08-08’);

위에 두 개의 LOAD 명령은 invites 테이블의 두 개의 다른 파티션으로 데이터를 로드한다. invites 테이블은 ds 키에 의해서 파티션되어 생성된다.

hive> LOAD DATA INPATH ‘/user/myname/kv2.txt’ OVERWRITE INTO TABLE invites PARTITION (ds=’2008-08-15)’;

위에 커맨드는 HDFS 파일/디렉토리로 부터 데이터를 읽어 table에 로드한다. HDFS로 부터 데이터를 로드하는 것의 결과는 파일/디렉토리로 이동되는 것이다. operation은 거의 즉시 수행된다.

SQL Operation

Example Queries

예제 쿼리는 build/dist/examples/queries에 있다. 더 많은 쿼리는 ql/src/test/queries/positive 에 있다

SELECTS and FILTERS

hive> SELECT a.foo FROM invites a WHERE a.ds=’2008-08-15’;

invites 테이블의 파티션 ds=2008-08-15의 모든 로우에서 foo 컬럼을 선택한다. 결과는 어디에도 저장되지 않는다. 그러나 콘솔 화면에 디스플레이 된다.
다음 예제들에서 INSERT(하이브 테이블이나, 로컬 디렉토리, HDFS 디렉토리)는 선택적이다.

hive> INSERT OVERWRITE DIRECTORY ‘/tm/hdfs_out’ SELECT a.* FROM invites a WHERE a.ds=’2008-08-15’;

쿼리의 결과를  HDFS 디렉토리에 저장한다. 결과 데이터는 디렉토리 파일 안에 있다. (mapper의 개수의 의존한다)
파티션된 테이블은 항상 WHERE 절에 파티션 선택해야 한다.

hive> INSERT OVERWRITE LOCAL DIRECTORY ‘/tmp/local_out’ SELECT a.* FROM pokes a;

pokes 테이블의 모든 로우를 선택하여 로컬 디렉토리로 로드한다.

hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a;
hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a WHERE a.key < 100;
hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/reg_3' SELECT a.* FROM events a;
hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_4' select a.invites, a.pokes FROM profiles a;
hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT COUNT(*) FROM invites a WHERE a.ds='2008-08-15';
hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT a.foo, a.bar FROM invites a;
hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/sum' SELECT SUM(a.pc) FROM pc1 a;

컬럼의 합(SUM), 평균(arg), 최소값(min), 최대값(max)이 사용 될 수 있다.

GROUP BY

hive> FROM invites a INSERT OVERWRITE TABLE events ELECT a.bar, count**) WHERE a.foo > 0 GROUP BY a.bar
hive> INSERT OVERWRITE TABLE events SELECT a.bar, count(*) FROM invites a WHERE a.foo > 0 GROUP BY a.bar;

MULTITABLE INSERT

FROM src
INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100
INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200
INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE src.key >= 300;

JOIN

hive> FROM pokes t1 JOIN ivites t2 ON (t1.bar=t2.bar) INSERT OVERWRITE TABLE evners SELECt t1.bar, t2.bar,t2.foo;

 

STREAMING

hive> FROM invites a INSERT OVERWRITE TABLE event SELECT TRANSFORM (a.foo, b.bar) AS (off,rb) USING ‘/bin/cat’ WHERE a.ds>’2008-0809

 

Reference

Posted by 김민우 julingks
TAG ddl, dml, Hadoop, hive, NoSQL, SQL

Hive는  하둡 위에서 돌아가는 데이터 웨어하우스 인프라 스트럭처다.   하둡에 저장된 데이터를 요약, adhoc  쿼리, 큰 데이터셋의 분석을 쉽게 만들어주는 도구를 제공한다.  Hive는 이 데이타 위의 스트럭처를 집어 넣는 매커니즘을 제공한다. 이 데이터에 쿼리 요청을 위해서 SQL과 친숙한 SQL의 기반의 Hive QL로 불리는 간단한 쿼리 언어를 제공한다. 동시에 이 언어는 내장 언어가 제공하지 않는 좀 더 정교한 분석을 하기 위해 전통적인 맵/리듀스 프로그래머가 커스텀 mapper와 reducer를 연결할 수 있게 해준다.

Installation and Configuration

Requirements

  • Java 1.6
  • Hadoop 0.17.x to 0.20.x.

Installing HIve a Stable Release

아파치 다운로드 미러에서 안정된 배포 버전을 다운 받는다.압축을 푼다.

$ tar -xzvf hive-x.y.z.tar.gz

HIVE_HOME환경변수를 설치한 디렉토리를 가리키도록 설정한다.

$ cd hive-x.y.z
$ export HIVE_HOME=`pwd`

마지막으로 $HIVE_HOME/bin 을 PATH에 추가한다

$ export PATH=$HIVE_HOME/bin:$PATH

Running Hive

하이브는 하둡을 사용한다는 것은

  • PATH에 하둡이 있어야 한다.
  • export HADOOP_HOME=<hadoop-install-dir>

추가로 /tmp 와 /user/hive/warehouse를 만들어야 한다. HDFS에서 chmod g+w 정해야 한다. 그래야 하이브가 테이블을 만들 수 있다.

$HADOOP_HOME/bin/hadoop fs -mkdir /tmp
$ $HADOOP_HOME/bin/hadoop fs -mkdir /user/hive/warehouse
$ $HADOOP_HOME/bin/hadoop fs -chmod g+w /tmp
$ $HADOOP_HOME/bin/hadoop fs -chmod g+w /user/hive/warehouse

하이브 커맨드 라인 인터페이스 사용한다

$ $HIVE_HOME/bin/hive

 

Configugration management Overview

하이브 default 설정은 <install-dir>/conf/hive-default.xml에 저장되어 있다.
설정 변수는 <install-dir>/conf/hive-site.xml 에서 정의된 것의 따라 변경될 수 있다.
하이브 설정 디렉토리의 위치는 HIVE_CONF_DIR 환경 변수 설정에 의해 변경될 수 있다.
Log4j 설정은 <install-dir>/conf/hive-log4j.properties에 저장되었다.
하이브 설정은 하둡 위에 덮어 쓴다. - 즉 하둡 설정 변수를 초기값으로 상속 받는다.
하이브 설정은 다음에 의해 조작된다.

  • hive-site.xml을 편집하기나 알맞는 변수(하둡 변수 포함)를 정의한다.
  • cli에서 set 커맨드를 사용한다.
  • 다음 syntax를 사용하여 하이브를 실행한다
    - $ bhin/hive -hiveconf x1=y1 -hiveconf x2=y2
    - HIVE_OPTS 환경변수를 위와 같이 “-hiveconf x1=y1 -hiveconf x2=y2” 로 지정한다

Runtime Configuration

  • Hive 퀄리는 map-reduce 쿼리들을 이용해서 실행된다. 따라서 그런 쿼리들은 하둡 설정 변수에 의해 control 된다.
  • cli 커맨드 ‘SET’ 은 하둡 (또는 하이브) 설정 변수를 지정하는데 사용될 수 있다. 예를 들면
hive> SET mapred.job.tracker=myhost.mycompany.com:50030;
hive> SET -v;

-v 는 현재의 모든 설정들을 보여준다. -v 옵션이 없다면 오직 기본 하둡 설정과 다른 변수만 표시된다

Hive, Map-Reduce and Local-Mode

Hive 컴파일러는 대부분의 쿼리들의 map-reduce job들을 생성한다. 이 job들은 다음 변수가 가리키는 Map-Reduce 클러스터에 제출된다.

mapred.job.tracker

보통 map-reduce 클러스터는 여러 개의 노드를 가리키지 않는 반면에, 하둡 또한 유저 워크스테이션 위에서 map-reduce job들을 로컬에서 실행하기 위한 멋진 옵션을 제공한다. 이 것은 작은 데이터 셋에 쿼리를 실행할 때 매우 유용할 수 있다. 이 경우에 로컬 모드 실행은 보통 대형 클러스터에 job을 제출하는 것 보다 상당히 빠르다. 데이터는 HDFS에서 투명하게 접근할 수 있다. 반대로 로컬 모드는 오직 하나의 reducer 만 실행하며 큰 데이터 셋 처리에서는 매우 느려진다.

0.7버전부터 하이브는 로컬 모드 실행을 완전히 지원한다. 이것을 가능하게 하기 위해 사용자는 다음 옵션을 지정해야 한다.

hive> SET mapred.job.tracker=local;

추가로, mapred.local.dir 로컬 머신 위에 유요한 경로를 가리켜야 합니다. (예를 들면 /tmp/<username>/mapred/local) (그러지 않으면, 사용자는 로컬 디스크 스페이스를 할당 받으라는 예외를 얻게 된다.)

0.7 버전부터, 하이브는 또한 자동적으로 로컬 모드에서 map-reduce job들을 실행하기 위한 모드를 제공한다. 관련된 옵션은 다음과 같습니다.

hive> SET hive.exec.mode.local.auto=false;

이 특징은 초기 값이 false임을 명심하자. 만약 ture라면, 하이브는  쿼리에서 각각 map-reduce job의 크기를 분석한다.
다음 thresholds가 만족된다면 로컬에서 실행이 될 수도 있다.

  • job의 총 입력 크기가 hive.exec.mode.local.auto.inputbytes.max  (초기값 128MB) 보다 작을 때
  • 총 map-task의 개수가 havie.exec.mode.local.auto.tasks.max( 초기값 4)
  • 총 필요한 reduce-task의 개수가 1 또는 0

따라서 작은 데이터 셋의 쿼리와 다음 job들의 입력이 실질적으로 더 작을 때 여러 개 map-reduce job들 위한 쿼리에서 job들은 로컬에서 실행될 수 있다.

하둡 서버 노드들의 runtime 환경과 하이브 클라이언트를 실행하는 머신의 차이점이 있을 수 있다는 것을 명심해라. (vm 버전이나 다른 소프트웨어 라이브러리 때문에) 이는 로컬 모드에서 실행 중 예기치 못한 행동이나 에러를 유발할 수 잇다. 또한 로컬 모든 실행은 child jvm(하이브 클라이언트의)으로 분리되어 실행된다. 사용자가 정말 원한다면, child jvm을 위한 최대 메모리 양을 hive.mapred.local.mem. 옵션을 통해서 조정할 수 있다. 초기값은 0 이다. 이 경우에 하이브는 하둡이 child jvm의 초기 메모리 한계를 정하도록 한다.

Error Logs

하이브는 로깅을 위해 log4j를 사용한다.  디폴트 로그들은 CLI에 의해 콘솔로 방출되지 않는다. 디폴트 로깅 레벨은 WARN 이다. 로그는 다음 폴더에 저장된다.

  • /tmp/{user.name}/hive.log

사용자가 원한다면 밑에 보여지는 arguments를 추가하여 콘솔로 방출하게 할 수 있다.

  • bin/hive -hiveconf hive.root.logger=INFO,console

사용자는 오직 다음을 이용해서 로깅 레벨을 변경할 수 있다.

  • bin/hive -hiveconf hive.root.logger=INFO,DRFA

set 커맨드를 이용해서 hive.root.logger를 정하는 것은 로깅의 property를 변경하지 않는다. property는 초기화 시간에 결정된다.

하둡 클러스터에서 하이브 실행 중 로깅은 하둡 설정에 의해 통제된다. 보통 하둡은 테스크가 실행되었을 때 클러스터 머신에 저장된 맵과 리듀스 테스크 당 하나의 로그파일을 생성한다, 로그 파일은 하둡의 JobTracker 웹 UI에서 Task Details 페이지를 클릭 해서 얻을 수 있다.

로컬 모드를 사용할 때, 하둡/하이브 실행 로그는 클라이언트 머신 자체에서 생성된다. 0.6 버전부터는 하이브는 디폴트로 로그가 어디에 전달될지를 결정하기 위해 hive-exec-log4j.properties 를 사용한다. 디폴트 설정은 로컬 모드에서 실행된 쿼리 당 하나의 로그 파일이 생성된다. 그리고 이것은 /tmp/{user.name} 밑에 저장된다. 설정파일을 분리하여 생성하는 의도는 관리자가 만약 바람직하다면 실행 로그 캡쳐를 집중화 할 수 있도록 하기 위함이다.  실행 로그는 runt-time 에러를 디버깅 하는 데는 가치가 없다.

Reference


Posted by 김민우 julingks
페이지네이션을 위한 SQL query

LIMIT 는 SQL statement의 결과의 숫자를 제한할 때 사용한다.
만약 table에 1000 개의 row가 있다면, 다음과 같은 쿼리로 처음부터 10개의 결과를 얻을 수 있다.
SELECT column FROM table LIMIT 10

한 페이지에 10개의 아이템을 보여주고 2번째 페이지의 결과를 가져오고 싶다면 다음과 같이 하면 된다.
SELECT column FROM table LIMIT 10 OFFSET 20

그러면 21~30 번째 row의 결과를 얻을 수 있다.
Posted by 김민우 julingks