Distributed Views

For many applications on the web, it seems likely that materialized distributed views have greater value in building applications with distributed data. It is usually necessary to provide users good response times, even when using remote data in the application. Materialized views essentially cache the data from the remote server(s) in a view that can be used just like a local table by the application.

The goal with distribuetd views is to have the required data local as far as possible, so that applications can use remote data without paying any serious performance penalty. Furthermore, it may be that the remote sites or servers are temporarily unavailable. At such times, it is desirable to have the application continue working even while the remote sites and servers are unavailable.

To use distributed materialized views, SQL statements or the User API can be used to create views. For example, the following SQL statement creates a materialied SQL view. Like the earlier query example, ituses a products table in a database named storedb on a remote server named acmeserver, where DataCaster is running on the default Tomcat HTTP port 8080.

CREATE MATERIALIZED PERIODIC REFRESH VIEW acme_products AS SELECT * FROM http://acmeserver:8080/Table/storedb/products

Now you can use acme_products in queries and other programs, and it will perform much like a local table. This CREATE statement specified the view should be periodically refreshed, and this refresh is performed by polling the remote server at fixed intervals to get all the changes from the remote server.

Setting up Distributed Views

DataCaster supports distributed views across servers with periodic updates that keep track of changes in the base tables and views. To create and use these periodic refresh views, special indexes need to be added to allow for efficient updates.

Setup Authentication and Authorization for Remote Users

Remote users need to be authenticated and authorized to be able to access tables and views on remote servers. The only exception is the case where anonymous access is enabled for tables.

For authenticated access, setup is needed on both the server and client systems.

Enable Anonymous Access for Remote Users

To add anonymous access for unauthenticated users, the following statement should be used.

GRANT SELECT ON MYTABLE TO ANONYMOUS

Server Setup

On the server, one or more users need to be setup for remote access. These users should also be authorized for access to specific tables to be made available to those users.

Add a User for Remote Access

To add a user for remote access, use the WebAdmin page, or command line tool.

Authorize a User

Remote users need to be authorized for access to specific tables. Authenticated users have access to all tables with grant access as PUBLIC. For example,

GRANT SELECT ON MYTABLE TO PUBLIC

allows all authenticated users to access MYTABLE. To enable a specific user(s) instead, do the following for each user:

GRANT SELECT ON MYTABLE TO user_name

Add System-level Remote Access for a Remote Client

In addition to adding individual users for remote access, it is also possible to add system-level access for remote client systems, so that every user on that client system have authenticated access to the server tables and views. This simplifies authentication setup when the client system is a server with many users.

To add a remote client for system-level remote access, use the WebAdmin page, or command line tool.

Client Setup

For each client that wants to access a server, the username and password to be used needs to be setup for each server the user wants to access. Authentication information is not used in the SQL statements or other ways of accessing the tables. Such access information needed for authentication must be setup ahead of time for each user and server. This may be simplified through the use of server-level accees.

Add User Access to a Remote Server

To add a user for remote access, use the WebAdmin page, or command line tool.

Access Remote Tables and Views

Remote tables and views can be used in SQL statements with the URL of the remote table or view. For example, use the following to access a table named ArticleLinks in database feeds on win-server which is running DataCaster on port 8080.

select * from http://win-server:8080/Table/feeds/ArticleLinks;

select * from http://192.168.0.201:8080/Table/testdb/users;

Creating a Synchronous Refresh view

To create a synchronous refresh view, use the following:

CREATE MATERIALIZED REFRESH VIEW view_name AS SELECT ...

Creating a Periodic Refresh view

To create a synchronous refresh view, use the following:

CREATE MATERIALIZED PERIODIC REFRESH VIEW view_name AS SELECT ...

Creating a Delta Index

In order to enable periodic refresh of a table, a delta index is required. A Delta index can be added to a table using the CREATE INDEX command with an index of type DeltaBTree and no columns specified. For example, to create such an index on the users table, use the following:

CREATE INDEX deltaidx TYPE DeltaBTree users;