Shulou(Shulou.com)05/31 Report--

How to operate cross-library Join, for this problem, this article details the corresponding analysis and solution, hoping to help more small partners who want to solve this problem find a simpler and easier way.

Abstract: Enterprises choose to split their online business databases or choose different database types to meet their business needs. Business data is scattered everywhere. How to summarize and query these data conveniently has become a big problem for users

background

As business complexity increases and data size grows, more and more companies choose to split their online business databases vertically or horizontally, or even choose different database types to meet their business needs. SQL queries that were originally implemented in the same database instance now need to be completed across multiple database instances. Business data is scattered in various places. How to easily summarize and query these data has become a big problem for users.

For this kind of problem, the traditional solution requires users to gather all the instance data in advance to the same place, and then do offline query analysis. For this reason, users need to maintain data migration links, purchase machine resources to store aggregated data, and pay a lot of resources and operation and maintenance costs. Not only that, data migration also means data delay. The online business data just generated needs to "wait a while" or even "wait a day" to be analyzed, which cannot meet the real-time requirements.

In order to solve the problem of timely query across database instances, Alibaba Cloud DMS (Data Management) launched a cross-instance query service.

What is Cross-Instance Query Service

Cross-instance query service provides timely association query service for online heterogeneous data sources in different environments. No matter whether the database is MySQL, SQL Server, PostgreSQL or Redis, no matter which Aliyun region the database instance is deployed in, it can realize the association query between these database instances without data collection and only through one SQL.

Not only that, database instances can also be deployed in different resource environments. In addition to RDS, we also support self-built databases on ECS, self-built databases with public IP, user-local IDC self-built databases, and even databases deployed in other cloud vendors.

Features Online data timely query

At present, most data analysis solutions need to export OLTP database data to offline data system for analysis, but this solution can not meet the real-time requirements, and there is also a risk of data loss when data is exported to offline system.

DMS cross-instance query service, without user migration tasks, directly write a SQL, can achieve direct association analysis of multiple online databases. Because there is no need for data synchronization, the complexity of the business architecture is reduced, and the budget and operation and maintenance costs of offline computing resources owned by users are greatly saved.

DBLink

Those familiar with Oracle should know that we can set up a DBLink on the currently logged Oracle to point to another remote Oracle database table. In Cross-Instance Query Services, we redefined the concept of DBLink, which is a virtual connection to any database instance of the user, an alias for the database instance. For example, for MySQL, DBLink and ip/port correspond one-to-one. DBLink enables SQL access to arbitrary data sources.

cdn.com/5195fb64ba5d07fc26656b4276350f91e59bee3b.png">

Support multiple relational databases

Currently, MySQL, SQL Server, PostgreSQL and other relational databases are supported.

SQL access to NoSQL

In addition to relational databases, cross-instance queries also support SQL access to NoSQL databases such as Redis. Because SQL syntax is supported, relational queries between RDBMS and NoSQL can also be implemented. Yes, you are right, a SQL can implement the association query between MySQL and Redis.

Support cross-region and hybrid cloud queries

When an enterprise develops to a certain stage, the number of users and business volume continue to rise, and the original single room capacity can no longer meet the needs of business development. Combined with disaster recovery, high availability and other factors, cross-region deployment is usually selected, which is also called unitized deployment. At the same time, many enterprises also need to expand their business overseas and provide better experience for foreign users through local deployment. The problem brought about by such horizontal splitting is how to perform unified summary and association queries on global business data.

With DMS cross-instance query service, no matter which region of Alibaba Cloud your database instance is deployed in, you can realize unified query of all region data without cross-region data migration.

In addition to Aliyun RDS, we also support various databases deployed by users on Aliyun ECS. Not only that, if your database is deployed in the local IDC room, even other cloud vendors can implement cross-instance correlation queries for these hybrid cloud scenarios through cross-instance query services.

Cross-instance data import export

insert into b select * from a;

As we all know, this SQL statement can export the data of table a to table b, but if table a and table b are not on the same database instance, then this SQL will be helpless.

The appearance of cross-instance query service breaks the boundary of data import and export between instances. It can export data from the tables of one MySQL instance to the tables of another MySQL instance; it can also export the results of SQL Server table and PostgreSQL table association query to the tables of MySQL instance.

Compatible with standard SQL

Cross-instance queries can be implemented using standard SQL statements. At the same time, cross-instance query service is highly compatible with MySQL, supports MySQL protocol, and various common functions and syntax. You can connect to the cross-instance query service through the JDBC/ODBC driver; you can use various MySQL GUI tools to manage various data sources; or you can use them directly from the DMS cross-instance query console.

Serverless architecture

Cross-instance query is a serverless online database relational query service. Users do not need to pre-purchase computing resources, no maintenance resources, no transportation and upgrade costs, anytime and anywhere to use.

High Performance Low Latency

Cross-instance query service is based on powerful MPP computing engine, which continuously optimizes SQL queries, including pushdown, join algorithm, execution plan cache, Meta cache, local scheduling, connection pool and other technologies. At present, single-table queries and cross-instance multi-table association queries can be completed in milliseconds.

technical architecture

We found that what users encountered was actually a typical cross-instance query problem. At present, Aliyun DMS cross-instance query service already supports the ability of SQL query across multiple database instances, and users can solve the above problems with one SQL. It can not only meet the core requirement of "cross-library Join," but also greatly simplify the technical solution of users.

Associative Query of Heterogeneous Database

A company is migrating business data from SQL Server to MySQL, considering cost and future scalability. During this period, there must be some business subsystems still on SQL Server, and some other business subsystems have all migrated to MySQL. At this time, the joint query between the two subsystems can be realized by Alibaba Cloud's cross-instance query service. Not only that, but during migration, you can also verify whether the data on SQL Server and MySQL is consistent by querying services across instances.

Associative queries for hybrid cloud scenarios

A game company, due to various reasons, keeps database instances of Alibaba Cloud, Tencent, UCloud, AWS and other environments at the same time, and also deploys some databases in its own IDC. Business data is so scattered, just statistics about the current number of online users of the game, we have to go to each environment to query again and then do summary. With Alibaba Cloud's cross-instance query service, a SQL can realize the correlation query between cross-cloud vendors and IDC.

About how to operate the cross-library Join question answer to share here, I hope the above content can have some help to everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.

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.