CentOS7下构建SQL引擎
前言
全文来自:工业互联网时代,我们为什么需要时序数据库之:适合的就是最好的 (baidu.com)
当年图灵用他深邃的眼睛,看穿了世间万物的计算本质:凡是可以计算的,通过迭代,最终都可以表示为0、1的逻辑判断
图灵机指出了数据的3个核心需求:1、数据写入;2、数据存储;3、数据读取。
时序数据库的特点
-
数据写入
-
时间是一个主坐标轴,数据通常按照时间顺序抵达
大多数测量是在观察后的几秒或几分钟内写入的,抵达的数据几乎总是作为新条目被记录
95%到99%的操作是写入,有时更高 -
更新几乎没有
-
-
数据读取
- 随机位置的单个测量读取、删除操作几乎没有
- 读取和删除是批量的,从某时间点开始的一段时间内
- 时间段内读取的数据有可能非常巨大
-
数据存储
- 数据结构简单,价值随时间推移迅速降低
- 通过压缩、移动、删除等手段降低存储成本
关系数据库:
(1)数据写入:大多数操作都是DML操作,插入、更新、删除等;
(2)数据读取:读取逻辑一般都比较复杂;
(3)数据存储:很少压缩,一般也不设置数据生命周期管理
时间序列数据跟关系型数据库有太多不同,但是很多公司并不想放弃关系型数据库。于是就产生了一些特殊的用法,比如:用 MySQL 的 VividCortex, 用 PostgreSQL 的 TimescaleDB;当然,还有人依赖K-V、NoSQL数据库或者列式数据库的,比如:OpenTSDB的HBase,而Druid则是一个不折不扣的列式存储系统;更多人觉得特殊的问题需要特殊的解决方法,于是很多时间序列数据库从头写起,不依赖任何现有的数据库, 比如: Graphite,InfluxDB。
性能
时序数据库,核心问题去解决批量读写,对于 95% 以上场景都是写入的时序数据库,B-Tree 很明显是不合适的,业界主流都是采用 LSM Tree(Log Structured Merge Tree)或者LSM的“升级版”TSM(Time Sort Merge Tree) 替换 B-Tree,比如 Hbase、Cassandra、InfluxDB等。LSM Tree 核心思想就是通过内存写和后续磁盘的顺序写入获得更高的写入性能,避免了随机写入。
LSM Tree 简单操作流程如下:
数据写入和更新时首先写入位于内存里的数据结构。同时,为了避免数据丢失也会先写到磁盘文件中。
内存里的数据结构会定时或者达到固定大小会刷到磁盘。
随着磁盘上积累的文件越来越多,会定时的进行合并操作,减少文件数量。
在内存or文件中,对数据进行压缩、去重等操作。
分区
还有一个提升性能的关键点,即:分布式处理。
通常分布式数据库一般有两种Sharding策略:Range Sharding和Hash Sharding,前者对于基于主键的范围扫描比较高效;后者对于离散大规模写入以及随即读取相对比较友好。
对于时序数据库来说,基于时间的Range Sharding是最合理的考虑,但如果仅仅使用Time Range Sharding,会存在一个很严重的问题,即写入会存在热点,基于TimeRange Sharding的时序数据库写入必然会落到最新的Shard上,其他老Shard不会接收写入请求。对写入性能要求很高的时序数据库来说,热点写入肯定不是最优的方案。解决这个问题最自然的思路就是再使用Hash进行一次分区,**基于Key的Hash分区方案可以通过散列很好地解决热点写入的问题。
TimescaleDB
是基于 PostgreSQL 数据库开发的一款时序数据库,以插件化的形式打包提供,随着 PostgreSQL 的版本升级而升级,不会因为另立分支带来麻烦。
PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)
PostgreSQL 特征:
数据仓库(DW):能平滑迁移至同属 PostgreSQL 生态的 GreenPlum,DeepGreen,HAWK 等,使用 FDW 进行 ETL(postgres fdw是一种外部访问接口,它可以被用来访问存储在外部的数据,这些数据可以是外部的pg数据库,也可以是oracle、mysql等数据库,甚至可以是文件)
TimescaleDB 具有以下特点
-
基于时序优化
-
自动分片(自动按时间、空间分片(chunk))
-
全 SQL 接口
-
支持垂直于横向扩展
-
支持时间维度、空间维度自动分区。空间维度指属性字段(例如传感器 ID,用户 ID 等)
-
支持多个 SERVER,多个 CHUNK 的并行查询。分区在 TimescaleDB 中被称为 chunk。
-
自动调整 CHUNK 的大小
-
内部写优化(批量提交、内存索引、事务支持、数据倒灌)。
- 内存索引,因为 chunk size 比较适中,所以索引基本上都不会被交换出去,写性能比较好。
- 数据倒灌,因为有些传感器的数据可能写入延迟,导致需要写以前的 chunk,timescaleDB 允许这样的事情发生(可配置)。
-
复杂查询优化(根据查询条件自动选择 chunk,最近值获取优化(最小化的扫描,类似递归收敛),limit 子句 pushdown 到不同的 server,chunks,并行的聚合操作)
-
利用已有的 PostgreSQL 特性(支持 GIS,JOIN 等),方便的管理(流复制、PITR)
-
支持自动的按时间保留策略(自动删除过旧数据)
Windows下安装postgresql
使用 EnterpriseDB 来下载安装,EnterpriseDB 是全球唯一一家提供基于 PostgreSQL 企业级产品与服务的厂商。下载地址:https://www.enterprisedb.com/downloads/postgres-postgresql-downloads。选择12.8版本,之后timescale也是选择12版本,两个版本保存一致。
设置超级用户密码
5432默认端口号
一直next,直到完成安装。其它组件暂时先不安装。
配置环境变量,在Path里
打开pgAdmin
设置密码,两个密码可设置成一样
设置中文显示:File->Preference->UserLanguage 选择简体中文并刷新页面
完成
打开Navicat,连接postgresql
连接成功。
安装TimescaleDB
选择与postgresql对应的版本
关闭postgresql服务
解压timescaledb,以管理员身份运行setup.exe
,输入D:\PostgreSQL\12\data\postgresql.conf
除了上面那个路径外其他的选择yes就行。
安装完成后,启动postgresql服务,打开命令行窗口
输入create extension if not exists timescaledb cascade;
出现上图证明TimescaleDB安装成功!
第一个JDBC程序
打开命令行窗口,输入
postgres=# create database jdbcstudy; //创建数据库
postgres=# \l //查看数据库
postgres=# \c jdbcstudy //使用数据库
jdbcstudy=# create table users(
jdbcstudy(# id int primary key,
jdbcstudy(# name varchar(40),
jdbcstudy(# password varchar(40),
jdbcstudy(# email varchar(60),
jdbcstudy(# birthday DATE); //创建常规表
CREATE TABLE
jdbcstudy=# insert into users values
(1,'zhansan','123456','zhangsan@sina.com','1980-12-04'),(2,'lisi','123456','lisi@sina.com','1981-12-04'),(3,'wangwu','123456','wangwu@sina.com','1979-12-04'); //插入数据
jdbcstudy=# select * from users; //查看表
使用IDEA,新建普通java项目,连接postgresql
可以在这里看到刚才建的users表
导入postgreSQL的JDBC驱动程序jar包(https://jdbc.postgresql.org/download.html)
新建第一个JDBC程序,JdbcDemo01
package com.wang.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JdbcDemo01 {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/jdbcstudy";
String user = "postgres";
String pwd = "201314";
Connection conn = null;
try {
Class.forName("org.postgresql.Driver");
conn = DriverManager.getConnection(url, user, pwd);
Statement st = conn.createStatement();
/* Statement:适合只执行一次或极少执行的sql文。
PreparedStatement:适合执行需要传参并且会多次执行的sql文,并且一定程度上防止了sql注入。
CallableStatement:适合执行存储过程。*/
ResultSet rs = st.executeQuery("select id,name from users");
while (rs.next()) {
System.out.println("id="+rs.getString(1)+",name is "+rs.getString(2));
}
rs.close();
st.close();
conn.close();
} catch (Exception e) {
System.out.println(e);
e.printStackTrace();
}
}
}
运行成功。
创建超表
参考官方文档,CREATE | Timescale Docs
进入jdbcstudy
数据库,输入create extension if not exists timescaledb cascade;
,将PostgreSQL数据库转换为TimescaleDB
创建标准表
CREATE TABLE conditions (
time TIMESTAMPTZ NOT NULL,
location TEXT NOT NULL,
temperature DOUBLE PRECISION NULL
);
使用create_hypertable
创建超表。(创建分布式超表使用create_distributed_hypertable
)
SELECT create_hypertable('conditions', 'time');
TimescaleDB基本操作_woai243779594的博客-CSDN博客
添加了一个新的列humidity
(需要将任何新增列默认值设置为 NULL,否则将需要填写属于此超表的所有行的此值)
ALTER TABLE conditions ADD COLUMN humidity DOUBLE PRECISION NULL;
数据可以使用标准 SQL 命令插入到超表中
INSERT INTO conditions(time, location, temperature, humidity)
VALUES (NOW(), 'office', 70.0, 50.0);
可进行多行插入(推荐)
INSERT INTO conditions
VALUES
(NOW(), 'school', 73.0, 48.0),
(NOW(), 'basement', 66.5, 60.0),
(NOW(), 'garage', 77.0, 65.2);
可以看到,多条插入时时间都是相同的,单批次插入的数据属于同一时间分区。
数据可以使用标准 SQL 命令从超表查询,包括任意子句、命令、连接、子查询、窗口功能、用户定义的功能 (UDF)、子句等。
创建测试表
pgsql常用操作
\l //列举数据库
\dt //列举表
\d Person //查看表结构
\di //查看索引
创建Person表
CREATE TABLE Person(
time TIMESTAMPTZ NOT NULL,
num INT NULL,
birthday INT NULL,
name CHAR NULL,
age INT NULL,
city CHAR NULL,
tel CHAR NULL,
PRIMARY KEY(time,num)
);
SELECT create_hypertable('Person', 'time');
\d Person //查看表结构
编写java代码,插入10000条数据
package com.wang.jdbc;
import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.util.Calendar;
import java.util.Random;
public class RandomPerson {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/jdbcstudy";
String user = "postgres";
String pwd = "201314";
Connection conn = null;
PreparedStatement st = null;
try {
Class.forName("org.postgresql.Driver");
conn = DriverManager.getConnection(url, user, pwd);
for (int i = 0; i < 10000; i++) {
int birthday = Birthday();
Calendar cal = Calendar.getInstance();
int year = cal.get(Calendar.YEAR);
int age = year - birthday / 10000;
String sql = "INSERT INTO Person(time,num,birthday,name,age,city,tel) VALUES (NOW(),?,?,?,?,?,?)";
st = conn.prepareStatement(sql); //预编译
st.setInt(1, i);
st.setInt(2, birthday);
st.setString(3, getName());
st.setInt(4, age);
st.setString(5, getCity());
st.setString(6, getTel());
st.executeUpdate();
}
System.out.println("完成");
st.close();
conn.close();
} catch (Exception e) {
System.out.println(e);
e.printStackTrace();
}
}
//随机出生日期
private static int Birthday() {
int start = 1961;
int end = 2001;
Calendar birthday = Calendar.getInstance();
birthday.set(Calendar.YEAR, (int) (Math.random() *(end-start+1))+start);
birthday.set(Calendar.MONTH, (int) (Math.random() * 12));
birthday.set(Calendar.DATE, (int) (Math.random() * 31));
StringBuilder builder = new StringBuilder();
builder.append(birthday.get(Calendar.YEAR));
long month = birthday.get(Calendar.MONTH) + 1;
if (month < 10) {
builder.append("0");
}
builder.append(month);
long date = birthday.get(Calendar.DATE);
if (date < 10) {
builder.append("0");
}
builder.append(date);
return Integer.parseInt(builder.toString());
}
//随机城市
private static String getCity(){
String[] address = {"北京","上海","广州","西安","重庆","南京","拉萨","大理","成都","深圳",
"天津","烟台","呼和浩特","武汉","长沙","杭州","苏州","香港","郑州","大连"};
int a = (int) (Math.random()*10);
return address[a];
}
//随机电话
private static String getTel() {
String[] telFirst="134,135,136,137,138,139,150,151,152,157,158,159,130,131,132,155,156,133,153".split(",");
int index=getNum(0,telFirst.length-1);
String first=telFirst[index];
String second=String.valueOf(getNum(1,888)+10000).substring(1);
String third=String.valueOf(getNum(1,9100)+10000).substring(1);
return first+second+third;
}
public static int getNum(int start,int end)
{
return (int)(Math.random()*(end-start+1)+start);
}
//随机姓名
public static String getName() {
Random random = new Random();
String[] Surname = { "赵", "钱", "孙", "李", "周", "吴", "郑", "王", "冯", "陈", "褚", "卫", "蒋", "沈", "韩", "杨", "朱", "秦", "尤", "许",
"何","吕","施","张","孔","曹","严","华","金","魏","陶","姜","戚","谢","邹","喻","柏","水","窦","章"};
int index = random.nextInt(Surname.length - 1);
String name = Surname[index]; // 获得一个随机的姓氏
/* 从常用字中选取一个或两个字作为名 */
if (random.nextBoolean()) {
name += RandomPerson.getChinese() + RandomPerson.getChinese();
} else {
name += RandomPerson.getChinese();
}
return name;
}
public static String getChinese() {
String str = null;
int highPos, lowPos;
Random random = new Random();
highPos = (176 + Math.abs(random.nextInt(71)));// 区码,0xA0打头,从第16区开始,即0xB0=11*16=176,16~55一级汉字,56~87二级汉字
random = new Random();
lowPos = 161 + Math.abs(random.nextInt(94));// 位码,0xA0打头,范围第1~94列
byte[] bArr = new byte[2];
bArr[0] = (new Integer(highPos)).byteValue();
bArr[1] = (new Integer(lowPos)).byteValue();
try {
str = new String(bArr, "GB2312"); // 区位码组合成汉字
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
return str;
}
插入数据时,可变换这方式插入,例如增加延时,编写多条插入的sql语句
Thread.sleep(100); //延时
Linux下构建SQL引擎
构想:
Hadoop3.1.4+hive3.1.2+postgresql10+TimescaleDB+mysql5.7+presto0.196+sqoop1.4.7
使用之前搭建好的Hadoop集群1、Flink1.10集群环境搭建 - 萘汝 - 博客园 (cnblogs.com)
安装PostgreSQL
数据库
1.安装rmp文件
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
2.安装PostgreSql10
yum install postgresql10
3.安装服务端
yum install postgresql10-server
4.初始化
/usr/pgsql-10/bin/postgresql-10-setup initdb
5.设置自动启动并启动postgresql服务
systemctl enable postgresql-10
systemctl start postgresql-10
创建用户和数据库
1、使用postgres用户登录(PostgresSQL安装后会自动创建postgres用户,无密码)
su - postgres
2、登录postgresql数据库
psql
3、修改默认生成的 postgres 用户密码
alter user postgres with encrypted password '201314';
\l //查看数据库
4、退出psql
\q
exit
安装TimescaleDB
时序数据库(以插件形式存在于postgresql)
参照官网输入以下指令
sudo tee /etc/yum.repos.d/timescale_timescaledb.repo <<EOL
[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/$(rpm -E %{rhel})/\$basearch
repo_gpgcheck=1
gpgcheck=0
enabled=1
gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt
metadata_expire=300
EOL
sudo yum update -y
sudo yum install -y timescaledb-postgresql-10
修改postgresql.conf,在shared_preload_libraries =''
里添加timescaledb
vi /var/lib/pgsql/12/data/postgresql.conf
重启postgresql
systemctl stop postgresql-10.service
systemctl start postgresql-10.service
验证是否安装成功
su postgres
psql
create extension timescaledb;
开启远程访问
1.修改/var/lib/pgsql/10/data/postgresql.conf文件,取消 listen_addresses 的注释,将参数值改为“*”
vi /var/lib/pgsql/10/data/postgresql.conf
2.修改/var/lib/pgsql/10/data/pg_hba.conf文件,增加下图红框部分内容
vi /var/lib/pgsql/10/data/pg_hba.conf
3.切换到root用户,重启postgresql服务
systemctl restart postgresql-10.service
4、在主机使用数据库连接工具测试连接
补充:服务启动、关闭、重启、查看状态命令
systemctl start postgresql-10.service // 启动服务
systemctl stop postgresql-10.service // 关闭服务
systemctl restart postgresql-10.service // 重启服务
systemctl status postgresql-10.service // 查看状态
重启postgre数据库,
systemctl restart postgresql-10.service
登录postgresql,创建hive数据库
安装MySQL
关系数据库
在CentOS中默认安装有MariaDB,这个是MySQL的分支,但为了需要,还是要在系统中安装MySQL。
版本选择:mysql-5.7
rpm -qa|grep mariadb //查看是否安装mariadb
rpm -e mariadb-libs --nodeps //卸载mariadb
1.下载安装
cd /usr/local
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz //下载
tar -zxvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz //解压
mv mysql-5.7.35-linux-glibc2.12-x86_64 mysql //改名
mkdir /usr/local/mysql/data //新建data目录
groupadd mysql //创建用户组
useradd -g mysql mysql //用户和密码
chown -R mysql.mysql /usr/local/mysql //权限
cd /usr/local/mysql/bin //切换到bin目录
./mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/ --initialize //初始化,将临时密码保存下来
2.新建my.cnf文件,vi /etc/my.cnf
,添加以下内容:
[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=/usr/local/mysql/
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/data
# 允许最大连接数
max_connections=10000
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8
3.添加mysqld服务到系统
cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
chkconfig --add mysql
4.启动mysql
service mysql start
service mysql status //查看启动状态
ln -s /usr/local/mysql/bin/mysql /usr/bin //将mysql命令添加到服务
mysql -uroot -p //登录mysql,使用之前的临时密码
5.修改root密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '201314';
flush privileges;
6.修改远程连接并生效
use mysql;
update user set host='%' where user='root';
flush privileges;
select user,host from user;
至此,安装完成。
测试:在主机上连接mysql
安装Hive
数据仓库(元数据存储在postgresql中,元数据包括表名、表的属性等,实际数据存储于 HDFS 中)
输入 start-all.sh
,启动Hadoop集群,
hadoop01:
hadoop02:
hadoop03:
下载Hive,官网下载,由于Hadoop的版本是3.1.4,所以这里Hive选择3.1.2版本
cd /usr/local
wget https://dlcdn.apache.org/hive/hive-3.1.2/apache-hive-3.1.2-bin.tar.gz
解压:tar -zxvf apache-hive-3.1.2-bin.tar.gz
、改名:mv apache-hive-3.1.2-bin hive
hive只需要在hadoop的namenode节点集群里安装即可,即hadoop01。
配置环境 变量,编辑/etc/profile
文件
export HIVE_HOME=/usr/local/hive
export HIVE_CONF_DIR=${HIVE_HOME}/conf
export PATH=$PATH:$HIVE_HOME/bin
export CLASSPATH=.:${HIVE_HOME}/lib:$CLASSPATH
source /etc/profile
更新配置,输入hive --version
查看hive版本
安装成功。
配置Hive
1.让hadoop新建/user/hive/warehouse目录,执行命令:
$HADOOP_HOME/bin/hadoop fs -mkdir -p /user/hive/warehouse
给新建的目录赋予读写权限,执行命令:
$HADOOP_HOME/bin/hadoop fs -chmod 777 /user/hive/warehouse
2.让hadoop新建tmp目录,执行命令:
$HADOOP_HOME/bin/hadoop fs -mkdir -p /tmp/hive/
赋予权限,执行命令:
$HADOOP_HOME/bin/hadoop fs -chmod 777 /tmp/hive
3.查看hdfs目录和tmp目录是否创建成功
$HADOOP_HOME/bin/hadoop fs -ls /user/hive/
$HADOOP_HOME/bin/hadoop fs -ls /tmp/
4.修改hive-site.xml中的临时目录
cd /usr/local/hive/conf
,修改hive-site.xml。
如果没有,就以模板复制一个:
cp hive-default.xml.template hive-site.xml
vi hive-site.xml //配置hive-site.xml
- 将hive-site.xml文件中的${system:java.io.tmpdir}替换为/usr/local/hive/tmp
%s#${system:java.io.tmpdir}#/usr/local/hive/tmp#g
6.将${system:user.name}都替换为root
%s#${system:user.name}#root#g
7.下载postgresql的jdbc驱动包(需要选择与自己系统环境相符的版本):
https://jdbc.postgresql.org/download/postgresql-42.2.24.jar
将驱动包移动到hive的lib目录下:mv postgresql-42.2.24.jar /usr/local/hive/lib
修改hive-site.xml,将默认数据库改为postgresql,添加以下内容
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:postgresql://192.168.115.130:5432/hive?characterEncoding=UTF-8</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>org.postgresql.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>postgres</value>
<description>username to use against metastore database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>201314</value>
<description>password to use against metastore database</description>
</property>
修改hive.metastore.schema.verification,将对应的value修改为false
查找ConnectionURL、ConnectionDriverName、ConnectionUserName、ConnectionPassword,将多余的注释掉,只保留一个
找到hive-site.xml的第3237行
直接将<description></description>
注释掉
- 将hive-env.sh.template文件复制一份,并且改名为hive-env.sh
cp hive-env.sh.template hive-env.sh
vi hive-env.sh
,添加以下内容
export HADOOP_HOME=/usr/local/hadoop
export PATH=$PATH:$HIVE_HOME/bin
export HIVE_CONF_DIR=/usr/local/hive/conf
export HIVE_AUX_JARS_PATH=/usr/local/hive/lib
9.查看hadoop和hive的两个guava.jar版本不一致,两个位置分别位于下面两个目录:
/usr/local/hive/lib/
/usr/local/hadoop/share/hadoop/common/lib/
解决办法:删除低版本的那个,将高版本的复制到低版本目录下,hadoop目录下的为guava-27.0-jre.jar,hive目录下的为guava-19.0.jar,删除hive目录下的guava-19.0.jar,将hadoop的复制过去
rm /usr/local/hive/lib/guava-19.0.jar
cp /usr/local/hadoop/share/hadoop/common/lib/guava-27.0-jre.jar /usr/local/hive/lib/
启动测试
在postgres数据库中新建hive数据库。
初始化数据库schematool -dbType postgres -initSchema
,生成元数据
启动hive,./hive
执行show databases;
查看数据库
执行查看函数的命令show funtions;
执行查看sum函数的详细信息的命令desc function sum;
导入数据测试
1.在终端新建文件:touch /usr/local/hive/student.txt
添加内容:vi /usr/local/hive/student.txt
(注意:id和name直接是tab键)
001 zhangsan
002 lisi
003 wangwu
004 zhaoliu
005 chenqi
2.新建数据库,创建数据表
create database db_hive_edu;
use db_hive_edu;
create table student(id int,name string) row format delimited fields terminated by '\t';
3.将student.txt写入到数据表中
load data local inpath '/usr/local/hive/student.txt' into table db_hive_edu.student;
查看是否写入成功select * from student;
4.在界面上查看刚才写入hdfs的数据,打开浏览器访问如下地址:
http://hadoop01:9870/explorer.html#/user/hive/warehouse/db_hive_edu.db
点击student,
再点击student.txt,会弹出一个框
5.在MySQL数据库中查看hive创建的表
完成。
安装Presto
数据查询引擎(既支持非关系数据源查询,如HDFS、HBase;又支持关系数据源查询,如:MySQL、PostgreSQL)
由于在Presto0.209版本后,要求jdk版本至少为8u151
所以这里选择0.196版本进行安装
wget https://repo1.maven.org/maven2/com/facebook/presto/presto-server/0.196/presto-server-0.196.tar.gz
tar -zxvf presto-server-0.196.tar.gz //解压
mv presto-server-0.196 presto //改名
配置Presto
cd /usr/local/presto //进入presto目录
mkdir data //创建存储数据文件夹
mkdir etc //创建存储配置文件文件夹
1.添加jvm.config配置文件vi /usr/local/presto/etc/jvm.config
添加以下内容:
-server
-Xmx16G
-XX:+UseG1GC
-XX:G1HeapRegionSize=32M
-XX:+UseGCOverheadLimit
-XX:+ExplicitGCInvokesConcurrent
-XX:+HeapDumpOnOutOfMemoryError
-XX:+ExitOnOutOfMemoryError
2.Presto可以支持多个数据源
mkdir /usr/local/presto/etc/catalog
cd /usr/local/presto/etc/catalog
配置支持Hive的数据源
vi hive.properties
connector.name=hive-hadoop2
hive.metastore.uri=thrift://hadoop01:9083
hive.config.resources=/usr/local/hadoop/conf/core-site.xml,/usr/local/hadoop/conf/hdfs-site.xml
配置postgresql的数据源:
vi postgresql.properties
connector.name=postgresql
connection-url=jdbc:postgresql://hadoop01:5432/jdbcstudy
connection-user=postgres
connection-password=201314
将hadoop01上的presto分发到hadoop02、hadoop03
scp -r /usr/local/presto root@hadoop02:/usr/local/
scp -r /usr/local/presto root@hadoop03:/usr/local/
3.分别进入三台主机的/usr/local/presto/etc
路径,配置node属性,node.id每个节点都不一样
vi /usr/local/presto/etc/node.properties
Hadoop01:
node.environment=production
node.id=ffffffff-ffff-ffff-ffff-ffffffffffff
node.data-dir=/usr/local/presto/data
Hadoop02:
node.environment=production
node.id=ffffffff-ffff-ffff-ffff-fffffffffffe
node.data-dir=/usr/local/presto/data
Hadoop03:
node.environment=production
node.id=ffffffff-ffff-ffff-ffff-fffffffffffd
node.data-dir=/usr/local/presto/data
配置日志级别信息:
vi /usr/local/presto/etc/log.properties
com.facebook.presto=DEBUG
4.Presto是由一个coordinator节点和多个worker节点组成。在hadoop01上配置为coordinator,在hadoop02、hadoop03上配置为worker。
vi /usr/local/presto/etc/config.properties
Hadoop01:
coordinator=true
node-scheduler.include-coordinator=false
http-server.http.port=8880
query.max-memory=1GB
query.max-memory-per-node=512MB
discovery-server.enabled=true
discovery.uri=http://hadoop01:8880
Hadoop02、Hadoop03:
coordinator=false
http-server.http.port=8880
query.max-memory=1GB
query.max-memory-per-node=512MB
discovery-server.enabled = true
discovery.uri=http://hadoop01:8880
最后preso目录如下图所示,
5.配置环境变量
vim /etc/profile
export PRESTO_HOME=/usr/local/presto
export PATH=$PATH:$PRESTO_HOME/bin
source /etc/profile
presto --version //查看版本
启动测试
在hadoop01的/usr/local/hive目录下,启动Hive Metastore,用caron角色
nohup hive --service metastore >/dev/null 2>&1 &
分别在hadoop01、hadoop02、hadoop03上启动Presto Server
launcher start //直接启动
launcher run //可查看允许状态
打开浏览器,访问 hadoop01:8880
命令行启动(hadoop01)
下载启动jar包 presto-cli-0.196-executable.jar
cd /usr/local/presto/bin
wget https://repo1.maven.org/maven2/com/facebook/presto/presto-cli/0.196/presto-cli-0.196-executable.jar
mv presto-cli-0.196-executable.jar presto
chmod +x presto
连接hive,并启动:
presto --server hadoop01:8880 --catalog hive --schema default
show schemas; //查看数据库
show tables from db_hive_edu; //查询数据库db_hive_edu中的表
安装配置完成。
测试postgresql
在postgresql数据库中新建jdbcstudy数据库,新建person表并插入一些数据。
cd /usr/local/presto/bin
./launcher start //启动
./presto --server hadoop01:8880 --catalog postgresql --schema jdbcstudy
show schemas;
show tables from public;
基本查询:
select * from postgresql.public.person limit 5; //查看person表前5条数据
select count(*) from postgresql.public.person; //统计数据条数
select min(age) as "最小",max(age) as "最大" from postgresql.public.person; //查询age的最大值和最小值并重命名
select count(*) from postgresql.public.person where tel like '132%'; //统计电话号码以132开头的
完成。
安装Sqoop
数据迁移工具(Hadoop 和关系数据库服务器之间迁移数据的工具)
sqoop只需要安装在一个节点上,这个节点要能够访问到hive、hbase,所以这里安装在hadoop01节点上。
下载地址,这里选择1.4.7版本
wget https://archive.apache.org/dist/sqoop/1.4.7/sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz
解压tar -zxvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz
,改名
mv sqoop-1.4.7.bin__hadoop-2.6.0 sqoop
配置sqoop
配置环境变量
vim /etc/profile
export SQOOP_HOME=/usr/local/sqoop
export PATH=$PATH:$SQOOP_HOME/bin
export CLASSPATH=$CLASSPATH:$SQOOP_HOME/lib
source /etc/profile
查看环境变量是否配置成功sqoop-version
修改sqoop-env.sh
cd /usr/local/sqoop/conf
mv sqoop-env-template.sh sqoop-env.sh
vi sqoop-env.sh
增加如下内容:
export HADOOP_COMMON_HOME=/usr/local/hadoop
export HADOOP_MAPRED_HOME=/usr/local/hadoop
export HIVE_HOME=/usr/local/hive
export ZOOKEEPER_HOME=/usr/local/zookeeper
export ZOOCFGDIR=/usr/local/zookeeper/conf
修改configure-sqoop
cd /usr/local/sqoop/bin
vi configure-sqoop
注释掉HCatalog,Accumulo(除非你准备使用HCatalog,Accumulo等HADOOP上的组件)
# Moved to be a runtime check in sqoop.
#if [ ! -d "${HCAT_HOME}" ]; then
# echo "Warning: $HCAT_HOME does not exist! HCatalog jobs will fail."
# echo 'Please set $HCAT_HOME to the root of your HCatalog installation.'
#fi
#if [ ! -d "${ACCUMULO_HOME}" ]; then
# echo "Warning: $ACCUMULO_HOME does not exist! Accumulo imports will fail."
# echo 'Please set $ACCUMULO_HOME to the root of your Accumulo installation.'
#fi
# Add HCatalog to dependency list
#if [ -e "${HCAT_HOME}/bin/hcat" ]; then
# TMP_SQOOP_CLASSPATH=${SQOOP_CLASSPATH}:`${HCAT_HOME}/bin/hcat -classpath`
# if [ -z "${HIVE_CONF_DIR}" ]; then
# TMP_SQOOP_CLASSPATH=${TMP_SQOOP_CLASSPATH}:${HIVE_CONF_DIR}
# fi
# SQOOP_CLASSPATH=${TMP_SQOOP_CLASSPATH}
#fi
# Add Accumulo to dependency list
#if [ -e "$ACCUMULO_HOME/bin/accumulo" ]; then
# for jn in `$ACCUMULO_HOME/bin/accumulo classpath | grep file:.*accumulo.*jar | cut -d':' -f2`; do
# SQOOP_CLASSPATH=$SQOOP_CLASSPATH:$jn
# done
# for jn in `$ACCUMULO_HOME/bin/accumulo classpath | grep file:.*zookeeper.*jar | cut -d':' -f2`; do
# SQOOP_CLASSPATH=$SQOOP_CLASSPATH:$jn
# done
#fi
进入到sqoop的lib目录,下载mysql和postgresql的JDBC驱动,
cd /usr/local/sqoop/lib/
wget https://jdbc.postgresql.org/download/postgresql-42.2.24.jar
测试sqoop
sqoop list-tables --connect jdbc:postgresql://hadoop01:5432/jdbcstudy --driver org.postgresql.Driver --username postgres --password 201314
完成。
安装HBase
分布式数据库
版本选择:HBase2.4.0
start-all.sh
启动Hadoop,zkServer.sh start
启动各节点的 zkServer 服务
cd /usr/local
wget https://archive.apache.org/dist/hbase/2.4.0/hbase-2.4.0-bin.tar.gz
tar -zxvf hbase-2.4.0-bin.tar.gz
mv hbase-2.4.0 hbase
配置HBase
cd /usr/local/hbase/conf
vi hbase-env.sh
export JAVA_HOME=/usr/local/jdk1.8.0_301
export HBASE_PID_DIR=/usr/local/hbase
export HBASE_MANAGES_ZK=false
vi regionservers
hadoop02
hadoop03
vi backup-masters
hadoop02
mkdir /usr/local/hbase/tmp
vi hbase-site.xml
<property>
<name>hbase.cluster.distributed</name>
<value>true</value>
</property>
<!-- 设置HRegionServers共享目录 -->
<property>
<name>hbase.rootdir</name>
<value>hdfs://hadoop01:9000/hbase</value>
</property>
<!-- 指定Zookeeper集群位置 -->
<property>
<name>hbase.zookeeper.quorum</name>
<value>hadoop02:2181,hadoop03:2182</value>
</property>
<!-- 指定独立Zookeeper安装路径 -->
<property>
<name>hbase.zookeeper.property.dataDir</name>
<value>/usr/local/zookeeper</value>
</property>
<!-- 指定ZooKeeper集群端口 -->
<property>
<name>hbase.zookeeper.property.clientPort</name>
<value>2181</value>
</property>
<property>
<name>hbase.tmp.dir</name>
<value>/usr/local/hbase/tmp/</value>
</property>
<property>
<name>hbase.unsafe.stream.capability.enforce</name>
<value>false</value>
</property>
<property>
<name>dfs.datanode.max.transfer.threads</name>
<value>4096</value>
</property>
<!-- 文件异步读写配置 -->
<property>
<name>hbase.wal.provider</name>
<value>filesystem</value>
</property>
cp /usr/local/hadoop/etc/hadoop/hdfs-site.xml /usr/local/hbase/conf/
scp -r /usr/local/hbase root@hadoop02:/usr/local/
scp -r /usr/local/hbase root@hadoop03:/usr/local/
vi /etc/profile
配置环境变量:
export HBASE_HOME=/usr/local/hbase
export PATH=$PATH:$HBASE_HOME/bin
export CALSSPATH=.:${HBASE_HOME}/lib:$CLASSPATH
开启HBase:
start-hbase.sh
Hadoop01:
Hadoop02:
Hadoop03:
查看服务所占端口netstat -lntp
查看其在 HDFS 中的存储信息
hdfs dfs -ls /hbase
或
hadoop fs -ls /hbase
在Hadoop02、Hadoop03上运行 hbase shell
测试HBase
Hbase 基本命令
在hadoop02上:
list
create 'hbase_test','f1'
在hadoop03上:
list
删除表,需先禁用表后,再执行对表的删除操作
disable ‘hbase_test’
drop ‘hbase_test’
浏览器查看服务的web,输入地址:http://hadoop01:16010
完成。
依次创建以下四张表:
create 'tsdb',{NAME => 't', VERSIONS => 1, BLOOMFILTER => 'ROW'};
create 'tsdb-uid',{NAME => 'id', BLOOMFILTER => 'ROW'},{NAME => 'name', BLOOMFILTER => 'ROW'};
create 'tsdb-tree',{NAME => 't', VERSIONS => 1, BLOOMFILTER => 'ROW'};
create 'tsdb-meta',{NAME => 'name', BLOOMFILTER => 'ROW'};
退出exit;
停止 hbase 服务:/usr/local/hbase/bin/stop-hbase.sh
安装OpenTSDB
时序数据库
下载版本:2.4.0
cd /usr/local
yum -y localinstall opentsdb-2.4.0.noarch.rpm
vi /usr/lib/systemd/system/opentsdb.service
,添加以下内容
[Unit]
Description=OpenTSDB Service
[Service]
Type=forking
PrivateTmp=yes
ExecStart=/usr/share/opentsdb/etc/init.d/opentsdb start
ExecStop=/usr/share/opentsdb/etc/init.d/opentsdb stop
Restart=on-abort
输入systemctl status opentsdb
查看状态
vim /etc/opentsdb/opentsdb.conf
tsd.core.auto_create_metrics = true
tsd.storage.hbase.zk_quorum = hadoop02:2181,hadoop03:2182
tsd.storage.fix_duplicates = true
tsd.http.request.enable_chunked = true
tsd.http.request.max_chunk = 4096000
tsd.storage.max_tags = 16
tsd.storage.hbase.data_table = tsdb
tsd.storage.hbase.uid_table = tsdb-uid
tsd.storage.hbase.tree_table = tsdb-tree
tsd.storage.hbase.meta_table = tsdb-meta
启动OpenTSDB
cd /usr/share/opentsdb
bash bin/tsdb tsd &
在Hadoop02、Hadoop03上做同样配置,然后启动:
打开浏览器,登陆地址:http://hadoop01:4242
完成。
安装Kafka
安装scala
cd /usr/local
wget https://downloads.lightbend.com/scala/2.13.7/scala-2.13.7.tgz
mv scala-2.13.7 scala
配置环境变量
echo "export SCALA_HOME=/usr/local/scala" >> /etc/profile
echo -e 'export PATH=$PATH:$SCALA_HOME/bin'>> /etc/profile
source /etc/profile
测试:scala -version
将scala发送给其它节点,同时更改环境变量
scp -r /usr/local/scala root@hadoop02:/usr/local/
scp -r /usr/local/scala root@hadoop03:/usr/local/
echo "export SCALA_HOME=/usr/local/scala" >> /etc/profile
echo -e 'export PATH=$PATH:$SCALA_HOME/bin'>> /etc/profile
source /etc/profile
完成。
安装kafka
cd /usr/local
wget https://archive.apache.org/dist/kafka/2.7.0/kafka_2.13-2.7.0.tgz
tar -zxvf kafka_2.13-2.7.0
mv kafka_2.13-2.7.0 kafka
cd /usr/local/kafka/
mkdir data
cd config/
vim server.properties
修改为broker.id
、zookeeper.connect
、log.dirs
vim zookeeper.properties
,将dataDir=/temp/zookeeper
修改为dataDir=../zookeeper
配置环境变量:
echo "export KAFKA_HOME=/usr/local/kafka" >> /etc/profile
echo -e 'export PATH=$PATH:$KAFKA_HOME/bin'>> /etc/profile
source /etc/profile
将kafka发送给其它节点,同时更改环境变量
scp -r /usr/local/kafka root@hadoop02:/usr/local/
scp -r /usr/local/kafka root@hadoop03:/usr/local/
echo "export KAFKA_HOME=/usr/local/kafka" >> /etc/profile
echo -e 'export PATH=$PATH:$KAFKA_HOME/bin'>> /etc/profile
source /etc/profile
分别在hadoop102和hadoop103上修改配置文件server.properties中的broker.id=1、broker.id=2
cd /usr/local/kafka/config
vim server.properties
测试
启动集群,先启动zookeeper:
kafka-server-start.sh -daemon $KAFKA_HOME/config/zookeeper.properties
kafka-server-start.sh -daemon $KAFKA_HOME/config/server.properties
创建topic:
kafka-topics.sh --create --bootstrap-server hadoop01:9092 --topic first --partitions 2 --replication-factor 2
查看当前服务器中的所有topic:
kafka-topics.sh --list --bootstrap-server hadoop01:9092
删除topic
kafka-topics.sh --bootstrap-server hadoop01:9092 --delete --topic first
发送消息
kafka-console-producer.sh --broker-list hadoop01:9092 --topic first
消费消息
kafka-console-consumer.sh --bootstrap-server hadoop01:9092 --from-beginning --topic first
kafka-topics.sh --bootstrap-server hadoop01:9092 --describe --topic first
kafka-topics.sh --bootstrap-server hadoop01:9092 --delete --topic first
修改zookeeper-server-stop.sh
vim /usr/local/kafka/bin/kafka-server-stop.sh
kafka-server-stop.sh //关闭kafka
错误记录
使用Presto连接mysql
在mysql中新建jdbcstudy数据库,添加数据
配置mysql的数据源:
cd /usr/local/presto/etc/catalog
vi mysql.properties
connector.name=postgresql
connection-url=jdbc:mysql://hadoop01:3306/jdbcstudy
connection-user=mysql
connection-password=201314
scp mysql.properties root@hadoop02:/usr/local/presto/etc/catalog
scp mysql.properties root@hadoop03:/usr/local/presto/etc/catalog
启动测试:
cd /usr/local/presto/bin
./launcher start //启动
./presto --server hadoop01:8880 --catalog mysql --schema jdbcstudy
show schemas;
show tables from public;
错误原因在于jdbc版本太低,cd /usr/local/presto/plugin/mysql
进入presto存放mysql jdbc的文件夹可以看到jdbc的版本为5.1.44。这就导致在配置完mysql的数据源后,presto无法正常启动,哪怕更换jdbc版本,或是更换mysql版本,都无法解决这个问题。所以本人在前面安装完presto后只连接了hive和postgresql。具体解决办法,未知。。。
使用sqoop连接本机(127.0.0.1)的mysql和postgresql
存在两个问题:
1.正确的jdbc依赖包
2.本地数据库禁止远程登录
例1:
进入D:\PostgreSQL\12\data,修改pg_hba.conf文件和postgresql.conf文件,设置远程登录
重启postgresql。
sqoop list-tables --connect jdbc:postgresql://127.0.0.1:5432/jdbcstudy --driver org.postgresql.Driver --username postgres --password 201314
例2:
cd /usr/local/sqoop/lib
wget https://repo.maven.apache.org/maven2/mysql/mysql-connector-java/8.0.21/mysql-connector-java-8.0.21.jar
在主机上修改访问mysql权限。即让“%”任何主机都能访问mysql数据库,而不只允许localhost访问
use mysql
select host,user from user;
update user set host='%' where user ='root';
flush privileges; //使修改生效
重启mysql,测试连接
sqoop list-tables --connect jdbc:mysql://127.0.0.1:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT%2B8 --driver com.mysql.cj.jdbc.Driver --username root --password 201314
。。。待解决
切换hive的默认数据库为mysql
现在mysql下创建hive数据库
编辑hive-site.xml
vi /usr/local/hive/conf/hive-site.xml
<!--
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://192.168.115.130:3306/hive?createDatabaseIfNotExist=true&characterEncoding=UTF-8</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.cj.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>201314</value>
</property>
-->
切换hive的默认数据库为mysql,下载mysql的jdbc驱动包到hive的lib目录下
cd /usr/local/hive/lib
wget https://repo.maven.apache.org/maven2/mysql/mysql-connector-java/8.0.21/mysql-connector-java-8.0.21.jar
./schematool -dbType postgres -initSchema //初始化数据库
./hive
show databases;
。。。同样,jdbc和driver的原因,待解决
命令汇总:
1、启动hadoop命令:
start-all.sh
stop-all.sh
2、PostgreSQL
su postgres
3、Timescale
create extension timescaledb; 创建时序数据库
4、MySQL
mysql -uroot -p
5、hive启动命令:
hive
hive --service hiveserver -p 9083 //thrift模式
6、presto
launcher start 或 launcher run
presto --server hadoop01:8880 --catalog hive --schema default //连接hive
presto --server hadoop01:8880 --catalog postgresql --schema demo //连接postgres
7、sqoop
sqoop list-tables --connect jdbc:postgresql://hadoop01:5432/jdbcstudy --driver org.postgresql.Driver --username postgres --password 201314
8、hbase 命令
start-hbase.sh
命令行:hbase shell
9、zookeeper
zkServer.sh start
10、OpenTSDB
cd /usr/share/opentsdb
bash bin/tsdb tsd &
11、scala
scala
12、kafka
kafka-server-start.sh -daemon $KAFKA_HOME/config/zookeeper.properties
kafka-server-start.sh -daemon $KAFKA_HOME/config/server.properties
kafka-server-stop.sh
13、flink
/usr/local/hadoop/flink-1.10.3/bin/start-cluster.sh