Mass Ingestion

Back Next

Rules for customizing data type mappings

When you configure a target for a database ingestion task, you can optionally define data-type mapping rules to override the default mappings of source data types to target data types.

The default mappings are described in Default Data Type Mappings.

This feature is supported for tasks that have an Oracle source and a target type that supports SQL, including Databricks Delta, Google BigQuery, Microsoft Azure Synapse Analytics, Oracle, and Snowflake.

For example, you can create a data-type rule that maps Oracle NUMBER columns that have no precision to Snowflake target NUMBER() columns that also have no precision, instead of using the default mapping to the Snowflake VARCHAR(255) data type.

To create a data-type mapping rule:

Expand

Data Type Rules

In the

Create Rule

fields, enter a source data type and the target data type that you want to map it to.

In the Source
field only, you can include the percent (%) wildcard to represent the data type precision, scale, or size, for example, NUMBER(%,4), NUMBER(8,%), or NUMBER(%). Use the wildcard to cover all source columns that have the same data type but use different precision, scale, or size values, instead of specifying each one individually. For example, enter FLOAT(%) to cover FLOAT(16), FLOAT(32), and FLOAT(84). You cannot enter the % wildcard in the target data type. A source data type that uses the % wildcard must map to a target data type that uses specific precision, scale, or size value. For example, you could map the source data type FLOAT(%) to a target data type specification such as NUMBER(38,10).

Click

Add Rule

The rule appears in the list of rules.

To delete a rule, click the Delete icon at the right end of the rule row.

After you deploy a task with custom mapping rules, you cannot edit the rules until the task is undeployed.

Notes:

If you define multiple data-type rules for the same source data type with the same length or same precision and scale values, you will not be able to save the database ingestion task.

If you define multiple data-type rules for the same source data type but use the % wildcard to represent the length or precision and scale value in one rule and a specific length or precision and scale value in the second rule, the rule that contains the specific value is processed first, before the rule with the % wildcard. For example, if you map the source data types FLOAT(84) and FLOAT(%), the FLOAT(84) rule is processed first and then the FLOAT(%) rule is processed to cover any other FLOAT source columns with different sizes.

If a source data type requires a length or precision and scale value, make sure that you set the required attribute by using the % wildcard or a specific value, for example, VARCHAR(%) or VARCHAR(10).

If you define an invalid mapping, an error message is written to the log. You can then correct the mapping error, with assistance from your DBA if necessary.

For Oracle sources, you must use the data types that are returned by the following query for the source object:

select dbms_metadata.get_ddl('TABLE', 'YOUR_TABLE_NAME','TABLE_OWNER_NAME') from dual;

Mass Ingestion Databases does not differentiate between Oracle TIMESTAMP(0) and DATE data types when you define data-type mapping rules. If you create a rule for TIMESTAMP(0), the database ingestion job will also apply this rule to DATE columns.

Mass Ingestion Databases does not support the BYTE and CHAR semantics in data-type mappings rules.

If a source data type has a default value, you must specify it in your rule. For example, you must use TIMESTAMP(6) instead of TIMESTAMP.

Rename Saved Search

Table of Contents

Mass Ingestion

Mass Ingestion

Rules for customizing data type mappings

Rules for customizing data type mappings