The ORC Load orchestration component loads data into a table from one or more ORC files. The component automatically infers the schema from the structure of the selected file(s), creates the corresponding table, and then loads the data into the table.
Use cases
ORC Load suits data engineering tasks that bring columnar data from Hadoop-ecosystem and data lake environments into your warehouse. For example, you can use this component to:
- Ingest ORC files produced by Apache Hive or Spark ETL jobs as part of a data lake to warehouse migration.
- Load large-scale event or clickstream data generated by big data pipelines and stored in ORC format.
- Bring columnar-compressed data from cloud data lake storage—such as S3, Azure Data Lake Storage, or Google Cloud Storage—into a warehouse for downstream analytics.
- Migrate on-premises Hive warehouse data to Snowflake or Databricks by exporting to ORC and loading via this component.
- Process ORC files deposited by upstream data engineering teams on a recurring schedule.
You can authorize this component using either of these two methods:
- Cloud Provider Credentials: By default, explicit Cloud Provider Credentials are used to facilitate cross-account or cross-cloud data loads.
- Agent Identity: If cloud provider credentials are not specified, the component attempts to use the Matillion Agent’s native service identity to access the source data.
Properties
A human-readable name for the component.
Connect
Snowflake
Databricks
Google BigQuery
- Snowflake Managed: Load your file from a Snowflake internal stage.
- S3: Load your file from an S3 bucket.
- Google Cloud Storage: Load your file from a Google Cloud Storage bucket.
- Azure Blob Storage: Load your file from an Azure Blob Storage container.
Select a staging area for the data. The special value [New Stage] will create a temporary stage to be used for loading the data when the corresponding parameter values are provided.The options in this drop-down menu depend on the values you select for the Database and Schema parameters. If you change these values, the list of available options updates automatically, and the previously selected option may become invalid.When [New Stage] is selected, the component uses the cloud credentials configured for your environment to access the required resources. Snowflake Managed
S3
Google Cloud Storage
Azure Blob Storage
A stage may include directories. The user has the option to browse and select files from a specific directory path, for example, /Example/Path.If this is left blank, and if the Pattern parameter is also empty, all files from the stage will be loaded.
To retrieve the intended files, use the file explorer to enter the container path where the S3 bucket is located, or select from the list of S3 buckets.This must have the format s3://<bucket>/<path>.
Decide how the files are encrypted inside the S3 bucket. This property is available when using an existing Amazon S3 location for staging. The ID of the KMS encryption key you have chosen to use in the Encryption property.Only available when Encryption is set to KMS Encryption.
The secret definition denoting your master key for client-side encryption. Your password should be saved as a secret definition before using this component.Only available when Encryption is set to Client Side Encryption. Select the storage integration. Storage integrations are required to permit Snowflake to read from and write to a cloud storage location. Integrations must be set up in advance and configured to support Google Cloud Storage. For more information, read Create storage integration. Google Storage URL location
To retrieve the intended files, use the file explorer to enter the container path where the Google Cloud Storage bucket is located, or select from the list of Google Cloud Storage buckets.This must have the format gs://<bucket>/<path>.
To retrieve the intended files, use the file explorer to enter the container path where the Azure Storage account is located, or select from the list of storage accounts.This must have the format azure://<STORAGEACCOUNT>/<path>.
A valid regular expression (regex) to match part of a file’s path or name. Files that match the pattern will be included in the load.If this parameter is left empty, the component automatically uses the pattern *, which matches all files within the specified Staged file path or Stage parameters. The pattern applies to the entire file path, not just the directory defined in Staged file path.The subfolder containing the object to load must be included here.
- Only
ORC files are supported.
- Ensure that the pattern entered correctly targets the files you intend to load.
- S3: Load your file from an S3 bucket.
- Google Cloud Storage: Load your file from a Google Cloud Storage bucket.
- Azure Blob Storage: Load your file from an Azure Blob Storage container.
S3
Google Cloud Storage
Azure Blob Storage
To retrieve the intended files, use the file explorer to enter the container path where the S3 bucket is located, or select from the list of S3 buckets.This must have the format s3://<bucket>/<path>.
Google Storage URL location
To retrieve the intended files, use the file explorer to enter the container path where the Google Cloud Storage bucket is located, or select from the list of Google Cloud Storage buckets.This must have the format gs://<bucket>/<path>.For this to work, an external location with access to the specified location must be configured. For more information, read External Locations | Databricks on AWS. To retrieve the intended files, use the file explorer to enter the container path where the Azure Storage account is located, or select from the list of storage accounts.This must have the format azure://<STORAGEACCOUNT>/<path>.
A valid glob pattern to match part of a file’s path or name. Files that match the pattern will be included in the load.If this parameter is left empty, the component automatically uses the pattern *, which matches all files within the specified location parameters. The pattern applies to the entire file path, not just the directory defined in the location parameter.
- Only
ORC files are supported.
- Ensure that the pattern entered correctly targets the files you intend to load.
Select Google Cloud Storage to load your file from a Google Cloud Storage bucket.
Google Storage URL location
To retrieve the intended files, use the file explorer to enter the container path where the Google Cloud Storage bucket is located, or select from the list of Google Cloud Storage buckets.This must have the format gs://<bucket>/<path>.
Filter for specific files by their file name, including their file extension. You can use wildcards in the file name, for example prefix_*{filetype}.If this field is left empty, all files at the specified Google Storage URL location are loaded.If the specified Google Storage URL location contains a *, you can’t apply a file filter.
Snowflake
Databricks
Google BigQuery
The default value is set to [New File Format]. Specify a file format, and a temporary format with those settings will be used when the component runs. Alternatively, select a pre-made file format.
When True, removes whitespace from fields. The default setting is False.This will only work when Match By Column Name is set to either Case Insensitive or Case Sensitive in the Advanced Settings below.
Replace invalid characters
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.The default setting is False.
Specify one or more strings (one string per row of the table) to convert to NULL values. When one of these strings is encountered in the file, it is replaced with an SQL NULL value for that field in the loaded table. Click + to add a string.This will only work when Match By Column Name is set to either Case Insensitive or Case Sensitive.
When set to Yes, corrupt input files are skipped and not included in the load.
When set to Yes, files that cannot be found are ignored during the load process.
An optional timestamp as a filter to only ingest files that have a modification timestamp after the provided timestamp.
An optional timestamp as a filter to only ingest files that have a modification timestamp before the provided timestamp.
When set to Yes, files are read recursively from all subdirectories under the specified path. When enabled, partition inference is disabled. To control which files are loaded, use the Glob Pattern property instead.
When set to Yes, schemas from all input files are merged during the load.
Select the data types that Google BigQuery can use to represent decimal values from the source file. Google BigQuery uses the smallest selected type that can represent each value.
- NUMERIC: Supports up to 29 digits of precision and 9 digits of scale.
- BIGNUMERIC: Supports up to 76 digits of precision and 38 digits of scale.
- STRING: Preserves the original precision by representing the value as a string.
Destination
Snowflake
Databricks
Google BigQuery
Specify the name of the newly created or existing table to load data into. The table will be created according to the Load strategy you select below.
- Replace: If the specified table name already exists, that table will be destroyed and replaced by the table created during this pipeline run.
- Truncate and Insert: Each time the pipeline runs, two operations are performed: first, the table is truncated, meaning all existing rows are deleted. Then, your new rows are inserted. The table itself is never destroyed and recreated.
- Fail if Exists: If the specified table name already exists, this pipeline will fail to run, and no data will be copied to the table.
- Append: If the specified table name already exists, then the data is inserted without altering or deleting the existing data in the table. It’s appended onto the end of the existing data in the table. If the specified table name doesn’t exist, then an error message will appear and the table is not created. You must always provide a table name.
The name of the table to be created. The table will be created according to the Load strategy you select below.
- Replace: If the specified table name already exists, that table will be destroyed and replaced by the table created during this pipeline run.
- Truncate and Insert: Each time the pipeline runs, two operations are performed: first, the table is truncated, meaning all existing rows are deleted. Then, your new rows are inserted. The table itself is never destroyed and recreated.
- Fail if Exists: If the specified table name already exists, this pipeline will fail to run, and no data will be copied to the table.
- Append: If the specified table name already exists, then the data is inserted without altering or deleting the existing data in the table. It’s appended onto the end of the existing data in the table. If the specified table name doesn’t exist, then an error message will appear and the table is not created. You must always provide a table name.
If not specified, the default is Fail if Exists.Databricks uses COPY INTO, which is idempotent. This means it tracks which source files have already been loaded into the target Delta table and skips them on subsequent runs.Enabling Force load overrides this behavior for the Truncate and Insert and Append load strategies, causing all matching source files to be reprocessed.
Select the ID of the Google Cloud Platform project to load data into. The special value [Environment Default] uses the project defined in the environment.
Select the Google BigQuery dataset to load data into. The special value [Environment Default] uses the dataset defined in the environment.
The name of the table to be created in your Google BigQuery project. You can use a Table Input component in a transformation pipeline to access and transform this data after it has been loaded. Define what happens if the table name already exists in the specified destination.
- Replace: If the specified table name already exists, that table will be destroyed and replaced by the table created during this pipeline run.
- Truncate and Insert: Each time the pipeline runs, two operations are performed: first, the table is truncated, meaning all existing rows are deleted. Then, your new rows are inserted. The table itself is never destroyed and recreated.
- Fail if Exists: If the specified table name already exists, this pipeline will fail to run.
- Append: If the specified table name already exists, then the data is inserted without altering or deleting the existing data in the table. It’s appended onto the end of the existing data in the table. If the specified table name doesn’t exist, then the table will be created, and your data will be inserted into the table.
Advanced Settings
Snowflake
Databricks
Google BigQuery
Specify the number of errors or the percentage of errors required to skip the file. This parameter only accepts integer characters. % is not accepted. Specify percentages as a number only.This parameter is only available when On Error is set to either Skip File When n Errors or Skip File When n% Errors.
Specify the maximum size, in bytes, of data to be loaded for a given COPY statement. If the maximum is exceeded, the COPY operation discontinues loading files. For more information, refer to the Snowflake documentation.
When True, purges data files after the data is successfully loaded. Default setting is False.
Specify whether to load semi-structured data into columns in the target table that match corresponding columns represented in the data.
- Case Insensitive: Load semi-structured data into columns in the target table that match corresponding columns represented in the data. Column names should be case-insensitive.
- Case Sensitive: Load semi-structured data into columns in the target table that match corresponding columns represented in the data. Column names should be case-sensitive.
- None: The COPY operation loads the semi-structured data into a variant column or, if a query is included in the COPY statement, transforms the data.
When True, strings are automatically truncated to the target column length. When False (default), the COPY statement produces an error if a loaded string exceeds the target column length.
When True, loads all files, regardless of whether they have been loaded previously and haven’t changed since they were loaded. Default setting is False.When set to True, this option reloads files and can lead to duplicated data in a table.
When True, loads all files, regardless of whether they have been loaded previously and haven’t changed since they were loaded. Default setting is False.When set to True, this option reloads files and can lead to duplicated data in a table. The Load strategy in the destination needs to be Truncate and Insert or Append for this to work.
Select the rounding mode to use when storing numeric data types. For more information, read the Google BigQuery RoundingMode documentation.
ROUND_HALF_AWAY_FROM_ZERO rounds half values away from zero.ROUND_HALF_EVEN rounds half values to the nearest even value.
