Contents x
    Create External Table SYN
      • Dark
        Light
      Contents

      Create External Table SYN

        • Dark
          Light
        Article Summary

        This article is specific to the following platforms - Synapse.


        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. For in-depth information, please consult the Azure Synapse Analytics documentation.

        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.

        (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. For more information, please refer to the Microsoft documentation.


        Properties

        Synapse Properties

        PropertySettingDescription
        NameStringA human-readable name for the component.
        Create/ReplaceSelectCreate: 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.
        SchemaSelectSelect the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on schemas, please see the Azure Synapse documentation.
        Table NameStringProvide a new table name.
        Warning: This table will be recreated on each run of the job, and drop any existing table of the same name.
        Table MetadataColumn NameProvide the name of each new column.
        Data TypeSelect the data type. The available data types are: DATE: this data type is suitable for dates without times. More...
        DATETIME: this data type is suitable for timestamps. More...
        TIME: this data type is suitable for times target="_blank">More...
        INTEGER: this data type is suitable for whole number types (no decimals). More...
        NUMERIC: this data type is suitable for numeric types, with or without decimals. More...
        TEXT: this data type is suitable for text types. More...
        FLOAT: this data type is suitable for approximate number data types for use with floating point numeric data. More...
        BOOLEAN: this data type is suitable for data whether values are either "true" or "false". More...
        SizeThe 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 more information, read datetime (Transact-SQL).

        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).
        ScaleDefine the scale. More...
        Allow NullableSelect whether or not to allow nullable values. More...
        Data SourceSelectSelect an available external data source object.
        LocationStringSpecify the location of the external data source object.
        File FormatSelectSelect an available file format. More...
        Reject TypeSelectSelect from None, Percentage, or Value to determine the quantity of rows that can be rejected before the query fails.
        Reject Percentage ValueIntegerSpecify 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 ValueIntegerSpecify 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".
        More information can be found here.
        Reject ValueIntegerSpecify a literal number value of rows that can be rejected before the query fails.
        Only available when Reject Type is set to "Value".
        What's Next