Presto instructions

1, Presto introduction

1.1 official document address of Presto

https://prestodb.io/docs/0.255/index.html
 Note: This is Presto-0.255 Version official document. If you want to refer to other versions, replace "255" with the corresponding version number.

1.2 schematic diagram of Presto architecture

1.2.1 Presto adopts a typical master slave model:

1. The coordinator(master) is responsible for meta management, worker management, query parsing and scheduling;
2. worker is responsible for calculation, reading and writing;
3. The discovery server is usually embedded in the coordinator node and can also be deployed separately for node heartbeat. In the following, the default discovery and coordinator share a machine.

1.2.3 data model

Presto adopts three table structures:

1. catalog corresponds to a certain type of data source, such as hive data or mysql data;
2. schema corresponds to the database in mysql;
3. Table corresponds to the table in mysql.

1.3 Presto installation process

1.3.1 install Presto's Coordinator (dispatcher)

1. Put the installation package into the specified file directory, as shown in the following example: host: 127.0.4.2, path: / home/presto/

   # Unzip the installation package
   tar -zxf presto-server-0.255.tar.gz
   # Rename it as coordinator
   mv presto-server-0.255 presto-coordinator
   # Enter Presto Coordinator
   cd presto-coordinator
   # Create data and etc folders
   mkdir data									# Log file storage path
   mkdir etc										# Node information configuration and data source configuration file storage path
   

   Finally, under the / home / Presto / Presto coordinator path, the folder list is as follows

   bin											# Command script folder
   data										# Log file storage path after service startup
   etc											# The folder that is the profile of the presto service node
   lib											# Repository required by presto service
   plugin									# Folder of plugins supported by presto service
   

2. Add configuration file (/ home / Presto / Presto Coordinator / etc directory)

   config.properties

   # Create node configuration information
   touch config.properties
   # Edit profile
   vi config.properties
   
   # The configuration file is as follows
   coordinator=true																									# Indicates that the node is the coordinator
   node-scheduler.include-coordinator=false
   http-server.http.port=8090
   query.max-memory=50GB
   query.max-memory-per-node=1GB
   query.max-total-memory-per-node=2GB
   discovery-server.enabled=true
   discovery.uri=http://127.0.4.2:8090 																#  After presto is started, you can access the web path
   

   jvm.config

   # Create jvm memory configuration information
   touch jvm.properties
   # Edit profile
   vi jvm.properties
   
   # The configuration file is as follows
   -server
   -Xmx8G
   -XX:+UseG1GC
   -XX:G1HeapRegionSize=32M
   -XX:+UseGCOverheadLimit
   -XX:+ExplicitGCInvokesConcurrent
   -XX:+HeapDumpOnOutOfMemoryError
   -XX:+ExitOnOutOfMemoryError
   

   log.properties

   # Create log profile
   touch log.properties
   # Edit profile
   vi log.properties
   
   # The configuration file is as follows
   com.facebook.presto=INFO
   

   node.properties

   # Create node information profile
   touch node.properties
   # Edit profile
   vi node.properties
   
   # The configuration file is as follows
   node.environment=presto_cluster																					# The name of the same Presto cluster must be consistent
   node.id=presto_coordinator																							# The node.id of each node cannot be the same
   node.data-dir=/home/presto/presto-coordinator/data									# Step 1: the path of the manually created data folder
   

3. Data source configuration

   Create a new catalog folder in the / home / Presto / Presto Coordinator / etc directory and enter the folder

   mkdir catalog
   cd catalog
   

   (1) jmx configuration (each node needs to be configured according to official requirements)

   # Create jmx profile
   touch jmx.properties
   # Edit profile
   vi jmx.properties
   
   # Type the following configuration information in jmx.properties
   connector.name=jmx
   

   (2) MySQL type data source

   # Create data source profile
   touch mysql_1.properties
   # Edit profile
   vi mysql_1.properties
   
   # Then type the following configuration information in mysql.properties
   connector.name=mysql																									# Fixed, the plug-in will be found according to this name
   connection-url=jdbc:mysql://127.0.4.2:8036 														#  MySQL URL
   connection-user=root																									# MySQL users
   connection-password=xxxxxx																						# MySQL password
   

   (3) Data source of Elasticsearch type

   # Create data source profile
   touch elasticsearch.properties
   # Edit profile
   vi elasticsearch.properties
   
   # Then type the following configuration information in elasticsearch.properties
   connector.name=elasticsearch																						# Fixed, the plug-in will be found according to this name
   elasticsearch.host=127.0.4.2																						# Host IP used by ES cluster for access
   elasticsearch.port=9200																									# Host port for ES cluster access
   elasticsearch.default-schema-name=default							# The ES cluster itself does not have a database, but Presto's SQL syntax is similar to that of MySQL for convenience (example of correspondence: MySQL data source. Database. Table = = > es data source. Default. Index name). Default is configured as the "database" name by default, which can be customized.
   

   For other data source configurations, please refer to the official documentation.

   [supplement] for naming the name of the data source configuration file, it is recommended to use, such as mysql_ The symbolic field is in the format of. properties, in which the symbolic field cannot be dotted (.), in short, mysql_ After presto is successfully started, the symbolic field will become the name of the catalog.

4. start-up

   # Execute the launcher under the bin directory
   # start-up
   ./bin/launcher start
   # stop it
   ./bin/launcher stop
   # restart
   ./bin/launcher restart
   

5. Description of all folders after successful startup

   /home/presto/presto-coordinator     									# presto installation package folder
   /home/presto/presto-coordinator/bin									 	# Executable command folder
   /home/presto/presto-coordinator/lib										# jar required by presto service
   /home/presto/presto-coordinator/plugin								# presto supported data source plug-ins
   /home/presto/presto-coordinator/data									# The manually created data folder is used in the node.properties file
   /home/presto/presto-coordinator/data/var							# Automatically generated after the parent data path is configured in the node.properties configuration file
   /home/presto/presto-coordinator/data/var/log					# The log folder of presto is automatically generated after the data path is configured in the node.properties configuration file
   /home/presto/presto-coordinator/etc									  # Manually created etc folder, service configuration and data source configuration
   /home/presto/presto-coordinator/etc/catalog						# Manually create a catalog folder to store the folder configured by the data source
   /home/presto/presto-coordinator/etc/config.properties	# Configuration information of presto node (as coordinator, memory configuration, discovery node, etc.)
   /home/presto/presto-coordinator/etc/jvm.config				# jvm configuration
   /home/presto/presto-coordinator/etc/log.properties		# Log configuration
   /home/presto/presto-coordinator/etc/node.properties		# Node information configuration (cluster name, node Id, data path configuration mentioned above)
   

1.3.2 install Presto's worker

1. Distribute the entire directory of the installed Presto coordinator to other servers

   # Distribute the entire Presto coordinator folder to the specified directory of other servers
   # Example: send to 127.0.4.3, 127.0.4.4, 127.0.4.5
   scp -r /home/presto/presto-coordinator root@127.0.4.3:/home/presto
   scp -r /home/presto/presto-coordinator root@127.0.4.4:/home/presto
   scp -r /home/presto/presto-coordinator root@127.0.4.5:/home/presto
   

   [supplement] the first time you execute the scp command, you will be prompted whether to establish a connection with another server. Enter yes, then enter the password of the server to be distributed, and finally wait for the distribution to be completed.

2. Modification content

   After the first step of distribution, log in to the distributed servers in turn. Here, take 127.0.4.3 as an example, and do the following:

   (1) Modify folder name

   # Enter presto folder
   cd /home/presto
   # Modify the folder name where the Presto service is located, and change the Presto coordinator to presto-worker-01 for subsequent developers to distinguish
   mv presto-coordinator presto-worker-01
   

   (2) Modify profile

   # Enter the etc directory in the folder after modifying the file name
   cd /home/presto/presto-worker-01/etc
   
   # 1. Modify config.properties and enter the following configuration:
   coordinator=false																									# Indicates that the node is not the coordinator (dispatcher)
   http-server.http.port=8090
   query.max-memory=50GB
   query.max-memory-per-node=1GB
   query.max-total-memory-per-node=2GB
   discovery.uri=http://127.0.4.2:8090 																#  This should be consistent with the Coordinator node
   
   
   # 2. Modify node.properties
   node.environment=presto_cluster																		# The name of the same Presto cluster must be consistent
   node.id=presto_worker_node_01																			# The node.id of each node cannot be the same
   node.data-dir=/home/presto/presto-worker-01/data							# The data directory path of the current presto service. Since all files of the current service are distributed from the presto coordinator at the beginning, some files generated when the presto coordinator was started may exist in the corresponding data directory. If so, please delete all files in the data directory when deploying the worker node.
   
   

3. start-up

   Consistent with the Presto coordinator node, execute the launcher under the bin directory

   ./bin/launcher start
   

4. Referring to the above steps, operate the Presto coordinator folder distributed to other servers as the corresponding new worker node.

be careful

(1) The data source configuration information under the catalog of each node of presto should be consistent, so when updating and configuring the data source, all nodes should be updated uniformly and consistent.

1.3.3 Presto client usage

Download Presto executable client (presto-cli-0.255-executable.jar) from the official website. Example: after downloading, put it in the same level directory as Presto Coordinator (host: 127.0.4.2 in / home/presto directory)

cd /home/presto
# Rename for later use
mv presto-cli-0.255-executable.jar presto
# Give executable permission
chmod +x presto

# Use example	
# Description: - server is followed by the access path of presto cluster
# 		 --catalog followed by the name of the existing data source (optional)
# 		 --schema followed by the database name in the data source (optional)
./presto --server 127.0.4.2:8090 --catalog mysql_1 --schema data_base
 Note: the above command specifies the data source( catalog)，So enter after entering presto SQL Mode does not support switching data sources again

After the above command is executed, you can enter the following common commands:
show catalogs;				# Show all data sources
show schemas;					# Display database (corresponding to MySql database, and "default" configured in ES configuration file as database name)
show tables;					# Display database table (ES corresponding es index)
quit;									# Exit presto SQL typing mode

Common query SQL Example:
SELECT * FROM Data source 1.Database name.Table name;					corresponding:			SELECT * FROM catalog.schema.table;
Note: each SQL Commands must be in English semicolons(;)As an end sign.

2, Spring boot project adapts Presto

2.1 pom.xml

Add presto dependency to the original Druid database connection pool and MySQL connector Java dependency

<dependency>
  <groupId>com.facebook.presto</groupId>
  <artifactId>presto-jdbc</artifactId>
  <version>0.225</version>
</dependency>

2.2 application.properties

# Presto JdbcTemplate mode configuration
spring.datasource.presto.jdbctemplate.name=presto
spring.datasource.presto.jdbctemplate.driver-class-name=com.facebook.presto.jdbc.PrestoDriver
spring.datasource.presto.jdbctemplate.url=jdbc:presto://127.0.4.2:8090
spring.datasource.presto.jdbctemplate.username=root

# Presto MyBatis mode configuration
spring.datasource.presto.mybatis.name=presto
spring.datasource.presto.mybatis.type=org.apache.tomcat.jdbc.pool.DataSource
spring.datasource.presto.mybatis.driver-class-name=com.facebook.presto.jdbc.PrestoDriver
spring.datasource.presto.mybatis.jdbc-url=jdbc:presto://127.0.4.2:8090
spring.datasource.presto.mybatis.username=root

2.3 java data source configuration class

package com.demo.wechat.datasource;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
 * @Description Presto Data source configuration
 * @Author SunJiaHao
 * @Date 2021/6/22
 */
@Configuration
@MapperScan(basePackages = {"com.demo.wechat.presto.dao"}, sqlSessionTemplateRef = "prestoJdbcSqlSessionTemplate")
public class PrestoDataSourceConfig {

    /**
     * Configuration mode I
     * Description: initializes a bean of type JdbcTemplate of Presto
     * Function: used to perform relevant operations on Presto in the form of JdbcTemplate
     */
    @Bean("prestoDataSource")
    @ConfigurationProperties("spring.datasource.presto.jdbctemplate")
    public DataSource getPrestoDataSource() {
        return new DruidDataSource();
    }

    @Autowired
    @Qualifier("prestoDataSource")
    DataSource dataSource;

    @Bean("prestoTemplate")
    public JdbcTemplate getPrestoJdbcTemplate() {
        return new JdbcTemplate(dataSource);
    }

    /**
     * Configuration mode II
     * Description: initializes the sqlSessionTemplate type bean of Presto
     * Function: used to perform relevant operations on Presto in the way of MyBatis
     */
    @Bean(name = "prestoJdbcDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.presto.mybatis")
    public DataSource getDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "prestoJdbcSqlSessionFactory")
    public SqlSessionFactory getSqlSessionFactory(@Qualifier("prestoJdbcDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/prestoMapper/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "prestoJdbcTransactionManager")
    public DataSourceTransactionManager getTransactionManager(@Qualifier("prestoJdbcDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "prestoJdbcSqlSessionTemplate")
    public SqlSessionTemplate getSqlSessionTemplate(@Qualifier("prestoJdbcSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

2.4 use examples

1. dao

   package com.demo.wechat.presto.dao;
   
   import org.apache.ibatis.annotations.Mapper;
   
   import java.util.List;
   import java.util.Map;
   
   @Mapper
   public interface DemoDao {
       List<Map<String, Object>> queryMySQLBooks();
   
       List<Map<String, Object>> queryEsBooks();
   
       List<Map<String, Object>> queryUnionEsMySQLBooks();
   
       List<Map<String, Object>> queryBooksByPage(Map<String, Object> param);
   }
   
   
   

2. xml

   <?xml version="1.0" encoding="UTF-8"?>
   <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   <mapper namespace="com.demo.wechat.presto.dao.DemoDao">
   
       <select id="queryMySQLBooks" statementType="STATEMENT" resultType="map">
           select * from mysql_demo.data_base.presto_test_book
       </select>
   
       <select id="queryEsBooks" statementType="STATEMENT" resultType="map">
           select * from elasticsearch.default.people
       </select>
   
       <select id="queryUnionEsMySQLBooks" statementType="STATEMENT" resultType="map">
           select
           t2.book_name as bookName,
           t2.book_type as bookType,
           t2.author_name as authorName,
           t1.age as authorAge,
           t2.author_gender as authorGender,
           t1.country as authorCountry,
           t2.book_price as bookPrice
           from elasticsearch.default.people t1
           join mysql_demo.data_base.presto_test_book t2
           ON t2.author_name = t1.name
           WHERE <![CDATA[t1.age < 40]]>
       </select>
   
       <select id="queryBooksByPage" statementType="STATEMENT" resultType="map">
           WITH orderByAuthorAgeAsc AS (
               SELECT
               (ROW_NUMBER() over(ORDER BY t2.author_age ASC)) AS rowNum,
               t2.book_name as bookName,
               t2.book_type as bookType,
               t2.author_name as authorName,
               t2.author_age as authorAge,
               t2.author_gender as authorGender,
               t1.country as authorCountry,
               t2.book_price as bookPrice
               FROM elasticsearch.default.people t1
               JOIN mysql_demo.data_base.presto_test_book t2
               ON t2.author_name = t1.name
           )
   
           SELECT list.*, (SELECT COUNT(0) FROM orderByAuthorAgeAsc) AS rowTotal
           FROM orderByAuthorAgeAsc list
           WHERE list.rowNum BETWEEN ${startRow} AND ${endRow}
       </select>
     
     
       <!--  Paging query example: rowNum, rowTotal The name should be strictly unchanged(Required when building paged return body)  -->
       <select id="queryByPage" statementType="STATEMENT" resultType="map">
           WITH customTableName AS (
               SELECT
                   (ROW_NUMBER() over(ORDER BY tb.order_column_name ASC)) AS rowNum,
                   tb.column_name AS columnName
               FROM catalogName.schemaName.tableName tb
           )
   
           SELECT list.*, (SELECT COUNT(0) FROM customTableName) AS rowTotal
           FROM customTableName list
           WHERE list.rowNum BETWEEN ${startRow} AND ${endRow}
       </select>
   
   </mapper>
   

3. service

   package com.demo.wechat.presto.service;
   
   import java.util.List;
   import java.util.Map;
   
   public interface DemoService {
   
       List<Map<String, Object>> queryMySQLBooks();
   
       List<Map<String, Object>> queryEsBooks();
   
       List<Map<String, Object>> queryUnionEsMySQLBooks();
   
       Object queryBooksByPage(Map<String, Object> param);
   }
   
   

4. service.impl

   package com.demo.wechat.presto.service.impl;
   
   import com.demo.wechat.presto.dao.DemoDao;
   import com.demo.wechat.presto.service.DemoService;
   import com.demo.wechat.presto.util.PrestoUtil;
   import com.demo.wechat.presto.vo.PrestoPageInfo;
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.stereotype.Service;
   
   import java.util.List;
   import java.util.Map;
   
   @Service
   public class DemoServiceImpl implements DemoService {
   
       @Autowired
       private DemoDao demoDao;
   
       @Override
       public List<Map<String, Object>> queryMySQLBooks() {
           return demoDao.queryMySQLBooks();
       }
   
       @Override
       public List<Map<String, Object>> queryEsBooks() {
           return demoDao.queryEsBooks();
       }
   
       @Override
       public List<Map<String, Object>> queryUnionEsMySQLBooks() {
           return demoDao.queryUnionEsMySQLBooks();
       }
   
       @Override
       public Object queryBooksByPage(Map<String, Object> param) {
           // 1. Query parameter conversion
           Integer pageNo = Integer.parseInt(String.valueOf(param.get("pageNo")));
           Integer pageSize = Integer.parseInt(String.valueOf(param.get("pageSize")));
           param.put("startRow", (pageNo - 1) * pageSize + 1);
           param.put("endRow", pageNo * pageSize);
           // 2. Paging query
           List<Map<String, Object>> list = demoDao.queryBooksByPage(param);
           // 3. Returnee construction
           Map<String, Integer> prestoPageParam = PrestoUtil.getPrestoPageParam(list, param);
           return new PrestoPageInfo<>(list, prestoPageParam);
       }
   }
   
   

5. controller

   package com.demo.wechat.presto.controller;
   
   import com.demo.wechat.presto.service.DemoService;
   import lombok.extern.slf4j.Slf4j;
   import org.apache.ibatis.annotations.Param;
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.beans.factory.annotation.Qualifier;
   import org.springframework.jdbc.core.JdbcTemplate;
   import org.springframework.web.bind.annotation.GetMapping;
   import org.springframework.web.bind.annotation.RequestMapping;
   import org.springframework.web.bind.annotation.RestController;
   
   import java.util.HashMap;
   import java.util.List;
   import java.util.Map;
   
   /**
    * @Description Presto Connection test
    * @Author SunJiaHao
    * @Date 2021/6/23
    */
   @Slf4j
   @RestController
   public class DemoController {
   
       /**
        * Configuration mode I test
        * Use JdbcTemplate to perform Presto related operations
        */
       @Autowired
       @Qualifier("prestoTemplate")
       private JdbcTemplate jdbcTemplate;
   
       @GetMapping(value = "/prestoMysqlQuery")
       public List<Map<String, Object>> prestoTest01() {
           log.info("======= test Presto MySQL query=======");
           String sql = "select * from mysql_demo.data_base.presto_test_book";
           List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
           return list;
       }
   
       @RequestMapping(value = "/prestoEsQuery")
       public List<Map<String, Object>> prestoTest02() {
           log.info("======= test Presto ES query========");
           String sql = "select * from elasticsearch.default.people";
           List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
           return list;
       }
   
       @GetMapping("/prestoEsUnionMysqlQuery")
       public List<Map<String, Object>> prestoTest03() {
           log.info("======= test Presto ES MySQL Federated query=======");
           String sql = "select t2.book_name as bookName, t2.book_type as bookType, t2.author_name as authorName, t1.age as authorAge, t2.author_gender as authorGender, t1.country as authorCountry, t2.book_price as bookPrice from elasticsearch.default.people t1 join mysql_demo.data_base.presto_test_book t2 ON t2.author_name = t1.name WHERE t1.age < 40";
           List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
           return list;
       }
   
   
       /**
        * Configuration mode II test
        * Use MyBatis to perform Presto related operations
        */
       @Autowired
       private DemoService demoService;
   
       @GetMapping("/prestoMyBatisMySQL")
       public List<Map<String, Object>> prestoTest04() {
           return demoService.queryMySQLBooks();
       }
   
       @GetMapping("/prestoMyBatisEs")
       public List<Map<String, Object>> prestoTest05() {
           return demoService.queryEsBooks();
       }
   
       @GetMapping("/prestoMyBatisUnionEsMySQL")
       public List<Map<String, Object>> prestoTest06() {
           return demoService.queryUnionEsMySQLBooks();
       }
   
       /**
        * Paging query example
        * @param pageNo Page number (mandatory)
        * @param pageSize Page capacity (required)
        * @return PrestoPageInfo Object (similar to the return body of the PageHelper plug-in)
        */
       @GetMapping("/prestoQueryByPage")
       public Object prestoTest07(@Param("pageNo") Integer pageNo, @Param("pageSize") Integer pageSize) {
           Map<String, Object> param = new HashMap<>();
           param.put("pageNo", pageNo);
           param.put("pageSize", pageSize);
           return demoService.queryBooksByPage(param);
       }
   
   }
   
   
   

6. vo

   In order to ensure that the structure returned by the paging query to the front end is consistent with the original (PageInfo object of PageHelper), build a new vo as follows: (refer to 5. Paging query in controller above for usage)

   package com.demo.wechat.presto.vo;
   
   import java.io.Serializable;
   import java.util.Collection;
   import java.util.List;
   import java.util.Map;
   
   /**
    * @param <T>
    */
   @SuppressWarnings({"rawtypes", "unchecked"})
   public class PrestoPageInfo<T> implements Serializable {
       private static final long serialVersionUID = 1L;
       //Current page
       private int pageNum;
       //Number of pages
       private int pageSize;
       //Number of current pages
       private int size;
       //sort
       private String orderBy;
   
       //Since startRow and endRow are not commonly used, here is a specific usage
       //You can "display a total of size data from startRow to endRow" on the page
   
       //The row number of the first element of the current page in the database
       private int startRow;
       //The row number of the last element of the current page in the database
       private int endRow;
       //Total records
       private long total;
       //PageCount 
       private int pages;
       //Result set
       private List<T> list;
   
       //first page
       private int firstPage;
       //next page before
       private int prePage;
       //next page
       private int nextPage;
       //last page
       private int lastPage;
   
       //Is it the first page
       private boolean isFirstPage = false;
       //Is it the last page
       private boolean isLastPage = false;
       //Is there a previous page
       private boolean hasPreviousPage = false;
       //Next page
       private boolean hasNextPage = false;
       //Number of navigation pages
       private int navigatePages;
       //All navigation page numbers
       private int[] navigatepageNums;
   
       public PrestoPageInfo() {
       }
   
       /**
        * Encapsulate PrestoPageInfo
        * @param list Query results (current query page dataset)
        * @param pageParam Parameters required to convert to a return body similar to the PageHelper plug-in:
        *                  pageNum:Current page number
        *                  pageSize:Current page capacity
        *                  pageTotal:PageCount 
        *                  rowTotal:Total number of rows
        *                  startRow:The starting row of the current query result page
        *                  endRow:The end row of the current query result page
        *                  navigatePages:Number of navigation pages
        */
       public PrestoPageInfo(List<T> list, Map<String, Integer> pageParam) {
           this.pageNum = pageParam.get("pageNum");
           this.pageSize = pageParam.get("pageSize");
           this.orderBy = null;
           this.pages = pageParam.get("pageTotal");
           this.list = list;
           this.size = list.size();
           this.total = pageParam.get("rowTotal");
           this.startRow = pageParam.get("startRow");
           this.endRow = pageParam.get("endRow");
           if (list instanceof Collection) {
               this.navigatePages = pageParam.get("navigatePages");
               //Calculate navigation page
               calcNavigatepageNums();
               //Calculate front and back pages, first page, last page
               calcPage();
               //Determine page boundaries
               judgePageBoudary();
           }
       }
   
       /**
        * Calculate navigation page
        */
       private void calcNavigatepageNums() {
           //When the total number of pages is less than or equal to the number of navigation pages
           if (pages <= navigatePages) {
               navigatepageNums = new int[pages];
               for (int i = 0; i < pages; i++) {
                   navigatepageNums[i] = i + 1;
               }
           } else { //When the total number of pages is greater than the number of navigation pages
               navigatepageNums = new int[navigatePages];
               int startNum = pageNum - navigatePages / 2;
               int endNum = pageNum + navigatePages / 2;
   
               if (startNum < 1) {
                   startNum = 1;
                   //(top navigatePages page)
                   for (int i = 0; i < navigatePages; i++) {
                       navigatepageNums[i] = startNum++;
                   }
               } else if (endNum > pages) {
                   endNum = pages;
                   //Last navigatePages page
                   for (int i = navigatePages - 1; i >= 0; i--) {
                       navigatepageNums[i] = endNum--;
                   }
               } else {
                   //All middle pages
                   for (int i = 0; i < navigatePages; i++) {
                       navigatepageNums[i] = startNum++;
                   }
               }
           }
       }
   
       /**
        * Calculate front and back pages, first page, last page
        */
       private void calcPage() {
           if (navigatepageNums != null && navigatepageNums.length > 0) {
               firstPage = navigatepageNums[0];
               lastPage = navigatepageNums[navigatepageNums.length - 1];
               if (pageNum > 1) {
                   prePage = pageNum - 1;
               }
               if (pageNum < pages) {
                   nextPage = pageNum + 1;
               }
           }
       }
   
       /**
        * Determine page boundary
        */
       private void judgePageBoudary() {
           isFirstPage = pageNum == 1;
           isLastPage = pageNum == pages;
           hasPreviousPage = pageNum > 1;
           hasNextPage = pageNum < pages;
       }
   
       public int getPageNum() {
           return pageNum;
       }
   
       public void setPageNum(int pageNum) {
           this.pageNum = pageNum;
       }
   
       public int getPageSize() {
           return pageSize;
       }
   
       public void setPageSize(int pageSize) {
           this.pageSize = pageSize;
       }
   
       public int getSize() {
           return size;
       }
   
       public void setSize(int size) {
           this.size = size;
       }
   
       public String getOrderBy() {
           return orderBy;
       }
   
       public void setOrderBy(String orderBy) {
           this.orderBy = orderBy;
       }
   
       public int getStartRow() {
           return startRow;
       }
   
       public void setStartRow(int startRow) {
           this.startRow = startRow;
       }
   
       public int getEndRow() {
           return endRow;
       }
   
       public void setEndRow(int endRow) {
           this.endRow = endRow;
       }
   
       public long getTotal() {
           return total;
       }
   
       public void setTotal(long total) {
           this.total = total;
       }
   
       public int getPages() {
           return pages;
       }
   
       public void setPages(int pages) {
           this.pages = pages;
       }
   
       public List<T> getList() {
           return list;
       }
   
       public void setList(List<T> list) {
           this.list = list;
       }
   
       public int getFirstPage() {
           return firstPage;
       }
   
       public void setFirstPage(int firstPage) {
           this.firstPage = firstPage;
       }
   
       public int getPrePage() {
           return prePage;
       }
   
       public void setPrePage(int prePage) {
           this.prePage = prePage;
       }
   
       public int getNextPage() {
           return nextPage;
       }
   
       public void setNextPage(int nextPage) {
           this.nextPage = nextPage;
       }
   
       public int getLastPage() {
           return lastPage;
       }
   
       public void setLastPage(int lastPage) {
           this.lastPage = lastPage;
       }
   
       public boolean isIsFirstPage() {
           return isFirstPage;
       }
   
       public void setIsFirstPage(boolean isFirstPage) {
           this.isFirstPage = isFirstPage;
       }
   
       public boolean isIsLastPage() {
           return isLastPage;
       }
   
       public void setIsLastPage(boolean isLastPage) {
           this.isLastPage = isLastPage;
       }
   
       public boolean isHasPreviousPage() {
           return hasPreviousPage;
       }
   
       public void setHasPreviousPage(boolean hasPreviousPage) {
           this.hasPreviousPage = hasPreviousPage;
       }
   
       public boolean isHasNextPage() {
           return hasNextPage;
       }
   
       public void setHasNextPage(boolean hasNextPage) {
           this.hasNextPage = hasNextPage;
       }
   
       public int getNavigatePages() {
           return navigatePages;
       }
   
       public void setNavigatePages(int navigatePages) {
           this.navigatePages = navigatePages;
       }
   
       public int[] getNavigatepageNums() {
           return navigatepageNums;
       }
   
       public void setNavigatepageNums(int[] navigatepageNums) {
           this.navigatepageNums = navigatepageNums;
       }
   
       @Override
       public String toString() {
           final StringBuffer sb = new StringBuffer("PageInfo{");
           sb.append("pageNum=").append(pageNum);
           sb.append(", pageSize=").append(pageSize);
           sb.append(", size=").append(size);
           sb.append(", startRow=").append(startRow);
           sb.append(", endRow=").append(endRow);
           sb.append(", total=").append(total);
           sb.append(", pages=").append(pages);
           sb.append(", list=").append(list);
           sb.append(", firstPage=").append(firstPage);
           sb.append(", prePage=").append(prePage);
           sb.append(", nextPage=").append(nextPage);
           sb.append(", lastPage=").append(lastPage);
           sb.append(", isFirstPage=").append(isFirstPage);
           sb.append(", isLastPage=").append(isLastPage);
           sb.append(", hasPreviousPage=").append(hasPreviousPage);
           sb.append(", hasNextPage=").append(hasNextPage);
           sb.append(", navigatePages=").append(navigatePages);
           sb.append(", navigatepageNums=");
           if (navigatepageNums == null) sb.append("null");
           else {
               sb.append('[');
               for (int i = 0; i < navigatepageNums.length; ++i)
                   sb.append(i == 0 ? "" : ", ").append(navigatepageNums[i]);
               sb.append(']');
           }
           sb.append('}');
           return sb.toString();
       }
   }
   
   

7. util

   The parameter conversion tool classes required for paging are as follows: (refer to 5. Paging query in controller above for usage)

   package com.demo.wechat.presto.util;
   
   import org.springframework.util.CollectionUtils;
   import org.springframework.util.ObjectUtils;
   import org.springframework.util.StringUtils;
   
   import java.util.HashMap;
   import java.util.List;
   import java.util.Map;
   
   /**
    * @Description Presto Tool class
    * @Author SunJiaHao
    * @Date 2021/6/30
    */
   public class PrestoUtil {
   
       /**
        * Build the parameters required to return the structure similar to the PageHelper plug-in
        * @param list  Query results (paginated according to the query results)
        * @param param Query request parameters (pageNo and PageSize are used here)
        * @return
        */
       public static Map<String, Integer> getPrestoPageParam(List<Map<String, Object>> list, Map<String, Object> param) {
           // Page number
           Integer pageNo = Integer.parseInt(String.valueOf(param.get("pageNo")));
           // Page capacity
           Integer pageSize = Integer.parseInt(String.valueOf(param.get("pageSize")));
           // Number of pages (8 by default)
           Integer navigatePages = 8;
           // PageCount 
           Integer pageTotal = 0;
           // Total records
           Integer rowTotal = 0;
           // The line number of the first element of the current page in the query result
           Integer startRow = 0;
           // The line number of the last element of the current page in the query result
           Integer endRow = 0;
           if (!CollectionUtils.isEmpty(list)) {
               if (!ObjectUtils.isEmpty(list.get(0))) {
                   if (!StringUtils.isEmpty(list.get(0).get("rowTotal"))) {
                       rowTotal = Integer.parseInt(String.valueOf(list.get(0).get("rowTotal")));
                       pageTotal = (rowTotal % pageSize) == 0 ? (rowTotal / pageSize) : ((rowTotal / pageSize) + 1);
                   }
                   if (!StringUtils.isEmpty(list.get(0).get("rowNum"))) {
                       startRow = Integer.parseInt(String.valueOf(list.get(0).get("rowNum")));
                   }
               }
               if (!ObjectUtils.isEmpty(list.get(list.size() - 1)) && list.size() > 1) { // If the number of elements in the current page is greater than 1,rowNum takes the line number of the last row of data in the current page
                   if (!StringUtils.isEmpty(list.get(list.size() - 1).get("rowNum"))) {
                       endRow = Integer.parseInt(String.valueOf(list.get(list.size() - 1).get("rowNum")));
                   }
               } else { // Otherwise, there is only one row of data, endRow=startRow
                   endRow = startRow;
               }
           }
   
           Map<String, Integer> pageParam = new HashMap<>();
           pageParam.put("pageNum", pageNo);
           pageParam.put("pageSize", pageSize);
           pageParam.put("navigatePages", navigatePages);
           pageParam.put("pageTotal", pageTotal);
           pageParam.put("rowTotal", rowTotal);
           pageParam.put("startRow", startRow);
           pageParam.put("endRow", endRow);
           return pageParam;
       }
   }
   
   

3, Links to Presto SQL related knowledge points (refer to boss)

(1) Presto SQL: time and date functions

Official documents: https://prestodb.io/docs/current/functions/datetime.html
CSDN Blog: https://blog.csdn.net/zhao_cong/article/details/116063854

(2) Presto SQL: replace GROUP_CONCAT in MySQL

# SQL example of MySQL:
SELECT a, GROUP_CONCAT(b SEPARATOR ',') FROM `table` GROUP BY a;
# SQL example of Presto:
SELECT a, ARRAY_JOIN(ARRAY_AGG(b), ',') FROM `table` GROUP BY a;

(3) Presto FAQ optimization

https://www.tqwba.com/x_d/jishu/423362.html

(4) Presto function Encyclopedia

Official documents: https://prestodb.io/docs/0.255/functions.html
 Translated Chinese documents: https://blog.csdn.net/sinat_17697111/article/details/89101124?utm_medium=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-1.base&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-1.base

(5) Presto performance optimization

https://blog.csdn.net/qq_27657429/article/details/79928519
https://www.jianshu.com/p/f435ce79c966

(6) Presto query execution process and push down analysis under index conditions

https://cloud.tencent.com/developer/article/1630727