Create External Table - Maia Documentation

Create External Table is an orchestration component that lets you define a table that references data stored outside of your data warehouse. These external tables don’t store data themselves, but can be queried similarly to regular tables. External tables are read-only.

Cloud platform information

If you’re a Snowflake, Amazon Redshift, or Google BigQuery user, familiarize yourself with the corresponding information about this component.

Snowflake

When using Snowflake, external tables are useful for querying large datasets without physically loading them into Snowflake storage. The data is typically returned in a single column named VALUE, containing variant-type values. Each row stores the source data as a set of properties within this variant.

Due to the structure of the returned data, transformation (e.g using the Flatten Variant component) is often required to convert it into a usable format.
External tables must be refreshed after any changes to the underlying data. Use the Refresh External Table component to update the metadata before querying.

Amazon Redshift

When using Amazon Redshift, the Create External Table component references data stored in an Amazon S3 bucket. Like Snowflake, the external table doesn’t store the data internally. If the component requires access to a cloud provider (AWS, Azure, or Google Cloud), it will use credentials as follows:

If using Matillion Full SaaS: The component will use the cloud credentials associated with your environment to access resources.
If using Hybrid SaaS: By default the component will inherit the agent’s execution role (service account role). However, if there are cloud credentials associated with your environment, these will overwrite the role.

Referencing external data in this way provides an efficient method for querying large datasets without consuming storage on the Amazon Redshift cluster.

External tables are part of Amazon Redshift Spectrum, and may not be available in all regions. For a list of supported regions, read Amazon Redshift endpoints and quotas.
For more information about working with external tables, read Creating external tables for Redshift Spectrum.

Google BigQuery

When using Google BigQuery, the Create External Table component creates a table that references data stored in a Google Cloud Storage (GCS) bucket. The external table doesn’t store data internally but can be queried like a regular Google BigQuery table. For more information, read the Google BigQuery tables REST resource.

Properties

Snowflake
Amazon Redshift
Google BigQuery

string

required

A human-readable name for the component.

drop-down

required

Select one of:

Create: (default). This option creates a new external table. The component will fail if an external table with the same name already exists.
Create if not exists: This option will only create a new external table when an external table of the same name does not already exist. If the schema of the existing external table does not match the schema defined in this component, no attempt is made to fix or correct the schema, which could lead to errors later on in the pipeline if:
- Users did not expect an external table to already exist.
- Users did not expect to have a different schema to the one defined in this component.
Replace: This option drops any existing external table of the same name and then creates a new one. Selecting this option guarantees that after the component succeeds, the external table matches the schema defined in this component.

import CopyGrantsSnowflake from ‘/snippets/content/copy-grants-snowflake.mdx’;

drop-down

required

import SnowflakeDatabase from ‘/snippets/content/snowflake-database.mdx’;

drop-down

required

import SnowflakeSchema from ‘/snippets/content/snowflake-schema.mdx’;

string

required

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

column editor

A 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.
Partition Expression: The expression used to partition the data.

Toggle Text mode on at the bottom of the dialog to open a multi-line editor that lets you add items in a single block. For more information, read Text mode.To use grid variables, toggle Use Grid Variable on. For more information, read Grid variables.

drop-down

required

Select a Snowflake database for the external stage.

drop-down

required

Select a Snowflake schema for the external stage.

drop-down

required

Select an external stage for the data. Staging areas can be created through Snowflake using the CREATE STAGE command.

string

The 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.

string

Specify, using regular expression (RegEx) pattern syntax, files to be matched on the external stage.For example: '.*flight.*[.]csv'

drop-down

required

Select 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.The list of file formats will come from the database and schema that the specified tables exist in.

drop-down

required

The type of expected data to load. Some data 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.

drop-down

required

Select whether the input file is compressed in GZIP format, BROTLI, BZ2, DEFLATE, RAW_DEFLATE, ZSTD, or not compressed at all. The default is AUTO.When AUTO is selected, the compression algorithm is detected automatically, except for Brotli-compressed files, which cannot currently be detected automatically.

string

This option replaces the specified string with NULL in the output table. Use this if your data has a particular representation of missing data.

boolean

required

When true, removes trailing and leading whitespace from the input data. Default is false.

drop-down

required

string

The delimiter to be used that separates records (rows) in the file. Defaults to newline.\n can also signify a newline. \r can signify a carriage return.

string

(CSV) The delimiter that separates columns. The default is a comma. A [TAB] character can be specified as \t.

integer

The number of rows at the top of the file to ignore. The default setting is 0 and the property is left blank.

boolean

required

When true, skip any blank lines encountered in the CSV files. When false, blank lines produce an end-of-record error. Default is false.

string

When this option is specified, the backslash character \ in input data is treated as an escape character.

string

Single 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.

boolean

required

When true, removes trailing and leading whitespace from the input data. Default is false.

string

A 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.

string

This option replaces the specified string with NULL in the output table. Use this if your data has a particular representation of missing data.

boolean

required

Generate 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. Default is false.

boolean

required

When set to true, empty columns in the input file will become NULL. Default is true.

boolean

required

When true, Snowflake replaces invalid UTF-8 characters with the Unicode replacement character. When false, the load operation produces an error when invalid UTF-8 character encoding is detected. Default is false.

drop-down

The type of encoding that has been applied to the data. The default setting is UTF-8. Consult the Snowflake documentation for more information.

string

required

A human-readable name for the component.

drop-down

required

import RedshiftTableSchema from ‘/snippets/content/redshift-table-schema.mdx’;

string

required

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

drop-down

required

Create: Create the new table with the given name. The pipeline will fail if a table of that name already exists.
Create if not exists: Create the new table with the given name unless one already exists. The pipeline will succeed and continue in either case. If the schema of the existing table does not match the schema defined in this component, no attempt is made to fix or correct it, which could lead to errors later in the pipeline if you did not expect an existing table to exist, or to have a different schema to the one defined in this component.
Replace: Create the new table, potentially overwriting any existing table of the same name.

Since other database objects depend upon this table, DROP ... cascade is used, which may actually remove many other database objects.

column editor

required

Name: The name of the new column.
Type: Select the data type.
- Text: Can hold any type of data, subject to a maximum size.
- Integer: Suitable for whole-number types (no decimals).
- Numeric: Suitable for numeric types, with or without decimals.
- Real: Suitable for data of a single precision floating-point number.
- Double Precision: Suitable for data of a double precision floating-point number.
- Boolean: Suitable for data that is either true or false.
- Date: Suitable for dates without times.
- DateTime: Suitable for dates, times, or timestamps (both date and time).
- Super: Suitable for storing semi-structured data or documents as values.
Size: For text types, this is the maximum length. This is a limit on the number of bytes, not characters. For Amazon Redshift, since all data is stored using UTF-8, any non-ASCII character will count as 2 or more bytes. For numeric types, this is the total number of digits allowed, whether before or after the decimal point.
Scale: Scale is the number of digits to the right of the decimal point in a Float. For example, the number 123.45 has a scale of 2.

column editor

required

Assign one or more columns in this table as potential partitions.Partition columns allow queries on large datasets to be optimized when that query is made against the columns chosen as partition columns. When a partition is created, values for that column become distinct Amazon S3 storage locations, allowing rows of data in a location that is dependent on their partition column value.For example, it is common for a date column to be chosen as a partition column, thus storing all other data according to the date it belongs to. When creating partitioned data using the Add Partition component, it is vital that those partitioned columns have already been marked using this property.To use grid variables, toggle Use Grid Variable on. For more information, read Grid variables.

column editor

required

The Amazon S3 bucket location for the external table data.If the component requires access to a cloud provider (AWS, Azure, or Google Cloud), it will use credentials as follows:

If using Matillion Full SaaS: The component will use the cloud credentials associated with your environment to access resources.
If using Hybrid SaaS: By default the component will inherit the agent’s execution role (service account role). However, if there are cloud credentials associated with your environment, these will overwrite the role.

drop-down

required

Choose a file format for the source file.

string

(TEXTFILE) The delimiter to be used that separates fields (columns) in the file. Defaults to \\A.

string

(TEXTFILE) The delimiter to be used that separates records (rows) in the file. Defaults to newline.

\n can also signify a newline.
\r can signify a carriage return.

string

The number of rows at the top of the file to skip. The default setting is an empty field.

drop-down

required

(JSON) Strips the outer array from the JSON file, enabling JSON files that contain a single, anonymous array to be loaded without error. The default setting is No.

string

required

A human-readable name for the component.

drop-down

required

The Google Cloud project that owns the BigQuery dataset. The special value [Environment Default] uses the Google Cloud project defined in the environment. For more information, read Creating projects.

drop-down

required

The Google BigQuery dataset where the table will be created or updated. The special value [Environment Default] uses the dataset defined in the environment.

string

required

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

drop-down

required

The format of the source file. Available options are: CSV, JSON (New line delimited), and Cloud Datastore Backup.The properties below change depending on the option selected here.

CSV
JSON (New line delimited)
Cloud Datastore Backup

column editor

Define the columns for the external table. Nested structures are not supported.

Name: The name of the new column.
Data Type: The Google BigQuery data type for the column.
Mode: The column mode. Select NULLABLE, REQUIRED, or REPEATED.
Description: An optional description for the column.

drop-down

required

Select one of:

Create: Create the new table with the given name. The pipeline fails if a table of that name already exists.
Create if not exists: Create the new table with the given name unless one already exists. The pipeline succeeds and continues in either case. If the schema of the existing table doesn’t match the schema defined in this component, no attempt is made to fix or correct it, which could lead to errors later in the pipeline.
Replace: Create the new table, potentially overwriting any existing table of the same name. Since other database objects depend on this table, DROP ... cascade is used, which may remove many other database objects.

file explorer

required

Use the file explorer to enter the container path where the Google Cloud Storage data is located, or select from the list of GCS buckets. Enter the path after the gs:// prefix, for example: bucket-name/path.

drop-down

Select the compression format of the source file. Available options are NONE (default) and GZIP.

integer

The maximum number of parsing errors to allow before the component fails. Defaults to 0.

boolean

When set to Yes, accepts rows that contain values not matching the schema. Defaults to No.

drop-down

required

The character used to separate columns. Defaults to comma.

string

The character used to quote CSV fields.

drop-down

required

The character encoding of the source file. Available options are UTF-8 (default) and ISO-8859-1.

integer

required

The number of leading rows to ignore. Defaults to 0.

boolean

When set to Yes, allows newline characters within quoted CSV values. Defaults to No.

boolean

When set to Yes, accepts rows with missing trailing values. Defaults to No.

column editor

Define the columns for the external table. Nested structures are not supported.

Name: The name of the new column.
Data Type: The Google BigQuery data type for the column.
Mode: The column mode. Select NULLABLE, REQUIRED, or REPEATED.
Description: An optional description for the column.

drop-down

required

Select one of:

Create: Create the new table with the given name. The pipeline fails if a table of that name already exists.
Create if not exists: Create the new table with the given name unless one already exists. The pipeline succeeds and continues in either case. If the schema of the existing table doesn’t match the schema defined in this component, no attempt is made to fix or correct it, which could lead to errors later in the pipeline.
Replace: Create the new table, potentially overwriting any existing table of the same name. Since other database objects depend on this table, DROP ... cascade is used, which may remove many other database objects.

file explorer

required

drop-down

Select the compression format of the source file. Available options are NONE (default) and GZIP.

integer

The maximum number of parsing errors to allow before the component fails. Defaults to 0.

boolean

When set to Yes, accepts rows that contain values not matching the schema. Defaults to No.

drop-down

required

Select one of:

Create: Create the new table with the given name. The pipeline fails if a table of that name already exists.
Create if not exists: Create the new table with the given name unless one already exists. The pipeline succeeds and continues in either case. If the schema of the existing table doesn’t match the schema defined in this component, no attempt is made to fix or correct it, which could lead to errors later in the pipeline.
Replace: Create the new table, potentially overwriting any existing table of the same name. Since other database objects depend on this table, DROP ... cascade is used, which may remove many other database objects.

file explorer

required

​Cloud platform information

​Snowflake

​Amazon Redshift

​Google BigQuery

​Properties

Cloud platform information

Snowflake

Amazon Redshift

Google BigQuery

Properties