Let’s say you have an application developed in Node.js (or any other platform). This application connects to a MongoDB database (NoSQL) to store ratings on books (number of stars given and a comment). Let’s also say that you have another application developed in Java (or Python, C#, TypeScript… anything). This application connects to a MariaDB database (SQL, relational) to manage a catalog of books (title, year of publishing, number of pages).
假设你有一个使用Node.js（或其他平台）开发的应用程序。该应用程序连接到一个MongoDB数据库（NoSQL）以存储图书评分（星级和评论）。再假设你还有另一个使用Java（或Python、C#、TypeScript等）开发的应用程序。该应用程序连接到一个MariaDB数据库（SQL，关系型）以管理图书目录（标题、出版年份、页数）。

You are asked to create a report that shows the title and rating information for each book. Notice that the MongoDB database doesn’t contain the title of the books and the relational database doesn’t contain the ratings. We need to mix data created by a NoSQL application with data created by a SQL application.
你被要求创建一个报告，显示每本书的标题和评分信息。请注意，MongoDB数据库不包含书籍的标题，而关系数据库不包含评分信息。我们需要将由NoSQL应用程序创建的数据与由SQL应用程序创建的数据混合在一起。

A common approach to this is to query both databases independently (using different data sources) and process the data to match by, for example, ISBN (the id of a book) and put the combined information in a new object. This needs to be done in a programming language like Java, TypeScript, C#, Python, or any other imperative programming language that is able to connect to both databases.
一种常见的方法是独立查询两个数据库（使用不同的数据源），并通过处理数据来进行匹配，例如使用ISBN（书籍的ID），然后将合并后的信息放入一个新的对象中。这需要使用像Java、TypeScript、C#、Python或其他能够连接到两个数据库的命令式编程语言来完成。

A polyglot application 一个多语种应用程序

This approach works. However, joining data is a job for a database. They are built for this kind of data operations. Also, with this approach, the SQL application is no longer a SQL-only application, it becomes a database polyglot and this increases complexity making it harder to maintain.
这种方法是有效的。然而，数据的连接是数据库的工作。它们专门用于这种类型的数据操作。此外，采用这种方法后，SQL应用程序不再是仅限于SQL的应用程序，它变成了一个多语言数据库，这增加了复杂性，使得维护变得更加困难。

With a database proxy like MaxScale, you can join this data at the database level using the best language for data—SQL. Your SQL application doesn’t need to become a polyglot.
通过像MaxScale这样的数据库代理，您可以使用最适合数据的语言SQL在数据库级别上连接这些数据。您的SQL应用程序不需要成为多语言应用。

Although this requires an additional element in the infrastructure, you also gain all the functionality that a database proxy has to offer. Things such as automatic failover, transparent data masking, topology isolation, caches, security filters, and more.
虽然这需要基础设施中的一个额外元素，但您也可以获得数据库代理所提供的所有功能。诸如自动故障转移、透明数据屏蔽、拓扑隔离、缓存、安全过滤器等等。

MaxScale is a powerful intelligent database proxy that understands both SQL and NoSQL. It also understands Kafka (for CDC or data ingestion) but that’s a topic for another occasion. In short, with MaxScale you can connect your NoSQL application to a fully ACID-compliant relational database and store the data right there next to tables that other SQL applications use.
MaxScale是一个强大的智能数据库代理，它既了解SQL又了解NoSQL。它还了解Kafka（用于CDC或数据摄取），但这是另一个话题。简而言之，通过MaxScale，您可以将NoSQL应用程序连接到完全符合ACID的关系数据库，并将数据存储在其他SQL应用程序使用的表旁边。

MaxScale allows a SQL application to consume NoSQL data
MaxScale允许SQL应用程序使用NoSQL数据

Let’s try this last approach in a quick and easy-to-follow experiment with MaxScale. You will need the following installed on your computer:
让我们尝试用MaxScale进行一个简单易懂的实验，这是最后一种方法。您需要在计算机上安装以下软件：

• Docker Docker
• The mariadb-shell tool 这个工具
• The mongosh tool 这个工具

Setting up the MariaDB database
设置MariaDB数据库

Using a plain text editor create a new file and save it with the name docker-compose.yml. The file should contain the following:
使用纯文本编辑器创建一个新文件，并将其保存为docker-compose.yml。文件应包含以下内容：

version: "3.9"
services:
  mariadb:
    image: alejandrodu/mariadb
    environment:
      - MARIADB_CREATE_DATABASE=demo
      - MARIADB_CREATE_USER=user:Password123!
      - MARIADB_CREATE_MAXSCALE_USER=maxscale_user:MaxScalePassword123!

  maxscale:
    image: alejandrodu/mariadb-maxscale
    command: --admin_host 0.0.0.0 --admin_secure_gui false
    ports:
      - "3306:4000"
      - "27017:27017"
      - "8989:8989"
    environment:
      - MAXSCALE_USER=maxscale_user:MaxScalePassword123!
      - MARIADB_HOST_1=mariadb 3306
      - MAXSCALE_CREATE_NOSQL_LISTENER=user:Password123!

This is a Docker Compose file. It describes a set of services to be created by Docker. We are creating 2 services (or containers)—a MariaDB database server and a MaxScale database proxy. They will be running locally on your machine, but in production environments, it’s common to deploy them in separate physical machines. Keep in mind that these Docker images are not suitable for production! They are intended to be suitable for quick demos and tests. You can find the source code for these images on GitHub. For the official Docker images from MariaDB, head to the MariaDB page on Docker Hub.
这是一个Docker Compose文件。它描述了一组由Docker创建的服务。我们正在创建2个服务（或容器）-一个MariaDB数据库服务器和一个MaxScale数据库代理。它们将在您的本地机器上运行，但在生产环境中，将它们部署在单独的物理机器上是很常见的。请记住，这些Docker镜像不适用于生产环境！它们旨在适用于快速演示和测试。您可以在GitHub上找到这些镜像的源代码。要获取MariaDB的官方Docker镜像，请访问Docker Hub上的MariaDB页面。

The previous Docker Compose file configures a MariaDB database server with a database (or schema, they are synonyms in MariaDB) called demo. It also creates a user named user with the password Password123!. This user has suitable privileges on the demo database. There’s an additional user with name maxscale_user and password MaxScalePassword123!. This is the user that the MaxScale database proxy will use to connect to the MariaDB database.
之前的Docker Compose文件配置了一个名为 demo 的MariaDB数据库服务器，并创建了一个名为 user 的用户，密码为 Password123! 。该用户在 demo 数据库上拥有适当的权限。还有一个名为 maxscale_user 的附加用户，密码为 MaxScalePassword123! 。这是MaxScale数据库代理将用来连接到MariaDB数据库的用户。

The Docker Compose file also configures the database proxy by disabling HTTPS (don’t do this in production!), exposing a set of ports (more on this in a moment), and configuring the database user and location of the MariaDB database proxy (normally an IP address, but here we can use the name of the container previously defined in the Docker file). The last line creates a NoSQL listener that we’ll use to connect as a MongoDB client on the default port (27017).
Docker Compose文件还通过禁用HTTPS（在生产环境中不要这样做！），暴露一组端口（稍后会详细介绍），以及配置数据库用户和MariaDB数据库代理的位置（通常是IP地址，但在这里我们可以使用之前在Docker文件中定义的容器名称）来配置数据库代理。最后一行创建了一个NoSQL监听器，我们将使用它作为MongoDB客户端连接到默认端口（27017）。

To start the services (containers) using the command line, move to the directory in which you saved the Docker Compose file and run the following:
要使用命令行启动服务（容器），请进入保存Docker Compose文件的目录，并运行以下命令：

docker compose up -d

After downloading all the software and starting the containers, you’ll have a MariaDB database and MaxScale proxy both preconfigured for this experiment.
下载所有软件并启动容器后，您将拥有一个预配置的MariaDB数据库和MaxScale代理，用于此实验。

Creating a SQL table in MariaDB
在MariaDB中创建一个SQL表

Let’s connect to the relational database. In the command line, execute the following:
让我们连接到关系型数据库。在命令行中执行以下操作：

mariadb-shell --dsn mariadb://user:'Password123!'@127.0.0.1

Check that you can see the demo database:
请确认您能够看到 demo 数据库：

show databases;

Switch to the demo database:
切换到演示数据库：

use demo;

Connecting to a database with MariaDB Shell
使用MariaDB Shell连接到数据库

Create the books table:
创建 books 表格：

CREATE TABLE books(
  isbn VARCHAR(20) PRIMARY KEY,
  title VARCHAR(256),
  year INT
);

Insert some data. I’m going to use the cliché of inserting my own books:
插入一些数据。我要使用一个陈词滥调，插入我自己的书籍：

INSERT INTO books(title, isbn, year)
VALUES
  ("Vaadin 7 UI Design By Example", "978-1-78216-226-1", 2013),
  ("Data-Centric Applications with Vaadin 8", "978-1-78328-884-7", 2018),
  ("Practical Vaadin", "978-1-4842-7178-0", 2021);

Check that the books are stored in the database by running:
通过运行以下命令，检查图书是否存储在数据库中：

SELECT * FROM books;

Inserting data with MariaDB Shell
使用MariaDB Shell插入数据

Creating a JSON collection in MariaDB
在MariaDB中创建一个JSON集合

We haven’t installed MongoDB, yet we can use a MongoDB client to connect to create collections and documents as if we were using MongoDB, except that the data is stored in a powerful fully ACID-compliant and scalable relational database. Let’s try that out!
我们还没有安装MongoDB，但是我们可以使用MongoDB客户端连接来创建集合和文档，就像我们使用MongoDB一样，只是数据存储在一个功能强大、完全符合ACID标准且可扩展的关系型数据库中。让我们试试吧！

In the command line, use the MongoDB shell tool to connect to the MongoDB… wait… it’s actually the MariaDB database! Simply run the following:
在命令行中，使用MongoDB shell工具连接到MongoDB...等等...实际上是MariaDB数据库！只需运行以下命令：

mongosh

By default, this tool tries to connect to a MongoDB server (which again, happens to be MariaDB this time) running on your local machine (127.0.0.1) using the default port (20017). If everything goes well, you should be able to see the demo database listed when you run the following command:
默认情况下，此工具尝试连接到运行在本地机器（127.0.0.1）上使用默认端口（20017）的 MongoDB 服务器（这次是 MariaDB）。如果一切顺利，当您运行以下命令时，您应该能够看到 demo 数据库列出来。

show databases

Switch to the demo database:
切换到 demo 数据库：

use demo

Connecting to MariaDB using Mongo Shell
使用Mongo Shell连接到MariaDB

We are connected to a relational database from a non-relational client! Let’s create the ratings collection and insert some data into it:
我们从一个非关系型客户端连接到了一个关系型数据库！让我们创建 ratings 集合并向其中插入一些数据：

db.ratings.insertMany([
   {
       "isbn": "978-1-78216-226-1",
       "stars": 5,
       "comment": "A good resource for beginners who want to learn Vaadin"
   },
   {
       "isbn": "978-1-78328-884-7",
       "stars": 4,
       "comment": "Explains Vaadin in the context of other Java technologies"
   },
   {
       "isbn": "978-1-4842-7178-0",
       "stars": 5,
       "comment": "The best resource to learn web development with Java and Vaadin"
   }
])

Check that the ratings are stored in the database:
检查评级是否存储在数据库中：

db.ratings.find()

Querying a MariaDB database using Mongo Shell
使用Mongo Shell查询MariaDB数据库

Using JSON functions in MariaDB
在MariaDB中使用JSON函数

At this point, we have a single database that, from the outside, looks like a NoSQL (MongoDB) database and a relational (MariaDB) database. We are able to connect to the same database and write and read data from MongoDB clients and SQL clients. All the data is stored in MariaDB, so we can use SQL to join data from MongoDB clients or applications with data from MariaDB clients or applications. Let’s explore how MaxScale is using MariaDB to store MongoDB data (collections and documents).
目前，我们拥有一个单一的数据库，从外部看起来像一个NoSQL（MongoDB）数据库和一个关系型（MariaDB）数据库。我们能够连接到同一个数据库，并且能够从MongoDB客户端和SQL客户端写入和读取数据。所有的数据都存储在MariaDB中，因此我们可以使用SQL将MongoDB客户端或应用程序的数据与MariaDB客户端或应用程序的数据进行连接。让我们来探索一下MaxScale如何使用MariaDB来存储MongoDB的数据（集合和文档）。

Connect to the database using a SQL client like mariadb-shell, and show the tables in the demo schema:
使用类似 mariadb-shell 的 SQL 客户端连接到数据库，并显示演示模式中的表格：

show tables in demo;

You should see both the books and ratings tables listed. ratings was created as a MongoDB collection. MaxScale translated the commands sent from the MongoDB client and created a table to store the data in a table. Let’s see the structure of this table:
你应该看到 books 和 ratings 两个表被列出。 ratings 被创建为MongoDB集合。MaxScale将从MongoDB客户端发送的命令翻译并创建一个表来存储数据。让我们来看看这个表的结构：

describe demo.ratings;

A NoSQL collection stored as a MariaDB relational table
一个以MariaDB关系表形式存储的NoSQL集合

The ratings table contains two columns:
该 ratings 表包含两列：

• id: the object id
  id ：对象ID
• doc: the document in JSON format
  doc ：JSON格式的文档

If we inspect the contents of the table, we’ll see that all the data about ratings is stored in the doc column in JSON format:
如果我们检查表格的内容，我们会发现所有关于评分的数据都以JSON格式存储在 doc 列中：

SELECT doc FROM demo.ratings \G

NoSQL documents stored in a MariaDB database
存储在MariaDB数据库中的NoSQL文档

Let’s get back to our original goal—show the book titles with their rating information. The following is not the case, but let’s suppose for a moment that the ratings table is a regular table with columns stars and comment. Joining this table with the books table would be easy and our job would be done:
让我们回到我们最初的目标 - 显示书名及其评分信息。以下情况并非如此，但让我们暂时假设 ratings 表是一个常规表，具有列 stars 和 comment 。将此表与 books 表连接将很容易，我们的工作就完成了。

/* this doesn’t work */
SELECT b.title, r.stars, r.comment
FROM ratings r
JOIN books b USING(isbn)

We need to convert the doc column of the ratings table to a relational expression that can be used as a new table in the query. Something like this:
我们需要将 doc 表的 ratings 列转换为一个关系表达式，以便在查询中作为新表使用。类似这样的：

/* this still doesn’t work */
SELECT b.title, r.stars, r.comment
FROM ratings rt
JOIN ...something to convert rt.doc to a table... AS r
JOIN books b USING(isbn)

That something is the JSON_TABLE function. MariaDB includes a comprehensive set of JSON functions for manipulating JSON strings. We’ll use the JSON_TABLE function to convert the doc column into a relational form that we can use to perform SQL joins. The general syntax of the JSON_TABLE function is as follows:
那个东西就是JSON_TABLE函数。MariaDB包含了一套全面的JSON函数，用于操作JSON字符串。我们将使用 JSON_TABLE 函数将 doc 列转换为关系形式，以便我们可以使用它来执行SQL连接操作。 JSON_TABLE 函数的一般语法如下：

JSON_TABLE(json_document, context_path COLUMNS (
    column_definition_1,
    column_definition_2,
    ...
  )
) [AS] the_new_relational_table

Where: 在哪里：

• json_document: a string or expression that returns the JSON documents to be used
  json_document ：一个返回要使用的JSON文档的字符串或表达式
• context_path: a JSON Path expression that defines the nodes to be used as the source of the rows
  context_path ：一个JSON Path表达式，用于定义作为行源的节点

And the column definitions have the following syntax:
列定义具有以下语法：

new_column_name sql_type PATH path_in_the_json_doc [on_empty] [on_error]

Combining this knowledge, our SQL query would look like the following:
结合这些知识，我们的SQL查询将如下所示：

SELECT b.title, r.stars, r.comment
FROM ratings rt
JOIN JSON_TABLE(rt.doc, '$' COLUMNS(
    isbn VARCHAR(20) PATH '$.isbn',
    stars INT PATH '$.stars',
    comment TEXT PATH '$.comment'
  )
) AS r
JOIN books b USING(isbn);

Joining NoSQL and SQL data in a single SQL query
在一个单一的SQL查询中连接NoSQL和SQL数据

We could have used the ISBN value as the MongoDB ObjectID and consequently as the id column in the ratings table, but I’ll leave that to you as an exercise (hint: use _id instead of isbn when inserting data using the MongoDB client or app).
我们本可以将ISBN值用作MongoDB ObjectID，进而用作 id 表中的列，但我将把这个任务留给你作为练习（提示：在使用MongoDB客户端或应用程序插入数据时，使用 _id 而不是 isbn ）。

A word on scalability
关于可扩展性的一点说明

There’s a misconception that relational databases don’t scale horizontally (adding more nodes) while NoSQL databases do. But relational databases scale without sacrificing ACID properties. MariaDB has multiple storage engines tailored to different workloads. For example, you can scale a MariaDB database by implementing data sharding with the help of Spider. You can also use a variety of storage engines to handle different workloads in a per-table basis. Cross-engine joins are possible in a single SQL query.
有一个误解，即关系型数据库不能水平扩展（添加更多节点），而NoSQL数据库可以。但是关系型数据库可以在不牺牲ACID属性的情况下进行扩展。MariaDB拥有多个针对不同工作负载定制的存储引擎。例如，您可以通过使用Spider来实现数据分片，从而扩展MariaDB数据库。您还可以使用各种存储引擎来处理每个表的不同工作负载。在单个SQL查询中可以进行跨引擎连接。

Combining multiple storage engines in a single logical MariaDB database
在一个逻辑的MariaDB数据库中结合多个存储引擎

Conclusion 结论

Our job here is done! Now your systems can have an ACID-compliant scalable 360-degree view of your data independent of whether it was created by SQL or NoSQL applications. There’s less need to migrate your apps from NoSQL to SQL or to make SQL apps database polyglots. If you want to learn more about other features in MaxScale, watch this video or visit the docs.
我们的工作完成了！现在您的系统可以拥有符合ACID标准的可扩展的360度数据视图，无论是由SQL还是NoSQL应用程序创建的。不再需要将应用程序从NoSQL迁移到SQL，也不需要将SQL应用程序变成数据库多语言。如果您想了解更多关于MaxScale的其他功能，请观看这个视频或访问文档。