3. Manage Schema Store

Zetaris Cloud Data Fabric keeps all metadata for the external data sources in the Schema Store.

HubSpot Video
HubSpot Video


A user can manage the schema store using the following commands.

 

3.1 Data Source 

Show Data Source

This command shows the data sources registered in the schema store

SHOW DATASOURCES


Drop Data Source
This command drops the registered data source as well as all tables under that.

DROP DATASOURCE ORCL

Describe Data Source

DESCRIBE DATASOURCE ORCL

Describe Slave Data Source

DESCRIBE SLAVE DATASOURCE ORCL

 

3.1 Table

Describe data source table

DESC ORCL.USERS


Show all tables

SHOW TABLES

Show data source tables

SHOW DATASOURCE TABLES ORCL

 

Drop Table

DROP TABLE ORCL.USERS

This command doesn't delete the table in the target data source but it only deletes ingested metadata in the Zetaris Cloud Data Fabric schema store

 

3.2 View

Zetaris Cloud Data Fabric supports the view capability with query definition on a single data source or across multiple data sources

3.2.1 Create Data Source View

CREATE DATASOURCE VIEW TEEN_AGER FROM ORCL AS

  SELECT*FROM USERS WHERE AGE >=13AND AGE <20

The TEEN_AGER view belongs to ORCL data source.

With this capability a user can create a view with DBMS native query, which is really handy :
CREATE DATASOURCE VIEW SALARY_RANK FROM ORCL AS

  SELECT department_id, last_name, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary) RANK

  FROM employees

  WHERE department_id = 60

  ORDER BY RANK, last_name

 

SELECT * FROM ORCL.SALARY_RANK will produce :

DEPARTMENT_ID LAST_NAME SALARY RANK
------------- ------------------------- ---------- ----------
60 Lorentz 4200 1
60 Austin 4800 2
60 Pataballa 4800 2
60 Ernst 6000 4
60 Hunold 9000 5
Also, those views can be joined with other tables in other data sources.

 

3.2.2 Create Schema Store View

This view can across different data sources.

 

CREATE DATASOURCE VIEW TOP10_MOVIES_FOR_TEENS AS

  SELECT movies_from_oracle.title, user_rating.count, user_rating.min, user_rating.max, user_rating.avg

  FROM(

    SELECT iid, count(*) count, min(pref) min, max(pref) max, avg(pref) avg

    FROM TRDT.ratings ratings_from_teradata, PGRS.users users_from_postgres

    WHERE users_from_postgres.age >=13AND users_from_postgres.age <20

AND ratings_from_teradata.uid = users_from_postgres.uid

    GROUP BY ratings_from_teradata.iid

    ORDER BY avg DESC

    LIMIT20

) AS user_rating, ORCL.movies movies_from_oracle

WHERE movies_from_oracle.iid = user_rating.iid

This view can be queried like a normal table :

SELECT*FROM TOP10_MOVIES_FOR_TEENS

3.2.2 Delete View

DROP VIEW ORCL.TEEN_AGER