Using HetuEngine to Access Data Sources Across Sources and Domains

Using the HetuEngine Cross-Source Function

Companies often store large amounts of data from different sources in databases and warehouses to manage and collect information. However, having data from different sources with a variety of structures and scattered storage can make it expensive and time-consuming to query across all of them.

HetuEngine provides unified standard SQL statements to implement cross-source collaborative analysis, simplifying cross-source analysis operations.

Figure 1 HetuEngine cross-source analysis

Technologies and Advantages

• Compute pushdown: When HetuEngine is used for cross-source collaborative analysis, HetuEngine enhances the compute pushdown capability from the dimensions listed as follows to improve access efficiency.
  • Basic pushdown: predicate, projection, subquery, and limit
  • Aggregate pushdown: GROUP BY, ORDER BY, COUNT, SUM, MIN, and MAX
  • Operator pushdown: <, >, LIKE, and OR.

Usage Guide of Cross-Source Analysis

HetuEngine supports quick joint query of multiple data sources and GUI-based data source configuration and management. You can quickly add the following data sources on the HSConsole page by referring to Overview of HetuEngine Interactive Query:

• Adding a Hive Data Source
• Adding a Hudi Data Source
• Adding a ClickHouse Data Source
• Adding a GaussDB Data Source
• Adding an HBase Data Source
• Adding a Cross-Cluster HetuEngine Data Source
• Adding an IoTDB Data Source
• Adding a MySQL Data Source
• Adding an Oracle Data Source
• Adding a GBase Data Source

Process of Using Cross-Source Collaborative Analysis

1. Log in to the HetuEngine client by referring to Quickly Using HetuEngine to Access Hive Data Source.
2. Register data sources such as Hive, HBase, and GaussDB A.

   hetuengine> show catalogs;
     Catalog  
   ----------  
   dws                
   hive         
   hive_dg
   hbase
   system       
   systemremote 
   (6 rows)

3. Compile SQL statements for cross-source collaborative analysis.

   select * from hive_dg.schema1.table1 t1 join hbase.schema3.table3 t2 join dws.schema02.table4 t3 on t1.name = t2.item and t2.id = t3.cardNo;

HetuEngine Cross-Domain Function

HetuEngine provide unified standard SQL to implement efficient access to multiple data sources distributed in multiple regions (or data centers), shields data differences in the structure, storage, and region, and decouples data and applications.

Figure 2 HetuEngine cross-region functions

Key Technologies and Advantages

• No single-point failure bottleneck: HSFabric supports horizontal scale-out and multi-channel parallel transmission, maximizing the transmission rate. Cross-domain latency is no longer a bottleneck.
• Better computing resource utilization: Data compression and serialization tasks are delivered to Worker for parallel computing.
• Efficient serialization: The data serialization format is optimized to reduce the amount of data to be transmitted at the same data volume level.
• Streaming transmission: Based on HTTP 2.0 stream, ensure the universality of the HTTP protocol and reduce the repeated invoking of RPC during the transmission of a large amount of data.
• Resumable transmission: A large amount of data is prevented from being retransmitted after the connection is interrupted abnormally during data transmission.
• Traffic control: The network bandwidth occupied by data transmission can be limited by region to prevent other services from being affected due to exclusive traffic occupation in cross-region limited bandwidth scenarios.

Using a Cross-Domain Function

Prerequisites:

• At least one HSFabric instance has been deployed on the data nodes of the local and remote clusters.
• The nodes where the HSFabric instances of the local and remote clusters have been deployed can communicate with each other.

Procedure

1. Open the data source in the local domain. You can create a virtual schema to shield the real schema information and instance information of the physical data source in the local domain from remote access requests. The remote end can use the virtual schema name to access the data source in the local domain.

   CREATE VIRTUAL SCHEMA hive01.vschema01 WITH (
     catalog = 'hive01',
     schema = 'ins1'
   );

2. Register the data source of the HetuEngine type on the remote HetuEngine and add the local domain HetuEngine by referring to Adding a Cross-Cluster HetuEngine Data Source.
3. Use cross-domain collaborative analysis.

   // 1. Open the hive1.ins2 data source on the remote HetuEngine.
   CREATE VIRTUAL SCHEMA hive1.vins2 WITH (
     catalog = 'hive1',
     schema = 'ins2'
   );
   
   // 2. Register three types of data sources, including Hive, GaussDB A, and HetuEngine, on HetuEngine in the local domain.
   hetuengine> show catalogs;
     Catalog  
   ----------  
   dws        
   hetuengine_dc          
   hive         
   hive_dg
   system       
   systemremote 
   (6 rows)
   
   // 3. Perform cross-source collaborative analysis on HetuEngine in the local domain.
   select * from hive_dg.schema1.table1 t1 join hetuengine_dc.vins2.table3 t2 join dws.schema02.table4 t3 on t1.name = t2.item and t2.id = t3.cardNo;