Create External Table SF
    • Dark
      Light

    Create External Table SF

    • Dark
      Light

    Article Summary

    This article is specific to the following platforms - Snowflake.

    Create External Table

    This component lets users to create an "external" table that references externally stored data. This creates a table that references the data that is held externally, meaning the table itself does not hold the data. External tables can be queried but are read-only. To learn more, read Working with External Tables.

    External tables require an external stage to stage data. External stages can be managed via the Manage Stages menu.

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

    Data loaded in these tables takes the form of a single column ("VALUE") with a variant-type value with multiple properties. Each column of target data becomes a property within the variant on its respective row. Thus, the data will usually require some transformation to be in a desirable format. See also: Flatten Variant Component.

    Note

    External Tables must be refreshed before use whenever the externally held data is changed using the Refresh External Table component. We recommend always refreshing an External Table before using it in a job.

    Properties

    Snowflake Properties

    PropertySettingDescription
    NameTextA 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.
    DatabaseSelectSelect a Snowflake database to house the external table.
    SchemaSelectSelect the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on using multiple schemas, read Schemas.
    New Table NameStringThe name of the external table to be created or used.
    Partition ColumnsMultiple ExpressionA partition is defined by an expression applied to a set of data, resulting in a partition that only contains rows that satisfy the expression. For example, partitioning a dataset by the value in the 'year' column.
    For more information about setting up partitions with Snowflake's external tables, read Managing Regular Data Loads.
    Name: The name of the new partition column.
    Type: The data type of the partition column.
    Size:The data size of the new column values.
    Precision: The precision of the new column values.
    Expression: The expression used to partition the data.
    Stage DatabaseSelectSelect a Snowflake database for the external stage.
    Stage SchemaSelectSelect a Snowflake schema for the external stage.
    StageSelectSelect an external stage for the data. Staging areas can be managed via the Manage Stages menu or created through Snowflake using the CREATE STAGE command.
    Relative PathTextThe directory path to follow to the target data. File names cannot be specified. The path is relative to the storage location given in the external stage setup.
    PatternStringSpecify, using regular expression (RegEx) pattern syntax, files to be matched on the external stage.
    For example: '.*flight.*[.]csv'
    FormatSelectSelect a pre-made file format that will automatically set many of the component properties accordingly. These formats can be created through the Create File Format component.
    File TypeSelectThe type of expected data to load. Some may require additional formatting, explained in Preparing to Load Data.
    Available options are: AVRO, CSV, JSON, ORC, PARQUET, and XML.
    Component properties will change to reflect the choice made here and give options based on the specific file type.
    Enable OctalSelectEnables parsing of octal numbers.
    CompressionSelectWhether the input file is compressed in gzip format, BROTLI, BZ2, DEFLATE, RAW_DEFLATE, ZSTD, or not compressed at all.
    Record DelimiterTextThe delimiter to be used that separates records (rows) in the file. Defaults to newline. \ can also signify a newline. \\r can signify a carriage return.
    Field DelimiterTextThe delimiter that separates columns. The default is a comma. A [TAB] character can be specified as "\ ".
    Skip HeaderTextThe number of rows at the top of the file to ignore. The default setting is 0.
    EscapeSelectWhen this option is specified, the backslash character (\\) in input data is treated as an escape character.
    Escape Unenclosed FieldTextSingle character string used as the escape character for unenclosed field values. Accepts common escape sequences, octal values, or hex values. Also accepts a value of NONE (default). If a character is specified in the 'Escape' field, it will override this field.
    Trim SpaceSelectRemoves trailing and leading whitespace from the input data.
    Field Optionally EnclosedTextA character that is used to enclose strings. Can be a single quote (') or a double quote (") or NONE (default). Escape the character with an instance of the same character.
    Null IfTextThis option replaces the specified string with null in the output table. Use this if your data has a particular representation of missing data.
    Error On Column Count MismatchSelectGenerate an error if the number of delimited columns in the input does not match that of the table. If false, extra columns are not loaded into the table and missing columns are recorded as NULL in the table.
    Empty Field As NullSelectIf this is set, empty columns in the input file will become NULL.
    Encoding TypeSelectThe type of encoding that has been applied to the data. The default setting is UTF-8.
    Strip Outer ArraySelectEnable removal of outer square brackets from JSONs.
    Strip Null ValuesSelectEnables removal of null values.
    Ignore UTF-8 ErrorsSelectWhen true, replaces invalid UTF-8 sequences with the unicode replacement character (U+FFFD), instead of throwing an error.
    Preserve SpaceSelectWhen parsing XML files, leading and trailing spaces in elements are preserved if set to true.
    Strip Outer ElementSelectWhen parsing XML files, this will strip the outermost XML element, exposing the second-level elements as separate documents.
    Disable Snowflake DataSelectWhen parsing XML files, will disable recognition of Snowflake semi-structured data tags if set to true.
    Disable Auto ConvertSelectWhen parsing XML files, enables conversion of numeric and Boolean values from text to their native types if set to true.