Creating Views Procedure - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantage™
The following procedure shows how to create views using BTEQ:
  1. Log on to Teradata Vantage as user DBADMIN using BTEQ.
  2. Create one or more views using the CREATE VIEW statement.
    For example:
    CREATE VIEW database_name.view_name (column_name [,...] ) AS SELECT_clause;
    database_name
    The name of the database where the view will be created if it is different from the current database.
    Recommendation: The database should be a Views Database or a database located within a Views Database. See Database Creation.
    If database_name is not specified, the system creates the view in the default database for the current session.
    view_name
    The name of the view to be created.
    column_name
    The name of a view column. If more than one column is specified, list their names in the order in which each column is to be displayed for the view.
    LOCKING_clause
    The specified lock is placed on the underlying base table set each time the view is referenced in an SQL statement.
    Recommendation: Create views with a LOCKING ... FOR ACCESS modifier to allow concurrent access of the data to read-only users and users who will modify the data. This prevents deadlocks and increases performance.
    ACCESS locking is a tool that can be used to reduce the isolation of a user request allowing that request to access valid, but uncommitted data that is subject to change.
    This allows users that are interested in a broad, statistical snapshot of the data, but that do not require precise results, to attain access to data while modifications to that data are occurring. This can greatly improve performance, because those users do not have to wait unnecessarily for the write operation to complete.
    SELECT_clause
    The SELECT statement which obtains the data for the view. The following are some of the options and clauses you can use:
    Option Description
    DISTINCT Returns only one row from any set of duplicate rows in the SELECT result.
    ALL Returns all rows, including duplicate rows in the SELECT result. This is the default.
    TOP n or TOP m PERCENT [WITH TIES] Restricts a view to only n rows or m percent of the rows in an underlying base table. If you do not specify an ORDER BY clause, then a TOP n clause only specifies that any n base table rows be returned, not the TOP n.

    This option provides a fast method to obtain a restricted, non-statistically random sample of table rows.

    FROM Specifies the set of base tables or views from which data for the view are selected.
    WHERE Specifies a conditional expression by which rows for the view are selected.
    GROUP BY Groups result rows by the values in one or more columns or by various extended GROUP BY operations on specified column expressions.
    HAVING Specifies a conditional expression by which the groups defined by a GROUP BY clause are selected.
    QUALIFY Specifies a conditional ordered analytical function by which rows for the view are selected.
    WITH CHECK OPTION Integrity constraint option that restricts the rows in the table that can be affected by an INSERT or UPDATE statement to those defined by the WHERE clause. This option only pertains to updatable views.

    Recommendation: Specify a WITH CHECK OPTION clause in all your updatable view definitions to prevent unauthorized modification of data rows.

    ORDER BY Specifies the order in which result rows are to be sorted. You can only specify an ORDER BY clause for a view definition if you also specify either the TOP n or the TOP m PERCENT option.
    The example lists the major options for creating a view. You can modify your view definition by using the REPLACE VIEW statement. To automate the creation of views by using BTEQ scripts, see Using BTEQ Scripts to Create Database Objects.
  3. Set privileges on the view as follows:
    • Provide general users read-only access so they can use the view to query data.
    • Provide privileged users read and write access so they can use the view to update data in the underlying tables.