createView

Creates a new database view.

Note: 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:
    4. 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

Examples

Without parenthesis:

--liquibase formatted sql

--changeset liquibase-docs:createView-example
CREATE  VIEW  cat.v_person  AS  select  id,  
 name  from  person  where  id  >  10;

With parenthesis (Liquibase 4.25.1+):

--liquibase formatted sql

--changeset liquibase-docs:createView-example
CREATE  VIEW  cat.v_person  AS(select  id,  
 name  from  person  where  id  >  10;)

General example:

databaseChangeLog:
-  changeSet:
    id:  createView-example
    author:  liquibase-docs
    changes:
    -  createView:
        catalogName:  cat
        encoding:  UTF-8
        fullDefinition:  false
        path:  A String
        relativeToChangelogFile:  true
        remarks:  A String
        replaceIfExists:  false
        schemaName:  public
        selectQuery:  select id, name from person where id > 10
        viewName:  v_person

Databricks example:

databaseChangeLog:
  - changeSet:
      id: 2
      author: your.name
      changes:
        - createView:
            selectQuery: SELECT id, first_name, last_name, email FROM authors
            viewName: test_view
            tblProperties: 'this.is.my.key'=12,'this.is.my.key2'=true
      rollback:
        - dropView:
            viewName: test_view

General example:

{
    "databaseChangeLog": [
        {
            "changeSet": {
                "id": "createView-example",
                "author": "liquibase-docs",
                "changes": [
                    {
                        "createView": {
                            "catalogName": "cat",
                            "encoding": "UTF-8",
                            "fullDefinition": false,
                            "path": "A String",
                            "relativeToChangelogFile": true,
                            "remarks": "A String",
                            "replaceIfExists": false,
                            "schemaName": "public",
                            "selectQuery": "select id, name from person where id > 10",
                            "viewName": "v_person"
                        }
                    }
                ]
            }
        }
    ]
}

Databricks example:

{
  "databaseChangeLog": [
    {
      "changeSet": {
        "id": "2",
        "author": "your.name",
        "changes": [
          {
            "createView": {
              "selectQuery": "SELECT id, first_name, last_name, email FROM authors",
              "viewName": "test_view",
              "tblProperties": "'this.is.my.key'=12,'this.is.my.key2'=true"
            }
          }
        ],
        "rollback": [
          {
            "dropView": {
              "viewName": "test_view"
            }
          }
        ]
      }
    }
  ]
}

To apply the selectQuery value in XML, set it as a body of the tag: 

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    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/dbchangelog-ext
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro
        http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">

    <changeSet  author="liquibase-docs"  id="createView-example">
        <createView  catalogName="cat"
            encoding="UTF-8"
            fullDefinition="false"
            remarks="A String"
            replaceIfExists="false"
            schemaName="public"
            viewName="v_person">select id, name from person where id > 10</createView>
    </changeSet>

</databaseChangeLog>

Using the path attribute is an alternative to selectQuery:

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    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/dbchangelog-ext
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro
        http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">

    <changeSet  author="liquibase-docs"  id="createView-example">
        <createView  catalogName="cat"
            encoding="UTF-8"
	        path="A String"
	        relativeToChangelogFile="true"
	        remarks="A String"
	        replaceIfExists="false"
	        schemaName="public"
	        viewName="v_person"></createView>
    </changeSet>

</databaseChangeLog>

Databricks example:

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:databricks="http://www.liquibase.org/xml/ns/databricks"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    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/databricks
        http://www.liquibase.org/xml/ns/databricks/liquibase-databricks-latest.xsd
        http://www.liquibase.org/xml/ns/pro
        http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">

    <changeSet id="2" author="your.name">
        <databricks:createView viewName="test_view" tblProperties="'external.location'='s3://mybucket/myview','this.is.my.key'=12,'this.is.my.key2'=true">
            SELECT id, first_name, last_name, email FROM authors
        </databricks:createView>

        <rollback>
            <dropView viewName="test_view" />
        </rollback>
    </changeSet>

</databaseChangeLog>

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

Related links