| |
| <!DOCTYPE html> |
| <!--[if lt IE 7]> <html class="no-js lt-ie9 lt-ie8 lt-ie7"> <![endif]--> |
| <!--[if IE 7]> <html class="no-js lt-ie9 lt-ie8"> <![endif]--> |
| <!--[if IE 8]> <html class="no-js lt-ie9"> <![endif]--> |
| <!--[if gt IE 8]><!--> <html class="no-js"> <!--<![endif]--> |
| <head> |
| <meta charset="utf-8"> |
| <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1"> |
| <title>ALTER TABLE - Spark 3.0.0-preview2 Documentation</title> |
| |
| |
| |
| |
| <link rel="stylesheet" href="css/bootstrap.min.css"> |
| <style> |
| body { |
| padding-top: 60px; |
| padding-bottom: 40px; |
| } |
| </style> |
| <meta name="viewport" content="width=device-width"> |
| <link rel="stylesheet" href="css/bootstrap-responsive.min.css"> |
| <link rel="stylesheet" href="css/main.css"> |
| |
| <script src="js/vendor/modernizr-2.6.1-respond-1.1.0.min.js"></script> |
| |
| <link rel="stylesheet" href="css/pygments-default.css"> |
| |
| |
| <!-- Google analytics script --> |
| <script type="text/javascript"> |
| var _gaq = _gaq || []; |
| _gaq.push(['_setAccount', 'UA-32518208-2']); |
| _gaq.push(['_trackPageview']); |
| |
| (function() { |
| var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true; |
| ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js'; |
| var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s); |
| })(); |
| </script> |
| |
| |
| </head> |
| <body> |
| <!--[if lt IE 7]> |
| <p class="chromeframe">You are using an outdated browser. <a href="https://browsehappy.com/">Upgrade your browser today</a> or <a href="http://www.google.com/chromeframe/?redirect=true">install Google Chrome Frame</a> to better experience this site.</p> |
| <![endif]--> |
| |
| <!-- This code is taken from http://twitter.github.com/bootstrap/examples/hero.html --> |
| |
| <div class="navbar navbar-fixed-top" id="topbar"> |
| <div class="navbar-inner"> |
| <div class="container"> |
| <div class="brand"><a href="index.html"> |
| <img src="img/spark-logo-hd.png" style="height:50px;"/></a><span class="version">3.0.0-preview2</span> |
| </div> |
| <ul class="nav"> |
| <!--TODO(andyk): Add class="active" attribute to li some how.--> |
| <li><a href="index.html">Overview</a></li> |
| |
| <li class="dropdown"> |
| <a href="#" class="dropdown-toggle" data-toggle="dropdown">Programming Guides<b class="caret"></b></a> |
| <ul class="dropdown-menu"> |
| <li><a href="quick-start.html">Quick Start</a></li> |
| <li><a href="rdd-programming-guide.html">RDDs, Accumulators, Broadcasts Vars</a></li> |
| <li><a href="sql-programming-guide.html">SQL, DataFrames, and Datasets</a></li> |
| <li><a href="structured-streaming-programming-guide.html">Structured Streaming</a></li> |
| <li><a href="streaming-programming-guide.html">Spark Streaming (DStreams)</a></li> |
| <li><a href="ml-guide.html">MLlib (Machine Learning)</a></li> |
| <li><a href="graphx-programming-guide.html">GraphX (Graph Processing)</a></li> |
| <li><a href="sparkr.html">SparkR (R on Spark)</a></li> |
| </ul> |
| </li> |
| |
| <li class="dropdown"> |
| <a href="#" class="dropdown-toggle" data-toggle="dropdown">API Docs<b class="caret"></b></a> |
| <ul class="dropdown-menu"> |
| <li><a href="api/scala/index.html#org.apache.spark.package">Scala</a></li> |
| <li><a href="api/java/index.html">Java</a></li> |
| <li><a href="api/python/index.html">Python</a></li> |
| <li><a href="api/R/index.html">R</a></li> |
| <li><a href="api/sql/index.html">SQL, Built-in Functions</a></li> |
| </ul> |
| </li> |
| |
| <li class="dropdown"> |
| <a href="#" class="dropdown-toggle" data-toggle="dropdown">Deploying<b class="caret"></b></a> |
| <ul class="dropdown-menu"> |
| <li><a href="cluster-overview.html">Overview</a></li> |
| <li><a href="submitting-applications.html">Submitting Applications</a></li> |
| <li class="divider"></li> |
| <li><a href="spark-standalone.html">Spark Standalone</a></li> |
| <li><a href="running-on-mesos.html">Mesos</a></li> |
| <li><a href="running-on-yarn.html">YARN</a></li> |
| <li><a href="running-on-kubernetes.html">Kubernetes</a></li> |
| </ul> |
| </li> |
| |
| <li class="dropdown"> |
| <a href="api.html" class="dropdown-toggle" data-toggle="dropdown">More<b class="caret"></b></a> |
| <ul class="dropdown-menu"> |
| <li><a href="configuration.html">Configuration</a></li> |
| <li><a href="monitoring.html">Monitoring</a></li> |
| <li><a href="tuning.html">Tuning Guide</a></li> |
| <li><a href="job-scheduling.html">Job Scheduling</a></li> |
| <li><a href="security.html">Security</a></li> |
| <li><a href="hardware-provisioning.html">Hardware Provisioning</a></li> |
| <li><a href="migration-guide.html">Migration Guide</a></li> |
| <li class="divider"></li> |
| <li><a href="building-spark.html">Building Spark</a></li> |
| <li><a href="https://spark.apache.org/contributing.html">Contributing to Spark</a></li> |
| <li><a href="https://spark.apache.org/third-party-projects.html">Third Party Projects</a></li> |
| </ul> |
| </li> |
| </ul> |
| <!--<p class="navbar-text pull-right"><span class="version-text">v3.0.0-preview2</span></p>--> |
| </div> |
| </div> |
| </div> |
| |
| <div class="container-wrapper"> |
| |
| |
| |
| <div class="left-menu-wrapper"> |
| <div class="left-menu"> |
| <h3><a href="sql-programming-guide.html">Spark SQL Guide</a></h3> |
| |
| <ul> |
| |
| <li> |
| <a href="sql-getting-started.html"> |
| |
| Getting Started |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-data-sources.html"> |
| |
| Data Sources |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-performance-tuning.html"> |
| |
| Performance Tuning |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-distributed-sql-engine.html"> |
| |
| Distributed SQL Engine |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-pyspark-pandas-with-arrow.html"> |
| |
| PySpark Usage Guide for Pandas with Apache Arrow |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-migration-old.html"> |
| |
| Migration Guide |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref.html"> |
| |
| SQL Reference |
| |
| </a> |
| </li> |
| |
| |
| |
| <ul> |
| |
| <li> |
| <a href="sql-ref-datatypes.html"> |
| |
| Data Types |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref-null-semantics.html"> |
| |
| Null Semantics |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref-nan-semantics.html"> |
| |
| NaN Semantics |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref-syntax.html"> |
| |
| SQL Syntax |
| |
| </a> |
| </li> |
| |
| |
| |
| <ul> |
| |
| <li> |
| <a href="sql-ref-syntax-ddl.html"> |
| |
| Data Definition Statements |
| |
| </a> |
| </li> |
| |
| |
| |
| <ul> |
| |
| <li> |
| <a href="sql-ref-syntax-ddl-alter-database.html"> |
| |
| ALTER DATABASE |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref-syntax-ddl-alter-table.html"> |
| |
| <b>ALTER TABLE</b> |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref-syntax-ddl-alter-view.html"> |
| |
| ALTER VIEW |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref-syntax-ddl-create-database.html"> |
| |
| CREATE DATABASE |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref-syntax-ddl-create-function.html"> |
| |
| CREATE FUNCTION |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref-syntax-ddl-create-table.html"> |
| |
| CREATE TABLE |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref-syntax-ddl-create-view.html"> |
| |
| CREATE VIEW |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref-syntax-ddl-drop-database.html"> |
| |
| DROP DATABASE |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref-syntax-ddl-drop-function.html"> |
| |
| DROP FUNCTION |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref-syntax-ddl-drop-table.html"> |
| |
| DROP TABLE |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref-syntax-ddl-drop-view.html"> |
| |
| DROP VIEW |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref-syntax-ddl-truncate-table.html"> |
| |
| TRUNCATE TABLE |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref-syntax-ddl-repair-table.html"> |
| |
| REPAIR TABLE |
| |
| </a> |
| </li> |
| |
| |
| |
| </ul> |
| |
| |
| |
| <li> |
| <a href="sql-ref-syntax-dml.html"> |
| |
| Data Manipulation Statements |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref-syntax-qry.html"> |
| |
| Data Retrieval(Queries) |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref-syntax-aux.html"> |
| |
| Auxilarry Statements |
| |
| </a> |
| </li> |
| |
| |
| |
| </ul> |
| |
| |
| |
| <li> |
| <a href="sql-ref-functions.html"> |
| |
| Functions |
| |
| </a> |
| </li> |
| |
| |
| |
| <li> |
| <a href="sql-ref-arithmetic-ops.html"> |
| |
| Arthmetic operations |
| |
| </a> |
| </li> |
| |
| |
| |
| </ul> |
| |
| |
| |
| </ul> |
| |
| </div> |
| </div> |
| |
| <input id="nav-trigger" class="nav-trigger" checked type="checkbox"> |
| <label for="nav-trigger"></label> |
| <div class="content-with-sidebar" id="content"> |
| |
| <h1 class="title">ALTER TABLE</h1> |
| |
| |
| <h3 id="description">Description</h3> |
| <p><code class="highlighter-rouge">ALTER TABLE</code> statement changes the schema or properties of a table.</p> |
| |
| <h3 id="rename">RENAME</h3> |
| <p><code class="highlighter-rouge">ALTER TABLE RENAME</code> statement changes the table name of an existing table in the database.</p> |
| |
| <h4 id="syntax">Syntax</h4> |
| |
| <figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">table_identifier</span> <span class="k">RENAME</span> <span class="k">TO</span> <span class="n">table_identifier</span> |
| |
| <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">table_identifier</span> <span class="n">partition_spec</span> <span class="k">RENAME</span> <span class="k">TO</span> <span class="n">partition_spec</span></code></pre></figure> |
| |
| <h4 id="parameters">Parameters</h4> |
| <dl> |
| <dt><code><em>table_identifier</em></code></dt> |
| <dd> |
| Specifies a table name, which may be optionally qualified with a database name.<br /><br /> |
| <b>Syntax:</b> |
| <code> |
| [ database_name. ] table_name |
| </code> |
| </dd> |
| </dl> |
| |
| <dl> |
| <dt><code><em>partition_spec</em></code></dt> |
| <dd> |
| Partition to be renamed. <br /><br /> |
| <b>Syntax:</b> |
| <code> |
| PARTITION ( partition_col_name = partition_col_val [ , ... ] ) |
| </code> |
| </dd> |
| </dl> |
| |
| <h3 id="add-columns">ADD COLUMNS</h3> |
| <p><code class="highlighter-rouge">ALTER TABLE ADD COLUMNS</code> statement adds mentioned columns to an existing table.</p> |
| |
| <h4 id="syntax-1">Syntax</h4> |
| |
| <figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">table_identifier</span> <span class="k">ADD</span> <span class="n">COLUMNS</span> <span class="p">(</span> <span class="n">col_spec</span> <span class="p">[</span> <span class="p">,</span> <span class="n">col_spec</span> <span class="p">...</span> <span class="p">]</span> <span class="p">)</span></code></pre></figure> |
| |
| <h4 id="parameters-1">Parameters</h4> |
| <dl> |
| <dt><code><em>table_identifier</em></code></dt> |
| <dd> |
| Specifies a table name, which may be optionally qualified with a database name.<br /><br /> |
| <b>Syntax:</b> |
| <code> |
| [ database_name. ] table_name |
| </code> |
| </dd> |
| </dl> |
| |
| <dl> |
| <dt><code><em>COLUMNS ( col_spec )</em></code></dt> |
| <dd>Specifies the columns to be added to be renamed.</dd> |
| </dl> |
| |
| <h3 id="set-and-unset">SET AND UNSET</h3> |
| |
| <h4 id="set-table-properties">SET TABLE PROPERTIES</h4> |
| <p><code class="highlighter-rouge">ALTER TABLE SET</code> command is used for setting the table properties. If a particular property was already set, |
| this overrides the old value with the new one.</p> |
| |
| <p><code class="highlighter-rouge">ALTER TABLE UNSET</code> is used to drop the table property.</p> |
| |
| <h5 id="syntax-2">Syntax</h5> |
| |
| <figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="c1">--Set Table Properties </span> |
| <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">table_identifier</span> <span class="k">SET</span> <span class="n">TBLPROPERTIES</span> <span class="p">(</span> <span class="n">key1</span> <span class="o">=</span> <span class="n">val1</span><span class="p">,</span> <span class="n">key2</span> <span class="o">=</span> <span class="n">val2</span><span class="p">,</span> <span class="p">...</span> <span class="p">)</span> |
| |
| <span class="c1">--Unset Table Properties</span> |
| <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">table_identifier</span> <span class="n">UNSET</span> <span class="n">TBLPROPERTIES</span> <span class="p">[</span> <span class="n">IF</span> <span class="k">EXISTS</span> <span class="p">]</span> <span class="p">(</span> <span class="n">key1</span><span class="p">,</span> <span class="n">key2</span><span class="p">,</span> <span class="p">...</span> <span class="p">)</span> |
| </code></pre></figure> |
| |
| <h4 id="set-serde">SET SERDE</h4> |
| <p><code class="highlighter-rouge">ALTER TABLE SET</code> command is used for setting the SERDE or SERDE properties in Hive tables. If a particular property was already set,<br /> |
| this overrides the old value with the new one.</p> |
| |
| <h5 id="syntax-3">Syntax</h5> |
| |
| <figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="c1">--Set SERDE Properties</span> |
| <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">table_identifier</span> <span class="p">[</span> <span class="n">partition_spec</span> <span class="p">]</span> |
| <span class="k">SET</span> <span class="n">SERDEPROPERTIES</span> <span class="p">(</span> <span class="n">key1</span> <span class="o">=</span> <span class="n">val1</span><span class="p">,</span> <span class="n">key2</span> <span class="o">=</span> <span class="n">val2</span><span class="p">,</span> <span class="p">...</span> <span class="p">)</span> |
| |
| <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">table_identifier</span> <span class="p">[</span> <span class="n">partition_spec</span> <span class="p">]</span> <span class="k">SET</span> <span class="n">SERDE</span> <span class="n">serde_class_name</span> |
| <span class="p">[</span> <span class="k">WITH</span> <span class="n">SERDEPROPERTIES</span> <span class="p">(</span> <span class="n">key1</span> <span class="o">=</span> <span class="n">val1</span><span class="p">,</span> <span class="n">key2</span> <span class="o">=</span> <span class="n">val2</span><span class="p">,</span> <span class="p">...</span> <span class="p">)</span> <span class="p">]</span></code></pre></figure> |
| |
| <h4 id="set-location-and-set-file-format">SET LOCATION And SET FILE FORMAT</h4> |
| <p><code class="highlighter-rouge">ALTER TABLE SET</code> command can also be used for changing the file location and file format for |
| exsisting tables.</p> |
| |
| <h5 id="syntax-4">Syntax</h5> |
| |
| <figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="c1">--Changing File Format</span> |
| <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">table_identifier</span> <span class="p">[</span> <span class="n">partition_spec</span> <span class="p">]</span> <span class="k">SET</span> <span class="n">FILEFORMAT</span> <span class="n">file_format</span> |
| |
| <span class="c1">--Changing File Location</span> |
| <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">table_identifier</span> <span class="p">[</span> <span class="n">partition_spec</span> <span class="p">]</span> <span class="k">SET</span> <span class="k">LOCATION</span> <span class="s1">'new_location'</span></code></pre></figure> |
| |
| <h4 id="parameters-2">Parameters</h4> |
| <dl> |
| <dt><code><em>table_identifier</em></code></dt> |
| <dd> |
| Specifies a table name, which may be optionally qualified with a database name.<br /><br /> |
| <b>Syntax:</b> |
| <code> |
| [ database_name. ] table_name |
| </code> |
| </dd> |
| </dl> |
| |
| <dl> |
| <dt><code><em>partition_spec</em></code></dt> |
| <dd> |
| Specifies the partition on which the property has to be set.<br /><br /> |
| <b>Syntax:</b> |
| <code> |
| PARTITION ( partition_col_name = partition_col_val [ , ... ] ) |
| </code> |
| </dd> |
| </dl> |
| |
| <dl> |
| <dt><code><em>SERDEPROPERTIES ( key1 = val1, key2 = val2, ... )</em></code></dt> |
| <dd>Specifies the SERDE properties to be set.</dd> |
| </dl> |
| |
| <h3 id="examples">Examples</h3> |
| |
| <figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="c1">--RENAME table </span> |
| <span class="k">DESC</span> <span class="n">student</span><span class="p">;</span> |
| <span class="o">+</span><span class="c1">--------------------------+------------+----------+--+</span> |
| <span class="o">|</span> <span class="n">col_name</span> <span class="o">|</span> <span class="n">data_type</span> <span class="o">|</span> <span class="k">comment</span> <span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+------------+----------+--+</span> |
| <span class="o">|</span> <span class="n">name</span> <span class="o">|</span> <span class="n">string</span> <span class="o">|</span> <span class="k">NULL</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="n">rollno</span> <span class="o">|</span> <span class="nb">int</span> <span class="o">|</span> <span class="k">NULL</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="n">age</span> <span class="o">|</span> <span class="nb">int</span> <span class="o">|</span> <span class="k">NULL</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="o">#</span> <span class="n">Partition</span> <span class="n">Information</span> <span class="o">|</span> <span class="o">|</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="o">#</span> <span class="n">col_name</span> <span class="o">|</span> <span class="n">data_type</span> <span class="o">|</span> <span class="k">comment</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="n">age</span> <span class="o">|</span> <span class="nb">int</span> <span class="o">|</span> <span class="k">NULL</span> <span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+------------+----------+--+</span> |
| |
| <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">Student</span> <span class="k">RENAME</span> <span class="k">TO</span> <span class="n">StudentInfo</span><span class="p">;</span> |
| |
| <span class="c1">--After Renaming the table</span> |
| |
| <span class="k">DESC</span> <span class="n">StudentInfo</span><span class="p">;</span> |
| <span class="o">+</span><span class="c1">--------------------------+------------+----------+--+</span> |
| <span class="o">|</span> <span class="n">col_name</span> <span class="o">|</span> <span class="n">data_type</span> <span class="o">|</span> <span class="k">comment</span> <span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+------------+----------+--+</span> |
| <span class="o">|</span> <span class="n">name</span> <span class="o">|</span> <span class="n">string</span> <span class="o">|</span> <span class="k">NULL</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="n">rollno</span> <span class="o">|</span> <span class="nb">int</span> <span class="o">|</span> <span class="k">NULL</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="n">age</span> <span class="o">|</span> <span class="nb">int</span> <span class="o">|</span> <span class="k">NULL</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="o">#</span> <span class="n">Partition</span> <span class="n">Information</span> <span class="o">|</span> <span class="o">|</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="o">#</span> <span class="n">col_name</span> <span class="o">|</span> <span class="n">data_type</span> <span class="o">|</span> <span class="k">comment</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="n">age</span> <span class="o">|</span> <span class="nb">int</span> <span class="o">|</span> <span class="k">NULL</span> <span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+------------+----------+--+</span> |
| |
| <span class="c1">--RENAME partition</span> |
| |
| <span class="k">SHOW</span> <span class="n">PARTITIONS</span> <span class="n">StudentInfo</span><span class="p">;</span> |
| <span class="o">+</span><span class="c1">------------+--+</span> |
| <span class="o">|</span> <span class="n">partition</span> <span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------+--+</span> |
| <span class="o">|</span> <span class="n">age</span><span class="o">=</span><span class="mi">10</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="n">age</span><span class="o">=</span><span class="mi">11</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="n">age</span><span class="o">=</span><span class="mi">12</span> <span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------+--+</span> |
| |
| <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="k">default</span><span class="p">.</span><span class="n">StudentInfo</span> <span class="n">PARTITION</span> <span class="p">(</span><span class="n">age</span><span class="o">=</span><span class="s1">'10'</span><span class="p">)</span> <span class="k">RENAME</span> <span class="k">TO</span> <span class="n">PARTITION</span> <span class="p">(</span><span class="n">age</span><span class="o">=</span><span class="s1">'15'</span><span class="p">);</span> |
| |
| <span class="c1">--After renaming Partition</span> |
| <span class="k">SHOW</span> <span class="n">PARTITIONS</span> <span class="n">StudentInfo</span><span class="p">;</span> |
| <span class="o">+</span><span class="c1">------------+--+</span> |
| <span class="o">|</span> <span class="n">partition</span> <span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------+--+</span> |
| <span class="o">|</span> <span class="n">age</span><span class="o">=</span><span class="mi">11</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="n">age</span><span class="o">=</span><span class="mi">12</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="n">age</span><span class="o">=</span><span class="mi">15</span> <span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------+--+</span> |
| |
| <span class="c1">-- Add new column to a table</span> |
| |
| <span class="k">DESC</span> <span class="n">StudentInfo</span><span class="p">;</span> |
| <span class="o">+</span><span class="c1">--------------------------+------------+----------+--+</span> |
| <span class="o">|</span> <span class="n">col_name</span> <span class="o">|</span> <span class="n">data_type</span> <span class="o">|</span> <span class="k">comment</span> <span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+------------+----------+--+</span> |
| <span class="o">|</span> <span class="n">name</span> <span class="o">|</span> <span class="n">string</span> <span class="o">|</span> <span class="k">NULL</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="n">rollno</span> <span class="o">|</span> <span class="nb">int</span> <span class="o">|</span> <span class="k">NULL</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="n">age</span> <span class="o">|</span> <span class="nb">int</span> <span class="o">|</span> <span class="k">NULL</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="o">#</span> <span class="n">Partition</span> <span class="n">Information</span> <span class="o">|</span> <span class="o">|</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="o">#</span> <span class="n">col_name</span> <span class="o">|</span> <span class="n">data_type</span> <span class="o">|</span> <span class="k">comment</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="n">age</span> <span class="o">|</span> <span class="nb">int</span> <span class="o">|</span> <span class="k">NULL</span> <span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+------------+----------+</span> |
| |
| <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">StudentInfo</span> <span class="k">ADD</span> <span class="n">columns</span> <span class="p">(</span><span class="n">LastName</span> <span class="n">string</span><span class="p">,</span> <span class="n">DOB</span> <span class="nb">timestamp</span><span class="p">);</span> |
| |
| <span class="c1">--After Adding New columns to the table</span> |
| <span class="k">DESC</span> <span class="n">StudentInfo</span><span class="p">;</span> |
| <span class="o">+</span><span class="c1">--------------------------+------------+----------+--+</span> |
| <span class="o">|</span> <span class="n">col_name</span> <span class="o">|</span> <span class="n">data_type</span> <span class="o">|</span> <span class="k">comment</span> <span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+------------+----------+--+</span> |
| <span class="o">|</span> <span class="n">name</span> <span class="o">|</span> <span class="n">string</span> <span class="o">|</span> <span class="k">NULL</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="n">rollno</span> <span class="o">|</span> <span class="nb">int</span> <span class="o">|</span> <span class="k">NULL</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="n">LastName</span> <span class="o">|</span> <span class="n">string</span> <span class="o">|</span> <span class="k">NULL</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="n">DOB</span> <span class="o">|</span> <span class="nb">timestamp</span> <span class="o">|</span> <span class="k">NULL</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="n">age</span> <span class="o">|</span> <span class="nb">int</span> <span class="o">|</span> <span class="k">NULL</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="o">#</span> <span class="n">Partition</span> <span class="n">Information</span> <span class="o">|</span> <span class="o">|</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="o">#</span> <span class="n">col_name</span> <span class="o">|</span> <span class="n">data_type</span> <span class="o">|</span> <span class="k">comment</span> <span class="o">|</span> |
| <span class="o">|</span> <span class="n">age</span> <span class="o">|</span> <span class="nb">int</span> <span class="o">|</span> <span class="k">NULL</span> <span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+------------+----------+--+</span> |
| |
| |
| <span class="c1">--Change the fileformat</span> |
| <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">loc_orc</span> <span class="k">SET</span> <span class="n">fileformat</span> <span class="n">orc</span><span class="p">;</span> |
| |
| <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">p1</span> <span class="n">partition</span> <span class="p">(</span><span class="k">month</span><span class="o">=</span><span class="mi">2</span><span class="p">,</span> <span class="k">day</span><span class="o">=</span><span class="mi">2</span><span class="p">)</span> <span class="k">SET</span> <span class="n">fileformat</span> <span class="n">parquet</span><span class="p">;</span> |
| |
| <span class="c1">--Change the file Location</span> |
| <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">dbx</span><span class="p">.</span><span class="n">tab1</span> <span class="n">PARTITION</span> <span class="p">(</span><span class="n">a</span><span class="o">=</span><span class="s1">'1'</span><span class="p">,</span> <span class="n">b</span><span class="o">=</span><span class="s1">'2'</span><span class="p">)</span> <span class="k">SET</span> <span class="k">LOCATION</span> <span class="s1">'/path/to/part/ways'</span> |
| |
| <span class="c1">-- SET SERDE/ SERDE Properties</span> |
| <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">test_tab</span> <span class="k">SET</span> <span class="n">SERDE</span> <span class="s1">'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'</span><span class="p">;</span> |
| |
| <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">dbx</span><span class="p">.</span><span class="n">tab1</span> <span class="k">SET</span> <span class="n">SERDE</span> <span class="s1">'org.apache.madoop'</span> <span class="k">WITH</span> <span class="n">SERDEPROPERTIES</span> <span class="p">(</span><span class="s1">'k'</span> <span class="o">=</span> <span class="s1">'v'</span><span class="p">,</span> <span class="s1">'kay'</span> <span class="o">=</span> <span class="s1">'vee'</span><span class="p">)</span> |
| |
| <span class="c1">--SET TABLE PROPERTIES</span> |
| <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">dbx</span><span class="p">.</span><span class="n">tab1</span> <span class="k">SET</span> <span class="n">TBLPROPERTIES</span> <span class="p">(</span><span class="s1">'winner'</span> <span class="o">=</span> <span class="s1">'loser'</span><span class="p">)</span> |
| |
| <span class="c1">--DROP TABLE PROPERTIES</span> |
| <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">dbx</span><span class="p">.</span><span class="n">tab1</span> <span class="n">UNSET</span> <span class="n">TBLPROPERTIES</span> <span class="p">(</span><span class="s1">'winner'</span><span class="p">)</span></code></pre></figure> |
| |
| <h3 id="related-statements">Related Statements</h3> |
| <ul> |
| <li><a href="sql-ref-syntax-ddl-create-table.html">CREATE TABLE</a></li> |
| <li><a href="sql-ref-syntax-ddl-drop-table.html">DROP TABLE</a></li> |
| </ul> |
| |
| |
| |
| </div> |
| |
| <!-- /container --> |
| </div> |
| |
| <script src="js/vendor/jquery-3.4.1.min.js"></script> |
| <script src="js/vendor/bootstrap.min.js"></script> |
| <script src="js/vendor/anchor.min.js"></script> |
| <script src="js/main.js"></script> |
| |
| <!-- MathJax Section --> |
| <script type="text/x-mathjax-config"> |
| MathJax.Hub.Config({ |
| TeX: { equationNumbers: { autoNumber: "AMS" } } |
| }); |
| </script> |
| <script> |
| // Note that we load MathJax this way to work with local file (file://), HTTP and HTTPS. |
| // We could use "//cdn.mathjax...", but that won't support "file://". |
| (function(d, script) { |
| script = d.createElement('script'); |
| script.type = 'text/javascript'; |
| script.async = true; |
| script.onload = function(){ |
| MathJax.Hub.Config({ |
| tex2jax: { |
| inlineMath: [ ["$", "$"], ["\\\\(","\\\\)"] ], |
| displayMath: [ ["$$","$$"], ["\\[", "\\]"] ], |
| processEscapes: true, |
| skipTags: ['script', 'noscript', 'style', 'textarea', 'pre'] |
| } |
| }); |
| }; |
| script.src = ('https:' == document.location.protocol ? 'https://' : 'http://') + |
| 'cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.1/MathJax.js' + |
| '?config=TeX-AMS-MML_HTMLorMML'; |
| d.getElementsByTagName('head')[0].appendChild(script); |
| }(document)); |
| </script> |
| </body> |
| </html> |
