JDBC Storage Handler

JDBC Storage Handler

JdbcStorageHandler는 Hive에서 JDBC 데이터 소스를 읽도록 지원해요. 현재 JDBC 데이터 소스에 쓰는 것은 지원되지 않아요. JdbcStorageHandler를 사용하려면 JdbcStorageHandler로 외부(EXTERNAL) 테이블을 만들어야 해요. 추가로 파티셔닝·컴퓨테이션 푸시다운, 키스토어를 통한 비밀번호 보호 등 다양한 기능을 제공한답니다.

출처: 문서

본문

구문 (Syntax)

JdbcStorageHandler는 Hive에서 jdbc 데이터 소스 읽기를 지원해요. 현재 jdbc 데이터 소스에 쓰기는 지원되지 않아요. JdbcStorageHandler를 사용하려면 JdbcStorageHandler로 외부 테이블을 만들어야 해요. 간단한 예:

CREATE EXTERNAL TABLE student_jdbc
(
  name string,
  age int,
  gpa double
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
    "hive.sql.database.type" = "MYSQL",
    "hive.sql.jdbc.driver" = "com.mysql.jdbc.Driver",
    "hive.sql.jdbc.url" = "jdbc:mysql://localhost/sample",
    "hive.sql.dbcp.username" = "hive",
    "hive.sql.dbcp.password" = "hive",
    "hive.sql.table" = "STUDENT",
    "hive.sql.dbcp.maxActive" = "1"
);

다른 non-native Hive 테이블처럼 alter table 문으로 jdbc 외부 테이블의 테이블 속성을 변경할 수도 있어요:

ALTER TABLE student_jdbc SET TBLPROPERTIES ("hive.sql.dbcp.password" = "passwd");

테이블 속성 (Table Properties)

create table 문에서 다음 테이블 속성을 지정해야 해요:

• hive.sql.database.type: MYSQL, POSTGRES, ORACLE, DERBY, DB2
• hive.sql.jdbc.url: jdbc 연결 문자열
• hive.sql.jdbc.driver: jdbc 드라이버 클래스
• hive.sql.dbcp.username: jdbc 사용자 이름
• hive.sql.dbcp.password: 평문(clear text) jdbc 비밀번호. 이 매개변수는 강력히 권장되지 않아요. 권장 방식은 이를 keystore에 저장하는 거예요. 자세한 내용은 "securing password" 섹션 참고
• hive.sql.table / hive.sql.query: jdbc 데이터베이스에서 데이터를 얻는 방법을 알려주려면 "hive.sql.table" 또는 "hive.sql.query" 중 하나를 지정해야 해요. "hive.sql.table"은 단일 테이블을, "hive.sql.query"는 임의의 sql 쿼리를 나타내요.

위의 필수 속성 외에도 연결 세부 사항과 성능을 조정하는 선택적 매개변수를 지정할 수 있어요:

• hive.sql.catalog: jdbc catalog 이름 ("hive.sql.table"이 지정된 경우에만 유효)
• hive.sql.schema: jdbc schema 이름 ("hive.sql.table"이 지정된 경우에만 유효)
• hive.sql.jdbc.fetch.size: 한 배치에서 가져올 행 수
• hive.sql.dbcp.xxx: 모든 dbcp 매개변수가 commons-dbcp로 전달됨. 매개변수 정의는 https://commons.apache.org/proper/commons-dbcp/configuration.html 참고. 예를 들어 테이블 속성에 hive.sql.dbcp.maxActive=1을 지정하면 Hive는 maxActive=1을 commons-dbcp에 전달

지원 데이터 타입 (Supported Data Type)

Hive JdbcStorageHandler 테이블의 컬럼 데이터 타입은 다음일 수 있어요:

• 숫자 데이터 타입: byte, short, int, long, float, double
• scale과 precision이 있는 Decimal
• 문자열 데이터 타입: string, char, varchar
• Date
• Timestamp

참고: struct, map, array 같은 복합 데이터 타입은 지원되지 않아요.

컬럼/타입 매핑 (Column/Type Mapping)

hive.sql.table / hive.sql.query는 스키마가 있는 테이블 형식 데이터를 정의해요. 스키마 정의는 테이블 스키마 정의와 같아야 해요. 예를 들어 다음 create table 문은 실패할 거예요:

CREATE EXTERNAL TABLE student_jdbc
(
  name string,
  age int,
  gpa double
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
    . . . . . .
    "hive.sql.query" = "SELECT name, age, gpa, gender FROM STUDENT",
);

그러나 hive.sql.table / hive.sql.query 스키마의 컬럼 이름과 타입은 테이블 스키마와 다를 수 있어요. 이 경우 데이터베이스 컬럼은 위치별(by position)로 hive 컬럼에 매핑돼요. 데이터 타입이 다르면 Hive는 Hive 테이블 스키마에 따라 변환하려 시도해요. 예:

CREATE EXTERNAL TABLE student_jdbc
(
  sname string,
  age int,
  effective_gpa decimal(4,3)
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
    . . . . . .
    "hive.sql.query" = "SELECT name, age, gpa FROM STUDENT",
);

Hive는 밑바탕 테이블 STUDENT의 double "gpa"를 student_jdbc 테이블의 effective_gpa 필드로 decimal(4,3)에 변환하려 시도해요. 변환이 불가능하면 Hive는 필드에 대해 null을 생성해요.

자동 배송 (Auto Shipping)

JdbcStorageHandler가 쿼리에서 사용되면 JdbcStorageHandler는 필수 jar를 MR/Tez/LLAP 백엔드로 자동 배송해요. 사용자가 jar를 수동으로 추가할 필요가 없어요. JdbcStorageHandler는 또한 클래스패스에서 jdbc 드라이버 jar(mysql, postgres, oracle, mssql 포함)를 감지하면 백엔드로 필요한 jdbc 드라이버 jar도 배송해요. 그러나 사용자는 여전히 jdbc 드라이버 jar를 hive 클래스패스(보통 hive의 lib 디렉토리)에 복사해야 해요.

비밀번호 보호 (Securing Password)

대부분의 경우 "hive.sql.dbcp.password" 테이블 속성에 jdbc 비밀번호를 평문으로 저장하고 싶지 않아요. 대신 사용자는 다음 명령으로 비밀번호를 HDFS의 Java keystore 파일에 저장할 수 있어요:

hadoop credential create host1.password -provider jceks://hdfs/user/foo/test.jceks -v passwd1
hadoop credential create host2.password -provider jceks://hdfs/user/foo/test.jceks -v passwd2

이 명령은 hdfs://user/foo/test.jceks에 위치한 keystore 파일을 만들어 두 키(host1.password, host2.password)를 포함해요. Hive에서 테이블을 만들 때 create table 문의 "hive.sql.dbcp.password" 대신 "hive.sql.dbcp.password.keystore"와 "hive.sql.dbcp.password.key"를 지정해야 해요:

CREATE EXTERNAL TABLE student_jdbc
(
  name string,
  age int,
  gpa double
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
    . . . . . .
    "hive.sql.dbcp.password.keystore" = "jceks://hdfs/user/foo/test.jceks",
    "hive.sql.dbcp.password.key" = "host1.password",
    . . . . . .
);

테이블을 만들거나 변경할 때 관련된 사용자만 이 파일을 읽도록 authorizer(예: ranger)로 keystore 파일을 보호해야 해요. Hive는 keystore 파일의 권한을 확인해 사용자가 읽기 권한이 있는지 확인해요.

파티셔닝 (Partitioning)

Hive는 jdbc 데이터 소스를 분할(split)하고 각 분할을 병렬로 처리할 수 있어요. 다음 테이블 속성으로 분할 여부와 분할 수를 결정할 수 있어요:

• hive.sql.numPartitions: 데이터 소스에 대해 생성할 분할 수. 분할하지 않으면 1
• hive.sql.partitionColumn: 분할할 컬럼. 이 값이 지정되면 Hive는 hive.sql.lowerBound에서 hive.sql.upperBound까지 컬럼을 hive.sql.numPartitions개의 동일 구간으로 분할해요. partitionColumn이 정의되지 않고 numPartitions > 1이면 Hive는 오프셋(offset)을 사용해 데이터 소스를 분할해요. 그러나 offset은 일부 데이터베이스에서 항상 신뢰할 수 없어요. 데이터 소스를 분할하려면 partitionColumn을 정의하는 것이 매우 권장돼요. partitionColumn은 "hive.sql.table"/"hive.sql.query" 스키마가 생성하는 것에 존재해야 해요.
• hive.sql.lowerBound / hive.sql.upperBound: 구간을 계산하는 데 사용되는 partitionColumn의 하한/상한. 두 속성 모두 선택 사항이에요. 정의되지 않으면 Hive는 데이터 소스에 대해 MIN/MAX 쿼리를 수행해 하한/상한을 얻어요. hive.sql.lowerBound와 hive.sql.upperBound는 둘 다 null일 수 없다는 점에 주의해요. 첫 번째와 마지막 분할은 개방(open ended)돼요. 컬럼의 모든 null 값은 첫 번째 분할로 갈 거예요.

예:

TBLPROPERTIES (
    . . . . . .
    "hive.sql.table" = "DEMO",
    "hive.sql.partitionColumn" = "num",
    "hive.sql.numPartitions" = "3",
    "hive.sql.lowerBound" = "1",
    "hive.sql.upperBound" = "10",
    . . . . . .
);

이 테이블은 3개의 분할을 만든다: num<4 또는 num이 null, 4<=num<7, num>=7

TBLPROPERTIES (
    . . . . . .
    "hive.sql.query" = "SELECT name, age, gpa/5.0*100 AS percentage FROM STUDENT",
    "hive.sql.partitionColumn" = "percentage",
    "hive.sql.numPartitions" = "4",
    . . . . . .
);

Hive는 쿼리의 percentage 컬럼(60, 100)의 MIN/MAX를 얻기 위해 jdbc 쿼리를 수행할 거예요. 그런 다음 테이블은 4개의 분할을 만든다: (,70), [70,80), [80,90), [90,). 첫 번째 분할도 null 값을 포함해요.

JdbcStorageHandler가 생성한 분할을 보려면 hiveserver2 로그 또는 Tez AM 로그에서 다음 메시지를 찾아봐요:

jdbc.JdbcInputFormat: Num input splits created 4
jdbc.JdbcInputFormat: split:interval:ikey[,70)
jdbc.JdbcInputFormat: split:interval:ikey[70,80)
jdbc.JdbcInputFormat: split:interval:ikey[80,90)
jdbc.JdbcInputFormat: split:interval:ikey[90,)

컴퓨테이션 푸시다운 (Computation Pushdown)

Hive는 jdbc 테이블로 컴퓨테이션을 적극적으로 푸시다운해 jdbc 데이터 소스의 기본 능력을 최대한 활용해요.

예를 들어 voter_jdbc 테이블이 또 있다고 하면:

EATE EXTERNAL TABLE voter_jdbc
(
  name string,
  age int,
  registration string,
  contribution decimal(10,2)
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
    "hive.sql.database.type" = "MYSQL",
    "hive.sql.jdbc.driver" = "com.mysql.jdbc.Driver",
    "hive.sql.jdbc.url" = "jdbc:mysql://localhost/sample",
    "hive.sql.dbcp.username" = "hive",
    "hive.sql.dbcp.password" = "hive",
    "hive.sql.table" = "VOTER"
);

그러면 다음 조인 연산이 mysql로 푸시다운돼요:

select * from student_jdbc join voter_jdbc on student_jdbc.name=voter_jdbc.name;

이는 explain으로 확인할 수 있어요:

explain select * from student_jdbc join voter_jdbc on student_jdbc.name=voter_jdbc.name;
        . . . . . .
        TableScan
          alias: student_jdbc
          properties:
            hive.sql.query SELECT `t`.`name`, `t`.`age`, `t`.`gpa`, `t0`.`name` AS `name0`, `t0`.`age` AS `age0`, `t0`.`registration`, `t0`.`contribution`
FROM (SELECT *
FROM `STUDENT`
WHERE `name` IS NOT NULL) AS `t`
INNER JOIN (SELECT *
FROM `VOTER`
WHERE `name` IS NOT NULL) AS `t0` ON `t`.`name` = `t0`.`name`
        . . . . . .

컴퓨테이션 푸시다운은 jdbc 테이블이 "hive.sql.table"로 정의된 경우에만 발생해요. Hive는 테이블 위에 더 많은 컴퓨테이션을 가진 "hive.sql.query" 속성으로 데이터 소스를 재작성해요. 위 예에서 mysql은 두 테이블을 가져와 Hive에서 조인하는 대신 쿼리를 실행하고 조인 결과를 검색해요.

푸시다운될 수 있는 연산자에는 filter, transform, join, union, aggregation, sort가 포함돼요.

파생된 mysql 쿼리는 매우 복잡할 수 있고, 많은 경우 데이터 소스를 분할하지 않기를 원해요(복잡한 쿼리를 각 분할에서 여러 번 실행하지 않도록). 따라서 컴퓨테이션이 filter와 transform보다 더 많으면 Hive는 "hive.sql.numPartitions"가 1보다 커도 쿼리 결과를 분할하지 않아요.

기본이 아닌 스키마 사용 (Using a Non-default Schema)

스키마의 개념은 Oracle, MSSQL, MySQL, PostgreSQL 같은 DBMS마다 달라요. hive.sql.schema 테이블 속성의 올바른 사용은 외부 JDBC 테이블에 대한 클라이언트 연결 문제를 예방할 수 있어요. 자세한 내용은 Hive-25591 참고. JDBC 호환 데이터베이스에서 사용자 정의 스키마를 기반으로 외부 테이블을 만들려면 각 데이터베이스에 대한 아래 예시를 따라가요.

MariaDB

각 사용자를 만들어 기본 스키마와 연결하고, 스키마에 접근할 권한을 부여하는 형태를 사용해요. bob/ alice 스키마를 만들고 각각에 country 테이블을 만들어 데이터를 넣은 뒤, hive.sql.schema로 접근합니다.

MS SQL

CREATE DATABASE world;
USE world;

CREATE SCHEMA bob;
CREATE TABLE bob.country
(
    id   int,
    name varchar(20)
);

insert into bob.country
values (1, 'India');
insert into bob.country
values (2, 'Russia');
insert into bob.country
values (3, 'USA');

사용자를 만들고 기본 스키마와 연결:

CREATE LOGIN greg WITH PASSWORD = 'GregPass123!$';
CREATE USER greg FOR LOGIN greg WITH DEFAULT_SCHEMA=bob;

사용자가 데이터베이스에 연결하고 쿼리를 실행하도록 허용:

GRANT CONNECT, SELECT TO greg;

Oracle

Oracle에서 테이블을 서로 다른 네임스페이스/스키마로 나누는 것은 서로 다른 사용자를 통해 이루어져요. CREATE SCHEMA 문은 Oracle에 존재하지만 SQL 표준과 다른 DBMS에서 채택한 것과는 다른 의미를 가져요.

Oracle에서 "local" 사용자를 만들려면 Container Database(CDB)가 아니라 Pluggable Database(PDB)에 연결해야 해요. 다음 예는 Oracle XE 에디션에서 PDB XEPDB1만 사용해 테스트했어요.

ALTER SESSION SET CONTAINER = XEPDB1;

bob 스키마/사용자를 만들고 데이터베이스에 연결할 수 있는 적절한 권한을 줘요:

CREATE USER bob IDENTIFIED BY bobpass;
ALTER USER bob QUOTA UNLIMITED ON users;
GRANT CREATE SESSION TO bob;

CREATE TABLE bob.country
(
    id   int,
    name varchar(20)
);

insert into bob.country
values (1, 'India');
insert into bob.country
values (2, 'Russia');
insert into bob.country
values (3, 'USA');

SELECT ANY 권한이 없으면 한 사용자는 다른 사용자의 테이블/뷰를 볼 수 없어요. 사용자가 특정 사용자와 스키마로 데이터베이스에 연결하면 다른 사용자/스키마 네임스페이스의 테이블을 참조할 수 없어요. SELECT ANY 권한을 부여해야 해요:

GRANT SELECT ANY TABLE TO bob;
GRANT SELECT ANY TABLE TO alice;

사용자가 데이터베이스의 어떤 테이블/뷰에도 insert를 수행하도록 허용:

GRANT INSERT ANY TABLE TO bob;
GRANT INSERT ANY TABLE TO alice;

PostgreSQL

CREATE SCHEMA bob;
CREATE TABLE bob.country
(
    id   int,
    name varchar(20)
);

insert into bob.country
values (1, 'India');
insert into bob.country
values (2, 'Russia');
insert into bob.country
values (3, 'USA');

사용자를 만들고 기본 스키마(search_path)와 연결:

CREATE ROLE greg WITH LOGIN PASSWORD 'GregPass123!$';
ALTER ROLE greg SET search_path TO bob;

스키마에 접근할 필요한 권한 부여:

GRANT USAGE ON SCHEMA bob TO greg;
GRANT SELECT ON ALL TABLES IN SCHEMA bob TO greg;

더 알아보기 (Learn more)

• Hive JDBC Storage Handler 위키에서 지원 타입과 제약을 자세히 볼 수 있어요.
• Hive-25591에서 매핑 관련 추가 내용을 확인할 수 있어요.