Skip to content

Create External Table

The Create External Table component enables users to create an "external" table that references externally stored data, meaning the table itself does not hold the data. External Tables can be queried but are read-only. To learn more, read Use external tables with Synapse SQL.

Referencing externally held data can be valuable when wanting to query large datasets without resorting to storing that same volume of data in Azure Synapse Analytics.

:::info{title='Note'} This section only applies to the view created within Matillion ETL.

When using the FLOAT data type, users can enter any value that is greater than or equal to 1 and less than or equal to 2,147,483,647. If a FLOAT with a size less than or equal to 7 is used, a column of type REAL will be produced in the database—note that this column will still appear as a FLOAT type within Matillion ETL, simply with a smaller size. Conversely, if a FLOAT with a size greater than or equal to 8 is used, the traditional FLOAT value is used for the column both in the database and Matillion ETL. To learn more, read float and real (Transact-SQL). :::

Properties

Name = string

A human-readable name for the component.

Create/Replace = drop-down

  • Create: Create the new table with the given name. Will fail if a table of that name already exists.
  • Create if not exists: Will create the new table with the given name unless one already exists. Will succeed and continue in either case.
  • Replace: Will create the new table, potentially overwriting any existing table of the same name.

Schema = drop-down

Select the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on schemas, read the Azure Synapse documentation.

New Table Name = string

The name of the external table to be created or used.

Table Metadata = column editor

  • Column Name: Provide the name of each new column.
  • Data Type: Select the data type. The available data types are:
    • DATE: Suitable for dates without times.
    • DATETIME: Suitable for timestamps.
    • TIME: Suitable for times.
    • INTEGER: Suitable for whole number types (no decimals).
    • NUMERIC: Suitable for numeric types, with or without decimals.
    • TEXT: Suitable for text types.
    • FLOAT: Suitable for approximate number data types for use with floating point numeric data.
    • BOOLEAN: Suitable for data whether values are either "true" or "false".
  • Size: The size column sets the size, except for the data types BOOLEAN, DATE, DATETIME, and TIME.
    • For more information about DATE, read date (Transact-SQL).
    • For DATETIME, the size column sets the precision of the datetime offset, and accepted values range from 0-7, ranging from 0 to 9999999, that represent the fractional seconds.
      • If the DATETIME size is set to 0, the returned datetime will be 26 positions (YYYY-MM-DD hh:mm:ss {+|-}hh:mm).
      • If the DATETIME size is set to a precision of 1, the returned datetime will be 28 positions (26 + precision + 1) (YYYY-MM-DD hh:mm:ss.n {+|-}hh:mm).
      • If the DATETIME size is set to a precision of 7, the returned datetime will be 34 positions (26 + precision + 1) (YYYY-MM-DD hh:mm:ss.nnnnnnn {+|-}hh:mm).
    • For TIME, the size column sets the precision of the time offset, and accepted values range from 0-7, ranging from 0 to 9999999, that represent the fractional seconds.
      • If the TIME size is set to 0, the returned time will be 8 positions (hh:mm:ss).
      • If the TIME size is set to 1, the returned time will be 10 positions (8 + precision + 1) (hh:mm:ss.n).
      • If the TIME size is set to 7, the returned time will be 16 positions (8 + precision + 1) (hh:mm:ss.nnnnnnn).
  • Scale: Define the scale.
  • Allow Nullable: Select whether or not to allow nullable values.

Data Source = drop-down

Select an available external data source object.

Location = string

Specify the location of the external data source object.

File Format = drop-down

Select an available file format.

Reject Type = drop-down

Select from None, Percentage, or Value to determine the quantity of rows that can be rejected before the query fails.

Reject Percentage Value = integer

Specify a percentage of the number of rows that can be rejected before the query fails. Only available when Reject Type is set to "Percentage".

Reject Sample Value = integer

Specify the number of rows to retrieve before PolyBase recalculates the percentage of rejected rows. Allowed values are 1, 2, etc.. Only available when Reject Type is set to "Percentage".

To learn more, read Limitations and restrictions.

Reject Value = integer

Specify a literal number value of rows that can be rejected before the query fails. Only available when Reject Type is set to "Value".

Snowflake Delta Lake on Databricks Amazon Redshift Google BigQuery Azure Synapse Analytics
❌ ❌ ❌ ❌ ✅