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

Today, I will talk to you about how to query multiple heterogeneous databases, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

DBlink is only suitable for the same database association, so it is difficult to associate heterogeneous libraries. Other ways are either to change the deployment architecture or to increase the development workload, which is much more difficult to implement.

The easy way to develop seems to be ETL. By turning a heterogeneous library into a homogeneous library, you can write the associated SQL in a familiar way. However, this approach requires changing the original deployment architecture, adding an ETL layer and a data Mart layer, generally adding an administrator to maintain scheduled scheduling, or writing additional code to determine whether the data is ETL complete. The bigger problem is that this method needs to turn real-time computing into delayed computing, and Party A's father may clap the table when he knows.

The radical solution seems to be micro-services, turning a highly coupled ODBC/JDBC interface into a low-coupling http interface so that data can be transmitted using a unified json or xml. But in this way, we should develop data service interface for each table, client interface for each algorithm, unfamiliar framework and class library, and solve the security of permissions outside the database. The workload is sky-high. Moreover, the specific operations have to be hard-written.

More realistic methods can also use high-level languages such as JAVA, first use SQL to fetch heterogeneous data, and then use arraylist to convert into two classes, and then you can carry out join between list. But this is tantamount to using JAVA to manually replace SQL, a simple merge is not bad, the associated query is difficult to write, and there are sub-queries, associated grouping summary and other practical algorithms. In short, JAVA lacks structured class libraries, and the cost of replacing SQL is very high.

If you use aggregators to implement heterogeneous library associations, you can maintain the original deployment architecture and do not have a lot of development effort. First of all, SPL can use a unified data structure to access any database table, which solves the problem of heterogeneity. Secondly, SPL has a rich structured class library, which makes it easy to implement association queries and subsequent practical algorithms, which is often simpler than SQL. Finally, the aggregator provides a JDBC\ ODBC interface that allows programmers to implement heterogeneous library associations on the original code by introducing drivers.

For example, java accesses the orders table of Oracle through the JDBC driver of the aggregator, and associates the customer table of MySQL. Finally, the associated results are grouped and summarized:

Con=DriverManager.getConnection ("jdbc:esproc:local://")

ResultSet rs = con.executeQuery ("orcl.query (/" select * from orders/ ") .join (cust,my.query (/" select * from customer/ "): custid,city) .groups (city;sum (amount))")

After reading the above, do you have any further understanding of how multiple heterogeneous databases correlate queries? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.