大数据环境搭建-Hive和Mysql

curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
yum makecache
yum install wget

wget -i https://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
yum localinstall mysql57-community-release-el7-10.noarch.rpm
yum repolist enabled | grep mysql

yum install -y mysql-community-server --nogpgcheck

service mysqld start

chkconfig --list | grep mysqld
chkconfig mysqld on

vi /etc/my.cnf

[mysqld]
lower_case_table_names=1
character_set_server = utf8
max_connections = 1000
max_allowed_packet = 100M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

service mysqld restart

vi /var/log/mysqld.log

[Note] A temporary password is generated for root@localhost: tr&:DeYtt8+k

mysql -uroot -p

set global validate_password_policy=0;
set global validate_password_length=1;
alter user user() identified by "psvmc123";
flush privileges;
quit

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'psvmc123' WITH GRANT OPTION;
FLUSH PRIVILEGES; 
quit

ALTER USER 'root'@'%' PASSWORD EXPIRE NEVER;
flush privileges;

tar -zxvf apache-hive-2.3.9-bin.tar.gz -C /data/tools/bigdata/

cp mysql-connector-java-8.0.28.jar /data/tools/bigdata/apache-hive-2.3.9-bin/lib/

cd /data/tools/bigdata/apache-hive-2.3.9-bin

cd conf
cp hive-default.xml.template hive-site.xml
cp hive-env.sh.template hive-env.sh
cp hive-log4j2.properties.template hive-log4j.properties

<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://127.0.0.1:3306/metastore?createDatabaseIfNotExist=true</value>
  <description>JDBC connect string for a JDBC metastore</description>
</property>

<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.cj.jdbc.Driver</value>
  <description>Driver class name for a JDBC metastore</description>
</property>

<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>root</value>
  <description>username to use against metastore database</description>
</property>

<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>psvmc123</value>
  <description>password to use against metastore database</description>
</property>

<property>
  <name>hive.cli.print.current.db</name>
  <value>true</value>
  <description>Whether to include the current database in the Hive prompt.</description>
</property>

<property>
  <name>hive.cli.print.header</name>
  <value>true</value>
  <description>Whether to print the names of the columns in query output.</description>
</property>

<property>
  <name>hive.fetch.task.conversion</name>
  <value>more</value>
  <description>
    Some select queries can be converted to single FETCH task minimizing latency.
    Currently the query should be single sourced not having any subquery and should not have
    any aggregations or distincts (which incurs RS), lateral views and joins.
    1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only
    2. more    : SELECT, FILTER, LIMIT only (TABLESAMPLE, virtual columns)
  </description>
</property>

<property>
  <name>hive.server2.long.polling.timeout</name>
  <value>5000ms</value>
  <description>Time in milliseconds that HiveServer2 will wait, before responding to asynchronous calls that use long polling</description>
</property>
<property>
  <name>hive.server2.thrift.bind.host</name>
  <value>localhost</value>
  <description>Bind host on which to run the HiveServer2 Thrift interface.
  Can be overridden by setting $HIVE_SERVER2_THRIFT_BIND_HOST</description>
</property>
<property>
  <name>hive.server2.enable.doAs</name>
  <value>false</value>
  <description>
   Setting this property to true will have HiveServer2 execute
    Hive operations as the user making the calls to it.
  </description>
</property>

${system:java.io.tmpdir}

/data/tools/bigdata/apache-hive-2.3.9-bin/tmp

property.hive.log.dir = /data/tools/bigdata/apache-hive-2.3.9-bin/logs/

# 删除mysql中的元数据库
drop database metastore;
# 新建一个元数据库
create database metastore;

# 在命令行中,重新初始化
cd /data/tools/bigdata/apache-hive-2.3.9-bin
./bin/schematool -dbType mysql -initSchema

cd /data/tools/bigdata/apache-hive-2.3.9-bin
./bin/hive

show tables;

/data/tools/bigdata/hadoop-2.7.7/bin/hadoop fs -chmod 777 /tmp/
/data/tools/bigdata/hadoop-2.7.7/bin/hadoop fs -chmod 777 /user/hive/warehouse

cd /data/tools/bigdata/apache-hive-2.3.9-bin
./bin/hiveserver2

create table t_user(id int,name string,password string);

create table tablename(name string,password string)row format delimited fields terminated by ',';

load data inpath '/user/hadoop/output7/part-r-00000' into table t_table01;

create table table02 like table01;

create table tablename(id int,line string) partitioned by (dt string,country string);

select count(*) from tablename where count>50;

select * from tablename where count > 50 order by count;

show partitions tablename;

show tables;

show tables 't*';

describe tablename;

alter table table01 rename to table02;

alter table tablename add columns(new_col2 int comment 'a commment');

alter table tablename add columns(new_col3 int);

drop table tablename;

LOAD DATA LOCAL INPATH '/home/hadoop/input/sample.txt' OVERWRITE INTO TABLE records;

load data inpath '/user/hive/warehouse/part-r-00000' overwrite into table clickstream_log PARTITION(dt = '2018-11-30');

show functions;

describe function substr;

select col1[0],col2['b'],col3.c from complex;

select col1[0],col2['b'],col3.c from complex;

SELECT sales., things. FROM sales JOIN things ON (sales.id = things.id);

Explain SELECT sales., things. FROM sales JOIN things ON (sales.id = things.id);

SELECT sales., things. FROM sales LEFT OUTER JOIN things ON (sales.id = things.id);
SELECT sales., things. FROM sales RIGHT OUTER JOIN things ON (sales.id = things.id);
SELECT sales., things. FROM sales FULL OUTER JOIN things ON (sales.id = things.id);

SELECT * FROM things LEFT SEMI JOIN sales ON (sales.id = things.id);

SELECT /+ MAPJOIN(things) / sales., things. FROM sales JOIN things ON (sales.id = things.id);

FROM records2
INSERT OVERWRITE TABLE stations_by_year SELECT year, COUNT(DISTINCT station) GROUP BY year
INSERT OVERWRITE TABLE records_by_year SELECT year, COUNT(1) GROUP BY year
INSERT OVERWRITE TABLE good_records_by_year SELECT year, COUNT(1) WHERE temperature != 9999 AND (quality = 0 OR quality = 1 OR quality = 4 OR quality = 5 OR quality = 9) GROUP BY year;

CREATE TABLE target AS SELECT col1,col2 FROM source;

CREATE VIEW valid_records AS SELECT * FROM records2 WHERE temperature !=9999;

DESCRIBE EXTENDED valid_records;