docs/build/plain-html/topics/impala_create_view.html - impala - Git at Google

 <?xml version="1.0" encoding="UTF-8"?>
 <!DOCTYPE html
   PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
 <head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

 <meta name="copyright" content="(C) Copyright 2025" />
 <meta name="DC.rights.owner" content="(C) Copyright 2025" />
 <meta name="DC.Type" content="concept" />
 <meta name="DC.Title" content="CREATE VIEW Statement" />
 <meta name="DC.Relation" scheme="URI" content="../topics/impala_langref_sql.html" />
 <meta name="prodname" content="Impala" />
 <meta name="prodname" content="Impala" />
 <meta name="version" content="Impala 3.4.x" />
 <meta name="version" content="Impala 3.4.x" />
 <meta name="DC.Format" content="XHTML" />
 <meta name="DC.Identifier" content="create_view" />
 <link rel="stylesheet" type="text/css" href="../commonltr.css" />
 <title>CREATE VIEW Statement</title>
 </head>
 <body id="create_view">


   <h1 class="title topictitle1" id="ariaid-title1">CREATE VIEW Statement</h1>


   <div class="body conbody">

     <p class="p">
       The <code class="ph codeph">CREATE VIEW</code> statement lets you create a shorthand abbreviation for a
       more complicated query. The base query can involve joins, expressions, reordered columns,
       column aliases, and other SQL features that can make a query hard to understand or
       maintain.
     </p>


     <p class="p">
       Because a view is purely a logical construct (an alias for a query) with no physical data
       behind it, <code class="ph codeph">ALTER VIEW</code> only involves changes to metadata in the metastore
       database, not any data files in HDFS.
     </p>


     <p class="p">
         <strong class="ph b">Syntax:</strong>
       </p>


 <pre class="pre codeblock"><code>CREATE VIEW [IF NOT EXISTS] <var class="keyword varname">view_name</var>
     [(<var class="keyword varname">column_name</var> [COMMENT '<var class="keyword varname">column_comment</var>'][, ...])]
     [COMMENT '<var class="keyword varname">view_comment</var>']
     [TBLPROPERTIES ('<var class="keyword varname">name</var>' = '<var class="keyword varname">value</var>'[, ...])]
   AS <var class="keyword varname">select_statement</var></code></pre>

     <p class="p">
         <strong class="ph b">Statement type:</strong> DDL
       </p>


     <p class="p">
         <strong class="ph b">Usage notes:</strong>
       </p>


     <p class="p">
       The <code class="ph codeph">CREATE VIEW</code> statement can be useful in scenarios such as the
       following:
     </p>


     <ul class="ul">
       <li class="li">
         To turn even the most lengthy and complicated SQL query into a one-liner. You can issue
         simple queries against the view from applications, scripts, or interactive queries in
         <span class="keyword cmdname">impala-shell</span>. For example:
 <pre class="pre codeblock"><code>select * from <var class="keyword varname">view_name</var>;
 select * from <var class="keyword varname">view_name</var> order by c1 desc limit 10;</code></pre>
         The more complicated and hard-to-read the original query, the more benefit there is to
         simplifying the query using a view.
       </li>


       <li class="li">
         To hide the underlying table and column names, to minimize maintenance problems if those
         names change. In that case, you re-create the view using the new names, and all queries
         that use the view rather than the underlying tables keep running with no changes.
       </li>


       <li class="li">
         To experiment with optimization techniques and make the optimized queries available to
         all applications. For example, if you find a combination of <code class="ph codeph">WHERE</code>
         conditions, join order, join hints, and so on that works the best for a class of
         queries, you can establish a view that incorporates the best-performing techniques.
         Applications can then make relatively simple queries against the view, without repeating
         the complicated and optimized logic over and over. If you later find a better way to
         optimize the original query, when you re-create the view, all the applications
         immediately take advantage of the optimized base query.
       </li>


       <li class="li">
         To simplify a whole class of related queries, especially complicated queries involving
         joins between multiple tables, complicated expressions in the column list, and other SQL
         syntax that makes the query difficult to understand and debug. For example, you might
         create a view that joins several tables, filters using several <code class="ph codeph">WHERE</code>
         conditions, and selects several columns from the result set. Applications might issue
         queries against this view that only vary in their <code class="ph codeph">LIMIT</code>, <code class="ph codeph">ORDER
         BY</code>, and similar simple clauses.
       </li>

     </ul>


     <p class="p">
       For queries that require repeating complicated clauses over and over again, for example in
       the select list, <code class="ph codeph">ORDER BY</code>, and <code class="ph codeph">GROUP BY</code> clauses, you can
       use the <code class="ph codeph">WITH</code> clause as an alternative to creating a view.
     </p>


     <p class="p">
       You can optionally specify the table-level and the column-level comments as in the
       <code class="ph codeph">CREATE TABLE</code> statement.
     </p>


     <p class="p">
         <strong class="ph b">Complex type considerations:</strong>
       </p>


     <p class="p">
         For tables containing complex type columns (<code class="ph codeph">ARRAY</code>,
         <code class="ph codeph">STRUCT</code>, or <code class="ph codeph">MAP</code>), you typically use join queries to
         refer to the complex values. You can use views to hide the join notation, making such
         tables seem like traditional denormalized tables, and making those tables queryable by
         business intelligence tools that do not have built-in support for those complex types.
         See <a class="xref" href="../shared/../topics/impala_complex_types.html#complex_types_views">Accessing Complex Type Data in Flattened Form Using Views</a> for details.
       </p>


     <p class="p">
         Because you cannot directly issue <code class="ph codeph">SELECT <var class="keyword varname">col_name</var></code>
         against a column of complex type, you cannot use a view or a <code class="ph codeph">WITH</code>
         clause to <span class="q">"rename"</span> a column by selecting it with a column alias.
       </p>


     <p class="p">
         If you connect to different Impala nodes within an <span class="keyword cmdname">impala-shell</span>
         session for load-balancing purposes, you can enable the <code class="ph codeph">SYNC_DDL</code> query
         option to make each DDL statement wait before returning, until the new or changed
         metadata has been received by all the Impala nodes. See
         <a class="xref" href="../shared/../topics/impala_sync_ddl.html#sync_ddl">SYNC_DDL Query Option</a> for details.
       </p>


     <p class="p">
         <strong class="ph b">Security considerations:</strong>
       </p>


     <p class="p">
         If these statements in your environment contain sensitive literal values such as credit
         card numbers or tax identifiers, Impala can redact this sensitive information when
         displaying the statements in log files and other administrative contexts. See
         <span class="xref">the documentation for your Apache Hadoop distribution</span> for details.
       </p>


     <p class="p">
         <strong class="ph b">Cancellation:</strong> Cannot be cancelled.
       </p>


     <p class="p">
         <strong class="ph b">HDFS permissions:</strong> This statement does not touch any HDFS files or directories,
         therefore no HDFS permissions are required.
       </p>


     <p class="p">
         <strong class="ph b">Examples:</strong>
       </p>


 <pre class="pre codeblock"><code>-- Create a view that is exactly the same as the underlying table.
 CREATE VIEW v1 AS SELECT * FROM t1;

 -- Create a view that includes only certain columns from the underlying table.
 CREATE VIEW v2 AS SELECT c1, c3, c7 FROM t1;

 -- Create a view that filters the values from the underlying table.
 CREATE VIEW v3 AS SELECT DISTINCT c1, c3, c7 FROM t1 WHERE c1 IS NOT NULL AND c5 &gt; 0;

 -- Create a view that that reorders and renames columns from the underlying table.
 CREATE VIEW v4 AS SELECT c4 AS last_name, c6 AS address, c2 AS birth_date FROM t1;

 -- Create a view that runs functions to convert or transform certain columns.
 CREATE VIEW v5 AS SELECT c1, CAST(c3 AS STRING) c3, CONCAT(c4,c5) c5, TRIM(c6) c6, "Constant" c8 FROM t1;

 -- Create a view that hides the complexity of a view query.
 CREATE VIEW v6 AS SELECT t1.c1, t2.c2 FROM t1 JOIN t2 ON t1.id = t2.id;

 -- Create a view with a column comment and a table comment.
 CREATE VIEW v7 (c1 COMMENT 'Comment for c1', c2) COMMENT 'Comment for v7' AS SELECT t1.c1, t1.c2 FROM t1;

 -- Create a view with tblproperties.
 CREATE VIEW v7 (c1 , c2) TBLPROPERTIES ('tblp1' = '1', 'tblp2' = '2') AS SELECT t1.c1, t1.c2 FROM t1;
 </code></pre>

     <p class="p">
         <strong class="ph b">Related information:</strong>
       </p>


     <p class="p">
       <a class="xref" href="impala_views.html#views">Overview of Impala Views</a>,
       <a class="xref" href="impala_alter_view.html#alter_view">ALTER VIEW Statement</a>,
       <a class="xref" href="impala_drop_view.html#drop_view">DROP VIEW Statement</a>
     </p>


   </div>


 <div class="related-links">
 <div class="familylinks">
 <div class="parentlink"><strong>Parent topic:</strong> <a class="link" href="../topics/impala_langref_sql.html">Impala SQL Statements</a></div>
 </div>
 </div></body>
 </html>
	<?xml version="1.0" encoding="UTF-8"?>
	<!DOCTYPE html
	PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
	<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
	<head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

	<meta name="copyright" content="(C) Copyright 2025" />
	<meta name="DC.rights.owner" content="(C) Copyright 2025" />
	<meta name="DC.Type" content="concept" />
	<meta name="DC.Title" content="CREATE VIEW Statement" />
	<meta name="DC.Relation" scheme="URI" content="../topics/impala_langref_sql.html" />
	<meta name="prodname" content="Impala" />
	<meta name="prodname" content="Impala" />
	<meta name="version" content="Impala 3.4.x" />
	<meta name="version" content="Impala 3.4.x" />
	<meta name="DC.Format" content="XHTML" />
	<meta name="DC.Identifier" content="create_view" />
	<link rel="stylesheet" type="text/css" href="../commonltr.css" />
	<title>CREATE VIEW Statement</title>
	</head>
	<body id="create_view">


	<h1 class="title topictitle1" id="ariaid-title1">CREATE VIEW Statement</h1>






	<div class="body conbody">

	<p class="p">
	The <code class="ph codeph">CREATE VIEW</code> statement lets you create a shorthand abbreviation for a
	more complicated query. The base query can involve joins, expressions, reordered columns,
	column aliases, and other SQL features that can make a query hard to understand or
	maintain.
	</p>


	<p class="p">
	Because a view is purely a logical construct (an alias for a query) with no physical data
	behind it, <code class="ph codeph">ALTER VIEW</code> only involves changes to metadata in the metastore
	database, not any data files in HDFS.
	</p>


	<p class="p">
	<strong class="ph b">Syntax:</strong>
	</p>


	<pre class="pre codeblock"><code>CREATE VIEW [IF NOT EXISTS] <var class="keyword varname">view_name</var>
	[(<var class="keyword varname">column_name</var> [COMMENT '<var class="keyword varname">column_comment</var>'][, ...])]
	[COMMENT '<var class="keyword varname">view_comment</var>']
	[TBLPROPERTIES ('<var class="keyword varname">name</var>' = '<var class="keyword varname">value</var>'[, ...])]
	AS <var class="keyword varname">select_statement</var></code></pre>

	<p class="p">
	<strong class="ph b">Statement type:</strong> DDL
	</p>


	<p class="p">
	<strong class="ph b">Usage notes:</strong>
	</p>


	<p class="p">
	The <code class="ph codeph">CREATE VIEW</code> statement can be useful in scenarios such as the
	following:
	</p>


	<ul class="ul">
	<li class="li">
	To turn even the most lengthy and complicated SQL query into a one-liner. You can issue
	simple queries against the view from applications, scripts, or interactive queries in
	<span class="keyword cmdname">impala-shell</span>. For example:
	<pre class="pre codeblock"><code>select * from <var class="keyword varname">view_name</var>;
	select * from <var class="keyword varname">view_name</var> order by c1 desc limit 10;</code></pre>
	The more complicated and hard-to-read the original query, the more benefit there is to
	simplifying the query using a view.
	</li>


	<li class="li">
	To hide the underlying table and column names, to minimize maintenance problems if those
	names change. In that case, you re-create the view using the new names, and all queries
	that use the view rather than the underlying tables keep running with no changes.
	</li>


	<li class="li">
	To experiment with optimization techniques and make the optimized queries available to
	all applications. For example, if you find a combination of <code class="ph codeph">WHERE</code>
	conditions, join order, join hints, and so on that works the best for a class of
	queries, you can establish a view that incorporates the best-performing techniques.
	Applications can then make relatively simple queries against the view, without repeating
	the complicated and optimized logic over and over. If you later find a better way to
	optimize the original query, when you re-create the view, all the applications
	immediately take advantage of the optimized base query.
	</li>


	<li class="li">
	To simplify a whole class of related queries, especially complicated queries involving
	joins between multiple tables, complicated expressions in the column list, and other SQL
	syntax that makes the query difficult to understand and debug. For example, you might
	create a view that joins several tables, filters using several <code class="ph codeph">WHERE</code>
	conditions, and selects several columns from the result set. Applications might issue
	queries against this view that only vary in their <code class="ph codeph">LIMIT</code>, <code class="ph codeph">ORDER
	BY</code>, and similar simple clauses.
	</li>

	</ul>


	<p class="p">
	For queries that require repeating complicated clauses over and over again, for example in
	the select list, <code class="ph codeph">ORDER BY</code>, and <code class="ph codeph">GROUP BY</code> clauses, you can
	use the <code class="ph codeph">WITH</code> clause as an alternative to creating a view.
	</p>


	<p class="p">
	You can optionally specify the table-level and the column-level comments as in the
	<code class="ph codeph">CREATE TABLE</code> statement.
	</p>


	<p class="p">
	<strong class="ph b">Complex type considerations:</strong>
	</p>


	<p class="p">
	For tables containing complex type columns (<code class="ph codeph">ARRAY</code>,
	<code class="ph codeph">STRUCT</code>, or <code class="ph codeph">MAP</code>), you typically use join queries to
	refer to the complex values. You can use views to hide the join notation, making such
	tables seem like traditional denormalized tables, and making those tables queryable by
	business intelligence tools that do not have built-in support for those complex types.
	See <a class="xref" href="../shared/../topics/impala_complex_types.html#complex_types_views">Accessing Complex Type Data in Flattened Form Using Views</a> for details.
	</p>


	<p class="p">
	Because you cannot directly issue <code class="ph codeph">SELECT <var class="keyword varname">col_name</var></code>
	against a column of complex type, you cannot use a view or a <code class="ph codeph">WITH</code>
	clause to <span class="q">"rename"</span> a column by selecting it with a column alias.
	</p>


	<p class="p">
	If you connect to different Impala nodes within an <span class="keyword cmdname">impala-shell</span>
	session for load-balancing purposes, you can enable the <code class="ph codeph">SYNC_DDL</code> query
	option to make each DDL statement wait before returning, until the new or changed
	metadata has been received by all the Impala nodes. See
	<a class="xref" href="../shared/../topics/impala_sync_ddl.html#sync_ddl">SYNC_DDL Query Option</a> for details.
	</p>


	<p class="p">
	<strong class="ph b">Security considerations:</strong>
	</p>


	<p class="p">
	If these statements in your environment contain sensitive literal values such as credit
	card numbers or tax identifiers, Impala can redact this sensitive information when
	displaying the statements in log files and other administrative contexts. See
	<span class="xref">the documentation for your Apache Hadoop distribution</span> for details.
	</p>


	<p class="p">
	<strong class="ph b">Cancellation:</strong> Cannot be cancelled.
	</p>


	<p class="p">
	<strong class="ph b">HDFS permissions:</strong> This statement does not touch any HDFS files or directories,
	therefore no HDFS permissions are required.
	</p>


	<p class="p">
	<strong class="ph b">Examples:</strong>
	</p>


	<pre class="pre codeblock"><code>-- Create a view that is exactly the same as the underlying table.
	CREATE VIEW v1 AS SELECT * FROM t1;

	-- Create a view that includes only certain columns from the underlying table.
	CREATE VIEW v2 AS SELECT c1, c3, c7 FROM t1;

	-- Create a view that filters the values from the underlying table.
	CREATE VIEW v3 AS SELECT DISTINCT c1, c3, c7 FROM t1 WHERE c1 IS NOT NULL AND c5 > 0;

	-- Create a view that that reorders and renames columns from the underlying table.
	CREATE VIEW v4 AS SELECT c4 AS last_name, c6 AS address, c2 AS birth_date FROM t1;

	-- Create a view that runs functions to convert or transform certain columns.
	CREATE VIEW v5 AS SELECT c1, CAST(c3 AS STRING) c3, CONCAT(c4,c5) c5, TRIM(c6) c6, "Constant" c8 FROM t1;

	-- Create a view that hides the complexity of a view query.
	CREATE VIEW v6 AS SELECT t1.c1, t2.c2 FROM t1 JOIN t2 ON t1.id = t2.id;

	-- Create a view with a column comment and a table comment.
	CREATE VIEW v7 (c1 COMMENT 'Comment for c1', c2) COMMENT 'Comment for v7' AS SELECT t1.c1, t1.c2 FROM t1;

	-- Create a view with tblproperties.
	CREATE VIEW v7 (c1 , c2) TBLPROPERTIES ('tblp1' = '1', 'tblp2' = '2') AS SELECT t1.c1, t1.c2 FROM t1;
	</code></pre>

	<p class="p">
	<strong class="ph b">Related information:</strong>
	</p>


	<p class="p">
	<a class="xref" href="impala_views.html#views">Overview of Impala Views</a>,
	<a class="xref" href="impala_alter_view.html#alter_view">ALTER VIEW Statement</a>,
	<a class="xref" href="impala_drop_view.html#drop_view">DROP VIEW Statement</a>
	</p>


	</div>


	<div class="related-links">
	<div class="familylinks">
	<div class="parentlink"><strong>Parent topic:</strong> <a class="link" href="../topics/impala_langref_sql.html">Impala SQL Statements</a></div>
	</div>
	</div></body>
	</html>