Created on 06-10-202102:09 PM - edited on 06-13-202109:06 PM by subratadas
Accessing AWS Cloudera Data Warehouse to query data on Azure Cloudera Data Warehouse
Cloudera Data Platform enables in a single console to work with different public cloud providers. With this, you can have a true hybrid environment with only one admin console.
Cloudera Data Warehouse is a public cloud service that allows fast analytics in your preferred cloud provider.
In this article, I'll show how easy it is to connect between two Virtual Warehouses located in different cloud providers using Cloudera Data Warehouse.
We're using two different cloud providers for Cloudera Data Warehouse: one in AWS with TPC-DS data and another in Azure with the same TPC-DS data. We'll use Hive ACID to update the customer table on Azure and merge it with the customer table in AWS.
1.1 - Cloudera CDP Control Plane Access and Register two environments
For this exercise, you will need access to the Cloudera Data Platform. More information can be accessed here.
Also, since we will use two environments (AWS and Azure), we need to register the environments on the CDP control plane.
And after this, we can update the customer information with the new id and check it with the same query that we've run first:
Figure 8: Updated Address in Azure
2.2 - Create the External JDBC Table to connect from AWS Cloudera VW to Azure Cloudera VW
Now that we have the data on Azure, let's access Cloudera Data Warehouse created on AWS in Hue using the same method that we've accessed Hue in Azure with.
In this example, we've already the same schema/tables created in this environment with the data stored in S3 instead ADLS.
Figure 9: Schema of tables in AWS Cloudera Environment
Now in this AWS, we want to create the customer and address table pointing to the tables located in the Azure Virtual Warehouse:
Creating Customer Azure External Table in AWS Cloudera VW:
For this step, we need the Azure Virtual Warehouse JDBC address, we can get in Cloudera Data Warehouse UI in the Copy JDBC URL button:
Figure 10: Copy JDBC URL from Azure Cloudera Virtual Warehouse
Now we can execute the following script to create the JDBC tables (please change the "hive.sql.jdbc.url" value with the JDBC address from your Virtual Warehouse. Also change the user/password with your CDP user/password:
First, we will update the AWS customer table based on the results of the customer_azure table:
tpcds.customer_azure as caz
customer.c_customer_sk = caz.c_customer_sk
and caz.c_customer_sk = 11316001
when matched then
update set c_current_addr_sk = caz.c_current_addr_sk;
Figure 14: Updating Customer table in Cloudera AWS Data Warehouse using Azure Cloudera Data Warehouse as source
Note that we are not inserting a register in case it's not matched, and we're not updating other fields since we only want to demonstrate the address in this example, but this is completely possible. Also, in the WHERE clause, we're defining the customer_sk to match to one register, just for this example.
tpcds.customer_address_azure as cadaz
customer_address.ca_address_sk = cadaz.ca_address_sk
and cadaz.ca_address_sk = 6000001
when not matched then
insert values (cadaz.ca_address_sk,cadaz.ca_address_id,cadaz.ca_street_number,cadaz.ca_street_name,cadaz.ca_street_type,cadaz.ca_suite_number,cadaz.ca_city,cadaz.ca_county,cadaz.ca_state,cadaz.ca_zip,cadaz.ca_country,cadaz.ca_gmt_offset,cadaz.ca_location_type);
Figure 15: Insert new data into Customer Address table in Cloudera AWS Data Warehouse using Azure Cloudera Data Warehouse as source.
Now that we have updated/inserted new data, we can check the data on AWS with the same query that we've executed in Azure:
select c.c_current_addr_sk, ca.ca_street_name, ca.ca_country
from tpcds.customer c, customer_address ca
where c.c_current_addr_sk = ca.ca_address_sk
and c.c_customer_sk = 11316001;
Figure 16: Fresh data into AWS Cloudera Data Warehouse environment with the same view as Azure Cloudera Data Warehouse environment.
4. Conclusion and Going Further
With this, we've demonstrated how to access an Azure Cloudera Data Warehouse environment from an AWS Cloudera Data Warehouse environment and use Hive ACID features to upsert the data.
Going further this can be used as a hybrid multi-cloud strategy where one Cloudera environment can be used for Machine Learning and the other for Data Warehouse (Or DEV/PROD strategy). Also, this data/metadata that we've created can be accessed from other experiences like Data Engineering, Cloudera Machine Learning Data Flows, Data Hubs to have a complete end-to-end scenario.
We can also extend Cloudera Data Engineering with Airflow to schedule the refresh, so this can be periodically done.