CREATE EXTERNAL TABLE (U-SQL)

  • Article

Summary

U-SQL provides the ability to create external tables over data that is not owned by the U-SQL metadata object itself. Currently external tables are offered over data stored in Azure SQL Databases and are created with the CREATE EXTERNAL TABLE statement.

Syntax

Create_External_Table_Statement :=                                                                       
    'CREATE' 'EXTERNAL' 'TABLE' ['IF' 'NOT' 'EXISTS'] Identifier   
    '(' Column_Definition_List ')' Table_External_Specification.

Remarks

This statement creates a new external table with the specified name given by Identifier with the provided U-SQL table schema based on the external data provided by the Table_External_Specification.

  • Identifier
    If the Identifier is a three-part identifier, the table will be created in the specified database and schema. If it is a two-part identifier, then the table will be created in the specified schema of the current database context. If the identifier is a simple identifier, then the table will be created in the current database and schema context.

    If a table of the given name already exists in the specified database and schema context or the user has no permissions to create a table, an error is raised.

  • IF NOT EXISTS
    If the optional IF NOT EXISTS is specified, then the statement creates the table if it does not already exist, or succeeds without changes if the table already exists and the user has permission to at least enumerate all existing tables.

  • Column_Definition_List
    Defines the table schema as follows:

Syntax

  Column_Definition_List :=                                                                           
      Column_Definition { ',' Column_Definition }.
  • Column_Definition
    A column definition is of the form

Syntax

    Column_Definition :=                                                                           
        Quoted_or_Unquoted_Identifier Built_in_Type.

Each column has an identifier that can be either a quoted or unquoted identifier which is typed with one of the built-in U-SQL types. Each column identifier has to match to one of the column identifiers in the external table and the external columns data type (e.g., the SQL type in the Azure SQL database) will be mapped to the specified U-SQL type. If the mapping is not supported, an error is raised.

  • Table_External_Specification
    The external location where the data is managed is referenced with the following specification:

Syntax

  Table_External_Specification :=                                                                     
      'FROM' Datasource_Identifier 'LOCATION' Static_String_Expression.
 
  Datasource_Identifier :=                                                        
      DB_Object_Identifier.

The data source identifier is the name of the data source that has been created with the CREATE DATA SOURCE statement. The static string expression provided as the location determines the actual rowset resource in the external data source that is being mapped to the U-SQL external table.

If the data source is a SQL database, then the location is the schema-qualified name of a table or view in the SQL database. Its schema is mapped to the external table’s schema in the following way:

  • Any name in the column definition is mapped to the same name in the external table. If the external table’s naming is case-sensitive then the names have to exactly match, if the naming is case-insensitive, then the names are matched case-insensitively.
  • Any matched column has to have a U-SQL type in the column definition that is compatible with the external table’s column’s type, or an error is raised.
  • If a column is specified in the column definition that does not exist in the external table an error is raised.
  • If the external table has a column that is not specified in the column definition, then that column is not accessible via the external table.

An error is raised if the remote data source cannot be accessed, e.g., because the data source has disappeared, closed the firewall, or the credentials to access the remote data source have changed.

Examples

This example continues with the Data Source created from the example at CREATE DATA SOURCE (U-SQL).

// External Table - Create
USE DATABASE TestReferenceDB;
CREATE EXTERNAL TABLE IF NOT EXISTS dbo.BuildVersion_Local
(
    SystemInformationID byte,
    [Database Version] string,
    VersionDate DateTime,
    ModifiedDate DateTime
)
FROM MyAzureSQLDBDataSource LOCATION "[dbo].[BuildVersion]";


// External Table - Query
@result =
    SELECT *
    FROM TestReferenceDB.dbo.BuildVersion_Local;

OUTPUT @result
TO "/Output/ReferenceGuide/DDL/Tables/ExternalTableQuery1.csv"
USING Outputters.Csv();

See Also