Table of contents
TOC
Collapse the table of content
Expand the table of content

ALTER TABLE (U-SQL)

Michael Rys|Last Updated: 5/15/2018
|
4 Contributors

Modifies a table definition by adding, or dropping columns. Adding or dropping a column from a table is a meta data operation only and its performance will not be impacted by the size of the table.

Syntax
Alter_Table_Statement :=                                                          
    'ALTER' 'TABLE' Identifier   
    ( 'REBUILD'
    | 'ADD' 'COLUMN' Column_Definition_List     
    | 'DROP' 'COLUMN' Identifier_List ).  

Semantics of Syntax Elements

  • Identifier
    Identifies the table to be modified. If the Identifier is a three-part identifier, the table from the specified database and schema will be chosen. If the Identifier is a two-part identifier, then the table of the given schema and of the given name of the current static database context is chosen. If the identifier is a simple identifier, then the table of the given name in the current static database and schema context is chosen.

    If the specified table does not exist, is an external table, or the user does not have permissions to modify the table, an error is raised.

  • REBUILD
    Used to compact partitions and tables that have grown by multiple, incremental insertions into the same partitions in order to improve query performance over such tables. If the table is empty or has only been loaded once, the operation will succeed without the need to perform a compaction.

  • ADD | DROP
    If an already existing column is attempted to be added or a non-existent column is attempted to be dropped, an error is raised.

  • Column_Definition_List
    The name(s) and built-in USQL type of the column(s) to be added. If the added column is of a nullable type, existing rows will contain null in the added column. If the added column is of a not-nullable type, then the column will contain the type's default value (e.g., 0 for type int).

  • Identifier_List
    The name(s) of the column(s) to be dropped. A column cannot be dropped when it is:

    • Used in an index.
    • Used in a partition.

Examples

  • The examples can be executed in Visual Studio with the Azure Data Lake Tools plug-in.
  • The scripts can be executed locally. An Azure subscription and Azure Data Lake Analytics account is not needed when executed locally.
  • The examples below are based on the table defintion below.

Test Table

CREATE DATABASE IF NOT EXISTS TestReferenceDB; 
USE DATABASE TestReferenceDB;

DROP TABLE IF EXISTS Logs;
CREATE TABLE Logs (
    date DateTime, 
    eventType int, 
    eventTime DateTime, 
    INDEX Index_EventType CLUSTERED (eventType ASC) 
    DISTRIBUTED BY HASH(eventType) INTO 3);

Table Rebuild

//Introduce multiple incremental inserts
USE DATABASE TestReferenceDB;

INSERT INTO dbo.Logs
(date, eventType, eventTime)
VALUES
(new DateTime(2017,01,01), 1, new DateTime(2017,01,01,12,00,00));

INSERT INTO dbo.Logs
VALUES
(new DateTime(2017,02,01), 1, new DateTime(2017,02,01,12,00,00));

/*
// Rebuild table.  Execute separately and after INSERT statements above.
ALTER TABLE TestReferenceDB.dbo.Logs
REBUILD;
*/

Various Column Scenarios

USE DATABASE TestReferenceDB;

// Add a column
ALTER TABLE Logs ADD COLUMN eventName string;

// add another column
ALTER TABLE Logs ADD COLUMN result int;

// drop a column and add another one
ALTER TABLE Logs DROP COLUMN result;
ALTER TABLE Logs ADD COLUMN clientId string;

// drop a column and add 3 more columns
ALTER TABLE Logs DROP COLUMN clientId;
ALTER TABLE Logs ADD COLUMN result string, clientId string, payload int?;

// drop 2 columns
ALTER TABLE Logs DROP COLUMN clientId, result;

See Also

© 2018 Microsoft