Presto data connection

Presto is a distributed SQL query engine, which is designed for high-speed, real-time data analysis. It supports standard ANSI SQL, including complex query, aggregation, join and window functions. The operating model of Presto is fundamentally different from Hive or MapReduce. Hive translates the query into a multi-stage MapReduce task, which runs one after another. Each task reads the input data from the disk and outputs the intermediate results to the disk. However, the Presto engine does not use MapReduce. It uses a customized query and execution engine and response operators to support SQL syntax. Except for the improved scheduling algorithm, all data processing is performed in memory. Different processing ends form a processing pipeline through the network. This will avoid unnecessary disk reads and writes and additional delays. This pipelined execution model will run multiple data processing sections at the same time, and once data is available, data will be passed from one processing section to the next. This approach will greatly reduce the end-to-end response time of various queries. In this chapter, we will introduce how to connect to the Presto database in FineBI.

The document describes Prestodb, which does not support Prestosql data connection (now renamed trino).

2.1 Configuration information

2.2 Connection methods

There are two ways to connect:

• Method 1: After installing the driver isolation plugin, connect to the database.

  For details on downloading and installing the driver isolation plugin, please refer to: Driver isolation plugin.

• Method 2: Do not use the driver isolation plugin, directly connect to the database.

  See below for connection method. But in this way, there may be conflicts between JAR packages and cause fail to connect. It is recommended that users use the first method.

2.3 Place the driver

Put the database driver in the corresponding folder.

Download the Presto driver, and place the driver package under %FineBI%\webapps\webroot\WEB-INF\lib, and restart FineBI.

2.4 Create a data connection

Log into the data decision system, select "Manage>Data Connection>Data Connection Management", click "New Data Connection", and select "Presto" under "All", as shown in the figure below:

Enter the data connection configuration page, and enter the corresponding information of the database according to the configuration information in section 2.1. You can choose to fill in the connection pool attribute information (for the introduction of connection pool attribute, please refer to: Configure Data Connection), as shown in the following figure:

2.5 Test connection

Click "Test Connection", or click "Click to Connect Database" in the "Pattern", if the test connection is successful, it means that the database is successfully connected, as shown in the following figure:

After the test connection is successful, you can select the pattern, and select the corresponding pattern according to your needs. If you do not select, the first pattern will be the default. As shown below:

2.6 Preview effect

After the pattern is selected, click "Save" in the upper right corner, and the data connection is added successfully. As shown below:

• The presto database does not support Boolean null values, it will display false when read into FineBI.

• The presto database does not support the recognition of cast(xxx as decimal) syntax. If you use the cast(xxx as decimal) syntax to convert a list of text type fields to numeric types, the result is still text type.