Data Science Lab

<property>

              <name>hive.exec.compress.intermediate</name>

              <value>true</value>

</property>

<property>

              <name>hive.exec.compress.output</name>

              <value>true</value>

</property>

<property>

              <name>mapred.map.output.compression.codec</name>

               <value>com.hadoop.compression.fourmc.FourMCHighCodec</value>

</property>

set hive.exec.compress.output=true;

set mapreduce.output.fileoutputformat.compress=true;

set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.BZip2Codec;

set hive.exec.compress.output=true;

set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;

--table의 내용을 /user/tables/ 경로에 CSV 형태로 압축 저장

INSERT OVERWRITE DIRECTORY 'hdfs:///user/tables/'

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

SELECT *

  FROM table

;

--table의 내용을 temp_table 테이블에 저장

CREATE TABLE csvsample

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

LOCATION '/user/csv/'

AS

SELECT *

  FROM table

;

-- 맵퍼 단독 작업일 때 머지

set hive.merge.mapfiles=true;

-- 맵리듀스 작업일 때 머지

set hive.merge.mapredfiles=true;

-- 테즈 작업일 때 머지

set hive.merge.tezfiles=true;

--테즈에서 실행하는지 확인

SET hive.execution.engine;

--테즈 엔진으로 설정

SET  hive.execution.engine=tez;

-- 머지 작업의 대상이 되는 파일 사이즈(32MB이하)

set hive.merge.smallfiles.avgsize=32000000;

-- 머지 파일을 묶을때 기준(256MB)

set hive.merge.size.per.task=256000000;

set hive.stats.autogather=true;

set hive.stats.column.autogather=true;

--tbl 테이블 통계정보 수집

ANALYZE TABLE tbl COMPUTE STATISTICS;

--tbl 테이블의 yymmdd가 '2020-01-01'인 파티션의 통계정보 수집

ANALYZE TABLE tbl PARTITION(yymmdd='2020-01-01') COMPUTE STATISTICS;

--컬럼 통계정보 수집

ANALYZE TABLE tbl PARTITION(yymmdd='2018-01-01') COMPUTE STATISTICS FOR COLUMNS;

desc formatted tbl partition(yymmddval='20180101');

Table Parameters:               

        COLUMN_STATS_ACCURATE   {\"BASIC_STATS\":\"true\"}

        EXTERNAL                TRUE               

        last_modified_by        root             

        last_modified_time      1577490527         

        numFiles                9973               

        numPartitions           392                

        numRows                 2305106979         

        rawDataSize             12729039219        

        totalSize               717032673468       

        transient_lastDdlTime   1577490527

set hive.compute.query.using.stats=true;

-- 통계정보를 활용하여 쿼리 실행

hive> select count(*) from tbl;

EXPLAIN [EXTENDED|AST|DEPENDENCY|AUTHORIZATION|LOCKS|VECTORIZATION|ANALYZE] query

explain select * from tbl;

explain extended select * from tbl;

hive> explain

    > select * from tbl_a where date = '20200114' and (url like '%XXXX%' and param like '%XXXX%')

    > ;

OK

STAGE DEPENDENCIES:

  Stage-1 is a root stage

  Stage-0 depends on stages: Stage-1

STAGE PLANS:

  Stage: Stage-1

    Map Reduce

      Map Operator Tree:

          TableScan

            alias: tbl_a

            Statistics: Num rows: 1343079 Data size: 694357252 Basic stats: COMPLETE Column stats: NONE

            Filter Operator

              predicate: ((url like '%XXXX%') and (param like '%XXXX%')) (type: boolean)

              Statistics: Num rows: 335769 Data size: 173588925 Basic stats: COMPLETE Column stats: NONE

              Select Operator

                expressions: col_1 (type: string), col_2 (type: string), col_3 (type: string), col_4 (type: string), col_5 (type: string), col_6 (type: string), col_7 (type: string), col_8 (type: string), col_9 (type: string), col_10 (type: string), '20200114' (type: string)

                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10

                Statistics: Num rows: 335769 Data size: 173588925 Basic stats: COMPLETE Column stats: NONE

                File Output Operator

                  compressed: false

                  Statistics: Num rows: 335769 Data size: 173588925 Basic stats: COMPLETE Column stats: NONE

                  table:

                      input format: org.apache.hadoop.mapred.SequenceFileInputFormat

                      output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat

                      serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0

    Fetch Operator

      limit: -1

      Processor Tree:

        ListSink

Time taken: 2.517 seconds, Fetched: 32 row(s)

hive>

col_1,col_2,"col_3(a=3&b=40&c=NOTES&cid=1,10,33)"

create table tbl_a (

col_1 string

col_2 string

col_3 string

)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ("separatorChar" = ",","quoteChar" = "\"")

ALTER TABLE tbl_a set SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ("separatorChar" = ",","quoteChar" = "\"")

-- col2를 버켓팅하여 20개의 파일에 저장

CREATE TABLE tbl1(

       col1 STRING,

       col2 STRING

)

CLUSTERED BY (col2) INTO 20 BUCKETS

LOCATION '/user/data/'

-- col2를 버켓팅 하고, col1 기준으로 정렬

CREATE TABLE tbl2(

       col1 STRING,

       col2 STRING

)

CLUSTERED BY (col2) SORTED BY (col1) INTO 20 BUCKETS

LOCATION '/user/data/'

Skewed Table vs. List Bucketing Table

·       Skewed Table is a table which has skewed information.

·       List Bucketing Table is a skewed table. In addition, it tells Hive to use the list bucketing feature on the skewed table: create sub-directories for skewed values.

-- col2 입력 값중 1로 들어오는 값만 스큐로 저장

CREATE TABLE tbl_skewed (

col_1 STRING,

col_2 STRING

) SKEWED BY (col2) ON ('1')

STORED AS DIRECTORIES

LOCATION '/user/tbl_skewed/';

# Tables_in_hive

AUX_TABLE

MASTER_KEYS

SD_PARAMS

BUCKETING_COLS

METASTORE_DB_PROPERTIES

SEQUENCE_TABLE

CDH_VERSION

NEXT_COMPACTION_QUEUE_ID

SERDES

CDS

NEXT_LOCK_ID

SERDE_PARAMS

COLUMNS_V2

NEXT_TXN_ID

SKEWED_COL_NAMES

COMPACTION_QUEUE

NOTIFICATION_LOG

SKEWED_COL_VALUE_LOC_MAP

COMPLETED_COMPACTIONS

NOTIFICATION_SEQUENCE

SKEWED_STRING_LIST

COMPLETED_TXN_COMPONENTS

NUCLEUS_TABLES

SKEWED_STRING_LIST_VALUES

DATABASE_PARAMS

PARTITIONS

SKEWED_VALUES

DBS

PARTITION_EVENTS

SORT_COLS

DB_PRIVS

PARTITION_KEYS

TABLE_PARAMS

DELEGATION_TOKENS

PARTITION_KEY_VALS

TAB_COL_STATS

FUNCS

PARTITION_PARAMS

TBLS

FUNC_RU

PART_COL_PRIVS

TBL_COL_PRIVS

GLOBAL_PRIVS

PART_COL_STATS

TBL_PRIVS

HIVE_LOCKS

PART_PRIVS

TXNS

IDXS

ROLES

TXN_COMPONENTS

INDEX_PARAMS

ROLE_MAP

TYPES

KEY_CONSTRAINTS

SDS

TYPE_FIELDS

VERSION

WRITE_SET

SELECT

              *

FROM hive.TBLS as t

              inner join hive.DBS as d on t.DB_ID = d.DB_ID

ORDER BY d.NAME;

SELECT

              d.NAME, t.TBL_NAME, p.PART_NAME

FROM hive.DBS as d

              inner join hive.TBLS as t on d.DB_ID = t.DB_ID

    inner join hive.PARTITIONS as p on t.TBL_ID = p.TBL_ID

where t.TBL_NAME like '%key%';

<property>

   <name>javax.jdo.option.ConnectionURL</name>

   <value>jdbc:derby:metastore_db;create=true </value>

   <description>JDBC connect string for a JDBC metastore </description>

</property>

<property>

    <name>javax.jdo.option.ConnectionURL</name>

    <value>jdbc:mysql://[IP]:[port]/[DB NAME]</value>

    <description>username to use against metastore database</description>

</property>

<property>

    <name>javax.jdo.option.ConnectionDriverName</name>

    <value>org.mariadb.jdbc.Driver</value>

    <description>username to use against metastore database</description>

</property>

<property>

    <name>javax.jdo.option.ConnectionUserName</name>

    <value>User</value>

    <description>username to use against metastore database</description>

</property>

<property>

    <name>javax.jdo.option.ConnectionPassword</name>

    <value>Password</value>

    <description>password to use against metastore database</description>

</property>

<property>

    <name>hive.metastore.uris</name>

    <value>thrift://[Meta Store IP]:[Meta Store Port]</value>

    <description>JDBC connect string for a JDBC metastore</description>

</property>

Error while processing statement: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask

[2019-12-12 09:11:17.232]Container [pid=11024,containerID=container_1576170300177_0002_01_000176] is running 45285376B beyond the 'PHYSICAL' memory limit. Current usage: 1.0 GB of 1 GB physical memory used; 2.5 GB of 2.1 GB virtual memory used. Killing container. Dump of the process-tree for container_1576170300177_0002_01_000176 : |- PID PPID PGRPID SESSID CMD_NAME USER_MODE_TIME(MILLIS) SYSTEM_TIME(MILLIS) VMEM_USAGE(BYTES) RSSMEM_USAGE(PAGES) FULL_CMD_LINE |- 11024 11016 11024 11024 (bash) 0 0 9797632 287 /bin/bash -c /usr/lib/jvm/java-openjdk/bin/java -Djava.net.preferIPv4Stack=true -Dhadoop.metrics.log.level=WARN -Djava.net.preferIPv4Stack=true -Xmx820m -Djava.io.tmpdir=/data/yarn/nm/usercache/admin/appcache/application_1576170300177_0002/container_1576170300177_0002_01_000176/tmp -Dlog4j.configuration=container-log4j.properties -Dyarn.app.container.log.dir=/data/yarn/container-logs/application_1576170300177_0002/container_1576170300177_0002_01_000176 -Dyarn.app.container.log.filesize=0 -Dhadoop.root.logger=INFO,CLA -Dhadoop.root.logfile=syslog -Dyarn.app.mapreduce.shuffle.logger=INFO,shuffleCLA -Dyarn.app.mapreduce.shuffle.logfile=syslog.shuffle -Dyarn.app.mapreduce.shuffle.log.filesize=0 -Dyarn.app.mapreduce.shuffle.log.backups=0 org.apache.hadoop.mapred.YarnChild 10.210.63.18 31249 attempt_1576170300177_0002_r_000011_0 176 1>/data/yarn/container-logs/application_1576170300177_0002/container_1576170300177_0002_01_000176/stdout 2>/data/yarn/container-logs/application_1576170300177_0002/container_1576170300177_0002_01_000176/stderr |- 11057 11024 11024 11024 (java) 3712 153 2693627904 272913 /usr/lib/jvm/java-openjdk/bin/java -Djava.net.preferIPv4Stack=true -Dhadoop.metrics.log.level=WARN -Djava.net.preferIPv4Stack=true -Xmx820m -Djava.io.tmpdir=/data/yarn/nm/usercache/admin/appcache/application_1576170300177_0002/container_1576170300177_0002_01_000176/tmp -Dlog4j.configuration=container-log4j.properties -Dyarn.app.container.log.dir=/data/yarn/container-logs/application_1576170300177_0002/container_1576170300177_0002_01_000176 -Dyarn.app.container.log.filesize=0 -Dhadoop.root.logger=INFO,CLA -Dhadoop.root.logfile=syslog -Dyarn.app.mapreduce.shuffle.logger=INFO,shuffleCLA -Dyarn.app.mapreduce.shuffle.logfile=syslog.shuffle -Dyarn.app.mapreduce.shuffle.log.filesize=0 -Dyarn.app.mapreduce.shuffle.log.backups=0 org.apache.hadoop.mapred.YarnChild 10.210.63.18 31249 attempt_1576170300177_0002_r_000011_0 176 [2019-12-12 09:11:17.303]Container killed on request. Exit code is 143 [2019-12-12 09:11:17.312]Container exited with a non-zero exit code 143.

Map Task Memory (mapreduce.map.memory.mb)

변경 전/후 : 0GB -> 6GB

Reduce Task Memory (mapreduce.reduce.memory.mb)

변경 전/후 : 0GB -> 6GB

--beeline shell 실행

beeline

--beeline CLI로 HiveServer2 커넥트

!connect jdbc:hive2://{hive ip & port} {user} {password}

!connect jdbc:hive2://localhost:10000 user password

--beeline CLI로 MySQL 접속

!connect jdbc:mysql://localhost:port/db_name db_id db_password

beeline -u jdbc:hive2://localhost:10000 -n user -p password

hive --hiveconf mydb.data.name=queue --hivevar date=20190101

-- 커맨드에서 hive 실행

$ hive

--하이브 쉘에서 쿼리 실행

hive> select * from table;

-- 커맨드라인에서 하이브의 table 테이블 조회

$ hive -e "select * from table"

-- 쿼리의 실행 엔진과, 변수를 설정

$ hive -e 'select * from table where yymmdd=${hivevar:tDate}' \

   --hiveconf hie.execution.engine=tez \

   --hiveconf tez.queue.name=queue_name \

   --hivevar Date=20190101

--sample.hql 파일에 저장되어 있는 쿼리

select * from table where yymmdd=${hivevar:targetDate}

-- -f 옵션을 사용하여 파일 쿼리 실행 및 변수 입력

$ hive -f sample.hql --hivevar Date=20190101

-- 로깅 레벨, 파일 위치 변경

hive --hiveconf hive.log.file=hive_debug.log \

  --hiveconf hive.log.dir=./ \

  --hiveconf hive.root.logger=DEBUG,DRFA

Command

Description

Exit

하이브 쉘 종료

Reset

설정값 초기화

set <key>=<value>

설정값 입력

set

하이브의 설정값 출력

set -v

하둡, 하이브의 설정값 출력

add file <>

파일 추가

add files <>

여러개의 파일 추가, 공백으로 경로 구분

add jar <>

jar 파일 추가

add jars <>

여러개의 jar 파일 추가, 공백으로 경로 구분

!<command>

쉘 커맨드 실행

dfs <dfs command>

하둡 dfs 커맨드 실행

-- 옵션 설정

hive> set mapred.reduce.tasks=32;

-- 모든 옵션 값 확인

hive> set;

-- CLI 상에서 설정한 옵션 초기화

hive> reset;

-- 쉘커맨드 실행

hive> !ls -alh;

-- dfs 커맨드 실행

hive> dfs -ls /user/;

-- 파일 추가

hive> add file hdfs:///user/sample.txt;

-- 여러개의 파일 추가. 공백으로 구분

hive> add files hdfs:///user/sample1.txt hdfs:///user/sample2.txt;

-- jar 파일 추가

hive> add jar hdfs:///user/sample.jar;

pip install -r /path/to/requirements.txt

backports.ssl-match-hostname==3.5.0.1

certifi==2019.9.11

chardet==3.0.4

configobj==4.7.2

decorator==3.4.0

docopt==0.6.2

future==0.18.2

futures==3.1.1

hdfs==2.5.8

hdfs3==0.3.1

idna==2.8

iniparse==0.4

ipaddress==1.0.16

IPy==0.75

javapackages==1.0.0

kitchen==1.1.1

langtable==0.0.31

LinkChecker==9.3

lxml==3.2.1

MySQL-python==1.2.5

numpy==1.16.5

pandas==0.24.2

perf==0.1

pexpect==4.7.0

policycoreutils-default-encoding==0.1

psycopg2==2.5.1

ptyprocess==0.6.0

pycurl==7.19.0

pygobject==3.22.0

pygpgme==0.3

PyHive==0.6.1

pyliblzma==0.5.3

pysasl==0.4.1

python-augeas==0.5.0

python-dateutil==2.8.1

python-linux-procfs==0.4.9

pytz==2019.3

pyudev==0.15

pyxattr==0.5.1

PyYAML==3.10

requests==2.22.0

sasl==0.2.1

schedutils==0.4

seobject==0.1

sepolicy==1.1

six==1.9.0

slip==0.4.0

slip.dbus==0.4.0

SSSDConfig==1.16.4

thrift==0.13.0

thrift-sasl==0.3.0

urlgrabber==3.10

urllib3==1.25.7

virtualenv==15.1.0

yum-langpacks==0.4.2

yum-metadata-parser==1.1.4

from pyhive import hive

conn = hive.Connection(host='hd-master', port=10000, username='ID', password='PASSWORD', database='default', auth='CUSTOM')

cur = conn.cursor()

cur.execute('select 1 from tbl')

result = cur.fetchall()

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name

[(col_name data_type [COMMENT col_comment], ...)]

[COMMENT table_comment]

[ROW FORMAT row_format]

[STORED AS file_format]

CREATE TABLE tbl(

  col1 STRING

) PARTITIONED BY (yymmdd STRING);

FIELDS TERMINATED BY '\t'            -- 컬럼 구분

COLLECTION ITEMS TERMINATED BY ','   -- 리스트 구분

MAP KEYS TERMINATED BY '='           -- 맵데이터의 키밸류 구분

LINES TERMINATED BY '\n'             -- row 구분

ESCAPED BY '\\'                     

NULL DEFINED AS 'null'               

CREATE TABLE IF NOT EXISTS employee (

eid int,

name String,

salary String,

destination String)

COMMENT 'Employee details'

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

LINES TERMINATED BY '\n'

STORED AS TEXTFILE;

import java.sql.SQLException;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import java.sql.DriverManager;

public class HiveCreateTable {

   private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";

   public static void main(String[] args) throws SQLException {

      // Register driver and create driver instance

      Class.forName(driverName);

      // get connection

      Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/userdb", "", "");

      // create statement

      Statement stmt = con.createStatement();

      // execute statement

      stmt.executeQuery("CREATE TABLE IF NOT EXISTS "

         +" employee ( eid int, name String, "

         +" salary String, destignation String)"

         +" COMMENT ‘Employee details’"

         +" ROW FORMAT DELIMITED"

         +" FIELDS TERMINATED BY ‘\t’"

         +" LINES TERMINATED BY ‘\n’"

         +" STORED AS TEXTFILE;");

      System.out.println(“ Table employee created.”);

      con.close();

   }

}