mapping_rules

Creates, updates, deletes, gets or lists a mapping_rules resource.

Overview

Name	mapping_rules
Type	Resource
Id	google.datamigration.mapping_rules

Fields

Name	Datatype	Description
name	string	Full name of the mapping rule resource, in the form of: projects/{project}/locations/{location}/conversionWorkspaces/{set}/mappingRule/{rule}.
conditionalColumnSetValue	object	Options to configure rule type ConditionalColumnSetValue. The rule is used to transform the data which is being replicated/migrated. The rule filter field can refer to one or more entities. The rule scope can be one of: Column.
convertRowidColumn	object	Options to configure rule type ConvertROWIDToColumn. The rule is used to add column rowid to destination tables based on an Oracle rowid function/property. The rule filter field can refer to one or more entities. The rule scope can be one of: Table. This rule requires additional filter to be specified beyond the basic rule filter field, which is whether or not to work on tables which already have a primary key defined.
displayName	string	Optional. A human readable name
entityMove	object	Options to configure rule type EntityMove. The rule is used to move an entity to a new schema. The rule filter field can refer to one or more entities. The rule scope can be one of: Table, Column, Constraint, Index, View, Function, Stored Procedure, Materialized View, Sequence, UDT
filter	object	A filter defining the entities that a mapping rule should be applied to. When more than one field is specified, the rule is applied only to entities which match all the fields.
filterTableColumns	object	Options to configure rule type FilterTableColumns. The rule is used to filter the list of columns to include or exclude from a table. The rule filter field can refer to one entity. The rule scope can be: Table Only one of the two lists can be specified for the rule.
multiColumnDataTypeChange	object	Options to configure rule type MultiColumnDatatypeChange. The rule is used to change the data type and associated properties of multiple columns at once. The rule filter field can refer to one or more entities. The rule scope can be one of:Column. This rule requires additional filters to be specified beyond the basic rule filter field, which is the source data type, but the rule supports additional filtering capabilities such as the minimum and maximum field length. All additional filters which are specified are required to be met in order for the rule to be applied (logical AND between the fields).
multiEntityRename	object	Options to configure rule type MultiEntityRename. The rule is used to rename multiple entities. The rule filter field can refer to one or more entities. The rule scope can be one of: Database, Schema, Table, Column, Constraint, Index, View, Function, Stored Procedure, Materialized View, Sequence, UDT
revisionCreateTime	string	Output only. The timestamp that the revision was created.
revisionId	string	Output only. The revision ID of the mapping rule. A new revision is committed whenever the mapping rule is changed in any way. The format is an 8-character hexadecimal string.
ruleOrder	string	Required. The order in which the rule is applied. Lower order rules are applied before higher value rules so they may end up being overridden.
ruleScope	string	Required. The rule scope
setTablePrimaryKey	object	Options to configure rule type SetTablePrimaryKey. The rule is used to specify the columns and name to configure/alter the primary key of a table. The rule filter field can refer to one entity. The rule scope can be one of: Table.
singleColumnChange	object	Options to configure rule type SingleColumnChange. The rule is used to change the properties of a column. The rule filter field can refer to one entity. The rule scope can be one of: Column. When using this rule, if a field is not specified than the destination column's configuration will be the same as the one in the source column..
singleEntityRename	object	Options to configure rule type SingleEntityRename. The rule is used to rename an entity. The rule filter field can refer to only one entity. The rule scope can be one of: Database, Schema, Table, Column, Constraint, Index, View, Function, Stored Procedure, Materialized View, Sequence, UDT, Synonym
singlePackageChange	object	Options to configure rule type SinglePackageChange. The rule is used to alter the sql code for a package entities. The rule filter field can refer to one entity. The rule scope can be: Package
sourceSqlChange	object	Options to configure rule type SourceSqlChange. The rule is used to alter the sql code for database entities. The rule filter field can refer to one entity. The rule scope can be: StoredProcedure, Function, Trigger, View
state	string	Optional. The mapping rule state

Methods

Name	Accessible by	Required Params	Description
get	SELECT	conversionWorkspacesId, locationsId, mappingRulesId, projectsId	Gets the details of a mapping rule.
list	SELECT	conversionWorkspacesId, locationsId, projectsId	Lists the mapping rules for a specific conversion workspace.
create	INSERT	conversionWorkspacesId, locationsId, projectsId	Creates a new mapping rule for a given conversion workspace.
delete	DELETE	conversionWorkspacesId, locationsId, mappingRulesId, projectsId	Deletes a single mapping rule.
import	EXEC	conversionWorkspacesId, locationsId, projectsId	Imports the mapping rules for a given conversion workspace. Supports various formats of external rules files.

SELECT examples

Lists the mapping rules for a specific conversion workspace.

SELECT
name,
conditionalColumnSetValue,
convertRowidColumn,
displayName,
entityMove,
filter,
filterTableColumns,
multiColumnDataTypeChange,
multiEntityRename,
revisionCreateTime,
revisionId,
ruleOrder,
ruleScope,
setTablePrimaryKey,
singleColumnChange,
singleEntityRename,
singlePackageChange,
sourceSqlChange,
state
FROM google.datamigration.mapping_rules
WHERE conversionWorkspacesId = '{{ conversionWorkspacesId }}'
AND locationsId = '{{ locationsId }}'
AND projectsId = '{{ projectsId }}';

INSERT example

Use the following StackQL query and manifest file to create a new mapping_rules resource.

All Properties

/*+ create */
INSERT INTO google.datamigration.mapping_rules (
conversionWorkspacesId,
locationsId,
projectsId,
name,
displayName,
state,
ruleScope,
filter,
ruleOrder,
singleEntityRename,
multiEntityRename,
entityMove,
singleColumnChange,
multiColumnDataTypeChange,
conditionalColumnSetValue,
convertRowidColumn,
setTablePrimaryKey,
singlePackageChange,
sourceSqlChange,
filterTableColumns
)
SELECT 
'{{ conversionWorkspacesId }}',
'{{ locationsId }}',
'{{ projectsId }}',
'{{ name }}',
'{{ displayName }}',
'{{ state }}',
'{{ ruleScope }}',
'{{ filter }}',
'{{ ruleOrder }}',
'{{ singleEntityRename }}',
'{{ multiEntityRename }}',
'{{ entityMove }}',
'{{ singleColumnChange }}',
'{{ multiColumnDataTypeChange }}',
'{{ conditionalColumnSetValue }}',
'{{ convertRowidColumn }}',
'{{ setTablePrimaryKey }}',
'{{ singlePackageChange }}',
'{{ sourceSqlChange }}',
'{{ filterTableColumns }}'
;

Manifest

- name: your_resource_model_name
  props:
    - name: name
      value: string
    - name: displayName
      value: string
    - name: state
      value: string
    - name: ruleScope
      value: string
    - name: filter
      value:
        - name: parentEntity
          value: string
        - name: entityNamePrefix
          value: string
        - name: entityNameSuffix
          value: string
        - name: entityNameContains
          value: string
        - name: entities
          value:
            - string
    - name: ruleOrder
      value: string
    - name: revisionId
      value: string
    - name: revisionCreateTime
      value: string
    - name: singleEntityRename
      value:
        - name: newName
          value: string
    - name: multiEntityRename
      value:
        - name: newNamePattern
          value: string
        - name: sourceNameTransformation
          value: string
    - name: entityMove
      value:
        - name: newSchema
          value: string
    - name: singleColumnChange
      value:
        - name: dataType
          value: string
        - name: charset
          value: string
        - name: collation
          value: string
        - name: length
          value: string
        - name: precision
          value: integer
        - name: scale
          value: integer
        - name: fractionalSecondsPrecision
          value: integer
        - name: array
          value: boolean
        - name: arrayLength
          value: integer
        - name: nullable
          value: boolean
        - name: autoGenerated
          value: boolean
        - name: udt
          value: boolean
        - name: customFeatures
          value: object
        - name: setValues
          value:
            - string
        - name: comment
          value: string
    - name: multiColumnDataTypeChange
      value:
        - name: sourceDataTypeFilter
          value: string
        - name: sourceTextFilter
          value:
            - name: sourceMinLengthFilter
              value: string
            - name: sourceMaxLengthFilter
              value: string
        - name: sourceNumericFilter
          value:
            - name: sourceMinScaleFilter
              value: integer
            - name: sourceMaxScaleFilter
              value: integer
            - name: sourceMinPrecisionFilter
              value: integer
            - name: sourceMaxPrecisionFilter
              value: integer
            - name: numericFilterOption
              value: string
        - name: newDataType
          value: string
        - name: overrideLength
          value: string
        - name: overrideScale
          value: integer
        - name: overridePrecision
          value: integer
        - name: overrideFractionalSecondsPrecision
          value: integer
        - name: customFeatures
          value: object
    - name: conditionalColumnSetValue
      value:
        - name: valueTransformation
          value:
            - name: isNull
              value: []
            - name: valueList
              value:
                - name: valuePresentList
                  value: string
                - name: values
                  value:
                    - string
                - name: ignoreCase
                  value: boolean
            - name: intComparison
              value:
                - name: valueComparison
                  value: string
                - name: value
                  value: string
            - name: doubleComparison
              value:
                - name: valueComparison
                  value: string
                - name: value
                  value: number
            - name: assignSpecificValue
              value:
                - name: value
                  value: string
            - name: roundScale
              value:
                - name: scale
                  value: integer
            - name: applyHash
              value: []
        - name: customFeatures
          value: object
    - name: convertRowidColumn
      value:
        - name: onlyIfNoPrimaryKey
          value: boolean
    - name: setTablePrimaryKey
      value:
        - name: primaryKeyColumns
          value:
            - string
        - name: primaryKey
          value: string
    - name: singlePackageChange
      value:
        - name: packageDescription
          value: string
        - name: packageBody
          value: string
    - name: sourceSqlChange
      value:
        - name: sqlCode
          value: string
    - name: filterTableColumns
      value:
        - name: includeColumns
          value:
            - string
        - name: excludeColumns
          value:
            - string

DELETE example

Deletes the specified mapping_rules resource.

/*+ delete */
DELETE FROM google.datamigration.mapping_rules
WHERE conversionWorkspacesId = '{{ conversionWorkspacesId }}'
AND locationsId = '{{ locationsId }}'
AND mappingRulesId = '{{ mappingRulesId }}'
AND projectsId = '{{ projectsId }}';