presto中sql

1、presto介绍以及配置文件

之前调研过presto的安装，具体的可以去我主页查看部署

在这篇文档中，我会将所有公司IP隐藏，希望各位见谅

1、配置环境

1、presto的启动方式以及客户端使用

presto的启动靠launcher文件启动

presto客户端是靠后缀为executable.jar的文件启动，主要实现sql查询

#presto的启动  前台启动，输出日志
./launcher run 

#presto后台启动，开启守护进程
./launche start

#presto 后台停止
./launcher stop

#presto 重新启动
./launcher restart

#查看presto的开启状态
./launcher status


#客户端启动命令
./presto-cli-0.281-executable.jar --server 127.0.0.1:9980

2、导入依赖

在pom文件中导入以下依赖，后正常执行查询

        <!-- 多数据源驱动 -->
        <!-- https://mvnrepository.com/artifact/com.facebook.presto/presto-jdbc -->
        <dependency>
            <groupId>com.facebook.presto</groupId>
            <artifactId>presto-jdbc</artifactId>
            <version>0.278</version>
        </dependency>

<!--        alibaba json工具类-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.66</version>
        </dependency>

3、完成测试环境

创建一个测试类，将下列代码放入，需要调整一下presto的端口和sql语句即可完成

    @Test
    public void test01() throws SQLException {
        Connection connection = null;
//        其中system是指默认的catalog内所有的源数据连接，runtime是数据源中默认的schema
        String jdbcUrl = "jdbc:presto://IP:端口号/system/runtime";
        try {
            connection = DriverManager.getConnection(jdbcUrl,"root",null);
            Statement stmt = connection.createStatement()  
            String sql = "select * from mysql.test.student";
            ResultSet resultSet = stmt.executeQuery(sql);

            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            JSONArray array = new JSONArray();

            while (resultSet.next()){
                JSONObject jsonObj = new JSONObject();
                // 遍历每一列
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    String value = StringUtils.isBlank(resultSet.getString(columnName)) ? "" : resultSet.getString(columnName);
                    jsonObj.put(columnName, value);
                }
                array.add(jsonObj);
            }
            for (Object aa : array) {
                System.out.println(aa);
            }
            resultSet.close();
            stmt.close();

        }catch (Exception e){
            e.printStackTrace();
        }

    }

4、简单查询语句

#查看mysql数据库下有多少数据库
show schemas from mysql
#查看MySQL中test数据库有多少张表
show tables from mysql.test

2、presto基本配置

1、presto配置文件

在etc目录中创建下列文件

1、config.properties

#是否为Coordinator调度节点
coordinator=true
#主节点是否也作为work。在coordinator里做worker的工作会影响查询性能。
node-scheduler.include-coordinator=true
#指定HTTP端口。Presto使用HTTP来与外部和内部进行交流
http-server.http.port=端口号
#查询能用到的最大总内存
query.max-memory=2GB
#查询能用到的最大单结点内存
query.max-memory-per-node=1GB
query.max-total-memory-per-node=2GB
#Presto使用Discovery服务去找到集群中的所有结点。
discovery-server.enabled=true
#Coordinator节点的域名或者IP，每个Presto实例在启动时都会在Discovery服务里注册。
discovery.uri=http://IP:端口号

2、jvm.properties

-server
-Xmx16G
-XX:+UseG1GC
-XX:G1HeapRegionSize=32M
-XX:+UseGCOverheadLimit
-XX:+ExplicitGCInvokesConcurrent
-XX:+HeapDumpOnOutOfMemoryError
-XX:+ExitOnOutOfMemoryError

3、log.properties

#日志级别分别为INFO、DEBUG、ERROR三种级别
com.facebook.presto=INFO

4、node.properties

#自定义环境名字，Presto集群结点环境名字必须一致。
node.environment=mediaforce
#每个结点的唯一标识，重启或升级Presto都必须还保持原来的标识。
node.id=5
#数据目录，Presto用它来保存log和其他数据
node.data-dir=日志地址/data

2、catalog配置信息

1、MySQL配置信息

connector.name=mysql
connection-url=jdbc:mysql://IP:端口号?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
connection-user=账号
connection-password=密码

2、Oracle配置信息

connection-url=jdbc:oracle:thin:账号/密码@IP:端口号/orcl

connector.name=oracle
connection-url=jdbc:oracle:thin:账号/密码@IP:端口号/orcl

3、postgresql配置信息

该数据库需要制定数据源，如果要适配多数据源的话，需要重复配置，期间要避开文件名重复

connector.name=postgresql
connection-url=jdbc:postgresql://IP:端口号/库名
connection-user=账号
connection-password=密码

4、SQL server配置信息

connector.name=sqlserver
connection-url=jdbc:sqlserver://IP:端口号;DatabaseName=hyh
connection-user=账号
connection-password=密码

5、mongodb配置信息

connector.name=mongodb
mongodb.seeds=IP:端口号
mongodb.credentials=账号:密码@admin

6、kafka配置信息

connector.name=kafka
kafka.table-names=Kafka2.6
kafka.nodes=IP:端口号
kafka.hide-internal-columns=false

7、elasticsearch配置信息

connector.name=elasticsearch
elasticsearch.host=IP
elasticsearch.port=端口
elasticsearch.default-schema-name=default

3、通用配置信息

在配置catalog.porperties的过程中可以选择性的加入以下参数

#这个配置主要是数据库不区分大小写，例如Oracle中查询表名就需要大小写，presto在查询的时候会全部转换成小写
case-insensitive-name-matching=true

4、查询语句

配置完文件后，presto查询的语句是

select * from 文件名.数据库.表名

上述属于通用sql语句，但有些数据库就不能使用上面通用的sql

例如：postgresql文件 （该文件在配置文件时，就已经制定了数据库）

select * from 文件名.表名

#MySQL查询语句
select * from mysql.test.student

#Oracle查询语句
select * from oracle.ZMLNAME.t1

#postgresql查询语句
select * from postgresql01.public.two

#sqlserver查询语句
select * from sqlserver.dbo.stu_info;

#mongodb查询语句
select * from mongodb.lxjtest1.student;

#kafaka查询语句  该语句为显示表语句
show tables from kafka.kafka2


#elasticsearch查询语句  
select * from es.information_schema.columns;

3、联表查询

1、MySQL和Oracle进行连表查询

select a.n1,b.name from mysql.test.student as b join oracle.zmlname.t1 as a on b.id = a.n1

select a.n1,b.name from mysql.test.student as b join oracle.zmlname.t1 as a on b.SCORE = a.v1 where b.score = '7'

#联表查询带分页
select * from (select ROW_NUMBER() over() as row, * from (select a.n1,b.name from mysql.test.student as b join oracle.zmlname.t1 as a on b.id = a.n1)) T where T.row between 1 and 3

2、MySQL和postgresql进行连表查询

select a.id,a.age,b.name from mysql.test.student as b join postgresql.public.test02 as a on b.id = a.id where b.id = 3

select a.id,a.age,b.name from mysql.test.student as b join postgresql.public.test02 as a on b.SCORE = a.other where b.score = '7'

#联表查询带分页
select * from (select ROW_NUMBER() over() as row, * from (select a.id,a.age,a.other,b.name from mysql.test.student as b join postgresql.public.test02 as a on b.id = a.id where b.id = 4)) T where T.row between 1 and 3

4、高级查询

1、in

以查询MySQL数据库为例，查询id中包含3、8的数据

select * from mysql.test.student WHERE id in (3,8)

2、format_detetime 转化时间

以MySQL查询为例，set_time是数据库中存储的时间

select format_datetime(set_time,'yyyy-MM-dd HH-mm-ss') as set_time from mysql.sys.sys_config

3、limit 分页查询

between 和 and 分页可以这个公式 between ：( currentPage- 1) * pageSize+1 ，and ： (currentPage * pageSize)

presto中的数据量很大都是BT或者PT级的 分页的时候最好加下分区条件

#旧版    测试的时候用的旧版测试，可以运行
select * from (select ROW_NUMBER() over(order by id) as row, * from mysql.test.student) T where T.row between 1 and 3


#新版    受版本影响，offset方法不能使用,如果要使用需要更新presto版本
select * from mysql.test.student order by id offset 1 limit 3

4、排序

以查询Oracle数据库为例

#升序
select n1,v1,n2,v2 from oracle.ZMLNAME.t1 order by n1 asc 

#降序
select n1,v1,n2,v2 from oracle.ZMLNAME.t1 order by n1 desc