.png){kind=logo}
Create External Table SYN
-
DarkLight
Create External Table SYN
-
DarkLight
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 | ||
|---|---|---|
| Property | Setting | Description |
| Name | String | A human-readable name for the component. |
| Create/Replace | Select | 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 | Select | Select 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 Name | String | Provide 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 Metadata | Column Name | Provide the name of each new column. |
| Data Type | Select 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... |
|
| 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 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). |
|
| Scale | Define the scale. More... | |
| Allow Nullable | Select whether or not to allow nullable values. More... | |
| Data Source | Select | Select an available external data source object. |
| Location | String | Specify the location of the external data source object. |
| File Format | Select | Select an available file format. More... |
| Reject Type | Select | 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". More information can be found here. |
| 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". |