Shulou(Shulou.com)06/01 Report--

This article is translated and published by the official account EAWorld, and the source should be indicated when reprinted.

Author: Akmal B. Chaudhri

Translator: Li Yujue

Original title: Using'No Rip and Replace' with Apache ®Ignite ™, MySQL and Node.js

Original: http://t.cn/Efatkx6

The full text is 3769 words, and it takes about 8 minutes to read.

Introduction:

In this article, you can take a look at a useful scenario where Ignite works with existing data from other data sources, such as relational databases.

Usually in the industry, many systems also have great commercial value, they must be maintained or even enhanced, and undeveloped areas are rare. Ignite can be used in legacy or traditional systems in an organization to increase their value and provide new possibilities, such as cluster computing with horizontal scalability, significant memory-level performance advantages, and new applications using machines and deep learning.

You can take a look at an example where you already have some data in the relational database, and then see how Ignite caches that data in memory, SQL the in-memory data and change it back to the relational database. Here you will use some Node.js code to access Ignite and execute some SQL queries.

I. the existing database system

MySQL is used in this article, and a database called world is ready to load some of the data.

The world database is structured with three relational tables, as follows:

Country: represents the countries of the world (239 rows of data)

City: partial city information representing the country (4079 rows of data)

Countrylanguage: the language spoken by each country (984 rows of data).

Next, verify that MySQL has started successfully and accept external connections.

II. Web console and Web agent

In order to access the schema information of the MySQL database, you need to use Ignite's Web console. For convenience in this article, you use a GridGain-hosted service, but the source code of the Web console can be downloaded and can be built locally and run behind the company's firewall. Details can be found in the relevant documentation.

You also need a Web agent, which can be downloaded from the Web console, as shown in the following figure:

After downloading the zip package for the Web agent, you can extract the file, and the directory structure is roughly shown in the following figure:

Notice that there is a directory called jdbc-drivers, because this example requires access to MySQL, so you need to download the driver for MySQL, and then put the jar file in this folder, as shown in the following figure:

Start the Web agent from the terminal window as follows:

. / ignite-web-agent.sh

The output is roughly shown in the following figure:

Import schemas from MySQL

Now you can import the schema information. In the Configuration page of the Web console, there is an Import from Database button in the upper right corner, as shown in the following figure:

After clicking the button, the output is roughly shown in the following figure:

In this interface, you need to enter the JDBC URL, User and Password of the MySQL server, as shown in the following figure:

Click Next after completion, and you will see each database schema, and then cancel everything except the world schema, as shown in the following figure:

Once selected, click Next, and you will see three tables, as shown in the following figure:

For this article, the default values on this page will be fine, then click Next, which will jump to the page shown in the following figure:

For this article, the default value for this page is fine, and then click Save:

Next, on the Configuration page, you can see that a new configuration item named ImportedCluster is listed, as shown in the following figure:

In order to meet business needs, this configuration can be modified.

IV. Modify the configuration

If you click ImportedCluster in the image above, you will jump to the following page:

There are two tabs in this interface: Basic and Advanced.

In the Basic tab, the configuration name of the cluster (step 1), if you scroll down, the name of the Ignite store (step 2), and several other parameters, can be modified, in this case, the default value on this page will be maintained.

In the Advanced tab, there are other subitems, including Cluster, SQL Scheme, Caches, IGFS, and GridGain, as shown in the following figure, many of which can be fine-tuned if necessary:

Select the SQL Scheme tab here, select the line City, as shown in the following figure, and then scroll down and expand the Domain model for SQL query section:

Here is an indexes subkey with a value of CountryCode. If you click on it, you can modify it, as shown in the following figure:

Change the index name to idx_country_code here, and then click the Save button. Next, repeat the previous figure and the above process for Countrylanguage, change the index name to idx_lang_country_code, save the changes, and make these changes to ensure that the index name is unique throughout the Ignite schema.

Next, select the Caches tab, first select CityCache, as shown in the following figure, then scroll down and expand the Queries & Indexing section:

In Queries & Indexing, there is a field with an empty name of SQL schema name, where you enter PUBLIC, save the changes, and repeat the process for CountryCache and CountrylanguageCache.

Finally, go back to the Configuration page, select ImportedCluster, and download the project in the Actions drop-down box, as shown below:

At this point, a file named ImportedCluster-project.zip is saved. After decompressing the file, you can create a new project in IDE by reading the pom.xml file in it, as shown in the following figure:

In the pom.xml file, under dependencies, you need to check the mysql-connector-java dependency. If it is missing, you need to add it, as shown below:

Mysql

Mysql-connector-java

8.0.15

The version number here matches the version number of the previously used JDBC driver.

In this project, under the resources folder, there is a file named secret.properties, as shown in the following figure:

Here you need to fill in the previously used JDBC URL, Username, and Password, and then save the changes.

After the project is rebuilt, you can start an Ignite server node, as shown in the following figure:

Next, by running LoadCaches, you can load the data from MySQL into the Ignite store, as shown in the following figure:

In the Web console, go to Monitoring > Dashboard > Caches, and you can see that the Ignite storage has been created and the data has been loaded successfully, as shown in the following figure:

Ignite is now running, creating storage and loading data from MySQL, and you can access Ignite through any number of different interfaces, but the Node.js thin client will be used in this article.

5. Node.js thin client

Using the Node.js version of SQL example provided by Ignite as a template, you can create several Node.js applications and then execute the SQL query listed in the following table. The complete Node.js application code can be found in GitHub. In the following example, the Node.js application runs in the Node.js sample folder of Ignite:

Q1RO the three most populous countries

SELECT name, MAX (population)

AS max_pop FROM country

GROUP BY name, population

ORDER BY max_pop

DESC LIMIT 3

The three most populous cities in Q2:US, RUS and CHN

SELECT country.name, city.name, MAX (city.population)

AS max_pop FROM country

JOIN city ON city.countrycode = country.code

WHERE country.code IN ('USA','RUS','CHN')

GROUP BY country.name, city.name

ORDER BY max_pop

DESC LIMIT 3

Q3: update the country name

UPDATE country

SET name = 'USA'

WHERE name = 'United States'

Q4: restore the country name

UPDATE country

SET name = 'United States'

WHERE name = 'USA'

The output of Q1 is shown in the following figure:

Q2 is more complex than Q1 and contains the association of two tables. The output of Q2 is roughly shown in the following figure:

Q3 performs an update operation, and after Q3 executes, the Ignite cache is updated and the update is written back to MySQL, keeping the two in sync. You can use DBeaver to confirm that you first find the row with the value United States in the Country table, as shown in the following figure:

Refresh DBeaver after Q3 execution, and you can see that the value of the Name field has changed to USA, as shown in the following figure:

Q4 restores the original value, which can be verified by executing the SQL and then refreshing the DBeaver, as shown in the following figure:

VI. Next step

You can further test the Node.js thin client by modifying and adjusting the example that comes with Ignite. In addition, the Web console provides a number of options for fine-tuning and adjusting schema information from existing database systems, as described in the documentation of the Web console.

VII. Summary

In this article, you learned how to get schema information from an existing MySQL database system and create an Ignite project. The Ignite project can copy data from the MySQL server to the Ignite store, and then execute queries on that data. In terms of scale, Ignite can take advantage of the powerful power of cluster computing to parallelize operations, quickly execute queries and analyze them in memory, and even machine and deep learning, while retaining the commercial value of existing systems. Although the Node.js thin client is used in this example, Ignite also supports thin clients in other programming languages.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.