Movle

tar -zxf jdk-8u144-linux-x64.tar.gz -C /opt/module/

vi /etc/profile

#JAVA_HOME
export JAVA_HOME=/opt/module/jdk1.8.0_144
export PATH=$PATH:$JAVA_HOME/bin

source /etc/profile

tar -zxvf apache-maven-3.0.5-bin.tar.gz -C /opt/module/

vi /etc/profile

#MAVEN_HOME
export MAVEN_HOME=/opt/module/apache-maven-3.0.5
export PATH=$PATH:$MAVEN_HOME/bin

source /etc/profile

[root@bigdata111 software]# yum install svn

[root@bigdata111 software]# yum install autoconf automake libtool cmake

[root@bigdata111 software]# yum install ncurses-devel

[root@bigdata111 software]# yum install openssl-devel

[root@bigdata111 software]# yum install gcc*

[root@bigdata111 software]# tar -zxvf snappy-1.1.3.tar.gz -C /opt/module/

[root@bigdata111 module]# cd snappy-1.1.3/

[root@bigdata111 snappy-1.1.3]# ./configure

[root@bigdata111 snappy-1.1.3]# make

[root@bigdata111 snappy-1.1.3]# make install

[root@bigdata111 snappy-1.1.3]# ls -lh /usr/local/lib |grep snappy

[root@bigdata111 software]# tar -zxvf protobuf-2.5.0.tar.gz -C /opt/module/

[root@bigdata111 module]# cd protobuf-2.5.0/

[root@bigdata111 protobuf-2.5.0]# ./configure 

[root@bigdata111 protobuf-2.5.0]#  make 

[root@bigdata111 protobuf-2.5.0]#  make install

[root@bigdata111 protobuf-2.5.0]# protoc --version

[root@bigdata111 software]# tar -zxvf hadoop-2.8.4-src.tar.gz

[root@bigdata111 software]# cd hadoop-2.8.4-src/

[root@bigdata111 software]# mvn clean package -DskipTests -Pdist,native -Dtar -Dsnappy.lib=/usr/local/lib -Dbundle.snappy

hive (default)>set hive.exec.compress.intermediate=true;

hive (default)>set mapreduce.map.output.compress=true;

hive (default)>set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec;

hive (default)> select count(ename) name from emp;

hive (default)> set mapreduce.job.reduces=3;

hive (default)>set hive.exec.compress.output=true;

hive (default)>set mapreduce.output.fileoutputformat.compress=true;

hive (default)> set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;

hive (default)> set mapreduce.output.fileoutputformat.compress.type=BLOCK;

hive (default)> insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

hive (default)> set mapreduce.job.reduces=-1;

insert overwrite local directory '/opt/module/datas/distribute-result' select * from test;

create table log_text (

track_time string,

url string,

session_id string,

referer string,

ip string,

end_user_id string,

city_id string

)

row format delimited fields terminated by '\t'

stored as textfile ;

hive (default)> load data local inpath '/opt/module/datas/log.data' into table log_text ;

hive (default)> dfs -du -h /user/hive/warehouse/log_text;

create table log_orc(

track_time string,

url string,

session_id string,

referer string,

ip string,

end_user_id string,

city_id string

)

row format delimited fields terminated by '\t'

stored as orc ;

hive (default)> insert into table log_orc select * from log_text ;

hive (default)> dfs -du -h /user/hive/warehouse/log_orc/ ;

create table log_parquet(

track_time string,

url string,

session_id string,

referer string,

ip string,

end_user_id string,

city_id string

)

row format delimited fields terminated by '\t'

stored as parquet ;  

hive (default)> insert into table log_parquet select * from log_text ;

hive (default)> dfs -du -h /user/hive/warehouse/log_parquet/ ;

hive (default)> select count(*) from log_text;

hive (default)> select count(*) from log_orc;

hive (default)> select count(*) from log_parquet;

hadoop checknative [-a|-h]  check native hadoop and compression libraries availability

hadoop checknative

tar -zxvf hadoop-2.8.4.tar.gz

pwd

ll

cp ../native/* /opt/module/hadoop-2.8.4/lib/native/

scp - r ./native/ root@主机名:绝对路径

hadoop checknative

create table log_orc_none(

track_time string,

url string,

session_id string,

referer string,

ip string,

end_user_id string,

city_id string

)

row format delimited fields terminated by '\t'

stored as orc tblproperties ("orc.compress"="NONE");

hive (default)> insert into table log_orc_none select * from log_text ;

hive (default)> dfs -du -h /user/hive/warehouse/log_orc_none/ ;

create table log_orc_snappy(

track_time string,

url string,

session_id string,

referer string,

ip string,

end_user_id string,

city_id string

)

row format delimited fields terminated by '\t'

stored as orc tblproperties ("orc.compress"="SNAPPY");

hive (default)> insert into table log_orc_snappy select * from log_text ;

hive (default)> dfs -du -h /user/hive/warehouse/log_orc_snappy/ ;

hive (default)> set hive.fetch.task.conversion=none;

hive (default)> select * from emp;

hive (default)> select ename from emp;

hive (default)> select ename from emp limit 3;

hive (default)> set hive.fetch.task.conversion=more;

hive (default)> select * from emp;

hive (default)> select ename from emp;

hive (default)> select ename from emp limit 3;

set hive.exec.mode.local.auto=true;  //开启本地mr

//设置local mr的最大输入数据量，当输入数据量小于这个值时采用local  mr的方式，默认为134217728，即128M

set hive.exec.mode.local.auto.inputbytes.max=50000000;

//设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为4

set hive.exec.mode.local.auto.input.files.max=10;

hive (default)> set hive.exec.mode.local.auto=true; 

hive (default)> select * from emp cluster by deptno;

hive (default)> set hive.exec.mode.local.auto=false; 

hive (default)> select * from emp cluster by deptno;

// 创建大表
create table bigtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

// 创建小表
create table smalltable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

// 创建join后表的语句
create table jointable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

hive (default)> load data local inpath '/opt/module/datas/bigtable' into table bigtable;

hive (default)>load data local inpath '/opt/module/datas/smalltable' into table smalltable;

set hive.auto.convert.join = false;

insert overwrite table jointable

select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url

from smalltable s

left join bigtable  b

on b.id = s.id;

insert overwrite table jointable

select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url

from bigtable  b

left join smalltable  s

on s.id = b.id;

sbin/mr-jobhistory-daemon.sh start historyserver

// 创建原始表
create table ori(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

// 创建空id表
create table nullidtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

// 创建join后表的语句
create table jointable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

hive (default)> load data local inpath '/opt/module/datas/ori' into table ori;

hive (default)> load data local inpath '/opt/module/datas/nullid' into table nullidtable;

hive (default)> insert overwrite table jointable 

select n.* from nullidtable n left join ori o on n.id = o.id;

hive (default)> insert overwrite table jointable 

select n.* from (select * from nullidtable where id is not null ) n  left join ori o on n.id = o.id;

set mapreduce.job.reduces = 5;

insert overwrite table jointable

select n.* from nullidtable n left join ori b on n.id = b.id;

set mapreduce.job.reduces = 5;

insert overwrite table jointable
select n.* from nullidtable n full join ori o on 
case when n.id is null then concat('hive', rand()) else n.id end = o.id;

set hive.auto.convert.join = true;
//默认为true

set hive.mapjoin.smalltable.filesize=25000000;

set hive.auto.convert.join = true; 默认为true

insert overwrite table jointable
select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from smalltable s
join bigtable  b
on s.id = b.id;

insert overwrite table jointable
select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from bigtable  b
join smalltable  s
on s.id = b.id;

set hive.map.aggr = true

set hive.groupby.mapaggr.checkinterval = 100000

set hive.groupby.skewindata = true

hive (default)> create table bigtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

hive (default)> load data local inpath '/opt/module/datas/bigtable' into table bigtable;

set mapreduce.job.reduces = 5;

hive (default)> select count(distinct id) from bigtable;

hive (default)> select count(id) from (select id from bigtable group by id) a;

hive (default)> select o.id from bigtable b
join ori o on o.id = b.id
where o.id <= 10;

hive (default)> select b.id from bigtable b
join (select id from ori where id <= 10 ) o on b.id = o.id;

set hive.exec.dynamic.partition=true

set hive.exec.dynamic.partition.mode=nonstrict

set hive.exec.max.dynamic.partitions=1000

set hive.exec.max.dynamic.partitions.pernode=100

set hive.exec.max.created.files=100000

set hive.error.on.empty.partition=false

create table ori_partitioned(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) 
partitioned by (p_time bigint) 
row format delimited fields terminated by '\t';

hive (default)> load data local inpath '/opt/module/datas/ds1' into table ori_partitioned partition(p_time='20111230000010') ;
hive (default)> load data local inpath '/opt/module/datas/ds2' into table ori_partitioned partition(p_time='20111230000011') ;

create table ori_partitioned_target(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) PARTITIONED BY (p_time STRING) row format delimited fields terminated by '\t';

set hive.exec.dynamic.partition = true;  //（默认true）
set hive.exec.dynamic.partition.mode = nonstrict;  //(默认strict)
set hive.exec.max.dynamic.partitions = 1000;   //(默认1000)
set hive.exec.max.dynamic.partitions.pernode = 100;   //（默认100）
set hive.exec.max.created.files = 100000;  //(默认值100000)
set hive.error.on.empty.partition = false;   //(默认值false)

hive (default)> insert overwrite table ori_partitioned_target partition (p_time) 
select id, time, uid, keyword, url_rank, click_num, click_url, p_time from ori_partitioned;

hive (default)> show partitions ori_partitioned_target;

set hive.input.format= org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

hive (default)> select count(*) from emp;

hive (default)> set mapreduce.input.fileinputformat.split.maxsize=100;
hive (default)> select count(*) from emp;

set hive.exec.reducers.bytes.per.reducer=256000000

set hive.exec.reducers.max=1009

N=min(参数2=1009，总输入数据量/参数1=？)

set mapreduce.job.reduces = 5;

set hive.exec.parallel=true;              //打开任务并行执行,默认false
set hive.exec.parallel.thread.number=16;  //同一个sql允许最大并行度，默认为8。

EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] query

hive (default)> explain select * from emp;
hive (default)> explain select deptno, avg(sal) avg_sal from emp group by deptno;

hive (default)> explain extended select * from emp;
hive (default)> explain extended select deptno, avg(sal) avg_sal from emp group by deptno;

Table：显示这一行的数据是关于哪张表的
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key：实际使用的索引。如果为NULL，则没有使用索引。MYSQL很少会选择优化不足的索引，此时可以在SELECT语句中使用USE INDEX（index）来强制使用一个索引或者用IGNORE INDEX（index）来强制忽略索引
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：MySQL认为必须检索的用来返回请求数据的行数
type：这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL
system、const：可以将查询的变量转为常量.  如id=1; id为 主键或唯一键.
eq_ref：访问索引,返回某单一行的数据.(通常在联接时出现，查询使用的索引为主键或惟一键)
ref：访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生
range：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西，并且该字段上建有索引时发生的情况(注:不一定好于index)
index：以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描
ALL：全表扫描，应该尽量避免
Extra：关于MYSQL如何解析查询的额外信息，主要有以下几种
using index：只用到索引,可以避免访问表. 
using where：使用到where来过虑数据. 不是所有的where clause都要显示using where. 如以=方式访问索引.
using tmporary：用到临时表
using filesort：用到额外的排序. (当使用order by v1,而没用到索引时,就会使用额外的排序)
range checked for eache record(index map:N)：没有好的索引.

hive> show functions;

hive> desc function upper;

hive> desc function extended upper;

add jar linux_jar_path

create [temporary] function [dbname.]function_name AS class_name;

Drop [temporary] function [if exists] [dbname.]function_name;

hive (default)> add jar /opt/module/jars/Hive-1.0-SNAPSHOT.jar ;

hive (default)> create temporary function udf_lower as "HiveUDF.Lower";

hive (default)> select ename, udf_lower(ename) lowername from emp;

hive (default)> select * from emp;

hive (default)> select empno, ename from emp;

hive (default)> select ename AS name, deptno dn from emp;

hive (default)> select sal +1 from emp;

hive (default)> select count(1) cnt from emp;

hive (default)> select max(sal) max_sal from emp;

hive (default)> select min(sal) min_sal from emp;

hive (default)> select sum(sal) sum_sal from emp; 

hive (default)> select avg(sal) avg_sal from emp;

hive (default)> select * from emp limit 5;

hive (default)> select ename , sal from emp where sal > 1000;

hive (default)> select * from emp where sal =5000;

hive (default)> select * from emp where sal between 500 and 1000;

hive (default)> select * from emp where comm is null;

hive (default)> select * from emp where sal IN (1500, 5000);

hive (default)> select * from emp where sal LIKE '2%';

hive (default)> select * from emp where sal LIKE '_2%';

hive (default)> select * from emp where sal RLIKE '[2]';

hive (default)> select * from emp where sal>1000 and deptno=30;

hive (default)> select * from emp where sal>1000 or deptno=30;

hive (default)> select * from emp where deptno not IN(30, 20);

hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;

hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;

hive (default)> select deptno, avg(sal) from emp group by deptno;

hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;

hive (default)> select e.empno,e.ename,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

create table if not exists default.location(
loc int,
loc_name string
)
row format delimited fields terminated by '\t';

hive (default)> load data local inpath '/opt/module/datas/location.txt' into table default.location;

hive (default)>SELECT e.ename, d.deptno, l. loc_name
              FROM   emp e 
              JOIN   dept d
              ON     d.deptno = e.deptno 
              JOIN   location l
              ON     d.loc = l.loc;

hive (default)> select empno, deptno from emp, dept;

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno or e.ename=d.ename;  

hive (default)> select * from emp order by sal;

hive (default)> select * from emp order by sal desc;

hive (default)> select ename, sal*2 twosal from emp order by twosal;

hive (default)> select ename, deptno, sal from emp order by deptno, sal ;

hive (default)> set mapreduce.job.reduces=3;

hive (default)> set mapreduce.job.reduces;

hive (default)> select * from emp sort by empno desc;

hive (default)> insert overwrite local directory '/opt/module/datas/emp.txt' row format delimited fields terminated by '\t' select * from emp sort by deptno desc;

hive (default)> set mapreduce.job.reduces=3;

hive (default)> insert overwrite local directory '/opt/module/datas/distribute-result' row format delimited fields terminated by '\t' select * from emp distribute by deptno sort by empno desc;

hive (default)> insert overwrite local directory '/opt/module/datas/cluster-result' row format delimited fields terminated by '\t' select * from emp cluster by deptno;

hive (default)> select * from emp distribute by deptno sort by deptno;

create table stu_buck(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

hive (default)> desc formatted stu_buck;

hive (default)> load data local inpath '/opt/module/datas/s/student.txt' into table stu_buck;

create table stu(id int, name string)
row format delimited fields terminated by '\t';

load data local inpath '/opt/module/datas/s/student.txt' into table stu;

truncate table stu_buck;

select * from stu_buck;

insert into table stu_buck
select id, name from stu;

hive (default)> set hive.enforce.bucketing=true;

hive (default)> set mapreduce.job.reduces=-1;

hive (default)> truncate table stu_buck;

hive (default)> insert into table stu_buck
select id, name from stu;

hive (default)> select * from stu_buck;

hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);

hive (default)> select * from stu_buck tablesample(bucket 1 out of 3 on id);

hive (default)> select * from stu_buck tablesample(bucket 1 out of 2 on id);

hive (default)> select * from stu_buck tablesample(bucket 1 out of 8 on id);

hive (default)> select * from stu tablesample(0.1 percent) ;

hive>load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table student [partition (partcol1=val1,…)];

hive (default)> create table student (id string, name string) row format delimited fields terminated by '\t';

hive (default)> load data local inpath '/opt/module/datas/student.txt' into table default.student;

hive (default)> dfs -put /opt/module/datas/student.txt /user/movle/hive;

hive (default)>load data inpath '/user/movle/hive/student.txt' into table default.student;

hive (default)> dfs -put /opt/module/datas/student.txt /user/movle/hive/;

hive (default)>load data inpath '/user/movle/hive/student.txt' overwrite into table default.student;

hive (default)> create table student6(id int, name string) partitioned by (month string) row format delimited fields terminated by '\t';

hive (default)> insert into table  student6 partition(month='201909') values(1,'wangwu');

hive (default)> insert overwrite table student6 partition(month='201908')
             select id, name from student6 where month='201909';

hive (default)> from student6
              insert overwrite table student6 partition(month='201907')
              select id, name where month='201909'
              insert overwrite table student6 partition(month='201906')
              select id, name where month='201909';

create table if not exists student7
as select id, name from student;

hive (default)> create table if not exists student5(
              id int, name string
              )
              row format delimited fields terminated by '\t'
              location '/user/hive/warehouse/student5';

hive (default)> dfs -put /opt/module/datas/student.txt  /user/hive/warehouse/student5;

hive (default)> select * from student5;

hive (default)> export table default.student6 to '/user/hive/warehouse/export/student';

hive (default)> import table student2 partition(month='201909') from '/user/hive/warehouse/export/student';

hive (default)> insert overwrite local directory '/opt/module/datas/export/student'
            select * from student;

hive (default)> insert overwrite local directory '/opt/module/datas/export/student1'
             ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             
             select * from student;

hive (default)> insert overwrite directory '/user/movle/student2'
             ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
             select * from student;

hive (default)> dfs -get /user/hive/warehouse/student6/month=201909/000000_0  /opt/module/datas/export/student3.txt;

bin/hive -e 'select * from default.student;' > /opt/module/datas/export/student4.txt;

hive (default)> export table default.student to '/user/hive/warehouse/export/student2';

hive (default)> truncate table student;

hive (default)> create database db_hive;

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:For direct MetaStore DB connections, we don't support retries at the client level.)

hive> create database db_hive;

hive (default)> create database if not exists db_hive;

hive (default)> create database db_hive2 location '/db_hive2.db';

hive (default)> alter database db_hive set dbproperties('createtime'='20190506');

hive> desc database extended db_hive;

hive> show databases;

hive> show databases like 'db_hive*';

hive> desc database db_hive;

hive> desc database extended db_hive;

hive (default)> use db_hive;

hive>drop database db_hive2;

hive> drop database db_hive2;

hive> drop database if exists db_hive2;

hive> drop database db_hive;

hive> drop database db_hive cascade;

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 

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

[COMMENT table_comment] 

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

[CLUSTERED BY (col_name, col_name, ...) 

[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 

[ROW FORMAT row_format] 

[STORED AS file_format] 

[LOCATION hdfs_path]

create table if not exists student2(

id int, name string

)

row format delimited fields terminated by '\t'

stored as textfile

location '/user/hive/warehouse/student2';

create table if not exists student3
as select id from student;

create table if not exists student4 like student;

hive (default)> desc formatted student4;

create external table if not exists default.dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

create external table if not exists default.emp(
empno int,
ename string,
job string,
mgr int,
hiredate string, 
sal double, 
comm double,
deptno int)
row format delimited fields terminated by '\t';

hive (default)> show tables;

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept;

hive (default)> load data local inpath '/opt/module/datas/emp.txt' into table default.emp;

hive (default)> select * from emp;

hive (default)> select * from dept;

hive (default)> desc formatted dept;

/user/hive/warehouse/log_partition/20190702/20190702.log

/user/hive/warehouse/log_partition/20190703/20190703.log

/user/hive/warehouse/log_partition/20190704/20190704.log

hive (default)> create table dept_partition(
               deptno int, dname string, loc string
               )
               partitioned by (month string)
               row format delimited fields terminated by '\t';

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201909');

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201908');

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201907');

hive (default)> select * from dept_partition where month='201909';

hive (default)> select * from dept_partition where month='201909'
              union
              select * from dept_partition where month='201908'
              union
              select * from dept_partition where month='201907';

hive (default)> alter table dept_partition add partition(month='201906') ;

hive (default)>  alter table dept_partition add partition(month='201905') partition(month='201904');

hive (default)> alter table dept_partition drop partition (month='201904');

hive (default)> alter table dept_partition drop partition (month='201905'), partition (month='201906');

hive> show partitions dept_partition;

hive> desc formatted dept_partition;

hive (default)> create table dept_partition2(
               deptno int, dname string, loc string
               )
               partitioned by (month string, day string)
               row format delimited fields terminated by '\t';

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition2 partition(month='201909', day='13');

hive (default)> select * from dept_partition2 where month='201909' and day='13';

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201909/day=12;

hive (default)> dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=201909/day=12;

hive (default)> select * from dept_partition2 where month='201909' and day='12';

hive>msck repair table dept_partition2;

hive (default)> select * from dept_partition2 where month='201909' and day='12';

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201909/day=11;

hive (default)> dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=201909/day=11;

hive (default)> alter table dept_partition2 add partition(month='201909', day='11');

hive (default)> select * from dept_partition2 where month='201909' and day='11';

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201909/day=10;

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table dept_partition2 partition(month='201909',day='10');

hive (default)> select * from dept_partition2 where month='201909' and day='10';

ALTER TABLE table_name RENAME TO new_table_name

hive (default)> alter table dept_partition2 rename to dept_partition4;

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 

hive> desc dept_partition;

hive (default)> alter table dept_partition add columns(deptdesc string);

hive>desc dept_partition;

hive (default)> alter table dept_partition change column deptdesc desc int;

hive>desc dept_partition;

hive (default)> alter table dept_partition replace columns(deptno string, dname string, loc string);

hive>desc dept_partition;

hive (default)> drop table dept_partition;

mkdir datas

touch student.txt
vi student.txt

1001	zhangshan
1002	lishi
1003	zhaoliu

bin/hive

show databases;

use default;

show tables;

drop table student;

create table student(id int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

load data local inpath '/opt/module/datas/student.txt' into table student;

select * from student;

set;

bin/hive -hiveconf mapred.reduce.tasks=10;

hive (default)> set mapred.reduce.tasks;

hive (default)> set mapred.reduce.tasks=10;

hive (default)> set mapred.reduce.tasks;

bin/hdfs dfs -chmod g+w /user/hive/warehouse

pwd

mv hive-log4j.properties.template hive-log4j.properties

hive.log.dir=/opt/module/hive/logs

bin/hive

show databases;

use default;

show tables;

create table student(id int, name string) ;

show tables;

desc student;

insert into student values(1001,"ss1");

select * from student;

quit;

bin/hive -e "select id from student;"

touch hive.sql

select * from student;

bin/hive -f /opt/module/datas/hive.sql

bin/hive -f /opt/module/datas/hive.sql  > /opt/module/datas/hive_result.txt

exit;
quit;

dfs -ls /;

! ls /opt/module/datas;

cat .hivehistory