Denodo For Big Data
In order to build Virtual Data Services, the user follows three simple steps:
Discover and Consume Integrated Information
• Global Metadata / Data discovery. Global information search capability allows any user or application to discover, search, browse and eventually query both metadata and data through virtual data services to retrieve information.
• Hybrid Query Optimization. The best DV platforms utilize a combination of real-time query optimization and rewriting, intelligent caching, and selective data movement to achieve superior response and performance against both on-demand pull and scheduled batch push data requests.
• Integrated Business Information. Data virtualization delivers integrated information while hiding complexities of accessing disparate data. Users and applications get what they want, in the format they want, with real-time high performance.
Perform Data Governance and DV Management
• Data Governance. DV layer serves as a flexible and unified layer to expose business metadata to users. At the same time it helps to understand the underlying data layers through data profiling, data lineage, change impact analysis and other tools and expose needs for data normalization / quality in underlying sources. Thus DV can be the "single point of reference" to govern information.
• Security and Service Level Policy. All data views from source level to canonical business views to data services can be secured and authenticated to users, groups and roles at highly granular view-row-column level. Further custom security and access policies can throttle or manage service levels to protect source systems from overuse.
• Monitoring and Management. Leading DV platforms will include several monitors, dashboards, audit logs, and management consoles to ensure smooth operation of the DV solution. It also provides tools for managing clusters, high availability, users/roles and to migrate virtual data between development, test and production.
In this Sandbox, you will get to see how the Denodo Platform can integrate and combine data from different analytical data sources and present them to users as a single, integrated data set. Users can then access the data using their preferred tools, such as Zeppelin workbench (the tool that we’ll use in this sandbox).
To follow these steps, you will use the Denodo administration tool. This is the client that Denodo administrators and developers use to define the data models and configure the system. It’s similar to tools like SQL Developer, Toad or SQLServer Management Studio. If you are familiar with relational databases, it won’t take you long to learn how to use it. If you accidentally close this window, there is "Admin Tool" icon on the desktop that you can use to open it again.
You can log in using the following credentials:
Once connected, you will see a panel on the left hand side, the Server Explorer, with the existing Denodo virtual databases, data sources and views that are already available in the server. If you double-click on any of the objects, it will be open in a new tab in the workspace area.
In particular, we are going to use two databases in this sandbox:
sample_completed: this database contains fully configured data sources and example views and reports. Feel free to browse through this database and review and execute the existing views, and use them as examples.
my_test_drive: this is the database where you will create your own models. It only contains the pre-configured connections to the data sources. You will have to build the rest.
Analytical Queries with Data Virtualization
Working on a real example is the best way to learn how something works, isn’t it? This section will show you how to work with Denodo to run typical analytical queries in a distributed multi-source scenario.
Before you get started, under the sample_completed database, let's focus on the Application Layer folder. In particular, look at the Historical Reports folder, and open the view named same_store_sales_by_year.
This view represents the sales trends from year to year for each store. This is a key metric for retail companies as it shows how stores are faring - growing or declining - year over year.
SELECT bv_store.s_store_sk AS store_id，
bv_store.s_city AS city， bv_store.s_state AS state， bv_date_dim.d_year AS year， SUM（store_sales.ss_net_paid_inc_tax）AS total_sales FROM store_sales AS store_sales JOIN bv_date_dim AS bv_date_dim ON store_sales.ss_sold_date_sk = bv_date_dim.d_date_sk JOIN bv_store AS bv_store ON store_sales.ss_store_sk = bv_store.s_store_sk GROUP BY bv_store.s_store_sk，bv_store.s_city，bv_store.s_state， bv_date_dim.d_year ORDER BY store_id ASC，年份ASC
• Three different data sources: Redshift, Aurora and Impala
• Large data volumes: more than 288 millions of rows to perform this calculation.
Run the query
To run this query, simply click on the Execution wizard
, on the top right side of the view panel. Once the panel opens in the lower half of the screen, click on the Execute button (
) to launch the query. It will run a SELECT * FROM same_store_sales_by_year.
How is that possible?
How can a query that brings hundreds of millions of rows through the network, from three different remote sources, take less than 15 seconds?
What you see here is Denodo’s optimizer in action. In particular, you are seeing the result of three different techniques, carefully orchestrated by the optimizer:
Just try to disable the optimizer in the Denodo server settings, and run the query again. To do so, click on Administration > Server Configuration > Queries Optimization. Try to disable the different optimizer settings and run the query again after the charge. Without the static optimizer (which is in change of the query rewriting) the execution will take around 13 minutes to complete. This is what other federation engines do!
Building a Model Step-by-Step: Connecting to the Sources
Now that you have seen a pre-built query in action, let’s build one from scratch. For this section, you will use the database my_test_drive instead. Remember that you can still go back to samples_completed and use those views as a reference if you get stuck.
In my_test_drive you have the same folder structure that we saw in the previous section. Try to keep things organized during development, it will make your life easier. You can create new folders, rename your views and drag & drop them into a different folder when needed.
数据库Customer_MDM 顾客 customer_address 数据库PIM 商店
架构tpcds tpcds_store_sales catalog_returns
数据库tpcds 架构：tpcds_schema store_sales DATE_DIM
To open the shell, go to Tools > VQL Shell in the menu bar. Here you can run any query you want. VQL stands for Virtual Query Language, and it’s just Denodo’s flavor of standard SQL. Just make sure you select the right database in the Database drop down on the top-left of this panel.
select count(*) from my_test_drive.historical_store_sales ==> 219474321
您需要收集统计信息以提供基于成本的优化程序。Denodo提供统计管理器 来帮助您完成此操作。要打开它，请转到 顶部菜单中的工具>管理统计。在下拉列表中选择正确的数据库（my_test_drive ）并检查您拥有的所有基本视图。然后单击“ 收集统计信息”
Define a composite data model (derived view in Denodo’s lingo) in Denodo using the graphical wizards for data modeling.
Write your own SQL. This is the preferred method of some power users with a lot of SQL experience.
Create the UNION of all sales
Let’s now go back to the my_test_drive database to create your own view. You can access the modeling wizards with the right button in the server explorer tree.
Right-click on the folder name, and choose New > Union to open the modeling wizard for UNION views. The wizard is empty, you will have to drag and drop in the workspace the two base views involved in this model:
As you drag the second view, you will see that Denodo automatically links the fields of the two views. Denodo does this when the name and data type match on both sides. Since these two tables have the exact same structure, there is no extra manual modeling.
In the Output tab you can change the name of the view. You can also change the name of the columns to make them more user friendly, or add a description to each one of them. In the Metadata tab, you can add a description for the view.
Once you are done, click on the Save button on top of this panel. You have just created your first derived view!
JOIN the dimensions
Right click on the folder name and choose New > Join. Like before, you will have to drag and drop in the workspace the three base views involved in this model:
store_sales, created in the previous step
date_dim from redshift
store from Aurora
You will have to drag and drop the JOIN conditions, using the arrow that appears on the workspace when you drag a field name. Use the following conditions:
store_sales.ss_sold_date_sk = date_dim.d_date_sk
store_sales.ss_store_sk = store.s_store_sk
在JOIN之上，我们必须定义聚合。您不需要新的向导，您可以在“ 分组依据” 选项卡中定义聚合设置。启用Use Group By 复选框，并将以下字段添加到聚合中：
You just need some final polishing. In the Output tab, name your view same_store_sales_by_year. You can change the names of the fields and add descriptions. For example, you may want to use store_id instead of s_store_sk, since it’s a more understandable name for non-technical users.
您还可以添加新字段和计算指标。在这种情况下，单击New Aggr。字段 并使用以下表达式：
You should start by making sure that you built the right thing. To verify it, open the execution panel and get some sample data. Like in the initial example, execution should take less than 15 seconds, and return 1,206 rows. Results should look like this:
select * from same_store_sales_by_year CONTEXT('i18n'='us_pst','cache_wait_for_load'='true') TRACE
Tools => VQL Shell
Using external clients
You can also connect external clients to Denodo, like Tableau or Power BI, in order to create dashboards and charts. As part of this sandbox, we have included an instance of Apache Zeppelin, a very popular web-based notebook that allows you to run interactive queries to Denodo (via JDBC) and draw charts. A tab with Zeppelin should already be open in this browser, otherwise just head to http://localhost:7777/
To link a paragraph to the datasource, you have to specify an interpreter at the beginning. In the case of Denodo, you use %vdp. VDP stands for Virtual DataPort, which is the name of the Denodo server component. From the Zeppelin home screen you can access an example notebook we have created for this exercise with some templates.