createView

Creates a new database view.

Liquibase supports views, but does not support materialized views.

Uses

You can use the createView Change Type to create a view on a table. A view is a virtual table that stores the result of a particular SELECT SQL statement for easy access (without altering the table). For example, joining multiple tables into a single, easy-to-query object.

Note: The Liquibase extension for Databricks extends this Change Type to support Databricks features, including STRUCT and MAP variable types.

Run createView

To run this Change Type, follow these steps:

  1. Add the Change Type to your changeset, as shown in the examples on this page.

  2. Specify any required attributes. Use the table on this page to see which ones your database requires.

  3. Deploy your changeset by running the update command:liquibase update

Available attributes

Name

Description

Required for

Supports

catalogName

Name of the catalog

all

encoding

Encoding used in the file specified in the path attribute. Default: UTF-8.

all

fullDefinition

Set to true if selectQuery is the entire view definition. Set to false if the CREATE VIEW header should be added.

all

path

Path to file containing view definition. Using the path attribute is an alternative to selectQuery.

all

relativeToChangelogFile

Specifies whether the file path is relative to the changelog file rather than looked up in the search path. Default: false.

all

remarks

A short descriptive comment

all

replaceIfExists

If true, Liquibase uses CREATE OR REPLACE syntax when creating the view. Default: false.

Available in Liquibase 1.5+.

databricks, db2, firebird, h2, hsqldb, ingres, mariadb, mssql, mysql, oracle, postgresql, sqlite, sybase

schemaName

Name of the schema

all

selectQuery

SQL for generating the view

informix

all

tblProperties

The table properties you want to specify. Specify properties using the format 'key'='value'. Separate multiple properties using commas.

Note: This attribute is only available with the Liquibase extension for Databricks. It is not available for SQL changelogs. For more information, see Databricks SQL Reference: CREATE VIEW.

databricks

viewName

Name of the view

all

all

Database support

Database

Notes

Auto Rollback

DB2/LUW

Supported

Yes

DB2/z

Supported

Yes

Derby

Supported

Yes

Firebird

Supported

Yes

Google BigQuery

Supported

Yes

H2

Supported

Yes

HyperSQL

Supported

Yes

INGRES

Supported

Yes

Informix

Supported

Yes

MariaDB

Supported

Yes

MySQL

Supported

Yes

Oracle

Supported

Yes

PostgreSQL

Supported

Yes

Snowflake

Supported

Yes

SQL Server

Supported

Yes

SQLite

Supported

Yes

Sybase

Supported

Yes

Sybase Anywhere

Supported

Yes

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:pro-snowflake="http://www.liquibase.org/xml/ns/pro-snowflake"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/pro-snowflake
        http://www.liquibase.org/xml/ns/pro-snowflake/liquibase-pro-snowflake-latest.xsd">
  <!-- Basic view -->
  <changeSet id="create-basic-view" author="examples">
    <pro-snowflake:createView viewName="ACTIVE_CUSTOMERS">
      <pro-snowflake:selectQuery>SELECT * FROM CUSTOMERS WHERE STATUS = 'ACTIVE'</pro-snowflake:selectQuery>
    </pro-snowflake:createView>
  </changeSet>
  <!-- Secure view with OR REPLACE and comment -->
  <changeSet id="create-secure-view" author="examples">
    <pro-snowflake:createView
                viewName="SALES_SUMMARY"
                replaceIfExists="true"
                secure="true"
                comment="Aggregated sales data by region">
      <pro-snowflake:selectQuery>SELECT REGION, SUM(AMOUNT) AS TOTAL FROM SALES GROUP BY REGION</pro-snowflake:selectQuery>
    </pro-snowflake:createView>
  </changeSet>
  <!-- View with row access policy -->
  <changeSet id="create-view-with-rap" author="examples">
    <pro-snowflake:createView
                viewName="REGIONAL_DATA"
                schemaName="ANALYTICS"
                comment="Region-restricted data view">
      <pro-snowflake:selectQuery>SELECT SALES_REGION, SALES_COUNTRY, REVENUE FROM SALES_DATA</pro-snowflake:selectQuery>
      <pro-snowflake:rowAccessPolicy policyName="REGION_POLICY">
        <pro-snowflake:rowAccessPolicyColumns>
          <pro-snowflake:column columnName="SALES_REGION"/>
          <pro-snowflake:column columnName="SALES_COUNTRY"/>
        </pro-snowflake:rowAccessPolicyColumns>
      </pro-snowflake:rowAccessPolicy>
    </pro-snowflake:createView>
  </changeSet>
  <!-- View with IF NOT EXISTS -->
  <changeSet id="create-view-if-not-exists" author="examples">
    <pro-snowflake:createView
                viewName="QUARTERLY_REPORT"
                ifNotExists="true">
      <pro-snowflake:selectQuery>SELECT QUARTER, SUM(SALES) FROM TRANSACTIONS GROUP BY QUARTER</pro-snowflake:selectQuery>
    </pro-snowflake:createView>
  </changeSet>
</databaseChangeLog>