Apache Presto 简明教程

Apache Presto - MySQL Connector

MySQL 连接器用于查询外部 MySQL 数据库。

The MySQL connector is used to query an external MySQL database.

Prerequisites

MySQL 服务器安装。

MySQL server installation.

Configuration Settings

希望您已经在您的机器上安装了 MySQL 服务器。要使 Presto 服务器启用 MySQL 属性，您必须在 “etc/catalog” 目录中创建一个文件 “mysql.properties” 。发出以下命令以创建一个 mysql.properties 文件。

Hopefully you have installed mysql server on your machine. To enable mysql properties on Presto server, you must create a file “mysql.properties” in “etc/catalog” directory. Issue the following command to create a mysql.properties file.

$ cd etc
$ cd catalog
$ vi mysql.properties

connector.name = mysql
connection-url = jdbc:mysql://localhost:3306
connection-user = root
connection-password = pwd

保存文件并退出终端。在上述文件中，您必须在 connection-password 字段中输入 MySQL 密码。

Save the file and quit the terminal. In the above file, you must enter your mysql password in connection-password field.

Create Database in MySQL Server

打开 MySQL 服务器并使用以下命令创建一个数据库。

Open MySQL server and create a database using the following command.

create database tutorials

现在您已经在服务器中创建了“tutorials”数据库。要启用数据库类型，请在查询窗口中使用命令“use tutorials”。

Now you have created “tutorials” database in the server. To enable database type, use the command “use tutorials” in the query window.

Create Table

我们来在“tutorials”数据库中创建一个简单的表。

Let’s create a simple table on “tutorials” database.

create table author(auth_id int not null, auth_name varchar(50),topic varchar(100))

Insert Table

创建表后，使用以下查询插入三条记录。

After creating a table, insert three records using the following query.

insert into author values(1,'Doug Cutting','Hadoop')
insert into author values(2,’James Gosling','java')
insert into author values(3,'Dennis Ritchie’,'C')

Select Records

要检索所有记录，请键入以下查询。

To retrieve all the records, type the following query.

Query

select * from author

Result

auth_id    auth_name      topic
1        Doug Cutting     Hadoop
2        James Gosling    java
3        Dennis Ritchie     C

截至目前，您已使用 MySQL 服务器查询了数据。我们来将 Mysql 存储插件连接到 Presto 服务器。

As of now, you have queried data using MySQL server. Let’s connect Mysql storage plugin to Presto server.

Connect Presto CLI

键入以下命令以在 Presto CLI 上连接 MySql 插件。

Type the following command to connect MySql plugin on Presto CLI.

./presto --server localhost:8080 --catalog mysql --schema tutorials

您将收到以下应答。

You will receive the following response.

presto:tutorials>

此处 “tutorials” 指的是 mysql 服务器中的架构。

Here “tutorials” refers to schema in mysql server.

List Schemas

要列出 mysql 中的所有架构，请在 Presto 服务器中键入以下查询。

To list out all the schemas in mysql, type the following query in Presto server.

Query

presto:tutorials> show schemas from mysql;

Result

      Schema
--------------------
 information_schema
 performance_schema
 sys
 tutorials

从此结果中，我们可以得出结论，前三个架构是预定义的，最后一个是您自己创建的。

From this result, we can conclude the first three schemas as predefined and the last one as created by yourself.

List Tables from Schema

以下查询列出教程架构中的所有表。

Following query lists out all the tables in tutorials schema.

Query

presto:tutorials> show tables from mysql.tutorials;

Result

  Table
--------
 author

我们只在此架构中创建了一个表。如果您创建了多个表，它将列出所有表。

We have created only one table in this schema. If you have created multiple tables, it will list out all the tables.

Describe Table

要描述表字段，请键入以下查询。

To describe the table fields, type the following query.

Query

presto:tutorials> describe mysql.tutorials.author;

Result

  Column   |     Type     | Comment
-----------+--------------+---------
 auth_id   | integer      |
 auth_name | varchar(50)  |
 topic     | varchar(100) |

Show Columns from Table

Query

presto:tutorials> show columns from mysql.tutorials.author;

Result

 Column    |     Type     | Comment
-----------+--------------+---------
 auth_id   | integer      |
 auth_name | varchar(50)  |
 topic     | varchar(100) |

Access Table Records

要从 mysql 表获取所有记录，请发出以下查询。

To fetch all the records from mysql table, issue the following query.

Query

presto:tutorials> select * from mysql.tutorials.author;

Result

auth_id  |   auth_name    | topic
---------+----------------+--------
       1 | Doug Cutting   | Hadoop
       2 | James Gosling  | java
       3 | Dennis Ritchie | C

从此结果中，您可以在 Presto 中检索 mysql 服务器记录。

From this result, you can retrieve mysql server records in Presto.

Create Table Using as Command

Mysql 连接器不支持创建表查询，但可以使用 as 命令创建表。

Mysql connector doesn’t support create table query but you can create a table using as command.

Query

presto:tutorials> create table mysql.tutorials.sample as
select * from mysql.tutorials.author;

Result

CREATE TABLE: 3 rows

您无法直接插入行，因为此连接器有一些限制。它不支持以下查询：

You can’t insert rows directly because this connector has some limitations. It cannot support the following queries −

create
insert
update
delete
drop

要查看新创建表中的记录，请键入以下查询。

To view the records in the newly created table, type the following query.

Query

presto:tutorials> select * from mysql.tutorials.sample;

Result

auth_id  |   auth_name    | topic
---------+----------------+--------
       1 | Doug Cutting   | Hadoop
       2 | James Gosling  | java
       3 | Dennis Ritchie | C